MySQL Server und Query Profiling

Aus Thomas-Krenn-Wiki
Wechseln zu: Navigation, Suche

Der Datenbank-Server MySQL bietet unterschiedliche Möglichkeiten für Profiling und Monitoring, sowohl für den Server selbst als auch für dessen SQL-Queries. Der folgende Artikel stellt einige Varianten vor, wie detaillierte Informationen über MySQL eingeholt werden. Am Ende des Artikels werden Außerdem die Percona Cacti Templates erwähnt, die Graphen zum Ressourcen Monitoring von MySQL erstellen.

MySQL Summary

Das Programm pt-mysql-summary aus dem Percona Toolkit liefert eine Übersicht zum MySQL-Server:[1]

# pt-mysql-summary
# Percona Toolkit MySQL Summary Report #######################
# Instances ##################################################
  Port  Data Directory             Nice OOM Socket
  ===== ========================== ==== === ======
        /var/lib/mysql             0    0   /var/lib/mysql/mysql.sock
# MySQL Executable ###########################################
       Path to executable | /usr/sbin/mysqld
              Has symbols | Yes
# Report On Port 3306 ########################################
                     User | root@localhost
                     Time | 2015-04-14 07:49:09 (CEST)
                 Hostname | mysql1
                Databases | 15
                  Datadir | /var/lib/mysql/
                Processes | 6 connected, 3 running
              Replication | Is not a slave, has 2 slaves connected
[...]
# InnoDB #####################################################
         Buffer Pool Size | 15.0G
         Buffer Pool Fill | 100%
        Buffer Pool Dirty | 0%
[...]

Die Abschnitte gliedern sich in:

  • Summary Report
  • Processlist
  • Status Counters
  • Table Cache Usage
  • Percona Server Features und XtraDB Cluster
  • Plugins
  • Query Cache
  • Noteworthy Technologies (z.B. SSL, Prepared Statements)
  • InnoDB Counters
  • MyISAM Cache
  • Security (Old Passwords)
  • Binary Logging
  • Noteworthy Variables (z.B. flush_time, Heap und Buffer Sizes)

Status Counters

Details zu Status Counter erhält man auch über den erweiterten Status Output. Einzelne Werte dieses Outputs lassen sich auch per pmp-check-mysql-status überwachen: Diese Werte sind über Icinga überwachbar, z.B. mit dem pmp-check-mysql-status Plugin:[2]

  • z.B. -x Threads_running -w 20 -c 40
# mysqladmin ext | grep Innodb_buffer_pool_pages_free
| Innodb_buffer_pool_pages_free                 | 618820

Slow Query Log

Das Slow Query Log zeigt SQL-Queries an, die länger als einen bestimmte Dauer zu Abarbeitung benötigten. Die Dauer wird über den Parameter long_query_time (in Sekunden) bestimmt.[3]

Per default ist das Slow Query Log deaktiviert, um es zu aktivierten entfernt man den Kommentar in der MySQL-Konfiguration vor log_slow_queries:

$ sudo grep slow /etc/mysql/my.cnf 
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
#log-queries-not-using-indexes

Um Queries zu loggen, die keine Indices benutzen, muss außerdem das Kommentarzeichen vor log-queries-not-using-indexes entfernt werden. Außerdem werden keine Queries geloggt, die sich im Query Cache befinden. Die aktuelle Konfiguration fragen Sie am besten direkt mit dem mysql Kommando ab:

$ mysql -u root -p
mysql> show variables like '%slow_query%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | ON                            |
| slow_query_log_file | /var/log/mysql/mysql-slow.log |
+---------------------+-------------------------------+

Nach einem Neustart des MySQL-Servers finden sich die Queries im verwendeten Log-File wieder:

# tail /var/log/mysql/mysql-slow.log
INSERT INTO icinga_customvariables (config_type, has_been_modified, instance_id, is_json, object_id, varname, varvalue) VALUES ('1', '0', 1, '0', 12, 'fping_number', '5');
# User@Host: icinga2-ido-mysq[icinga2-ido-mysq] @ localhost []
# Query_time: 0.000112  Lock_time: 0.000020 Rows_sent: 0  Rows_examined: 0
SET timestamp=1428491333;

pt-query-digest

Input Varianten für pt-query-digest, aus [1] (percona.com), F. 17

Wird die Variable long_query_time vom Slow Log auf 0 gesetzt, werden alle Queries mitgeloggt. Das Percona Toolkit[1] beinhaltet das Kommandozeilenwerkzeug pt-query-digest, mit dem im Nachhinein die Log-Einträge ausgewertet werden. Neben dem Slow Log kann pt-query-digest auch aufgezeichnete Queries von tcpdump, processlist, Binary Log oder dem General Query Log auswerten.[4].

pt-query-digest gibt ein Profil der Queries aus und zeigt Top Queries im Sinne von Laufzeit an (Langsamsten Queries zu Beginn):

# pt-query-digest mysql-slow.log 

