MySQL Monitoring und Tuning

Ich beobachte auf einem Server mit einer MySQL Datenbank (SLES9, MySQL 4.1.x) eine relativ hohe Last durch den mysqld, vermutlich laufen dort ein paar nicht optimierte Querys bzw. Tabellen regelmäßig gegen die Wand bzw. zwingen die Datenbank zum Auslagern auf die Festplatte.
Bis dato lief der MySQL Server ohne individuelle Konfiguration ohne Auffälligkeiten und verrichtete seinen Dienst, mittlerweile habe ich angefangen an ein paar Werten zu schrauben um die Last und vor Allem das Auslagern auf die Festplatten zu minimieren. Interessante Werte sind hierbei in meinem Fall key_buffer_size, table_cache und tmp_table_size.

Bei meinen Tests haben mir die folgenden Artikel als gute Informationsquelle gedient:

• Optimizing MySQL: Hardware and the Mysqld Variables
• Optimizing MySQL: Optimizing the mysqld variables

Zum Beobachten der laufenden Querys habe ich zunächst das freie Tool mtop genutzt. Hierbei handelt es sich um ein Monitoring-Tool, dass dem Programm top ähnelt. Die SF.net Seite beschreibt das Tool wie folgt:

mtop (MySQL top) monitors a MySQL server showing the queries which are taking the most amount of time to complete. Features include ‘zooming’ in on a process to show the complete query, ‘explaining’ the query optimizer information for a query and ‘killing’ queries. In addition, server performance statistics, configuration information, and tuning tips are provided.

Mtop ist mehr zum On-Demand Monitoring geeignet, es verfügt zwar über eine farbliche Highlight-Funktion von long-queries, geloggt werden diese allerdings nicht.

Nach einigem Suchen habe ich das Perl-Script dbmon.pl gefunden. Dieses Script kann man sowohl als Daemon, als auch direkt an der Konsole laufen lassen. Im Daemon-Modus wird der Output in 3 verschiedene Logs geschrieben:

• dbmon.err
  Querys, die errors produzieren bzw. vom Script gekillt werden
• dbmon.log
  Einzelne Events mit einer eindeutigen Prüfsumme, Start-, Laufzeit und der Event-ID
• dbmon.sql
  Prüfsumme mit den kompletten SQL Querys

Hier ein kurzer Auszug aus der dbmon.log:


Prüfsumme Typ Zeit Benutzer@Host Typ Event-ID Dauer
9494d0a84860066360989c45e188fd7b Start 2007-05-02 13:41:23 nagios@localhost Query id=23567
9494d0a84860066360989c45e188fd7b Finish 2007-05-02 13:41:23 nagios@localhost Query id=23567 0.032 sec

Anhand der Prüfsumme kann man dann die lange laufenden Querys aus der dbmon.log in der dbmon.sql zuordnen.

Es gibt in dem Script auch die Möglichkeit lang laufende Querys automatisch zu “killen”, das habe ich aber nicht benutzt, da es für diesen Anwendungsfall keinen Sinn macht.

Man sollte unbedingt darauf achten das Tool nicht dauerhaft laufen zu lassen, da es ganz gut an der CPU zieht. Zum vorrübergehenden Monitoring der Datenbank und der Querys, die dort auflaufen ist das Script aber genau das Richtige.