What sort of MySQL query cache should I choose considering the following...

  • Large, rapidly growing database (I got 500,000 rows).
  • UPDATEs practically never happen.
  • SELECTs are rare, but when they do happen, they'll usually happen in huge batches.
  • Lots of small INSERTs.
  • A row is not usually SELECTed multiple times within a small period of time.
  • I want to reduce memory footprint (server is using lots of memory).

Would I be looking for a large cache, or small cache?

From what I understand a cache isn't hugely important as repeated SELECTs are very rare.

  • 1
    You should use the one that performs the best when you benchmark a realistic workload on it. That said, just turn the cache off. It adds overhead to every query while it goes through the code to check the cache. – yoonix Apr 9 '15 at 3:08
query_cache_type = 0
query_cache_size = 0

Seriously, turning off the QC is probably best. especially based on what you said. Keep in mind that every INSERT (or UPDATE) to a table causes all entries in the QC to be purged. Furthermore, if the QC size is large, (say, over 50M), the purge time slows down the write.

The ENGINE's cache is important to both reads and writes. Generally MyISAM's key_buffer_size should be 20% of available RAM. OR InnoDB's innodb_buffer_pool_size should be 70% of available RAM.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy