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).

Florian hat 2 Beiträge geschrieben

18 Kommentare zu “MySQL Systemvariablen – key_buffer_size

  1. Florian sagt:

    Gibt ja auf diesen Artikel wenig Feedback. 😉

    Sollen wir überhaupt die Reihe mit den MySQL-Systemvariablen durchführen oder besteht gar kein Interesse daran?

  2. fastmysql sagt:

    Gerne, bitte mehr davon!

    Ich glaube das Problem mit MySQL Einstellungen (my.cnf) hat jeder, der ein Root-Server hat. Und es gibt immer noch keine Patent Lösung? Dabei haben viele die Lösung gefunden oder zumindest den Lösungsweg. Es muss doch eine Formel geben.

    Eine Idee: „my.cnf Generator“.
    Ich bin kein Server Experte aber so ein Tool würde Jeden nützen.

    Als Beispiel gibt man einem Formular folgende Daten ein :
    – MySQL Version
    – Server Betriebsystem
    – Arbeitsspeicher Größe
    – Wird der Server auch als Webserver genutzt oder nur für MySQL
    – …usw.

    Und schon wird eine (my.cnf) generiert. Für so einen Tool würden alle Entwickler Bezahlen/Spenden.

    Leider habe ich nicht genug Erfahrung, sonst würde ich es selber machen 🙂

    Ich wäre dem Entwickler Dankbar!

  3. Ja interessant wäre das auf jeden Fall, auch wenn ich meist auf Servern arbeite wo ich nicht unbedingt alle rechte habe, aber immerhin könnte ich hierdrauf verweisen wenn mal etwas wirrklich zu langsam läuft 😉

  4. GhostGambler sagt:

    Ich für meinen Teil hatte den Beitrag noch gar nicht gelesen… Konfiguration ist bei MySQL harte Kost, kein Wunder, dass das immer unter geht 😉

    Man muss bei dem Buffer auch mit rein rechnen, dass MySQL ein paar MB zur internen Verwaltung des Buffers benötigt. Sprich: Man hat 20 MB Indizes, aber buffer_size sollte man auf 25 MB oder so setzen, weil ansonsten doch nicht alles rein passt.
    Wiederum nicht vergessen sollte man bei dem Ganzen „das muss alles rein passen“, dass aber auch noch andere – wichtige(re) – Dinge im Ram Platz finden müssen. Heißt, bevor man auf Biegen und Brechen versucht alles an Indizes in den Ram zu pressen, sollte man lieber auf ein read-keyhit von >95% hinarbeiten. Das ist ausreichend und lastet den Ram weniger stark aus.
    (read-keyhit= http://hackmysql.com/mysqlreportguide#key_report:read_ratio)

    Generell ist dieses Tool echt klasse: http://hackmysql.com/mysqlreport
    Sollte jeder kennen, der einen MySQL-Server optimieren will.

    Und wichtig ist auch noch, dass buffer_size *nur* für MyISAM relevant ist!
    InnoDB benutzt den Buffer entweder gar nicht oder nur wenig (irgendeinen Buffer hat InnoDB mitbenutzt, ich weiß aber aus dem Kopf gerade nicht welcher).
    Aber für praktisch alles hat InnoDB viele eigene Einstellungsmöglichkeiten.

  5. Graceland sagt:

    Ich finde das sogar sehr interessant.

    Besonders, weil ich gerne an der Konfiguration meines Servers rumschraube und teilweise garnicht weiß, was ich da bearbeite.
    Die Dokumentation ist ja wie gesagt teilweise sehr dürftig. 😉

    Über Apache Variablen gibt es zwar schon einiges mehr, aber auch da würde es mich sehr interessieren, was nachweislich den meisten Nutzen bringt.
    Da sagt irgendwie jede Seite etwas anderes.

  6. Thomas sagt:

    @Florian
    Was erwartest du mitten im „Sommer“? 😉
    Danke, ich warte schon auf den nächsten Artikel.

    @all
    Unter Debian (default config) sind die Variablen mit etwas andere Namen versehen, somit kann es sein, dass z.B. „key_buffer_size“ nicht gefunden wird. In der Datei „my.cnf“ steht lediglich „key_buffer“, dabei scheint es sich um einen Alias zu handeln.

    Das Standardtool phpMyAdmin hilft ebenfalls beim optimieren der Performance. Wenn man sich unter „Status“ umschaut, kann man rot/grün gehighlightete Zahlen entdecken.
    Gibt es dazu Erfahrungswerte?

  7. Florian sagt:

    Okay, dann werde ich wohl demnächst mal wieder etwas schreiben müssen. 😉
    Mache das hier nur nebenbei, betreibe eigentlich eine andere, eigene Seite. Aber zwischendurch ein bisschen seine gesammelten PHP- und MySQL-Kenntnisse niederzuschreiben, wenn sie denn sogar noch hilfreich für andere sind, macht auch Spaß.

    PS: Jup, phpMyAdmin stellt das gut dar mit den grünen und roten Werten. An denen kann man sich auch grob orientieren.

  8. Jan sagt:

    Zum Thema phpMyAdmin nochmal: Auf welchem Zeitraum basieren die Zahlen eigentlich? Sind das wirklich die seit dem Starten der MySQL-DB? Denn dann wäre die Aussagekraft der einzelnen Werte und auch der Farben recht gering, denn es ist ja ein Unterschied, ob man bspw. 100 Slow Queries über die gesamte Uptime hatte oder 100 an einem Tag.
    Also weiß da jemand, ob das tatsächlich einfach die Gesamtanzahl ist (insbesondere bei den Statusvariablen, die eine Anzahl kennzeichnen)?

  9. Florian sagt:

    Das sind die Zahlen, die sich seit dem letzten MySQL-Neustart ergeben haben. Ganz oben steht auch, wie lange der MySQL-Server schon in Betrieb ist.

    PS: Slow Queries sollte es nie geben, egal, wie lange der Server läuft. 😉

  10. GhostGambler sagt:

    Die Slow Queries-Grenze ist auch nur eine Einstellungssache.
    Abgesehen davon ist nicht jeder Query, nur weil er lange läuft, gleich schlecht. Ganz so simpel wie es von einigen „Optimierungsgurus“ angepriesen wird ist die Konfiguration vom Server und Verbesserung von Queries dann leider doch nicht. ^^,

  11. Marius sagt:

    Zu der Frage bzgl. der Reihe. Ja, bitte fortführen. Finde es super, endlich mal eine Quelle gefunden zu haben, die verständlich an die Problematik herangeht. Ich betreibe hobbymäßig ein Browserspiel und suche schon seit Langem nach einer möglichst optimalen Herangehensweise an die MySQL Einstellungen. Was ich bisher auf dieser Seite gefunden habe, war sehr informativ und dabei habe ich erst eine handvoll Beiträge gelesen.

Eine Antwort schreiben

Ihre E-Mail-Adresse wird nicht veröffentlicht. Benötigte Felder sind markiert mit *

You may use these HTML tags and attributes: <a href=""> <blockquote cite=""> <pre lang=""> <b> <strong> <i> <em>