Wissen, was läuft

Mit den Tabellen im Performance Schema (P_S) schuf MySQL schon in der Version 5.5 eine Möglichkeit, Interna des Servers zu instrumentieren. Ähnlich wie für dtrace ist der Code mit Hooks versehen, die Events erzeugen. Das können zum Beispiel Funktionsaufrufe sein, Wartezeiten des Betriebssystems oder sogenannte Stages. Bei Letzterem handelt es sich um Vorgänge während der Statement-Ausführung, etwa die Optimierung der Abfrage. Mit Version 5.6 (s. den //www.heise.de/artikel-archiv/ix/2012/10/066_Mehr-Durchblick:Artikel in iX 10/12) stieg die Anzahl der Hooks. Besonders die neuen Events erleichtern dem DBA die alltägliche Arbeit.

Wie der Name vermuten lässt, handelt es sich beim Performance Schema um eine Datenbank. Der Zugriff erfolgt deshalb ebenso wie beim älteren Information Schema mit SELECT-Befehlen. Jenes erstellt jedoch die Ergebnisse jeweils ad hoc, das P_S hingegen sammelt die Performance-Daten während der Laufzeit des Servers. Mit der kürzlich veröffentlichten Version 5.6 ist das P_S anders als im Vorgänger schon per Voreinstellung aktiviert. Es ist als klassische, nicht persistente Storage Engine implementiert und nutzt folglich dasselbe Interface wie InnoDB, MyISAM oder Binlog. Wichtiger Unterschied zu diesen Storage Engines: P_S vergisst alle Daten beim Herunterfahren des Servers.

Mit zwei Ausnahmen ist der Zugriff auf das P_S nur per SELECT-Befehl möglich: Die setup_%-Tabellen lassen sich mit UPDATE ändern, und TRUNCATE leert die Protokoll-Tabellen. Praktischerweise schreibt das P_S seine Änderungen nicht in den Binär-Log, sodass in einer Replikationsumgebung die Slaves keine Performance-Daten des Masters replizieren.

Tabellen fassen Ereignisse zusammen

Vereinfacht funktioniert P_S, indem der Server Events produziert, die von Tabellen auf verschiedene Art zusammengefasst werden. Im Folgenden geht es nur um die Möglichkeiten, die das P_S im Auslieferungszustand bietet. Es lässt sich darüber hinaus durch Änderungen etwa in den Tabellen setup_consumers und setup_instruments an eigene Wünsche anpassen.

Jeder DBA musste wohl schon erleben, dass MySQL von bestimmten Rechnern keine Verbindungsversuche mehr akzeptiert, zum Beispiel nach Netzproblemen. Der Server speichert die Verbindungsinformationen in seinem host_cache. Dazu gehören Anzahl und Art der Fehler. Steigt deren Zahl über den konfigurierbaren Wert von max-connection-error, verweigert MySQL weitere Zugriffe von diesem Rechner. Dank des P_S kann der DBA jetzt auf den früher nicht lesbaren host_cache zugreifen und Netzprobleme rechtzeitig erkennen. Bislang galt es, ein Gespür dafür zu haben und öfter mit FLUSH HOSTS alle Informationen der internen Tabelle zu löschen, auch die über die Fehler. In der Spalte host_cache.sum_connect_errors steht jetzt die Anzahl der relevanten Fehler, die gegen max-connection-error zählt.

Eine Übersicht der Verbindungen erlaubt dem DBA einen groben Einblick in das Nutzungsverhalten. Dafür zählt die Tabelle accounts alle bestehenden und beendeten Verbindungen. Ein Eintrag in ihr besteht aus der Kombination von User und Host in separaten Spalten, sodass man getrennt nach ihnen filtern kann. So gibt

SELECT user, SUM(current_connections) as anzahl 
   FROM accounts 
   WHERE user IS NOT NULL 
   GROUP BY user ORDER BY anzahl DESC 

die Nutzer sortiert nach der Summe aktueller Verbindungen aus. Die Einschränkung user IS NOT NULL verhindert das Zählen von System-Threads. Während diese Informationen auch aus der Tabelle PROCESSLIST im Information Schema zu gewinnen ist, verrät accounts in der Spalte total_connections zudem die Summe aller bisherigen Verbindungen, also auch beendeter:

SELECT user, SUM(total_connections) as anzahl 
   FROM accounts
   WHERE user IS NOT NULL 
   GROUP BY user ORDER BY anzahl DESC 

SHOW PROCESSLIST und analog die Tabelle PROCESSLIST im Information Schema enthalten Informationen über die laufenden Verbindungen, sodass der DBA und Überwachungsprogramme zum Beispiel lang laufende Abfragen finden können. Die Tabelle threads ist die informativere und schonendere P_S-Alternative dazu. Denn der Zugriff auf threads erfolgt nicht blockierend, während SHOW PROCESSLIST einen globalen Lock setzt, der den Aufbau neuer Verbindungen ausschließt. Außerdem setzt dieser Aufruf kurzfristig einen eigenen Lock für jeden Thread, was diesen verzögern kann.

Performance Schema ist schneller

Den Unterschied veranschaulicht ein kleiner Test, der in vier parallelen Verbindungen 100 000-mal die beiden Verfahren verwendete. Die Abfragen auf das P_S waren in 3,9 Sekunden erledigt, während SHOW PROCESSLIST mit 35,1 Sekunden rund neunmal so viel Zeit benötigte.

Darüber hinaus speichert threads Hintergrund-Threads wie die von InnoDB und liefert durch einen Join mit der Tabelle events_statements_current weitere Informationen zu den Abfragen:

SELECT * 
   FROM threads 
     JOIN events_statements_current USING (thread_id) 

Dazu gehört etwa, ob sie eine temporäre Tabelle erstellten und einen Index nutzten.

Während threads einen Überblick über die laufenden Prozesse liefert, ist es oft von Interesse, welche Abfragen der Server überhaupt ausgeführt hat. Hierzu eignen sich jene Tabellen im P_S, deren Namen mit events_statements_summary beginnen. Sie enthalten die Queries in einem normalisierten Format: Was sich nur hinsichtlich der konkreten Parameter unterscheidet, fassen sie zusammen und anonymisieren es.

So speichert die Tabelle events_statements_summary_by_digest alle normalisierten Abfragen und weitere Informationen, zum Beispiel die Anzahl der betrachteten und gesendeten Zeilen. Das ist ein Indiz für die Traffic-Verteilung. Außerdem erfährt man, welche Abfrage eine temporäre Tabelle erstellt hat oder gar vom Server auf die Festplatte ausgelagert wurde. Beides weist auf Performance-Einbußen hin. Früher verriet nur die Ausgabe von SHOW GLOBAL STATUS, dass es temporäre Tabellen auf der Festplatte gab; eine Zuordnung zu Abfragen war nicht möglich. Das P_S liefert mit

SELECT schema_name, digest_text, sum_created_tmp_disk_tables
   FROM events_statements_summary_by_digest 
   WHERE sum_created_tmp_disk_tables > 0;

diese Information frei Haus.

Details zu Statements und Abfragen

Anhand der Namen der events_statements_summary- Tabellen lässt sich die Gruppierung der Daten erkennen. So fasst events_statements_summary_by_user_by_event_name alle Statement-Ereignisse (SELECT, UPDATE und so weiter) nach Anwendern zusammen, was man in SQL mit GROUP BY (User, Statements) erledigen würde:

SELECT * FROM events_statements_summary_by_user_by_event_name
   WHERE user='gast' AND count_star > 0;

etwa gibt die Abfragen des Users gast gruppiert nach Statement-Art aus. Die Bedingung count_star > 0 beschränkt die Ausgabe auf nicht leere Gruppen. Ohne P_S gibt SHOW GLOBAL STATUS LIKE ’COM_%’ zumindest die Summe aller Statements aus, jedoch nur global für alle Anwender. Das P_S kann diese Daten darüber hinaus nach User, Host, Account und Thread aufschlüsseln. Die Tabelle events_statements_summary_by_thread_by_event_name liefert die Statements gruppiert nach Threads. In events_statements_summary_global_by_event_name finden sich Details zu allen ausgeführten Statements, gruppiert nach ihrer Art (siehe Listingkasten).

EVENT_NAME: statement/sql/select
                  COUNT_STAR: 56
              SUM_TIMER_WAIT: 260686798000
              MIN_TIMER_WAIT: 169637000
              AVG_TIMER_WAIT: 4655121000
              MAX_TIMER_WAIT: 112761682000
               SUM_LOCK_TIME: 8457000000
                  SUM_ERRORS: 4
                SUM_WARNINGS: 0
           SUM_ROWS_AFFECTED: 0
               SUM_ROWS_SENT: 202070
           SUM_ROWS_EXAMINED: 202933
 SUM_CREATED_TMP_DISK_TABLES: 1
      SUM_CREATED_TMP_TABLES: 10
        SUM_SELECT_FULL_JOIN: 0
  SUM_SELECT_FULL_RANGE_JOIN: 0
            SUM_SELECT_RANGE: 0
      SUM_SELECT_RANGE_CHECK: 0
             SUM_SELECT_SCAN: 48
       SUM_SORT_MERGE_PASSES: 0
              SUM_SORT_RANGE: 0
               SUM_SORT_ROWS: 22
               SUM_SORT_SCAN: 9
           SUM_NO_INDEX_USED: 48
      SUM_NO_GOOD_INDEX_USED: 0 

Als letztes Beispiel seien die Ein-/Ausgabeoperationen betrachtet. Spätestens, wenn die Platte beim Lesen oder Schreiben nicht mehr mithält, interessiert den DBA, welche Tabellen die höchste Last verursachen. Eine Liste der zehn intensivsten Plattennutzer liefert

SELECT file_name, count_read+count_write  
   FROM file_summary_by_instance 
   ORDER BY (count_read+count_write) DESC 
   LIMIT 10; 

Dies setzt für InnoDB voraus, dass die Option innodb_file_per_table gesetzt ist, da sonst alle Tabellen in einer Datei landen und die Zuordnung zwischen Dateien und Tabellen nicht mehr funktioniert.

Fazit

Schon diese einfachen Beispiele zeigen, welche Möglichkeiten das Performance Schema bietet. Allerdings lassen sich noch keine Transaktionen instrumentieren. Zudem ist der Overhead der Technik streckenweise erheblich – da folgen hoffentlich noch Verbesserungen.

In Zukunft dürfte für den MySQL-DBA kein Weg am Performance Schema vorbeiführen. Auch dem Entwickler hilft es, wenn er alle Abfragen genau verfolgen und herausfinden kann, womit sie ihre Zeit verbringen.

Erkan Yanar

ist Diplom-Soziologe und arbeitet als DBA-Consultant.

Alle Links: www.ix.de/ix1304156 (ck)