Test und Lösung für den großen Speicherverbrauch und die hohe CPU-Auslastung von MySQL

Nach den Änderungen:

innodb_buffer_pool_size=576M ->256M Der InnoDB-Engine-Puffer nimmt den Großteil des Speichers ein, daher ist das erste, was zu tun ist, ihn zu reduzieren
query_cache_size=100M ->16M Abfrage-Cache
tmp_table_size=102M ->64M temporäre Tabellengröße
Schlüsselpuffergröße = 256 M -> 32 M

Nach dem Neustart des MySQL-Dienstes fiel der virtuelle Speicher unter 200.

Darüber hinaus befinden sich im MySQL-Installationsverzeichnis mehrere Dateien: my-huge.ini, my-large.ini, my-medium.ini ... Hierbei handelt es sich um empfohlene Konfigurationen basierend auf der Speichergröße, und auch Anfänger können sich bei der Einrichtung darauf beziehen.
2G-Speicher MySQL-Datenbankserver my.ini-Optimierung (my.ini)
2G Speicher, für weniger Sender, hochwertige Einstellungen, Testfunktionen:
table_cache = 1024 Je größer der physische Speicher, desto größer die Einstellung. Der Standardwert ist 2402, und es ist am besten, ihn auf 512-1024 einzustellen
innodb_additional_mem_pool_size=8M Standard ist 2M
innodb_flush_log_at_trx_commit=0 Warten Sie, bis die Warteschlange innodb_log_buffer_size voll ist, bevor Sie sie gleichmäßig speichern. Der Standardwert ist 1
innodb_log_buffer_size=4M Standard ist 1M
innodb_thread_concurrency=8 Stellen Sie es auf die Anzahl der CPUs auf Ihrem Server ein. Der Standardwert ist 8
key_buffer_size=256M Der Standardwert ist 218, am besten passt man ihn auf 128 an
tmp_table_size=64M Der Standardwert ist 16M, passen Sie ihn auf 64-256 für das Maximum an
read_buffer_size=4M, Standard ist 64K
read_rnd_buffer_size=16M, Standard ist 256K
sort_buffer_size=32M, Standard ist 256K
max_connections=1024, Standard ist 1210

Prüfung 1:
table_cache=512 oder 1024
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=1M
innodb_thread_concurrency=8 Stellen Sie es auf die Anzahl der CPUs auf Ihrem Server ein. Der Standardwert ist 8
Schlüsselpuffergröße = 128 M
tmp_table_size=128M
read_buffer_size=64K oder 128K
Read_Rnd_Buffer_Größe = 256 KB
Sortierpuffergröße = 512 KB
max_Verbindungen=1024

Prüfung 2:
table_cache=512 oder 1024
innodb_additional_mem_pool_size=8M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=4M
innodb_thread_concurency=8
Schlüsselpuffergröße = 128 M
tmp_table_size=128M
Lesepuffergröße = 4 M
read_rnd_buffer_size=16M
Sortierpuffergröße = 32 M
max_Verbindungen=1024

allgemein:
Tabellencache = 512
innodb_additional_mem_pool_size=8M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=4M
innodb_thread_concurency=8
Schlüsselpuffergröße = 128 M
tmp_table_size=128M
Lesepuffergröße = 4 M
read_rnd_buffer_size=16M
Sortierpuffergröße = 32 M
max_Verbindungen=1024

Nach dem Testen. Wenn keine besondere Situation vorliegt, verwenden Sie am besten die Standardeinstellung.
2G Speicher, für mehrere Stationen, stressresistente Einstellungen, am besten:
table_cache = 1024 Je größer der physische Speicher, desto größer die Einstellung. Der Standardwert ist 2402, und es ist am besten, ihn auf 512-1024 einzustellen
innodb_additional_mem_pool_size=4M Standard ist 2M
innodb_flush_log_at_trx_commit = 1
(Wenn Sie den Wert auf 0 setzen, bedeutet das, dass gewartet wird, bis die Warteschlange innodb_log_buffer_size voll ist, bevor die Daten gleichmäßig gespeichert werden. Der Standardwert ist 1.)
innodb_log_buffer_size=2M Standard ist 1M
innodb_thread_concurrency=8 Stellen Sie es auf die Anzahl der CPUs auf Ihrem Server ein. Der Standardwert ist normalerweise 8.
key_buffer_size=256M Der Standardwert ist 218, am besten passt man ihn auf 128 an
tmp_table_size=64M Der Standardwert ist 16M, passen Sie ihn auf 64-256 für das Maximum an
read_buffer_size=4M, Standard ist 64K
read_rnd_buffer_size=16M, Standard ist 256K
sort_buffer_size=32M, Standard ist 256K
max_connections=1024, Standard ist 1210
thread_cache_size=120, Standard ist 60
query_cache_size=64M

