Monday, May 07, 2012

mySQL 5.6 looks very promising

I have been doing some research lately on various mySQL related features and found myself distracted by mySQL 5.6. If everything that I am reading is correct, this should be the best mySQL version yet.

mySQL 5.6 will have multi thread replication, making time delayed slaves-an actual feature and not a byproduct of replicating high write volumes. Also, crash safe replication! When a master crashed and corrupted the binary log this was a pain in the ass to fix. In the past I had to write scripts to walk the primary key and do a checksum on each returned table chunk and pick which row was correct and which one was not.

The performance optimization for innodb that addresses some stalls is as exciting as multiple SQL threads for replication. One major change is in the stat layer, which was throttling throughput for in memory workloads at high thread concurrency. You can read the details here.

The optimizer is getting an overhaul that has been needed for some time. This is exciting but by habit I still will probably just use (FORCE|USE) INDEX. Here is an example of using FORCE INDEX to get better performance.

This stat shows innodb hitting the disk. A single query was flopping between two indexes producing 33% MORE disk reads then necessary. Making a single line change on that query gave me more capacity in EC2. (Don't hit the disk in EC2). In theory, the changes to the optimizer will reduce these flip-flops of indexes - but I doubt it will be as good as a human picking the index for their query.

Finally more and more stats from INFORMATION_SCHEMA which will probably create a flurry of bugs on the 1st couple days of release as the database hits large installs, and unexpected workloads exposes some stuff in the overall code - as everyone will start using information more to show dips in throughput.

In conclusion, 5.6 looks awesome and I can't wait for a Percona Port.

1 comment:

Øystein said...


Thanks for the good words about MySQL 5.6.
Note that the optimizer improvements are mostly not about picking the right index to use. MySQL 5.6 introduces several new query execution strategies (e.g., index condition push-down, batched key access, semijoin, and subquery materialization) that can not be achieved just by FORCE/USE INDEX.