MySQL Query Cache

As soon as I opened the website in the morning, I saw the latest article on MySQL query cache released by the Percona website:

https://www.percona.com/blog/2015/08/07/mysql-query-cache-worst-enemy-best-friend/

There is also an article on its comments:

https://blog.gslin.org/archives/2015/08/07/5906/percona-%E5%B0%8D-mysql_query_cache-%E7%9A%84%E6%B8%AC%E8%A9% a6-%e4%bb%a5-magento-%e7%82%ba%e4%be%8b/

The main content is:

1. mysql's query cache has one drawback:

The query cache is well known for its contentions:a global mutex have to be acquired for any read or write operation, whic h means that any access is serialized. This wasn't an issue-years ago, but with today's multi-core servers, such serialization is the best of the Mance.

Both the read and write of the query cache must be given a global lock, which must be serial. So when select is a lot of concurrency, it inevitably creates a lock competition. Reduce performance.

But the original is also called:

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

A cache hit, as long as dozens of milliseconds, if there is no hit, and walk the primary key index, it takes hundreds of milliseconds. But he doesn't think about it here, what if we put the primary key index in the cache?

Even so, the result of the actual test in the original text shows that close query cache,mysql has better concurrency and throutput.

The second link was followed by a comment:

I have done this kind of experiment, the result is also very similar, the main reason is that the query cache is still cost, the hard spec is a little bit, but when the query number exceeds a certain level, as shown in the text of the results, the performance will be reduced, buffer open Also Useless, instead of using query cache to guarantee output performance . ( very simple, that is, the global lock competition is the result )

If you want to open the query cache, as long as the 1MB is enough , there will not be too much promotion

As long as the software cache has this problem, to understand this problem, the server level CPU L3 cache is very large, can effectively improve performance and output.

About PK search, which is related to MySQL key buffer. MySQL key buffer is really big, because this is put index, to open enough to put all the index, too small to put all the index, there is no guarantee of effectiveness . (But Key_buffer_size seems to have a role in MyISAM, right?) )

MySQL Query Cache