whenpenguinsattack.com

Tuesday, March 14, 2006

10 database speed tests

By Justin Silverton

I came across the following 10 benchmark tests covering:

SQLite version 3.3.3
SQLite version 3.3.3
SQLite version 2.8.17
SQLite version 2.8.17
PostgreSQL version 8.1.2
MySQL version 5.0.18
FirebirdSQL version 1.5.2

About the hardware/database settings used:

All databases were installed with default settings.
Tests were run on 1.6GHz Sempron with 1GB of ram and 7200rpm SATA disk running Windows 2000 + SP4 with all updates applied.

Test 1: 1000 INSERTs

CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty three');INSERT INTO t1 VALUES(2,75560,'seventy five thousand five hundred sixty');... 995 lines omittedINSERT 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');

SQLite 3.3.3 (sync):
3.823
SQLite 3.3.3 (nosync):
1.668
SQLite 2.8.17 (sync):
4.245
SQLite 2.8.17 (nosync):
1.743
PostgreSQL 8.1.2:
4.922
MySQL 5.0.18 (sync):
2.647
MySQL 5.0.18 (nosync):
0.329
FirebirdSQL 1.5.2:
0.320

Test 2: 25000 INSERTs in a transaction

BEGIN;CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));INSERT INTO t2 VALUES(1,298361,'two hundred ninety eight thousand three hundred sixty one');... 24997 lines omittedINSERT 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;

SQLite 3.3.3 (sync):
0.764
SQLite 3.3.3 (nosync):
0.748
SQLite 2.8.17 (sync):
0.698
SQLite 2.8.17 (nosync):
0.663
PostgreSQL 8.1.2:
16.454
MySQL 5.0.18 (sync):
7.833
MySQL 5.0.18 (nosync):
7.038
FirebirdSQL 1.5.2:
4.280

Test 3: 25000 INSERTs into an indexed table

BEGIN;CREATE TABLE t3(a INTEGER, b INTEGER, c VARCHAR(100));CREATE INDEX i3 ON t3(c);... 24998 lines omittedINSERT INTO t3 VALUES(24999,442549,'four hundred forty two thousand five hundred forty nine');INSERT INTO t3 VALUES(25000,423958,'four hundred twenty three thousand nine hundred fifty eight');COMMIT;

SQLite 3.3.3 (sync):
1.778
SQLite 3.3.3 (nosync):
1.832
SQLite 2.8.17 (sync):
1.526
SQLite 2.8.17 (nosync):
1.364
PostgreSQL 8.1.2:
19.236
MySQL 5.0.18 (sync):
11.524
MySQL 5.0.18 (nosync):
12.427
FirebirdSQL 1.5.2:
6.351

Test 4: 100 SELECTs without an index

SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<1000;select>=100 AND b<1100;select>=200 AND b<1200;...>=9700 AND b<10700;select>=9800 AND b<10800;select>=9900 AND b<10900;>Test 5: 100 SELECTs on a string comparison

SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one%';SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%two%';SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%three%';... 94 lines omittedSELECT 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%';

SQLite 3.3.3 (sync):
4.853
SQLite 3.3.3 (nosync):
4.868
SQLite 2.8.17 (sync):
4.511
SQLite 2.8.17 (nosync):
4.500
PostgreSQL 8.1.2:
6.565
MySQL 5.0.18 (sync):
3.424
MySQL 5.0.18 (nosync):
2.090
FirebirdSQL 1.5.2:
5.803

Test 6: INNER JOIN without an index

SELECT t1.a FROM t1 INNER JOIN t2 ON t1.b=t2.b;
SQLite 3.3.3 (sync):
14.473
SQLite 3.3.3 (nosync):
14.445
SQLite 2.8.17 (sync):
47.776
SQLite 2.8.17 (nosync):
47.750
PostgreSQL 8.1.2:
0.176
MySQL 5.0.18 (sync):
3.421
MySQL 5.0.18 (nosync):
3.443
FirebirdSQL 1.5.2:
0.141

Test 7: Creating an index

CREATE INDEX i2a ON t2(a);CREATE INDEX i2b ON t2(b);
SQLite 3.3.3 (sync):
0.552
SQLite 3.3.3 (nosync):
0.526
SQLite 2.8.17 (sync):
0.650
SQLite 2.8.17 (nosync):
0.605
PostgreSQL 8.1.2:
0.276
MySQL 5.0.18 (sync):
1.159
MySQL 5.0.18 (nosync):
0.275
FirebirdSQL 1.5.2:
0.264