Zehn Parameter zur Optimierung der MySQL-Datenbankleistung
(1) max_Verbindungen:
Die Anzahl gleichzeitig zulässiger Clients. Durch Erhöhen dieses Wertes erhöht sich die Anzahl der von mysqld benötigten Dateideskriptoren. Diese Zahl sollte erhöht werden, da es sonst häufig zu zu vielen Verbindungsfehlern kommt. Der Standardwert ist 100, ich habe ihn auf 1024 geändert.
(2) Datensatzpuffer:
Jeder Thread, der einen sequentiellen Scan durchführt, weist für jede Tabelle, die er scannt, einen Puffer dieser Größe zu. Wenn Sie viele sequentielle Scans durchführen, möchten Sie diesen Wert möglicherweise erhöhen. Der Standardwert ist 131072 (128k), ich habe ihn auf 16773120 (16m) geändert
(3) Schlüsselpuffergröße:
Indexblöcke werden gepuffert und von allen Threads gemeinsam genutzt. key_buffer_size ist die für Indexblöcke verwendete Puffergröße. Erhöhen Sie sie so weit Sie können, um die Indizes besser handhaben zu können (für alle Lesevorgänge und mehrere Schreibvorgänge). Wenn Sie es zu groß machen, beginnt das System mit der Seitenauslagerung und wird erheblich langsamer. Der Standardwert ist 8388600 (8 M), mein MySQL-Host hat 2 GB Speicher, deshalb habe ich ihn auf 402649088 (400 MB) geändert.
4) back_log:
Die Anzahl der Verbindungen, die MySQL haben darf. Dies funktioniert, wenn der Haupt-MySQL-Thread in kurzer Zeit viele Verbindungsanforderungen erhält. Anschließend benötigt der Haupt-Thread einige (wenn auch kurze) Zeit, um die Verbindung zu überprüfen und einen neuen Thread zu starten.
Der back_log-Wert gibt an, wie viele Anfragen gestapelt werden können, bevor MySQL vorübergehend aufhört, auf neue Anfragen zu antworten. Sie müssen ihn nur erhöhen, wenn Sie in kurzer Zeit viele Verbindungen erwarten. Mit anderen Worten: Dieser Wert legt die Größe der Warteschlange für eingehende TCP/IP-Verbindungen fest. Ihr Betriebssystem hat seine eigenen Beschränkungen hinsichtlich der Warteschlangengröße. Der Versuch, den Backlog-Wert höher als das Limit Ihres Betriebssystems einzustellen, hat keine Auswirkungen.
Wenn Sie Ihre Host-Prozessliste beobachten und eine große Anzahl von 264084 | nicht authentifizierten Benutzern | xxx.xxx.xxx.xxx | null | connect | null | login | null finden, die auf die Verbindung warten, sollten Sie den Wert von back_log erhöhen. Der Standardwert ist 50, ich habe ihn auf 500 geändert.
(5) interactive_timeout:
Die Anzahl der Sekunden, die der Server auf eine Aktion bei einer interaktiven Verbindung wartet, bevor sie geschlossen wird. Ein interaktiver Client ist als ein Client definiert, der die Option client_interactive für mysql_real_connect() verwendet. Der Standardwert ist 28800, ich habe ihn auf 7200 geändert.
(6) Sortierpuffer:
Jedem Thread, der eine Sortierung durchführen muss, wird ein Puffer dieser Größe zugewiesen. Durch Erhöhen dieses Wertes werden Order-By- oder Group-By-Vorgänge beschleunigt. Der Standardwert ist 2097144 (2 m), ich habe ihn auf 16777208 (16 m) geändert.
(7) Tabellencache:
Die Anzahl der geöffneten Tabellen für alle Threads. Durch Erhöhen dieses Wertes kann sich die Anzahl der von mysqld benötigten Dateideskriptoren erhöhen. MySQL erfordert 2 Dateideskriptoren für jede eindeutig geöffnete Tabelle. Der Standardwert ist 64, ich habe ihn auf 512 geändert.
(8) Thread-Cache-Größe:
Die Anzahl der darin gespeicherten Threads kann wiederverwendet werden. Wenn dies der Fall ist, wird der neue Thread aus dem Cache genommen und beim Trennen der Verbindung wird der Thread des Clients in den Cache gestellt, sofern Platz vorhanden ist. Wenn viele neue Threads vorhanden sind, kann dieser Variablenwert erhöht werden, um die Leistung zu verbessern. Durch Vergleichen der Statusvariablen „Verbindungen“ und „Threads_created“ können Sie die Rolle dieser Variablen erkennen. Ich habe es auf 80 eingestellt.
(9) MySQL-Suchfunktion Verwenden Sie MySQL, um ohne Berücksichtigung der Groß- und Kleinschreibung und auf Chinesisch zu suchen. Geben Sie beim Starten von mysqld einfach --default-character-set=gb2312 an.
(10) Wartezeit:
Die Anzahl der Sekunden, die der Server auf eine Aktion bei einer Verbindung wartet, bevor sie geschlossen wird. Der Standardwert ist 28800, ich habe ihn auf 7200 geändert.

Hinweis: Parameter können durch Ändern der Datei /etc/my.cnf und Neustarten von MySQL angepasst werden. Dies ist eine relativ vorsichtige Arbeit, und die obigen Ergebnisse sind nur meine Meinungen. Sie können basierend auf den Hardwarebedingungen Ihres eigenen Hosts (insbesondere der Speichergröße) weitere Änderungen vornehmen.