如何為 mySQL 分配記憶體限制?
mysql Tuner 報告 mySQL 可以使用 166% 的已安裝記憶體,我該如何限制記憶體使用?
$$ !! $$最大可能記憶體使用量:426.8M(已安裝 RAM 的 166%)
您可以配置
- 所有 MyISAM
- 所有 InnoDB
- MyISAM 和 InnoDB 的混合
在為任一引擎分配記憶體之前,最好考慮一下每個儲存引擎的記憶體功能
為MISAM配置
使用的主要機制是密鑰記憶體。它只記憶體 .MYI 文件中的索引頁。要調整密鑰記憶體的大小,請執行以下查詢:
SELECT CONCAT(ROUND(KBS/POWER(1024, IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999), SUBSTR(' KMG',IF(PowerOf1024<0,0, IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_key_buffer_size FROM (SELECT LEAST(POWER(2,32),KBS1) KBS FROM (SELECT SUM(index_length) KBS1 FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) AA ) A, (SELECT 2 PowerOf1024) B;
給定您目前的數據集,這將給出 MyISAM 密鑰記憶體 ( key_buffer_size ) 的推薦設置(查詢將建議上限為 4G (4096M)。對於 32 位作業系統,4GB 是限制。對於 64 位,8GB。
配置InnoDB
使用的主要機制是 InnoDB 緩衝池。它記憶體訪問的 InnoDB 表中的數據和索引頁。要調整 InnoDB 緩衝池的大小,請執行以下查詢:
SELECT CONCAT(ROUND(KBS/POWER(1024, IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999), SUBSTR(' KMG',IF(PowerOf1024<0,0, IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables WHERE engine='InnoDB') A, (SELECT 2 PowerOf1024) B;
給定目前數據集,這將為 InnoDB 緩衝池 ( innodb_buffer_pool_size ) 的大小提供推薦設置。
不要忘記調整 InnoDB 日誌文件(ib_logfile0 和 ib_logfile1)的大小。MySQL 原始碼將所有 InnoDB 日誌文件的總大小上限設置為 < 4G (4096M)。(注意:Percona 伺服器二進製文件超過了這個。我最近使用innodb_log_file_size為單個 InnoDB 日誌文件設置了一個 4G 的大型數據庫伺服器)
為簡單起見,僅給出兩個日誌文件,您可以通過以下方式調整它們的大小:
- 步驟 1) 將 innodb_log_file_size=NNN 添加到 /etc/my.cnf (NNN 應該是 innodb_buffer_pool_size 的 25% 或 2047M,以較小者為準)
- 步驟 2) 服務 mysql 停止
- 步驟 3) rm /var/log/mysql/ib_logfile$$ 01 $$
- 第4步)服務mysql啟動(ib_logfile0和ib_logfile1重新創建)
警告
在兩個查詢的末尾是一個內聯查詢:
(SELECT 2 PowerOfTwo) B
(SELECT 0 PowerOf1024)
以字節為單位給出設置(SELECT 1 PowerOf1024)
以千字節為單位給出設置(SELECT 2 PowerOf1024)
給出以兆字節為單位的設置(SELECT 3 PowerOf1024)
以千兆字節為單位給出設置- 不接受小於 0 或大於 3 的冪
結語
常識是無可替代的。如果您的記憶體有限、混合使用儲存引擎或它們的組合,您將不得不針對不同的場景進行調整。
如果你有 2GB RAM 和 16GB InnoDB,分配 512M 作為
innodb_buffer_pool_size
.如果您有 2GB RAM 和 4GB MyISAM 索引,請將 512M 分配為
key_buffer_size
.如果你有 2GB RAM 和 4GB MyISAM 索引和 16GB InnoDB,分配 512M as
key_buffer_size
和 512M asinnodb_buffer_pool_size
.可能的情況是無窮無盡的!
請記住,無論您分配什麼,都要為數據庫連接和作業系統留出足夠的 RAM。