MySQL Monitoring & Profiling

Hier will ich einiges festhalten zum Thema MySQL Monitoring.

Meist lassen sich bei einem MySQL Datenbank Server Performance Probleme nur durch ein gutes Monitoring aufspüren. Daher liefert MySQL diverse Informationen zur aktuellen Auslastung und über seinen Zustand über diverse SQL Befehle aus.

Was läuft gerade?

Der wohl am meist benutzte SQL Befehl bei der Auswertung ist der um sich sie aktuell laufenden Prozesse anzeigen zu lassen.

-- Shortcut für die Prozessliste
SHOW FULL PROCESSLIST;

-- Prozessliste anzeigen mit sortierung
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY STATE DESC

-- Menge aller Prozesse
SELECT count(*) FROM INFORMATION_SCHEMA.PROCESSLIST

-- Shortcut für die Prozessliste

SHOW FULL PROCESSLIST;

-- Prozessliste anzeigen mit sortierung

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY STATE DESC

-- Menge aller Prozesse

SELECT count(*) FROM INFORMATION_SCHEMA.PROCESSLIST

Wie ist der aktuelle Status der Datenbank?

Werte zum aktuellen Status wie z.B. Verbindungen, geöffnete Tabellen etc. lassen sich auch direkt per SQL abrufen.

-- Alle oder nur bestimmte Statuswerte anzeigen lassen
SHOW GLOBAL STATUS;
SHOW GLOBAL STATUS LIKE '%keyword%';

-- Alle oder nur bestimmte Variablen Werte holen
SHOW GLOBAL VARIABLES ;
SHOW GLOBAL VARIABLES LIKE '%keyword%';

-- Alle oder nur bestimmte Statuswerte anzeigen lassen

SHOW GLOBAL STATUS;

SHOW GLOBAL STATUS LIKE '%keyword%';

-- Alle oder nur bestimmte Variablen Werte holen

SHOW GLOBAL VARIABLES ;

SHOW GLOBAL VARIABLES LIKE '%keyword%';

Hier einige oft benutzte Befehle für bestimmte Teilinformationen

-- Informationen über die Tabellen
SHOW GLOBAL VARIABLES LIKE '%table_size';
SHOW TABLE STATUS;
SHOW OPEN TABLES;

-- Status über die Inno DB tabellen
SHOW GLOBAL STATUS LIKE 'inno%';

-- Puffergrößen
SHOW GLOBAL VARIABLES LIKE '%buffer%';

-- Infos über den Query Cache
SHOW GLOBAL VARIABLES LIKE '%query_cache%';

-- Infos über die Volltextsuche
SHOW GLOBAL VARIABLES LIKE 'ft_%';

-- charset Einstellungen
SHOW GLOBAL VARIABLES LIKE '%character%';
SHOW GLOBAL VARIABLES LIKE '%collation%';

-- Informationen über die Tabellen

SHOW GLOBAL VARIABLES LIKE '%table_size';

SHOW TABLE STATUS;

SHOW OPEN TABLES;

-- Status über die Inno DB tabellen

SHOW GLOBAL STATUS LIKE 'inno%';

-- Puffergrößen

SHOW GLOBAL VARIABLES LIKE '%buffer%';

-- Infos über den Query Cache

SHOW GLOBAL VARIABLES LIKE '%query_cache%';

-- Infos über die Volltextsuche

SHOW GLOBAL VARIABLES LIKE 'ft_%';

-- charset Einstellungen

SHOW GLOBAL VARIABLES LIKE '%character%';

SHOW GLOBAL VARIABLES LIKE '%collation%';

Auswertung und Aufbereitung der Daten

Anhand der Werte die uns nun MySQL liefern kann, kann man noch diverse Rückschlüssen ziehen. Man kann z.B. die Werte über ein bestimmten Zeitraum mitschneidet und dann als Diagramme visualisieren. Dies bieten z.B. einige Tools an die am Ende des Artikels vorgestellt werden. Hier einfach mal eine Liste mit Dingen die man auswerten und/oder visualisieren könnte:

Performance/Durchsatz

opened_tables/sec.
threads_created/sec.
avg queries/sec (t) für Select, Insert, Update, Replace, Delete, Call
read/sec. – select + qcache_read
writes/sec. – insert, update, delete, replace
Opened Tables (t)
New Connections (t)
Cache Hits vs. Inserts vs. Not Cached: avg cache ops/sec. (t)
Lowmem Prunes: avg cache ops/sec. (t)
Current-,Active-,Aborted Connections/min (t)
Inno DB Puffer, Query Cache, Key cache, Thread cache % (t)
Waited, Immediate: avg locks/sec. (t)
Memory Temp tables, Disk Temp Tables: total/min (t)

Auslastung

table_cache_size
open_tables
thread_cache_size
threads_cached
Zeigt an wie viel % der tmp Tabellen auf Festplatte erzeugt wurden. Es sollten so wenig wie möglich auf der Festplatte erzeugt werden. Der Wert sollte daher klein sein:
created on HD [%] = (Created_tmp_disk_tables / (Created_tmp_tables + Created_tmp_disk_tables)) * 100
Query Cache Hit Ration: qcache_hits*100 / (qcache_hits + com_select)
Connections: max_connections, max_used_connections, thread connections

Weblinks zum Thema MySQL Monitoring

Software Tools die bei der Auswertung helfen

MYSQL Jet Profiler – Überwacht laufende MySQL-Server und ermittelt die am meisten benutzten Tabellen sowie besonders lange laufende SQL-Abfragen
MySQL Query Analyzer and PHP
mysqlreport
mtop – zeigt Infos zum MySQL Server an
myTop – zeigt Infos zum MySQL Server an
Innotop
MRTG – The Multi Router Traffic Grapher
Ganglia (Distributiertes Grid Monitoring)
gmetric
show innodb status
DSH (Distributed SSH Shell) executing multiple remote shell commands
Maatkit – Power Tools for Power Users of MySQL
Nagios – Nagios PlugIn für MySQL
DBTuna – Monitoring Tool für SQL, MySQl und Oracle DBs

MySQL Monitor mit RRD

MySQL Activity Report – DEMO
MysqlRRDtoolConfig – pearl script to log mysql vars
RRDtool – Messdaten mit RRDtool und Perl verwalten

Thomas Eses

WEBDEVELOPER – PHP, JavaScript, Datenbanken…

Kategorie Tags: