Open Source im professionellen Einsatz
Linux-Magazin 12/2015
© Maksim Toome, 123RF

© Maksim Toome, 123RF

So macht man einer MySQL-Datenbank Beine

Auf die Überholspur

Heute sind Webdienste selten, bei denen nicht irgendwo eine MySQL-Datenbank mitwirkt. Manchmal entspricht ihr Durchsatz oder ihre Antwortzeit nicht den Erwartungen. Dieser Artikel zeigt, wie man bei MySQL Performanceprobleme in den Griff bekommt.

1099

Oft sind Performanceprobleme zu lösen, von denen sich anfangs nur diffuse Vorstellungen und vage Beschreibungen finden. Deshalb gilt es als Erstes festzuhalten, worin genau die Schwierigkeiten bestehen. Dieser Schritt hilft bereits Ordnung in die Sache zu bringen. Dazu stellt sich der MySQL-Admin Fragen wie: Wann tritt das Problem auf? Was genau geschieht, bevor der Nutzer merkt, dass die Anwendung langsam wird? Was hat er vorher angeklickt?

Neben Fragen, die die Ursache eingrenzen und darauf abzielen, klarer zu beschreiben, was zu verbessern ist, vervollständigen die MySQL-Logfiles das Bild. Auf jeden Fall ist es immer hilfreich, dem Problem unmittelbar nach seinem Auftreten auf den Grund zu gehen. Es drei Wochen später noch zu verstehen und zu lösen erfordert wesentlich mehr Spürsinn. Zum Schluss heißt es messen, messen, messen. Im Idealfall gibt es eine Monitoring-Infrastruktur, die alle notwendigen Performancedaten bereits erfasst.

Das Ziel identifizieren

Wer das Performanceproblem beschrieben und dokumentiert hat, legt im Anschluss fest, was die Ziele der Tuningmaßnahme sein sollen: Eine kürzere Antwortzeit oder mehr Durchsatz? Ein Ziel könnte etwa lauten: "Die Aktion X, die nach dem Klick 3,5 Sekunden dauert, soll in Zukunft in weniger als einer Sekunde ausgeführt werden." Wenn es sich um ein Durchsatzproblem handelt, könnte ein Ziel lauten: "Bei 30 Prozent mehr Last im nächsten Jahr soll das System immer noch im Durchschnitt innerhalb von 350 Millisekunden auf eine Bestellung antworten."

Es ist unabdingbar, realistische Ziele zu wählen. Das heißt solche, die zumindest die natürlichen Grenzen respektieren, die nicht zu beeinflussen sind: Je nach Netzwerk gibt es eine Obergrenze für die Übertragungsgeschwindigkeit. Oder es gibt technische Limits (100 Millionen Zeilen zu durchsuchen dauert halt ein Dutzend Sekunden). Oder der Tuner stößt an finanzielle Grenzen. (Ja, eine SSD könnte 10000 IOPS liefern, aber die zahlt der Kunde nicht.) Performancetuning erfordert immer Kompromisse, seien es finanzielle oder solche bei der Sicherheit oder beim Aufwand für Korrekturmaßnahmen und Codechanges.

Den Engpass finden

Am Anfang gilt es, den Flaschenhals auf der technischen Seite zu finden. Das kann der Webbrowser, das Netzwerk, der Web- oder Applikationsserver, die Datenbank oder auch die Hardware sein. Auf Datenbankmaschinen sind typischerweise die Ressourcen I/O, RAM und CPU – in dieser Reihenfolge – die limitierenden Faktoren.

Welche dieser Ressourcen aktuell die Datenbankleistung begrenzt, lässt sich mit Tools wie »iostat« für das I/O-System, »free« und »vmstat« für den Memoryverbrauch und das Swapping sowie »top« oder »mpstat« für die CPU-Auslastung herausfinden. Den Speicherverbrauch klärt das Kommando »free« , wie in Listing 1 zu sehen ist.

Listing 1

free

# free -m
             total       used       free     shared    buffers     cached
Mem:        117080     113060       4020       2420       1980      62260
-/+ buffers/cache:      48810      68270
Swap:       119730          0     119730

