Percona-Tools für Entwickler, Teil 1

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 KEY `BoxID` (`BoxID`),
#   PRIMARY KEY (`BoxID`),
# Column types:
#         `boxid` bigint(20) not null default '0'
# To remove this duplicate index, execute:
ALTER TABLE `TESTDB`.`Boxes` DROP INDEX `BoxID`;

# ########################################################################
# TESTDB.groups
# ########################################################################
# Key group_name ends with a prefix of the clustered index
# Key definitions:
#   UNIQUE KEY `group_name` (`group_name`,`group_id`)
#   PRIMARY KEY (`group_id`),
# Column types:
#         `group_name` varchar(255) not null default ''
#         `group_id` int(10) unsigned not null default '0'
# To shorten this duplicate clustered index, execute:
ALTER TABLE `TESTDB`.`groups` DROP INDEX `group_name`, ADD INDEX `group_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 TESTDB 

Ufff! Das ist schon ziemlich viel. Durch pt-visual-explain gejagt:

# mysql -uroot -psupergeheim TESTDB PRIMARY
      |        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!

Für neue Blogupdates anmelden:


Ein Gedanke zu “Percona-Tools für Entwickler, Teil 1

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.