In the second post in my series on open source databases on big machines, we’ll look at whether READ COMMITTED scales with multiple cores.
The default transaction level for InnoDB is REPEATABLE READ. A more permissive level is READ COMMITTED, and is known to work well. While the REPEATABLE READ level maintains the transaction history up to the start of the transaction, READ COMMITTED maintains the transaction history up to the start of the current statement. Peter Zaitsev described the differences between how these modes are handled in this blog post. Both can theoretically cause performance slowdowns, but READ COMMITTED is usually seen as fast-working – at least on a typical MySQL machine (owned by Percona):
The default transaction isolation mode for PostgreSQL is also READ COMMITTED. Originally I wanted to use this mode for MySQL tests as well. But when I tested on a machine with 144 cores, I found that after 36 threads REPEATABLE READ continued to scale while READ COMMITTED slowed down. It then got stuck at about 3x slower results for standard OLTP RW tests.
Dimitri Kravtchuk wrote about performance issues with READ COMMITTED in 2015, but he tested with 40 cores that time. My tests show that there is a huge difference after 40 cores.
I tested this originally with Percona Server 5.7.15 and recently re-tested with Oracle’s MySQL versions 5.6.35 and 5.7.17. I confirmed that the bug exists in these versions as well, and reported it. I the Oracle MySQL Team fixes it. The good news is that while 5.6 stopped scaling after 16 threads, 5.7 improves this to 36 threads.
Results for 5.6.35:
Results for 5.7.17:
Machine details:
PostgreSQL Professional and Freematiq machine (tests for MYSQL 5.6.35, Percona 5.7.15 and MySQL 5.7.17 servers):
Processors: physical = 4, cores = 72, virtual = 144, hyperthreading = yes
Memory: 3.0T
Disk speed: about 3K IOPS
OS: CentOS 7.1.1503
File system: XFS
Percona machine (test for Percona 5.7.15 server):
Processors: physical = 2, cores = 12, virtual = 24, hyperthreading = yes
Memory: 251.9G
Disk speed: about 33K IOPS
OS: Ubuntu 14.04.5 LTS
File system: EXT4
Test: SysBench OLTP RW test, converted to use prepared statements, as described in this post.
MySQL Options: described in this post.
How come in the 1st graph read committed keeps pace with repeatable read all the way to 1024 threads while in the 2nd graph read committed falls far behind repeatable read after 36 threads? What causes the difference?
This depends from the number of InnoDB threads. For the first graph I used machine with 24 CPU cores and innodb_thread_concurrency set to 24. For the second I used machine with 144 cores.
Note also different number of TPS. On the machine with 24 CPU cores number is below 10,000 and on machine with 144 cores number of TPS with 36 threads is 20,000 (two times more) with maximum near to 50,000
I see. What’s the general recommendation on innodb_thread_concurrency? Should it be 0 (as in the 2nd graph) or set to the number of cores (as in the 1st graph)?
It is 144 in the second graph. Recommendation is from number of CPU cores to 2*(NumCPUs+NumDisks). See old, but still actual blog post, explaining why is this: https://www.percona.com/blog/2006/06/05/innodb-thread-concurrency/ and recommendations from the user manual: https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_thread_concurrency
Oh OK. Thanks.