大幅優(yōu)化MySQL查詢性能的奇技淫巧
回顧 MySQL / InnoDB 的改善歷史。你能很容易發(fā)現(xiàn)。在MySQL 5.6穩(wěn)定版本中從來沒有在read-only 這么快的提速，它很容易搞懂，以及在read-only（RO）有著良好的擴(kuò)張性。也很期待它在read+write（RW）上達(dá)到一個(gè)較高水平。（特別是在讀取數(shù)據(jù)是數(shù)據(jù)庫(kù)主要工作的時(shí)候）
然而。我們對(duì)于RO在 MySQL 5.6的表現(xiàn)也十分的高興,在5.7這個(gè)版本中，主要工作集中在 read+write (RW)上, 因?yàn)樵诖髷?shù)據(jù)的處理上還沒能達(dá)到我們的期望。但是RW依賴RO下。能夠再次提高速度。 InnoDB 團(tuán)隊(duì)通過不斷的改進(jìn)，強(qiáng)烈的推進(jìn)優(yōu)化著5.7這個(gè)版本的每秒的性能。
下面就按順序?yàn)榇蠹抑v解
事實(shí)上，在MySQL中只讀工作量控制內(nèi)部鏈接的方式有以下兩種：
- 用單個(gè)表：MDL，trx_sys和lock_sys(InnoDB)
- 多表：trx_sys和lock_sys(主要是InnoDB)
任何很快的單表范圍測(cè)試的工作量主要由于MDL鏈接導(dǎo)致鎖住。而多表將會(huì)由于InnoDB內(nèi)部構(gòu)件限制(不同的表將由不同的MDL鎖保護(hù)，所以這種情況下MDL中的鏈接瓶頸將會(huì)降低)。但是同樣，也要看工作量的大小--一個(gè)比一般多的只讀工作測(cè)量將會(huì)在MySQL5.6中表現(xiàn)的會(huì)更好(如Sysbench OLTP_RO),同時(shí)在工作量少而快的查詢(如Sysbench Point-Selects(用外鍵去取一個(gè)記錄))將會(huì)使所有鏈接變得困難，而且只能在16核-HT中測(cè)量，而在32核中表現(xiàn)很差..但是任何如Point-Select測(cè)試的工作量將在所有MySQL內(nèi)部構(gòu)件一起工作是會(huì)讓你看到可能達(dá)到最大的性能(開始用SQL解析器，終止與取行值)..在你給定的MySQL版本和給定的HW配置下，這也可能達(dá)到最大SQL 查詢/每秒(QPS)率。
在Mysql5.6上我們獲得的最佳結(jié)果是25萬個(gè)查詢每秒，這也是那段時(shí)間Mysql/InnoDb上使用SQL語(yǔ)句查詢得到的最好的結(jié)果了。
當(dāng)然，只有在使用‘只讀事務(wù)'功能才能達(dá)到這么高速度（Mysql5.6上的新功能）；另外，需要使用AUTOCOMMIT=1，否則CPU就會(huì)被輕易地浪費(fèi)在啟動(dòng)事務(wù)、提交事務(wù)上，你會(huì)實(shí)際上損失系統(tǒng)的整體性能。
因此，在Mysql5.7上介紹的第一個(gè)改進(jìn)是‘只讀事務(wù)的自動(dòng)發(fā)現(xiàn)'（實(shí)際上每個(gè)InnoDb事務(wù)都被認(rèn)為是只讀的直到有一個(gè)DML聲明在此之外）功能---，這很大程度上簡(jiǎn)化了只讀事務(wù)功能，節(jié)省了用戶和開發(fā)者的時(shí)間，他們不用再去管理是否采用只讀事務(wù)功能。但是，使用這個(gè)功能你仍然不能達(dá)到Mysql潛在的最佳每秒查詢率，因?yàn)镃PU時(shí)間還是浪費(fèi)在事務(wù)的開啟、結(jié)束狀態(tài)處理過程當(dāng)中。
同時(shí)，Percona用不同的的方案來解決“事務(wù)列表”管理(TRX-列表)及在InnoDB中trx_sys互斥鏈接慢的問題。Percona的解決方案在用事務(wù)處理Point-Selects高負(fù)載時(shí)能表現(xiàn)良好，但MySQL5.7表現(xiàn)一般(但我不會(huì)公布5.7的結(jié)果，因?yàn)樗拇a不公開)...所以，至少我現(xiàn)在可以做一些比較：
觀察結(jié)果:
- 在MySQL5.6，Percona 5.5和MySQL5.7中的8個(gè)表中用同樣的Roint-Select-TRX只讀測(cè)試(用事務(wù))(2013.5月的結(jié)果)
- 同時(shí)你也可以看到，在同樣的16核-HT配置下我們離峰值25萬/s的結(jié)果還很遠(yuǎn)。
- MySQL5.6在trx_sys互斥訪問中延長(zhǎng)了鏈接時(shí)間，而且自從64個(gè)用戶后每秒的請(qǐng)求數(shù)將減少。
- Percona5.5能維持很長(zhǎng)的時(shí)間的負(fù)載，每秒請(qǐng)求在512個(gè)用戶時(shí)才開始減少
- 當(dāng)MySQL5.7已經(jīng)保持一段時(shí)間時(shí)，每秒請(qǐng)求依然沒有減少(對(duì)于更多用戶并發(fā)的情況你在這幅圖里是看不到的)...
然而，很明顯，如果用MySQL想要得到最大的潛在每秒查詢速率，事務(wù)應(yīng)當(dāng)避免。
讓我們來看一看這是2013年5月我們的每秒最大查詢速率。
在同一點(diǎn)八張表進(jìn)行測(cè)試，但是沒有使用MySQL5.6的事物：
觀察：
- 上面的測(cè)試是保持MySQL5.6始終執(zhí)行在16核上，然后是16芯-HT,32核，32芯-HT.
- 正如你所看到的，最大的每秒查詢速率比預(yù)期的還要大 -—— 在MySQL上是每秒27.5萬
- 最大的結(jié)果已經(jīng)達(dá)到16芯-HT.
- 然而在32核上的結(jié)果并沒有16芯-HT上的好（由于競(jìng)爭(zhēng)中斷，在相同內(nèi)核中，具有2CPU線程的配置能夠更好的管理線程競(jìng)爭(zhēng)——所以真正的并發(fā)性仍保存在16線程，而不是32核上）
而在MySQL5.7上做同樣的測(cè)試卻看起來大有不同，因?yàn)樵?.7中l(wèi)ock_sys互斥鏈接的時(shí)間段已經(jīng)很低了，同時(shí)trx_sys互斥相關(guān)代碼也得到第一次變化的情形：
觀察結(jié)果：
- 首先你可以看到5.7在同樣的16核-HT配置下的性能已經(jīng)比5.6的要好
- 之后，在32核配置下沒有明顯的增強(qiáng)！
- 在32核-HT配置下達(dá)到了35萬/秒的最大請(qǐng)求！
- 從上面特殊(具有攻擊性)只讀負(fù)載測(cè)試的情況下可以容易看出我們?cè)?2核中得到的結(jié)果要比16的好，同時(shí)我們還沒有啟動(dòng)超線程(在32核-HT)...牛吧！；-）
從另一方面來講，仍然有改進(jìn)的空間這點(diǎn)還是很清晰的。有關(guān)trx_sys的爭(zhēng)用仍然在持續(xù)。我們沒有充分的使用CPU的能力來做有用的工作（仍然有許多CPU周期用在鎖的輪轉(zhuǎn)）...不過現(xiàn)在的結(jié)果比以前好多了，并且比5.6好很多，因此沒有理由繼續(xù)挖掘來提高這方面的性能，我們主要集中在我們?cè)?jīng)花費(fèi)了巨大的空間的讀寫負(fù)載的性能提高上。
到了5月底，也就是我們的性能會(huì)議期間，Sunny給try_sys互斥爭(zhēng)用增加了幾個(gè)新的更改，從那以后最大的每秒可進(jìn)行的查詢（QPS）可達(dá)到375K！這是不是對(duì)5.7進(jìn)行了足夠的性能提高，對(duì)嗎？;-)
同時(shí)，我們繼續(xù)與建議用其他方式管理TRX列表的Percona團(tuán)隊(duì)交換了意見，他們的方案看起來非常有趣，不過在5.5上，這樣的代碼卻不能展示出更高的每秒可進(jìn)行的查詢數(shù)（QPS），而且在5.6上的這樣代碼（曾經(jīng)測(cè)試過Percona Server 5.6）最大的每秒可進(jìn)行的查詢數(shù)（QPS）也不會(huì)比在MySQL 5.6上大。然而，討論涉及到一個(gè)有趣的觀點(diǎn)：如果同時(shí)有一些讀寫負(fù)載在運(yùn)行的話，它對(duì)只讀性能有什么影響呢？...而且，即使在同樣的測(cè)試條件下MySQL 5.7代碼仍然運(yùn)行的要好一些，效果是非常明顯的（你可以在這兒查看我的分析，然而，再次說明一下，這段時(shí)間內(nèi)我不能展示５.7上的結(jié)果，因?yàn)樗拇a還沒有對(duì)大眾公布－也許會(huì)在以后的一篇文章中給出）..
由于這兒同時(shí)對(duì)任何純粹的讀寫負(fù)載也有影響，因此有足夠的動(dòng)機(jī)以Sunnys很長(zhǎng)時(shí)間所期待的那樣重新寫整個(gè)TRX列表相關(guān)的代碼，然而，這種經(jīng)歷簡(jiǎn)直讓人癡迷！
;-)) 日復(fù)一日，我們很高興的看到我們的每秒可進(jìn)行的查詢圖逐漸變高，直到在同一個(gè)32核的超線程服務(wù)器上達(dá)到了每秒可進(jìn)行的查詢440K!
5.7開發(fā)里程碑發(fā)布2上進(jìn)行的Select 8個(gè)表所得到的結(jié)果數(shù)：
不需要說明..;-))
然而，有一個(gè)小小的令人奇怪的地方－我們?cè)噲D與Sunny通過不同的工具分析所有瓶頸和代碼更改所帶來的影響。而且在某些測(cè)試?yán)铮钗页泽@的是Sunny觀察到比我更高的每秒可進(jìn)行的查詢數(shù)..這個(gè)“奇異之處”與下面因素相關(guān)：
- 在高負(fù)載下，現(xiàn)在的５.7代碼都運(yùn)行在接近硬件極限（主要是CPU)的位置，因此每條指令都非常重要！
- 如果使用的Unix套接字或者IP端口，那么區(qū)分就會(huì)非常明顯！
- Sysbench自身使用了30%的CPU時(shí)間，不過同樣的測(cè)試負(fù)載使用的是（具有更短的代碼路徑的）老版本的Sysbench的話，它將只使用20%CPU，剩余的10%用在MySQL服務(wù)器上。
- 因此，同樣測(cè)試負(fù)載的情況下，使用Unix套接字而不是IP 端口，并且使用Sysbench-0.4.8替代Sysbench-0.4.13的話，我們將得到每秒可進(jìn)行的查詢數(shù)超過500K!-很容易，不是嗎？;-))
讓我們來比較“之前”和“之后”的差異
觀察結(jié)果：
- 通過Sysbench降低了CPU的使用率。
- 在MySQL服務(wù)器上具有更高的CPU可用性。
- 我們實(shí)現(xiàn)了50萬每秒查詢。
還有什么呢？
我可能只提到：kudos Sunny和整個(gè)MySQL的開發(fā)團(tuán)隊(duì)；
讓我們看一下現(xiàn)在選擇8張表工作負(fù)載的情況下的最大每秒查詢。
- 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
每個(gè)引擎都在以下配置下進(jìn)行測(cè)試：
- CPU taskset: 8核-HT,16核，16核-HT,32核，32核-HT
- 并發(fā)會(huì)話數(shù)：8，16，32 ... 1024
- InnoDB自旋等待延時(shí)：6，96
最好的結(jié)果是來自任意兩個(gè)特定的組合間的比較。通過對(duì)數(shù)據(jù)庫(kù)引擎的比較，我得到了下面的一個(gè)圖表，這個(gè)圖表我在以前的文章中已經(jīng)提到過了。
面是一些評(píng)論：
- 對(duì)Mysql5.7的巨大差距結(jié)果不需要做過多的評(píng)論，因?yàn)檫@是很明顯的。
- 那么，有趣的是基于MySQL5.5的代碼庫(kù)引擎沒有任何的接近MySQL5.6的結(jié)果。
- 這已經(jīng)證實(shí)了在使用MySQL5.6的代碼庫(kù)引擎之后，Percona Server達(dá)到了MySQL5.6的水平，然而MariaDB-10仍然還在探索的路上。
- 因此，毫無疑問，MySQL5.6是代碼的基石!
- MySQL5.7是在MySQL5.6基礎(chǔ)上的再一次優(yōu)化擴(kuò)展。
具有什么樣的擴(kuò)展性呢？
答案是簡(jiǎn)單的：MySQL5.7是唯一在此基礎(chǔ)上進(jìn)行擴(kuò)展的。
如果使用ip端口和一個(gè)重量級(jí)的Sysbench-0.4.13，會(huì)得到如下的結(jié)果：
QPS只是稍微的略低一點(diǎn)，但是總體的趨勢(shì)是完全一樣的。
可擴(kuò)展性也是非常的相似：
注意：對(duì)一個(gè)單表綁定過多的工作負(fù)載是不好的：
- 減少InnoDB間的爭(zhēng)論使得其他的爭(zhēng)論更加的明顯。
- 當(dāng)負(fù)載是綁定在一張單表上時(shí)候，MDL的爭(zhēng)論將變得更加主導(dǎo)。
- 這是預(yù)期希望的，我們?cè)谙乱粋€(gè)DMRS上將保持不變。
還有很多挑戰(zhàn)擺在我們面前;-)
作為參考，我上述測(cè)試的硬件配置信息如下：
- Server : 32cores-HT (bi-thread) Intel 2300Mhz, 128GB RAM
- OS : Oracle Linux 6.2
- FS : 啟用"noatime,nodiratime,nobarrier"掛載的EXT4
my.conf:
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的二進(jìn)制版本在這里：
- Sysbench-0.4.13-lux86
- Sysbench-0.4.8-lux86
使用UNIX socket來運(yùn)行Point-Selects測(cè)試的Sysbench命令如下（在parallel中啟動(dòng)8個(gè)進(jìn)程）：
--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端口來運(yùn)行Point-Selects測(cè)試的Sysbench命令如下（在parallel中啟動(dòng)8個(gè)進(jìn)程）：
--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 &
相關(guān)文章
這篇文章主要介紹了MySQL 查詢的排序、分頁(yè)相關(guān)的相關(guān)知識(shí)，幫助大家更好的理解和使用數(shù)據(jù)庫(kù)，感興趣的朋友可以了解下2020-11-11
本文文章主要介紹了淺談mysql join底層原理，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2021-08-08
在MySQL中使用mysqlbinlog flashback的簡(jiǎn)單教程
這篇文章主要介紹了在MySQL中使用mysqlbinlog flashback的簡(jiǎn)單教程,可以很方便地恢復(fù)數(shù)據(jù),作者還列出了使用時(shí)一些需要注意的地方,需要的朋友可以參考下2015-05-05
MySQL數(shù)據(jù)庫(kù)連接異常匯總（值得收藏）
這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)連接異常匯總，幫助大家更好的理解和學(xué)習(xí)mysql，感興趣的朋友可以了解下2020-08-08
盡管索引可以顯著提高數(shù)據(jù)庫(kù)的查詢性能，但在某些情況下，MySQL可能不會(huì)使用索引，本文就來介紹一下MySQL在哪些情況下不使用索引，具有一定的參考價(jià)值，感興趣的可以了解一下2024-01-01
MySQL慢查詢優(yōu)化之慢查詢?nèi)罩痉治龅膶?shí)例教程
這篇文章主要介紹了MySQL慢查詢?nèi)罩痉治龅膶?shí)例教程,通過設(shè)置參數(shù)從慢查詢?nèi)罩鹃_始分析性能問題的原因,需要的朋友可以參考下2015-11-11
mysql 搜尋附近N公里內(nèi)數(shù)據(jù)的簡(jiǎn)單實(shí)例
下面小編就為大家?guī)硪黄猰ysql 搜尋附近N公里內(nèi)數(shù)據(jù)的簡(jiǎn)單實(shí)例。小編覺得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧2017-04-04
mysql中影響數(shù)據(jù)庫(kù)性能的因素講解
在本篇文章中我們給大家講述了mysql中影響性能的因素以及相關(guān)知識(shí)點(diǎn)內(nèi)容，有興趣的朋友參考下。2018-09-09
在SQL中,SUM函數(shù)是用于計(jì)算指定字段的總和的聚合函數(shù),這篇文章將給大家詳細(xì)介紹了SUM函數(shù)在SQL中的值處理原則,文中有詳細(xì)的代碼示例供大家參考,具有一定的參考價(jià)值,需要的朋友可以參考下2023-12-12
在同一Linux下安裝兩個(gè)版本的MySQL的流程步驟
打工人奉旨制作數(shù)據(jù)庫(kù)服務(wù)的虛擬機(jī)模板,模板中包含各種數(shù)據(jù)庫(kù),其中mysql需要具備5.7及8.0兩個(gè)版本,并保證服務(wù)能正常同時(shí)使用,所以本文給小編介紹了在同一Linux下安裝兩個(gè)版本的MySQL的流程步驟,需要的朋友可以參考下2024-03-03