問題描述

我有一個具有 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 輔助翻譯的英文資料結果。如果您對結果不滿意，可以加入我們改善翻譯效果：薇曉朵技術論壇。