Is there a way to tell MySQL that some queries has highter priority in cache and mustn't be popped out until it's data become invalid?
The problem is that innodb cache size is rather small and data of "Major" heavy weight queries are popped out quickly because of other frequently used queries.
So we want to say: "these Major query data MUST stay in cache. If cache is full, no Major data will be popped out until it become invalid. If cache is full of Major data, no other queries will use cache".
Some info: engine=InnoDB. Cache is turned on and directives SQL_(NO)_CACHE are not used in application queries. Cache is big enought to store all Major data and much "hot" data, but too small to store even 5% of all queries data. It's desirable to use cache on all queries but they shouldn't replace Major data
Best Answer
First of all, there are two caches relevant to your query.
The Query cache uses
SQL_(NO)_CACHEand is the less important cache. For production systems with lots of writes, you may as well turn off the QC. Every write to a table flushes all entries in the QC for the table(s) involved. That leads to another point: A large QC can lead to a lot of flushing activity. Limitquery_cache_sizeto no more than about 50M. The QC can help with CPU.The buffer_pool is the important cache for InnoDB. You should give about 70% of RAM to
innodb_buffer_pool_size. That is where blocks of InnoDB data and indexes are cached. It helps because it avoids much of the I/O.Often, the most important thing to do is to identify the slowest queries (using
SHOW PROCESSLISTand/or the slowlog) and work on speeding them up. Sometimes adding an index, sometimes a minor change to a SELECT, etc, can speed up the system far more than tweaking the cache sizes.The buffer_pool has a way to avoid letting a table scan blow out the cache. The QC has no mechanism like what you are asking for.
If you do keep the QC on, then set
query_cache_type=DEMANDand useSQL_(NO)_CACHE.