Mysql – Reducing query cache for mostly INSERT-heavy database

cacheMySQLoptimization

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.

Best Answer

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.

UPDATE 2013-02-19 13:37 EDT

You just commented:

Also if I understand correctly , you want to say InnoDB use Both INDEX AND Data in memory , so it should have faster speed than MyISAM , but I saw MyISAM insert speed is 10 time faster than InnoDB, why ?

Believe it or not, it is possible to tune MyISAM to outperform InnoDB

Jul 06, 2012 : MySQL table with 100,000 records queried often
May 03, 2012 : Which is faster, InnoDB or MyISAM?
Sep 20, 2011 : Best of MyISAM and InnoDB

Mysql – Calculating time spent on query cache lock

Half a gigabyte is too large for the query cache. This write-up is several years old but still very applicable:

http://www.mysqlperformanceblog.com/2007/03/23/beware-large-query_cache-sizes/

In MySQL 5.5., you can benchmark this using profiling but the real problem isn't how long any particular query waits for the lock, because the answers will generally be all across the board and largely unrelated to the query itself or the tables, because the query cache is accessed by a global mutex that every query contends for.

There is apparently supposed to be a timeout to prevent long waits, but which does not work correctly, according to Bug #68573.

Your fix would be a more reasonable query cache.

I dissected and described a little bit of the query cache's inner workings here.

Best Answer

Related Solutions

Mysql – Does MySQL’s MyISAM use cache before insert

UPDATE 2013-02-19 13:37 EDT

Mysql – Calculating time spent on query cache lock

Related Question