问题描述

我有一个具有 600MB MySQL 数据库的网站的问题。网站太慢了我注意到 MySQL 数据库获得的越大，越慢。当它是 5MB，网站是非常快的。当它开始变大时，它开始变得越来越慢，现在，在 600MB，这是非常慢的，需要 10 秒加载页面。

我检查了顶级进程，它与高负载或任何东西无关。它甚至与 IOPS 没有任何关系，因为我在 HDD 7.2k rpm 驱动器上进行了测试，现在在使用 Intel 320 SSD 驱动器进行测试时也出现了同样的问题，所以我不认为这是高查询。

该网站正在使用 Wordpress，并有 9 个插件活跃。人们说这可能是插件… 也许… 但是现在我只想将整个数据库缓存在内存中，并希望得到帮助和方向，从哪里开始，如何做。

我拥有 16GB 的内存和 3.1GHz 的 i5-2400 4 核。 OS 是 centos 5.7

top - 07:23:57 up 9 days, 12:15, 0 users, load average: 0.09, 0.04, 0.05
Tasks: 162 total, 1 running, 161 sleeping, 0 stopped, 0 zombie
Cpu(s): 8.2%us, 1.0%sy, 0.0%ni, 90.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 16367532k total, 3641628k used, 12725904k free, 612140k buffers
Swap: 1046520k total, 0k used, 1046520k free, 1538896k cached

最佳解决方法

如果我是你，我会将所有数据切换到 InnoDB 。许多人已经讨论了表锁定/行锁定。我会一直选择 InnoDB 双手。但是，there is another profound reason for choosing InnoDB…CACHING 。

虽然大多数人吹嘘 MyISAM 的读取速度更快，但大多数人都忘记了 MyISAM 的多个缓存 (称为密钥缓存 (由 key_buffer_size 设置)) 仅缓存来自.MYI 文件的索引页。它从不缓存数据页。它在 32 位系统中的官方最大容量为 4GB 。 8GB 是 64 位的最佳最大值。

InnoDB 缓冲池缓存数据和索引页面。根据您所拥有的服务器，可以将其高速缓存到 RAM 中的整个数据集。您可以调整 InnoDB 高达 80％的 RAM 和 10％的 DB Conenctions，并为操作系统留下 10％。 This is true even for different operating systems 。

I have recommended these things for Drupal customers 取得了巨大的成功。 It applies to WordPress 也一样。我为 WordPress 的客户提供了 DB 支持。同样的改善。

您可以更有效地使用 configure memory for InnoDB，您可以更多的 MyISAM 。总是有一种方法来实现 tweek InnoDB to suit your performance needs 。随着数据的增长，它将最终成为 become a requirement 。

更新 2011-11-21 11:44 EST

如果您的完整数据集足够小，您可以在 mysql 启动后的每个表上执行 SELECT 查询。

对于 InnoDB 和/或 MyISAM 的所有表，请运行此查询：

SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',
    db,'.',tb,' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (
    SELECT
        engine,table_schema db,table_name tb,index_name,
        GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
    FROM (
        SELECT
            B.engine,A.table_schema,A.table_name,
            A.index_name,A.column_name,A.seq_in_index
        FROM
            information_schema.statistics A INNER JOIN
            (SELECT engine,table_schema,table_name
            FROM information_schema.tables
            WHERE engine IN ('InnoDB','MyISAM')) B
            USING (table_schema,table_name)
        WHERE
            B.table_schema NOT IN ('information_schema','mysql')
            AND A.index_type <> 'FULLTEXT'
        ORDER BY
            table_schema,table_name,index_name,seq_in_index
        ) A
    GROUP BY
        table_schema,table_name,index_name
) AA
ORDER BY
    engine DESC,db,tb
;

这将输出您需要运行的每个可能的 SELECT 查询，它将召集要引用的所有索引。将此查询放在一个名为/root/MakeSelectQueriesToLoad.sql 的文件中。运行脚本并收集输出/root/SelectQueriesToLoad.sql 。最后运行它：

mysql -u... -p... -AN < /root/MakeSelectQueriesToLoad.sql > /root/SelectQueriesToLoad.sql
mysql -u... -p... < /root/SelectQueriesToLoad.sql

这肯定会将所有索引页面预加载到 InnoDB 缓冲池和 MyISAM 密钥缓存中。如果您的所有数据都是 InnoDB，请进行两项更改：

  • 用 WHERE engine='InnoDB'代替 WHERE engine IN ('InnoDB','MyISAM')

  • 用 CONCAT('SELECT * FROM ', 代替 CONCAT('SELECT ',ndxcollist,' FROM ',

这样还会将更多的数据页面填入 InnoDB 缓冲池。

最后注：Make sure the InnoDB Buffer Pool is large enough to hold all your InnoDB Data

次佳解决方法

您已经在内存中缓存整个数据库。问题几乎肯定是即使在 RAM 中搜索数据库所需的时间。

观察您的磁盘 I /O 统计信息。你可能会看到只有偶然的磁盘 I /O 位。数据库在内存中。这不是问题。您需要首先安装 iostat 。你没有提到你的平台或发行版，但它可能在一个名为 iostat 的包中。您可能会发现 atop 更加友好。

有谁告诉你这样做后，得到任何证据表明你的整个数据库还没有在内存或磁盘 I /O 是问题吗？否则，他们的建议相当于从未见过或检查过你的医生，但只是听说你的手臂受伤，告诉你放弃它。

参考文献

注：本文内容整合自 Google/Baidu/Bing 辅助翻译的英文资料结果。如果您对结果不满意，可以加入我们改善翻译效果：薇晓朵技术论坛。