Detaillierte Erläuterung der Parameteroptimierung table_open_cache und sinnvolle Konfiguration unter MySQL 5.6

1. Einleitung

table_cache ist ein sehr wichtiger MySQL-Leistungsparameter, der in Versionen nach 5.1.3 table_open_cache genannt wird. table_cache wird hauptsächlich verwendet, um die Anzahl der Tabellencaches festzulegen. Da jede Clientverbindung auf mindestens eine Tabelle zugreift, hängt der Wert dieses Parameters mit max_connections zusammen.
Beispielsweise sollten Sie für 1000 parallel laufende Verbindungen über einen Tabellencache von mindestens 1000 × N verfügen, wobei N die maximale Anzahl von Tabellen in einem Join von Abfragen ist, die die Anwendung ausführen kann. Darüber hinaus müssen einige zusätzliche Dateideskriptoren für temporäre Tabellen und Dateien reserviert werden.

2. Cache-Mechanismus

Wenn eine Verbindung auf eine Tabelle zugreift, überprüft MySQL die Anzahl der aktuell zwischengespeicherten Tabellen. Wenn die Tabelle im Cache geöffnet wurde, wird zur Beschleunigung der Abfrage direkt auf die im Cache vorhandene Tabelle zugegriffen, wenn die Tabelle nicht im Cache gespeichert ist, wird die aktuelle Tabelle dem Cache hinzugefügt und abgefragt.

Vor dem Ausführen eines Cachevorgangs wird table_open_cache verwendet, um die maximale Anzahl der Cachetabellen zu begrenzen: Wenn die aktuell zwischengespeicherten Tabellen table_open_cache nicht erreichen, werden neue Tabellen hinzugefügt. Wenn dieser Wert erreicht wurde, gibt MySQL den vorherigen Cache basierend auf der letzten Abfragezeit, der Abfragerate und anderen Regeln der Cachetabelle frei. Immer wenn MySQL auf eine Tabelle zugreift, wird die Tabelle, sofern im Tabellenpuffer Platz vorhanden ist, geöffnet und dort platziert, was einen schnelleren Zugriff auf den Tabelleninhalt ermöglicht.

3. Wie beurteilt man

Durch die Überprüfung der Statuswerte Open_tables und Opened_tables während Spitzenzeiten können Sie entscheiden, ob Sie den Wert von table_open_cache erhöhen müssen.

Wenn Sie feststellen, dass open_tables gleich table_open_cache ist und opened_tables wächst, müssen Sie den Wert von table_open_cache erhöhen (die obigen Statuswerte können mit SHOW GLOBAL STATUS LIKE 'Open%tables' abgerufen werden).

Beachten Sie, dass Sie table_open_cache nicht blind auf einen großen Wert setzen können. Wenn der Wert zu hoch eingestellt ist, gehen Ihnen möglicherweise die Dateideskriptoren aus, was zu instabiler Leistung oder Verbindungsfehlern führt.

Offene_Tabellen / Geöffnete_Tabellen >= 0,85

Offene_Tabellen / Tabellencache <= 0,95

4. Vorschläge

Wenn Sie sich am Anfang nicht sicher sind, platzieren Sie die MySQL-Datenbank für einen bestimmten Zeitraum in der Produktionsumgebung, passen Sie dann den Parameterwert so an, dass er größer als der Wert von Opened_tables ist, und stellen Sie sicher, dass er unter extremen Bedingungen relativ hoher Last immer noch etwas größer als Opened_tables ist.
Bei der Standardinstallation von MySQL beträgt der Wert von table_cache auf Maschinen mit weniger als 2 GB Arbeitsspeicher 256 bis 512. Wenn die Maschine über 4 GB Arbeitsspeicher verfügt, beträgt der Standardwert 2048. Dies bedeutet jedoch nicht, dass dieser Wert umso größer sein sollte, je größer der Arbeitsspeicher der Maschine ist. Da eine Erhöhung von table_cache dazu führt, dass MySQL schneller auf SQL reagiert, werden zwangsläufig mehr Deadlocks erzeugt, was den gesamten Satz von Datenbankoperationen verlangsamt und die Leistung ernsthaft beeinträchtigt. Daher müssen Sie bei der routinemäßigen Wartung weiterhin Beurteilungen basierend auf der tatsächlichen Situation der Datenbank vornehmen und den Table_Cache-Wert finden, der am besten zu der von Ihnen gewarteten Datenbank passt.

mysql>Tabellen leeren; kann open_tables löschen

# Service mysqld Neustart kann geöffnete Tabellen löschen

Nachfolgend finden Sie Anweisungen für MySQL 5.6

table_open_cache gibt die Größe des Tabellencaches an. Immer wenn MySQL auf eine Tabelle zugreift, wird die Tabelle, sofern im Tabellenpuffer Platz vorhanden ist, geöffnet und dort platziert, was einen schnelleren Zugriff auf den Tabelleninhalt ermöglicht.
Durch die Überprüfung der Statuswerte Open_tables und Opened_tables während Spitzenzeiten können Sie entscheiden, ob Sie den Wert von table_open_cache erhöhen müssen.
Wenn Sie feststellen, dass open_tables gleich table_open_cache ist und open_tables wächst, müssen Sie den Wert von table_open_cache erhöhen (die obigen Statuswerte können über SHOW GLOBAL STATUS LIKE 'Open%tables' abgerufen werden).
Beachten Sie, dass Sie table_open_cache nicht blind auf einen großen Wert setzen können. Wenn der Wert zu groß ist, überschreitet er die Dateideskriptoren der Shell (überprüfen Sie mit ulimit -n), was zu unzureichenden Dateideskriptoren, instabiler Leistung oder Verbindungsfehlern führt.

Testumgebung: Tencent Cloud CDB, Speicher 4000 M, überprüfen Sie table_open_cache=512 in der Konsole, überwachen Sie, ob die Einstellung table_open_cache sinnvoll ist und ob sie optimiert werden muss.

Variablen wie „%table_open_cache%“ anzeigen; 

globalen Status wie „Open%tables“ anzeigen;

Es wird festgestellt, dass open_tables gleich table_open_cache ist, beide sind 512, was darauf hinweist, dass MySQL die zwischengespeicherte Tabelle freigibt, um die neue Tabelle aufzunehmen. Zu diesem Zeitpunkt müssen Sie möglicherweise den Wert von table_open_cache erhöhen. Für eine Maschine mit 4G-Speicher wird empfohlen, ihn auf 2048 einzustellen

Passendere Werte:

Offene_Tabellen / Geöffnete_Tabellen >= 0,85
Offene_Tabellen / offener_Tabellencache <= 0,95

Wenn Sie sich bei diesem Parameter nicht sicher sind, hier ein sehr konservativer Einstellungsvorschlag: Setzen Sie die MySQL-Datenbank für einen Zeitraum eines Probebetriebs in die Produktionsumgebung ein und passen Sie den Parameterwert dann so an, dass er größer als der Wert von Opened_tables ist. Stellen Sie sicher, dass er unter extremen Bedingungen relativ hoher Last immer noch etwas größer als Opened_tables ist.