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.

MySQLTuner Skript

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)

Der angezeigte Wert 0.1162109375 zeigt,dass von den obigen 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