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