搜尋

首頁 >資料庫 >mysql教程 >使用 MySQL5.7實現每秒50萬的高效能查詢

使用 MySQL5.7實現每秒50萬的高效能查詢

巴扎黑
巴扎黑原創
2017-08-10 10:49:182844瀏覽

[導讀] 本文提供MySql5 7實現每秒50W查詢一文的細節以及基準測試結果，解釋了我早期在Mysql Connect 發表的談話。回顧MySQL   InnoDB 的改善歷史。你能很容易發現。在MySQL 5 6穩定版中從來沒有在read-only這麼

本文提供 MySql5.7實現每秒50W查詢 一文的細節以及基準測試結果，解釋了我早期在Mysql Connect 發表的談話。

回顧 MySQL / InnoDB 的改善歷史。你能很容易發現。在MySQL 5.6穩定版中從來沒有在read-only 這麼快的加速，它很容易搞懂，以及在read-only（RO）有著良好的擴張性。也期待它在read+write（RW）上達到一個較高水準。 （特別是在讀取資料是資料庫主要工作的時候）使用 MySQL5.7實現每秒50萬的高效能查詢


然而。我們對於RO在 MySQL 5.6的表現也十分的高興,在5.7這個版本中，主要工作集中在 read+write (RW)上, 因為在大數據的處理上還沒能達到我們的期望。但是RW依賴RO下。能夠再次提高速度。 InnoDB 團隊透過不斷的改進，強烈的推進優化著5.7這個版本的每秒的效能。

下面就依序為大家講解

事實上，在MySQL中只讀工作量控制內部連結的方式有以下兩種：

  • 用單一表格：MDL，trx_sys和lock_sys(InnoDB)

  • #多表：trx_sys和lock_sys(主要是InnoDB)

#任何很快的單表範圍測試的工作量主要由於MDL連結導致鎖住。而多表將會由於InnoDB內部構件限制(不同的表將由不同的MDL鎖保護，所以這種情況下MDL中的鏈結瓶頸將會降低)。但同樣，也要看工作量的大小--一個比一般多的只讀工作測量將會在MySQL5.6中表現的會更好(如Sysbench OLTP_RO),同時在工作量少而快的查詢(如Sysbench Point-Selects(用外鍵去取一個記錄))將會使所有鏈結變得困難，而且只能在16核-HT中測量，而在32核中表現很差..但是任何如Point-Select測試的工作量將在所有MySQL內部構件一起工作是會讓你看到可能達到最大的性能(開始用SQL解析器，終止與取行值)..在你給定的MySQL版本和給定的HW配置下，這也可能達到最大SQL 查詢/每秒(QPS)率。

在Mysql5.6上我們得到的最佳結果是25萬個查詢每秒，這也是那段時間Mysql/InnoDb上使用SQL語句查詢得到的最好的結果了。

當然，只有在使用'只讀交易'功能才能達到這麼高速度（Mysql5.6上的新功能）；另外，需要使用AUTOCOMMIT=1，否則CPU就會被輕易地浪費在啟動事務、提交事務上，你會實際上損失系統的整體效能。

因此，在Mysql5.7上介紹的第一個改進是'只讀事務的自動發現'（實際上每個InnoDb事務都被認為是只讀的直到有一個DML聲明在此之外）功能---，這很大程度上簡化了只讀事務功能，節省了使用者和開發者的時間，他們不用再去管理是否採用唯讀事務功能。但是，使用這個功能你還是無法達到Mysql潛在的最佳每秒查詢率，因為CPU時間還是浪費在交易的開啟、結束狀態處理過程當中。

同時，Percona用不同的方案來解決「交易清單」管理(TRX-清單)及在InnoDB中trx_sys互斥連結慢的問題。 Percona的解決方案在用事務處理Point-Selects高負載時能表現良好，但MySQL5.7表現一般(但我不會公佈5.7的結果，因為它的程式碼不公開)...所以，至少我現在可以做一些比較：

使用 MySQL5.7實現每秒50萬的高效能查詢

觀察結果:

  • 在MySQL5.6，Percona 5.5和MySQL5.7中的8個表中用同樣的Roint-Select-TRX只讀測試(用事務)(2013.5月的結果)

  • ##同時你也可以看到，在同樣的16核心-HT配置下我們離峰值25萬/s的結果還很遠。

  • MySQL5.6在trx_sys互斥存取中延長了連結時間，而且自從64個使用者後每秒的請求數將會減少。

  • Percona5.5能維持很長的時間的負載，每秒請求在512個使用者時才開始減少

  • 當MySQL5.7已經維持一段時間時，每秒請求依然沒有減少(對於更多用戶並發的情況你在這張圖裡是看不到的)...

然而，很明顯，如果用MySQL想要得到最大的潛在每秒查詢速率，事務就應該避免。

讓我們來看看這是2013年5月我們的每秒最大查詢速率。

在同一點八張表進行測試，但沒有使用MySQL5.6的事物：

使用 MySQL5.7實現每秒50萬的高效能查詢

觀察：

  • 上面的測試是保持MySQL5.6總是執行在16核心上，然後是16核心-HT,32核，32芯-HT.

  • 正如你所看到的，最大的每秒查詢速率比預期的還要大-—— 在MySQL上是每秒27.5萬

  • 最大的結果已經達到16芯-HT.

  • #然而在32核心上的結果並沒有16芯-HT上的好（由於競爭中斷，在相同核心中，具有2CPU線程的配置能夠更好的管理線程競爭——所以真正的並發性仍保存在16線程，而不是32核上）

而在MySQL5.7上做同樣的測試卻看起來大有不同，因為在5.7中lock_sys互斥鏈結的時間段已經很低了，同時trx_sys互斥相關程式碼也得到第一次變化的情形：

使用 MySQL5.7實現每秒50萬的高效能查詢

觀察結果：

  • 首先你可以看到5.7在同樣的16核心-HT配置下的效能已經比5.6的要好

  • 之後，在32核心配置下沒有明顯的增強！

  • 在32核心-HT配置下達到了35萬/秒的最大請求！

  • 從上面特殊(具有攻擊性)只讀負載測試的情況下可以容易看出我們在32核中得到的結果比16的好，同時我們還沒有啟動超線程(在32核心-HT)...牛吧！ ；-）

從另一方面來講，仍有改進的空間這點還是很清晰的。有關trx_sys的爭用仍然在持續。我們沒有充分的使用CPU的能力來做有用的工作（仍然有許多CPU週期用在鎖的輪轉）...不過現在的結果比以前好多了，並且比5.6好很多，因此沒有理由繼續挖掘來提高這方面的效能，我們主要集中在我們曾經花費了巨大的空間的讀寫負載的性能提高。

到了5月底，也就是我們的效能會議期間，Sunny為try_sys互斥爭用增加了幾個新的更改，從那時起最大的每秒可進行的查詢（QPS）可達到375K ！這是不是對5.7進行了足夠的性能提高，對嗎？ ;-)

同時，我們繼續與建議用其他方式管理TRX列表的Percona團隊交換了意見，他們的方案看起來非常有趣，不過在5.5上，這樣的程式碼卻不能展示出更高的每秒可進行的查詢數（QPS），而且在5.6上的這樣程式碼（曾經測試過Percona Server 5.6）最大的每秒可進行的查詢數（QPS）也不會比在MySQL 5.6上大。然而，討論涉及到一個有趣的觀點：如果同時有一些讀寫負載在運行的話，它對只讀性能有什麼影響？ ……而且，即使在同樣的測試條件下MySQL 5.7程式碼仍然運行的要好一些，效果是非常明顯的（你可以在這裡查看我的分析，然而，再次說明一下，這段時間內我不能展示５ .7上的結果，因為它的程式碼還沒有對大眾公佈－也許會在以後的一篇文章中給出）..

由於這兒同時對任何純粹的讀寫負載也有影響，因此有足夠的動機以Sunnys很長時間所期待的那樣重新寫整個TRX列表相關的代碼，然而，這種經歷簡直讓人痴迷！

;-)) 日復一日，我們很高興的看到我們的每秒可進行的查詢圖逐漸變高，直到在同一個32核的超線程伺服器上達到了每秒可進行的查詢440K!

