本文通過筆者經歷的一個真實案例來介紹一個MySQL中的重要引數innodb_buffer_pool_size，希望能給大家帶來些許收穫，當遇到類似效能問題時可以多一種思考方式。
圖片拍攝於大唐不夜城
問題背景
有個廣西的客戶，之前系統一直用的很流暢，最近反饋系統響應極慢，卡頓嚴重，希望我們儘快解決。
收到反饋以後我立馬去檢視伺服器各項指標，微服務所在機器各項指標正常，但是資料庫伺服器已不堪重負，看一下top的輸出：
資料庫伺服器的硬體配置為8核16G記憶體，最繁忙的時候系統負載已經達到了16，cpu利用率700%，可想而知系統當前服務質量有多糟糕。
自我思考
使用量突然上來了嗎？
觀察了使用人數的趨勢圖，是多了一些，但還在同一個數量級（toB的客戶，一般都在一個可控的量）。
資料量突然上來了嗎？
確實是多了一些，有幾張表，之前一年多才30多萬資料，最近一個月已經長到1百萬級別，到底是業務上量了還是程式bug呢，還需要持續排查。
慢查多了嗎？
是的，慢查很多，而且最近幾天猛漲，懷疑是不是增加了新功能，根據慢SQL去查業務程式碼，確實增加了七八張報表，我嘗試點了一下，確實很慢，高峰期前端幾乎全是超時，太慢了。
toB系統業務複雜，OLTP和OLAP都兼備，MySQL應付OLTP很擅長，但是OLAP就有點捉襟見肘了，也許有人會說為什麼不用大資料的那一套東西來解決OLAP的問題呢？
成本、成本、成本太高了，toB業務，大多數都部署在客戶的自建機房中，最好能少引入或者不引入其他元件，最大限度降低各項成本，包括和客戶的溝通成本，客戶要付出的財力成本，後期的運維成本等等。
解決過程
鋪墊了這麼多，具體怎麼解決呢？短期來看就是先採用垂直擴容的方式抵禦一波，俗稱升配置，長期來看的話需要梳理&優化慢查，採用讀寫分離等手段逐步優化。
話不多說，先聯絡客戶升配置，8核16G變16核32G，希望第二天早高峰能給客戶帶來絲滑般的使用者體驗。
第二天我盯著資料庫觀察負載，確實好了一些，但終歸還是涼涼了，只是來的比以往稍晚一些，一時間不知道怎麼處理了，客戶嚴重質疑我們的能力。
問題究竟出在哪裡了呢？現在這個硬體配置已經很強悍了，感覺完全沒有發揮出來，cpu最高才跑到800%。過往的工作中，有專門的DBA負責資料庫的運維，遇到過一次升配的場景，升完以後有很明顯的效能提升，那感覺刷刷的。
窮途末路的時候我撥通了老闆的電話，和他溝通了目前的境遇，希望老同志能指點迷津，快速幫我捅破窗戶紙，他有句話提醒了我，“為什麼浙江客戶的資料庫沒這個問題呢，配置一樣，浙江客戶的業務量還要更大一些，區別就是他們用的阿里雲RDS，廣西客戶是自建MySQL，會不會有什麼資料庫的引數我們沒有意識到”。
跟他聊完我立馬去對比了阿里雲RDS和自建MySQL的相關引數，有一個值非常可疑“innodb_buffer_pool_size“，浙江客戶RDS中這個值為12G，而廣西客戶自建MySQL這個值才為128M，相差接近百倍，好奇心驅使著我快速一探究竟。
看下官方文件對於innodb_buffer_pool的解釋：
-
The buffer pool is a memory area that holds cached data for tables, indexes, and other auxiliary buffers. For efficiency of high-volume read operations, the buffer pool is divided into pages that can potentially hold multiple rows. For efficiency of cache management, the buffer pool is implemented as a linked list of pages; data that is rarely used is aged out of the cache, using a variation of the LRU algorithm. For more information, see Section 15.5.1, “Buffer Pool”. InnoDBInnoDB
The size of the buffer pool is important for system performance:
-
InnoDB allocates memory for the entire buffer pool at server startup, using operations. The innodb_buffer_pool_size system variable defines the buffer pool size. Typically, a recommended innodb_buffer_pool_size value is 50 to 75 percent of system memory. innodb_buffer_pool_size can be configured dynamically, while the server is running. For more information, see Section 15.8.3.1, “Configuring InnoDB Buffer Pool Size”. malloc()
-
On systems with a large amount of memory, you can improve concurrency by dividing the buffer pool into multiple buffer pool instances. The innodb_buffer_pool_instances system variable defines the number of buffer pool instances.
-
A buffer pool that is too small may cause excessive churning as pages are flushed from the buffer pool only to be required again a short time later.
-
A buffer pool that is too large may cause swapping due to competition for memory.
提取出這麼幾個重要資訊：
1.緩衝池是儲存表、索引和其他輔助緩衝區的快取資料的記憶體區域；
2.緩衝池的大小對MySQL的效能很重要；
3.推薦的大小是實體記憶體的50%到75%；
4.inndb_buffer_pool_size可以在MySQL執行時動態調整；
5."Configuring InnoDB Buffer Pool Size"這個配置手冊中提到預設值為128兆。
再看看《MySQL技術內幕InnoDB儲存引擎》這本書中關於緩衝池的介紹：
緩衝池簡單來說就是一塊記憶體區域，通過記憶體的速度來彌補磁碟速度較慢對資料庫效能的影響。在資料庫中進行讀取頁的操作，首先將從磁碟讀到的頁放在緩衝池中，這個過程稱為將頁“FIX“到緩衝池中。下一次再讀相同的頁時，首先判斷該頁是否在緩衝池中，若在緩衝池中，稱該頁在緩衝池中被命中，直接讀取該頁，否則，讀取磁碟上的頁。
做了一定的瞭解之後我決定動手去改一下這個值看看效果，之前提到客戶已經把資料庫伺服器的配置擴到了16核32G，那我就把inndb_buffer_pool_size設定為32G*0.75=24G。
--注意：
--1.這裡的單位是位元組
--2.這是動態調整，長遠來看這個配置應該放到配置檔案中，防止重啟丟失
set global innodb_buffer_pool_size = 25769803776
調完以後效果顯著，我拿了幾個耗時嚴重的SQL做了對比，查詢時間減少一半以上，慢查數量明顯下降，看看此時的資料庫負載情況
調整緩衝池大小之前cpu和負載都很高，但是記憶體佔用很低，調整完以後cpu和負載明顯下降，記憶體佔用高了，這正是我們所期待的效果。
看看客戶給我們的反饋
總結
問題暫時告一段落，雖說沒有100%的解決問題，但也解決了70%-80%的問題，那些剩餘的20%-30%需要開發人員去優化程式碼，硬體擴容並不是銀彈，慶幸的是，經過這次調整應該能給開發人員爭取一些時間來優化程式碼。
另一方面也折射出自己對資料庫的認識只是停留在開發視角的一些知識，比如SQL優化、索引的匹配、慢查優化等等，對於底層原理性的東西欠缺太多，有人會說這類東西應該交給DBA負責，這麼說倒也沒什麼問題，但現實情況是有些團隊就是沒有配備DBA的，只能開發頂，再者說，作為開發而言，對於這類知識不一定要熟練掌握，但起碼應該有一些初步的認識。
推薦閱讀
MySQL如何使用記憶體 (oracle.com)
https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/memory-use.html
調整innodb_buffer_pool_size
Configuring InnoDB Buffer Pool Size (oracle.com)
