Percona tools for Mysql

Published on Author admin

percona-server-logo

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.

To install toolkit in Debian use apt-get (Advanced Packaging Tool):

Debian:

First of all slow queries should be logged – enable this feature at your configuration file – my.cnf

To see wether slow-query low is enable or not, run:

To analyse slow queries:
analizing whole log-file

analizing a part of the log-file:

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.

to enable general_log:

wait for a while to collect queries (an hour for example), than torn it off:

and run genlog analizing tool

To analyze memcache queries:

First of all we have to collect some dump from the interface:

where:
c – number of packets before exit
port 11211 – standart memcached port
i -interface
see man tcpdump for other options:)

Another useful tool – pt-table-checksum. You must use it to check your backups!