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

Physischer und virtueller Speicher:

Der Server muss so konfiguriert werden, dass der von MySQL verwendete Arbeitsspeicher den verfügbaren physischen Speicher nicht überschreitet, um die Verwendung von virtuellem Speicher zu vermeiden, der die Leistung beeinträchtigen kann.

Speicherbeschränkungen nach Betriebssystem:

Das Betriebssystem hat Einschränkungen hinsichtlich des Speichers, der von MySQL-Prozessen verwendet werden kann. Auf 32-Bit-Systemen liegt diese Grenze in der Regel bei etwa 4 GB, während sie auf 64-Bit-Systemen viel höher ist, was eine größere Speicherauslastung ermöglicht.

Puffer und Cache:

MySQL verfügt über mehrere konfigurierbare Puffer und Caches, z. B. InnoDB-Poolpuffer, MyISAM-Schlüsselpuffer und Abfragecaches. Diese Einstellung sollte an die verfügbare Speicherkapazität angepasst werden.

Festlegen des Arbeitsspeichers für Verbindungen und Abfragen

Die Speicherauslastung in MySQL wird nicht nur von Puffern und Caches beeinflusst, sondern auch von der Anzahl der Verbindungen und der Art der ausgeführten Abfragen. Einige der Schritte zum Einrichten des Arbeitsspeichers für Verbindungen und Abfragen sind:

Thread Concurrency:

Legt die Anzahl der Threads fest, die gleichzeitig ausgeführt werden können. Die Einstellung innodb_thread_concurrency kann verwendet werden, um die Anzahl der gleichzeitig ausgeführten InnoDB-Threads zu begrenzen und so den Wettbewerb um Speicherressourcen zu verringern.

Speicher pro Verbindung:

Jede Verbindung zu MySQL benötigt eine bestimmte Menge an Arbeitsspeicher, um Abfragen auszuführen. Parameter wie sort_buffer_size, join_buffer_size und read_buffer_size können konfiguriert werden, um die Menge an Arbeitsspeicher zu bestimmen, die von einem bestimmten Abfragevorgang verwendet wird. sort_buffer_size gibt z. B. den Arbeitsspeicher an, der für den Sortiervorgang verwendet wird, und join_buffer_size gibt den Arbeitsspeicher für den Verknüpfungsvorgang an.

Query Cache:

Das Aktivieren und Konfigurieren der Abfragezwischenspeicherung kann dazu beitragen, die Belastung des Servers zu reduzieren, indem die Ergebnisse häufig ausgeführter Abfragen gespeichert werden. Der Parameter query_cache_size gibt die Gesamtgröße des Caches an, während query_cache_limit die maximale Größe der Abfrageergebnisse begrenzt, die zwischengespeichert werden können.

InnoDB Buffer Pool:

Bei InnoDB-Tabellen ist der Pufferpool der Hauptspeicherbereich, der zum Speichern von Daten und Indizes verwendet wird. innodb_buffer_pool_size Einstellungen sollten an die Menge des verfügbaren physischen Speichers angepasst werden, um sicherzustellen, dass Lese- und Schreibvorgänge effizient ausgeführt werden können, ohne häufig auf den Datenträger zugreifen zu müssen.

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