MySQL Tuning mit Query Cache
Ein unbegründetes Schattendasein fristet in den meisten Distributionen der Query Cache in MySQL. Doch richtig gesetzt bringt dieser einen gehörigen Performance Boost.
Die Funktionsweise erklärt sich am besten, wenn man den Cache einschaltet und die Auswirkungen betrachtet. Es kann dabei nichts passieren, aber wie immer gilt:
Zuerst auf einem Entwicklungssystem testen und dann produktiv einsetzen!
Betrachten wir das System des Query Caches in MySQL.
linux ~ # mysql
mysql> show variables like 'query%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+---------+
Betrachtet man diese Variablen, ist zwar der Cache an (query_cache_type = ON), aber er hat die Größe 0 (query_cache_size = 0). Damit ist der Query Cache dann wieder abgeschalten.
Man schaltet den Cache wie folgt ein:
mysql> set global query_cache_size=1000000;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'query%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 999424 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+---------+
Um die Funktionsweise zu überprüfen geht man wie folgt vor.
linux ~ # mysql mysql
mysql> show status like 'qc%';
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 990760 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 5 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+--------+
mysql> select * from cache_pages;
[...]
6 rows in set (0.06 sec)
mysql> show status like 'qc%';
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 805368 |
| Qcache_hits | 0 |
| Qcache_inserts | 2 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 6 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 7 |
+-------------------------+--------+
Jetzt sieht man mehrere Dinge. Der Wert Qcache_free_memory hat sich verringert (die Abfrage wurde in den Cache geschrieben), der Wert Qcache_queries_in_cache hat sich erhöht (Anzahl der Abfragen im Cache). Jetzt wird die selbe Abfrage nochmals gestartet.
mysql> select * from cache_pages;
[...]
6 rows in set (0.00 sec)
Wichtig dabei ist die Dauer der Abfrage (0.00 sec). Das deutet darauf hin, dass die Abfrage aus dem Cache beantwortet werden konnte. Das sieht man auch an den Status Variablen des Caches.
mysql> show status like 'qc%';
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 805368 |
| Qcache_hits | 1 |
| Qcache_inserts | 2 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 7 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 7 |
+-------------------------+--------+
Die Status Variable QCache_hits hat sich erhöht.
Von dieser zusätzlichen Geschwindigkeit können viele Programme profitieren. Vor allem können Programme davon profitieren, die häufig mit den gleichen Abfragen auf die gleichen Daten lesend zugreifen.
Grenzen des Query Cache
Der Query Cache merkt sich komplette Select Abfragen und deren Ergebnisse. Wenn ein Teil der Abfrage geändert wird, dann greift der Caching Mechanismus nicht.
mysql> show status like 'qc%';
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 805368 |
| Qcache_hits | 2 |
| Qcache_inserts | 2 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 8 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 7 |
+-------------------------+--------+
8 rows in set (0.00 sec)
mysql> select * from cache_pages;
6 rows in set (0.00 sec)
mysql> show status like 'qc%';
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 805368 |
| Qcache_hits | 3 |
| Qcache_inserts | 2 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 9 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 7 |
+-------------------------+--------+
mysql> select * from cache_pages where 1;
[...]
6 rows in set (0.00 sec)
mysql> show status like 'qc%';
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 630632 |
| Qcache_hits | 3 |
| Qcache_inserts | 3 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 10 |
| Qcache_queries_in_cache | 3 |
| Qcache_total_blocks | 9 |
+-------------------------+--------+
In diesem Beispiel wurde die SQL-Anweisung geringfügig geändert (WHERE 1) und schon greift der Caching Mechanismus nicht mehr (Qcache_hits bleibt gleich und QCache_queries_in_cache erhöht sich).
Wenn die Programmierer dieses bei der Programmierung beachten und häufiger Copy&Paste ihrer SQL-Anweisungen machen, ohne sie zu verändern, kann dieser Mechanismus gut greifen.
Mit der größe des Caches muss man etwas probieren. Sobald der Cache voll ist, werden die am längsten nicht mehr verwendeten Queries aus dem Cache gelöscht (Qcache_lowmem_prunes). Für die meisten Webanwendungen empfiehlt sich ein Wert > 1MB zu setzen.
Um diesen Wert permanent zu setzen (auch nach einem Restart des MySQL Servers) muss dieser Wert noch in die my.cnf des MySQL Servers geschrieben werden.
[mysqld]
[...]
query_cache_size = 1M
Dadurch wird der Wert bei jedem Starten gesetzt. In der Konfigurationsdatei kann man mit den Anhängen k/M... arbeiten, was die Eingabe des Wertes übersichtlicher gestaltet.
:wq
Simon | wrote at 21.02.2010 15:16 | email | homepage
Could you help me. Love is that splendid triggering of human vitality. the supreme activity which nature affords anyone for going out of himself toward someone else. Help me! I can not find sites on the: Amoxicillin tired. I found only this - amoxicillin no prescription. Isolates the net of p450 patients in the possibility, amoxicillin. Affect even the wool that has been used for you, amoxicillin. Thank you very much
. Simon from Kiribati.