Percona-Toolkit… das ist doch nur für Admins? Mag man denken, aber es gibt in dieser großen Sammlung von Werkzeugen (der Nachfolger von Maatkit) auch Dinge, die ein reiner Developer nutzen kann und sollte. Und ich meine da nicht nur DevOps. Im Artikel wird genau erklärt, welche Tools das sind und wie ihr sie insbesondere als reiner Developer sofort nutzen könnt.
Vielleicht als erstes auch den Tipp, sich die aktuelle Version der Percona-Tools zu installieren. Das wäre aktuell die 2.1.7 vom 19. November. Für die unter uns, die sich jetzt sagen „ochnööö, schon wieder was installieren“: Ihr könnt die Tools auch einzeln (mit wget) herunterladen und ausführen – vorausgesetzt Perl ist installiert:
# wget percona.com/get/TOOL # perl TOOL
wobei ihr natürlich TOOL durch den Namen des jeweiligen Tools ersetzt.
So, und bevor ich anfange den knapp bemessenen Platz zu verschwenden (man sagte mir maximal zwei Bildschirmseiten) fange ich einfach mal an.
Indexe aufräumen mit pt-duplicate-key-checker
Der Key-checker ist das Gegenstück zu MySQLs Procedure Analyse(), aber halt für Indexe. Das Programm prüft Notwendigkeit und Redundanz von Indexen und Foreign-Keys und macht Vorschläge zur „Verbesserung“. Sieht so aus:
[TESTVM]# pt-duplicate-key-checker D=TESTDB,u=test,p=supergeheim # ######################################################################## # TESTDB.Boxes # ######################################################################## # BoxID is a duplicate of PRIMARY # Key definitions: # UNIQUE KEYBoxID(BoxID), # PRIMARY KEY (BoxID), # Column types: #boxidbigint(20) not null default '0' # To remove this duplicate index, execute: ALTER TABLETESTDB.BoxesDROP INDEXBoxID; # ######################################################################## # TESTDB.groups # ######################################################################## # Key group_name ends with a prefix of the clustered index # Key definitions: # UNIQUE KEYgroup_name(group_name,group_id) # PRIMARY KEY (group_id), # Column types: #group_namevarchar(255) not null default '' #group_idint(10) unsigned not null default '0' # To shorten this duplicate clustered index, execute: ALTER TABLETESTDB.groupsDROP INDEXgroup_name, ADD INDEXgroup_name(group_name); ...
Und so weiter. Ihr könnt die Ausgabe direkt als SQL-Script ausführen, aber erst nachdem ihr alles angeschaut habt. Denn zum Beispiel macht der Index `group_name´ oben unter Umständen viel Sinn! Das ist aber ein ganz anderes Thema.
Etwas grundsätzliches
Die meisten (!) Percona-Tools verlangen als Verbindungsparameter zur Datenbank ein „DSN“. Wer jetzt an die DSN bei PDO denkt, liegt falsch. Percona benutzt hier ein Format, welches mich stark an LDAP erinnert. Das mag verwirren – wenn ihr euch aber näher damit beschäftigt, merkt ihr, dass es durchaus durchdacht ist. „D=TESTDB“ bedeutet nichts anderes als verbinde zu Datenbank „TESTDB“, „u=test“ ist der Username und „p=supergeheim“ das Passwort. „h“ wäre – wer hätte es geahnt? – der Host. Und so weiter, alle Parameter sind in jedem Befehl in der Doku erklärt und sie werden jeweils auch erweitert, also beachten. Die Parameter könnt ihr in beliebiger Reihenfolge mit Komma getrennt aneinander hängen.
Sich einen Überblick verschaffen mit pt-mysql-summary und pt-summary
Es kommt vor, dass ihr in einer völlig fremden Umgebung arbeiten müsst. Eine neue VM, ein anderes Betriebssystem, ein neues Projekt… Es ist dann eine gute Idee, sich erst mal einen Überblick zu verschaffen. Ausdrücklich erwähne ich hier auch noch mysqltuner.pl, was für diesen Zweck ebenfalls gut zu gebrauchen ist (und dazu noch Tipps gibt; zu Tipps von Tools befolgen aber später noch mehr).
Es kommt ja auch mal vor, dass ihr vor lauter Kopf die Bretter nicht mehr seht – auch dann ist es manchmal eine gute Idee, diese Tools einfach mal laufen zu lassen, denn mit ihnen könnt ihr systematische Fehler gut finden.
Ausdrücklich sei hiermit davor gewarnt diese Tools „einfach mal so“ auf einem Produktiven System laufen zu lassen! Dazu bitte vorher mal die Dokumentation genau durchlesen.
pt-mysql-summary
Dieses Tool benutzt für die Datenbankverbindung leider wieder die MySQL-Syntax, also kein DSN. Zwei Bindestriche tippen und alles danach kennt ihr vom MySQL-Kommandozeilen-Tool.
# pt-mysql-summary -- -u root -psupergeheim
Erzeugt viel Ausgabe…
# Percona Toolkit MySQL Summary Report #######################
System time | 2012-12-07 21:00:15 UTC (local TZ: CET +0100)
# Instances ##################################################
Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
3306 /var/lib/mysql 0 0 /var/run/mysqld/mysqld.sock
# MySQL Executable ###########################################
Path to executable | /usr/sbin/mysqld
Has symbols | No
# Report On Port 3306 ########################################
User | root@localhost
Time | 2012-12-07 22:00:15 (CET)
Hostname | vaillant-debian
Version | 5.5.20-1~dotdeb.0 (Debian)
Built On | debian-linux-gnu x86_64
Started | 2012-12-07 11:35 (up 0+10:24:32)
Databases | 6
Datadir | /var/lib/mysql/
Processes | 1 connected, 1 running
Replication | Is not a slave, has 0 slaves connected
Pidfile | /var/run/mysqld/mysqld.pid (exists)
# Processlist ################################################
Command COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
Query 1 1 0 0
...
Das schaut ihr – mit der geöffneten Dokumentation daneben – durch und versucht zu verstehen, was da angezeigt wird. Dann speichert es einfach ab, mit Datum und Uhrzeit. Wenn dann mal irgendwann irgendwas nicht mehr läuft, vergleicht ihr diese Datei mit der aktuellen Ausgabe. Hilft in 50 Prozent aller Fälle weiter! Ganz ehrlich: Ich überspringe das durchlesen meist, außer wenn ich weiß, was ich suche. Aber das zu speichern/archivieren solltet ihr euch angewöhnen.
Beispiel für pt-summary
Funktioniert im Prinzip genauso wie pt-mysql-summary, nur liefert es Daten über die gesamte Maschine. Es ist also stark von Vorteil, wenn ihr dafür root-Rechte habt. Der Aufruf braucht prinzipiell keine Parameter.
# pt-summary
Ich zeige keine Ausgabe, die Dokumentation dazu ist viel ausführlicher, als ich das hier darstellen könnte. Auch hier gilt: Ausgabe abspeichern mit Datum im Dateinamen und diffen, wenn etwas nicht mehr funktioniert.
Besser als nix: pt-query-advisor und pt-variable-advisor
Bei diesen beiden Tools bin ich zwiegespalten. Immer wenn eine Maschine einem Menschen Vorschläge macht, dies oder jenes zu „verbessern“, solltet ihr sicher sein, daß die vorgeschlagenen Änderungen wirklich etwas verbessern, ansonsten bleiben lassen. Ihr könnt diese Werkzeuge also entweder als DAU oder als schlauer Developer nutzen, daher sind sie in meiner Hitliste dabei.
pt-query-advisor
Ihr wollt sehen, welche Queries von einem Request ausgelöst werden. Die Daten zu bekommen ist seit MySQL 5.1.21 recht einfach: Slow-Query-Log anschalten, Wartezeit auf 0 Sekunden setzen.
mysql> SET GLOBAL slow_query_log ='ON'; mysql> SET GLOBAL long_query_time=0.0; # auf millisekunden genau
Anfänger, Vorsicht: Damit werden alle Queries in die Datei geschrieben (welche Datei seht ihr mit „SHOW VARIABLES LIKE ‚%slow%'“), wenn ihr also öfter mal ein Backup einspielt (was auf einem Entwicklungssystem ja Sinn machen kann), dann ist da schnell die Platte voll. Also bevor ihr euch wundert, warum plötzlich gar nix mehr funktioniert, hoffe ich, ihr erinnert euch an pt-summary?
Den Dump in den pt-query-advisor pumpen:
# tail -f /var/lib/mysql/slow-query.log | pt-query-advisor --group-by none --report-format full -v
Und schon erzeugt das für alle Queries eine Analyse und gibt aus, was ihm nicht passt.
# Query ID 0x4B1E7805DC277905 at byte 1401856
# NOTE ALI.001 Aliasing without the AS keyword. Explicitly using the AS keyword in column or table aliases, such as "tbl AS alias," is more readable than implicit aliases such as "tbl alias".
# NOTE CLA.003 LIMIT with OFFSET. Paginating a result set with LIMIT and OFFSET is O(n^2) complexity, and will cause performance problems as the data grows larger.
# NOTE COL.001 SELECT *. Selecting all columns with the * wildcard will cause the query's meaning and behavior to change if the table's schema changes, and might cause the query to retrieve too much data.
# NOTE KWR.001 SQL_CALC_FOUND_ROWS is inefficient. SQL_CALC_FOUND_ROWS can cause performance problems because it does not scale well; use alternative strategies to build functionality such as paginated result screens.
# CRIT JOI.001 Mixing comma and ANSI joins. Mixing comma joins and ANSI joins is confusing to humans, and the behavior and precedence differs between some MySQL versions, which can introduce bugs.
# WARN RES.001 Non-deterministic GROUP BY. The SQL retrieves columns that are neither in an aggregate function nor the GROUP BY expression, so these values will be non-deterministic in the result.
# NOTE STA.001 The != operator is non-standard. Use the <> operator to test for inequality instead.
SELECT SQL_CALC_FOUND_ROWS
err.*,
kb.*,
...
Wenn bestimmte Regeln nicht gefallen, könnt ihr diese excluden: „–ignore-rules ALI.001“ und schon meckert es nicht mehr das fehlende „AS“ an, welches für dieses Projekt in fast jeder Query benutzt wurde und nachträglich kaum änderbar ist.
Fazit: Super bei einem Code-Review. Einfach den Analyzer mal draufschalten und ich garantiere interessante Diskussionen! :) Ihr könnt es vielleicht auch dazu verwenden, um in einem Projekt bestimmte Antipatterns zu verbieten. Keine Empfehlung dies zu automatisieren, denn mit manchen Queries kommt es nicht zurecht, beispielsweise moserte es mir bei einem verschachteltem JOIN mit Subselects an, siehe Zeile 6. Die Query ist aber diesbezüglich einwandfrei. Da scheinen also noch Bugs drin zu sein.
pt-variable-advisor
Oder auch das mysqltuner.pl (siehe oben) für Arme. Naja, ich übertreibe :) . pt-variable-advisor macht sehr zuverlässig genau was es soll. Aber macht es Sinn? Nun, ich nehme es deswegen hier mit rein, so manchen dummen Fehler findet ihr damit tatsächlich.
Hier die Ausgabe von meinem Testsystem:
[TESTVM]# pt-variable-advisor u=root,p=supergeheim | grep -v 'NOTE\|^$' # WARN delay_key_write: MyISAM index blocks are never flushed until necessary. # WARN innodb_log_file_size: The InnoDB log file size is set to its default value, which is not usable on production systems. # WARN slave_net_timeout: This variable is set too high. # WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.
Ich hab mir hier die NOTE’s und leere Zeilen schon rausgefiltert. Sicher: alles richtig, aber ist es auf einem Entwicklungssystem auch wichtig? Kann ich so nicht beantworten.
Zum Schluss pt-visual-explain
Du verstehst deine eigene Query nicht mehr? Deine Query versteht dich nicht? Dann ist pt-visual-explain vielleicht das richtige für Dich!
Über dieses Tool wurde schon in einigen anderen Blog-Artikeln gepostet und ich mache das jetzt auch. Denn es stellt den zeilenorientieren EXPLAIN einer Query als Baumstruktur dar, was allein schon revolutionär ist.
Meiner Meinung nach hilft es, das zeilenorientierte Explain besser zu verstehen. Wer aber erwartet, dass dadurch alles einfacher wird, den muss ich enttäuschen; es sieht zwar so aus, als wäre es viel logischer, aber eigentlich wird die Information nur anders verteilt. Das seht ihr gut bei richtig großen Queries:
# mysql -uroot -psupergeheim TESTDBUfff! Das ist schon ziemlich viel. Durch pt-visual-explain gejagt:
# mysql -uroot -psupergeheim TESTDBPRIMARY | possible_keys PRIMARY | key_len 257 | ref func | rows 1 +- DEPENDENT SUBQUERY +- Filesort | +- Filter with WHERE | +- Bookmark lookup | +- Table | | table eref | | possible_keys PRIMARY | +- Index lookup | key eref->PRIMARY | possible_keys PRIMARY | key_len 257 | ref func | rows 1 +- DEPENDENT SUBQUERY +- JOIN | +- Filter with WHERE | | +- Bookmark lookup | | +- Table | | | table e_obj | | | possible_keys errorcode | | +- Unique index lookup | | key e_obj->errorcode | | possible_keys errorcode | | key_len 365 | | ref TESTDB.e_mand.errorcode,TESTDB.e_mand.device_id,TESTDB.kb.sernum | | rows 1 | +- JOIN | +- Filter with WHERE | | +- Unique index lookup | | key kb->PRIMARY | | possible_keys PRIMARY | | key_len 66 | | ref func | | rows 1 | +- JOIN | +- Bookmark lookup | | +- Table | | | table e_mand | | | possible_keys device_id,errorcode_deviceid_idx | | +- Unique index lookup | | key e_mand->device_id | | possible_keys device_id,errorcode_deviceid_idx | | key_len 301 | | ref TESTDB.eref.device_id,TESTDB.eref.errorcode,const | | rows 1 | +- JOIN | +- Filter with WHERE | | +- Bookmark lookup | | +- Table | | | table eref | | | possible_keys PRIMARY | | +- Unique index lookup | | key eref->PRIMARY | | possible_keys PRIMARY | | key_len 299 | | ref func,func | | rows 1 | +- Constant index lookup | key mand->PRIMARY | possible_keys PRIMARY | key_len 2 | ref const | rows 1 +- DEPENDENT SUBQUERY +- JOIN | +- Filter with WHERE | | +- Bookmark lookup | | +- Table | | | table e_obj | | | possible_keys errorcode | | +- Unique index lookup | | key e_obj->errorcode | | possible_keys errorcode | | key_len 365 | | ref TESTDB.e_mand.errorcode,TESTDB.e_mand.device_id,TESTDB.kb.sernum | | rows 1 | +- JOIN | +- Filter with WHERE | | +- Unique index lookup | | key kb->PRIMARY | | possible_keys PRIMARY | | key_len 66 | | ref func | | rows 1 | +- JOIN | +- Bookmark lookup | | +- Table | | | table e_mand | | | possible_keys device_id,errorcode_deviceid_idx | | +- Unique index lookup | | key e_mand->device_id | | possible_keys device_id,errorcode_deviceid_idx | | key_len 301 | | ref TESTDB.eref.device_id,TESTDB.eref.errorcode,const | | rows 1 | +- JOIN | +- Filter with WHERE | | +- Bookmark lookup | | +- Table | | | table eref | | | possible_keys PRIMARY | | +- Unique index lookup | | key eref->PRIMARY | | possible_keys PRIMARY | | key_len 299 | | ref func,func | | rows 1 | +- Constant index lookup | key mand->PRIMARY | possible_keys PRIMARY | key_len 2 | ref const | rows 1 +- JOIN +- Unique index lookup | key cB->PRIMARY | possible_keys PRIMARY,p_user_id | key_len 34 | ref TESTDB.uma.user_id | rows 1 +- JOIN +- Bookmark lookup | +- Table | | table uma | | possible_keys PRIMARY | +- Unique index lookup | key uma->PRIMARY | possible_keys PRIMARY | key_len 8 | ref TESTDB.kb.boxowner_umaid | rows 1 +- JOIN +- Bookmark lookup | +- Table | | table kb | | possible_keys PRIMARY | +- Unique index lookup | key kb->PRIMARY | possible_keys PRIMARY | key_len 66 | ref TESTDB.err.sernum | rows 1 +- JOIN +- Filter with WHERE | +- Index lookup | key err_ref->PRIMARY | possible_keys PRIMARY | key_len 257 | ref TESTDB.err.errorcode | rows 1 +- JOIN +- Bookmark lookup | +- Table | | table ger | | possible_keys PRIMARY | +- Unique index lookup | key ger->PRIMARY | possible_keys PRIMARY | key_len 18 | ref TESTDB.err.device_id | rows 1 +- Filter with WHERE +- Bookmark lookup +- Table | table err | possible_keys msg_status,errorcode_deviceid_idx,sernum +- Index range scan key err->sernum possible_keys msg_status,errorcode_deviceid_idx,sernum key_len 66 rows 4 Soooo... jetzt die Codeansicht oben behutsam zuklappen! :) Die Männer mit der weißen Jacke kommen gleich...
... nein, so schlimm ist es nicht, ehrlich! Wie gesagt, es gibt einem eine andere Sicht und verdeutlicht, wie komplex das Thema eigentlich ist. Und - ernsthaft - das ist oft sehr wichtig, zum Beispiel, um dem Kunden zu zeigen, dass seine Ideen gut, aber anstrengend sind.Damit wären wir schon am Ende des ersten Teils und haben - wie versprochen - knapp zwei Bildschirmseiten gefüllt (wenn ihr den Monitor hochkant stellt und die Schrift ganz klein macht). Ich verspreche, im zweiten Teil dieses Artikels geht es nochmal einen Schritt tiefer in die erstaunliche Welt dieses Toolsets (Schritt tiefer, nicht Schrift kleiner). Unter anderem wird pt-query-digest und pt-diskstats behandelt.
Das wird aber erst nach Weihnachten der Fall sein. Darum wünsche ich allen Lesern, die bis hier durchgehalten haben, ein Frohes Fest!
Lesenswert: Percona-Tools für Entwickler, Teil 1 http://t.co/zXcBemFJ