5.7開發里程碑發布2上進行的Select 8個表格所得到的結果數：

使用 MySQL5.7實現每秒50萬的高效能查詢

##不需要說明..;-))

然而，有一個小小的令人奇怪的地方——我們試圖與Sunny透過不同的工具分析所有瓶頸和程式碼變更所帶來的影響。而且在某些測試裡，令我吃驚的是Sunny觀察到比我更高的每秒可進行的查詢數..這個「奇異之處」與下面因素相關：

  • 在高負載下，現在的５.7程式碼都運行在接近硬體極限（主要是CPU)的位置，因此每個指令都非常重要！

  • 如果使用的Unix套接字或IP端口，那麼區分就會非常明顯！

  • Sysbench本身使用了30%的CPU時間，不過同樣的測試負載使用的是（具有更短的程式碼路徑的）舊版的Sysbench的話，它將只使用20 %CPU，剩餘的10%用在MySQL伺服器。

  • 因此，同樣測試負載的情況下，使用Unix套接字而不是IP 端口，並且使用Sysbench-0.4.8替代Sysbench-0.4.13的話，我們將得到每秒可進行的查詢數超過500K!-很容易，不是嗎？ ;-))

讓我們來比較「之前」和「之後」的差異

使用 MySQL5.7實現每秒50萬的高效能查詢

观察结果：

  • 通过Sysbench降低了CPU的使用率。

  • 在MySQL服务器上具有更高的CPU可用性。

  • 我们实现了50万每秒查询。

还有什么呢？

我可能只提到：kudos Sunny和整个MySQL的开发团队；

让我们看一下现在选择8张表工作负载的情况下的最大每秒查询。

  • MySQL-5.7.2 (DMR2)

  • MySQL-5.6.14

  • MySQL-5.5.33

  • Percona Server 5.6.13-rc60.5

  • Percona Server 5.5.33-rel31.1

  • MariaDB-10.0.4

  • MariaDB-5.5.32

每个引擎都在以下配置下进行测试：

  • CPU taskset: 8核-HT,16核，16核-HT,32核，32核-HT

  • 并发会话数：8，16，32 ... 1024

  • InnoDB自旋等待延时：6，96

最好的结果是来自任意两个特定的组合间的比较。通过对数据库引擎的比较，我得到了下面的一个图表，这个图表我在以前的文章中已经提到过了。

使用 MySQL5.7實現每秒50萬的高效能查詢下面是一些评论：

  • 对Mysql5.7的巨大差距结果不需要做过多的评论，因为这是很明显的。

  • 那么，有趣的是基于MySQL5.5的代码库引擎没有任何的接近MySQL5.6的结果。

  • 这已经证实了在使用MySQL5.6的代码库引擎之后，Percona Server达到了MySQL5.6的水平，然而MariaDB-10仍然还在探索的路上。

  • 因此，毫无疑问，MySQL5.6是代码的基石!

  • MySQL5.7是在MySQL5.6基础上的再一次优化扩展。

具有什么样的扩展性呢？

使用 MySQL5.7實現每秒50萬的高效能查詢

答案是简单的：MySQL5.7是唯一在此基础上进行扩展的。

如果使用ip端口和一个重量级的Sysbench-0.4.13，会得到如下的结果：

使用 MySQL5.7實現每秒50萬的高效能查詢

QPS只是稍微的略低一点，但是总体的趋势是完全一样的。

可扩展性也是非常的相似：

使用 MySQL5.7實現每秒50萬的高效能查詢

 

更多的结果将会出来，敬请期待；

注意：对一个单表绑定过多的工作负载是不好的：

  • 减少InnoDB间的争论使得其他的争论更加的明显。

  • 当负载是绑定在一张单表上时候，MDL的争论将变得更加主导。

  • 这是预期希望的，我们在下一个DMRS上将保持不变。

 

还有很多挑战摆在我们面前;-)

作为参考，我上述测试的硬件配置信息如下：

  • Server : 32cores-HT (bi-thread) Intel 2300Mhz, 128GB RAM

  • OS : Oracle Linux 6.2

  • FS : 启用"noatime,nodiratime,nobarrier"挂载的EXT4


