MySQL query cache

De MySQL query cache kan de performance van de database ennorm vergroten. Standaard staat de query cache van MySQL aan, maar met een beetje betere keuze van de standaard waardes is het mogelijk om betere performance en betere resultaten te boeken. In deze howto alles over de query cache, wat het doet en hoe je hier optimaal gebruik van kan maken.

Wat doet de query cache? In de query cache slaat mysql zowel de query (het select statement) als het resultaat daarvan in geheugen op. Wanneer je hetzelfde select statement opnieuw naar de database afvuurt, dan zal mysql het resultaat uit geheugen toveren in plaats van de gegevens van schijf te moeten lezen. Als het dus vaak voorkomt dat exact dezelfde vraag aan de database wordt gevraagd, zoals het geval is bij bijvoorbeeld een weblog als deze, dan kan een juiste instelling van deze waarde een snelheidsverbetering opleveren.

aan de slag

Allereerst gaan we eens onderzoeken hoe de query cahce op dit moment is ingesteld. dat doen we als volgt:

mysql> show variables like '%query_cache%';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| have_query_cache  | YES     |
| query_cache_limit | 1048576 |
| query_cache_size  | 0       |
| query_cache_type  | ON      |
+-------------------+---------+
4 rows in set (0.00 sec)

Dit zijn de MySQL defaults, en het valt je misschien al op, de query_size is 0 en zodoende wordt er toch niets gecached. Vlug aanpassen naar 16MB ofzo.
Voeg de volgende regel toe aan je mysql.ini of my.cnf (afhankelijk van je patform)
set-variable=query_cache_size=16M
en herstart mysql.
mysql> show variables like '%query_cache%';
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| have_query_cache  | YES      |
| query_cache_limit | 1048576  |
| query_cache_size  | 16777216 |
| query_cache_type  | ON       |
+-------------------+----------+
4 rows in set (0.00 sec)
dat ziet er beter uit.
Test en huiver.
select * from nucleus_item;
1518 rows in set (0.28 sec)
gevolgde door een o zo voorspelbare
select * from nucleus_item;
1518 rows in set (0.04 sec)

Kijk dat is toch 7 maal zo snel. Dat is prettig. Laten we eens kijken wat er in de cache zit.
mysql>  show status like '%qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_queries_in_cache | 1        |
| Qcache_inserts          | 1        |
| Qcache_hits             | 1        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_free_memory      | 15814064 |
| Qcache_free_blocks      | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+
8 rows in set (0.00 sec)

Precies wat we verwachten, eenmaal een query gecached, en eenmaal uit geheugen gehaald.

de verwarring

Helaas gebeurd er niet altijd wat je verwacht. Gelukkig is dat wel allemaal verklaarbaar als je weet wat er aan de hand is. Wanneer ik

mysql> SELECT * FROM nucleus_item;
1518 rows in set (0.21 sec)

En die is weer langzaam.
mysql>  SHOW STATUS LIKE '%qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_queries_in_cache | 2        |
| Qcache_inserts          | 2        |
| Qcache_hits             | 1        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_free_memory      | 14860192 |
| Qcache_free_blocks      | 1        |
| Qcache_total_blocks     | 6        |
+-------------------------+----------+
8 rows in set (0.00 sec)

Het blijkt dat de query cache 'case-sensitive' is. Het is zelfs nog strikter, namelijk byte-sensitive (spaties tellen dus ook mee), het komt erop neer dat je telkens exact dezelfde query moet sturen om gebruik te kunnen maken van de cache.
Dan zijn er ook nog de grote resultsets. Wanneer een query een resultset groter dan 1MB terugstuurt, dan wordt die ook niet in de cache opgeslagen. Wanneer je wel grotere resultsets wilt opslaan in de cache, juist omdat er maar heel weinig maar wel heel grote queries in je applicatie worden gebruikt, dan is het zaak om de query_cache_limit op te hogen naar bijvoorbeeld 2MB. Daartoe neem je de volgende regel op in je configuratiefile
set-variable=query_cache_limit=2M
Vervolgens mysql weer herstarten, en je kan weer opgelucht ademhalen. Ook de grotere queries worden vanaf nu gecached.
Om te waarborgen dat de cache gelijk loopt met wat er op schijf in de database staat zal mysql altijd alle relevante queries uit de cache halen wanneer er iets in een tabel wordt geschreven (of weggehaald). Nadat ik
mysql> update nucleus_item set ititle='' where inumber=8;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
Heb uitgevoerd, dan zal mijn zorgvuldig aangelegde cache leeg moeten zijn, de resultaten zijn namelijk verouderd!
mysql>  SHOW STATUS LIKE '%qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_queries_in_cache | 0        |
| Qcache_inserts          | 2        |
| Qcache_hits             | 1        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_free_memory      | 16768448 |
| Qcache_free_blocks      | 1        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

Er zijn meer gevallen denkbaar waarin de query niet wordt gecached, en als je erover nadenkt is dat ook allemaal verklaarbaar. Het gebruik van de volgende functies in een query zorgt ervoor dat het resultaat niet in de cache komt:

  • CURDATE
  • CURRENT_DATE
  • CURRENT_TIME
  • CURRENT_TIMESTAMP
  • CURTIME
  • NOW
  • RAND
  • RELEASE_LOCK
  • SYSDATE
  • UNIX_TIMESTAMP()
  • USER

Finetuning

Als je je applicatie door en door kent, dan zijn er situaties denkbaar waarbij je volledige controle over de cache wilt hebben. In een database waar sommige tabellen elke seconde worden geschreven heeft het weinig zin diezelfde tabellen ook in de querycache te houden. De mogelijkheid om hierover controle te hebben wordt je ook door Mysql geboden. Hier zit echter wel een keerzijde aan, elke query die je wel in de cache opgeslagen wilt zien zal je moeten aanpassen.
Allereerst moeten we het type cache dat mysql gebruikt aanpassen. Dit zetten we op 'on demand'. Weer in de configuratiefile toevoegen

 
set-variable=query_cache_type=2

en mysql weer een slinger geven.
Wanneer je nu een willekeurige query draait, dan zal die niet worden gecached. Echter als je weet dat een bepaalde query heel lang (rekbaar begrip, maar zeker langer dan een minuut) hetzelfde resultaat teruggeeft kan je met het extra sql commando SQL_CACHE mysql de opdracht geven de query wel te cachen (het on-demand principe)

select SQL_CACHE * from nucleus_item;

zal dus wel gecached worden.

Als je de mogelijkheid hebt, speel dan eens met de waardes en probeer een optimum te vinden. Momenteel zit ik druk met mijn productie database te testen en lukt het om 3000 queries in de cache te hebben die gemiddeld 1000 keer worden geraakt. Of om het anders te zeggen ik heb 300.000 cache hits gehad. Als je dan bedenkt dat er 600.000 queries zijn afgevuurd, dan is een score van 50% uit geheugen geen slecht resultaat.

previous item: next item:
thank you for watching  Creative Commons License