MySQL Key buffers

In een andere howto hebben we gekeken hoe we ervoor kunnen zorgen dat MySQL queries kon oplsaan in zijn cache waardoor er een performance winst geboekt kan worden wanneer dezelfde query nogmaals wordt uitgevoerd. Voor de keys of indexen bestaat er eenzelfde mechanisme. Ik zal bij de behandeling ervan niet ingaan op de uitbreiding van dit principe in versie 4.1 en hoger.

Laten we eerst maar eens kijken wat de defaults zijn die MySQL gebruikt voor de key_buffer_size.

mysql> show variables like 'key_%';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388600 |
+-----------------+---------+
1 row in set (0.04 sec)

In tegenstelling tot de query_cache staat de key_buffer standaard wel aan op een waarde van 8MB. Dus we kunnen direct gaan experimenteren zonder dat we de waarde moeten aanpassen. Het principe achter de key_buffer is identiek als achter de query cache. Wanneer MySQl gebruik kan maken van een index, dan wordt er een of meer 'blocks' uit de index vanaf de schijf in de key buffer gelezen. Een volgende keer dat dezelfde index opnieuw wordt aangesproken controleert MySQL of dat gedeelte van de index al in de buffer staat. Is dat het geval, dan zal de index uit het geheugen worden gebruikt, en hoeft er niet naar de schijf te worden gekeken.
Voor de experimenten zal ik de query_cache uitzetten, omdat de resultaten anders sterk beinvloed worden door deze cache.
Allereerst een query waarvan we zeker weten dat die een index gebruikt

select * from nucleus_item where inumber = 1518;
1 row in set (0.08 sec)

En dan de status variabelen uitlezen die ons vertellen hoe het ervoor staat met de key_buffer

mysql> show status like 'key_%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Key_blocks_used    | 2     |
| Key_read_requests  | 2     |
| Key_reads          | 2     |
| Key_write_requests | 0     |
| Key_writes         | 0     |
+--------------------+-------+
5 rows in set (0.00 sec)

We lezen dat MySQL 2 blokken uit de index heeft ingelezen, en deze in de key_buffer heeft geplaatst (key_reads en key_blocks_used). Daarnaast zijn er twee requests geweest voor index informatie. Dat leert ons dat de index die zojuist is gebruikt een fysieke ruimte van twee blokken in beslag neemt.
Vervolgens voeren we dezelfde query nog eens uit, om aan te tonen dat ook de key_buffer een heilzaam effect heeft op de performance (bedenk dat de query cache uit staat, dus alle performance winst wordt nu zuiver uit de key_buffer gehaald!)

select * from nucleus_item where inumber = 1518;
1 row in set (0.00 sec)

En als we dan gaan kijken naar de status van de key_buffer, dan zien we

mysql> show status like 'key_%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Key_blocks_used    | 2     |
| Key_read_requests  | 4     |
| Key_reads          | 2     |
| Key_write_requests | 0     |
| Key_writes         | 0     |
+--------------------+-------+
5 rows in set (0.00 sec)

Het verschil met de vorige keer is dat er wel twee extra verzoeken om index informatie zijn bijgekomen (key_read_requests). Het feit dat het aantal lees acties niet is toegenomen toont aan dat we niet nogmaals naar schijf zijn gegaan om deze informatie op te zoeken. Dat geeft de burger moed. Eens zien wat er gebeurd wanneer we een nabijgelegen record proberen te benaderen.

select * from nucleus_item where inumber = 1517;
1 row in set (0.00 sec)

Wederom vliegensvlug antwoord. Een blik op de status bevestigd ons vermoeden:

mysql> show status like 'key_%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Key_blocks_used    | 2     |
| Key_read_requests  | 6     |
| Key_reads          | 2     |
| Key_write_requests | 0     |
| Key_writes         | 0     |
+--------------------+-------+
5 rows in set (0.00 sec)

Ook deze informatie zat al in het geheugen. Anders dan de query cache waarbij we alleen snelheidswinst boeken wanneer exact dezelfde informatie wordt opgevraagd werkt de key buffer op een verzameling van gegevens. Nu is het juist aanbrengen van een index op je tabellen sowieso een moeilijke maar zeer belangrijke taak, omdat indexen ervoor zorgen dat MySQL niet alle gegevens af hoeft te lopen op zoek naar de gevraagde informatie. Indien de informatie op basis van een index wordt opgevraagd zal MySQL slechts een klein gedeelte van de gegevens hoeven te controleren. Wanneer deze index informatie in geheugen ligt zal dit opzoeken nog sneller gaan.
Een index laat zich het best vergelijken met een telefoonboek. Wanner ik vraag om het telefoonnummer van meneer jansen uit deventer, dan zal je die informatie redelijk snel kunnen vinden. Omdat ik de informatie vraag op basis waarop het telefoonboek is geindexeerd; plaatsnaam en achternaam. Wanneer ik echter vraag wat de naam is die bij het telefoonnummer 0570622876 hoort, dan zal je een zeer lange tijd bezig zijn om met de gevraagde informatie terug te komen. Een telefoonboek is niet geindexeerd op telefoonnummer. Wellicht kan je er nog achter komen dat 0570 het kengetal van Deventer is, maar vervolgens zal je toch alle 40.000 duizend telefoonnummers in Deventer af moeten lopen om de juiste naam erbij te vinden (nee het is niet mijn telefoonnummer). Exact opdezelfde manier werken databases en indexen. Als je de database om gegevens vraagt op basis van een niet geindexeerd veld, dan zal er 'sequentieel' door de database worden gezocht om alle gevraagde informatie bij elkaar te krijgen.
Een voorbeeld.

mysql> select * from nucleus_item where idraft = 1;
12 rows in set (0.18 sec)

En wanneer we vervolgens kijken naar de status van de key buffer dan zien we

mysql> show status like 'key_%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Key_blocks_used    | 2     |
| Key_read_requests  | 6     |
| Key_reads          | 2     |
| Key_write_requests | 0     |
| Key_writes         | 0     |
+--------------------+-------+
5 rows in set (0.00 sec)

dat er niets veranderd is. En dat klopt ook aangezien het veld 'idraft' niet is geindexeerd.

Op jacht naar het optimum

Dus je indexen liggen allemaal goed. ( zet de slowquery log aan en gebruik explain als je twijfelt of op zoek bent naar verbeteringen) en je wilt graag weten hoe groot je de key_buffer moet / mag maken. Allereerst ben je beperkt door de fysieke hoeveelheid RAM in de machine. Wat je ook instelt, je moet altijd ruim onder deze hoeveelheid blijven omdat je operatingsystem anders gaat swappen, en dus op schijf geheugen gaat nadoen. En dat is alles behalve snel. Een goede regel is dat je een kwart tot de helft van het beschikbare geheugen aan de key buffer moet geven. Dus als je server 512MB aan intern geheigen heeft kan je je geen buil vallen aan een key buffer van 128MB. Dit kan je weer in je configuratie bestand opnemen zodat MySQL altijd netjes met deze waarde opstart.

set-variable=key_buffer_size=128M

Mysql opnieuw opstarten en dan monitoren wat de key_buffer doet. Insiders beweren dat op een operationele database de verhouding tussen Key_reads en Key_read_requests 1:100 of meer moet zijn. Of als je de volgende formule hanteert: 100 - (key_reads/key_read_requests) * 100 je een waarde van 99 of hoger moet krijgen. Deze waarde zal je uiteraard niet direct na de herstart van MySQL krijgen omdat de buffer dan helemaal leeg is en er dus vaker naar de schijf moet worden gekeken. Echter wanneer je een tijdje draait moet je lanzaam naar deze verhouding toegroeien. Op mijn huidige productie database kan je zien dat de verhoudingen nog niet optimaal zijn

mysql> show status like 'key_%';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| Key_blocks_used    | 16334   |
| Key_read_requests  | 1367425 |
| Key_reads          | 16122   |
| Key_write_requests | 12005   |
| Key_writes         | 17691   |
+--------------------+---------+
5 rows in set (0.00 sec)

Maar de keybuffer is op dit moment slechts voor 15% gevuld, dus is dit zich nog aan het optimaliseren. Met het volgende php scriptje kan je met een browser zelf de verhouding van jouw database controleren.

< ? php
// replace your host, user and password!
$connection = @mysql_connect($MYSQL_HOST, $MYSQL_USER, $MYSQL_PASSWORD)
    or connectError('Could not connect to MySQL database.');
// Get some db info ...
$query = "SHOW STATUS LIKE 'key_reads'";
$res = mysql_query($query);
$obj = mysql_fetch_array($res);
$key_reads = $obj['Value'];
$query = "SHOW STATUS LIKE 'key_read_requests'";
$res = mysql_query($query);
$obj = mysql_fetch_array($res);
$key_read_requests = $obj['Value'];
$ratio = 100 - ($key_reads / $key_read_requests) * 100;
echo "Cache hit ratio: ".round($ratio,2);
? >

Succes.

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