mySQL Tuning in wenigen Minuten
Lange Zeit litt mySQL ja an Kinderkrankheiten und einem wenig vollständigen SQL-92 Standard. Mittlerweile scheint sich dies allerdings maßgeblich verbessert zu haben und mySQL wurde zu einem ernstzunehmenden DBMS. Es gibt also offensichtlich ein oder zwei gute Gründe mySQL zu benutzen. Um so wichtiger wird es, mySQL auch entsprechend zu optimieren, denn die Standardkonfiguration ist eher bescheiden und langsam, insbesondere die Distributionskonfiguration vieler Linuxsysteme, etwa Debian und Ubuntu.
Praktische Helferlein
Schnell und unkompliziert funktioniert eine Erstdiagnose mit dem mySQL Performance Tuning Script. Dabei gibt dieses einfache Shellscript sehr zielsicher und hilfreich Tipps und Vorschläge die mySQL Performance zu verbessern. So gibt es nützliche Hinweise zu Table-, Thread und Querycache.
Zielsicher gibt das Shellscript von Matthew Montgomery auch Hinweise zu wichtigen mySQL-Variablen wie max_connections (Maximale Verbindungsanzahl), key_buffer_size (Tabellenindexgröße im Buffer für myISAM), tmp_table_size (maximale Größe für temporäre Tabellen im Arbeitsspeicher). Befolgt man gegebene Tipps hat man schon eine solide Ausgangslage.
Sehr hilfreich sind hier die Hinweise auf eben jenen Tabellencache, der Tabellendefinitionen im Arbeitsspeicher hält. Das beschleunigt den Zugriff auf Tabellen maßgeblich, wobei hier im Regelfall sehr kleine Datenmengen anfallen, sofern eben nicht Hunderttausende Tabellen in einer Datenbank stehen.
Der Querycache sollte sich mittlerweile umgesprochen haben. Potentiell lassen sich damit große Performancegewinne erzielen, wenn auf eine Datenbank wenig(er) Schreibzugriffe stattfinden, so dass der Cache auch effektiv greifen kann.
InnoDB
InnoDB ist eine ACID-konforme mySQL-Engine und damit wunderbar parallelisierbar. Wo die traditionelle myISAM-Engine versagt, wenn mehrere konkurrierende Schreibzugriffe auftreten, weil diese dort nur serialisiert werden können, kann die InnoDB-Engine hier große Pluspunkte sammeln und maßgeblich zum Performancegewinn beitragen. Je nach Maschine und Hardware, kann InnoDB zu massiven Performancegewinnen führen.
InnoDB lohnt nicht für jede Datenbank und jede Tabelle, kann aber richtig eingesetzt von Vorteil sein. Es spricht dabei auch nichts dagegen, myISAM und InnoDB-Tabellen in einer Datenbank konkurrierend zu verwenden. Dabei gibt es keine Einschränkungen in der Benutzbarkeit oder der Kompatibilität zu vorhandenen Queries. So ist es auch ohne Probleme möglich, Joins über Tabellen verschiedener Engines zu machen. Auf InnoDB kann - so in der mySQL-Binary vorhanden - im laufenden Betrieb umgestellt werden. Ob InnoDB vorhanden ist, zeigt ein entsprechendes Kommando:
mysql> SHOW ENGINES; +------------+----------+-------------------------------------------------+ | Engine | Support | Comment | +------------+----------+--------------------------------------------- ---+ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with [..] | | MEMORY | YES | Hash based, stored in memory, useful [..] | | InnoDB | YES | Supports transactions, row-level locki [..] | [..] +------------+----------+-------------------------------------------------+ 12 rows in set (0.02 sec)
Eine Änderung auf eine andere Engine ist schließlich relativ stressfrei mit ALTER TABLE name ENGINE=InnoDB.
Es gibt aber auch eine ganze Menge an InnoDB-Parameter, die konfigurierbar sind. Diese haben maßgeblichen und großen Einfluss auf die mySQL Performance. So ist es für InnoDB vorteilhaft möglichst viel Speicher zu reservieren, was man bei vorhandenen Ressourcen, etwa einer dedizierten Datenbankmaschine durchaus verkraften kann.
Am wichtigsten ist hier innodb_buffer_pool_size. Darüber wird festgelegt, wieviel Speicher mySQL für die vorhandenen InnoDB-Tabellen allokieren darf. Sinnvollerweise sind dies auch bis zu 80% des vorhandenen Hauptspeichers. Auf 32-Bit-Architekturen sind dies aber 3 GB RAM pro Prozess, gerade für moderne Datenbankserver schon mal zu wenig - denn mySQL ist threaded, nach außen für den Kernel also ein einziger Prozess.
Bei hoher Anzahl von Schreibqueries hilft ein richtig gesetztes innodb_flush_log_at_trx_commit. Darüber wird konfiguriert, wie mySQL erfolgreich abgeschlossene Transaktionen (bei Abschluss mit “commit“) behandeln soll. Zur Auswahl stehen etwa sofortiges Schreiben und verzögertes Schreiben, einmal pro Sekunde. Letzteres ist in Maßstäben einer Festplatte eine sehr große Zeiteinheit und sollte daher auf jeden Fall aktiviert werden. Ein Wert von “2″ bewirkt hier nämlich eben dies, mit dem Vorteil, dass Änderungen dennoch in den gebufferten (Arbeits-)speicher geschrieben werden.
Loading...