InnoDB optimieren

Vergrößerung des InnoDB-Bufferpools


InnoDB verwaltet einen Cache-Speicher, den sogenannten Buffer Pool. Mit dem Parameter innodb_buffer_pool_size können Sie die Größe dieses Pools definieren. Auf einem dedizierten Datenbank Server kann dieser Pool durchaus bis zu 80% des gesamten RAMs beanspruchen. Je größer Sie diesen Wert einstellen, umso weniger Festplattenzugriffe sind für den Zugriff auf die Tabellendaten erforderlich. Stellen Sie den Wert allerdings nicht zu groß ein, da es ansonsten zu Engpässen kommen kann.


Mit der folgenden Abfrage kann die benötigte Größe errechnet werden:

SQL: innodb_buffer_pool_size ermitteln
  1. SELECT CONCAT(ROUND(KBS/POWER(1024,
  2. IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
  3. SUBSTR(' KMG',IF(PowerOf1024<0,0,
  4. IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
  5. FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
  6. WHERE engine='InnoDB') A,
  7. (SELECT 3 PowerOf1024) B;


Transaktions-Log


Standardmäßig ist der Parameter innodb_flush_log_at_trx_commit auf den Wert 1 gesetzt. In dem Fall wird der Transaktions-Log nach jedem Commit auf die Festplatte geschrieben. Da Plattenzugriffe sehr langsam sind, kostet dies reichlich Geschwindigkeit. Wenn Sie den Wert auf 2 stellen, dann werden die Commits im Cache zwischengespeichert und das Schreiben auf die Festplatte auf 1x die Sekunde verzögert. Das ist bei Festplatten eine große Zeitspanne und erhöht die Geschwindigkeit daher um ein vielfaches.


Erhöhung des Autoextend-Werts


Der Parameter innodb_autoextend_increment sollte optimalerweise 1 - 5% des zur Verfügung stehenden Festplattenspeichers groß sein (Maximaler Wert: 1000M). Je niedriger der Wert ist um so stärker ist die Fragmentierung.

Wenn Sie diesen Wert ändern wollen, dann fahren Sie vorher den MySQL Server herunter und stellen Sie sicher das dass es keine Informationen zu ausstehenden Transaktionen im Log (/var/lib/mysql/ibdata1) gibt.


Kopieren Sie die alten Logdateien an einen sicheren Ort, falls etwas während des Herunterfahrens schief geht und Sie die Dateien benötigen, um den Tablespace wiederherzustellen. Löschen Sie danach die alten Logdateien aus dem Logdateiverzeichnis (/var/lib/mysql/ibdata1). Nach der Änderung des Parameters können Sie den MySQL Server neustarten. Es werden dann automatisch neue Log Dateien angelegt.


Logdatei


Der Parameter innodb_log_file_size sollte maximal 25% von der Größe des innodb_buffer_pool_size betragen. Je größer die Logs sind um so schneller sind im normalen Betrieb die Zugriffe. Allerdings dauert die Wiederherstellung bei einem Fehler dann länger.


MySQL Performance Tuning Primer Script


Eine weitere Hilfe zur Optimierung von MySQL ist das Script tuning-primer.sh von Matthew Montgomery (http://day32.com/MySQL/).


Laden Sie das Script per wget http://www.day32.com/MySQL/tuning-primer.sh auf Ihren Server und führen Sie es dann aus.