More SSD vs HDD vs InnoDB vs MyISAM Numbers

I spent some more time today comparing InnoDB and MyISAM on SSD.

I increased the data/cache ratio by about 5X. I allocated 1G of memory for MyISAM or InnoDB (restating MySQL after each test). Resulting on disk images are 6G for MyISAM and 7G for InnoDB.

This is on a 30M row DB which stores ints and char data.

I’m primarily testing the theory that SSD could be used to get near in-memory performance by using cheap SSDs since they can do lots of random reads.

MyISAM would clearly outpace InnoDB if I would perform the initial ‘prepare’ (bulk insert) stage in multiple processes. MyISAM became CPU bottlenecked which ended up slowing the write rate.

InnoDB on the other end had the SSD at 100% utilization. I’m not sure why. It could either be an issue with the 16k page size or the write ahead log.

A 3x performance boost is more than acceptable here especially when you consider you can create a 96G SSD RAID array for the same price as 8G of RAM.

I might end up recompiling MySQL with an 8k and 4k page size for InnoDB just to see if it makes any difference.

Further, I might spend some time trying to figure out why InnoDB is so slow at performing the initial INSERTs.

200802031658

Figure 1: MySQL performance time for sysbench for inserting and performing OLTP queries on the data. Times are in minutes (lower is better)


  1. tgabi

    Thanks, for sharing. BTW, what filesystem you used for this and how much memory the test server had ?

  2. Hey.

    This was XFS.

    The system had 2G of memory total and 1G allocated to either key_buffer_size or innodb_buffer_pool_size (depending on which test I was running).

    I also restarted between tests to clear out memory.

  3. gu_falcon

    InnoDB is so slow on inserts because it stores them already in order of the primary key. MyIsam stores new sets in the order they are created and therefore is faster inserting but sower selecting. greetz

  4. gu_falcon,

    this isn’t the reason… we’re inserting sequentially ordered PKs so this isn’t a problem.

    Kevin






Follow

Get every new post delivered to your Inbox.

%d bloggers like this: