升級到MySQL 5.7 解決分區問題

升級到MySQL 5.7 解決分區問題

前言

經常有小夥伴問，MySQL的分區（partition）怎麼樣？能用不？是不是有很多bug？不知MySQL的分區為何會給普羅福士這樣的印象。但Inside君的印象中，分區影響比較大的bug就下面的一例（嚴格意義也很難說是bug），也是小夥伴們諮詢Inside君分區遇到最多的問題。不過，好在這個bug已在5.7版本中得到了修複（準確來說是5.7支援了Native Paritition）。看來又多了一個升級到5.7的理由。總結來說，生產環境有必要每天弄個分區嘛？

本文

一個月之前，Scott和同事們發現公司有一個MySQL MHA叢集的master（假設master機器名為hostA）每隔一周左右就會掛一次（指MySQL掛掉），在幾周內，MHA來回切了好幾次。

按照國際慣例，Scott按照如下順序去查問題到底出在哪裡：

1. 先翻MySQL error log，沒有發現異常

2. 再翻Linux系統記錄檔，果然，翻到了下面的內容：

Nov 26 13:05:38 hostA kernel: mysql invoked oom-killer: gfp_mask=0x280da, order=0, oom_adj=0, oom_score_adj=0

......

Nov 26 13:05:38 hostA kernel: Out of memory: Kill process 32271 (mysqld) score 976 or sacrifice child

Nov 26 13:05:38 hostA kernel: Killed process 32271, UID 496, (mysqld) total-vm:83064212kB, anon-rss:64204132kB, file-rss:4544kB

該機器的實體記憶體大小為62G，從上面的日誌看，MySQL確實已經把它用滿了。該機器上MySQL的innodb_buffer_pool=31G，Scott認為這已經相當保守了，而且各種buffer_size我們都使用的是預設值，MySQL OOM時的使用者串連數是100+，

這些目測都沒有什麼問題，但是居然還是發生了OOM，實在是不可思議。當時覺得就是記憶體不夠用了唄，沒有查出具體原因，後來62G的記憶體加到了125G（innodb_buffer_pool_size增大到64G，這值確實很保守），還是發生了OOM。

其實一開始Scott就發現，這台機器上有一個更早的問題，就是因為系統最大檔案開啟數不夠導致這台機器的xtrabackup備份總是不成功，具體是什麼原因請等Scott去整理xtrabackup備份的更詳細的過程。然後我去檢查了該機器上面的*.ibd檔案和*.frm檔案數量，嚇我一跳（話說Inside君也是嚇尿了）：

[userA@hostA mysql]$ sudo find . -name '*.ibd' | wc -l

169577

[userA@hostA mysql]$ sudo find . -name '*.frm' | wc -l

2534

也就是說，該機器上面竟然有17萬個ibd檔案，但是只有2534張表，很明顯是分區表中的分區數量非常多。

[userA@hostA mysql]$ sudo find . -name '*par*' | wc -l

1882

Scott仔細比較了這台機器和其他沒有問題的機器的不同，發現這台機器上面分區數量太多是唯一的一個不同，這讓Scott沒有辦法不懷疑是分區導致的問題。

Scott仍然按照國際慣例，第一時間去查MySQL 5.6的官方文檔，無果。。。（官方文檔雖然不是萬能的，但是仍然是出現問題的第一參考資料）。去MySQL的bugs頁面搜尋關於partition的bug，無果。。。去google了下，發現有的比較雜的網站上面寫道MySQL分區數量太多引發記憶體耗盡的問題，但是文章講的內容感覺不是很正確。

最後在薑老師的指點下，看了這篇文章：

http://mysqlserverteam.com/innodb-native-partitioning-early-access/

上面是MySQLTeam Dev寫的關於InnoDB Native Partitioning的文章。文章中大概講的內容是，在5.6裡面，分區的資訊是在MySQL Server層維護的（在.par檔案裡面），InnoDB引擎層是不知道有分區這個概念的，InnoDB引擎層把每一個分區都當成一張普通的InnoDB表。在開啟一個分區表時，會開啟很多個分區，開啟這些分區表就相當於開啟了同等數量的InnoDB表，這需要更多記憶體存放InnoDB表的中繼資料和各種與ibd檔案開啟相關的各種cache與handler的資訊。在5.7裡面，InnoDB引入了Native Partitioning，它把分區的資訊從Server層移到了InnoDB層，開啟一個分區表和開啟一個InnoDB表的記憶體開銷基本是一樣的。

If we compare the amount of memory used when opening a single instance of this table, first using the old generic non-native partitioning, and then with InnoDB Native Partitioning we see the following:

One open instance of the table takes 49% less memory (111MB vs 218MB) with the current state of Native Partitioning support. With ten open instances of the table, we take up 90% less memory (113MB vs 1166MB)!

由於升級到5.7還需要一些時日，目前已經將分區數量減少到25000，125G剩餘記憶體在20天裡一直穩定在20G左右，這也表明確實是分區數量太多的原因。

本文永久更新連結地址：