優(yōu)實(shí)戰(zhàn)：從緩沖原理到參數(shù)配置)
干過(guò)幾年MySQL的人多少都遇到過(guò)類似的“靈異事件”同樣的SQL某些實(shí)例秒回某些實(shí)例要等幾百毫秒服務(wù)器明明16G內(nèi)存MySQL一開就跑滿查了下基準(zhǔn)值卻只設(shè)了128M更常見的是數(shù)據(jù)庫(kù)重啟之后頭十分鐘慢得讓人懷疑人生跑一會(huì)兒又恢復(fù)正常了。這些問題的答案基本都指向同一個(gè)核心——Buffer Pool。專注內(nèi)存優(yōu)化的人繞不開這個(gè)東西。Buffer Pool是InnoDB存儲(chǔ)引擎在內(nèi)存里的核心緩沖區(qū)域MySQL讀數(shù)據(jù)、寫數(shù)據(jù)最終都要經(jīng)過(guò)它。Buffer Pool配置得合理不合理直接決定了你的數(shù)據(jù)庫(kù)IO高不高、查詢快不快、重啟后回血慢不慢。這篇文章就圍繞Buffer Pool的緩沖原理和配置展開把它的工作方式、關(guān)鍵參數(shù)、實(shí)操調(diào)整步驟和常見坑一次講清楚。無(wú)論你是剛接觸MySQL的后端開發(fā)還是已經(jīng)在排查線上問題的運(yùn)維/DBA都值得把這篇看完。我不講云里霧里的理論只講實(shí)際調(diào)優(yōu)時(shí)手上真正要用的東西。1. Buffer Pool到底在緩沖什么1.1 磁盤IO是怎么成為“慢”的根源的理解Buffer Pool之前得先搞清楚MySQL的數(shù)據(jù)是怎么存的。InnoDB存儲(chǔ)引擎在磁盤上維護(hù)了一套完整的表空間文件通常是ibd文件數(shù)據(jù)以“頁(yè)”為單位組織默認(rèn)一個(gè)頁(yè)16KB。也就是說(shuō)你要讀一條記錄InnoDB不是直接去文件里定位這一行而是把包含這行的整個(gè)16KB數(shù)據(jù)頁(yè)加載到內(nèi)存里再在內(nèi)存中找目標(biāo)記錄。磁盤讀取速度是什么概念一塊普通SATA固態(tài)的隨機(jī)讀延遲大約在100微秒到200微秒這個(gè)量級(jí)而內(nèi)存訪問延遲是幾十納秒到百納秒兩者相差三到四個(gè)數(shù)量級(jí)。真要每條SQL都去磁盤翻頁(yè)數(shù)據(jù)庫(kù)基本上就廢了。這也是為什么InnoDB必須有一個(gè)內(nèi)存緩沖層把最常訪問的數(shù)據(jù)頁(yè)、索引頁(yè)留在內(nèi)存里。Buffer Pool字面意思就是這一片“駐留熱數(shù)據(jù)的內(nèi)存?zhèn)}庫(kù)”。你可以把Buffer Pool想象成一個(gè)超市的門口陳列區(qū)磁盤就是你后場(chǎng)的超大倉(cāng)庫(kù)。客人請(qǐng)求要買的東西如果能直接從前臺(tái)陳列區(qū)拿到那速度飛快一旦前臺(tái)沒有就得跑去后場(chǎng)翻倉(cāng)庫(kù)搬過(guò)來(lái)再給客人。前臺(tái)越大、擺得越科學(xué)客人等待時(shí)間就越短。Buffer Pool就是這個(gè)“前臺(tái)陳列區(qū)”它的大小和淘汰策略決定了多少請(qǐng)求可以不用跑去“后場(chǎng)倉(cāng)庫(kù)”翻磁盤。1.2 緩沖池里不只有數(shù)據(jù)頁(yè)很多入門教程一提Buffer Pool就說(shuō)“緩存數(shù)據(jù)頁(yè)”這句話不嚴(yán)謹(jǐn)。InnoDB的Buffer Pool里除了聚簇索引頁(yè)和二級(jí)索引頁(yè)之外還承擔(dān)著幾類特別重要的角色。數(shù)據(jù)頁(yè)和索引頁(yè)這是占大頭的內(nèi)容表數(shù)據(jù)和索引的緩存都在這。undo頁(yè)事務(wù)回滾時(shí)需要讀的舊版本數(shù)據(jù)也放在Buffer Pool里。自適應(yīng)哈希索引AHIInnoDB在熱點(diǎn)記錄上自動(dòng)構(gòu)建的內(nèi)存哈希索引用來(lái)加速等值查詢。鎖信息lock infoInnoDB的行鎖管理結(jié)構(gòu)也占用Buffer Pool內(nèi)存空間。數(shù)據(jù)字典信息表結(jié)構(gòu)、列定義等元數(shù)據(jù)緩存。這里有個(gè)常見的認(rèn)知誤區(qū)很多人以為binlog、redo log的緩沖也歸Buffer Pool管。不是的。redo log有自己的內(nèi)存緩沖log bufferbinlog的寫入由復(fù)制線程和binlog cache管理和Buffer Pool是兩套獨(dú)立機(jī)制。調(diào)內(nèi)存時(shí)不要把它們的占用和Buffer Pool混在一起算否則你做容量規(guī)劃時(shí)會(huì)有偏差。1.3 冷熱分離的LRU變體InnoDB為什么偏要“兩段式”Buffer Pool再大也是有限的內(nèi)存放不下所有數(shù)據(jù)頁(yè)那到底該淘汰誰(shuí)、留下誰(shuí)InnoDB沒有用教科書上的標(biāo)準(zhǔn)LRU最近最少使用算法而是做了一套變體把整個(gè)緩沖池的LRU鏈表分成了兩個(gè)區(qū)域young區(qū)熱端和old區(qū)冷端。標(biāo)準(zhǔn)LRU的問題在于某些只讀一次的大操作會(huì)把整個(gè)緩沖池“洗一遍”。舉個(gè)例子你凌晨跑一個(gè)報(bào)表任務(wù)全表掃描了幾千萬(wàn)行這些冷數(shù)據(jù)剛讀進(jìn)來(lái)時(shí)因?yàn)椤皠偙辉L問過(guò)”會(huì)直接占據(jù)LRU最熱的位置把真正高頻訪問的線上熱點(diǎn)數(shù)據(jù)全部擠出去。等第二天業(yè)務(wù)高峰期一到熱點(diǎn)數(shù)據(jù)全不在內(nèi)存里所有查詢都要重新從磁盤撈IO被打滿這叫“緩存污染”。標(biāo)準(zhǔn)LRU基本沒有防御能力。InnoDB的改進(jìn)是新讀入的頁(yè)先放在old區(qū)頭部而不是young區(qū)。默認(rèn)情況下old區(qū)占整個(gè)LRU鏈表的37%由innodb_old_blocks_pct控制。如果這個(gè)頁(yè)在old區(qū)里停留了超過(guò)innodb_old_blocks_time默認(rèn)1000毫秒后仍然被再次訪問才會(huì)被提升到y(tǒng)oung區(qū)如果只是掃描一遍之后就不再訪問它會(huì)在old區(qū)里慢慢被淘汰根本碰不到熱數(shù)據(jù)。這個(gè)設(shè)計(jì)最精妙的地方在于它不要求你手動(dòng)區(qū)分“哪些是掃描數(shù)據(jù)”而是用時(shí)間門檻自動(dòng)隔離。1000毫秒這個(gè)默認(rèn)值是經(jīng)驗(yàn)值對(duì)大多數(shù)OLTP業(yè)務(wù)夠用。但如果你的系統(tǒng)有大量報(bào)表、批量任務(wù)這個(gè)值往往需要調(diào)大。否則不是內(nèi)存不夠而是內(nèi)存里裝的全是“一次性垃圾”。2. 核心參數(shù)逐個(gè)拆解每個(gè)配置項(xiàng)背后都有講究2.1 大頭innodb_buffer_pool_size這是Buffer Pool優(yōu)化里最核心、效益最直接的一個(gè)參數(shù)它決定緩沖池總共分配多少字節(jié)內(nèi)存。5.7和8.0的默認(rèn)值都是128MB說(shuō)實(shí)話在生產(chǎn)環(huán)境里基本等于沒緩存。官方文檔給出的參考區(qū)間是物理內(nèi)存的50%~70%但我建議你把它當(dāng)成“最高上限參考”而不是盲目梭哈的指標(biāo)。合理設(shè)值的基礎(chǔ)是先算賬。以一臺(tái)16G內(nèi)存、專用于MySQL的服務(wù)器為例操作系統(tǒng)本身需要預(yù)留一部分內(nèi)存加上文件緩存等其他開銷至少留2G。MySQL自身的各種內(nèi)部結(jié)構(gòu)還要吃內(nèi)存連接線程棧thread_stack默認(rèn)256KB、排序緩沖sort_buffer_size、join_buffer、臨時(shí)表、binlog cache、性能監(jiān)控?cái)?shù)據(jù)結(jié)構(gòu)等。這些雜七雜八加起來(lái)通常占1G~2G連接數(shù)一多能吃更多。余量再打80%~90%的“安全折扣”防止慢SQL突然把sort buffer之類打爆。按這個(gè)算法16G內(nèi)存的機(jī)器Buffer Pool通常建議設(shè)置在8G~11G之間。如果專門跑MySQL我一般把10G作為起步參考值再結(jié)合命中率調(diào)整而不是直接照抄“70%”公式。這里必須提醒一點(diǎn)Buffer Pool尺寸不是越大越好。調(diào)大Buffer Pool相當(dāng)于給MySQL多劃了內(nèi)存但如果系統(tǒng)物理內(nèi)存本來(lái)就緊湊超過(guò)一定比例后會(huì)觸發(fā)操作系統(tǒng)swapMySQL的響應(yīng)時(shí)間會(huì)呈斷崖式下跌。那種“調(diào)完參數(shù)內(nèi)存直接爆掉機(jī)器卡死只能重啟”的事故十有八九是內(nèi)存預(yù)算沒算清楚。嚴(yán)格來(lái)說(shuō)Buffer Pool永遠(yuǎn)是“夠用就好”不要把物理內(nèi)存全部透支進(jìn)去。2.2 切分instances與chunk_size怎么搭配當(dāng)Buffer Pool變大之后所有操作都去搶一把大鎖顯然不現(xiàn)實(shí)。InnoDB允許你把Buffer Pool切成多個(gè)實(shí)例通過(guò)innodb_buffer_pool_instances控制。每個(gè)實(shí)例有獨(dú)立的LRU鏈表、獨(dú)立的free list、獨(dú)立的內(nèi)存管理結(jié)構(gòu)并發(fā)訪問時(shí)鎖競(jìng)爭(zhēng)會(huì)大幅降低。在MySQL 5.7及以上版本Buffer Pool大小超過(guò)1GB時(shí)instances默認(rèn)是8。比如你設(shè)了10G的Buffer Pool默認(rèn)就有8個(gè)實(shí)例每個(gè)實(shí)例約1.28G。至于該設(shè)多少個(gè)實(shí)例一個(gè)常見參考是每個(gè)實(shí)例保持在1G~2G之間。實(shí)例太多會(huì)帶來(lái)額外的內(nèi)存碎片和管理開銷實(shí)例太少又抵消不了并發(fā)競(jìng)爭(zhēng)。8G~16G的Buffer Pool設(shè)8個(gè)實(shí)例是比較穩(wěn)妥的組合。chunk_size則是“內(nèi)存重分配”的最小粒度默認(rèn)128MB。Buffer Pool在resize時(shí)是以chunk為單位進(jìn)行內(nèi)存申請(qǐng)和釋放的所以總大小、實(shí)例數(shù)、chunk大小三者之間必須滿足嚴(yán)格的倍數(shù)關(guān)系每個(gè)實(shí)例的大小 innodb_buffer_pool_size / innodb_buffer_pool_instances并且每個(gè)實(shí)例大小必須是 innodb_buffer_pool_chunk_size 的整數(shù)倍。舉個(gè)例子Buffer Pool設(shè)10G10240MB8個(gè)實(shí)例每個(gè)實(shí)例1280MB1280MB除以128MB等于10合法。如果設(shè)10G卻配了3個(gè)實(shí)例每個(gè)實(shí)例約3413MB不是128的整數(shù)倍MySQL會(huì)拒絕啟動(dòng)或自動(dòng)向上取整調(diào)整實(shí)際值——線上出過(guò)這種“改完配置MySQL起不來(lái)”的事故。MySQL 8.0開始部分參數(shù)支持動(dòng)態(tài)修改但你仍然要小心限制。實(shí)際線上調(diào)整時(shí)我建議先按chunk_size取整計(jì)算好目標(biāo)值再動(dòng)配置避免在后半夜面對(duì)一個(gè)起不來(lái)的數(shù)據(jù)庫(kù)。2.3 冷熱邊界old_blocks_time和old_blocks_pct冷熱分離的實(shí)現(xiàn)依賴兩個(gè)參數(shù)前面已經(jīng)提到了一個(gè)innodb_old_blocks_time另一個(gè)是innodb_old_blocks_pct。innodb_old_blocks_time數(shù)據(jù)頁(yè)在old區(qū)待多久之后再被訪問才能升級(jí)到y(tǒng)oung區(qū)。單位毫秒默認(rèn)1000。innodb_old_blocks_pctold區(qū)占整個(gè)LRU鏈表的比例默認(rèn)37。對(duì)絕大多數(shù)OLTP業(yè)務(wù)來(lái)說(shuō)默認(rèn)值就夠用。但如果你遇到了“報(bào)表一跑完線上查詢就變慢”的場(chǎng)景大概率就是全表掃描把緩沖池污染了此時(shí)把innodb_old_blocks_time調(diào)到2000甚至5000往往立竿見影。容易忽略的是innodb_old_blocks_pct。默認(rèn)37%的意思是哪怕熱區(qū)很缺空間新頁(yè)最多也只能占用那37%的冷區(qū)不能一進(jìn)來(lái)就侵占熱區(qū)。如果你的業(yè)務(wù)緩存命中率很高、熱數(shù)據(jù)量不大可以考慮把冷區(qū)比例適當(dāng)調(diào)小一點(diǎn)給熱數(shù)據(jù)更多空間。但我一般不建議隨便亂動(dòng)這個(gè)值37%是官方在大量測(cè)試下選出來(lái)的均衡值。動(dòng)這個(gè)參數(shù)前最好先盯一兩個(gè)業(yè)務(wù)周期的命中率曲線確認(rèn)有明確的冷數(shù)據(jù)污染現(xiàn)象再調(diào)。2.4 預(yù)熱與持久化dump和load參數(shù)數(shù)據(jù)庫(kù)重啟后Buffer Pool一片空白你要讀任何熱數(shù)據(jù)都得先從磁盤加載一次。這就是為什么“重啟瞬間性能暴跌”。InnoDB提供了一套內(nèi)存頁(yè)位置持久化機(jī)制innodb_buffer_pool_dump_at_shutdown關(guān)閉時(shí)把Buffer Pool中的頁(yè)id記錄到系統(tǒng)表空間的一個(gè)dump文件里默認(rèn)OFF。innodb_buffer_pool_load_at_startup啟動(dòng)時(shí)加載這個(gè)dump文件按記錄把熱點(diǎn)頁(yè)重新加載回Buffer Pool默認(rèn)OFF。innodb_buffer_pool_dump_pctdump時(shí)只記錄最新訪問的百分之多少的頁(yè)默認(rèn)25。這三個(gè)參數(shù)強(qiáng)烈建議在生產(chǎn)環(huán)境打開。它們的作用不是讓頁(yè)里的數(shù)據(jù)“持久化”數(shù)據(jù)本身已落盤而是把“哪些頁(yè)是熱數(shù)據(jù)”這一信息保存下來(lái)啟動(dòng)時(shí)按圖索驥加載讓服務(wù)快速恢復(fù)最佳狀態(tài)。需要注意dump_pct的默認(rèn)值25%是一個(gè)性能權(quán)衡全量記錄所有頁(yè)的位置啟動(dòng)加載時(shí)間會(huì)很長(zhǎng)只記錄25%最新熱點(diǎn)通常已經(jīng)能覆蓋絕大多數(shù)高頻訪問。如果你的熱數(shù)據(jù)范圍本來(lái)就很大可以考慮把dump_pct調(diào)到50甚至100但要額外承擔(dān)啟動(dòng)變慢的代價(jià)。3. 從原理到落地完整配置實(shí)操3.1 動(dòng)手前先給MySQL做“內(nèi)存體檢”不要一上來(lái)就改參數(shù)先搞清楚當(dāng)前狀態(tài)。三步走第一步看系統(tǒng)物理內(nèi)存。執(zhí)行 free -h 或 cat /proc/meminfo。確認(rèn)這臺(tái)服務(wù)器是不是MySQL專用上面還跑著其他什么服務(wù)剩余可分配內(nèi)存有多少。第二步看當(dāng)前Buffer Pool參數(shù)和狀態(tài)。進(jìn)入MySQL命令行SHOW VARIABLES LIKE innodb_buffer_pool%; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool%;重點(diǎn)關(guān)注兩個(gè)狀態(tài)值Innodb_buffer_pool_read_requests從Buffer Pool讀到的邏輯讀請(qǐng)求數(shù)。Innodb_buffer_pool_reads從磁盤發(fā)起物理讀的次數(shù)。計(jì)算命中率的公式很簡(jiǎn)單命中率 Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests Innodb_buffer_pool_reads) * 100%這個(gè)值長(zhǎng)期低于99%的話說(shuō)明你的Buffer Pool大概率偏小很多請(qǐng)求被迫走磁盤。正常OLTP業(yè)務(wù)穩(wěn)定狀態(tài)下應(yīng)該穩(wěn)定在99%以上才算合理。第三步查看當(dāng)前Buffer Pool運(yùn)行細(xì)節(jié)。執(zhí)行 SHOW ENGINE INNODB STATUS\G 輸出里找 “BUFFER POOL AND MEMORY” 這一段。重點(diǎn)看兩個(gè)數(shù)字Free buffers空閑頁(yè)數(shù)和Modified db pages臟頁(yè)數(shù)。如果Free buffers長(zhǎng)期低于幾百說(shuō)明可用頁(yè)太少加內(nèi)存是有必要的如果Modified db pages長(zhǎng)期很大說(shuō)明刷臟壓力高這時(shí)單靠加Buffer Pool不一定能解決還要看IO能力。3.2 改配置文件與動(dòng)態(tài)調(diào)整的兩種姿勢(shì)MySQL 5.7及以上版本支持在線修改Buffer Pool大小不需要重啟。典型操作SET GLOBAL innodb_buffer_pool_size 10 * 1024 * 1024 * 1024;注意單位是字節(jié)而且這個(gè)值必須滿足前面的倍數(shù)關(guān)系。在線resize的過(guò)程是異步的如果是擴(kuò)容新增的內(nèi)存在后臺(tái)逐步啟用不會(huì)立刻卡住業(yè)務(wù)如果是縮容InnoDB要把多余的頁(yè)刷到磁盤過(guò)程可能持續(xù)較久建議在業(yè)務(wù)低峰期操作否則會(huì)引發(fā)大量磁盤寫。在線改完之后千萬(wàn)記得這只是改了運(yùn)行時(shí)的值。MySQL一旦重啟又會(huì)回到my.cnf里的舊配置。所以如果你想長(zhǎng)期生效必須同步修改配置文件。以Linux上常見的my.cnf路徑/etc/my.cnf或/etc/mysql/my.cnf為例在[mysqld]段下寫入或修改[mysqld] innodb_buffer_pool_size 10G innodb_buffer_pool_instances 8 innodb_buffer_pool_chunk_size 128M innodb_old_blocks_time 1000 innodb_old_blocks_pct 37 innodb_buffer_pool_dump_at_shutdown ON innodb_buffer_pool_load_at_startup ON innodb_buffer_pool_dump_pct 25改完后用 mysql --help 驗(yàn)證配置語(yǔ)法不嚴(yán)謹(jǐn)最靠譜的方式是 reload或重啟后看日志有沒有報(bào)錯(cuò)再檢查一遍參數(shù)是否生效SHOW VARIABLES LIKE innodb_buffer_pool_size; SHOW VARIABLES LIKE innodb_buffer_pool_instances;這里有個(gè)容易踩的點(diǎn)如果你同時(shí)設(shè)置了size、instances、chunk_size三個(gè)參數(shù)三者不滿足倍數(shù)關(guān)系時(shí)MySQL不是簡(jiǎn)單拒絕而是在啟動(dòng)日志里打一個(gè)warning然后自動(dòng)調(diào)整實(shí)際值。經(jīng)驗(yàn)不足的人很容易忽略日志盯著“配置文件里的理想值”做判斷結(jié)果實(shí)際情況和預(yù)期差一大截。改完一定要查實(shí)際生效值。3.3 壓測(cè)與監(jiān)控用數(shù)據(jù)驗(yàn)證優(yōu)化效果參數(shù)改完了如何證明有效最樸素的辦法是把核心業(yè)務(wù)SQL的響應(yīng)時(shí)間對(duì)比一下但受網(wǎng)絡(luò)和并發(fā)影響誤差通常比較大。我推薦做一輪受控壓測(cè)。sysbench是一個(gè)經(jīng)典工具可以快速生成一組表和讀寫負(fù)載。以O(shè)LTP讀寫混合場(chǎng)景為例準(zhǔn)備數(shù)據(jù)的核心命令大致長(zhǎng)這樣sysbench /usr/share/sysbench/oltp_read_write.lua \ --mysql-host127.0.0.1 --mysql-port3306 \ --mysql-userroot --mysql-passwordyour_password \ --mysql-dbtestdb \ --tables8 --table-size1000000 \ --threads16 --time120 \ prepareprepare執(zhí)行完后跑一輪測(cè)試sysbench /usr/share/sysbench/oltp_read_write.lua \ --mysql-host127.0.0.1 --mysql-port3306 \ --mysql-userroot --mysql-passwordyour_password \ --mysql-dbtestdb \ --tables8 --table-size1000000 \ --threads16 --time120 --report-interval10 \ run壓測(cè)中重點(diǎn)觀察兩項(xiàng)QPS每秒事務(wù)數(shù)/查詢數(shù)和延遲分布95% latencies。調(diào)整Buffer Pool前后各跑一輪對(duì)比這些數(shù)字。我自己做過(guò)的案例里一臺(tái)16G內(nèi)存的MySQL服務(wù)器Buffer Pool從默認(rèn)128M調(diào)到8G后QPS大約提升了一倍多95%延遲從幾十毫秒降到個(gè)位數(shù)毫秒直觀得多。比壓測(cè)更重要的是長(zhǎng)期監(jiān)控。建議至少盯三個(gè)指標(biāo)命中率、free buffers數(shù)量、臟頁(yè)比例。警戒線大概是這樣命中率低于99%需要排查內(nèi)存是否過(guò)小或是否存在緩存污染。Free buffers長(zhǎng)期低于100說(shuō)明緩沖池空間緊張。臟頁(yè)占比如果長(zhǎng)時(shí)間高于20%~30%要關(guān)注刷臟線程和磁盤IO能力而不是一味加內(nèi)存。3.4 重啟后快速回溫的經(jīng)驗(yàn)開了dump和load參數(shù)后重啟MySQL有一個(gè)過(guò)程實(shí)例先啟動(dòng)然后后臺(tái)線程讀取dump文件把記錄的頁(yè)逐步加載進(jìn)Buffer Pool。加載期間性能不會(huì)瞬間回滿通常需要幾分鐘到幾十分鐘取決于dump_pct和熱數(shù)據(jù)量。有一點(diǎn)容易被忽略如果你的MySQL是5.7以前的版本沒有dump_pct參數(shù)那全套預(yù)熱機(jī)制的作用范圍是“所有記錄的頁(yè)”。而8.0里你可以把dump_pct調(diào)大讓更多頁(yè)在啟動(dòng)后回到內(nèi)存。但這對(duì)啟動(dòng)耗時(shí)影響很大別拿默認(rèn)25%就覺得預(yù)熱不徹底。具體取舍是如果線上熱點(diǎn)數(shù)據(jù)非常集中25%足夠如果你的業(yè)務(wù)模型是“廣撒網(wǎng)型”訪問可以考慮調(diào)大。我一般會(huì)在每次計(jì)劃性重啟前執(zhí)行一次SET GLOBAL innodb_buffer_pool_dump_at_shutdown ON;然后確認(rèn)mysqld是用正常方式關(guān)閉的不是kill -9這樣dump文件才會(huì)正常生成。強(qiáng)制殺進(jìn)程是沒法生成dump文件的這一點(diǎn)在故障恢復(fù)場(chǎng)景下尤其坑。4. 常見問題與排查技巧實(shí)錄4.1 典型問題速查表我整理了幾條在生產(chǎn)環(huán)境里高頻出現(xiàn)的問題基本都能在Buffer Pool這個(gè)范疇內(nèi)找到原因現(xiàn)象常見原因排查與解決辦法MySQL實(shí)際內(nèi)存占用遠(yuǎn)超innodb_buffer_pool_size沒有預(yù)算連接線程、sort buffer、join buffer、臨時(shí)表等內(nèi)存用performance_schema查內(nèi)存維度給連接數(shù)和各類buffer設(shè)置硬上限命中率長(zhǎng)期在90%左右浮動(dòng)Buffer Pool偏小或數(shù)據(jù)訪問模式分散調(diào)大buffer_pool_size觀察命中率曲線是否回升如果到頂仍不改善考慮冷熱數(shù)據(jù)分層機(jī)器內(nèi)存還有很多但MySQL還是慢實(shí)例鎖競(jìng)爭(zhēng)、chunk配置不合理、或是磁盤本身慢檢查innodb_buffer_pool_instances觀察實(shí)例狀態(tài)分布重啟后開頭十幾分鐘慢到無(wú)法忍受沒開啟dump/load預(yù)熱或熱數(shù)據(jù)太多dump_pct偏低開啟預(yù)熱參數(shù)適當(dāng)調(diào)高dump_pct改完Buffer Pool參數(shù)后MySQL啟動(dòng)失敗size、instance、chunk三者不滿足倍數(shù)關(guān)系檢查error log按倍數(shù)關(guān)系重新計(jì)算配置高峰期磁盤IO突然打滿Buffer Pool里的臟頁(yè)刷盤壓力大關(guān)注Modified db pages和redo log大小必要時(shí)調(diào)整刷臟線程配置但別一上來(lái)就關(guān)雙1這張表里的每一個(gè)問題我大多都實(shí)際碰到過(guò)排查方向基本是一致的先把“參數(shù)設(shè)置成多少”和“實(shí)際生效多少”對(duì)齊再看狀態(tài)值的變化趨勢(shì)最后才是判斷要不要改參數(shù)。順序顛倒很容易被表象帶跑。4.2 我在生產(chǎn)環(huán)境踩過(guò)的三個(gè)坑第一個(gè)坑以為調(diào)大Buffer Pool就等于內(nèi)存優(yōu)化做完。有一年我負(fù)責(zé)的訂單庫(kù)頻繁告警IO高。看參數(shù)當(dāng)時(shí)Buffer Pool才2G理所當(dāng)然調(diào)到8G結(jié)果問題沒有消失只是延遲從“明顯卡頓”變成“偶發(fā)卡頓”。后來(lái)查了performance_schema才發(fā)現(xiàn)應(yīng)用服務(wù)器用的連接池把max_connections設(shè)到了2000光是線程棧和sort buffer就吃了將近3G內(nèi)存加上各類鎖競(jìng)爭(zhēng)CPU也扛不住。這次之后我才養(yǎng)成習(xí)慣調(diào)內(nèi)存參數(shù)先排查連接和各session緩沖再動(dòng)Buffer Pool。內(nèi)存永遠(yuǎn)是一個(gè)整體預(yù)算Buffer Pool只占其中最大的那一項(xiàng)而已。第二個(gè)坑全表掃描污染熱點(diǎn)數(shù)據(jù)。線上庫(kù)同時(shí)承擔(dān)在線交易和后臺(tái)報(bào)表查詢。凌晨一個(gè)統(tǒng)計(jì)任務(wù)會(huì)給一個(gè)千萬(wàn)級(jí)大表做全表掃描跑完之后白天的核心查詢明顯變慢。剛開始我還以為是緩存太小把Buffer Pool從6G一路加到12G機(jī)器內(nèi)存快撐不住了問題照樣存在。后來(lái)才想到是LRU污染把innodb_old_blocks_time調(diào)到5000之后報(bào)表任務(wù)和線上交易基本互不干擾了。那之后我遇到“內(nèi)存很大卻還是慢”的問題第一反應(yīng)不再是加內(nèi)存。第三個(gè)坑在線調(diào)整和配置文件不一致引發(fā)的“幽靈參數(shù)”。有次我在一臺(tái)測(cè)試機(jī)上手滑運(yùn)行里執(zhí)行了SET GLOBAL把Buffer Pool改成20G然后直接reload了配置文件配置里寫的是8G。結(jié)果MySQL起來(lái)之后實(shí)際生效值看起來(lái)還是20G因?yàn)樵诰€修改值在運(yùn)行內(nèi)存里reload只是重新讀配置并沒有恢復(fù)運(yùn)行時(shí)變量。這種情況很容易誤導(dǎo)后面接手排查的人。現(xiàn)在我的習(xí)慣是每改一個(gè)參數(shù)立即執(zhí)行一遍SHOW VARIABLES確認(rèn)生效值如果要回滾也優(yōu)先用SET GLOBAL改回目標(biāo)值而不是單純依賴reload。最后再分享一個(gè)小技巧。每次做內(nèi)存調(diào)整之前我習(xí)慣先記錄一組基線數(shù)據(jù)包括Innodb_buffer_pool_read_requests、Innodb_buffer_pool_reads、free buffers、modified db pages然后調(diào)整后再采樣對(duì)比用數(shù)據(jù)說(shuō)話。這比憑感覺判斷“有沒有變快”靠譜得多。MySQL 8.0里可以查sys庫(kù)的視圖但老版本沒有這些便利寫一行SQL記錄一下也不費(fèi)事。Buffer Pool調(diào)優(yōu)不是一次性動(dòng)作它是跟著業(yè)務(wù)增長(zhǎng)速度持續(xù)微調(diào)的過(guò)程。只要命中率、臟頁(yè)和延遲這組指標(biāo)穩(wěn)定了這塊大內(nèi)存就算真正發(fā)揮了它的價(jià)值。 MySQL Buffer Pool調(diào)優(yōu)實(shí)戰(zhàn)：從緩沖原理到參數(shù)配置
干過(guò)幾年MySQL的人多少都遇到過(guò)類似的“靈異事件”同樣的SQL某些實(shí)例秒回某些實(shí)例要等幾百毫秒服務(wù)器明明16G內(nèi)存MySQL一開就跑滿查了下基準(zhǔn)值卻只設(shè)了128M更常見的是數(shù)據(jù)庫(kù)重啟之后頭十分鐘慢得讓人懷疑人生跑一會(huì)兒又恢復(fù)正常了。這些問題的答案基本都指向同一個(gè)核心——Buffer Pool。專注內(nèi)存優(yōu)化的人繞不開這個(gè)東西。Buffer Pool是InnoDB存儲(chǔ)引擎在內(nèi)存里的核心緩沖區(qū)域MySQL讀數(shù)據(jù)、寫數(shù)據(jù)最終都要經(jīng)過(guò)它。Buffer Pool配置得合理不合理直接決定了你的數(shù)據(jù)庫(kù)IO高不高、查詢快不快、重啟后回血慢不慢。這篇文章就圍繞Buffer Pool的緩沖原理和配置展開把它的工作方式、關(guān)鍵參數(shù)、實(shí)操調(diào)整步驟和常見坑一次講清楚。無(wú)論你是剛接觸MySQL的后端開發(fā)還是已經(jīng)在排查線上問題的運(yùn)維/DBA都值得把這篇看完。我不講云里霧里的理論只講實(shí)際調(diào)優(yōu)時(shí)手上真正要用的東西。1. Buffer Pool到底在緩沖什么1.1 磁盤IO是怎么成為“慢”的根源的理解Buffer Pool之前得先搞清楚MySQL的數(shù)據(jù)是怎么存的。InnoDB存儲(chǔ)引擎在磁盤上維護(hù)了一套完整的表空間文件通常是ibd文件數(shù)據(jù)以“頁(yè)”為單位組織默認(rèn)一個(gè)頁(yè)16KB。也就是說(shuō)你要讀一條記錄InnoDB不是直接去文件里定位這一行而是把包含這行的整個(gè)16KB數(shù)據(jù)頁(yè)加載到內(nèi)存里再在內(nèi)存中找目標(biāo)記錄。磁盤讀取速度是什么概念一塊普通SATA固態(tài)的隨機(jī)讀延遲大約在100微秒到200微秒這個(gè)量級(jí)而內(nèi)存訪問延遲是幾十納秒到百納秒兩者相差三到四個(gè)數(shù)量級(jí)。真要每條SQL都去磁盤翻頁(yè)數(shù)據(jù)庫(kù)基本上就廢了。這也是為什么InnoDB必須有一個(gè)內(nèi)存緩沖層把最常訪問的數(shù)據(jù)頁(yè)、索引頁(yè)留在內(nèi)存里。Buffer Pool字面意思就是這一片“駐留熱數(shù)據(jù)的內(nèi)存?zhèn)}庫(kù)”。你可以把Buffer Pool想象成一個(gè)超市的門口陳列區(qū)磁盤就是你后場(chǎng)的超大倉(cāng)庫(kù)。客人請(qǐng)求要買的東西如果能直接從前臺(tái)陳列區(qū)拿到那速度飛快一旦前臺(tái)沒有就得跑去后場(chǎng)翻倉(cāng)庫(kù)搬過(guò)來(lái)再給客人。前臺(tái)越大、擺得越科學(xué)客人等待時(shí)間就越短。Buffer Pool就是這個(gè)“前臺(tái)陳列區(qū)”它的大小和淘汰策略決定了多少請(qǐng)求可以不用跑去“后場(chǎng)倉(cāng)庫(kù)”翻磁盤。1.2 緩沖池里不只有數(shù)據(jù)頁(yè)很多入門教程一提Buffer Pool就說(shuō)“緩存數(shù)據(jù)頁(yè)”這句話不嚴(yán)謹(jǐn)。InnoDB的Buffer Pool里除了聚簇索引頁(yè)和二級(jí)索引頁(yè)之外還承擔(dān)著幾類特別重要的角色。數(shù)據(jù)頁(yè)和索引頁(yè)這是占大頭的內(nèi)容表數(shù)據(jù)和索引的緩存都在這。undo頁(yè)事務(wù)回滾時(shí)需要讀的舊版本數(shù)據(jù)也放在Buffer Pool里。自適應(yīng)哈希索引AHIInnoDB在熱點(diǎn)記錄上自動(dòng)構(gòu)建的內(nèi)存哈希索引用來(lái)加速等值查詢。鎖信息lock infoInnoDB的行鎖管理結(jié)構(gòu)也占用Buffer Pool內(nèi)存空間。數(shù)據(jù)字典信息表結(jié)構(gòu)、列定義等元數(shù)據(jù)緩存。這里有個(gè)常見的認(rèn)知誤區(qū)很多人以為binlog、redo log的緩沖也歸Buffer Pool管。不是的。redo log有自己的內(nèi)存緩沖log bufferbinlog的寫入由復(fù)制線程和binlog cache管理和Buffer Pool是兩套獨(dú)立機(jī)制。調(diào)內(nèi)存時(shí)不要把它們的占用和Buffer Pool混在一起算否則你做容量規(guī)劃時(shí)會(huì)有偏差。1.3 冷熱分離的LRU變體InnoDB為什么偏要“兩段式”Buffer Pool再大也是有限的內(nèi)存放不下所有數(shù)據(jù)頁(yè)那到底該淘汰誰(shuí)、留下誰(shuí)InnoDB沒有用教科書上的標(biāo)準(zhǔn)LRU最近最少使用算法而是做了一套變體把整個(gè)緩沖池的LRU鏈表分成了兩個(gè)區(qū)域young區(qū)熱端和old區(qū)冷端。標(biāo)準(zhǔn)LRU的問題在于某些只讀一次的大操作會(huì)把整個(gè)緩沖池“洗一遍”。舉個(gè)例子你凌晨跑一個(gè)報(bào)表任務(wù)全表掃描了幾千萬(wàn)行這些冷數(shù)據(jù)剛讀進(jìn)來(lái)時(shí)因?yàn)椤皠偙辉L問過(guò)”會(huì)直接占據(jù)LRU最熱的位置把真正高頻訪問的線上熱點(diǎn)數(shù)據(jù)全部擠出去。等第二天業(yè)務(wù)高峰期一到熱點(diǎn)數(shù)據(jù)全不在內(nèi)存里所有查詢都要重新從磁盤撈IO被打滿這叫“緩存污染”。標(biāo)準(zhǔn)LRU基本沒有防御能力。InnoDB的改進(jìn)是新讀入的頁(yè)先放在old區(qū)頭部而不是young區(qū)。默認(rèn)情況下old區(qū)占整個(gè)LRU鏈表的37%由innodb_old_blocks_pct控制。如果這個(gè)頁(yè)在old區(qū)里停留了超過(guò)innodb_old_blocks_time默認(rèn)1000毫秒后仍然被再次訪問才會(huì)被提升到y(tǒng)oung區(qū)如果只是掃描一遍之后就不再訪問它會(huì)在old區(qū)里慢慢被淘汰根本碰不到熱數(shù)據(jù)。這個(gè)設(shè)計(jì)最精妙的地方在于它不要求你手動(dòng)區(qū)分“哪些是掃描數(shù)據(jù)”而是用時(shí)間門檻自動(dòng)隔離。1000毫秒這個(gè)默認(rèn)值是經(jīng)驗(yàn)值對(duì)大多數(shù)OLTP業(yè)務(wù)夠用。但如果你的系統(tǒng)有大量報(bào)表、批量任務(wù)這個(gè)值往往需要調(diào)大。否則不是內(nèi)存不夠而是內(nèi)存里裝的全是“一次性垃圾”。2. 核心參數(shù)逐個(gè)拆解每個(gè)配置項(xiàng)背后都有講究2.1 大頭innodb_buffer_pool_size這是Buffer Pool優(yōu)化里最核心、效益最直接的一個(gè)參數(shù)它決定緩沖池總共分配多少字節(jié)內(nèi)存。5.7和8.0的默認(rèn)值都是128MB說(shuō)實(shí)話在生產(chǎn)環(huán)境里基本等于沒緩存。官方文檔給出的參考區(qū)間是物理內(nèi)存的50%~70%但我建議你把它當(dāng)成“最高上限參考”而不是盲目梭哈的指標(biāo)。合理設(shè)值的基礎(chǔ)是先算賬。以一臺(tái)16G內(nèi)存、專用于MySQL的服務(wù)器為例操作系統(tǒng)本身需要預(yù)留一部分內(nèi)存加上文件緩存等其他開銷至少留2G。MySQL自身的各種內(nèi)部結(jié)構(gòu)還要吃內(nèi)存連接線程棧thread_stack默認(rèn)256KB、排序緩沖sort_buffer_size、join_buffer、臨時(shí)表、binlog cache、性能監(jiān)控?cái)?shù)據(jù)結(jié)構(gòu)等。這些雜七雜八加起來(lái)通常占1G~2G連接數(shù)一多能吃更多。余量再打80%~90%的“安全折扣”防止慢SQL突然把sort buffer之類打爆。按這個(gè)算法16G內(nèi)存的機(jī)器Buffer Pool通常建議設(shè)置在8G~11G之間。如果專門跑MySQL我一般把10G作為起步參考值再結(jié)合命中率調(diào)整而不是直接照抄“70%”公式。這里必須提醒一點(diǎn)Buffer Pool尺寸不是越大越好。調(diào)大Buffer Pool相當(dāng)于給MySQL多劃了內(nèi)存但如果系統(tǒng)物理內(nèi)存本來(lái)就緊湊超過(guò)一定比例后會(huì)觸發(fā)操作系統(tǒng)swapMySQL的響應(yīng)時(shí)間會(huì)呈斷崖式下跌。那種“調(diào)完參數(shù)內(nèi)存直接爆掉機(jī)器卡死只能重啟”的事故十有八九是內(nèi)存預(yù)算沒算清楚。嚴(yán)格來(lái)說(shuō)Buffer Pool永遠(yuǎn)是“夠用就好”不要把物理內(nèi)存全部透支進(jìn)去。2.2 切分instances與chunk_size怎么搭配當(dāng)Buffer Pool變大之后所有操作都去搶一把大鎖顯然不現(xiàn)實(shí)。InnoDB允許你把Buffer Pool切成多個(gè)實(shí)例通過(guò)innodb_buffer_pool_instances控制。每個(gè)實(shí)例有獨(dú)立的LRU鏈表、獨(dú)立的free list、獨(dú)立的內(nèi)存管理結(jié)構(gòu)并發(fā)訪問時(shí)鎖競(jìng)爭(zhēng)會(huì)大幅降低。在MySQL 5.7及以上版本Buffer Pool大小超過(guò)1GB時(shí)instances默認(rèn)是8。比如你設(shè)了10G的Buffer Pool默認(rèn)就有8個(gè)實(shí)例每個(gè)實(shí)例約1.28G。至于該設(shè)多少個(gè)實(shí)例一個(gè)常見參考是每個(gè)實(shí)例保持在1G~2G之間。實(shí)例太多會(huì)帶來(lái)額外的內(nèi)存碎片和管理開銷實(shí)例太少又抵消不了并發(fā)競(jìng)爭(zhēng)。8G~16G的Buffer Pool設(shè)8個(gè)實(shí)例是比較穩(wěn)妥的組合。chunk_size則是“內(nèi)存重分配”的最小粒度默認(rèn)128MB。Buffer Pool在resize時(shí)是以chunk為單位進(jìn)行內(nèi)存申請(qǐng)和釋放的所以總大小、實(shí)例數(shù)、chunk大小三者之間必須滿足嚴(yán)格的倍數(shù)關(guān)系每個(gè)實(shí)例的大小 innodb_buffer_pool_size / innodb_buffer_pool_instances并且每個(gè)實(shí)例大小必須是 innodb_buffer_pool_chunk_size 的整數(shù)倍。舉個(gè)例子Buffer Pool設(shè)10G10240MB8個(gè)實(shí)例每個(gè)實(shí)例1280MB1280MB除以128MB等于10合法。如果設(shè)10G卻配了3個(gè)實(shí)例每個(gè)實(shí)例約3413MB不是128的整數(shù)倍MySQL會(huì)拒絕啟動(dòng)或自動(dòng)向上取整調(diào)整實(shí)際值——線上出過(guò)這種“改完配置MySQL起不來(lái)”的事故。MySQL 8.0開始部分參數(shù)支持動(dòng)態(tài)修改但你仍然要小心限制。實(shí)際線上調(diào)整時(shí)我建議先按chunk_size取整計(jì)算好目標(biāo)值再動(dòng)配置避免在后半夜面對(duì)一個(gè)起不來(lái)的數(shù)據(jù)庫(kù)。2.3 冷熱邊界old_blocks_time和old_blocks_pct冷熱分離的實(shí)現(xiàn)依賴兩個(gè)參數(shù)前面已經(jīng)提到了一個(gè)innodb_old_blocks_time另一個(gè)是innodb_old_blocks_pct。innodb_old_blocks_time數(shù)據(jù)頁(yè)在old區(qū)待多久之后再被訪問才能升級(jí)到y(tǒng)oung區(qū)。單位毫秒默認(rèn)1000。innodb_old_blocks_pctold區(qū)占整個(gè)LRU鏈表的比例默認(rèn)37。對(duì)絕大多數(shù)OLTP業(yè)務(wù)來(lái)說(shuō)默認(rèn)值就夠用。但如果你遇到了“報(bào)表一跑完線上查詢就變慢”的場(chǎng)景大概率就是全表掃描把緩沖池污染了此時(shí)把innodb_old_blocks_time調(diào)到2000甚至5000往往立竿見影。容易忽略的是innodb_old_blocks_pct。默認(rèn)37%的意思是哪怕熱區(qū)很缺空間新頁(yè)最多也只能占用那37%的冷區(qū)不能一進(jìn)來(lái)就侵占熱區(qū)。如果你的業(yè)務(wù)緩存命中率很高、熱數(shù)據(jù)量不大可以考慮把冷區(qū)比例適當(dāng)調(diào)小一點(diǎn)給熱數(shù)據(jù)更多空間。但我一般不建議隨便亂動(dòng)這個(gè)值37%是官方在大量測(cè)試下選出來(lái)的均衡值。動(dòng)這個(gè)參數(shù)前最好先盯一兩個(gè)業(yè)務(wù)周期的命中率曲線確認(rèn)有明確的冷數(shù)據(jù)污染現(xiàn)象再調(diào)。2.4 預(yù)熱與持久化dump和load參數(shù)數(shù)據(jù)庫(kù)重啟后Buffer Pool一片空白你要讀任何熱數(shù)據(jù)都得先從磁盤加載一次。這就是為什么“重啟瞬間性能暴跌”。InnoDB提供了一套內(nèi)存頁(yè)位置持久化機(jī)制innodb_buffer_pool_dump_at_shutdown關(guān)閉時(shí)把Buffer Pool中的頁(yè)id記錄到系統(tǒng)表空間的一個(gè)dump文件里默認(rèn)OFF。innodb_buffer_pool_load_at_startup啟動(dòng)時(shí)加載這個(gè)dump文件按記錄把熱點(diǎn)頁(yè)重新加載回Buffer Pool默認(rèn)OFF。innodb_buffer_pool_dump_pctdump時(shí)只記錄最新訪問的百分之多少的頁(yè)默認(rèn)25。這三個(gè)參數(shù)強(qiáng)烈建議在生產(chǎn)環(huán)境打開。它們的作用不是讓頁(yè)里的數(shù)據(jù)“持久化”數(shù)據(jù)本身已落盤而是把“哪些頁(yè)是熱數(shù)據(jù)”這一信息保存下來(lái)啟動(dòng)時(shí)按圖索驥加載讓服務(wù)快速恢復(fù)最佳狀態(tài)。需要注意dump_pct的默認(rèn)值25%是一個(gè)性能權(quán)衡全量記錄所有頁(yè)的位置啟動(dòng)加載時(shí)間會(huì)很長(zhǎng)只記錄25%最新熱點(diǎn)通常已經(jīng)能覆蓋絕大多數(shù)高頻訪問。如果你的熱數(shù)據(jù)范圍本來(lái)就很大可以考慮把dump_pct調(diào)到50甚至100但要額外承擔(dān)啟動(dòng)變慢的代價(jià)。3. 從原理到落地完整配置實(shí)操3.1 動(dòng)手前先給MySQL做“內(nèi)存體檢”不要一上來(lái)就改參數(shù)先搞清楚當(dāng)前狀態(tài)。三步走第一步看系統(tǒng)物理內(nèi)存。執(zhí)行 free -h 或 cat /proc/meminfo。確認(rèn)這臺(tái)服務(wù)器是不是MySQL專用上面還跑著其他什么服務(wù)剩余可分配內(nèi)存有多少。第二步看當(dāng)前Buffer Pool參數(shù)和狀態(tài)。進(jìn)入MySQL命令行SHOW VARIABLES LIKE innodb_buffer_pool%; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool%;重點(diǎn)關(guān)注兩個(gè)狀態(tài)值Innodb_buffer_pool_read_requests從Buffer Pool讀到的邏輯讀請(qǐng)求數(shù)。Innodb_buffer_pool_reads從磁盤發(fā)起物理讀的次數(shù)。計(jì)算命中率的公式很簡(jiǎn)單命中率 Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests Innodb_buffer_pool_reads) * 100%這個(gè)值長(zhǎng)期低于99%的話說(shuō)明你的Buffer Pool大概率偏小很多請(qǐng)求被迫走磁盤。正常OLTP業(yè)務(wù)穩(wěn)定狀態(tài)下應(yīng)該穩(wěn)定在99%以上才算合理。第三步查看當(dāng)前Buffer Pool運(yùn)行細(xì)節(jié)。執(zhí)行 SHOW ENGINE INNODB STATUS\G 輸出里找 “BUFFER POOL AND MEMORY” 這一段。重點(diǎn)看兩個(gè)數(shù)字Free buffers空閑頁(yè)數(shù)和Modified db pages臟頁(yè)數(shù)。如果Free buffers長(zhǎng)期低于幾百說(shuō)明可用頁(yè)太少加內(nèi)存是有必要的如果Modified db pages長(zhǎng)期很大說(shuō)明刷臟壓力高這時(shí)單靠加Buffer Pool不一定能解決還要看IO能力。3.2 改配置文件與動(dòng)態(tài)調(diào)整的兩種姿勢(shì)MySQL 5.7及以上版本支持在線修改Buffer Pool大小不需要重啟。典型操作SET GLOBAL innodb_buffer_pool_size 10 * 1024 * 1024 * 1024;注意單位是字節(jié)而且這個(gè)值必須滿足前面的倍數(shù)關(guān)系。在線resize的過(guò)程是異步的如果是擴(kuò)容新增的內(nèi)存在后臺(tái)逐步啟用不會(huì)立刻卡住業(yè)務(wù)如果是縮容InnoDB要把多余的頁(yè)刷到磁盤過(guò)程可能持續(xù)較久建議在業(yè)務(wù)低峰期操作否則會(huì)引發(fā)大量磁盤寫。在線改完之后千萬(wàn)記得這只是改了運(yùn)行時(shí)的值。MySQL一旦重啟又會(huì)回到my.cnf里的舊配置。所以如果你想長(zhǎng)期生效必須同步修改配置文件。以Linux上常見的my.cnf路徑/etc/my.cnf或/etc/mysql/my.cnf為例在[mysqld]段下寫入或修改[mysqld] innodb_buffer_pool_size 10G innodb_buffer_pool_instances 8 innodb_buffer_pool_chunk_size 128M innodb_old_blocks_time 1000 innodb_old_blocks_pct 37 innodb_buffer_pool_dump_at_shutdown ON innodb_buffer_pool_load_at_startup ON innodb_buffer_pool_dump_pct 25改完后用 mysql --help 驗(yàn)證配置語(yǔ)法不嚴(yán)謹(jǐn)最靠譜的方式是 reload或重啟后看日志有沒有報(bào)錯(cuò)再檢查一遍參數(shù)是否生效SHOW VARIABLES LIKE innodb_buffer_pool_size; SHOW VARIABLES LIKE innodb_buffer_pool_instances;這里有個(gè)容易踩的點(diǎn)如果你同時(shí)設(shè)置了size、instances、chunk_size三個(gè)參數(shù)三者不滿足倍數(shù)關(guān)系時(shí)MySQL不是簡(jiǎn)單拒絕而是在啟動(dòng)日志里打一個(gè)warning然后自動(dòng)調(diào)整實(shí)際值。經(jīng)驗(yàn)不足的人很容易忽略日志盯著“配置文件里的理想值”做判斷結(jié)果實(shí)際情況和預(yù)期差一大截。改完一定要查實(shí)際生效值。3.3 壓測(cè)與監(jiān)控用數(shù)據(jù)驗(yàn)證優(yōu)化效果參數(shù)改完了如何證明有效最樸素的辦法是把核心業(yè)務(wù)SQL的響應(yīng)時(shí)間對(duì)比一下但受網(wǎng)絡(luò)和并發(fā)影響誤差通常比較大。我推薦做一輪受控壓測(cè)。sysbench是一個(gè)經(jīng)典工具可以快速生成一組表和讀寫負(fù)載。以O(shè)LTP讀寫混合場(chǎng)景為例準(zhǔn)備數(shù)據(jù)的核心命令大致長(zhǎng)這樣sysbench /usr/share/sysbench/oltp_read_write.lua \ --mysql-host127.0.0.1 --mysql-port3306 \ --mysql-userroot --mysql-passwordyour_password \ --mysql-dbtestdb \ --tables8 --table-size1000000 \ --threads16 --time120 \ prepareprepare執(zhí)行完后跑一輪測(cè)試sysbench /usr/share/sysbench/oltp_read_write.lua \ --mysql-host127.0.0.1 --mysql-port3306 \ --mysql-userroot --mysql-passwordyour_password \ --mysql-dbtestdb \ --tables8 --table-size1000000 \ --threads16 --time120 --report-interval10 \ run壓測(cè)中重點(diǎn)觀察兩項(xiàng)QPS每秒事務(wù)數(shù)/查詢數(shù)和延遲分布95% latencies。調(diào)整Buffer Pool前后各跑一輪對(duì)比這些數(shù)字。我自己做過(guò)的案例里一臺(tái)16G內(nèi)存的MySQL服務(wù)器Buffer Pool從默認(rèn)128M調(diào)到8G后QPS大約提升了一倍多95%延遲從幾十毫秒降到個(gè)位數(shù)毫秒直觀得多。比壓測(cè)更重要的是長(zhǎng)期監(jiān)控。建議至少盯三個(gè)指標(biāo)命中率、free buffers數(shù)量、臟頁(yè)比例。警戒線大概是這樣命中率低于99%需要排查內(nèi)存是否過(guò)小或是否存在緩存污染。Free buffers長(zhǎng)期低于100說(shuō)明緩沖池空間緊張。臟頁(yè)占比如果長(zhǎng)時(shí)間高于20%~30%要關(guān)注刷臟線程和磁盤IO能力而不是一味加內(nèi)存。3.4 重啟后快速回溫的經(jīng)驗(yàn)開了dump和load參數(shù)后重啟MySQL有一個(gè)過(guò)程實(shí)例先啟動(dòng)然后后臺(tái)線程讀取dump文件把記錄的頁(yè)逐步加載進(jìn)Buffer Pool。加載期間性能不會(huì)瞬間回滿通常需要幾分鐘到幾十分鐘取決于dump_pct和熱數(shù)據(jù)量。有一點(diǎn)容易被忽略如果你的MySQL是5.7以前的版本沒有dump_pct參數(shù)那全套預(yù)熱機(jī)制的作用范圍是“所有記錄的頁(yè)”。而8.0里你可以把dump_pct調(diào)大讓更多頁(yè)在啟動(dòng)后回到內(nèi)存。但這對(duì)啟動(dòng)耗時(shí)影響很大別拿默認(rèn)25%就覺得預(yù)熱不徹底。具體取舍是如果線上熱點(diǎn)數(shù)據(jù)非常集中25%足夠如果你的業(yè)務(wù)模型是“廣撒網(wǎng)型”訪問可以考慮調(diào)大。我一般會(huì)在每次計(jì)劃性重啟前執(zhí)行一次SET GLOBAL innodb_buffer_pool_dump_at_shutdown ON;然后確認(rèn)mysqld是用正常方式關(guān)閉的不是kill -9這樣dump文件才會(huì)正常生成。強(qiáng)制殺進(jìn)程是沒法生成dump文件的這一點(diǎn)在故障恢復(fù)場(chǎng)景下尤其坑。4. 常見問題與排查技巧實(shí)錄4.1 典型問題速查表我整理了幾條在生產(chǎn)環(huán)境里高頻出現(xiàn)的問題基本都能在Buffer Pool這個(gè)范疇內(nèi)找到原因現(xiàn)象常見原因排查與解決辦法MySQL實(shí)際內(nèi)存占用遠(yuǎn)超innodb_buffer_pool_size沒有預(yù)算連接線程、sort buffer、join buffer、臨時(shí)表等內(nèi)存用performance_schema查內(nèi)存維度給連接數(shù)和各類buffer設(shè)置硬上限命中率長(zhǎng)期在90%左右浮動(dòng)Buffer Pool偏小或數(shù)據(jù)訪問模式分散調(diào)大buffer_pool_size觀察命中率曲線是否回升如果到頂仍不改善考慮冷熱數(shù)據(jù)分層機(jī)器內(nèi)存還有很多但MySQL還是慢實(shí)例鎖競(jìng)爭(zhēng)、chunk配置不合理、或是磁盤本身慢檢查innodb_buffer_pool_instances觀察實(shí)例狀態(tài)分布重啟后開頭十幾分鐘慢到無(wú)法忍受沒開啟dump/load預(yù)熱或熱數(shù)據(jù)太多dump_pct偏低開啟預(yù)熱參數(shù)適當(dāng)調(diào)高dump_pct改完Buffer Pool參數(shù)后MySQL啟動(dòng)失敗size、instance、chunk三者不滿足倍數(shù)關(guān)系檢查error log按倍數(shù)關(guān)系重新計(jì)算配置高峰期磁盤IO突然打滿Buffer Pool里的臟頁(yè)刷盤壓力大關(guān)注Modified db pages和redo log大小必要時(shí)調(diào)整刷臟線程配置但別一上來(lái)就關(guān)雙1這張表里的每一個(gè)問題我大多都實(shí)際碰到過(guò)排查方向基本是一致的先把“參數(shù)設(shè)置成多少”和“實(shí)際生效多少”對(duì)齊再看狀態(tài)值的變化趨勢(shì)最后才是判斷要不要改參數(shù)。順序顛倒很容易被表象帶跑。4.2 我在生產(chǎn)環(huán)境踩過(guò)的三個(gè)坑第一個(gè)坑以為調(diào)大Buffer Pool就等于內(nèi)存優(yōu)化做完。有一年我負(fù)責(zé)的訂單庫(kù)頻繁告警IO高。看參數(shù)當(dāng)時(shí)Buffer Pool才2G理所當(dāng)然調(diào)到8G結(jié)果問題沒有消失只是延遲從“明顯卡頓”變成“偶發(fā)卡頓”。后來(lái)查了performance_schema才發(fā)現(xiàn)應(yīng)用服務(wù)器用的連接池把max_connections設(shè)到了2000光是線程棧和sort buffer就吃了將近3G內(nèi)存加上各類鎖競(jìng)爭(zhēng)CPU也扛不住。這次之后我才養(yǎng)成習(xí)慣調(diào)內(nèi)存參數(shù)先排查連接和各session緩沖再動(dòng)Buffer Pool。內(nèi)存永遠(yuǎn)是一個(gè)整體預(yù)算Buffer Pool只占其中最大的那一項(xiàng)而已。第二個(gè)坑全表掃描污染熱點(diǎn)數(shù)據(jù)。線上庫(kù)同時(shí)承擔(dān)在線交易和后臺(tái)報(bào)表查詢。凌晨一個(gè)統(tǒng)計(jì)任務(wù)會(huì)給一個(gè)千萬(wàn)級(jí)大表做全表掃描跑完之后白天的核心查詢明顯變慢。剛開始我還以為是緩存太小把Buffer Pool從6G一路加到12G機(jī)器內(nèi)存快撐不住了問題照樣存在。后來(lái)才想到是LRU污染把innodb_old_blocks_time調(diào)到5000之后報(bào)表任務(wù)和線上交易基本互不干擾了。那之后我遇到“內(nèi)存很大卻還是慢”的問題第一反應(yīng)不再是加內(nèi)存。第三個(gè)坑在線調(diào)整和配置文件不一致引發(fā)的“幽靈參數(shù)”。有次我在一臺(tái)測(cè)試機(jī)上手滑運(yùn)行里執(zhí)行了SET GLOBAL把Buffer Pool改成20G然后直接reload了配置文件配置里寫的是8G。結(jié)果MySQL起來(lái)之后實(shí)際生效值看起來(lái)還是20G因?yàn)樵诰€修改值在運(yùn)行內(nèi)存里reload只是重新讀配置并沒有恢復(fù)運(yùn)行時(shí)變量。這種情況很容易誤導(dǎo)后面接手排查的人。現(xiàn)在我的習(xí)慣是每改一個(gè)參數(shù)立即執(zhí)行一遍SHOW VARIABLES確認(rèn)生效值如果要回滾也優(yōu)先用SET GLOBAL改回目標(biāo)值而不是單純依賴reload。最后再分享一個(gè)小技巧。每次做內(nèi)存調(diào)整之前我習(xí)慣先記錄一組基線數(shù)據(jù)包括Innodb_buffer_pool_read_requests、Innodb_buffer_pool_reads、free buffers、modified db pages然后調(diào)整后再采樣對(duì)比用數(shù)據(jù)說(shuō)話。這比憑感覺判斷“有沒有變快”靠譜得多。MySQL 8.0里可以查sys庫(kù)的視圖但老版本沒有這些便利寫一行SQL記錄一下也不費(fèi)事。Buffer Pool調(diào)優(yōu)不是一次性動(dòng)作它是跟著業(yè)務(wù)增長(zhǎng)速度持續(xù)微調(diào)的過(guò)程。只要命中率、臟頁(yè)和延遲這組指標(biāo)穩(wěn)定了這塊大內(nèi)存就算真正發(fā)揮了它的價(jià)值。