Zusammenfassung der speicherbezogenen Parameter von MySQL 8.0

Theoretisch entspricht der von MySQL verwendete Speicher dem globalen gemeinsam genutzten Speicher + max_connections × threadspezifischer Speicher.

Das ist: innodb_buffer_pool_size + innodb_log_buffer_size + thread_cache_size + table_open_cache + table_definition_cache + key_buffer_size + max_connections * (thread_stack + sort_buffer_size + join_buffer_size + read_buffer_size + read_rnd_buffer_size + binlog_cache_size + tmp_table_size)

Nachfolgend klassifizieren wir die globalen Speicherparameter und threadspezifischen Parameter und stellen kurz die Funktionen der relevanten Parameter vor.

Globaler gemeinsam genutzter Speicher

innodb_buffer_pool_size

Der Parameter innodb_buffer_pool_size ist einer der wichtigsten Parameter für die MySQL-Datenbank. Seine Auswirkung auf die InnoDB-Speicher-Engine ist ähnlich der Auswirkung des Key Buffer Cache auf die MyISAM-Speicher-Engine. Der Hauptunterschied besteht darin, dass der InnoDB Buffer Pool nicht nur Indexdaten zwischenspeichert, sondern auch Tabellendaten, und zwar vollständig entsprechend den Datenstrukturinformationen in der Datendatei. Dies ist ähnlich dem Datenbank-Buffer-Cache in Oracle SGA. Daher sollte die im SHOW ENGINE innodb status ermittelte Buffer Pool-Größe mit 16 KB multipliziert werden.

Die Trefferquote des InnoDB-Pufferpools kann wie folgt berechnet werden: (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100 %.

innodb_change_buffering

Änderungspufferung ist eine neue Funktion, die in MySQL 5.5 hinzugefügt wurde. Änderungspufferung ist eine Erweiterung der Einfügepufferung. Die Einfügepufferung ist nur für das Einfügen gültig, während Änderungspufferung für das Einfügen, Löschen, Aktualisieren (Löschen+Einfügen) und Bereinigen gültig ist. Wenn beim Ändern der Daten eines Indexblocks (Sekundärindex) der Indexblock nicht im Pufferpool vorhanden ist, werden die geänderten Informationen im Änderungspuffer zwischengespeichert. Wenn der erforderliche Indexblock durch Indexscannen in den Pufferpool eingelesen wird, wird er mit den geänderten Informationen im Änderungspuffer zusammengeführt und dann bei Bedarf wieder auf die Festplatte geschrieben.

Der Zweck besteht darin, den durch zufällige E/A verursachten Leistungsverlust zu verringern. Einfach ausgedrückt: Konvertieren Sie zufällige E/A so weit wie möglich in sequentielle E/A. SSDs sind heutzutage weit verbreitet. Wenn die Leistung für wahlfreien und sequentiellen Zugriff auf SSDs nahezu gleich ist, bringt die Änderungspufferungsfunktion keine große Leistungsverbesserung. Bei billigen mechanischen Festplatten kann dieser Parameter jedoch immer noch zur Leistungsverbesserung beitragen.

Die Änderungspufferung wird durch den Parameter innodb_change_buffering gesteuert:

• alle: Puffereinfügungen, Löschmarkierungsvorgänge und Bereinigungen.
• keine: Keine Vorgänge puffern.
• Einfügungen: Puffereinfügevorgänge.
• Löschvorgänge: Lösch- und Markierungsvorgänge im Puffer.
• Änderungen: Sowohl Einfügungen als auch Löschmarkierungen puffern.
• Bereinigungen: Puffern Sie die physischen Löschvorgänge, die im Hintergrund stattfinden.
  

Beachten Sie, dass dieser Speicher im Innodb-Pufferpool zugewiesen ist und bei der Berechnung des Gesamtspeichers nicht berücksichtigt werden muss.

innodb_change_buffer_max_size

Gibt den maximalen Prozentsatz des Änderungspuffers im Pufferpool an. Der Standardwert beträgt 25 % und der Höchstwert 50 %. Wenn im System schwerwiegende Einfügungen, Aktualisierungen und aktive Löschungen vorkommen, erhöhen Sie max_size; bei reinen Berichtssystemen, die Daten nicht ändern, können Sie den Parameterwert verringern.

innodb_log_buffer_size

Dies ist der vom Transaktionsprotokoll der InnoDB-Speicher-Engine verwendete Puffer. Um die Leistung zu verbessern, werden die Informationen zunächst in den Innofb-Protokollpuffer geschrieben. Wenn die entsprechenden Bedingungen des Parameters innodb_flush_log_trx_commit erfüllt sind (oder der Protokollpuffer voll ist), wird das Protokoll in die Datei geschrieben (oder auf die Festplatte synchronisiert). Der Parameter innodb_flush_log_trx_commit kann auf 0, 1 oder 2 gesetzt werden, wie unten erläutert:

• 0: Die Daten im Protokollpuffer werden einmal pro Sekunde in die Protokolldatei geschrieben und das Dateisystem wird gleichzeitig mit der Festplatte synchronisiert. Das Commit jeder Transaktion löst jedoch kein Leeren des Protokollpuffers in die Protokolldatei oder des Dateisystems auf die Festplatte aus. Dieser Modus ist der schnellste, aber weniger sicher. Der Absturz des mysqld-Prozesses führt zum Verlust aller Transaktionsdaten der vorherigen Sekunde.
• 1: Wenn jede Transaktion festgeschrieben wird, werden die Daten im Protokollpuffer in die Protokolldatei geschrieben und die Synchronisierung vom Dateisystem zur Festplatte wird ebenfalls ausgelöst. Dieser Modus ist der sicherste, aber auch der langsamste.
• 2: Das Transaktionscommit löst die Aktualisierung des Protokollpuffers in der Protokolldatei aus, löst jedoch nicht die Synchronisierung des Festplattendateisystems mit der Festplatte aus. Dieser Modus ist schneller und sicherer als 0. Alle Transaktionsdaten in der letzten Sekunde können nur verloren gehen, wenn das Betriebssystem abstürzt oder das System die Stromversorgung verliert.
  

Thread-Cache-Größe

Die Cachegröße des Thread-Pools wird verwendet, um den aktuellen Thread nach der Trennung der Clientverbindung zwischenzuspeichern, sodass schnell auf eine neue Verbindungsanforderung geantwortet werden kann, ohne einen neuen Thread zu erstellen. Dies kann die Effizienz der Verbindungsherstellung erheblich verbessern, insbesondere bei Anwendungen, die kurze Verbindungen verwenden. Die Trefferquote des Verbindungsthread-Cache kann wie folgt berechnet werden: (Verbindungen – erstellte Threads) / Verbindungen * 100 %. Sie können die Thread-Pool-Größe auch über die folgenden MySQL-Statuswerte entsprechend anpassen:

mysql> globalen Status wie „Thread%“ anzeigen;
+---------------------+------+
| Variablenname | Wert |
+---------------------+------+
| Zwischengespeicherte Threads | 2 |
| Threads_verbunden | 1 |
| Threads_erstellt | 3 |
| Laufende Threads | 2 |
+---------------------+------+
4 Zeilen im Satz (0,01 Sek.)

Wenn Threads_cached abnimmt, Threads_connected jedoch nie abnimmt und Threads_created weiter zunimmt, können Sie die Größe von thread_cache_size entsprechend erhöhen.

Tabelle_öffnen_Cache

table_open_cache gibt die Größe des Tabellencaches an, der zum Zwischenspeichern der Dateihandle-Informationen der Tabellendatei verwendet wird. Wenn unser Client-Programm eine Abfrage an MySQL sendet, muss MySQL für jede an der Abfrage beteiligte Tabelle Informationen zum Tabellendatei-Handle abrufen. Wenn kein Tabellencache vorhanden ist, muss MySQL häufig Dateien öffnen und schließen, was zweifellos einen gewissen Einfluss auf die Systemleistung hat. Immer wenn MySQL auf eine Tabelle zugreift und im Tabellenpuffer Platz vorhanden ist, wird die Tabelle geöffnet und darin abgelegt, sodass schneller auf den Tabelleninhalt zugegriffen werden kann. Beachten Sie, dass hier die Anzahl der zwischengespeicherten Tabellendatei-Handle-Informationen und nicht die Größe des Speicherplatzes festgelegt wird.

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. Open_tables ist die Anzahl der aktuell geöffneten Tische und Opened_tables ist die Anzahl aller geöffneten Tische. 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. 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). Wenn der Wert von Open_tables nahe am Wert von table_cache liegt und Opened_tables immer noch ansteigt, bedeutet dies, dass MySQL die zwischengespeicherte Tabelle freigibt, um Platz für die neue Tabelle zu schaffen. Zu diesem Zeitpunkt müssen Sie möglicherweise den Wert von table_cache erhöhen. Für die meisten Situationen sind geeignete Werte:

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

Es wird empfohlen, die MySQL-Datenbank eine Zeit lang in einer Produktionsumgebung auszuführen und dann den Parameterwert so anzupassen, dass er größer als der Wert von Opened_tables ist, und sicherzustellen, dass er unter extremen Bedingungen hoher Belastung immer noch etwas größer als Opened_tables ist.

Tabellendefinitionscache

table_definition_cache ist ähnlich wie table_open_cache. Ersteres speichert frm-Dateien im Cache. Letzteres wird im Dokument nicht erläutert. Es sollte ibd/MYI/MYD sein.

Statuswert:

Open_table_definitions: Die Anzahl der zwischengespeicherten Tabellendefinitionsdateien.frm

Opened_table_definitions: Die Gesamtzahl der FRM-Dateien, die im Verlauf zwischengespeichert wurden

Schlüsselpuffergröße

key_buffer_size gibt die Größe des Indexpuffers an, die die Geschwindigkeit der Indexverarbeitung bestimmt, insbesondere die Geschwindigkeit des Indexlesens. Durch Überprüfen der Statuswerte Key_read_requests und Key_reads können Sie feststellen, ob die Einstellung key_buffer_size sinnvoll ist. Das Verhältnis key_reads / key_read_requests sollte möglichst gering sein, mindestens 1:100, besser ist 1:1000 (der obige Statuswert kann mittels SHOW STATUS LIKE 'key_read%' abgerufen werden). key_buffer_size funktioniert nur für MyISAM-Tabellen. Auch wenn Sie keine MyISAM-Tabellen verwenden, die internen temporären Festplattentabellen aber MyISAM-Tabellen sind, sollten Sie diesen Wert verwenden. Sie können den Prüfstatuswert „created_tmp_disk_tables“ verwenden, um Details zu erhalten.

Max_Verbindungen

Die maximale Anzahl von MySQL-Verbindungen. Durch Erhöhen dieses Wertes erhöht sich die Anzahl der von mysqld benötigten Dateideskriptoren. Wenn der Server eine große Anzahl gleichzeitiger Verbindungsanforderungen hat, wird empfohlen, diesen Wert zu erhöhen, um die Anzahl paralleler Verbindungen zu erhöhen. Dies hängt natürlich von der Fähigkeit des Computers ab, dies zu unterstützen. Denn wenn mehr Verbindungen vorhanden sind, stellt MySQL für jede Verbindung einen Verbindungspuffer bereit, der mehr Speicher verbraucht. Daher sollten Sie den Wert entsprechend anpassen und ihn nicht blind erhöhen. Wenn der Wert zu klein ist, wird häufig FEHLER 1040: Zu viele Verbindungen angezeigt. Mit dem Platzhalter „conn%“ können Sie die Anzahl der Verbindungen im aktuellen Status anzeigen und so die Größe des Werts bestimmen. max_used_connections / max_connections * 100 % (Idealwert ≈ 85 %) Wenn max_used_connections gleich max_connections ist, dann ist max_connections zu niedrig eingestellt oder überschreitet die Server-Lastgrenze. Wenn es niedriger als 10 % ist, ist es zu hoch eingestellt.

Thread-/Sitzungs-/Verbindungs-exklusiver Speicher

binlog_cache_size

Der für jede Sitzung zugewiesene Speicher wird verwendet, um den Binärprotokoll-Cache während des Transaktionsprozesses zu speichern, was die Effizienz der Aufzeichnung des Binärprotokolls verbessern kann. Der Standardwert ist 32 KB. Wenn keine großen Transaktionen vorhanden sind und DML nicht sehr häufig ist, können Sie ihn kleiner einstellen. Wenn die Transaktionen groß und zahlreich sind und DML-Operationen häufig sind, können Sie ihn entsprechend erhöhen.

Die Verwendung von binlog_cache_size in der Datenbank kann wie folgt angezeigt werden: Binlog_cache_disk_use gibt an, wie oft temporäre Dateien zum Zwischenspeichern von Binärprotokollen verwendet werden, da unser binlog_cache_size-Speicher nicht ausreicht; Binlog_cache_use gibt an, wie oft binlog_cache_size zum Zwischenspeichern verwendet wird

tmp_table_size und max_heap_table_size

tmp_table_size gibt die maximale Größe der temporären Tabelle im internen Speicher an, die für jeden Thread zugewiesen werden muss. (Die tatsächliche Grenze ist das Minimum von tmp_table_size und max_heap_table_size.) Wenn die temporäre Tabelle im Arbeitsspeicher die Grenze überschreitet, konvertiert MySQL sie automatisch in eine festplattenbasierte MyISAM-Tabelle und speichert sie standardmäßig im angegebenen tmpdir-Verzeichnis:

mysql> Variablen wie „tmpdir“ anzeigen;
+---------------+-------+
| Variablenname | Wert |
+---------------+-------+
| temporäres Verzeichnis | /tmp/ |
+---------------+-------+

Vermeiden Sie beim Optimieren von Abfrageanweisungen die Verwendung temporärer Tabellen. Wenn dies unvermeidlich ist, stellen Sie sicher, dass diese temporären Tabellen im Speicher gespeichert sind. Falls erforderlich und wenn Sie viele Group-By-Anweisungen und viel Arbeitsspeicher haben, erhöhen Sie den Wert von tmp_table_size (und max_heap_table_size). Diese Variable gilt nicht für vom Benutzer erstellte Speichertabellen.

Sie können die Gesamtzahl der internen, festplattenbasierten temporären Tabellen und die Gesamtzahl der im Speicher erstellten temporären Tabellen (Created_tmp_disk_tables und Created_tmp_tables) vergleichen. Das allgemeine Verhältnis lautet:

Erstellte temporäre Festplattentabellen/Erstellte temporäre Tabellen<5 %

max_heap_table_size definiert die Größe der Speichertabelle, die der Benutzer erstellen kann. Dieser Wert wird verwendet, um den maximalen Zeilenwert der Speichertabelle zu berechnen. Diese Variable unterstützt dynamische Änderungen, d. h. setzen Sie @max_heap_table_size = xxx.

Oben finden Sie eine detaillierte Zusammenfassung der speicherbezogenen Parameter von MySQL 8.0. Weitere Informationen zu den Speicherparametern von MySQL 8.0 finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!