Die wichtigsten Werte in Listing 1 bedeuten:

  • Mem total: Insgesamt zur Verfügung stehender Speicher (RAM), hier: 117080 MByte.
  • Mem used: Effektiv durch Applikationen genutzter Speicher (RAM), hier: 48810 MByte.
  • Mem free: Potenziell freier Speicher (RAM), hier: 68270 MByte.
  • Mem cached: Durch das Betriebssystem genutzter Speicher (RAM) für den Filesystemcache, hier: 62260 MByte.
  • Swap used: Hier ist zurzeit kein Swapspeicher in Gebrauch.

Ist der Speicher die limitierende Ressource, wird »Mem used« sich in Richtung »Mem total« bewegen und »Mem free« gegen null. Zudem baut das Betriebssystem den Filesystemcache (»Mem cached« ) ab (nähert sich null) und »Swap used« wird sich von null weg hin zu »Swap total« bewegen.

Der Speicherverbrauch des wichtigsten Prozesses (»mysqld« ) lässt sich so ermitteln, wie es Listing 2 demonstriert. Die Speicher-relevanten Werte bedeuten:

Listing 2

Speicherverbrauch von mysqld

01 # ps aux | egrep 'mysqld|VSZ'
02 USER    PID %CPU %MEM      VSZ     RSS START   TIME COMMAND
03 mysql  1568  0.0  0.0     4440     744 Sep08   0:00 mysqld_safe
04 mysql  2337  0.1  5.9 29163040 7115280 Sep08  13:00 mysqld
  • VSZ: Durch den Prozess vom Betriebssystem angeforderter Speicher (Virtual Size).
  • RSS: Durch das Betriebssystem im RAM gehaltener Speicherbereich des Prozesses (Resident Set Size). Dieser Speicher ist Teil des Mem-used-Bereichs aus dem »free« -Befehl. Die Summe der RSS-Werte aller Prozesse sollte in etwa »Mem used« entsprechen. Liegt die Summe der VSZ-Werte aller Prozesse deutlich über dem verfügbaren Speicher (»Mem total« ) ist der Speicher (RAM) overcommitted, was unter Volllast zu Swapping und einem Ausbremsen des Systems führen kann.

Ob und wie stark ein System swapt und wie stark dies das I/O-System belastet, das zeigt Listing 3.

Listing 3

vmstat

01 # vmstat 1
02 procs -------------memory---------- ----swap---- -----io----- -system-- ------cpu-----
03  r  b    swpd    free   buff  cache    si     so    bi     bo   in   cs us sy id wa st
04  0  0   10848  139772   3612 793656     0      0     0      0  788 2583  7  5 88  0  0
05  0  0   10924  152000   3584 689528     0     76     4    124  802 2759  7  6 88  0  0 <- <i class="replaceable">Beginn des swappens
06  0  1  103260  117124   3528 636156     0  92336     0  92556  834 2607  7  5 83  5  0
07  2  1  126168  138116   3552 553200     0  22908   348  23076 1143 2880  6  3 57 35  0 <- <i class="replaceable">I/O System belastet (wa)
08  0  3  265376  149720   1136 378148     0 105300   252 105348  993 3002  6  4 62 29  0
09  0 14  495028  117264    560 329792     0 137352  1252 137488  880 3506  5  2 22 71  0 <- <i class="replaceable">heftiges Swappen
10  0 10  597776  117244    320 317840     0  29400   588  29400  664 2522  2  2  0 96  0 <- <i class="replaceable">Prozess gekillt
11  0 13 1667548 8263784    784 304768   296      0   920      0  298  675  0  0 25 75  0 <- <i class="replaceable">Ausgelagerter  Speicher zurück
12  1  7 1659568 8247460   1200 314888  6900      0 16056   1132  905 1995  6  1 24 70  0
13  1  2 1654428 8148296   2516 373604 13004      0 49036   1100 2444 7135  8  6 68 19  0
14  1  0 1649624 8128492   3088 396844   328      0  2604     28  497 1634  4  1 94  1  0

Die Bestimmung der Auslastung des I/O-Systems ist etwas komplizierter: Es gilt, vier verschiedene I/O-Muster zu erkennen: sequenzielles Lesen und Schreiben sowie Lesen und Schreiben an zufälligen Positionen. Letzteres kommt bei Datenbanken häufig vor. Ersteres können normale, drehende Platten (HDD) besonders gut, Letzteres können SSDs deutlich besser.

Die Auslastung des I/O-Systems lässt sich so abfragen, wie es Listing 4 für sequenzielles Schreiben auf SSD (single threaded, 16-KByte-Blöcke, O_DIRECT) zeigt. Dort stehen »r/s« und »w/s« für die Anzahl der Lese- beziehungsweise Schreiboperationen pro Sekunde (zusammen: IOPS), »await« für die durchschnittliche Servicezeit aller I/O-Requests, »rKB/s« und »wKB/s« für die Menge gelesener beziehungsweise geschriebener Daten (Durchsatz).

Listing 4

Auslastung des I/O-Systems

01 # iostat -xk 1
02
03 Device: rrqm/s  wrqm/s   r/s     w/s rkB/s     wkB/s avgrq-sz avgqu-sz  await r_await w_await  svctm  %util
04 sda       2.00    8.00  2.00 9500.00 16.00 151948.00    31.99     1.07   0.11    4.00    0.11   0.09  88.40

Ist der Lese- oder Schreibdurchsatz hoch (wie in Listing 4), handelt es sich um sequenzielle Operationen. Zufällige Lese- oder Schreiboperationen ergeben einen deutlich geringeren Durchsatz.

Fehlt noch die CPU-Auslastung. Da muss man etwas genauer hinschauen. Im Beispiel von Listing 5 scheint sich das System zu 71 Prozent im Leerlauf zu befinden, aber Core 1 (ein Thread zum Beispiel einer MySQL-Verbindung) wird über längere Zeit voll ausgelastet.

Listing 5

mpstat

01 # mpstat -P ALL 1
02
03 CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest  %gnice   %idle
04 all   26.94    0.00    0.59    1.05    0.00    0.02    0.00    0.00    0.00   71.40   <- <i class="replaceable">71% idle
05   0    1.00    0.00    0.37    0.00    0.00    0.06    0.00    0.00    0.00   98.57
06   1  100.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00
07   2    3.77    0.00    1.32    1.32    0.00    0.00    0.00    0.00    0.00   93.58
08   3    2.82    0.00    0.69    2.89    0.00    0.00    0.00    0.00    0.00   93.60

Ist der limitierende Faktor oder der schuldige Prozess identifiziert, kann der Admin sich überlegen, wie dieses Performanceproblem in den Griff zu kriegen ist. Bei MySQL lassen sich fünf Ebenen identifizieren, auf denen er die Schwierigkeiten angehen und allenfalls auch beseitigen kann.

Diesen Artikel als PDF kaufen

Express-Kauf als PDF

Umfang: 7 Heftseiten

Preis € 0,99
(inkl. 19% MwSt.)

Linux-Magazin kaufen

Einzelne Ausgabe
 
Abonnements
 
TABLET & SMARTPHONE APPS
Bald erhältlich
Get it on Google Play

Deutschland

Ähnliche Artikel

  • Abfragen-Beschleuniger

    MySQL ist ein schnelles Datenbanksystem. Wenn die Datenbankanwendung dennoch in der Praxis manchmal lahmt, sind die Ursachen meist suboptimales Datenbankdesign, ineffiziente Abfragen oder der Verzicht auf Indizierung. Diese Fehler sind aber vermeidbar.

  • Group Replication

    Jüngst hat Oracle mit der Group Replication eine Hochverfügbarkeitslösung für das allgegenwärtige MySQL vorgestellt, die mit wenig Aufwand einsetzbar ist.

  • Gesundheitscheck

    In Datenbankanwendungen gestalten sich Überwachung und Fehlersuche oft nicht gerade trivial. Lässt sich der globale Status noch leicht ermitteln, erfordert die Ursachenforschung im Problemfall Einblicke ins Innenleben. MySQL Enterprise will hier mit einem eigenen Monitoring-Tool helfen.

  • Vertraut und doch neu

    Seit Version 5.2 ist Maria DB unter den MySQL-Forks eine attraktive Alternative. Nicht nur, dass MySQL-Entwickler Monty Widenius hier persönlich Hand anlegt - die Kombination von Patches und neuen Funktionen bei größtmöglicher Kompatibilität hebt Maria DB unter den Konkurrenten hervor.

  • Maria-DB-Replikation

    Sind die Datenbankinhalte so wichtig, dass sie auch zwischen periodischen Datensicherungen nicht verloren gehen dürfen, dann ist Replikation ein Ausweg. Dieser Artikel beschreibt, wie sich die Doppelung mit Maria DB unter Zuhilfenahme von Xtra Backup bequem einrichten lässt.

comments powered by Disqus

Stellenmarkt

Artikelserien und interessante Workshops aus dem Magazin können Sie hier als Bundle erwerben.