Test 8: 5000 SELECTs with an index

SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100;select>=100 AND b<200;select>=200 AND b<300;...>=499700 AND b<499800;select>=499800 AND b<499900;select>=499900 AND b<500000;>Test 9: 1000 UPDATEs without an index

BEGIN;UPDATE t1 SET b=b*2 WHERE a>=0 AND a<10;update b="b*2">=10 AND a<20;... b="b*2">=9980 AND a<9990;update b="b*2">=9990 AND a<10000;commit;>Test 10: 25000 UPDATEs with an index

BEGIN;UPDATE t2 SET b=271822 WHERE a=1;UPDATE t2 SET b=28304 WHERE a=2;... 24996 lines omittedUPDATE t2 SET b=442549 WHERE a=24999;UPDATE t2 SET b=423958 WHERE a=25000;COMMIT;

SQLite 3.3.3 (sync):
1.883
SQLite 3.3.3 (nosync):
1.894
SQLite 2.8.17 (sync):
1.994
SQLite 2.8.17 (nosync):
1.973
PostgreSQL 8.1.2:
23.933
MySQL 5.0.18 (sync):
16.348
MySQL 5.0.18 (nosync):
17.383
FirebirdSQL 1.5.2:
15.542

Test 9: 1000 UPDATEs without an index

BEGIN;UPDATE t1 SET b=b*2 WHERE a>=0 AND a<10;update b="b*2">=10 AND a<20;... b="b*2">=9980 AND a<9990;update b="b*2">=9990 AND a<10000;commit;>Test 10: 25000 UPDATEs with an index

BEGIN;UPDATE t2 SET b=271822 WHERE a=1;UPDATE t2 SET b=28304 WHERE a=2;... 24996 lines omittedUPDATE t2 SET b=442549 WHERE a=24999;UPDATE t2 SET b=423958 WHERE a=25000;COMMIT;

SQLite 3.3.3 (sync):
3.153
SQLite 3.3.3 (nosync):
3.088
SQLite 2.8.17 (sync):
3.993
SQLite 2.8.17 (nosync):
3.983
PostgreSQL 8.1.2:
5.740
MySQL 5.0.18 (sync):
2.718
MySQL 5.0.18 (nosync):
1.641
FirebirdSQL 1.5.2:
2.976


If you want to see some more information on the above and 10 more tests, you can go Here

5 Comments:

  • use a graph you crazy bastard

    By Anonymous Anonymous, at 4:52 AM  

  • It looks like the results for test 8 got overwritten (with a dupe of the test 9 text).

    By Blogger Tracy Nelson, at 1:12 PM  

  • you're right. Test 8 and 9 now have the proper results.

    By Blogger justin silverton, at 1:21 PM  

  • Interesting results but the tests are not a very good indication of performance in realistic situations. Performing tend of thousand of inserts, repeated selects or updates in a single transaction rarely happens (it is the sort of thing done during infrequent batch imports/exports/updates). There is also no testing of concurrency at all, and only one test with joins. There was no serious look at sorting or grouping or aggregate functions.

    OF all the tests that would concern me at all, SQLLite and MySQL performed terribly compared to PgSQL and Firebird. The other tests looked rigged to make SQLLite and MySQL look good, Firebird look mediocre and PgSQL look bad. I say we should find more realistic benchmarks, like fewer INSERTS contained in numerous transactions instead of one big transaction, and all launched from several users at once to test concurrency, locking and atomicity. In such a case PgSQL would destroy the competition because it doesn't lock tables or even rows during data modification and at the same time maintains data integrity.

    I'd also like to see how intelligently each product uses indeces...how about a query joining 3, 4, 5 or more tables into one? Also, lets see performance during stored procedure compilation and execution and do a real test of indexed and non-indexed sorting, grouping and aggregate functions.

    I know that MySQL and SQLLite are very good at what they do and would be the first I'd consider for embedded databases or simply structured but heavily loaded databases. However in my experience they are not a great choice for multi-user apps with a lot of data modification and complex queries and views. The lack of proper validation on various datatypes, coarse locking levels and so on make the smaller-footprint, "faster" databases fall over quite quickly.

    By Anonymous Anonymous, at 3:15 PM  

  • Have you thought about doing the same tests w/ Oracle EE & XE?

    By Blogger Mike King, at 8:49 AM  

Post a Comment

Links to this post:

Create a Link

<< Home