MySQL Optimisation Tip #1 - Turn on the query cache
The Query Cache
If your using MySQL in production you should turn on the query cache - this is off by default ( even if you have installed using the wizard and choosen "dedicated server" ).
The query cache basically remebers the results of your queries and saves them from being executed twice unnecessarily resulting in huge performance improvements.
Bare in mind that any inserts or updates will reset the parts of the query cache that relate to the affect tables. Luckily most applications read from the table much more often that they write data into them.
How to turn on the Query Cache
- Open your my.ini (or my.cfg) file in notepad and set query_cache_type to 1. There are 3 settings for this: 0 (disable / off), 1 (enable / on) and 2 (on demand).
Tip: If you can't find this in your config file, so just add it in.
- Now you need to tell MySQL how much memory space to use. For this, find (or add) the query_cache_size option and set it to something that makes sense. This is a global setting so all sites and applications on the server will share this. You could try 30MB to start with
query-cache-size = 20M
- You will need to restart MySQL for the caching to begin.
How to know of the cache is being used
After restarting your server, execute the following SQL:
SHOW STATUS LIKE 'q%';
You will get something like:
Qcache_free_blocks 1613
Qcache_free_memory 202554688
Qcache_hits 679493
Qcache_inserts 207317
Qcache_lowmem_prunes 0
Qcache_not_cached 4532
Qcache_queries_in_cache 3488
Qcache_total_blocks 8788
The important two are Qcache_queries_in_cache and Qcache_not_cached.
Qcache_queries_in_cache is the total number og queries that are currently cached in memory.
Qcache_not_cached is the total number of queries that were executed where MySQL could not use a cached version.
Tags:
Comments [1]
Peter Coppinger aka Topper is a neurotic web monster who spends most of his chaotic life developing ColdFusion web applications when not drinking himself into a stupor and scheming his plans for world dominance.
