快取記憶體中的 MySQL 資料庫

問題描述

我有一個具有 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 是問題嗎？否則，他們的建議相當於從未見過或檢查過你的醫生，但只是聽說你的手臂受傷，告訴你放棄它。

參考文獻

Cache MySQL database in memory

注：本文內容整合自 Google/Baidu/Bing 輔助翻譯的英文資料結果。如果您對結果不滿意，可以加入我們改善翻譯效果：薇曉朵技術論壇。

快取記憶體中的 MySQL 資料庫