my.conf:

max_connections=4000
 key_buffer_size=200M
 low_priority_updates=1
 table_open_cache = 8000
 back_log=1500
 query_cache_type=0
 table_open_cache_instances=16

# files
 innodb_file_per_table
 innodb_log_file_size=1024M
 innodb_log_files_in_group = 3
 innodb_open_files=4000

# buffers
 innodb_buffer_pool_size=32000M
 innodb_buffer_pool_instances=32
 innodb_additional_mem_pool_size=20M
 innodb_log_buffer_size=64M
 join_buffer_size=32K
 sort_buffer_size=32K

# innodb
 innodb_checksums=0
 innodb_doublewrite=0
 innodb_support_xa=0
 innodb_thread_concurrency=0
 innodb_flush_log_at_trx_commit=2
 innodb_max_dirty_pages_pct=50
 innodb_use_native_aio=1
 innodb_stats_persistent = 1
 innodb_spin_wait_delay= 6 / 96

# perf special
 innodb_adaptive_flushing = 1
 innodb_flush_neighbors = 0
 innodb_read_io_threads = 4
 innodb_write_io_threads = 4
 innodb_io_capacity = 4000
 innodb_purge_threads=1
 innodb_adaptive_hash_index=0

# monitoring
 innodb_monitor_enable = '%'
 performance_schema=OFF

如果你需要的话，Linux Sysbench的二进制版本在这里：

  • Sysbench-0.4.13-lux86

  • Sysbench-0.4.8-lux86

使用UNIX socket来运行Point-Selects测试的Sysbench命令如下（在parallel中启动8个进程）：

LD_PRELOAD=/usr/lib64/libjemalloc.so /BMK/sysbench-0.4.8 --num-threads=$1 --test=oltp --oltp-table-size=10000000 使用 MySQL5.7實現每秒50萬的高效能查詢
        --oltp-dist-type=uniform --oltp-table-name=sbtest_10M_$n 使用 MySQL5.7實現每秒50萬的高效能查詢
        --max-requests=0 --max-time=$2 --mysql-socket=/SSD_raid0/mysql.sock 使用 MySQL5.7實現每秒50萬的高效能查詢
        --mysql-user=dim --mysql-password=dim --mysql-db=sysbench 使用 MySQL5.7實現每秒50萬的高效能查詢
        --mysql-table-engine=INNODB  --db-driver=mysql 使用 MySQL5.7實現每秒50萬的高效能查詢
        --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 使用 MySQL5.7實現每秒50萬的高效能查詢
        --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=on 使用 MySQL5.7實現每秒50萬的高效能查詢
        --oltp-read-only=on run  > /tmp/test_$n.log &

使用IP端口来运行Point-Selects测试的Sysbench命令如下（在parallel中启动8个进程）：

LD_PRELOAD=/usr/lib64/libjemalloc.so /BMK/sysbench-0.4.13 --num-threads=$1 --test=oltp --oltp-table-size=10000000 使用 MySQL5.7實現每秒50萬的高效能查詢
        --oltp-dist-type=uniform --oltp-table-name=sbtest_10M_$n 使用 MySQL5.7實現每秒50萬的高效能查詢
        --max-requests=0 --max-time=$2 --mysql-host=127.0.0.1 --mysql-port=5700 使用 MySQL5.7實現每秒50萬的高效能查詢
        --mysql-user=dim --mysql-password=dim --mysql-db=sysbench 使用 MySQL5.7實現每秒50萬的高效能查詢
        --mysql-table-engine=INNODB  --db-driver=mysql 使用 MySQL5.7實現每秒50萬的高效能查詢
        --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 使用 MySQL5.7實現每秒50萬的高效能查詢
        --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=on 使用 MySQL5.7實現每秒50萬的高效能查詢
        --oltp-read-only=on run  > /tmp/test_$n.log &

以上是使用 MySQL5.7實現每秒50萬的高效能查詢的詳細內容。更多資訊請關注PHP中文網其他相關文章！

陳述：
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn
上一篇：MySQL動態新增、刪除、修改列欄位教學下一篇：MySQL動態新增、刪除、修改列欄位教學

相關文章

看更多