☰

配置数据库引擎

tag-icon • tag-icon ubuntu mysql memory
配置数据库引擎

mysql tuner 报告说 mySQL 可以使用已安装 RAM 的 166％，我该如何限制 RAM 的使用？

[!!] 最大可能的内存使用量：426.8M（已安装 RAM 的 166%）

答案1

您可以配置

  • 所有 MyISAM
  • 所有InnoDB
  • MyISAM 和 InnoDB 的混合

在为任一引擎分配内存之前，最好先考虑一下每个存储引擎的缓存情况

配置数据库引擎

使用的主要机制是密钥缓存。它仅缓存来自 .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 密钥缓存的推荐设置（密钥缓冲区大小）给定您当前的数据集（查询将建议限制为 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 Server 二进制文件超过了这个限制。我最近使用以下方法为单个 InnoDB 日志文件设置了一个 4G 的大型数据库服务器innodb_log_file_size）

为了简单起见，仅给出两个日志文件，您可以按照以下方式调整它们的大小：

  • 步骤 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）service mysql start（重新创建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 作为key_buffer_size和 512M 作为innodb_buffer_pool_size。

可能出现的情况无穷无尽！！！

请记住，无论您分配什么，都要为 DB 连接和操作系统留出足够的 RAM。

答案2

您应该调整中的key_buffer_size和，这两个是最重要的内存相关设置。要获取当前值，请使用命令行客户端中的和。innodb_buffer_pool_sizemy.cnfshow variables like 'key_buffer_size';show variables like 'innodb_buffer_pool_size';mysql

答案3

对于 Myisam Engine

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections

这个值给出了 mysql 可以使用的总内存。这个值应该小于你的 RAM 或者接近 RAM 的 60%。对于 innodbinnodb_buffer_pool_size应该小于你的 RAM 或 60% 的 RAM

调整上述值，使 RAM 使用率达到 RAM 的 60％。

相关内容