MySQL and the SSB – Part 2 – MyISAM vs InnoDB low concurrency

this blog post is part two in what is now a continuing series on the star schema benchmark.

in my previous blog post i compared mysql 5.5.30 to mysql 5.6.10, both with default settings using only the innodb storage engine.  in my testing i discovered that innodb_old_blocks_time had an effect on performance of the benchmark.  there was some discussion in the comments and i promised to follow up with more ssb tests at a later date.

i also promised more low concurrency ssb tests when peter blogged about the importance of performance at low concurrency .

the ssb
the ssb tests a database’s ability to optimize queries for a star schema. a star schema presents some unique challenge to the database optimizer. the ssb benchmark consists of four sets of queries. each set is known as a “flight”. i have labeled each query as q{flight_number}.{query_number}. in general, each flight examines different time periods or different regions. the flights represent the type of investigations and drill-downs that are common in olap analysis.

each query in each flight (q1.1 for example) is tested with a cold buffer pool. then the query is tested again without restarting the database. the first test is described as the cold test, and the second as the hot test. the database software is restarted after the hot test. all os caches are dropped at this time as well.

these set of queries were tested on the ssb at scale factor: 20. this means there is approximately 12gb of data in the largest table.

you can find the individual ssb query definitions in my previous blog post .

test environment
these tests were done on a relatively fast machine with a xeon e5-2680 (8 cores, 16 threads) with fast io (ocz r4 1.6tb) and 128gb memory. for the hot test, the data fits in the buffer pool and has been loaded by the cold test already. the buffer pool and adaptive hash index are cold for the cold test. all tests were done with no concurrency. the hardware for this test was provided by adotomi . i will be blogging about raw performance of the ocz card in another post.

also, while it is labeled on the graphs, it is important to note that in all cases, lower times are better .

ssb flight #1
here you will see the start of an interesting trend. myisam is faster when the data is not cached (the cold run) but is slower in the hot (cached) run. i did some investigation during the testing and found that innodb does more io than myisam when the database is cold, but uses less cpu time when the database is hot. i am only speculating (and i can investigate further), but i believe the adaptive hash index is improving performance of innodb significantly during the hot run, as hash indexes are faster than a b-tree index. also accessing pages from the buffer pool should be faster than getting them from the os cache, which is another advantage of innodb.

ssb flight #2
flight #2 is similar to flight #1. myisam is faster than innodb when the database is cold, but the opposite is true when the database is hot.

ssb flight #3
here in some cases myisam is substantially faster than innodb both cold and hot.

ssb flight #4
there is one query in this flight, q4.3, which is faster using myisam than innodb. like the queries in flight #3 that are faster using myisam, q4.3 examines very little data. it seems that innodb performs better when a larger number of rows must be joined together (q4.1, q4.2) but worse when small amounts of data are examined.

conclusion

in some cases myisam is faster than innodb, but usually only when the buffer pool is cold. please don’t take away that you should be using myisam for everything! . myisam may be good for raw performance, but there are limitations which myisam imposes that are difficult to work with.  myisam does not maintain checksum consistency during regular operations and is not acid compliant. myisam and innodb may perform differently under concurrency, which this benchmark does not cover. i will make a follow-up post about concurrency in another blog post in this series. regardless, when the working set fits in memory, innodb almost always performs better, at least for this workload.

notes

mysql version used: 5.6.11, custom compiled to remove performance_schema

for the innodb tests, a 64gb buffer pool was used. o_direct was used so, there was no caching of data at the filesystem level. the innodb indexes were built using alter table fast index creation (merge sort).

for the myisam tests i used a 10gb key buffer. i used alter table disable keys and built the keys with sort via alter table enable keys.
my.cnf

[mysqld]
datadir=/mnt/mysql56/data
basedir=/usr/local/mysql
socket=/var/lib/mysql/mysql.sock
user=justin
innodb_buffer_pool_size=64g
innodb_log_file_size=4g
innodb_file_per_table
innodb_stats_on_metadata=off
innodb_file_format=barracuda
innodb_log_buffer_size=32m
innodb_buffer_pool_instances=16
metadata_locks_hash_instances=32
table_open_cache_instances=8
sort_buffer_size=128k
read_rnd_buffer_size=8m
join_buffer_size=8m
default_tmp_storage_engine=myisam
tmpdir=/dev/shm
innodb_undo_logs=32
innodb_old_blocks_time=0
table_open_cache=2048
table_definition_cache=16384
innodb_flush_method=o_direct
key_buffer_size=10g
# disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_stats_persistent
innodb_stats_auto_update=off
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid