I found interesting blog post comparing speeds of free databases. Test used MS SQL Express (2 versions) and MySQL. I decided to port that test into IBM DB2 9.7 and test it on similar hardware. Original test used CPU Intel Core 2 1.80 GHz, 2 GB RAM, OS: MS Windows XP. My configuration is same except slightly faster CPU running on 2.2 Ghz. I executed only bechmark for DB2 Express C and PostgreSQL, results for other databases are copied from linked blog post.
We are creating 3 tables, one with 10M rows and two smaller tables with about 1M and 3M rows. We are testing speed of INNER JOIN between these tables using 2 SQL queries.
|table_cislo||1 001 213||cislo integer PRIMARY KEY, datum timestamp|
|table_datum||2 998 946||cislo integer IDENTITY, datum timestamp PRIMARY KEY|
|table_zaklad||10 000 000||cislo integer IDENTITY, datum timestamp|
Indexes: table_zaklad(cislo,datum) + indexes created by database because of primary key
Select 1: select count(*) as sel1 from table_zaklad tz inner join table_datum td on tz.datum = td.datum inner join table_cislo tc on tz.cislo = tc.cislo
Select 2: select count(*) as sel2 from table_cislo tc inner join table_datum td on tc.cislo=td.cislo inner join table_zaklad tz on td.datum = tz.datum
First task is to create and fill test tables using stored procedure. Test preparation time was: 110 minutes for MS SQL and 32 minutes for DB2. In case of DB2 this task was CPU bound, one core worked at 100%, in case of MS SQL task seems to be IO bound because it used at most 50% of one core. No prepare time data are available for MySQL and PostgreSQL because data were not created using stored procedure but loaded from CSV file.
Diskspace used for storing test data
|Database||Diskspace used (MB)|
|MS SQL 2005 Express (mdf+ldf)||647|
|MS SQL 2008 Express (mdf+ldf)||841|
|MySql 5.1 InnoDB||754|
|MySql 5.1 MyISAM (myi+myd+frm)||433|
|DB2 9.7 Express C||680|
|PostgreSQL 8.3.8||1 005|
Rules for benchmark are simple: Database server can use at most 0,5 GB RAM for cache. Restart database server to flush buffer cache before running each query.
Query times in seconds
|Database||Query 1||Query 2|
|MS SQL 2005 Express||70,71||65,78|
|MS SQL 2008 Express||109,09||92,21|
|MySql 5.1 InnoDB||34,14||104,78|
|MySql 5.1 MyISAM||44,89||85,73|
|DB2 9.7 Express C (noindex)||26,24||13,83|
|DB2 9.7 Express C (index)||11,87||18,06|
In DB2 we are using STMM to tune memory and tablespace with flag NO FILE SYSTEM CACHING for avoiding caching data by OS between database restarts and spoiling benchmarks. During benchmark STMM configured default buffer pool with 2500 pages (8K each). This is quite low especially when compared to memory cache used by other databases. Entire database is 860MB and it will not fit into max allowed 0,5GB buffer pool; disk reads are unavoidable.
There is probably bug in DB2 optimizer, because dropping indexes will make second query run bit faster. Very important is to do runstats on benchmark tables. Without runstats db2 is just slightly faster then MS SQL Express.
You can run this test yourself. Here is zip file with installation script and instructions: dbench.zip Right click on file and choose Save as..