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 🙂

 

Optimierungsorgie Teil 2

Wie vorhin schon mal erwähnt, habe ich in den letzten zwei Wochen einige Optimierungsarbeiten an unserer MySQL-Datenbank vorgenommen.

Unsinn hoch Drei – Probierphase

Interessanterweise waren viele der Maßnahmen völlig für den Eimer – das habe ich aber erst seeeeehr spät festgestellt.

Beispiele

  • mysqltuner.pl immer wieder laufen lassen, der mir stoisch eine Erhöhung der tmp_table_size (und damit auch max_heap_table_size) empfiehlt.
  • Empfehlung, den innodb_buffer_pool_size auf 80% des installierten Arbeitsspeichers in den Wind geschlagen (20% für PHP? Nie im Leben) und gegen 50 und 60% getauscht.
  • query_cache an – und wieder aus – und wieder an – und wieder aus – man ist sich da im Expertennetz nicht einig: Einige verteufeln, andere bringen wieder den Standardspruch „your milage may vary“.
  • diverse Buffer hoch und wieder runter – meistens hoch

Irgendwann war dann die Datenbank so aufgeblasen, dass nur noch 2 oder 3 PHP-Instanzen ausführbar gewesen wären, wenn meine NAS mich das denn hätte so stark begrenzen lassen. Da ich für php mal sportlich

memory_limit = 300M

vorgesehen hatte (man weiß ja, WordPress mag es gerne ausufernd – falsch… aber das wusste ich da noch nicht) und in der Tat sich die PHP-Prozesse gerne mal so um die 200M reingetan haben, war irgendwann auch für den Mathe-Versager Mirko klar: Das passt alles nicht rein.

Hier liefen also ein halbes Dutzend PHP-Prozesse, eine Datenbank, mit der ich vermutlich die Deutsche Bank hätte versorgen können, ein OpCode-Cache, in den sämtlich 15.000 PHP Scripte reingepasst haben und noch diverse andere Dinge, die zusammen vermutlich so 12 bis 16GB Arbeitsspeicher hätten haben wollen – und ein paar lustige CPU-Kerne gleich noch oben drauf.

Katastrophenalarm – Wir werden geDDOSed.

Eines Morgens so gegen 11 dann der wahrgewordene Alptraum: Unser Webserver wird angegriffen!!!1eins

Wir verzeichnen dutzende Anfragen gleichzeitig, alle landen in getrennten PHP-Prozessen (soso, plötzlich können da auch 20 Prozesse gespawned werden, obwohl die Begrenzung viel niedriger lag). Die Datenbank braucht für Ömmelsabfragen plötzlich 70 Sekunden und mehr (vorher: Bruchteile einer Sekunde!).

Also ich per SSH in den Server (wie praktisch, dass ich auf dem Server ein renice-Script laufen habe, dass mir alle paar Minuten die SSH- und anderen kritischen Prozesse auf bequemere Prioritäten schiebt) und mit sehr viel Geduld im Minutenbereich die Datenbank freundlich um Beendigung gebeten. Daraufhin dann auch gleich den Webserver (der sowieso ohne Datenbank lustige Ausfälle zeigt bis hin zum Verzweiflungsselbstmord).

Puh. Intrusion Prevention auf die NAS gebügelt, irgendwer muss mal den Türsteher machen (Firewall wäre naheliegend, aber mit dem Käse läuft dann grad mal gar nix mehr so richtig – auch eine Möglichkeit Ruhe zu haben…). Die wiederum lutscht sich auch knapp 600 bis 800MB Arbeitsspeicher, wovon wir bekanntlich eigentlich schon im Zehnerpotenzbereich zu wenig haben.

Dann war Ruhe.

Nächster Tag – geht schon wieder los!

Am nächsten Morgen, wieder so gegen 11 – der nächste Angriff.

Da ich zwei Tage vorher eine Hoax-Mail mit der Bitte um Überweisung eines frechen Betrags erhalten habe mit der Ankündigung mir im Gegenzug das Geschäft dann nicht zu zerstören, habe ich langsam bisschen Muffensausen bekommen. Ich dachte „ok, das war kein Hoax, die A-Geigen meinen das so“.

