In this blog post, we’ll be discussing Percona Server 5.7 performance improvements.
Starting from the Percona Server 5.6 release, we’ve introduced several significant changes that help address performance problems for highly-concurrent I/O-bound workloads. Some of our research and improvements were re-implemented for MySQL 5.7 – one of the best MySQL releases. But even though MySQL 5.7 showed progress in various aspects of scalability and performance, we’ve found that it’s possible to push I/O bound workload limits even further.
Percona Server 5.7.11 currently has two major performance features in this area:
- Multi-threaded LRU flusher. In a limited form, this feature exists in Percona Server 5.6. We split the LRU flusher thread out of the existing page cleaner thread, and it is now solely tasked with flushing the flush list. Along with several other important changes, this notably improved I/O bound workload performance. MySQL 5.7 has also made a step forward by introducing a pool of page cleaner threads that should help improve parallelism in flushing. However, we believe that the current approach is not good enough – especially for LRU flushing. In one of our next Percona Server 5.7 performance improvements posts, we’re going to describe aspects of MT flushing, and why it’s especially important to have an independent MT LRU flusher.
- Parallel doublewrite buffer. For ages, MySQL has had only one doublewrite buffer for flushing data pages. So even if you had several threads for flushing you couldn’t efficiently use them – doublewrite quickly became a bottleneck. We’ve changed that by attaching two doublewrite buffers to each buffer pool instance: one for each type of page flushing (LRU and flush list). This completely avoids any doublewrite contention, regardless of the flusher thread count. We’ve also moved the doublewrite buffer out of the system tablespace so you can now configure its location.
Now let’s review the results of a sysbench OLTP_RW, I/O-bound scenario. Below are the key settings that we used in our test:
- dataset 100GB
- innodb_buffer_pool_size=25GB
- innodb_doublwrite=1
- innodb_flush_log_at_trx_commit=1
While evaluating MySQL 5.7 RC we observed a performance drop in I/O-bound workloads, and it looked very similar to MySQL 5.6 behavior. The reason for the drop is the lack of free pages in the buffer pool. Page cleaner threads are unable to perform enough LRU flushing to keep up with the demand, and the query threads resort to performing single page flushes. This results in increased contention between all the of the flushing structures (especially the doublewrite buffer).
For ages (Vadim discussed this ten years ago!) InnoDB has had a universal workaround for most scalability issues: the innodb_thread_concurrency system variable. It allows you to limit the number of active threads within InnoDB and reduce shared resource contention. However, it comes with a trade-off in that the maximum possible performance is also limited.
To understand the effect, we ran the test two times with two different InnoDB concurrency settings:
- innodb_thread_concurrency=0: with this default value Percona Server 5.7 shows the best results, while MySQL 5.7 shows sharply decreasing performance with more than 64 concurrent clients.
- innodb_thread_concurrency=64: limiting the number of threads inside InnoDB affects throughput for Percona Server slightly (with a small drop from the default setting), but for MySQL that setting change is a huge help. There were no drops in performance after 64 threads, and it’s able to maintain this performance level up to 4k threads (with some variance).
To understand the details better, let’s zoom into the test run with 512 threads:
The charts above show that contentions significantly affect unrestricted concurrency throughput, but affect latency even worse. Limiting concurrency helps to address contentions, but even with this setting Percona Server shows 15-25% better.
Below you can see the contention situation for each of the above runs. The graphs show total accumulated waiting time across all threads per synchronization object (per second). For example, the absolute hottest object across all graphs is the doublewrite mutex in MySQL-5.7.11 (without thread concurrency limitation). It has about 17 seconds of wait time across 512 client threads for each second of run time.
mysql server settings
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | innodb_log_file_size=10G innodb_doublewrite=1 innodb_flush_log_at_trx_commit=1 innodb_buffer_pool_instances=8 innodb_change_buffering=none innodb_adaptive_hash_index=OFF innodb_flush_method=O_DIRECT innodb_flush_neighbors=0 innodb_read_io_threads=8 innodb_write_io_threads=8 innodb_lru_scan_depth=8192 innodb_io_capacity=15000 innodb_io_capacity_max=25000 loose-innodb-page-cleaners=4 table_open_cache_instances=64 table_open_cache=5000 loose-innodb-log_checksum-algorithm=crc32 loose-innodb-checksum-algorithm=strict_crc32 max_connections=50000 skip_name_resolve=ON loose-performance_schema=ON loose-performance-schema-instrument='wait/synch/%=ON', |
Conclusion
If you are already testing 5.7, consider giving Percona Server a spin – especially if your workload is I/O bound. We’ve worked hard on Percona Server 5.7 performance improvements. In upcoming posts, we will delve into the technical details of our LRU flushing and doublewrite buffer changes.
thanks for the post. Do we have similar stats on CPU bound workload.
Thank you for the post.
I translated this article into Japanese for user in Japan.
Translated one is as follows
https://yakst.com/ja/posts/3872
If there is any problem, please get in touch with me.
Thanks.
Great work. Could you indicate the hardware config you used for this test? Also i see innodb_log_file_size=10G. Is there a particular reason you set innodb_log_file_size so high?
Franck,
Big innodb_log_file_size is used to put less pressure from background IO. This size should be fine as it only affects recovery time, and with big buffer pool size and fast IO storage – the recovery will be very fast. I should write more on this,
Perhaps slightly off but how well does MariaDB compare in terms of numbers?