Ubuntu

如何為 mySQL 分配記憶體限制?

  • January 18, 2016

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 askey_buffer_size和 512M as innodb_buffer_pool_size.

可能的情況是無窮無盡的!

請記住,無論您分配什麼,都要為數據庫連接和作業系統留出足夠的 RAM。

引用自:https://serverfault.com/questions/415530