MySQL query cache with time based expiration

Question

The database we use has a read and write intensive load. Some read queries are heavy.

Unfortunately the Query Cache cannot be used as it is invalidated too quickly.

We wanted to avoid the classic Memcached/MySQL couple to simplify the architecture and decrease connections/latency.

Is there a strategy to add a "time based cache" in MySQL? (ideally per collection)

EDIT:

• Each query group has a different TTL (from 5 seconds to several days).

• Rick James: What's the value of innodb_buffer_pool_size? How much RAM?

Answer 1

I'll assume your database is all InnoDB when answering this question.

What you are asking for is not possible for the Query Cache. The mechanisms you have in mind do exist with the InnoDB Buffer Pool.

What you should try is the following:

1. Disable the Query Cache (Why query_cache_type is disabled by default start from MySQL 5.6?)
2. Make the InnoDB Buffer Pool Resistant to Scans
   • Increasing innodb_old_blocks_pct
   • Increasing innodb_old_blocks_time

This controls what and how long blocks of data and index are to stay inside the Buffer Pool

To release blocks after a while, you would change innodb_old_blocks_pct back to 37

• Toto: Interesting strategy. Unfortunately, it will not be possible in our specific case as the TTL will depend on the query type. :(

Answer 2

MySQL can't do that. All query cache entries that use a table are invalidated when any change is made to that table to make sure query cache responses are always correct. This behavior can't be changed.

Depending on your data, you might be able to separate your data into an old or rarely changing table and a newer frequently changing table so you can get more use out of the query cache.

Another possibility is to periodically create materialized views. The utility of this approach depends heavily on the data and queries, but if you can make it work it can work very well.