As promised, here is the follow up of the MySQL 8.0-dev Progress teaser ;-)
so, yes, we expect to see the most hot contentions gone, as it's seen from the following graph :
Observations :
- the graph is representing the spin waits / spin rounds events happening during the same Sysbench Update-NoKEY workload
- the load is progressing from 8 concurrent users to 16, 32, .. 512
- 3 engines are tested one after one : MySQL 5.7, MySQL 8.0 (current DMR), MySQL 8.0-dev (current prototype)
- first all 3 engines are running on 22cores-HT only (1 CPU socket)
- then, the second time : on 44cores-HT (2 CPU sockets)
- and the most important thing to retain about this graph is that on 8.0-dev we see all main hot contentions gone ;-)
And not having all these contentions is resulting in the following :
Observations :
- one of the most painful things about current InnoDB WRITE performance is its scalability.. (in fact the absence of scalability ;-))
- the problem is not new, just that since READ scalability was greatly improved since MySQL 5.7, seeing WRITE to remain not scaling become even more painful..
- and this is because of all overhead and contentions we have on REDO level, transactions & lock management, etc..
- so, MySQL 5.7 and 8.0 DMR are limited by this, and moving from 1 CPU socket to 2 CPU socket cannot help here (in fact the things may become only worse as with more CPU cores all these contentions will become only higher)..
- while 8.0-dev code is giving us a huge expectation to finally see all these problems gone, and potentially get x2 times better performance even on a single CPU socket !! ;-))
- NOTE : we're not running for "high numbers" here, there was a long and hard battle to see performance improvement since a low load as well, so a positive difference is already seen on 8 users, and even more seen since 16 ;-)
However, this is not the only benefit.. -- the story is going way more far, because all this deep remastering is potentially allowing us to get a rid of all the overhead we see when READ
COMMITTED (RC) transaction isolation is used.. - example of such an overhead you can see from here :
http://dimitrik.free.fr/blog/archives/2015/02/mysql-performance-impact-of-innodb-transaction-isolation-modes-in-mysql-57.html
and this was one of many reasons why REPEATABLE READ (RR) transaction isolation is historically used within InnoDB by default (and still continue to be the default in MySQL 5.7)..
While potentially with 8.0-dev the things could finally change ;-)
The following graphs are representing the results from Sysbench OLTP_RW workload comparing MySQL 5.7 and 8.0-dev:
Observations :
- first the test is executed with RR isolation on both 5.7 and 8.0-dev
- yes, 8.0-dev is doing better than 5.7, but the most important is the next ;-)
- the next step both are executed with RC isolation
- and then you can see TPS on 5.7 going lower..
- while on 8.0-dev TPS level remains just the same on RC as on RR !! ;-)
So, crossing fingers, if all is going as expected, this could allow us for the first time to have RC isolation in InnoDB as default.. -- what does it mean for all MySQL users and their apps ?..
=> huge overall experience changes + less headache + less locking conflict, etc. etc. etc. (and to not go very far, just to mention the yesterday's article from Alex :
https://www.percona.com/blog/2017/05/08/chasing-a-hung-transaction-in-mysql-innodb-history-length-strikes-back/)..
Well, work in progress, crossing fingers, stay tuned ;-))
the last remark: just to bring your attention once more that MySQL 8.0 is moved to have UTF8 charset by default !!! => so, if you're planning to run any tests with 8.0
DMR, please, mind to remember this !! - so use UTF8 on both sides (server and client) and consider the UTF8 overhead, or switch the server back to latin1 if your "client" apps are latin1.. --
otherwise you'll not be happy ;-))
more benchmark results and further observations about MySQL 8.0-dev you can find in my slides :
Thank you for using MySQL ! and Go MySQL !! ;-))
Rgds,
-Dimitri