Haben wir den InnoDB-Buffer richtig gewählt?

Beim letzten Mal haben wir uns ja angesehen, wie wir eine optimale Größe für den innodb_buffer_pool_size berechnen.

Wir können auch noch kontrollieren, ob wir mit unserem prozentualen Aufschlag ein gutes Gefühl hatten.

Prüfe, was du tust

Das Prüfen ist nicht ganz sooooo banal wie das Ermitteln der potentiell optimalen Größe des Bufferpools.

Vorab schon mal das notwendige SQL:

SELECT (PagesData*PageSize)/POWER(1024,2) BufferGenutztMB 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;

Wir fragen damit ab, wie viele Bufferpool-Seiten wir nutzen – das müssen wir noch in lesbare Werte umrechnen, indem wir die Anzahl der Bufferseiten mit der Seitengröße (in Byte) multiplizieren. Und wir wollen den Schotter in Megabyte haben, da wir in der my.cnf auch mit Megabyte arbeiten (… nicht müssen, aber wir tun es).

Heraus kommt dabei beispielsweise dies hier:

+-----------------+
| BufferGenutztMB |
+-----------------+
|      234.828125 |
+-----------------+
1 row in set (0.00 sec)

Das heißt, es werden 234MB Buffer genutzt. In der my.cnf hatte ich in meinem Fall zuvor 200MB festgelegt.

Abweichung Baby

Tja, irgendwie wird mehr Buffer genutzt, als wir tatsächlich vorgesehen haben.

Leider habe ich darauf keine Antwort gefunden – bestimmt hat das was mit Rundungen oder der Umsetzung auf Seitenbasis zu tun.

Meine PageSize ist 16.384 Byte:

MariaDB [(none)]> SELECT variable_value PageSize
    -> FROM information_schema.global_status
    -> WHERE variable_name='Innodb_page_size';
+----------+
| PageSize |
+----------+
| 16384    |
+----------+
1 row in set (0.00 sec)

Meine Anzahl Pages für den Bufferpool liegt bei 15.034:

MariaDB [(none)]> SELECT variable_value PagesData
    -> FROM information_schema.global_status
    -> WHERE variable_name='Innodb_buffer_pool_pages_data';
+-----------+
| PagesData |
+-----------+
| 15034     |
+-----------+
1 row in set (0.00 sec)

Habt ihr eine Antwort?

Was jemand von euch darauf schon eine Antwort gefunden hat, ich wäre dankbar für eine Mitteilung… oder mache ich einen Denkfehler irgendwo? Wenn ja, auch hier wäre ich für Aufklärung offen 🙂

Optimale Buffersize für InnoDBs

Wenn wir bei einer MySQL-Datenbank die moderne InnoDB-Engine für die Tabellen nutzen, müssen wir uns mit dem Gedanken warmmachen, dass es statt einem Dutzend verschiedener Buffer-Parameter im Prinzip nur noch einen einzigen gibt.

innodb_buffer_pool_size

Dieser Parameter ist der innodb_buffer_pool_size.

Im Prinzip speichert das Datenbanksystem in diesem Buffer die Indizes und Datenseiten der verwendeten Tabellen. Ausnahmen bilden wir immer Datenfelder, die nicht in der Datenseite selbst gespeichert werden – also beispielsweise BLOBs oder andere Konstrukte, die lediglich per ID oder Teildaten in der Datenseite aufgenommen werden.

Da im Zweifel der Speicherzugriff immer viel schneller als der Zugriff auf einer Festplatte ist, sollten wir natürlich dafür sorgen, dass dieser Buffer möglichst eine optimale Größe hat.

Optimale Größe für den Buffer

Was ist denn nun die optimale Größe?

Denken wir luxuriös: Wir wollen einfach alles im Buffer ablegen, was irgendwie sinnvollerweise in den Buffer gepackt werden kann. Also sämtliche Daten- und Index-Informationen.

Wir bekommen wir denn jetzt heraus, wie groß dieses „Alles“ ist – was ja sicherlich als Näherungsgröße für unsere Buffergröße sinnvoll ist?

Das ist ganz einfach – alle Informationen über sämtliche Tabellen hält MySQL im „information_schema“ bereit.

Da müssen wir nur mal nachsehen 🙂

Information_schema befragen

Diese Abfrage ist eigentlich banal:

SELECT CEILING(InnoDB_Alles/POWER(1024,2)) BOAH_ALLES FROM
(SELECT SUM(data_length+index_length) InnoDB_Alles
FROM information_schema.tables WHERE engine='InnoDB') A

Die Antwort kann dann – bitte ein bisschen Geduld aufbringen, die Abfrage dauert – beispielsweise so lauten:

+------------+
| BOAH_ALLES |
+------------+
|        133 |
+------------+
1 row in set (0.58 sec)

Tada – wir wissen jetzt, dass wir 133MB Daten- und Indexinformationen in unserer Datenbank haben.

Fertig… oder?

Nun. Jetzt haben wir erst mal die Info, was wir an Nettodaten so in unseren Tabellen liegen haben, die per InnoDB-Engine verwaltet werden.

Aber das reicht natürlich noch nicht – wir machen mit den Daten ja auch was, also stellen Abfragen beliebiger Komplexität.

Update: Es ist auch nicht zu vergessen, dass nicht alle Tabellen mit der InnoDB-Engine betrieben werden können. Sämtliche Systemtabellen, dazu gehören auch die Information-Schemas, laufen mit myISAM oder anderen Engines. Die brauchen ebenfalls Buffer – die aber werden zwar vom Hauptspeicher abgezogen, werden aber über die übliche Verdächtigen wie key_buffer_size und so geregelt.

Und für diese Abfragen braucht die Datenbank auch immer mal ein bisschen Platz – für Zwischentabellen oder sonstige Infos.

Überschlägig und je nach Anwendungsfall dürfen wir davon ausgehen, dass 60 bis 80% der Nettodaten noch mal an Bufferbedarf oben drauf kommen – wir können auch 200% Aufschlag nehmen, wenn wir genug Speicher haben. Aber wir sollten ein bisschen vernünftig sein, vor allem dann, wenn auf demselben Rechner noch anderes Zeug läuft – was ja ebenfalls Ressourcen braucht, nech.

Bufferaufschlag

Ich für meinen Fall nehme einen Aufschlag von 60% an. Jetzt können wir natürlich per Taschenrechner die 133MB einfach mit 1,6 multiplizieren und bekommen unseren „endgültigen“ Buffer.

Aber bekanntlich bin ich faul und überlasse alles dem Rechner, was geht:

SELECT CEILING(InnoDB_Alles*1.6/POWER(1024,2)) BOAH_ALLES FROM
(SELECT SUM(data_length+index_length) InnoDB_Alles
FROM information_schema.tables WHERE engine='InnoDB') A

Ergebnis:

+------------+
| BOAH_ALLES |
+------------+
|        213 |
+------------+
1 row in set (0.58 sec)

Tada – also ändern wir doch ein wenig die my.cnf und stellen einen Buffer ein 🙂