Percona MySQL Toolkit
Percona Toolkit for MySQL is a collection of advanced command-line tools created by Percona to perform a variety of MySQL and system tasks that are too difficult or complex to perform manually. These tools are ideal alternatives to private or “one-off” scripts because they are professionally developed, formally tested, and fully documented. They are also fully self-contained, so installation is quick and easy and no libraries are installed. Toolkit designed for:
- Verify master and replica data consistency
- Efficiently archive rows
- Find duplicate indexes
- Summarize MySQL servers
- Analyze queries from logs and tcpdump
- Collect vital system information when problems occur
To install toolkit in FreeBSD use ports collection:
FreeBSD:
It’s a good idea to update ports before installing anything or to keep them updated by means of crontab.
|
1 2 |
# cd /usr/ports/databases/percona-toolkit # make install clean |
To install toolkit in Debian use apt-get (Advanced Packaging Tool):
Debian:
|
1 2 |
# apt-get update # apt-get install percona-toolkit |
First of all slow queries should be logged – enable this feature at your configuration file – my.cnf
|
1 2 3 |
slow_query_log = 1 # enable slow log queries slow-query-log-file = /var/log/mysql/db-slow.log # log-file long_query_time = 1 # time in seconds |
To see wether slow-query low is enable or not, run:
|
1 |
# mysql -e "show variables like '%slow_query%';" |
|
1 2 3 4 5 6 |
+---------------------+------------------------------+ | Variable_name | Value | +---------------------+------------------------------+ | slow_query_log | ON | | slow_query_log_file | /var/db/mysql/db-slow.log | +---------------------+------------------------------+ |
To analyse slow queries:
analizing whole log-file
|
1 |
# pt-query-digest slow.log > slow-log.txt |
analizing a part of the log-file:
|
1 |
# pt-query-digest --since="2013-12-31 00:00:01" --until="2013-12-31 00:10:00" /var/db/mysql/db-slow.log > digest-slow-log.txt |
You can analize not only slow-queries but also to analize general log file. By default I keep general log disabled as it takes much space. Enabling general-log can be done without restarting mysql.
|
1 |
# mysql -e "show variables like '%general%';" |
|
1 2 3 4 5 6 |
+------------------+-------------------------+ | Variable_name | Value | +------------------+-------------------------+ | general_log | OFF | | general_log_file | /var/db/mysql/db.log | +------------------+-------------------------+ |
to enable general_log:
|
1 |
# mysql -e "set global general_log = 'on'" |
wait for a while to collect queries (an hour for example), than torn it off:
|
1 |
# mysql -e "set global general_log = 'off'" |
and run genlog analizing tool
|
1 |
# pt-query-digest --type genlog /var/db/mysql/db.log > genlog.txt |
To analyze memcache queries:
First of all we have to collect some dump from the interface:
|
1 |
# tcpdump -c 100 -s 65535 -x -nn -q -tttt -p -i em0 port 11211 > memcached_dump.txt |
where:
c – number of packets before exit
port 11211 – standart memcached port
i -interface
see man tcpdump for other options:)
|
1 |
# pt-query-digest --type memcached file.txt > digest-memcached.txt |
Another useful tool – pt-table-checksum. You must use it to check your backups!
|
1 2 |
# pt-table-checksum h=host,P=port,u=user,p=pass --databases dbname # pt-table-checksum h=host,P=port,u=user,p=pass --tables db.some_table |