Detaillierte Analyse des MySQL 8.0-Speicherverbrauchs

Beim Start von MySQL 8.0 werden verschiedene Puffer und Caches konfiguriert, um die Datenbankleistung zu verbessern. Wenn wir den MySQL 8.0-Dienst auf einem Server konfigurieren, wird der Speicher dieses Servers gleichzeitig vom Betriebssystem, dem MySQL 8.0-Dienst und anderen Anwendungen gemeinsam genutzt.

In einer Produktionsumgebung treten häufig Speicheralarme auf. Bevor Sie diese Alarme behandeln, müssen Sie wissen, wo MySQL selbst den meisten Speicher verbraucht. Auf diese Weise können Sie intuitiver bestimmen, wie viel Speicher Ihr MySQL-Dienst belegt und wie Sie den Speicherverbrauch von MySQL selbst reduzieren können.

In der MySQL-Konfigurationsdatei sind innodb_buffer_pool_size und innodb_log_buffer_size die beiden am häufigsten verwendeten speicherbezogenen Parameter. Schauen wir uns diese beiden Parameter einmal an.

1. innodb_buffer_pool_size

Dieser Parameter definiert die Größe des Pufferpools, mit dem Sie vielleicht vertraut sind. Der Inhalt des Pufferpools umfasst InnoDB-Tabellen, Indizes und andere Hilfspuffer. Die Größe des Pufferpools hat einen großen Einfluss auf die Leistung des MySQL-Systems. Standardmäßig beträgt die in MySQL 8.0 konfigurierte Pufferpoolgröße 128 MB. Wenn es sich normalerweise um eine einzelne Maschine und eine einzelne Instanz ohne andere Zwecke handelt, lautet die offizielle MySQL-Empfehlung, die Größe zwischen 50 % und 75 % des Systemspeichers zu konfigurieren. Wenn auf Ihrem Server andere Anwendungen bereitgestellt sind, müssen Sie dieses Verhältnis natürlich entsprechend reduzieren, um Speicher freizugeben.

Wenn Ihr Betriebssystem über ausreichend Arbeitsspeicher verfügt, können Sie mehrere InnoDB-Pufferpoolinstanzen einrichten. Mit den folgenden Parametern können Sie die Anzahl der Instanzen anpassen:

mysql> Variablen wie „%innodb_buffer_pool_instances%“ anzeigen;
+------------------------------+----------+
| Variablenname | Wert |
+------------------------------+----------+
| innodb_buffer_pool_instances | 1 |
+------------------------------+----------+
1 Zeile im Satz (0,00 Sek.)

2. innodb_log_buffer_size

Dieser Parameter definiert die maximale Größe der im Speicher zwischengespeicherten Daten, bevor die InnoDB-Speicher-Engine das Redo-Protokoll auf die Festplatte schreibt. Der Standardwert ist 16 MB. Nachdem dieser Wert erhöht wurde, müssen große Transaktionen keine Redo-Protokolle mehr auf die Festplatte schreiben, bevor die Transaktion festgeschrieben wird. Wenn Ihre Aktualisierungs-, Lösch- und Einfügevorgänge eine große Anzahl von Zeilen betreffen, sollten Sie eine Erhöhung dieses Werts in Erwägung ziehen.

Hier kommt der Punkt:

Im Betriebssystem hängt der von MySQL belegte Speicher nicht nur mit den beiden oben genannten Speicherkonfigurationsparametern zusammen. Wenn wir den von MySQL belegten Speicher berechnen, addieren wir normalerweise die folgenden vier Werte:

1. innodb_buffer_pool_size

2. key_buffer_size (dieser Parameter ist normalerweise der Schlüsselparameter des von der Myisam-Tabelle belegten Speichers)

3. max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) (diese drei sind Puffer auf Verbindungsebene)

4. max_connections*2MB

Wenn Sie also mit dem Befehl top feststellen, dass der von Ihrem MySQL belegte Speicher weit über innodb_buffer_pool_size liegt, müssen Sie als weiteren wichtigen Faktor berücksichtigen, ob die Anzahl der Verbindungen den Grenzwert überschreitet. Sobald die Anzahl der Verbindungen zu hoch ist, wird der von den beiden oben genannten Teilen 3 und 4 belegte Speicher sehr groß sein.

Natürlich sind die oben genannten Faktoren die Hauptfaktoren, die den MySQL-Speicher belegen. Darüber hinaus können Sie für den anderen Speicherverbrauch die offizielle Dokumentation überprüfen:

https://dev.mysql.com/doc/refman/8.0/en/memory-use.html

Das obige Dokument stellt auch vor, wie man mit performance_schema die MySQL-Speichernutzung überwacht. Hier werde ich den gesamten Prozess beschreiben. Detaillierte Informationen und eine Einführung in die Parameter finden Sie im offiziellen Dokument.

