Optimieren Sie die MySQL-Datenbank unter hoher Last unter Linux gründlich

Gleichzeitig nimmt die Zahl der Online-Besuche weiter zu. Wenn der Server mit 1G-Speicher offensichtlich stark ausgelastet ist, stürzt er sogar jeden Tag ab oder friert ab und zu ein. Dieses Problem beschäftigt mich schon seit mehr als der Hälfte pro Monat. MySQL verwendet einen sehr skalierbaren Algorithmus, sodass Sie ihn normalerweise mit weniger Speicher ausführen oder MySQL mehr Speicher geben können, um eine bessere Leistung zu erzielen.

Nach der Installation von MySQL sollten sich die Konfigurationsdateien im Verzeichnis /usr/local/mysql/share/mysql befinden. Es gibt mehrere Konfigurationsdateien, darunter my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf , Websites mit unterschiedlichen Verkehrsströmen und Serverumgebungen mit unterschiedlichen Konfigurationen erfordern natürlich unterschiedliche Konfigurationsdateien.

Unter normalen Umständen kann die Konfigurationsdatei my-medium.cnf die meisten unserer Anforderungen erfüllen; im Allgemeinen kopieren wir die Konfigurationsdatei nach /etc/my.cnf und müssen diese Konfigurationsdatei nur ändern, indem wir mysqladmin-Variablen „extended-status –u“ verwenden root –p kann die aktuellen Parameter sehen. Es gibt drei Konfigurationsparameter, die am wichtigsten sind, nämlich key_buffer_size, query_cache_size, table_cache .

key_buffer_size funktioniert nur für MyISAM-Tabellen,

key_buffer_size gibt die Größe des Indexpuffers an, der die Geschwindigkeit der Indexverarbeitung bestimmt, insbesondere die Geschwindigkeit des Indexlesens. Im Allgemeinen legen wir es auf 16M fest. Tatsächlich reicht diese Zahl bei weitem nicht aus, um die Statuswerte Key_read_requests und Key_reads zu überprüfen. Das Verhältnis key_reads / key_read_requests sollte so niedrig wie möglich sein, mindestens 1:100, besser ist 1:1000 (der obige Statuswert kann mit SHOW STATUS LIKE 'key_read%' ermittelt werden). Oder wenn Sie phpmyadmin installiert haben, können Sie es über den laufenden Status des Servers sehen. Der Autor empfiehlt die Verwendung von phpmyadmin zur Verwaltung von MySQL. Die folgenden Statuswerte sind die Beispielanalyse, die ich über phpmyadmin erhalten habe.

Dieser Server läuft seit 20 Tagen
key_buffer_size – 128M
key_read_requests – 650759289
key_reads – 79112
Das Verhältnis liegt nahe bei 1:8000 und der Gesundheitszustand ist sehr gut.

Eine andere Möglichkeit, key_buffer_size zu schätzen, besteht darin, den von den Indizes jeder Tabelle in Ihrer Website-Datenbank belegten Speicherplatz zu addieren. Nehmen Sie diesen Server als Beispiel: Die Indizes mehrerer größerer Tabellen ergeben zusammen etwa 125 MB größer werden.

Ab 4.0.1 bietet MySQL einen Mechanismus zur Abfragepufferung. Mithilfe der Abfragepufferung speichert MySQL die SELECT-Anweisung und die Abfrageergebnisse im Puffer. In Zukunft werden die Ergebnisse für dieselbe SELECT-Anweisung (Groß- und Kleinschreibung beachten) direkt aus dem Puffer gelesen. Laut MySQL-Benutzerhandbuch kann durch die Verwendung der Abfragepufferung eine Effizienz von bis zu 238 % erreicht werden.

Durch Anpassen der folgenden Parameter können Sie feststellen, ob die Einstellung query_cache_size sinnvoll ist.
Qcache-Einfügungen
Qcache-Treffer
Qcache Lowmem-Pflaumen
Qcache-freie Blöcke
Qcache-Gesamtblöcke

