mysql 如何分配內(nèi)存

我們?nèi)匀皇褂脙蓚€會話，一個會話 run，用于運(yùn)行主 SQL；另一個會話 ps，用于進(jìn)行 performance_schema 的觀察：

創(chuàng)新互聯(lián)專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都網(wǎng)站制作、成都做網(wǎng)站、外貿(mào)營銷網(wǎng)站建設(shè)、金城江網(wǎng)絡(luò)推廣、重慶小程序開發(fā)公司、金城江網(wǎng)絡(luò)營銷、金城江企業(yè)策劃、金城江品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運(yùn)營等，從售前售中售后，我們都將竭誠為您服務(wù)，您的肯定，是我們最大的嘉獎；創(chuàng)新互聯(lián)為所有大學(xué)生創(chuàng)業(yè)者提供金城江建站搭建服務(wù)，24小時服務(wù)熱線：18982081108，官方網(wǎng)址：www.cdcxhl.com

主會話線程號為 29，

將 performance_schema 中的統(tǒng)計量重置，

臨時表的表大小限制取決于參數(shù)? tmp_table_size 和 max_heap_table_size 中較小者，我們實(shí)驗(yàn)中以設(shè)置 max_heap_table_size 為例。

我們將會話級別的臨時表大小設(shè)置為 2M（小于上次實(shí)驗(yàn)中臨時表使用的空間），執(zhí)行使用臨時表的 SQL：

查看內(nèi)存的分配記錄：

會發(fā)現(xiàn)內(nèi)存分配略大于 2M，我們猜測臨時表會比配置略多一點(diǎn)消耗，可以忽略。

查看語句的特征值：

可以看到語句使用了一次需要落磁盤的臨時表。

那么這張臨時表用了多少的磁盤呢？

我們開啟 performance_schema 中 waits 相關(guān)的統(tǒng)計項：

重做實(shí)驗(yàn)，略過。

再查看 performance_schema 的統(tǒng)計值：

可以看到幾個現(xiàn)象：

1. 臨時表空間被寫入了 7.92MiB 的數(shù)據(jù)。

2. 這些數(shù)據(jù)是語句寫入后，慢慢逐漸寫入的。

來看看這些寫入操作的特征，該方法我們在?實(shí)驗(yàn) 03?使用過：

可以看到寫入的線程是 page_clean_thread，是一個刷臟操作，這樣就能理解數(shù)據(jù)為什么是慢慢寫入的。

也可以看到每個 IO 操作的大小是 16K，也就是刷數(shù)據(jù)頁的操作。

結(jié)論：

我們可以看到，

1. MySQL 會基本遵守 max_heap_table_size 的設(shè)定，在內(nèi)存不夠用時，直接將表轉(zhuǎn)到磁盤上存儲。

2. 由于引擎不同（內(nèi)存中表引擎為 heap，磁盤中表引擎則跟隨 internal_tmp_disk_storage_engine 的配置），本次實(shí)驗(yàn)寫磁盤的數(shù)據(jù)量和?實(shí)驗(yàn) 05?中使用內(nèi)存的數(shù)據(jù)量不同。

3. 如果臨時表要使用磁盤，表引擎配置為 InnoDB，那么即使臨時表在一個時間很短的 SQL 中使用，且使用后即釋放，釋放后也會刷臟頁到磁盤中，消耗部分 IO。

MYSQL數(shù)據(jù)庫不夠用怎么辦？

可以啊，你可以把不會關(guān)聯(lián)不大的數(shù)據(jù)分離開，比述說客戶數(shù)據(jù)跟物品數(shù)據(jù)分別部署在兩個數(shù)據(jù)庫中，這樣完全可以訪問啊。。。

如何增大 MYSQL 內(nèi)存

查看 /proc/meminfo

Tips：

“大內(nèi)存頁”也稱傳統(tǒng)大頁、大頁內(nèi)存等有助于 Linux 進(jìn)行虛擬內(nèi)存的管理，標(biāo)準(zhǔn)的內(nèi)存頁為 4KB，這里使用“大內(nèi)存頁”最大可以定義 1GB 的頁面大小，在系統(tǒng)啟動期間可以使用“大內(nèi)存頁”為應(yīng)用程序預(yù)留一部分內(nèi)存，這部分內(nèi)存被占用且永遠(yuǎn)不會被交換出內(nèi)存，它會一直保留在那里，直到改變配置。（詳細(xì)介紹請看下面鏈接官方解釋）

那么這么大頁內(nèi)存是分配給誰的呢？

查詢一下：

shell /proc/sys/vm/hugetlb_shm_group

27

shell id 27

uid=27(mysql) gid=27(mysql) groups=27(mysql)

hugetlb_shm_group 文件里填的是指定大頁內(nèi)存使用的用戶組 id，這里查看到是 MySQL 組 id，那既然是給 MySQL 的為什么 free 等于 total，并且 mysql 還只有 20 多 G 實(shí)際使用內(nèi)存呢？

原來在 MySQL 中還有專門啟用大內(nèi)存頁的參數(shù)，在 MySQL 大內(nèi)存頁稱為 large page。

查看 MySQL 配置文件

發(fā)現(xiàn)配置文件中確實(shí)有 large-page 配置，但出于禁用狀態(tài)。

后與業(yè)務(wù)確認(rèn)，很早之前確實(shí)啟用過 mysql 的 large page，不過后面禁用了。排查到這基本就有了結(jié)論。

結(jié)論

這套環(huán)境之前開啟了 20000 的大內(nèi)存頁，每頁大小為 2MB，占用了 40G 內(nèi)存空間，給 MySQL 使用，并且 MySQL 開啟了 large page，但后來不使用的時候，只關(guān)閉了 MySQL 端的 large page 參數(shù)，但沒有實(shí)際更改主機(jī)的關(guān)于大內(nèi)存頁的配置，所以導(dǎo)致，實(shí)際上主機(jī)上的還存在 20000 的大內(nèi)存頁，并且沒在使用，這一部分長期空閑，并且其他程序不能使用。

所以 MySQL 在使用 20G 內(nèi)存左右，整個主機(jī)內(nèi)存就飽和了，然后在部分條件下，就觸發(fā)了 OOM，導(dǎo)致 mysqld 被 kill，但主機(jī)上又有 mysqld_safe 守護(hù)程序，所以又再次給拉起來，就看到了文章初的偶爾連接不上的現(xiàn)象。