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

3. Festlegen der Speicherauslastung für Verbindungen

Berechnung des für die Verbindung erforderlichen Speichers

Die Verwaltung des für jede MySQL-Verbindung verwendeten Speichers ist unerlässlich, um sicherzustellen, dass der Server mehrere Verbindungen effizient verarbeiten kann, ohne dass der Arbeitsspeicher ausgeht. Jede Verbindung zu einem MySQL-Server verwendet eine bestimmte Menge an Arbeitsspeicher, die je nach Konfiguration und Art der ausgeführten Abfrage variiert. Zu den Speicherkomponenten, die bei jeder Verbindung berücksichtigt werden müssen, gehören:

• Thread Stack: Jede Verbindung benötigt eine bestimmte Menge an Speicher für den Thread-Stack, die durch die thread_stack Parameter bestimmt wird.
• Sort Buffer: Der für die Sequenzierungsoperation verwendete Speicher wird durch die Parameter sort_buffer_size bestimmt.
• Join Buffer: Der für den Verknüpfungsvorgang verwendete Arbeitsspeicher wird durch den Parameter join_buffer_size bestimmt.
• Read Buffer: Der Speicher, der für Leseoperationen von der Festplatte verwendet wird, wird durch die Parameter von read_buffer_size bestimmt.

Um den für jede Verbindung erforderlichen Gesamtarbeitsspeicher zu berechnen, können Sie die Werte aus den verwendeten Puffern und Stacks addieren. Zum Beispiel:

Total Memory per Connection = thread_stack + sort_buffer_size + join_buffer_size   read_buffer_size

Wenn von Ihrem MySQL-Server erwartet wird, dass er viele Verbindungen gleichzeitig verarbeiten kann, müssen Sie sicherstellen, dass der für alle Verbindungen erforderliche Gesamtspeicher die physische Speicherkapazität des Servers nicht überschreitet. Wenn Sie z. B. 100 gleichzeitige Verbindungen erwarten, gehen Sie wie folgt vor:

Total Memory for All Connections =10  × Total Memory per Connection

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:

Anpassen der Speicherparameter:

Passen Sie Parameter wie join_buffer_size, sort_buffer_size, read_buffer_size und tmp_table_size schrittweise an, während Sie die Speicherauslastung und die Serverleistung überwachen. Stellen Sie sicher, dass diese Werte nicht so groß sind, dass sie zu Speicherengpässen führen, aber sie sind auch groß genug, um eine gute Leistung zu gewährleisten.

Kontinuierliche Überwachung und Anpassung:

Überwachen Sie regelmäßig die Speicherauslastung und die Abfrageleistung mithilfe von Überwachungstools wie MySQL Performance Schema oder Überwachungstools von Drittanbietern. Passen Sie auf der Grundlage von Überwachungsdaten Speicherparameter an, um die Leistung zu optimieren und Speicherengpässe zu vermeiden.

Effiziente Abfrageeinstellungen:

Optimieren Sie Abfragen, um die Speicherauslastung zu reduzieren, z. B. durch die Verwendung der richtigen Indizes, die Vermeidung unnötiger Verknüpfungsvorgänge und die Begrenzung der gleichzeitig verarbeiteten Datenmenge. Eine gut optimierte Abfrage benötigt weniger Arbeitsspeicher und wird schneller ausgeführt.

Maximale Verbindungseinschränkungen:

Legen Sie mit max_connections Parametern eine Obergrenze für die Anzahl gleichzeitiger Verbindungen fest. Auf diese Weise wird sichergestellt, dass die Gesamtspeicherauslastung die physische Speicherkapazität des Servers nicht überschreitet.