by
udid
tags:
4 comments

MySQL (5.5) query cahce is disabled by default (size is 0).

 

mysql> SHOW VARIABLES LIKE '%query_cache%';
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 0 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
6 rows in set (0.06 sec)

 

To enable is you should set a size, e.g. 20MB

mysql> SET GLOBAL query_cache_size=20000000;
Query OK, 0 rows affected (0.00 sec)

 

learn more:
http://www.databasejournal.com/features/mysql/article.php/3110171/MySQLs...
 

4

Comments

shlomo
8:39 Jun 29th
Just a short note, if you want the change to be persistent after a reboot or a restart then you can set the value using my.ini: [mysqld] query_cache_size=32M

udid
13:53 Aug 12th
Right, Thanks Shlomo, So if you want for example a bigger temporary table size (to avoid mysql from using disks), on linux, you should edit /etc/my.cfg and add those two lines to [mysqld] tmp_table_size=100000000 max_heap_table_size=100000000

shahar
11:18 Jun 29th
Why would you want to use query cache in the first place? It's known to be very inefficient and using it has a negative effect on overall performance. Use memcache instead.

shlomo
21:59 Jun 29th
I didnt say I would use query cache, i just specified how to persist its settings. You are right in stating that it is inefficient and it is true that the query cache is invalidated as soon as the table is modified, for instance, by an INSERT statement. On top of that as far as I recall, the cache can not be distributed across mysql instances. I actually conducted several tests with memcached, however that was compared to EhCache and my results coupled with others reported on the internet proved that for caching Hibernate objects in is better to use an internal JVM cache such as EhCache rather then using memcached. The main reason being that memcaced runs as a separate process and the objects passed to its cache are serialized. I know that memcahed is excellent for many many distributed applications and I would definitely consider using it in future applications. See also (one of my favorite web sites): http://highscalability.com/bunch-great-strategies-using-memcached-and-mysql-better-together