Wenn der Wert von Qcache_lowmem_prunes sehr groß ist, bedeutet dies, dass die Pufferung häufig unzureichend ist. Wenn der Wert von Qcache_hits jedoch sehr groß ist, bedeutet dies, dass der Abfragepuffer sehr häufig verwendet wird Wenn der Wert von Qcache_hits niedrig ist, weist dies darauf hin, dass die Abfragewiederholungsrate sehr niedrig ist. In diesem Fall kann die Verwendung der Abfragepufferung in Betracht gezogen werden. Darüber hinaus kann das Hinzufügen von SQL_NO_CACHE zur SELECT-Anweisung eindeutig darauf hinweisen, dass der Abfragepuffer nicht verwendet wird.
Qcache_free_blocks: Wenn der Wert sehr groß ist, bedeutet dies, dass sich viele Fragmente im Puffer befinden. query_cache_type gibt an, ob die Abfragepufferung verwendet werden soll

Ich habe eingestellt:
query_cache_size = 32M
query_cache_type= 1

Rufen Sie den folgenden Statuswert ab:
Qcache-Abfragen im Cache 12737 geben die Anzahl der aktuell zwischengespeicherten Elemente an
Qcache fügt 20649006 ein
Qcache erreicht 79060095. Es scheint, dass die Rate wiederholter Abfragen ziemlich hoch ist.
Qcache lowmem prunes 617913 Es kommt so oft vor, dass der Cache zu niedrig ist.
Qcache nicht zwischengespeichert 189896
Qcache freier Speicher 18573912 Derzeit verbleibender Cache-Speicherplatz
Qcache freie Blöcke 5328 Diese Zahl scheint etwas groß und fragmentiert zu sein
Qcache-Gesamtblöcke 30953
Wenn der Speicher 32 MB zulässt, sollten Sie mehr hinzufügen.

table_cache gibt die Größe des Tabellencaches an. Immer wenn MySQL auf eine Tabelle zugreift und Platz im Tabellenpuffer vorhanden ist, wird die Tabelle geöffnet und darin abgelegt, was einen schnelleren Zugriff auf den Tabelleninhalt ermöglicht. Durch Überprüfen der Statuswerte Open_tables und Opened_tables zur Spitzenzeit können Sie entscheiden, ob Sie den Wert von table_cache erhöhen müssen. Wenn Sie feststellen, dass open_tables gleich table_cache ist und open_tables wächst, müssen Sie den Wert von table_cache erhöhen (der obige Statuswert kann mit SHOW STATUS LIKE 'Open%tables' abgerufen werden). Beachten Sie, dass table_cache nicht blind auf einen großen Wert gesetzt werden kann. Bei einer zu hohen Einstellung kann es zu unzureichenden Dateideskriptoren kommen, was zu instabiler Leistung oder Verbindungsfehlern führen kann.

Für Maschinen mit 1 GB Arbeitsspeicher beträgt der empfohlene Wert 128–256.

Autoreneinstellungen
table_cache = 256

Erhalten Sie den folgenden Status:
Offene Tische 256
Offene Tische 9046
Obwohl open_tables bereits gleich table_cache ist, läuft es im Verhältnis zur Serverlaufzeit seit 20 Tagen und der Wert von open_tables ist ebenfalls sehr niedrig. Daher dürfte eine Erhöhung des Wertes von table_cache kaum von Nutzen sein. Wenn der obige Wert nach 6 Stunden Laufzeit erscheint, sollten Sie eine Erhöhung von table_cache in Betracht ziehen.

Wenn Sie keine Binärprotokolle aufzeichnen müssen, deaktivieren Sie diese Funktion. Beachten Sie, dass Sie die Daten vor dem Problem nicht wiederherstellen können. Das Binärprotokoll enthält alle Anweisungen, die Daten aktualisieren Der Zweck besteht darin, die Datenbank so weit wie möglich wiederherzustellen. Wenn Sie eine synchrone Replikation (Replikation) durchführen, müssen Sie außerdem das Binärprotokoll zum Übertragen von Änderungen verwenden.

