Percona 對 mysql_query_cache 的測試 (以 Magento 為例)

Percona 的人以現在的觀點來看 mysql_query_cache:「The MySQL query cache: Worst enemy or best friend?」。

起因主要也是懷疑 query cache 是 global mutex 在現在的硬體架構 (主要是 CPU 數量成長) 應該是個負面的影響,但不確定影響多少:

The query cache is well known for its contentions: a global mutex has to be acquired for any read or write operation, which means that any access is serialized. This was not an issue 15 years ago, but with today’s multi-core servers, such serialization is the best way to kill performance.

這邊就有點怪了,PK search 應該是個位數 ms 等級才對 (一般 EC 網站的資料量都應該可以 memory fit),不知道他是怎麼測的:

However from a performance point of view, any query cache hit is served in a few tens of microseconds while the fastest access with InnoDB (primary lookup) still requires several hundreds of microseconds. Yes, the query cache is at least an order of magnitude faster than any query that goes to InnoDB.

anyway,他實際測試兩個不同的 configuration,首先是打開 query cache 的:

再來是關閉 query cache 的:

測試的方式在原文有提到,這邊就不抄過來了。測試的結果可以看到關閉 query cache 得到比較好的 thoughput:

Throughput scales well up to somewhere between 10 and 20 threads (for the record the server I was using had 16 cores). But more importantly, even at the higher concurrencies, the overall throughput continued to increase: at 20 concurrent threads, MySQL was able to serve nearly 3x more queries without the query cache.

跟預期的差不多,硬體的改變使得演算法也必須跟著改,不然就會遇到問題...

3 thoughts on “Percona 對 mysql_query_cache 的測試 (以 Magento 為例)”

  1. 這個我有做過類似的試驗, 結果也很類似, 主要原因是 query cache 還是有 cost, 硬體規格不高時的確有點用處, 但是當 query 數量超過某個級數後, 就像文中數據顯示的結果一樣, 性能就會下降, buffer 開大也沒用, 反而要停用 query cache 才能保證輸出效能.

    如果要開 query cache, 只要開 1MB 就夠了, 開再多也不會有太大的提升

    只要是 software cache 都有這個問題, 為了解決這個問題, server 級的 CPU 的 L3 cache 都很大, 可以有效提升效能及輸出.

    關於 PK search, 跟 mysql 的 key buffer 有關. mysql 的 key buffer 的確要開大, 因為這是放 index 用的, 要開到足夠放進所有的 index, 開太小放不進所有 index, 就不能保證效能了.

  2. 但是,key_buffer_size只对MyISAM表起作用的,增大 key buffer对innodb没有作用吧。

Leave a Reply

Your email address will not be published. Required fields are marked *