Performance Tuning des MySQL Servers

Diese Anleitung soll Ihnen helfen, die Antwortzeiten Ihres MySQL Servers bei erhöhter Last durch viele Anfragen und viele Daten zu verbessern.

Die vorgeschlagenen Werte basieren auf Erfahrungen und haben jeweils in unseren Setups die gewünschte Wirkung erzielt. Da sich aber jeder Datenbestand vom Umfang und Struktur her unterschiedet müssen im Zweifelsfall die Parameter durch einen MySQL Spezialisten an die Datenstruktur und -grösse angeglichen werden.

Diese Werte setzen ebenfalls voraus, dass Ihr MySQL Server ausschliesslich MyISAM Tabellen verwendet, ansonsten sind noch weitere Werte anzupassen (z.B. beim Einsatz von InnoDB).

Folgende MySQL Server Einstellung sind massgeblich verantwortlich für die erzielte Performance bei Lese- resp. Schreibabfragen:

• key_buffer_size
• table_cache
• thread_cache_size
• query_cache_size

Melden Sie sich im Control Panel Ihrer BlueOnyx VPS an.

Um die MySQL Konfiguration zu ändern navigieren Sie zu Server Management | Network Services | MySQL Config | /etc/my.cnf Editor.

Um die MySQL Parameter des laufenden Dienstes zu überprüfen navigieren Sie zu Personal Profile | phpMyAdmin | Show MySQL system variables.

Dieser Wert kann auf ca. 50-100% der effektiven Datenmenge in der Datenbank gesetzt werden. Wenn also Ihre Datenbanken alle zusammen rund 30MB Speicher benutzen stellen Sie diesen Wert auf z.B. 16M, 24M o. 32M ein.

Um den für Ihr Setup besten Wert exakter zu erruieren kontrollieren Sie folgende Statusvariablen im phpMyAdmin unter Show MySQL runtime information:

• Key_blocks_unused
• Verhältnis von Key_read_requests zu Key_reads, die Anzahl Key_read_requests sollte idealerweise viel höher sein als die der Key_reads.

Setzen Sie diesen Wert so hoch, dass pro Verbindung und Tabelle jeweils ein table_cache vorhanden ist, dies verhindert, dass Tabellen rechenintensiv erneut geöffnet werden müssen. Wenn Sie also z.B. 50 Tabellen und 5 gleichzeitige Nutzer haben ist es sinnvoll diesen Wert auf über 250 einzustellen, z.B. 500.

Mit 512 - 1024 haben Sie hier für einen Datenbankserver mit rund 100 Tabellen und mittlerer Last eine gute Einstellung.

Prüfen Sie gelegentlich den Wert für Opened_tables unter Show MySQL runtime information, dieser Wert sollte nicht zu hoch sein sonst sollten den Wert von table_cache erhöhen.

Das Erzeugen eines neuen Threads für eine eingehende Verbindung ist sehr ressourcen-intensiv so dass es wenn immer möglich vermieden werden sollte.

Stellen Sie den thread_cache_size auf 12 oder 16 ein und prüfen Sie nach einiger Zeit den Wert der von Threads_created unter Show MySQL runtime information, ist dieser hoch, sollten Sie den thread_cache_size in Schritten von 4 oder 8 erhöhen.

In den meisten Fällen ist ein Wert zwischen 32M und 128M sinnvoll. Prüfen Sie dazu die Werte der folgenden Statusvariablen unter Show MySQL runtime information:

• Qcache_hits
• Qcache_lowmem_prunes

Ist die Anzahl von Qcache_lowmem_prunes rel. hoch so sollten Sie den Wert für query_cache_size weiter erhöhen. Werte weit über 512M machen aber meist nur wenig Sinn.

Achtung: Wenn Ihre Applikation bereits über eigene Caching Strategien verfügt kann diese Einstellung unnötig sein oder jenachdem sogar kontraproduktiv wirken.

Wenn Sie die optimalen Werte für Ihren Datenbankserver erruiert haben, können Sie diese unter Server Management | Network Services | MySQL Config | /etc/my.cnf Editor einfügen und danach den MySQL Dienst zu starten. Ihre Konfigurationsdatei sieht dann etwa so aus:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

key_buffer_size=16M
table_cache=512
thread_cache_size=16
query_cache_size=64M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid