MySQL Server und Query Profiling
Hinweis: Bitte beachten Sie, dass dieser Artikel / diese Kategorie sich entweder auf ältere Software/Hardware Komponenten bezieht oder aus sonstigen Gründen nicht mehr gewartet wird. Diese Seite wird nicht mehr aktualisiert und ist rein zu Referenzzwecken noch hier im Archiv abrufbar. |
---|
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
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]
- Setup Tables: Display und Konfiguration von Monitoring Charakteristika
- Event Tables: Aktuelle Events für Threads (z.B. für Statements, Wait)
- History Tables: Aufzeichnung einer Event Historie
- Summary Tables: Zusammenfassung über Gruppen von Events
- Instance Tables: Welche Instanzen produzieren Events
- 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:
- The MySQL sys schema (github.com)
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
Innotop ist ein Werkzeug im top Stil zur Live-Analyse von SQL-Queries:
- Innotop (github.com)
- Innotop: A real-time, advanced investigation tool for MySQL (percona.com)
Percona Cacti Templates
Die Percona Monitoring Plugins for Cacti [9] erstellen, mit Hilfe von Cacti, Graphen zum Ressourcen Monitoring eines MySQL-Servers.
Einzelnachweise
- ↑ 1,0 1,1 Percona Toolkit for MySQL (percona.com)
- ↑ Percona Monitoring Plugins (percona.com)
- ↑ MySQL 5.5 Slow Query Log (dev.mysql.com)
- ↑ Query Optimization with MySQL 5.6: Old and New Tricks (de.slideshare.net)
- ↑ Query Optimization with MySQL 5.6 (slideshare.net)
- ↑ SHOW PROFILE Syntax (dev.mysql.com)
- ↑ Performance Schema Table Descriptions (dev.mysql.com)
- ↑ Using MySQL 5.6 Performance Schema to Troubleshoot Typical Workload Bottlenecks (percona.com)
- ↑ Percona Monitoring Plugins for Cacti (percona.com)
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.
|