

Caching MySQL queries - sangguine

Hi. memcached is not really good for MySQL. Then, what is a good way or practice to cache MySQL queries? Do you recommend any reading on caching?
======
bigbee
The easiest way is to use MySQL's own query cache. Increasing its size yields
wonderful results.

On linux, edit my.cnf and add the following:

    
    
      [mysqld]
      ...
      query_cache_type=1
      query_cache_limit=1M
      query_cache_size=128M # or more - depends on you total  available RAM
      key_buffer=128M # or more
    

Note that you'll need to restart mysql to make these changes count

------
andrewf
The biggest thing you need to sort out when caching is exactly how well synced
your cache needs to be.

Once you've written fresh data to the database, is it okay for you to keep
receiving stale data in response to later queries?

If you've got multiple web servers, do they all have to return the same data,
or is it okay if some lag behind the others?

If you're happy to serve stale (versus some tunable time metric) data
inconsistently across different servers, then just find a database client
library that does client-side result set caching (ADODB for PHP will do this,
either in the filesystem or against memcache) and run with it.

------
gtani
[http://www.databasejournal.com/features/mysql/article.php/31...](http://www.databasejournal.com/features/mysql/article.php/3110171)
<http://dev.mysql.com/doc/refman/5.0/en/query-cache-how.html>
[http://www.mysqlperformanceblog.com/2008/01/29/how-mysql-
que...](http://www.mysqlperformanceblog.com/2008/01/29/how-mysql-query-cache-
works-with-transactions/)

------
aaroneous
What do you mean memcached is not good for MySQL?

~~~
gnaritas
He's probably wanting to cache entire result sets instead of individual
objects so he's running into problems wanting to expire lots of duplicate data
when the db gets updated. Seems to be common misuse of Memcached when people
first try it.

Memcached is perfectly fine to use with MySql, just don't cache query results
directly, cache the individual rows so there's only ever one copy of that row
cached. You can then update the cache when you update the db. If you need to
cache queries, cache just the keys for the result of the query and use them to
go back and fetch the cached rows individually from Memcached. Use a multi get
for faster retrieval.

Memcached is a distributed object cache, keep each object in it only once and
you won't need to expire stuff. You could also use the object version as part
of the cache key which will auto expire older version by making all the
clients look for the newer version. Old versions will be pushed out naturally
as newer version fill up the cache.

~~~
mariorz
MySQL has it's own query cache, if that is what he means he should just
increase the query cache size really.

