MySQL MariaDB Feintuning von innodb_buffer_pool_size

Installiert ist MariaDB in Version 10.0.28. Bei einer Kontrolle fiel auf, dass der MySQL Dienst relativ stark auf die Festplatte schreibt und “gefühlt” höhere CPU Last als andere Systeme erzeugt.

Die Analyse mittels MySQLTuner Skript zeigte unter anderen dies:

innodb_buffer_pool_size (>= 1G) if possible.

Nachfolgend geht es nur um diese Variable, was sie bedeutet, wie der aktuelle Wert ist und wie er verändert werden kann.

INNODB_BUFFER_POOL_SIZE

Der Wert bestimmt die Anzahl an Daten / Tabellen, einer InnoDB Datenbank, die maximal in den Arbeitsspeicher geschrieben werden können.

INNODB_BUFFER_POOL_SIZE Werte anzeigen

Um den aktuellen Wert der Variable zu erhalten, ist folgendes nach der Anmeldung an MySQL auszuführen:

# MariaDB [(none)]> show variables like ‘innodb_buffer%’
-> ;
+————————————-+—————-+
| Variable_name | Value |
+————————————-+—————-+
| innodb_buffer_pool_size | 134217728 |
+————————————-+—————-+
10 rows in set (0.00 sec)

Hier ist gut zu sehen, dass die Datenbank mit dem Defaultwert von 134217728 = 128MB eingestellt ist.

Wieviel die Datenbank aktuell tatsächlich davon braucht ist mit folgenden Befehl zu sehen:

MariaDB [(none)]> SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
-> (SELECT variable_value PagesData
-> FROM information_schema.global_status
-> WHERE variable_name=’Innodb_buffer_pool_pages_data’) A,
-> (SELECT variable_value PageSize
-> FROM information_schema.global_status
-> WHERE variable_name=’Innodb_page_size’) B;
+————–+
| DataGB |
+————–+
| 0.1162109375 |
+————–+
1 row in set (0.00 sec)

Hier gut zu sehen, dass von den eingestellten 128MB aktuell ca. 116MB belegt werden.

INNODB_BUFFER_POOL_SIZE Richtwerte

Laut MySQL Handbuch 5.6 gelten folgende Werte:

  • 32-bit Platforms:
    • Min Value = 5242880
    • Max Value = 2*32-1
  • 64-bit Platforms:
    • Min Value = 5242880
    • Max Value = 2*64-1

Bei der Vergabe des Wertes ist immer auf das System und dessen Ressourcen zu achten. Wenn der Wert an Arbeitsspeicher zu hoch ist, können andere Dienste fehlschlagen oder beendet werden. Ein zu hoher Wert ist auch an einer stärkeren Beanspruchung der Festplatte(n) feststellbar.

INNODB_BUFFER_POOL_SIZE setzen

Eine Möglichkeit ist die Empfehlung der RIBPS (Recommended InnoDB Buffer Pool Size) der Datenbankengine als Richtwert zu nehmen, sofern die Systemressourcen dies erlauben! Dafür mit Root-Rechten an MySQL / MariaDB anmelden und dann folgendes für eine Empfehlung basierend auf allen InnoDB Daten und Indexes plus zusätzlicher 60 Prozent Reserven ausführen:

MariaDB [(none)]> SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine=’InnoDB’) A;

+——-+
| RIBPS |
+——-+
| 3 |
+——-+
1 row in set (0.92 sec)

Das Ergebnis zeigt, dass der RIBPS Wert plus die 60% Reserven bei 3GB liegen sollte. Der neue Wert für innodb_buffer_pool_size wird in der my.cnf gesetzt und sieht für die empfohlenen 3GB wie folgt aus:

innodb_buffer_pool_size=3G

Danach muss der MySQL Dienst neugestartet werden: # service mysql restart oder # /etc/init.d/mysql restart

Nach ca. 1 Woche kann man nun erneut kontrollieren, wie sich die Vergrößerung des Wertes von 128MB auf 3G des innodb_buffer_pool_size, auswirkt.

INNODB_BUFFER_POOL_SIZE 80% Regel

Es gibt bei dedizierten Servern, laut MySQL Handbuch, eine grobe 80% Regel. Diese besagt, dass der Wert für die innodb_buffer_pool_size 80 Prozent des physischen Speichers betragen soll. Nachfolgend eine Tabelle die zeigt was diese Regel bedeutet:

Total Server RAM Buffer pool MIT 80% Regel Remaining RAM
1G 800MB 200MB
16G 13G 3G
32G 26G 6G
64G 51G 13G
128G 102G 26G
256G 205G 51G
512G 409G 103G
1024G 819G 205G

Wow 51GB RAM nur für den MySQL Server. Das mag in dem ein oder anderen Fall völlig überzogen sein, oder gar zu wenig?! Daher sollte IMO die 80 Prozent Regel skeptisch und von Fall zu Fall betrachtet werden.

Irgendwo im Netz lass ich dazu mal, dass damals als MySQL noch zu Yahoo gehörte, der Techniker Heikki Tuuri, der Autor von InnoDB, und Peter Zaitsev zu der Regel befragt wurden. Heikki Tuuri soll geantwortet haben, dass er auf einem Server mit 1G RAM 80 Prozent für MySQL reserviert habe und das für ok befand. Er merkte aber auch, dass diese Regel nicht unbedingt auf größere Server übertragbar sei…

http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.