1. Ansicht

performance_schema.setup_instruments

Suchen Sie in dieser Tabelle nach dem Namen der Speichervariablen, an der Sie interessiert sind (direkte Suche, es gibt mehr als 490 Ergebnisse, unterteilt in mehrere Hauptkategorien; achten Sie darauf, die Parameter zu filtern, an denen Sie interessiert sind). Beispielsweise suchen wir nach speicher-/innodb-bezogenen Parametern, die den von der Innodb-Speicher-Engine belegten Speicher darstellen. Die Ergebnisse sind wie folgt:

mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory/innodb%';
+-------------------------------------------+---------+-------+-------------------+------------+---------------+
| NAME | AKTIVIERT | ZEITGESTEUERT | EIGENSCHAFTEN | VOLATILITÄT | DOKUMENTATION |
+-------------------------------------------+---------+-------+-------------------+------------+---------------+
| Speicher/InnoDB/adaptiver Hash-Index | JA | NULL | | 0 | NULL |
| Speicher/InnoDB/Protokoll und Seitenarchivierer | JA | NULL | | 0 | NULL |
| Speicher/Innodb/buf_buf_pool | JA | NULL | globale_Statistiken | 0 | NULL |
| Speicher/innodb/buf_stat_per_index_t | JA | NULL | | 0 | NULL |
| Speicher/InnoDB/Klon | JA | NULL | | 0 | NULL |
| Speicher/innodb/dict_stats_bg_recalc_pool_t | JA | NULL | | 0 | NULL |
| Speicher/innodb/dict_stats_index_map_t | JA | NULL | | 0 | NULL |
| Speicher/innodb/dict_stats_n_diff_on_level | JA | NULL | | 0 | NULL |
| Speicher/InnoDB/Sonstiges | JA | NULL | | 0 | NULL |
| Speicher/InnoDB/Partitionierung | JA | NULL | | 0 | NULL |
| Speicher/innodb/row_log_buf | JA | NULL | | 0 | NULL |
| Speicher/Innodb/Row_merge_sort | JA | NULL | | 0 | NULL |
| Speicher/Innodb/std | JA | NULL | | 0 | NULL |
| Speicher/innodb/trx_sys_t::rw_trx_ids | JA | NULL | | 0 | NULL |
| Speicher/innodb/undo::Tablespaces | JA | NULL | | 0 | NULL |
| Speicher/innodb/ut_lock_free_hash_t | JA | NULL | | 0 | NULL |
| Speicher/innodb/api0api | JA | NULL | | 0 | NULL |
| Speicher/innodb/api0misc | JA | NULL | | 0 | NULL |
| Speicher/innodb/btr0btr | JA | NULL | | 0 | NULL |

2. Schreiben Sie relevante Parameter in die Konfigurationsdatei und aktivieren Sie Statistiken. Am Beispiel von memory/innodb/row_log_buf wird die Konfigurationsdatei wie folgt geändert:

Leistungsschemainstrument = "Speicher/InnoDB/Row_Log_Buf = GEZÄHLT"

3. Starten Sie die Instanz und zeigen Sie die Speicherstatistiken in der Tabelle memory_summary_global_by_event_name der Datenbank performance_schema an.

SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/innodb/row_log_buf'\G

Natürlich können Sie auch die Gesamtergebnisse jeder Kategorie basierend auf den Ergebnissen in der Sys-Tabelle wie folgt anzeigen:

mysql> SELECT SUBSTRING_INDEX(Ereignisname,'/',2) AS
  Codebereich, FORMAT_BYTES(SUM(aktuelle_Zuweisung))
  AS current_alloc
  VON sys.x$memory_global_by_current_bytes
  GROUP BY SUBSTRING_INDEX(Ereignisname,'/',2)
  ORDER BY SUM(aktuelle_Zuweisung) DESC;
+-----------------------------+------------------+
| Codebereich | aktuelle_Zuweisung |
+-----------------------------+------------------+
| Speicher/InnoDB | 843,24 MiB |
| Speicher/Leistungsschema | 81,29 MiB |
| Speicher/Mysys | 8,20 MiB |
| Speicher/SQL | 2,47 MiB |
| Speicher/Speicher | 174,01 KiB |
| Speicher/Myisam | 46,53 KiB |
| Speicher/Schwarzes Loch | 512 Bytes |
| Speicher/föderiert | 512 Bytes |
| Speicher/CSV | 512 Bytes |
| Speicher/vio | 496 Bytes |
+-----------------------------+------------------+

Ausführlichere Informationen finden Sie in der offiziellen Dokumentation.

Oben finden Sie eine detaillierte Analyse des Speicherverbrauchs von MySQL 8.0. Weitere Informationen zum Speicherverbrauch von MySQL 8.0 finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!