Menü Schließen

MySQL MariaDB Feintuning von innodb_buffer_pool_size

MariaDB Logo

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 RAMBuffer pool MIT 80% RegelRemaining RAM
1G800MB200MB
16G13G3G
32G26G6G
64G51G13G
128G102G26G
256G205G51G
512G409G103G
1024G819G205G

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

3 Kommentare

  1. Murat

    Hallo Zusammen,
    innodb_buffer_pool_size= kann auch wie folgt über MySQL selbst eingetragen werden.
    mysql -u root -p
    Password eingeben und als MySQL befehl „SET GLOBAL innodb_buffer_pool_size=2147483648;“ eingeben (bsp. für 2GB)
    „exit;“ dann MySQL mit „systemctl restart mysql“ neustarten.
    VG

  2. Stephan Frank

    Hallo,
    vielen Dank für den erklärenden Bericht. Aber leider greift bei mir diese Einstellung in der my.cnf nicht. Egal welchen Wert ich dort angebe, die datenbank startet immer mit dem default Wert von 128M… Und beendet sich regelmäßig, weil dieser Wert zu klein ist.
    Gibt es da für CentOS 7 Ausnahmen beim Eintrqagen dieses Wertes?
    Ich hab das ganze auch mal auf einem opensuse 15.0 Server ausprobiert, da funktioniert dieser Eintrag ohne Probleme…

    Vielleicht hast du ja einen Tipp.

    Viele Grüße
    DER ruebenmaster

    • JARVIS

      Hallo Frank,
      gut das der Artikel helfen konnte. CentOS habe ich leider nicht im Zugriff, kann es aktuell nicht nachvollziehen. Du kannst aber mal schauen wo die my.cnf liegt: „# /usr/libexec/mysqld –help –verbose“

Schreibe einen Kommentar

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