☰

如何使用 MySQL 上的大部分可用内存

tag-icon • tag-icon mysql performance memory innodb myisam
如何使用 MySQL 上的大部分可用内存

我有一台 MySQL 服务器，它同时具有 InnoDB 和 MyISAM 表。InnoDB 表空间很小，不到 4 GB。MyISAM 很大，总共约 250 GB，其中 50 GB 用于索引。

我们的服务器有 32 GB 的 RAM，但通常只使用约 8GB。我们的 key_buffer_size 只有 2GB。但我们的密钥缓存命中率约为 95%。我觉得难以置信。

以下是我们的关键统计数据：

| Key_blocks_not_flushed | 1868 | | Key_blocks_unused | 109806 | | Key_blocks_used | 1714736 | | Key_read_requests | 19224818713 | | Key_reads | 60742294 | | Key_write_requests | 1607946768 | | Key_writes | 64788819 |

key_cache_block_size 默认为 1024。

我们有 52 GB 的索引数据，2GB 的密钥缓存足以获得 95% 的命中率。这可能吗？另一方面，数据集是 200GB，由于 MyISAM 使用 OS（Centos）缓存，我预计它会使用更多内存来缓存访问的 myisam 数据。但在这个阶段，我看到 key_buffer 已完全使用，我们的 innodb 缓冲池大小为 4gb，也已完全使用，总计 6GB。这意味着仅使用 1 GB 来缓存数据？

我的问题是如何检查所有可用内存的使用位置？如何检查 MyISAM 是否访问 OS 缓存而不是磁盘来读取数据？

答案1

我很容易相信 95% 的键缓存命中率。听听你的统计数据：

您声明您有 50GB 的 MyISAM 表索引

键缓存用于缓存 MyISAM 表的索引页。此选项由选项key_buffer_size设置

您说它被设置为 2G。难怪命中率是 95%。索引块不断地被调入和调出密钥缓存。在 MySQL 发现第一个查询所需的索引 (.MYI) 信息最初不存在后，需要特定行的数十个查询被加载到密钥缓存中。在第一个查询加载它后，所有需要相同 .MYI 信息来查找数据 (.MYD) 的后续查询都将被缓存。MySQL 不会在其自己的缓存中缓存 MyISAM 数据。

您应该可以设置key_buffer_size为8G。

以下是摘录http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_key_buffer_size：

您可以增加该值，以便更好地处理所有读取和多次写入的索引；在主要功能是使用 MyISAM 存储引擎运行 MySQL 的系统上，机器总内存的 25% 是此变量的可接受值。但是，您应该知道，如果将该值设置得太大（例如，超过机器总内存的 50%），您的系统可能会开始分页并变得非常慢。这是因为 MySQL 依赖操作系统为数据读取执行文件系统缓存，因此您必须为文件系统缓存留出一些空间。您还应该考虑除 MyISAM 之外可能使用的任何其他存储引擎的内存要求。

同一 URL 解释了 32 位操作系统的最大 key_buffer_size 为 4G。对于给定的服务器配置，key_buffer_size 不应超过 8G。

对于数据读取，您唯一需要的状态变量是 Key_reads。它指示了从 .MYI 文件中提取索引页的频率。

您需要监控的关键缓存命中率如下

KRR_NOW = Key_read_requests Now
KRR_SEC = Key_read_requests one ago
KRD_NOW = Key_reads Now
KRD_SEC = Key_reads one second ago
KRR_DELTA = KRR_NOW - KRR_SEC
KRD_DELTA = KRD_NOW - KRD_SEC

因此，KeyCache 命中率 (KHR) 是这个公式

KHR = 100 * (KRR_DELTA - KRD_DELTA) / KRR_DELTA

您希望 KeyCache 命中率达到 99+%

现在让我们来讨论一下 InnoDB。

您的 innodb_buffer_pool_size 应设置为 4G。这是因为 InnoDB 缓冲池会缓存数据页和索引页。您可以使用以下命令为 innodb_buffer_pool 指定一个更具体的数字：

SELECT CONCAT(CEILING(ibbytes / POWER(1024,3)),'G') FROM (SELECT SUM(data_length+index_length) ibbytes FROM information_schema.tables where engine='InnoDB') A;

相关内容