建站
咨詢
售后
建站咨詢
新聞中心
在這篇文章中，我們來探討下當(dāng)您向?qū)嵗砑痈鄡?nèi)存時(shí)該怎么做。在擴(kuò)展資源時(shí)，向運(yùn)行 MySQL 的服務(wù)器添加內(nèi)存是常見的做法。
創(chuàng)新互聯(lián)是一家專業(yè)的成都網(wǎng)站建設(shè)公司，我們專注成都網(wǎng)站設(shè)計(jì)、網(wǎng)站制作、網(wǎng)絡(luò)營(yíng)銷、企業(yè)網(wǎng)站建設(shè)，友情鏈接，廣告投放為企業(yè)客戶提供一站式建站解決方案，能帶給客戶新的互聯(lián)網(wǎng)理念。從網(wǎng)站結(jié)構(gòu)的規(guī)劃UI設(shè)計(jì)到用戶體驗(yàn)提高，創(chuàng)新互聯(lián)力求做到盡善盡美。
先看一些背景
擴(kuò)展資源只是為您的環(huán)境添加更多資源，這可以分為兩種主要方式：垂直擴(kuò)展和水平擴(kuò)展。
垂直擴(kuò)展正在增加給定實(shí)例的硬件容量，從而擁有更強(qiáng)大的服務(wù)器，而水平擴(kuò)展正在添加更多服務(wù)器，這是一種非常標(biāo)準(zhǔn)的負(fù)載平衡和分片方法。
隨著流量的增長(zhǎng)，工作數(shù)據(jù)集變得越來越大，因此我們開始受到影響，因?yàn)闊o(wú)法放入內(nèi)存的數(shù)據(jù)必須從磁盤中檢索。這是一項(xiàng)代價(jià)高昂的操作，即使使用現(xiàn)代 NVME 驅(qū)動(dòng)器也是如此，因此在某些時(shí)候，我們將需要處理我們提到的任何一種擴(kuò)展解決方案。
在這種情況下，我們將討論添加更多內(nèi)存，這通常是垂直擴(kuò)展硬件的最快和最簡(jiǎn)單的方法，并且擁有更多內(nèi)存可能是 MySQL 的主要好處。
如何計(jì)算內(nèi)存利用率
首先，我們需要清楚在 MySQL 操作期間哪些變量分配了內(nèi)存，我們將只介紹常見的變量，因?yàn)樗鼈冇泻芏唷４送?，我們需要知道一些變量將全局分配?nèi)存，而其他變量將進(jìn)行每個(gè)線程的分配。
為簡(jiǎn)單起見，我們將考慮使用標(biāo)準(zhǔn)存儲(chǔ)引擎InnoDB來討論這個(gè)主題。
我們有全局分配的變量：
key_buffer_size：MyISAM 設(shè)置應(yīng)設(shè)置為 8-16M，任何高于此值的設(shè)置都是錯(cuò)誤的，因?yàn)槌浅鲇谔厥庠?，否則我們不應(yīng)使用 MyISAM 表。一個(gè)典型的場(chǎng)景是 MyISAM 只被系統(tǒng)表使用，這些系統(tǒng)表很?。ㄟ@對(duì) 5.7 以上的版本有效），并且在 MySQL 8 中系統(tǒng)表被遷移到 InnoDB 引擎。所以這個(gè)變量的影響可以忽略不計(jì)。
query_cache_size : 0 是默認(rèn)值，在 8.0 中被刪除，所以我們不會(huì)考慮它。
innodb_buffer_pool_size：這是 InnoDB 放置頁(yè)面以執(zhí)行操作的緩存。越大越好。
當(dāng)然，還有其他的，但在使用默認(rèn)值運(yùn)行時(shí)它們的影響很小。
此外，在每個(gè)線程（或打開的連接）上分配了其他變量：
read_buffer_size、 read_rnd_buffer_size 、sort_buffer_size、join_buffer_size和tmp_table_size ，以及其他一些變量。默認(rèn)情況下，所有這些都工作得很好，因?yàn)榉峙湫《咝?。因此，主要的潛在問題變成了我們分配的許多連接可以保持這些緩沖區(qū)一段時(shí)間并增加額外的內(nèi)存壓力。理想的情況是控制打開（和使用）的連接數(shù)，并嘗試將該數(shù)量減少到不損害應(yīng)用程序的足夠數(shù)量。
但是我們不要忘記重點(diǎn)，我們有更多的內(nèi)存，我們需要知道該如何正確地調(diào)優(yōu)它以獲得最佳使用效果。
我們需要關(guān)注的對(duì)內(nèi)存影響最大的設(shè)置是innodb_buffer_pool_size，因?yàn)檫@是幾乎所有魔法發(fā)生的地方，并且通常是更重要的內(nèi)存消耗者。有一條古老的經(jīng)驗(yàn)法則說，“此設(shè)置的大小應(yīng)設(shè)置為可用內(nèi)存的 75% 左右”，一些云供應(yīng)商將此值設(shè)置為total_memory*0.75。
我說“舊”是因?yàn)楫?dāng)運(yùn)行具有 8G 或 16G RAM 的實(shí)例很常見時(shí)，該規(guī)則很好，因此從 8G 中分配大約 6G 或從 16G 中分配 13G 過去是合乎邏輯的。
但是如果我們遇到一個(gè)100G甚至200G的實(shí)例呢？現(xiàn)在這種硬件已經(jīng)不少見了，那么我們會(huì)用100G中的80G還是200G中的160G呢？意思是，我們會(huì)避免分配 20G 到 40G 的內(nèi)存并將其留給文件系統(tǒng)緩存操作嗎？雖然這些文件系統(tǒng)操作并非毫無(wú)用處，但我認(rèn)為操作系統(tǒng)在專用數(shù)據(jù)庫(kù)上為此需要超過 4G-8G 的內(nèi)存。此外，建議對(duì) InnoDB 使用 O_DIRECT 刷新方法來繞過文件系統(tǒng)緩存。
示例
現(xiàn)在我們了解了分配內(nèi)存的主要變量，讓我們檢查一下我目前正在處理的一個(gè)好的用例。假設(shè)這個(gè)系統(tǒng)：
Shell
$ free -m
total used free shared buff/cache available
Mem: 385625 307295 40921 4 37408 74865所以大約 380G 的 RAM，相當(dāng)大的內(nèi)存量?，F(xiàn)在讓我們檢查一下考慮到最大使用連接數(shù)的最大潛在分配。
*這里有一點(diǎn)免責(zé)聲明，雖然這個(gè)查詢并不完全準(zhǔn)確，因此它可能與實(shí)際結(jié)果不同，但我們可以了解可能要分配的內(nèi)容，我們可以利用 performance_schema 數(shù)據(jù)庫(kù)，但這可能需要啟用一些儀器默認(rèn)禁用：
MySQL
mysql > show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 67 |
+----------------------+-------+
1 row in set (0.00 sec)因此，最多使用 67 個(gè)連接，我們可以得到：
MySQL
mysql > SELECT ( @@key_buffer_size
-> + @@innodb_buffer_pool_size
-> + 67 * (@@read_buffer_size
-> + @@read_rnd_buffer_size
-> + @@sort_buffer_size
-> + @@join_buffer_size
-> + @@tmp_table_size )) / (1024*1024*1024) AS MAX_MEMORY_GB;
+---------------+
| MAX_MEMORY_GB |
+---------------+
| 316.4434 |
+---------------+
1 row in set (0.00 sec)到目前為止，一切都很好，我們?cè)趦?nèi)存范圍內(nèi)，現(xiàn)在讓我們看看innodb_buffer_pool_size有多大，以及它的大小是否合適：
MySQL
mysql > SELECT (@@innodb_buffer_pool_size) / (1024*1024*1024) AS BUFFER_POOL_SIZE;
+------------------+
| BUFFER_POOL_SIZE |
+------------------+
| 310.0000 |
+------------------+
1 row in set (0.01 sec)所以緩沖池是 310G，大約占總內(nèi)存的 82%，到目前為止，總使用量大約是 84%，剩下大約 60G 的內(nèi)存沒有被使用。好吧，被文件系統(tǒng)緩存使用，最終，InnoDB 沒有使用它。
好的，現(xiàn)在讓我們進(jìn)入正題，如何正確配置內(nèi)存以供 MySQL 有效使用。從 pt-mysql-summary 我們知道緩沖池已滿：
Shell
Buffer Pool Size | 310.0G
Buffer Pool Fill | 100%這是否意味著我們需要更多內(nèi)存？也許吧，所以讓我們使用以下命令檢查我們知道的實(shí)例中有多少磁盤操作，而工作數(shù)據(jù)集不適合內(nèi)存（這正是我們?cè)黾觾?nèi)存大小的原因）：
Shell
mysqladmin -r -i 1 -c 60 extended-status | egrep "Innodb_buffer_pool_read_requests|Innodb_buffer_pool_reads"
| Innodb_buffer_pool_read_requests | 99857480858|
| Innodb_buffer_pool_reads | 598600690 |
| Innodb_buffer_pool_read_requests | 274985 |
| Innodb_buffer_pool_reads | 1602 |
| Innodb_buffer_pool_read_requests | 267139 |
| Innodb_buffer_pool_reads | 1562 |
| Innodb_buffer_pool_read_requests | 270779 |
| Innodb_buffer_pool_reads | 1731 |
| Innodb_buffer_pool_read_requests | 287594 |
| Innodb_buffer_pool_reads | 1567 |
| Innodb_buffer_pool_read_requests | 282786 |
| Innodb_buffer_pool_reads | 1754 |Innodb_buffer_pool_read_requests：從內(nèi)存中讀取滿足的頁(yè)面（好）
Innodb_buffer_pool_reads：從磁盤讀取頁(yè)面（壞）
您可能會(huì)注意到，我們?nèi)匀粫?huì)從磁盤讀取一些數(shù)據(jù)，并且我們希望避免它們，所以讓我們將緩沖池大小增加到 340G（總內(nèi)存的 90%）并再次檢查：
Shell
mysqladmin -r -i 1 -c 60 extended-status | egrep "Innodb_buffer_pool_read_requests|Innodb_buffer_pool_reads"
| Innodb_buffer_pool_read_requests | 99937722883 |
| Innodb_buffer_pool_reads | 599056712 |
| Innodb_buffer_pool_read_requests | 293642 |
| Innodb_buffer_pool_reads | 1 |
| Innodb_buffer_pool_read_requests | 296248 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_read_requests | 294409 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_read_requests | 296394 |
| Innodb_buffer_pool_reads | 6 |
| Innodb_buffer_pool_read_requests | 303379 |
| Innodb_buffer_pool_reads | 0 |現(xiàn)在我們幾乎不需要磁盤，IO 壓力被釋放了。
概括
如果您增加服務(wù)器的內(nèi)存大小，您主要需要關(guān)注innodb_buffer_pool_size，因?yàn)檫@是最關(guān)鍵的調(diào)整變量。在大型系統(tǒng)上分配 90% 到 95% 的總可用內(nèi)存一點(diǎn)也不壞，因?yàn)椴僮飨到y(tǒng)只需要幾 GB 即可正常運(yùn)行，而更多的內(nèi)存交換應(yīng)該足以正常運(yùn)行。
此外，請(qǐng)檢查您需要（和已使用）的最大連接數(shù)，因?yàn)檫@是導(dǎo)致內(nèi)存問題的常見錯(cuò)誤，如果您需要在打開 1000 個(gè)連接的情況下運(yùn)行，則可能無(wú)法分配緩沖池的 90% 內(nèi)存，并且可能需要一些額外的操作（即，添加代理層或連接池）。
從 MySQL 8 開始，我們有一個(gè)名為innodb_dedicated_server的新變量，它將自動(dòng)計(jì)算內(nèi)存分配。雖然這個(gè)變量對(duì)于初始方法非常有用，但它可能會(huì)在 RAM 超過 4G 的系統(tǒng)中分配不足，因?yàn)樗O(shè)置緩沖池大小 =（檢測(cè)到的服務(wù)器內(nèi)存 * 0.75），所以在 200G 的服務(wù)器中，我們有緩沖池只有 150 個(gè)。
結(jié)論
垂直擴(kuò)展是提高性能的最簡(jiǎn)單、最快的方法，而且成本更低——但并不神奇。正確調(diào)整變量需要分析和理解內(nèi)存的使用方式。這篇文章重點(diǎn)介紹了調(diào)整內(nèi)存分配時(shí)要考慮的基本變量，特別是 innodb_buffer_pool_size 和 max_connections。不需要時(shí)不要過度調(diào)整，并注意這兩者如何影響您的系統(tǒng)。
本文題目：創(chuàng)新互聯(lián)mysql教程:MySQL進(jìn)階教程：升級(jí)內(nèi)存后MySQL優(yōu)化
本文網(wǎng)址：http://www.dlmjj.cn/article/cdjcsss.html
