1

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

1 Answer 1

1

First of all, there are two caches relevant to your query.

The Query cache uses SQL_(NO)_CACHE and 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. Limit query_cache_size to 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 PROCESSLIST and/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=DEMAND and use SQL_(NO)_CACHE.

2
  • Hi Rick, thanks for your reply. Our select query must perform group operation on big amount of data that will change very rarely or never. The data can't be limited and the query is well optimized. This group operation is performed on disk and is very slow, we haven't got enought buffer pool memory to store selected data there. So we decided to store results in QC, but as mentioned above new queries pop them out. Demand QC type will turn off cache for other queries, so it would store only Major data that is undesirable Commented Mar 19, 2015 at 17:48
  • If this is a Data Warehouse type report ("group with big..."), then think about Summary Tables. Regardless, consider asking us how to further optimize the query (preferably in a diff posting). Include EXPLAIN SELECT ... and SHOW CREATE TABLE.
    – Rick James
    Commented Mar 19, 2015 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.