ChannelDB2

DB2 Express C vs MS SQL Express vs MySQL vs PostgreSQL benchmark

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.

About benchmark


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 name rows columns
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

Preparations


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

Benchmark

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.


Results


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
PostgreSQL 8.3.8 145,96 268,89

DB2 notes


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.

Download


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..

Views: 6612

Comment

You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Comment by Radim Kolar on August 22, 2010 at 7:50am
I am preparing TPC-B benchmark using TPC-B 2.0 benchmark rules. Stay tuned for results.
Comment by Hackeron on December 26, 2009 at 6:10pm
It seems the benchmark there uses count(*) in all queries which is not a realistic or useful test at all.
Comment by SuryaPadmanaban on December 5, 2009 at 8:23am
great information.!
Comment by Radim Kolar on October 28, 2009 at 8:01am
I updated post, added results for PostgreSQL.
Comment by Norm on October 27, 2009 at 9:27pm
The secret sauce here is pre-fetch. DB2 detects that sequential access in the join and the buffer manager makes io requests to the io sub-system before the agent process doing the join needs the page.
Not sure what your benchmark rules are, but there are a few things to speed it up a few notches:
- a clustering index on datum column on each table
- if database is on RAID, then set db2_parallel_io=*
- set a block area in the buffer pool to speed up the sequential access

Norm
Comment by Pradeep Kumar G S on October 12, 2009 at 12:49am
thats a great news to the db2 community
Comment by fuangwith on October 11, 2009 at 1:43am
wow

Featured Downloads

Try BLU Acceleration on Cloud

© 2018   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service