頻繁查詢記憶體失效的成本值得嗎？

我目前正在研究一個 MySQL 數據庫，在該數據庫中，我們看到查詢記憶體中有大量失效，主要是因為在許多表上執行的大量 INSERT、DELETE 和 UPDATE 語句。

我要確定的是允許查詢記憶體用於針對這些表執行的 SELECT 語句是否有任何好處。由於它們很快就失效了，在我看來，最好的辦法是對這些表的 SELECT 語句使用 SQL_NO_CACHE。

頻繁失效的成本值得嗎？

編輯：應下面使用者@RolandoMySQLDBA 的要求，這裡是關於 MyISAM 和 INNODB 的資訊。

InnoDB

• 數據大小：177.414 GB
• 索引大小：114.792 GB
• 表大小：292.205 GB

MyISAM

• 數據大小：379.762 GB
• 索引大小：80.681 GB
• 表大小：460.443 GB

附加資訊：

• 版本：5.0.85
• 查詢記憶體限制：1048576
• query_cache_min_res_unit: 4096
• 查詢記憶體大小：104857600
• 查詢記憶體類型：開啟
• query_cache_wlock_invalidate：關閉
• innodb_buffer_pool_size: 8841592832
• 24GB 記憶體

您應該只禁用查詢記憶體

[mysqld]
query_cache_size = 0

然後重啟mysql。為什麼我會建議？？？

查詢記憶體將始終與 InnoDB 對接。如果修改不影響其他事務的可重複讀取，如果 InnoDB 的 MVCC 允許從查詢記憶體中提供查詢，那就太好了。不幸的是，InnoDB 並沒有這樣做。顯然，您有很多查詢很快就會失效並且可能沒有被重用。

對於 MySQL 4.0 下的 InnoDB，事務的查詢記憶體被禁用。對於 MySQL 4.1+，InnoDB 在允許基於每個表訪問查詢記憶體時扮演交通警察。

從您的問題的角度來看，我想說刪除查詢記憶體的理由與其說是成本，不如說是 InnoDB 管理它的方式。

有關 InnoDB 如何與查詢記憶體互動的更多資訊，請閱讀《高性能 MySQL（第二版）》一書的第 213-215 頁。

如果您的所有或大部分數據都是 MyISAM，您可以按照您最初的想法使用 SQL_NO_CACHE。

如果您同時使用 InnoDB 和 MyISAM，則必鬚根據記憶體未命中率的高低為您的應用程序找到合適的平衡點。事實上，同一本書的第 209-210 頁指出了記憶體未命中的原因：

• 該查詢不可記憶體，要麼是因為它包含一個不確定的構造（例如 CURRENT_DATE），要麼是因為它的結果集太大而無法儲存。這兩種不可記憶體的查詢都會增加 Qcache_not_cached 狀態變數。
• 伺服器以前從未見過查詢，所以它從來沒有機會記憶體它的結果。
• 查詢的結果先前已被記憶體，但伺服器已將其刪除。這可能是因為沒有足夠的記憶體來保存它，因為有人指示伺服器將其刪除，或者因為它已失效

幾乎沒有不可記憶體的查詢導致高記憶體未命中的根本原因可能是：

• 查詢記憶體尚未預熱。那就是伺服器沒有機會用結果集填充記憶體。
• 伺服器正在查看以前從未見過的查詢。如果您沒有大量重複查詢，即使在記憶體預熱後也會發生這種情況。
• 有很多記憶體失效。

更新 2012-09-06 10:10 EDT

查看最新更新的資訊，您已query_cache_limit設置為 1048576 (1M)。這將任何結果集限制為 1M。如果您檢索更大的東西，它根本不會被記憶體。雖然您已query_cache_size設置為 104857600 (100M)，但這僅允許在完美世界中設置 100 個記憶體結果。如果您執行數百個查詢，碎片將很快出現。您還有 4096 (4K) 作為最小大小的結果集。不幸的是，mysql 沒有對查詢記憶體進行碎片整理的內部機制。

如果你必須有查詢記憶體並且你有這麼多記憶體，你可以執行以下操作：

SET GLOBAL query_cache_size = 0;
SELECT SLEEP(60);
SET GLOBAL query_cache_size = 1024 * 1024 * 1024;

為了清除查詢記憶體。您會失去所有記憶體的結果，因此請在非高峰時段執行這些行。

我還將分配以下內容：

• query_cache_size = 1G
• query_cache_limit = 8M

剩下 23G 的 RAM。我會提出以下幾點：

• innodb_buffer_pool_size = 12G
• key_buffer_size = 4G

剩下7G。這對於作業系統和數據庫連接應該足夠了。

請記住，鍵緩衝區僅記憶體 MyISAM 索引頁，而 InnoDB 緩衝池記憶體數據和索引。

還有一個建議：升級到 MySQL 5.5，這樣您就可以將 InnoDB 配置為多 CPU 和多執行緒以進行讀/寫 I/O。

請參閱我之前關於將 MySQL 5.5 與訪問 InnoDB 的多個 CPU 結合使用的文章

• Nov 24, 2011:為什麼 mysql 5.5 比 5.1 慢 (linux,using mysqlslap)
• Oct 05, 2011:查詢在一些較新的 MySQL 版本中執行時間較長
• Sep 20, 2011:多核和 MySQL 性能
• Jun 19, 2011:如何正確執行 MySQL 烘烤？

更新 2012-09-06 14:56 EDT

我清除查詢記憶體的方法相當極端，因為它會處理記憶體的數據並形成完全不同的 RAM 段。正如您在評論中指出的那樣，FLUSH QUERY CACHE（正如您所建議的）甚至RESET QUERY CACHE會更好。為了澄清起見，當我說“沒有內部機制”時，我的意思就是這個意思。需要進行碎片整理，並且必須手動完成。它需要 crontab’d。

如果你在 InnoDB 上比在 MyISAM 上更頻繁地執行 DML（插入、更新、刪除），我會說完全刪除查詢記憶體，我在一開始就說過。

引用自：https://dba.stackexchange.com/questions/23699