E831 MSQL Database Tests

We are certifying the MSQL Database for use in the E831/FOCUS online system, as a replacement for the VMS-specific Datatrieve system used our the previous experiment E687.

We have tested the reliability and performance of MSQL both within the expected, and well beyond the expected limits of usage. We have also completed a pseudo-formal code review, to determine that the program is well structured, uses standard techniques, and can be maintained. We have written a local installation guide for the Fermilab software environment. Donatella Torretta has also produced a nice document PN519 (postscript) comparing and reviewing Oracle, MSQL and HEPDB.

The largest existing E687 database files contained about 10,000 records. In normal usage only one process was likely to be actively accessing any database. Typical thousand row updates required several hours to complete.

We have tested up to 500,000 records with 3 to 8 simultaneous updates. Thousand row updates comparable to those in E687 complete in under 2 minutes.

We have tested MSQL Version 1.0.7, under IRIX 5.3 . Test are run on a 64 Megabyte Indy R4600PC-133 (dc831.fnal.gov), using a local server and local disk.

Our tests measure:

1. Speed of loads, updates and selects
2. Integrity of data

We measure these under several conditions:

1. Various table sizes ( 1K, 20K, 100K, 200K, 500K )
2. Single client and multiple client

These tests use rows of one CHAR(16) string and five INT's. The data fields are

• name CHAR(16)
• main INT
• slot INT
• chan INT
• start INT
• exp INT

Each record is updated in two steps:

1. Update field 'exp'in the unexpired record matching each channel:
   UPDATE data SET exp=... WHERE name='...' AND exp=2000000000
2. Write a new unexpired record
   INSERT INTO data VALUES ( ... )

An ASCII dump of rows modfied and inserted by each update set was compared to a dump from a small reference table built with a single client. The dumps are found to be identical in all cases.

We appear to be CPU limited in these tests. The Unix 'top' utility shows that 80% to 95% of the available CPU capacity is being used by the client and server during all substantial updates. Typically, the server uses 80% and the client 15%.

MSQL presently supports only Primary Keys, which must be unique. Our E831 tables do not have any unique fields which could be used as Primary Keys. Therefore we are measuring the raw, unindexed speed of the MSQL database.

We display in the chart below the times required to perform each of the measured tasks. The times reported are elapsed times, measured with the 'time' shell command. The system is otherwise idle during these tests. The tasks are performed by 'load', 'update' and 'select' test programs written in C.

For the smaller tables we do a repeated select within the test program, in order to reduce program loading and startup overheads. By moving the server connect and disconnect code in and out of this loop we have measured the connect+disconnect overhead to be about 12 milliseconds. This is about the same time as the actual select process for a single record in a 1000 row table.

The following items are tabulated and plotted below :

• The time required to pre-load the table to its initial size
• The time required to perform 1000 row updates
• The time required to select a single row
• The time required to select 1000 rows with a single SQL query
• The time required to load 1000 additional rows
• The amount of virtual memory used by the msqld server process