# 29s user time, 310ms system time, 306.50M rss, 438.52M vsz
# Current date: Mon Apr 13 10:16:12 2015
# Hostname: mysql
# Files: mysql-slow.log
# Overall: 3.66k total, 467 unique, 0.00 QPS, 0.00x concurrency __________
# Time range: 2010-06-09 00:00:35 to 2015-02-11 20:00:11
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time        184984s      9s    419s     51s    151s     45s     42s
# Lock time            15s       0      3s     4ms       0    71ms       0
# Rows sent        500.05M       0   2.65M 139.79k   1.69M 491.22k    3.89
# Rows examine       3.23G       0 234.22M 923.81k   2.49M   5.53M 440.37k
# Query size       128.45M       6   2.75M  35.91k  68.96k 136.30k  10.29k
# Profile
# Rank Query ID           Response time    Calls R/Call   V/M   Item
# ==== ================== ================ ===== ======== ===== ==========
#    1 0x7A8EB8C13A4A8435 29885.0000 16.2%   305  97.9836 22.08 SELECT
#    2 0xA45C5FB6D066119B 26077.0000 14.1%   369  70.6694 22.49 SELECT
#    3 0x67A347A2812914DF 13737.0000  7.4%   397  34.6020 14.53 SELECT
#    4 0xD7A9797E81785092 11855.0000  6.4%   121  97.9752 22.05 SELECT

Output

Die Ausgabe von pt-query-digest ist per default ein Analysis Report. Daneben gibt es auch die Varianten Review (Queries werden in einer Datenbank abgelegt und können einzeln analysiert werden) oder History (Metriken zu Queries werden in einer Datenbank abgelegt). Folgende Werte sind beim Output besonders interessant:

  • Calls: Anzahl der Aufrufe des Queries
  • Exec Time: Ausführungszeit der Queries
  • Scores: V/M - Variance to Mean Ratio bzw. Index of Dispersion, zu Deutsch Variationskoeffizient, ein Streuungsmaß

MySQL Performance Schema

Das MySQL Performance Schema wurde mit Version 5.6 erweitert und auch per default aktiviert.[5] Das Performance Schema ersetzt das frühere Profiling Interface, mit Version 5.6.7 sind die Profiling Kommandos als deprecated gekennzeichnet.[6]

Das Performance Schema offenbart Runtime Statistics und Query Execution Statistics. Die Vorteile des Schemas sind die strukturierte Zugangsweise über SQL Statements, Timing Informationen und der geringe Overhead (zw. 5% und 10%). Die Tabellen des Performance Schemas gliedern sich in:[7][8]

  1. Setup Tables: Display und Konfiguration von Monitoring Charakteristika
  2. Event Tables: Aktuelle Events für Threads (z.B. für Statements, Wait)
  3. History Tables: Aufzeichnung einer Event Historie
  4. Summary Tables: Zusammenfassung über Gruppen von Events
  5. Instance Tables: Welche Instanzen produzieren Events
  6. Other Tables: Z.B. performance_timers, host_cache

Sys Schema

Das MySQL Schema, vormals ps_helper, ist ein einfacherer Zugang zu den umfassenden Tabellen des Performance Schemas:

Das Sys Schema erstellt mehrere Views, mit denen man auf das Performance Schema zugreift. Es ist daher eine Art Schicht über dem Performance Schema zum leichteren Auswerten der Events.

Seit Version 5.7.7 ist das Sys Schema auch direkt in MySQL integriert:

Folgende Fragen lassen sich z.B. mit dem Sys Schema bzw. ps_helper, über das Performance Schema, einfach beantworten:

  • Welche Tabelle belegt den meisten Platz im innodb Buffer Pool (innodb_buffer_stats_by_table)
  • Welche Statements haben die größte Latenz (statement_analysis)

Innotop

Bsp. Ausgabe von Innotop, von [2] (percona.com)

Innotop ist ein Werkzeug im top Stil zur Live-Analyse von SQL-Queries:

Percona Cacti Templates

Die Percona Monitoring Plugins for Cacti [9] erstellen, mit Hilfe von Cacti, Graphen zum Ressourcen Monitoring eines MySQL-Servers.

Mysql-cacti-template.png

Einzelnachweise

  1. 1,0 1,1 Percona Toolkit for MySQL (percona.com)
  2. Percona Monitoring Plugins (percona.com)
  3. MySQL 5.5 Slow Query Log (dev.mysql.com)
  4. Query Optimization with MySQL 5.6: Old and New Tricks (de.slideshare.net)
  5. Query Optimization with MySQL 5.6 (slideshare.net)
  6. SHOW PROFILE Syntax (dev.mysql.com)
  7. Performance Schema Table Descriptions (dev.mysql.com)
  8. Using MySQL 5.6 Performance Schema to Troubleshoot Typical Workload Bottlenecks (percona.com)
  9. Percona Monitoring Plugins for Cacti (percona.com)


Foto Georg Schönberger.jpg

Autor: Georg Schönberger

Georg Schönberger, Abteilung DevOps bei der XORTEX eBusiness GmbH, absolvierte an der FH OÖ am Campus Hagenberg sein Studium zum Bachelor Computer- und Mediensicherheit, Studium Master Sichere Informationssysteme. Seit 2015 ist Georg bei XORTEX beschäftigt und arbeitet sehr lösungsorientiert und hat keine Angst vor schwierigen Aufgaben. Zu seinen Hobbys zählt neben Linux auch Tennis, Klettern und Reisen.


Das könnte Sie auch interessieren

MySQL Optimize Table
MySQL Performance Tuning
MySQL Replikation