I’ve been reviewing our settings for innodb prior to testing our new SSDs drives later this week.
Here are some initial thoughts:
* Both sync_binlog and innodb_flush_log_at_trx_commit should both be enabled. The extra seeks required isn’t really an issue on SSD and the extra reliability is worth the slight performance hit.
* Read ahead for Innodb and the underlying block driver should probably be disabled. There’s no sense reading another 512 blocks in SSD. You can get the IO quick enough so why slow yourself down potentially reading content you don’t need? Innodb use a heuristic algorithm for read ahead but the best it can do is equal the same performance as SSD. At the very minimum disabling disk based read ahead is probably a good idea.
* If your database is primarily small fixed size rows it might make sense to recompile using a smaller block size. SSD performance seems to be a function of write size. If you constantly need to write 16k where 90% is re-written content you’re going to see an effective 4x slowdown. Jeremy Cole mentioned that changing this would bloat the DB. I’ll have to experiment. I’m also going to have to figure out if O_DIRECT can be used with less than 16k block sizes. I don’t think it can.
* The new thread concurrency stuff in MySQL 5.1 is probably going to be very important. There’s no reason multiple concurrent threads shouldn’t be able to mutate and access the DB in parallel since we’re no longer really bound by disk seeks. Letting the DB go full out seems like a big win. This is going to require MySQL 5.1 though which should be available any year now (*cough*).
Given all this, I think performance will still be outstanding for Innodb on SSD but probably a good deal of variability in performance.