MyISAM vs InnoDB vs Postgres benchmark
6th October 2011
For some reason, I believed that MyISAM storage engine should be very fast – faster than InnoDB and Postgres. After all, MyISAM does not support transactions, has no logging, and is overall simpler than “true” storage engines/databases.
I was surprised to find out that this isn’t true, at least for the specific (simple!) query I’m interested in:
- SELECT primary_id FROM tablename WHERE indexed_varchar = %s AND intcol1 <= %d AND intcol2 > %d
While at it, I also tested Postgres on that same database, using the same method. With only 1 thread, Postgres was somewhere between MyISAM and InnoDB in queries per second, while with more threads Postgres was getting way ahead. This was expected, because I’ve known that Postgres generally has much better multi-threading scalability than MySQL.
I should have trusted the results posted back in 2007 on mysqlperformanceblog.
Versions tested: MySQL 5.1.41, PostgreSQL 8.4.8. Postgres config was not modified, MySQL had key_buffer and query_buffer increased to values allowing to contain the whole table which was tested.
Note: be very careful when testing with different engines. Initially, I had completely different numbers for Postgres, because when manually porting MySQL schema to Postgres, indices for columns intcol1 and intcol2 were ported differently from MySQL version. That made Postgres results look terrible – 4k q/s at 8 threads.
Also note, that for MySQL the native mysqli PHP driver was used, while for Postgres I’ve used PDO.
Code for this benchmark is based on phptestsuite.stable.tar.gz.
Hardware used: core i7 (4 cores + 4 HT “cores”), frequency steps within 1.6-4.6 GHz; 16 GB RAM.
