How to improve mysql performance using CACHING

A deep analisys of a performance bottleneck could be long and is based on different kind of data (OS metrics, IO performance tests, DB wait stats and so on…). Before starting with this (or simply after a new delivery) some simple checks could help you to correct rapidly some of the common (and bigger) bottlenecks you can find in a low-performance installation.
One of this is cache sizing.

A cache disabled or not correctly configured is a common reason of low-performance problems.
MySQL engine use a result-caching to improve query speed. A query result is cached in memory to avoid slow IO reading operations. Mysql use the query_cache_size parameter to size the engine cache. A result is saved in the cache only if it’s larger then the query_cache_min_res_unit parameter but smaller then the query_cache_limit.

A query result is kept in memory until:
– the result is valid (data are not modified)
– there are no more-used results using spaces in cache dedicated memory

A good sizing of cache instance parameters is based on instance activity. My suggestion are:
– Start with a simple configuration using system memory available (avoiding paging). note: by default mysql cache is DISABLED
– Start keeping query_cache_min_res_unit little.
– Check istance metrics after some days of normal database activity to understand how you can tune better cache parameters

This is an example of an installed configuration:

mysql> show variables like '%query_cache%';
| Variable_name                | Value    |
| have_query_cache             | YES      |
| query_cache_limit            | 2097152  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 33554432 |
| query_cache_type             | ON       |
5 rows in set (0.00 sec)

After some days of normal instance activity start watching some metrics to understand whats heppening.
Those values are self explained

mysql> show status like 'QCache%';
| Variable_name           | Value    |
| Qcache_free_blocks      | 957      |
| Qcache_free_memory      | 15523704 |
| Qcache_hits             | 1978478  |
| Qcache_inserts          | 132298   |
| Qcache_lowmem_prunes    | 6037     |
| Qcache_not_cached       | 5969     |
| Qcache_queries_in_cache | 3389     |
| Qcache_total_blocks     | 8396     |
8 rows in set (0.00 sec)

Talking about performances there are no “top configuration” usable. You have to work with users and system integrators to understand:
– Any application suggested cache configuration
– In low performance situations: what is the performance target ?
– Are there any test query usable to test configuration tuning?

For a deeper explanation of all mysql cache parameters start from here:

About Paolo Zaboi 44 Articles
Fermamente convinto che "I predatori dell'Arca Perduta" sia uno dei migliori film della storia del cinema, vive e passa il suo tempo in quel di Milano. Adora i film commercialissimi, tutto ciò che è Marvel, le vecchie avventure grafiche della Lucas e le serie TV. Giocatore (nel poco tempo libero) su Pc e Playstation, con fiducia e speranza, attende dagli anni '80 l'uscita della prossima console Atari. Ha creato questo blog un po' per spirito di condivisione... è un po' per scrivere in libertà di tutto quello che gli piace. Odia leggere i lunghi profili sui blog per questo il resto lo trovate solo su Linkedin: ------ Firmly convinced that "Riders of the Lost Ark" is one of the best movies in cinema history, he lives and spend all his time in Milan. He loves movies, everything Marvel, Lucas's old graphic adventures and TV series. Player (in leisure time) on PC and Playstation, with confidence and hope, expects the release of the next Atari console from the 1980's. He created this blog (maybe...) for sharing spirit ... or simply to write free about anything he loves. He hates reading long profiles on blogs so all the rest is on Linkedin:

Be the first to comment

Leave a Reply

Your email address will not be published.