:::: MENU ::::

WebDevExp

Meine Erfahrungen in der Webentwicklerwelt

High Performance MySQL Konfiguration – my.cnf

mysql

High Performance MySQL Konfiguration – my.cnf

Ich beschäftige mich aus beruflichen Gründen schon recht lange mit der Thematik und möchte das gesammelte Wissen nun mal niederschreiben.

Es gibt definitiv keine Konfiguration, die allgemeingültig ist, aber ich versuche hiermit eine Hilfestellung zu geben, wie man die besten Werte für die eigenen Bedürfnisse findet.

Nicht nur der Server spielt eine Rolle, auch die Storage-Engine, um sich viel Stress zu ersparen, sollte man im vorraus planen, was man braucht und für welche Engine man sich entscheidet. Auch hier gibt es keine Universallösung, jede hat ihre Vor- und Nachteile, aber darauf will ich in diesem Eintrag nicht eingehen.

Da ich für das aktuell laufende Projekt nur InnoDB einsetze, beziehen sich die Ratschläge auf eine reine InnoDB Konfiguration und sollten ab MySQL Version 5.0 funktionieren.

innodb_dirty_pages_pct
Gibt an, wie viele  InnoDB-Buffer-Pages im RAM vorhanden sein dürfen, ohne auf Festplatte geschrieben worden zu sein. Standard ist 90%, was für die meisten Umgebungen absolut OK sein sollte.

thread_cache_size
Wie viele Threads dürfen sich im Cache befinden? Jede Verbindung zum Server braucht einen eigenen Thread, ist die Verbindung beendet, kann der Thread im Cache gelagert werden und steht der nächsten Verbindung zur Verfügung. Threads zu erstellen ist unnötiger Overhead, „SHOW GLOBAL STATUS“ auf der MySQL Konsole gibt in der Variable „threads_created“ aus, wie viele Threads erstellt werden. Dieser Wert sollte um maximal 10 pro Sekunde steigen, steigt er schneller, kann der Thread-cache vergrößert werden.

table_definition_cache
Sollte größer oder gleich der Anzahl der vorhandenen Tabellen sein um unnötigen I/O Overhead zu ersparen.

innodb_open_files
Sollte größer oder gleich der Anzahl der vorhandenen .ibd Dateien im MySQL-Datenverzeichnis sein (bei Debian meistens /var/lib/mysql).

innodb_log_buffer_size
Die globale Variable „innodb_os_log_written“ hilft hier bei der Ermittlung eines sinnvollen Wertes, der Wert sollte nicht zu schnell steigen, hier kann I/O Performance gewonnen werden.

skip_name_resolve
Sollte aktiv sein, damit nicht für jeden Client 2 DNS-Lookups stattfinden, gleichzeitig bedeutet dies, dass Berechtigungen nur noch auf IP und nicht auf Hostnamen-Ebene verteilt werden können.

innodb_flush_method
Falls ein Hardware-Raid mit ordentlichem Cache und Battery-Backup genutzt wird, sollte hier 0_direct eingestellt werden, um zu verhindern, dass OS-Buffer genutzt werden, welche das Logfile unbrauchbar machen könnten, wenn der Server abstürzt.

innodb_flush_log_at_trx
Sollte auf 1 gestellt werden, damit jede beendete Transaktion sofort im Binarylog landet, um Datenverlust zu verhindern, falls der Server abstürzt. Der Wert 1 ist der beste Kompromiss zwischen Performance und Datensicherheit!

Diverse Key-Buffers und andere Caches (ausgenommen dem Query-Cache) können sehr klein gehalten werden (nicht abschalten, MySQL nutzt MyISAM für die Metatabellen). Hier würde nur sinnlos RAM belegt werden!

Auf Linux-Systemen sollte die swappiness noch auf 0 oder 1 gestellt werden, um zu verhindern, dass der Kernel MySQL Buffers in den SWAP-Space verschiebt, falls diese länger nicht genutzt werden. Swap-I/O ist weitaus schlimmer als normaler HDD-I/O, da MySQL „denkt“ es würde mit schnellem RAM arbeiten, welcher in Wirklichkeit aber auf der HDD im Swap zu finden ist. Auf Debian-Systemen kann man diesen Wert mittels /etc/sysctl.conf setzen (vm.swappiness = 0) und danach „sysctl -p“ ausführen.

Der restlich verfügbare Arbeitsspeicher sollte dem InnoDB-Buffer-Pool zur Verfügung gestellt werden, ist dieser groß genug, kann auf nahezu alle HDD-Leseabfragen verzichtet werden und es entsteht nahezu nur noch Schreib-I/O.

Es gibt noch weitere Einstellungen bezüglich des InnoDB-Tablespace und allgemeinen Einstellungen, falls hier noch Intresse besteht, hinterlasst nen Kommentar, werde dann versuchen zeitnah einen weiteren Beitrag zu schreiben.

Leave a comment

Loading Facebook Comments ...