Tag: multi

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.

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