Caching in MySQL

7.1 Caching auf der DB-Seite

MySQL verwendet bei der Arbeit mit Tabellen hochskalierbare Algorithmen, sodass MySQL auch mit wenig Speicher ausgeführt werden kann. Für eine bessere Leistung benötigen Sie natürlich mehr RAM.

Um die aktuellen Einstellungen anzuzeigen, stellen Sie eine Verbindung zur Datenbank her

#mysq -u root -p

mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 134217728 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
5 rows in set (0.00 sec)

Stellen Sie zunächst sicher, dass das Caching aktiviert ist. Variable:

• query_cache_typemuss ON (1) oder DEMAND (2) sein.
• query_cache_limit– bestimmt die maximale Größe des Ergebnisses, das in den Cache gelangt
• query_cache_sizevon Null verschieden sein. Bei Verwendung von DEMAND werden nur Anfragen zwischengespeichert, die die Anweisung enthaltenSQL_CACHE;
• query_cache_min_res_unitdie Mindestgröße des zugewiesenen Speicherblocks zum Speichern der Ergebnisse der zwischengespeicherten Abfrage. MySQL speichert den Cache nicht in einem großen Speicherblock, sondern weist query_cache_min_res_unitbei Bedarf Blöcke mit einer Mindestgröße (standardmäßig = 4 KB) zu. Der letzte Block wird auf die Datengröße gekürzt und der verbleibende Speicher wird freigegeben.

Der Effekt des Caching besteht darin, dass der Server beim Empfang einer Anfrage prüft, ob sich der Hash der Anfrage im Cache befindet. Wenn der Hash übereinstimmt, gibt der Server das Ergebnis sofort zurück – ohne die Anfrage zu analysieren, zu optimieren usw. Overhead – begleitet von einem Caching-Mechanismus – Durchsuchen des Caches, Schreiben des Abfrageergebnisses in den Cache usw.

Und wenn Sie viele kleine Anfragen im Cache haben, kann dies aufgrund einer großen Anzahl freier Blöcke zu einer Speicherfragmentierung führen. Dies führt wiederum dazu, dass zwischengespeicherte Einträge aufgrund von Speichermangel gelöscht werden. In diesem Fall ist es sinnvoll, den Wert von zu verringern query_cache_min_res_unit. Wenn die meisten Ihrer Abfragen große Ergebnisse generieren, kann eine Erhöhung dieser Einstellung die Leistung verbessern.

Versuchen wir, den Effekt zu bewerten. Wir betrachten, wie sich die Cache-Trefferzähler ändern (Qcahe_hits), die Anzahl der aufgrund von Speichermangel für ungültig erklärten Anforderungen (Qcache_lowmem_prunes), die Gesamtzahl der Anforderungen vom Typ SELECT (und nur diese werden zwischengespeichert):

#mysq -u root -p
mysql> show status like 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 715       |
| Qcache_free_memory      | 130369640 |
| Qcache_hits             | 24209     |
| Qcache_inserts          | 16215     |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 444       |
| Qcache_queries_in_cache | 1740      |
| Qcache_total_blocks     | 4225      |
+-------------------------+-----------+
8 rows in set (0.00 sec)

7.2 Aktueller Cache-Status

Zur Überwachung des Abfragecaches wird Folgendes verwendet SHOW STATUS:

mysql> SHOW STATUS LIKE 'Qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 10       |
| Qcache_free_memory      | 16755496 |
| Qcache_hits             | 49812    |
| Qcache_inserts          | 103999   |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 180      |
| Qcache_queries_in_cache | 6        |
| Qcache_total_blocks     | 28       |
+-------------------------+----------+
8 rows in set (0.00 sec)

• Qcache_free_blockszeigt an, wie viele freie Blöcke sich im Cache befinden (wird kleiner, wenn die zwischengespeicherten Anforderungen zunehmen);
• Qcache_total_blocks— die Anzahl der belegten Blöcke;
• Qcache_free_memory- zeigt den freien „verfügbaren“ Speicher für das Caching an;
• Qcache_hits- die Anzahl der Anfragen, deren Ergebnisse aus dem Cache entnommen wurden, ohne dass tatsächlich auf die Datenbank zugegriffen wurde;
• Qcache_inserts– die Anzahl der Anfragen, die dem Cache hinzugefügt wurden;
• Qcache_lowmem_prunes– die Anzahl der Anfragen, die aufgrund von Speichermangel aus dem Cache entfernt wurden;
• Qcache_not_cached- die Anzahl der Anfragen, die aufgrund der Nutzung von Zeitmanagementfunktionen usw. nicht in den Cache geschrieben wurden;
• Qcache_queries_in_cache– die Anzahl der Anfragen, die sich im Cache befinden.

Sie können die Gesamtzahl der SELECT-Abfragen anzeigen:

mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 16719 |
+---------------+-------+
1 row in set (0.00 sec)

7.3 Caching-Effizienz

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

Der aktuelle Wert kann wie folgt angezeigt werden:

SHOW VARIABLES LIKE 'query_cache_size';

Auch hier stellt sich die Frage: Wie wählt man einen adäquaten Wert?query_cache_size?

