Speed Comparison: SQLite, MySQL, PostgreSQL

This series of tests compares SQLcrypt™ with stock SQLite running on the same platform. The purpose is to measure the overhead introduced by SQLcrypt™'s storage-level cryptographic operations. The test script is speedtest-sqlcrypt.tcl and is adapted from SQLite's tools/speedtest.tcl.

The test script also tests MySQL and PostgreSQL. The intention is to get their numbers alongside SQLcrypt's and SQLite's, to provide a feel of how these database engines compare on the same platform, for these benchmarks. I shall discuss MySQL and PostgreSQL data encryption in a separate web page.

The version of MySQL used is the MySQL 4.1.7 Essential Windows installer. MySQL is configured for a developer workstation setup, using "multi-function storage", which means it uses both the original, fast ISAM storage and the newer, transaction-safe InnoBase storage. I do not know how MySQL decides which storage to use while running these tests. The MySQL installation is otherwise untuned.

The version of PostgreSQL used is 8.0.0 beta 4. Since this is a beta release, I have not included its numbers. I will do so when PostgreSQL version 8 is actually released.

Test Environment

The platform used for these tests is a Pentium III 833Mhz with 256MB RAM and an IDE 5400RPM disk. The operating system is Windows 98 SE2.

The first test creates an empty database, hence, for SQLcrypt™, it invokes the shell command ".encrypt" with the passphrase "passphrase". Subsequent tests invoke the shell command ".decrypt" because they are operating on an existing, encrypted database.

For each test, after SQLcrypt™ is done and before the other database engines are tested, the test input is modified to remove the commands ".encrypt" and ".decrypt" since these commands are specific to SQLcrypt™.

The times reported on all tests are wall-clock time in seconds. Both SQLite and SQLcrypt™ are tested with full disk synchronisation.

Test 1: 1000 INSERTs

.encrypt passphrase
CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));
INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty three');
... 996 lines omitted
INSERT INTO t1 VALUES(998,66289,'sixty six thousand two hundred eighty nine');
INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred twenty two');
INSERT INTO t1 VALUES(1000,94142,'ninety four thousand one hundred forty two');

Test 2: 25000 INSERTs in a transaction

.decrypt passphrase
BEGIN;
CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));
... 24998 lines omitted
INSERT INTO t2 VALUES(24999,447847,'four hundred forty seven thousand eight hundred forty seven');
INSERT INTO t2 VALUES(25000,473330,'four hundred seventy three thousand three hundred thirty');
COMMIT;

Test 3: 100 SELECTs without an index

.decrypt passphrase
SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<1000;
SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<1100;
... 95 lines omitted
SELECT count(*), avg(b) FROM t2 WHERE b>=9700 AND b<10700;
SELECT count(*), avg(b) FROM t2 WHERE b>=9800 AND b<10800;
SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b<10900;

Test 4: 100 SELECTs on a string comparison

.decrypt passphrase
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one%';
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%two%';
... 95 lines omitted
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety eight%';
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety nine%';
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one hundred%';

Test 5: Creating an index

.decrypt passphrase
CREATE INDEX i2a ON t2(a);
CREATE INDEX i2b ON t2(b);

Test 6: 5000 SELECTs with an index

.decrypt passphrase
SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100;
SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<200;
... 4995 lines omitted
SELECT count(*), avg(b) FROM t2 WHERE b>=499700 AND b<499800;
SELECT count(*), avg(b) FROM t2 WHERE b>=499800 AND b<499900;
SELECT count(*), avg(b) FROM t2 WHERE b>=499900 AND b<500000;

Test 7: 1000 UPDATEs without an index

.decrypt passphrase
BEGIN;
UPDATE t1 SET b=b*2 WHERE a>=0 AND a<10;
... 997 lines omitted
UPDATE t1 SET b=b*2 WHERE a>=9980 AND a<9990;
UPDATE t1 SET b=b*2 WHERE a>=9990 AND a<10000;
COMMIT;

Test 8: 25000 UPDATEs with an index

.decrypt passphrase
BEGIN;
UPDATE t2 SET b=271822 WHERE a=1;
... 24997 lines omitted
UPDATE t2 SET b=442549 WHERE a=24999;
UPDATE t2 SET b=423958 WHERE a=25000;
COMMIT;

Test 9: 25000 text UPDATEs with an index

.decrypt passphrase
BEGIN;
UPDATE t2 SET c='four hundred sixty eight thousand twenty six' WHERE a=1;
... 24997 lines omitted
UPDATE t2 SET c='thirty five thousand sixty five' WHERE a=24999;
UPDATE t2 SET c='three hundred forty seven thousand three hundred ninety three' WHERE a=25000;
COMMIT;

Test 10: INSERTs from a SELECT

.decrypt passphrase
BEGIN;
INSERT INTO t1 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t1;
COMMIT;

Test 11: DELETE without an index

.decrypt passphrase
DELETE FROM t2 WHERE c LIKE '%fifty%';

Test 12: DELETE with an index

.decrypt passphrase
DELETE FROM t2 WHERE a>10 AND a<20000;

Test 13: A big INSERT after a big DELETE

.decrypt passphrase
INSERT INTO t2 SELECT * FROM t1;

Test 14: A big DELETE followed by many small INSERTs

.decrypt passphrase
BEGIN;
DELETE FROM t1;
... 2998 lines omitted
INSERT INTO t1 VALUES(2999,37835,'thirty seven thousand eight hundred thirty five');
INSERT INTO t1 VALUES(3000,97817,'ninety seven thousand eight hundred seventeen');
COMMIT;

Test 15: DROP TABLE

.decrypt passphrase
DROP TABLE t1;
DROP TABLE t2;