So verbessern Sie die Leistung von MySQL-Servern mit der richtigen Optimierung

Festlegen des Speichers für Sortierpuffer und temporäre Tabellen

Sortiervorgänge und die Verwendung temporärer Tabellen können viel Arbeitsspeicher erfordern, insbesondere wenn die ausgeführten Abfragen komplex sind oder große Datasets umfassen. Durch Optimieren dieser Einstellungen können die Abfrageleistung und die Effizienz der Speicherauslastung verbessert werden.

Sort Buffer Size:

Der Parameter sort_buffer_size gibt die Größe des Puffers an, der für den Sortiervorgang verwendet wird. Dieser Speicher wird pro Verbindung zugeordnet, die die Sortierung durchführt. Eine größere Puffergröße kann die Sortierleistung verbessern, erhöht aber auch den Speicherverbrauch. Die richtige Balance zu finden, ist der Schlüssel zur Optimierung. Sie können z. B. klein anfangen und diese schrittweise erhöhen, während Sie die Leistung überwachen.

Vorläufige Tabelle:

Operationen, die temporäre Tabellen erfordern, wie z. B. die GROUP BY-Operation oder die Verwendung von Aggregatfunktionen, verwenden Arbeitsspeicher, der durch die Parameter tmp_table_size und max_heap_table_size definiert wird. Wenn die Größe der temporären Tabelle den angegebenen Wert überschreitet, wird die Tabelle auf dem Datenträger gespeichert, was die Leistung beeinträchtigen kann. Daher kann das Festlegen eines ausreichend großen Werts für diesen Parameter dazu beitragen, eine hohe Leistung aufrechtzuerhalten.

• tmp_table_size: Gibt die maximale Größe temporärer Tabellen an, die im Arbeitsspeicher erstellt werden.
• max_heap_table_size: Gibt die maximale Größe von HEAP-Tabellen (In-Memory-Tabellen) an.

Durch Anpassen dieser Werte entsprechend der Arbeitsauslastung und der Speicherverfügbarkeit können die Effizienz der Speicherauslastung und die Abfrageleistung erheblich verbessert werden.

4. Festlegen der Speicherauslastung für Abfragen

Berechnen des für die Abfrageausführung erforderlichen Arbeitsspeichers

Um die Speicherauslastung während der Abfrageausführung zu optimieren, ist es wichtig zu verstehen, wie viel Arbeitsspeicher für die auszuführende Abfrage erforderlich ist. Zu den Parametern, die sich auf die Speicherauslastung für die Abfrageausführung auswirken, gehören:

• join_buffer_size: Wird verwendet, wenn MySQL Verknüpfungsvorgänge ohne Index ausführt.
• sort_buffer_size: Wird für Sortiervorgänge verwendet, die einen Speicherpuffer erfordern.
• read_buffer_size: Der Puffer, der für Tabellenscanvorgänge verwendet wird.
• read_rnd_buffer_size: Wird nach dem Sortieren verwendet, um Zeilen in der Reihenfolge zu lesen, in der sortiert wurde.
• tmp_table_size und max_heap_table_size: Gibt die maximale Größe der temporären Tabelle an, die im Arbeitsspeicher erstellt wird.

Um den für die Abfrageausführung erforderlichen Gesamtarbeitsspeicher zu berechnen, müssen Sie den Typ und die Komplexität der ausgeführten Abfrage sowie die Parameter berücksichtigen, die sich auf die Speicherauslastung auswirken. Eine Abfrage, die einen großen gemeinsamen Vorgang oder die Sortierung eines großen Datasets umfasst, benötigt z. B. mehr Arbeitsspeicher als eine einfache Abfrage.

Angenommen, Sie verfügen über mehrere Abfragen, die einen Verknüpfungspuffer und einen Sortierpuffer erfordern, dann kann der erforderliche Arbeitsspeicher wie folgt berechnet werden:

Total Memory for Query Execution = join_buffer_size   + sort_buffer_size + read_buffer_size   read_rnd_buffer_size

Wenn viele Abfragen gleichzeitig ausgeführt werden, ist der erforderliche Gesamtarbeitsspeicher ein Vielfaches des Arbeitsspeichers pro Abfrage.

Verwalten des Arbeitsspeichers zur Vermeidung von Speicherengpässen

Um Speicherengpässe während der Abfrageausführung zu vermeiden, ist es wichtig, die Speicherparameter mit Bedacht anzupassen und sicherzustellen, dass die Gesamtspeicherauslastung die physische Speicherkapazität des Servers nicht überschreitet. Zu den Schritten, die zum effektiven Organisieren des Speichers unternommen werden können, gehören: