程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL 性能:使用 MySQL 5.7 實現每秒 50 萬查詢

MySQL 性能:使用 MySQL 5.7 實現每秒 50 萬查詢

編輯:MySQL綜合教程

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

回顧 MySQL / InnoDB 的改善歷史。你能很容易發現。在MySQL 5.6穩定版本中從來沒有在read-only 這麼快的提速,它很容易搞懂,以及在read-only(RO)有著良好的擴張性。也很期待它在read+write(RW)上達到一個較高水平。(特別是在讀取數據是數據庫主要工作的時候)\


然而。我們對於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.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.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互斥相關代碼也得到第一次變化的情形:

\

觀察結果:

  • 首先你可以看到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代碼仍然運行的要好一些,效果是非常明顯的(你可以在這兒查看我的分析,然而,再次說明一下,這段時間內我不能展示5.7上的結果,因為它的代碼還沒有對大眾公布-也許會在以後的一篇文章中給出)..

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

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

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

不需要說明..;-))

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

  • 在高負載下,現在的5.7代碼都運行在接近硬件極限(主要是CPU)的位置,因此每條指令都非常重要!
  • 如果使用的Unix套接字或者IP端口,那麼區分就會非常明顯!
  • Sysbench自身使用了30%的CPU時間,不過同樣的測試負載使用的是(具有更短的代碼路徑的)老版本的Sysbench的話,它將只使用20%CPU,剩余的10%用在MySQL服務器上。
  • 因此,同樣測試負載的情況下,使用Unix套接字而不是IP 端口,並且使用Sysbench-0.4.8替代Sysbench-0.4.13的話,我們將得到每秒可進行的查詢數超過500K!-很容易,不是嗎?;-))

讓我們來比較“之前”和“之後”的差異

觀察結果:

  • 通過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的巨大差距結果不需要做過多的評論,因為這是很明顯的。
  • 那麼,有趣的是基於MySQL5.5的代碼庫引擎沒有任何的接近MySQL5.6的結果。
  • 這已經證實了在使用MySQL5.6的代碼庫引擎之後,Percona Server達到了MySQL5.6的水平,然而MariaDB-10仍然還在探索的路上。
  • 因此,毫無疑問,MySQL5.6是代碼的基石!
  • MySQL5.7是在MySQL5.6基礎上的再一次優化擴展。

具有什麼樣的擴展性呢?

答案是簡單的:MySQL5.7是唯一在此基礎上進行擴展的。

如果使用ip端口和一個重量級的Sysbench-0.4.13,會得到如下的結果:

QPS只是稍微的略低一點,但是總體的趨勢是完全一樣的。

可擴展性也是非常的相似:

 

更多的結果將會出來,敬請期待;

注意:對一個單表綁定過多的工作負載是不好的:

  • 減少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 \
        --oltp-dist-type=uniform --oltp-table-name=sbtest_10M_$n \
        --max-requests=0 --max-time=$2 --mysql-socket=/SSD_raid0/mysql.sock \
        --mysql-user=dim --mysql-password=dim --mysql-db=sysbench \
        --mysql-table-engine=INNODB  --db-driver=mysql \
        --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 \
        --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=on \
        --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 \
        --oltp-dist-type=uniform --oltp-table-name=sbtest_10M_$n \
        --max-requests=0 --max-time=$2 --mysql-host=127.0.0.1 --mysql-port=5700 \
        --mysql-user=dim --mysql-password=dim --mysql-db=sysbench \
        --mysql-table-engine=INNODB  --db-driver=mysql \
        --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 \
        --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=on \
        --oltp-read-only=on run  > /tmp/test_$n.log &

願你有所收獲, 
-Dimitri

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved