MySQL table cache

Dit is voorlopig het laatste deel over MySQL tuning. In de eerdere delen zagen we al dat een juiste instelling van de query_cache en key_buffer variabelen voor een betere performance van MySQL kunnen zorgen. De table_cache variabele verhoogt helaas niet de performance. Toch is een correcte instelling wel essentieel. Een foute (te lage) instelling beinvloedt de performance van MySQL in negatieve zin. Eerst maar eens onderzoeken wat de standaard instelling van MySQL is voor de table_cache.

mysql> show variables like 'table_cache';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache   | 64    |
+---------------+-------+
1 row in set (0.00 sec)

De betekenis van deze variabele is dat er maximaal 64 tabellen tegelijk door MySQL kunnen worden geopend. Dat lijkt heel veel als je een database hebt met slechts tien tabellen. Echter dit is de maximum waarde voor alle connecties. Dus als er acht clients connecten, en ze doen alle acht een query over jouw database van tien tabellen (waarbij ze dan wel alle tien in de query moeten voorkomen) dan zal MySQL 80 tabellen tegelijk willen openen en loop je dus tegen je table_cache aan. Vanaf dat moment zal de performance dus onbedoeld gaan afnemen.
Een vrij simpele rekensom leert ons wat de minimum waarde voor de table_cache moet zijn:

max_connections * maximum tabellen in een join

Dus als je maximaal 150 connecties tegelijk toelaat aan de MySQL database, en je hebt queries over maximaal 5 tabellen tegelijk zal de table_cache op minimaal 750 moeten staan. Hierbij ga ik er wel vanuit de het maximaal aantal connecties ook daadwerkelijk bereikt wordt en het niet een veilige hoge waarde is die nooit bereikt zal worden.

Controle.

Je kan door de status variabelen van MySQL uit te vragen er achter komen of het noodzakelijk is om de table_cache aan te passen.

mysql> show status like 'open%tab%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 24    |
| Opened_tables | 30    |
+---------------+-------+
2 rows in set (0.00 sec)

In het huidige voorbeeld is er geen enkele reden om de table_cache variabele aan te passen, het aantal open_tables ligt ver onder het maximum en ook het aantal opened_tables (en later weer afgesloten ...) is onder de table_cache waarde. Anders wordt het wanneer je de volgende waardes terugkrijgt bij dezelfde query.

mysql> show status like 'open%tab%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 54    |
| Opened_tables | 90    |
+---------------+-------+
2 rows in set (0.00 sec)

Je ziet dat MySQL momenteel 54 tabellen open heeft, maar veel belangrijker in totaal 90 tabellen open heeft gehad. Ergo, om aan alle verzoeken te voldoen zijn er tabellen afgesloten om ruimte te maken voor andere tabellen. De waarde van de table_cache kan hier beter worden opgehoogd naar een waarde van 100. Als volgt in je configuratie bestand aanpassen:

set-variable=table_cache=100

Je kan helaas niet de table_cache ongestraft ophogen. De table_cache is nauw verbonden met de variabele open_files_limit. Deze staat standaard op 1024, en moet altijd minimaal twee keer de waarde van table_cache hebben. En daar schuilt het gevaar van de beperking van je operating system. Want die kan slechts een beperkt aantal open files per proces aan. Op een niet specifiek getuned operating system is een waarde van 512 in de regel het maximum om probleemloos te kunnen draaien.
Vooral bij webhosting bedrijven die heel veel weblogs draaien in een MySQL database kan dit dus een enorm probleem opleveren.

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

sinds MySQL 5.1 is de variabele table_cache veranderd in table_open_cache

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