semanticblog

Der MySQL KEY-BUFFER

Die wohl wichtigste Einstellung, welche in der my.cnf eines MySQL-Server vorgenommen werden sollte, ist die key_buffer_size.
Ein hoher key_buffer sorgt dafür, dass die Verarbeitung der Indizes Ihrer Tabellen direkt über den RAM-Speicher erfolgen kann,
was natürlich ein Geschwindigkeitsvorteil gegenüber dem Filesystem ist.

Der Wert dieser Variablen sollte so gewählt werden, dass der key_buffer zwischen 25% und 50% des gesamten Systemspeichers benutzt.
Die Hauptvoraussetzung hierfür ist, dass es sich um einen dedizierten Datenbank-Server handelt.
Falls neben der MySQL-Datenbank noch ein Apache-Server installiert ist, sollte der Wert eher kleiner gehalten werden.
Sollten die Indizes ihrer Tabellen viel kleiner sein als 25% – 50% des vefügbaren RAMs, können Sie den Wert ebenfalls entsprechend kleiner setzen.


Gesetzt werden kann der Key-Buffer übrigens im laufenden Betrieb mit folgendem Befehl (Anbei ein Beispiel für einen key_buffer der Größe 80MB):
mysql> SET GLOBAL key_buffer_size=80*1024*1024;

Berechnung der KEY-Buffer-Größe:

Um einen Überblick über die aktuelle Leistung ihre Systems zu erhalten, ist es ratsam sich anzeigen zu lassen,
wie oft auf das Filesystem zugegriffen werden muss, um die Schlüssel der Indizes einzulesen.


Der nachfolgende Begfehl zeigt Ihnen diese Zahl in Abständen von 10 Sekunden. Falls sie den Vorgang über einen längeren Zeitraum beobachten wollen,
setzen Sie beispielsweise -i auf 100.
$ mysqladmin extended-status -uadmin -p -r -i 10 | grep Key_reads
| Key_reads | 4516933
| Key_reads | 12
| Key_reads | 10
| Key_reads | 36
| Key_reads | 4
| Key_reads | 54
| Key_reads | 37
| Key_reads | 82

Sollten die ausgegeben Zahlen recht klein sein, stellt das noch kein Problem für die Performance Ihres MySQL-Servers da.
Ab etwa 75-100 Key_reads in der Sekunde ist es jedoch ratsam, den key_buffer (falls möglich) anzupassen.


Falls der key_buffer nicht richtig eingestellt ist, werden die Schlüssel Ihrer Indizes genauso behandelt,
wie die Datenblöcke Ihrer Tabellen, welche über das Filesystem eingelesen werden. Sollte das passieren, wird
die Benutzung von Indizes nahezu überflüssig. Die Indizes müssen aus dem RAM kommen!

Wenn Sie wissen wollen, wie viel Speicher letztendlich dem key_buffer-Caches zugewiesen werden soll,
könnte es helfen zu wissen, wie viel Platz Ihr MyISAM-Indizes sind tatsächlich auf der Festplatte verbrauchen.


Dies kann mit diesem Befehl ermittelt werden:
$ du -sch `find /var/lib/mysql/meinedatenbank/ -name “*.MYI”`

150,0M /var/lib/mysql/meinedatenbank/tabelle1.MYI
890,0M /var/lib/mysql/meinedatenbank/tabelle2.MYI
10,0M /var/lib/mysql/meinedatenbank/tabelle3.MYI
950,0M /var/lib/mysql/meinedatenbank/tabelle4.MYI
2,0G insgesamt

Für die weitergehenden Berechnungen zum key_buffer benötigen Sie einige Variablen, die in die Rechnungen mit einfließen:

mysql> SHOW VARIABLES LIKE ‘key_%’;
+————————–+————-+
| Variable_name | Value
+————————–+————-+
| key_buffer_size | 147372182
| key_cache_age_threshold | 200
| key_cache_block_size | 2048
| key_cache_division_limit | 100
+————————–+————-+
4 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE ‘Key_%’;
+————————+——————+
| Variable_name | Value
+————————+——————+
| Key_blocks_not_flushed | 0
| Key_blocks_unused | 9
| Key_blocks_used | 125235
| Key_read_requests | 19886434342
| Key_reads | 8451382
| Key_write_requests | 57643111
| Key_writes | 2473240
+————————+—————–+
7 rows in set (0.00 sec)

Die Formeln zur Berechnung des Cache hit ratio beziehungsweise des benutzen Buffers lauten:

Cache hit ratio
100 – ( (Key_reads * 100) / Key_read_requests )


Percentage of buffer in use
100 – ( (Key_blocks_unused * key_cache_block_size) * 100 / key_buffer_size )

Anbei eine Beispielrechnung:

Cache hit ratio:
100 – ((8451382 * 100) / 19886434342 ) =
100 – 0,042498227 = 99,957501773 %


Sollte dieser Wert unter 95% fallen, ist dies definitiv nicht gut. Am Besten sind Werte in der 99,99% – Region.

Percentage of buffer in use:
100 – ( (9 * 2048 ) * 100 / 147372182 ) =
100 – ( (18432 / 167772160 ) =
100 – 0,000109863 = 99,999890137 % !


Wenn der Buffer zu 99,99.. ausgenutzt wird, ist dies ein gutes Zeichen, andererseits könne man sich Gedanken machen diesen etwas zu erhöhen.
Es sind einige Test und etwas ausprobieren notwendig, bis die optimalen Einstellungen gefunden sind.