log_bin gibt die Protokolldatei an. Wenn kein Dateiname angegeben wird, generiert MySQL selbst den Standarddateinamen. MySQL fügt nach dem Dateinamen automatisch einen numerischen Index hinzu und generiert bei jedem Start des Dienstes eine neue Binärdatei neu. Verwenden Sie außerdem log-bin-index, um die Indexdatei anzugeben; verwenden Sie binlog-do-db, um die Datenbank anzugeben, die aufgezeichnet werden soll; verwenden Sie binlog-ignore-db, um die Datenbank anzugeben, die nicht aufgezeichnet werden soll. Hinweis: binlog-do-db und binlog-ignore-db geben jeweils nur eine Datenbank an. Die Angabe mehrerer Datenbanken erfordert mehrere Anweisungen. Darüber hinaus ändert MySQL alle Datenbanknamen in Kleinbuchstaben. Sie müssen bei der Angabe der Datenbank alle Namen in Kleinbuchstaben verwenden, sonst funktioniert es nicht.

Um diese Funktion zu deaktivieren, fügen Sie einfach ein #-Zeichen davor ein
#log-bin

Langsames Abfrageprotokoll aktivieren (langsames Abfrageprotokoll)

Das langsame Abfrageprotokoll ist nützlich, um problematische Abfragen aufzuspüren. Es zeichnet alle Abfragen auf, die long_query_time überprüft haben, und zeichnet bei Bedarf auch ohne Verwendung eines Index auf. Hier ist ein Beispiel für ein langsames Abfrageprotokoll:

Um langsame Abfrageprotokolle zu aktivieren, müssen Sie die Parameter log_slow_queries, long_query_times und log-queries-not-using-indexes festlegen.

log_slow_queries gibt Protokolldateien an. Wenn kein Dateiname angegeben wird, generiert MySQL selbst einen Standarddateinamen. long_query_times gibt den Schwellenwert für langsame Abfragen an. Der Standardwert beträgt 10 Sekunden. log-queries-not-using-indexes ist ein nach 4.1.0 eingeführter Parameter, der angibt, dass Abfragen, die keine Indizes verwenden, protokolliert werden. Der Autor setzt long_query_time=10

Autoreneinstellungen:

sort_buffer_size = 1M
max_connections=120
wait_timeout =120
back_log=100
read_buffer_size = 1M
thread_cache=32
Interactive_timeout=120
thread_concurrency = 4

Parameterbeschreibung:

Rückstand
Die Anzahl der Verbindungen, die MySQL haben muss. Dies funktioniert, wenn der Haupt-MySQL-Thread in kurzer Zeit viele Verbindungsanfragen erhält und der Haupt-Thread dann einige Zeit (wenn auch nur kurz) benötigt, um nach Verbindungen zu suchen und einen neuen Thread zu starten. Der back_log-Wert gibt an, wie viele Anfragen in kurzer Zeit im Stack gespeichert werden können, bevor MySQL vorübergehend keine neuen Anfragen mehr beantwortet. Nur wenn Sie viele Verbindungen in kurzer Zeit erwarten, müssen Sie ihn erhöhen, d. h. dieser Wert ist die Größe der Abhörwarteschlange für eingehende TCP/IP-Verbindungen. Ihr Betriebssystem hat seine eigene Grenze für diese Warteschlangengröße. Die Manpage für den Unix-Systemaufruf listen(2) sollte weitere Details enthalten. Sehen Sie in der Dokumentation Ihres Betriebssystems nach, um den Maximalwert dieser Variablen herauszufinden. Der Versuch, back_log auf einen höheren Wert als den Grenzwert Ihres Betriebssystems festzulegen, hat keine Auswirkung.
max_connections
Die maximale Anzahl gleichzeitiger Verbindungen beträgt 120. Wenn dieser Wert überschritten wird, erfolgt eine automatische Wiederherstellung und Probleme werden automatisch gelöst.

thread_cache
Ich konnte keine spezifischen Anweisungen finden, aber nach der Einstellung auf 32 wurden in 20 Tagen mehr als 400 Threads erstellt, während an einem Tag zuvor Tausende von Threads erstellt wurden, sodass es immer noch nützlich ist.

thread_concurrency
#Setzen Sie die Nummer Ihrer CPU auf x2. Wenn beispielsweise nur eine CPU vorhanden ist, ist thread_concurrency = 2
#Es gibt 2 CPUs, dann thread_concurrency=4

überspringen-innodb
#Innodb-Unterstützung entfernen