MySQL Performance: MySQL 5.6 GA -vs- MySQL 5.5 tuning details

This post is the next part following the initial article about MySQL 5.6 vs 5.5 benchmark results (and MySQL 5.6 scalability).

The focus in this article is on the "tuning impact" used during the published tests, and also I have for you few more test results to present which were missed initially -- Sysbench tests using 8 tables instead of a single one (as it does by default).

All my.conf setting I've used during the tests was already presented within an initial article, so let's go directly to details about special tuning setting. While many parameters are pretty important (like use or not use O_DIRECT, choose a right REDO log and Buffer Pool size, flush or not flush neighbor pages, right I/O capacity, etc.) -- but all these ones less or more predictable, and once chosen right, not giving you many troubles (unless something did not change in your workload or data volume). But there are two of them which are directly related to internal InnoDB contentions and should be very well tested before applied:

Adaptive Index Hashing (AHI, innodb_adaptive_hash_index, default=1) is helping a lot and in many cases, but sometimes supporting very badly concurrent access or concurrent changes which is expressed by growing contention on the "btr_search_latch" rw-lock. Such a contention may happen not only on a mixed Read-Write (RW) activity, but on a Read-Only (RO) as well. And there is no general rule, and the only real test may give you a real answer (while "generally" you may expect a higher contention on a RW workload rather on a RO)..

Spin Wait Delay (SD, innodb_spin_wait_delay, default=6) value is used on spin wait loops of mutexes and rw-locks in InnoDB. The setting is giving the "max" value for the random wait delay interval chosen on spin wait (expecting a mutex or rw-lock will be free soon, InnoDB is "spinning" (looping) on CPU involving "pause" instructions and trying to acquire the lock in the next loop). In fact this solution is "hiding" contention rather solving it, and may use CPU time a lot just for "spinning" (while we can do some other and more useful work) -- but in any case it gives you an expected benefit, and InnoDB locking is going really faster, and usually you're finally obtaining a better performance within your workload.. However, again, there is no "silver bullet", and the "right" value cannot be suggested generally, and that's why in MySQL 5.6 the default value remaining the same as in 5.5, while a bigger one can be more appropriate, but the result may still vary depending on your workload, CPU number, and CPU frequency on your server, and so on. At least the variable is dynamic and you can test it live on your workload (as I've made in RO testing, looking for the most optimal value).. I know that on my server this value may vary from 6 (default) to 128, and give a pretty interesting performance impact! For MySQL 5.6 tuning os this setting is simply a must, and you'll see how on one of the presented tests performance is dropping already on 64 concurrent user sessions (when default setting is used (sd=6)), while with a more appropriated one (sd=96) performance remains much more stable and much more higher!..

So far :

  • the following graphs are representing test results obtaining with variations: AHI= 1 or 0, SD= 6 or 96
  • in fact there are 4 combinations, and each one is named like "ahi=n,sd=m", where n is 1 or 0, and m is 6 or 96
  • on configurations with few CPU cores having a smaller SD value seems to be better in most of cases, while with more CPU cores a higher SD value is preferable (while there is may be some exceptions as well)
  • so, don't hesitate to test and you'll get the right answer for your workload


Then, for every test case the following graphs are presented :

  • Best-to-best TPS/QPS performance : there are both, TPS and QPS graphs, so you're able to see a ratio between transactions and queries/sec -- personally I prefer to see queries/sec performance, as it speaks much better (reaching near 300K QPS with all SQL and MySQL layers overhead is telling more than 20K TPS, except if you're aware about what exactly your transactions are doing)..
  • And then impact of SD and AHI pair within each CPU cores configuration:

    • Tuning impact @16cores
    • Tuning impact @32cores
    • Tuning impact @32cores with HT-enabled



Hope I did not forget anything. Here are the results:

Sysbench OLTP_ROBest-to-best TPS/QPS performance:


Tuning impact @16cores:



Tuning impact @32cores:



Tuning impact @32cores with HT-enabled:



Sysbench OLTP_RO 8-tablesBest-to-best TPS/QPS performance:


Tuning impact @16cores:


Tuning impact @32cores:


Tuning impact @32cores with HT-enabled:


Sysbench OLTP_RO-trx
Best-to-best TPS/QPS performance:


Tuning impact @16cores:


Tuning impact @32cores:


Tuning impact @32cores with HT-enabled:



Sysbench OLTP_RO-trx 8-tables
Best-to-best TPS/QPS performance:


Tuning impact @16cores:


Tuning impact @32cores:


Tuning impact @32cores with HT-enabled:



Sysbench OLTP_RO Point-Selects
Best-to-best TPS/QPS performance:


Tuning impact @16cores:


Tuning impact @32cores:


Tuning impact @32cores with HT-enabled:



Sysbench OLTP_RO Point-Selects 8-tables
Best-to-best TPS/QPS performance:


Tuning impact @16cores:


Tuning impact @32cores:


Tuning impact @32cores with HT-enabled:



Sysbench OLTP_RO Simple-Ranges
Best-to-best TPS/QPS performance:


Tuning impact @16cores:


Tuning impact @32cores:


Tuning impact @32cores with HT-enabled:



Sysbench OLTP_RW 10M
Best-to-best TPS/QPS performance:


Tuning impact @16cores:


Tuning impact @32cores:


Tuning impact @32cores with HT-enabled:



Sysbench OLTP_RW 10M 8-tables
Best-to-best TPS/QPS performance:


Tuning impact @16cores:


Tuning impact @32cores:


Tuning impact @32cores with HT-enabled:



SUMMARY :

  • as you can see, Spin Wait Delay is playing a very critical role for performance on your workload..
  • as well enabling or disabling AHI may bring some unexpected regression, select it right..
  • using or not using Hyper Threading (HT) for MySQL server is very depending on a workload too..
  • so, the main rule is: keep in mind all these settings and tune your MySQL 5.6 server to the best! ;-)
  • more to come..


to be continued..

Rgds,
-Dimitri