DimitriK's (dim) Weblog

MySQL Performance: Analyzing Benchmarks, part 4: TRX list

This article inspired by benchmark results published by Alexey @Percona related to the "trx_list" modifications came with latest Percona Server 5.5. I was particularly curious about this feature, because the exactly the same solution was rejected by Sunny two years ago while analyzed this kind of problems (described within presented bug report and others)..

But well, one real test result is better than many discussions, so let's see what kind of results I will get on my own server. I'll test the same OLTP_RO Point-Select workload using 8 tables (by running 8 Sysbench processes in parallel). I'd say that this kind of load was initially pretty problematic for MySQL 5.6, as queries here are very short and running particularly fast. So, the idea of Read-Only transaction feature coming in 5.6 initially because of this workload, before being useful in other cases as well.

So, lets try the first case when transactions are not used and user sessions are using auto-commit (which is automatically will use Read-Only transactions in 5.6). The following graph is representing OLTP_RO Point-Select test on 16cores (MySQL server is running within a taskset with 16cores HT-enabled):

MySQL 5.6 -vs- Percona 5.5 @16cores-HT :

Observations :

• Percona Server 5.5 is still hitting kernel_mutex contention here
• While MySQL 5.6 is hitting now trx_sys_mutex contention here, which is much lower also and near invisible comparing to kernel_mutex waits on the InnoDB mutex waits graph...
• Percona Server is reaching near 200K QPS here (seems like my server is not as fast as tested by Alexey to show higher QPS)..
• while MySQL 5.6 is not far from 300K QPS within the same workload..
• so, looks to me like Sunny was right ;-)
• or probably we will have more details once Percona will release their 5.6 code tree? - let's see..
• however, the advantage of 5.6 on this test is pretty clear and doesn't need any comment ;-)

Now, let's bring to the same picture also MySQL 5.5 and MariaDB 5.5.

MySQL 5.6/ MySQL 5.5/ MariaDB 5.5/ Percona 5.5 @16cores-HT :

Observations :

• Percona 5.5 has a clear advantage here over MySQL 5.5 and MariaDB 5.5
• kernel_mutex contention is dominating on all 5.5 based engines
• while MySQL 5.6 is just out-passing them all..

However, more work is needed in 5.6 to get a rid of this trx_sys_mutex contention, because on 32cores it kills performance and we are hitting QPS drop rather increase. And again, have to lower this contention by using a bigger "spin delay" value (96 here) to avoid getting things even worse.. However MySQL 5.6 is still better here than Percona 5.5:

MySQL 5.6 / Percona 5.5 @32cores :

And if we enable HT here the result will be only worse:

MySQL 5.6 @32cores, HT=off / on :

Probably this is a good example of problems related to hot contentions (enabling HT on 16cores improving performance, and using 16cores-HT giving a better result than pure 32cores -- as this contention is so hot, it's as expected then..)

While, of course, as soon as you're having slightly more heavy queries within your workload, this contention will have much less impact, and you'll get higher QPS on 32cores rather on 16cores and matching your HW capacity.. But we have a good challenge here, so work continues, stay tuned ;-)


Regarding the test case #2 (when transactions are used) - well, nothing surprising in results, because it was exactly the reason to have Read-Only transactions feature, right? ;-) However, I may say you now that in the next MySQL 5.6 release the code is discovering "auto-magically" Read-Only transactions (in fact any transaction is considered Read-Only until it did not involve any writes, and switching to a "normal" transaction model as soon as the first write was involved).. - just wait little bit before the code is published..

To summarize, here are few "simple" charts presenting obtained results:

OLTP_RO 8-tables Point-Selects @16cores-HT :

OLTP_RO 8-tables Point-Selects @32cores :

All this said, there was a very good progress we made already in MySQL 5.6, but there are still tons of challenges in MySQL to improve performance and yet more to do ;-) (and many similar things coming in mind while on road to Percona Live MySQL Conference and flying from Paris to San Francisco.. - looking forward to discuss about any MySQL Performance problems there, so see you soon, it's already within 2 days! ;-))

Rgds,
-Dimitri