Also wieder gleiches Programm. Mit Geduld und Spucke in die NAS und alles totgelegt. Auch gleich mal einen Neustart der NAS gemacht (nach wochenlanger Uptime… ich bekam feuchte Augen, echt) und die letzten Sicherheitsupdates installiert.

Aber dann hatte ich einen Idee… zwei Tage hintereinander, zwei mal praktisch dieselbe Uhrzeit… das klingt doch verdächtig nach „Cron“.

cron um 11
cron um 11

Und was sehe ich da? Da läuft ein Backupscript von mir höchstpersönlich.

Was macht das… isset Schuld?

ya-backup
ya-backup

Naja. Also…. wenn das jetzt den Server derartig überlastet, dann weiß ich auch nicht.

Dann fiel mir ein, was ich die Tage davor gemacht habe.

Optimierungsorgie

In den letzten Tage habe ich für unsere Webseite (nicht die hier, hier ist nix optimiert, hier läuft nen nacktes WordPress mit einem Simple-Theme und nem einfachen WP Suppencache) Optimierungen am MySQL-Server vorgenommen.

Na – eigentlich ist es gar kein MySQL-Server. Sondern ein Percona MariaDB 5.5.50. Und nach nächtelanger Recherche in den Unweiten des Internets habe ich mich gefragt, ob unsere Datenbankzugriffe eigentlich schnell genug sind.

Ehrgeiz, wieso auch nicht

Wir hatten Anfragezeiten, die ich natürlich mit diversen Tools untersucht habe, die so insgesamt bei 1 bis 2 Sekunden für das Zusammensuchen sämtlicher Infos für eine Seite betrugen. Bei einem selbstgelöteten Redis-Cache, der die Benutzer der Seite beliefert (mit Auslieferungszeiten unterhalb der Wahrnehmungsgrenze) und diversen weiteren Optimierungen, die für Zugriffszeiten sorgen, die bei weniger als 15% aller Webseiten erreicht werden (so GTMetrix und andere) und das alles über eine kleine NAS, die bei uns im Keller steht, kann man ja nicht zufrieden sein, wenn die Backend-Benutzer mit so einer „lahmen Krücke“ auskommen müssen.

Tableengine tauschen

Also. Ran ans Werk. Fangen wir erst mal damit an, dass wir die Engine von MyISAM auf InnoDB umstellen. Ein simples

alter table engine=InnoDB;

reicht dafür schon.

Panik durch „Expertennetz“

Im Vorfeld habe ich mich fast eingenässt, weil ich so viel Unsinn darüber im Netz der Netze gefunden habe. Von „wird extrem lahm, wenn man das umstellt“ bis „vorher unbedingt alle Daten sichern, am Besten in einen atombombensicheren Kellergewölbe bei Neumond“ alles dabei.

Und was soll ich sagen. Bei einer winzigkleinen Testtabelle war das kein Thema, alles wie vorher.

Faule Informatiker

Aber ich hatte jetzt echt keinen Bock das für sämtliche Tabellen unserer Datenbank per Hand zu machen – Informatiker sind faul, die 2000€-Kisten auf unserem Schreibtisch sollen mal in die Puschen kommen… also Stackoverflow angeworfen und bei den richtigen Profis „Inspiration“ geholt (wie gesagt, wir sind faul – wieso das Rad neu erfinden *g*).

Da plumpst der Dicke über folgenden Auszug:

alter table in PHP
alter table in PHP

Ja ne is kla – ich baue mir doch kein PHP-Script dafür… mit SQL muss das doch auch gehen, immerhin kann man damit ja alles machen (für geeignete Werte von „alles“ und für geeignete & gedachte Werte von „kann“):

alter table SQL
alter table SQL

Viel besser.

Das was dabei raus kommt, dann doch direkt in die Datenbank löten:

mysql alter in ssh terminal
mysql alter in ssh terminal