MySQL Systemvariablen – key_buffer_size

Ein Thema, welches in der MySQL-Szene gern unter den Teppich gekehrt wird, sind die MySQL-Systemvariablen. Und das nicht umsonst: Sie richtig zu setzen, ist so anwendungsspezifisch, dass sich die meisten davor scheuen. Auch das MySQL-Manual bietet hier nur versteckt Hinweise und schreibt in der Kurzfassung lediglich, dass key_buffer_size und table_cache zwei Systemvariablen sind, die die Performance des MySQL-Servers am Stärksten tangieren.

In diesem Blog-Beitrag wollen wir uns diesem heiklen Thema etwas nähern. Vorab: Eine Patentlösung gibt es leider nicht. Nach dem Prinzip „learning by doing“ wird an dieser Stelle jeder selbst ausprobieren müssen, welche Variable auf seinem System besonders anspringt. Dieser Beitrag soll lediglich einen kleinen Einstieg ermöglichen und grundlegende Tipps mit auf den Weg geben.
Da es an dieser Stelle den Rahmen sprengen würde, alle MySQL-Systemvariablen zu durchleuchten, wollen wir uns bei diesem Post auf die Variable key_buffer_size beschränken. In regelmäßigen Abständen werden auf PHP Performance weitere Systemvariablen vorgestellt und näher analyisiert.

Die mitunter wichtigste Systemvariable in Bezug auf die Performance des MySQL-Servers ist key_buffer_size. In dieser Variable werden die Indizes aller Spalten aller MyISAM-Tabellen zwischengespeichert. Auf InnoDB und andere Speicherengines hat sie keinen Einfluss. Das MySQL-Manual empfiehlt, 25-50 Prozent des gesamten Arbeitsspeichers dieser Variable zuzuweisen. Bei einem Server mit 512 MiB würde sich also eine Größe zwischen 128 und 256 MiB anbieten. Weiterer, sehr grober Anhaltspunkt ist in diesem Zusammenhang die Aussage, dass key_buffer_size so groß sein sollte wie die Summe aller Indizes. Soweit zur nüchternen Theorie:

In der Praxis erlebt man oftmals etwas anderes. Aus Erfahrung können wir sagen, dass meistens sogar ein Viertel oder noch weniger des Systemspeichers vollkommen ausreichend sind, auch dass die Summe aller Indizes stets kleiner sein soll als key_buffer_size, deckt sich nicht mit unseren Erfahrungswerten. Mit den von MySQL bereitgestellten Statusvariablen Key_blocks_unused und Key_blocks_used (SHOW status;) können sie jederzeit überprüfen, ob es einen Engpass gibt. Sobald Key_blocks_unused gegen 0 strebt, sollte man sich Gedanken machen, denn dann können die Indizes nicht mehr komplett im RAM gehalten werden.

Auf unserem Testsystem (512 MiB-Ram) haben wir zunächst probeweise key_buffer_size auf spartanische 8 MiB gesetzt. Die Systemvariable Key_blocks_used spuckte daraufhin den Wert 7754 aus; bei einer Standard-Blockgröße von 1024 KiB (key_cache_block_size) wurden also rund 7,6 MiB von 8 MiB belegt. Um zu schauen, wie viel sich der Server genehmigt, wenn er praktisch genügend Speichervolumen hat, haben wir key_buffer_size kurzfristig den Wert 256 MiB zugewiesen. Las man nun die Systemvariable Key_blocks_used aus, so bekam man als Rückgabewert die Zahl 20429 geliefert. Damit war klar, der Server benötigt rund 20 MiB, wenn man ihm „praktisch“ kein Limit setzt – das Limit war 256 MiB, von diesem war er aber offensichtlich weit enfernt.

Die 256 MiB waren also völlig überdimensioniert, die 8 MiB aber auch klar unterdimensioniert. Optimal wären bei dieser Konfiguration wahrscheinlich 20 MiB, und damit weniger als ein Zehntel des gesamten Arbeitsspeichers.
Da für gewöhnlich jedoch die Größe der Indizes linear steigt, sollte man im Vorfeld ruhig gutmütig an die Sache gehen und etwas oben drauf schlagen, zumal man die Statusvariablen vermutlich nicht wöchentlich überprüfen möchte, sondern eher alle paar Monate. Ob einem bei dieser Ausgangssituation 32 MiB genügen oder eventuell auch erst 64 MiB als ausreichend erachtet werden, hängt davon ab, wie stark der Wachstum der Datenbanken, genauer gesagt deren Indizes, ist.

Die Lehre dieses Artikels ist, wie eingangs erwähnt, „learning by doing“. Man sollte immer selbst schauen, wie bei sich persönlich die Sitatuation ausschaut, um die maximale Performance zu erzielen. Hätte man an dieser Stelle auf die Entwickler gehört, so hätte man wichtigen Speicher verschenkt, den man besser anderen Systemvariablen oder sogar anderen Programmen (bspw. Apache) zuweisen hätte können.
Zudem wird empfohlen, niemals mehr als 50 Prozent des gesamten Speicher key_buffer_size zuzuweisen, dies führt selbst laut den MySQL-Entwicklern zu einer deutlichen Verlangsamung des Datenbankservers. Der Maximalwert für key_buffer_size beträgt im übrigen 4 GiB (bei 32-Bit-Systemen).