Das wird helfen Qcache_lowmem_prunes. Diese Variable speichert die Anzahl der Anfragen, die aus dem Cache entfernt wurden, weil neue Anfragen zwischengespeichert werden mussten. Es ist eine solche Cache-Größe anzustreben, bei der Qcache_lowmem_prunessie nur geringfügig ansteigt. Dazu empfiehlt es sich, die Differenz der Werte Qcache_lowmem_prunespro Stunde und die Anzahl der Anfragen, die MySQL in derselben Stunde erhält, zu vergleichen.

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2
query_cache_size = (Verkehrsvolumen in 10 Minuten) * 1,2 "

Dadurch werden Anfragen 10 Minuten lang zwischengespeichert + 20 % zusätzlicher Speicher für die Cache-Fragmentierung und zusätzliche Caching-Reserve zur Verfügung gestellt

Sie können die Anzahl und das durchschnittliche Volumen einer Antwort auf eine Anfrage mithilfe der Bytes_sent - Variablen berechnen

Und so query_cache_sizehaben wir die Werte erhöht, danach sollten Sie auf die Werte achten Qcache_total_blocks, Qcache_free_blocksund Qcache_queries_in_cache. MySQL speichert den Cache in Blöcken. Für 1 Anfrage werden 2 Blöcke benötigt: einer für den Anfragetext selbst, der zweite für das Ergebnis.

Betrachten wir die Tabelle vom WertQcache%

Gesamtzahl der Cache-BlöckeQcache_total_blocks – 28

Anfrage 6 wird jetzt zwischengespeichert, was bedeutet, dass 6 * 2 = 12 Blöcke belegt sind

Kostenlose Blöcke Qcache_free_blocks – 10. Je mehr inaktiv Qcache_free_blocks, desto größer ist der Grad der „Fragmentierung“ des Caches.

Wenn die meisten Abfragen eine kleine Menge an resultierenden Daten haben, lohnt es sich, die minimale Cache-Blockgröße zu reduzieren query_cache_min_res_unit, die standardmäßig 4 KB beträgt.

Wenn die meisten Anfragen viele Daten zurückgeben, lohnt es sich, die Größe des Cache-Blocks zu erhöhen.

Die Hauptsache ist, einen Mindestwert zu erreichen Qcache_free_blocks.

Wenn der Zähler Qcache_not_cachedgroß ist, können Sie versuchen, die Variable zu erhöhen query_cache_limit. Dadurch können Sie das Limit erhöhen und die Ergebnisse von Abfragen zwischenspeichern, die „nicht passen“.

Die folgenden Konfigurationsvariablen sind für die Verwendung des Abfragecaches verantwortlich:

• query_cache_size– die Größe des Abfragecaches. query_cache_size = 0deaktiviert die Cache-Nutzung;
• query_cache_limit– die Größe der maximalen im Cache gespeicherten Stichprobe;
• query_cache_wlock_invalidate– legt fest, ob die Daten aus dem Cache entnommen werden, wenn die Tabelle, zu der sie gehören, zum Lesen gesperrt ist.
=

Um das Caching von MySQL-Abfragen zu aktivieren, fügen Sie einfach die folgenden Zeilen zu my.cnf (Abschnitt [mysqld]) hinzu:

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

Und starten Sie den Dienst neu.

7.4 Wo der Cache nicht verwendet wird

Nicht zwischengespeichert:

• Anfragen vonSQL_NO_CACHE
• Vorbereitete Abfragen(Prepared statements);
• Abfragen, die Unterabfragen der äußeren Abfrage sind;
• Abfragen innerhalb gespeicherter Prozeduren und Funktionen;
• Abfragen, die Funktionen verwenden:

  BENCHMARK (), CONNECTION_ID (), CONVERT_TZ (), CURDATE (), CURRENT_DATE (), CURRENT_TIME (), CURRENT_TIMESTAMP (), CURTIME (), DATABASE (), ENCRYPT ()mit einem Argument, FOUND_ROWS (), GET_LOCK (), LAST_INSERT_ID (), LOAD_FILE (), MASTER_POS_WAIT (), NOW (), RAND (), RELEASE_LOCK (), SLEEP (), SYSDATE (), UNIX_TIMESTAMP ()ohne Argumente, USER (),UUID ();

• Abfragen mit gespeicherten Funktionen, Benutzervariablen oder referenzierenden Tabellen in MySQL- Systemdatenbanken oder INFORMATION_SCHEMA;
• • Anfragen in den folgenden Formen:
• SELECT ... IN SHARE MODE
• SELECT ... FOR UPDATE
• SELECT ... INTO OUTFILE ...
• SELECT ... INTO DUMPFILE ...
• SELECT * FROM ... WHERE autoincrement_col IS NULL
• Abfragen mit temporären Tabellen oder ohne Verwendung von Tabellen;
• Anfragen, die Warnungen generieren (Warnungen);

Sie können den Cache mit dem folgenden Befehl defragmentieren:

mysql>flush query cache;

Löschen – Befehl:

mysql>flush query cache;

Das wichtigste

Arbeiten Sie in der Produktion niemals mit Standardeinstellungen. Dies führt dazu, dass die meisten Ressourcen des Servers nicht genutzt werden. Durch die richtige MySQL-Optimierung kann die Datenbankleistung um ein Vielfaches verbessert werden. Dies beschleunigt nicht nur die Anwendung, sondern bewältigt auch eine hohe Belastung.