246
京東網上商城
Percona Server 5.7有哪些性能提升？
In this blog post, we’ll be discussing Percona Server 5.7 performance improvements.
在這篇文章中，我們將討論Percona Server 5.7有哪些性能提升。
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.6發布以來，我們引入了幾個重要的更新，有助於高並發I/O負載場景下的性能瓶頸定位。我們（在性能方麵的）某些研究和提升在目前最好的MySQL版本5.7下被重新實現了。但即使MySQL 5.7在擴展性和性能等方麵都有所提升，我們還是發現了可以增進I/O工作負載性能的一些地方。
Percona Server 5.7.11 currently has two major performance features in this area:
Percona Server 5.7.11 有兩個主要的性能方麵的特性：
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.
多線程LRU刷新，這個特性在Percona Server 5.6就存在了，但效果有限。我們把LRU刷新線程從page cleaner線程中分離出來，現在完全隻做flush list的刷新。除此外還有其他幾個重要的變化，這些都顯著提升了高I/O負載為主時的性能。MySQL 5.7更進一步引入了多個page cleaner線程機製，這將有助於提高flush的並行性。但我們還是認為目前的做法還不夠好 —— 尤其是LRU list刷新方麵。
在我們下一篇介紹Percona Server 5.7性能改進的文章中，我們將講述MT刷新（Multi-threaded）方麵的內容，以及為什麼獨立的MT LRU 刷新特別重要。
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.
並行doublewrite buffer，長期以來，MySQL隻有一個doublewrite buffer來刷新數據頁。所以，即使你有多個線程進行flush，也不能有效地使用他們 —— doublewrite會很快成為瓶頸。我們則在每個buffer pool instance中實現兩個doublewrite buffer，每個分別負責不同類型的頁麵刷新（LRU和flush list）。這完全避免了doublewrite的爭用，無需考慮flush的線程數大小。我們還將doublewrite buffer從係統表空間中分離出來，可以自行配置其路徑。
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:
現在我們看下sysbench OLTP_RW，I/O負載為主場景下的測試結果。下麵是測試中使用的關鍵設置:
數據集大小：100GB
innodb_buffer_pool_size=25GB
innodb_doublewrite=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).
當評估MySQL 5.7 RC時，我們觀察到I/O負載為主場景中的性能下降，而且看起來類似MySQL 5.6的表現。**性能下降的原因是buffer pool沒有可用的空閑頁。**page cleaner 線程不能及時進行完成 LRU flush，而且查詢線程又觸發了單頁刷新。這導致所有flushing structures（尤其是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.
很長時間（Vadim十年前討論過），**InnoDB大多數擴展性問題都有統一的解決方案**：innodb_thread_concurrency 選項。它可以限製InnoDB內部活躍線程的數量並減少共享資源爭用。但是，它的缺陷也很明顯，限製了可以發揮的最大性能。
To understand the effect, we ran the test two times with two different InnoDB concurrency settings:
想了解其效果如何，我們可以在兩種不同的InnoDB並發設置下進行**對比測試：**
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=0，這是默認值，Percona Server 5.7 表現最好，而MySQL 5.7 顯示超過64並發之後性能就急劇下降。
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).
innodb_thread_concurrency=64，限製了InnoDB內部並發數，略微影響Percona Server的吞吐量（和默認設置模式略有下降），但是對於MySQL這個改變有很大幫助。在64個並發之後，性能並沒有下降，而是在達到4k並發時依然能保持這個性能表現（當然會有些波動）。
To understand the details better, let’s zoom into the test run with 512 threads:
為了更好的了解細節，我們用512線程並發查看運行中的細節：
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.
上圖顯示，爭用會嚴重影響不設限製的並發吞吐量，但對響應延遲影響更大。限製並發有助於解決爭用，但即使這樣，**Percona Server 還是有 15% ~ 25%的優勢。**
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 5.7.11的doublewrite mutex（innodb_thread_concurrency=0），每秒並行512線程時共有大概有17s的等待時間。
MySQL server 設置
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',
總結
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.
如果你測試過MySQL 5.7，不妨再測試下Percona Server ——尤其在I/O負載為主的場景。我們在Percona Server 5.7的性能改進上煞費苦心。在後續發布的文章中，我們將深入了解LRU flushing和doublewrite buffer上的一些變化等技術細節。
原文發布時間為：2017-10-27
本文作者：天一閣@知數堂
本文來自雲棲社區合作夥伴“老葉茶館”，了解相關信息可以關注“老葉茶館”微信公眾號
最後更新：2017-10-27 12:03:44