Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Does Read Committed Scale on 144 Cores?

DZone's Guide to

Does Read Committed Scale on 144 Cores?

Today, we look at MySQL and PostgreSQL performance running on a lot of cores. READ COMMITTED is viewed as fast-working, but how does it really scale?

· Database Zone
Free Resource

Read why times series is the fastest growing database category.

The default transaction level for InnoDB is REPEATABLE READ. A more permissive level is READ COMMITTED, which 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):

READ COMMITTED

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.

READ COMMITTED

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. 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:


READ COMMITTED

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.

Learn how to get 20x more performance than Elastic by moving to a Time Series database.

Topics:
mysql ,database ,database performance ,postresql

Published at DZone with permission of Sveta Smirnova, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}