Percona Toolkit hands-on

Preamble

While browsing on Percona server found their open source licenses MySQL toolkit and below post is just an installation/testing example on a master-master active-passive replication cluster. Underlining operating system is Red Hat Enterprise Linux Server release 5.5 (Tikanga) 64 bits and Percona toolkit release is 2.0.4.

Installation

Download the Percona toolkit at http://www.percona.com/software/percona-toolkit/, unzip the file and installation is as simple as:

perl Makefile.PL
make
make test
make install

You obviously need Perl:

[root@server1 ~]# perl -v
 
This is perl, v5.8.8 built for x86_64-linux-thread-multi
 
Copyright 1987-2006, Larry Wall
 
Perl may be copied only under the terms of either the Artistic License or the
GNU General Public License, which may be found in the Perl 5 source kit.
 
Complete documentation for Perl, including FAQ lists, should be found on
this system using "man perl" or "perldoc perl".  If you have access to the
Internet, point your browser at http://www.perl.org/, the Perl Home Page.

And packages that will most probably not be on your server:

[mysql55@server1 percona-toolkit-2.0.4]$ perl Makefile.PL
Checking if your kit is complete...
Looks good
Warning: prerequisite DBD::mysql 3 not found.
Warning: prerequisite DBI 1.46 not found.
Writing Makefile for percona-toolkit

Install them from your repository servers (there are part of the default RedHat packages):

[root@server1 tmp]# rpm -Uvh perl-DBI-1.52-2.el5.x86_64.rpm
Preparing...                ########################################### [100%]
   1:perl-DBI               ########################################### [100%]
[root@server1 tmp]# rpm -Uvh perl-DBD-MySQL-3.0007-2.el5.x86_64.rpm
error: Failed dependencies:
        libmysqlclient.so.15()(64bit) is needed by perl-DBD-MySQL-3.0007-2.el5.x86_64
        libmysqlclient.so.15(libmysqlclient_15)(64bit) is needed by perl-DBD-MySQL-3.0007-2.el5.x86_64

MySQL release I have on my server is community one but it has been installed with tar file in a customized directory, so unlike rpm edition no libraries have been copied to /usr/lib64/mysql/ directory. And even if temptation to create a symbolic link is high my 5.5 MySQL community edition does have the good release of MySQL client library:

[root@server1 ~]# ll /mysql55/software/5.5.20/lib/
total 225144
-rwxr-xr-x 1 mysql55 mysql55  14768770 Dec 16 21:53 libmysqlclient.a
lrwxrwxrwx 1 mysql55 mysql55        16 Feb  6 14:09 libmysqlclient_r.a -> libmysqlclient.a
lrwxrwxrwx 1 mysql55 mysql55        17 Feb  6 14:09 libmysqlclient_r.so -> libmysqlclient.so
lrwxrwxrwx 1 mysql55 mysql55        17 Feb  6 14:09 libmysqlclient_r.so.18 -> libmysqlclient.so
lrwxrwxrwx 1 mysql55 mysql55        17 Feb  6 14:09 libmysqlclient_r.so.18.0.0 -> libmysqlclient.so
lrwxrwxrwx 1 mysql55 mysql55        20 Feb  6 14:09 libmysqlclient.so -> libmysqlclient.so.18
lrwxrwxrwx 1 mysql55 mysql55        24 Feb  6 14:09 libmysqlclient.so.18 -> libmysqlclient.so.18.0.0
-rwxr-xr-x 1 mysql55 mysql55   6838882 Dec 16 21:53 libmysqlclient.so.18.0.0
-rwxr-xr-x 1 mysql55 mysql55 103436570 Dec 16 21:55 libmysqld.a
-rwxr-xr-x 1 mysql55 mysql55 104226866 Dec 16 21:49 libmysqld-debug.a
-rwxr-xr-x 1 mysql55 mysql55      9488 Dec 16 21:52 libmysqlservices.a
-rwxr-xr-x 1 mysql55 mysql55    997127 Dec 16 21:40 libtcmalloc_minimal.so
drwxr-xr-x 3 mysql55 mysql55      4096 Feb  6 14:09 plugin

The libmysqlclient.so.15 library is coming with MySQL 5.0 so Percona toolkit has been compiled with this version that you must have on your server:

[root@server1 tmp]# rpm -Uvh perl-DBD-MySQL-3.0007-2.el5.x86_64.rpm mysql-5.0.77-4.el5_4.2.x86_64.rpm
Preparing...                ########################################### [100%]
   1:mysql                  ########################################### [ 50%]
   2:perl-DBD-MySQL         ########################################### [100%]

Then the installation procedure goes with no issue…

Testing

Summarized system information with pt-summary:

[root@server1 ~]# pt-summary
# Percona Toolkit System Summary Report ######################
        Date | 2012-03-13 09:02:33 UTC (local TZ: CET +0100)
    Hostname | server1
      Uptime | 38 days, 19:30, 2 users, load average: 0.03, 0.01, 0.00
      System | VMware, Inc.; VMware Virtual Platform; vNone (Other)
 Service Tag | VMware-42 3a b7 9a 8c 0d 47 3f-a2 6e aa ed 0c 86 15 9a
    Platform | Linux
     Release | Red Hat Enterprise Linux Server release 5.5 (Tikanga)
      Kernel | 2.6.18-274.12.1.el5
Architecture | CPU = 64-bit, OS = 64-bit
   Threading | NPTL 2.5
    Compiler | GNU CC version 4.1.2 20080704 (Red Hat 4.1.2-51).
     SELinux | Disabled
 Virtualized | VMWare
# Processor ##################################################
  Processors | physical = 1, cores = 0, virtual = 1, hyperthreading = no
      Speeds | 1x3066.775
      Models | 1xIntel(R) Xeon(R) CPU X5675 @ 3.07GHz
      Caches | 1x12288 KB
.
.
.

MySQL summarized information with pt-mysql-summary:

[root@server1 ~]# pt-mysql-summary --defaults-file=/mysql55/software/5.5.20/conf/my.cnf --socket=/mysql55/software/5.5.20/conf/mysql55.sock --user=root --password=password
# Percona Toolkit MySQL Summary Report #######################
         System time | 2012-03-13 08:42:18 UTC (local TZ: CET +0100)
# Instances ##################################################
  Port  Data Directory             Socket
  ===== ========================== ======
   3316 /mysql55/data01            /mysql55/software/5.5.20/conf/mysql55.sock
# Report On Port 3316 ########################################
                User | root@localhost
                Time | 2012-03-13 09:42:18 (CET)
            Hostname | server1
             Version | 5.5.20-log MySQL
            Built On | linux2.6 x86_64
             Started | 2012-02-08 06:19 (up 34+03:22:53)
           Databases | 5
             Datadir | /mysql55/data01/
           Processes | 6 connected, 2 running
         Replication | Is a slave, has 1 slaves connected
             Pidfile | /mysql55/software/5.5.20/conf/5.5.20.pid (exists)
.
.
.

I/O activity with pt-ioprofile:

[root@server1 ~]# pt-ioprofile
strace is not in PATH
[root@server1 tmp]# rpm -Uvh strace-4.5.18-5.el5_4.1.x86_64.rpm
Preparing...                ########################################### [100%]
   1:strace                 ########################################### [100%]
[root@server1 ~]# pt-ioprofile
Mon Mar 12 17:59:23 CET 2012
Tracing process ID 28009
     total      write      lseek filename
  0.000079   0.000070   0.000009 /mysql55/data01/master.info

Find an d print hierarchical information of slaves with pt-slave-find:

[root@server1 ~]# pt-slave-find --defaults-file=/mysql55/software/5.5.20/conf/my.cnf --socket=/mysql55/software/5.5.20/conf/mysql55.sock --user=repladmin --password=password
Cannot connect to F=/mysql55/software/5.5.20/conf/my.cnf,S=/mysql55/software/5.5.20/conf/mysql55.sock,h=server3.domain.com,p=...,u=repladmin
Localhost
Version         5.5.20-log
Server ID       1
Uptime          34+03:38:57 (started 2012-02-08T06:19:25)
Replication     Is a slave, has 1 slaves connected, is not read_only
Filters         binlog_do_db=orgsecdb; replicate_do_db=orgsecdb
Binary logging  ROW
Slave status    0 seconds behind, running, no errors
Slave mode      STRICT
Auto-increment  increment 1, offset 1
InnoDB version  1.1.8

Interactive I/O monitoring with pt-diskstats:

[root@server1 ~]# pt-diskstats --iterations=5
 
  #ts device    rd_s rd_avkb rd_mb_s rd_mrg rd_cnc   rd_rt    wr_s wr_avkb wr_mb_s wr_mrg wr_cnc   wr_rt busy in_prg    io_s  qtime stime
  {1} sda        0.0     0.0     0.0     0%    0.0     0.0     5.4    47.0     0.2    91%    0.0     0.3   2%      0     5.4    0.0   0.3
  {1} sda2       0.0     0.0     0.0     0%    0.0     0.0     5.4    47.0     0.2    91%    0.0     0.3   2%      0     5.4    0.0   0.3
  {1} dm-0       0.0     0.0     0.0     0%    0.0     0.0    58.1     4.0     0.2     0%    0.2     3.9   1%      0    58.1    3.8   0.1
  {1} dm-3       0.0     0.0     0.0     0%    0.0     0.0     5.4     4.0     0.0     0%    0.0     3.2   1%      0     5.4    0.5   2.8
 
  {2} sda        0.0     0.0     0.0     0%    0.0     0.0     2.3    47.0     0.1    91%    0.0     0.3   1%      0     2.3    0.0   0.3
  {2} sda2       0.0     0.0     0.0     0%    0.0     0.0     2.3    47.0     0.1    91%    0.0     0.3   1%      0     2.3    0.0   0.3
  {2} dm-0       0.0     0.0     0.0     0%    0.0     0.0    24.7     4.0     0.1     0%    0.1     3.9   0%      0    24.7    3.8   0.1
  {2} dm-3       0.0     0.0     0.0     0%    0.0     0.0     2.3     4.0     0.0     0%    0.0     3.2   1%      0     2.3    0.5   2.8
 
  {3} sda        0.0     0.0     0.0     0%    0.0     0.0     1.5    47.0     0.1    91%    0.0     0.3   1%      0     1.5    0.0   0.3
  {3} sda2       0.0     0.0     0.0     0%    0.0     0.0     1.5    47.0     0.1    91%    0.0     0.3   1%      0     1.5    0.0   0.3
  {3} dm-0       0.0     0.0     0.0     0%    0.0     0.0    15.7     4.0     0.1     0%    0.1     3.9   0%      0    15.7    3.8   0.1
  {3} dm-3       0.0     0.0     0.0     0%    0.0     0.0     1.5     4.0     0.0     0%    0.0     3.2   0%      0     1.5    0.5   2.8
 
  {4} sda        0.0     0.0     0.0     0%    0.0     0.0     1.1    47.0     0.0    91%    0.0     0.3   0%      0     1.1    0.0   0.3
  {4} sda2       0.0     0.0     0.0     0%    0.0     0.0     1.1    47.0     0.0    91%    0.0     0.3   0%      0     1.1    0.0   0.3
  {4} dm-0       0.0     0.0     0.0     0%    0.0     0.0    11.5     4.0     0.0     0%    0.0     3.9   0%      0    11.5    3.8   0.1
  {4} dm-3       0.0     0.0     0.0     0%    0.0     0.0     1.1     4.0     0.0     0%    0.0     3.2   0%      0     1.1    0.5   2.8
 
  {5} sda        0.0     0.0     0.0     0%    0.0     0.0     0.8    47.0     0.0    91%    0.0     0.3   0%      0     0.8    0.0   0.3
  {5} sda2       0.0     0.0     0.0     0%    0.0     0.0     0.8    47.0     0.0    91%    0.0     0.3   0%      0     0.8    0.0   0.3
  {5} dm-0       0.0     0.0     0.0     0%    0.0     0.0     9.1     4.0     0.0     0%    0.0     3.9   0%      0     9.1    3.8   0.1
  {5} dm-3       0.0     0.0     0.0     0%    0.0     0.0     0.8     4.0     0.0     0%    0.0     3.2   0%      0     0.8    0.5   2.8

Check your MySQL configuration with pt-variable-advisor:

[root@server1 ~]# pt-variable-advisor --defaults-file=/mysql55/software/5.5.20/conf/my.cnf --socket=/mysql55/software/5.5.20/conf/mysql55.sock --password=password u=root
Code for rule concurrent_insert caused an error: Argument "AUTO" isn't numeric in numeric gt (>) at /usr/bin/pt-variable-advisor line 2770.
# WARN delay_key_write: MyISAM index blocks are never flushed until necessary.
 
# WARN innodb_flush_log_at_trx_commit-1: InnoDB is not configured in strictly ACID mode.
 
# WARN innodb_lock_wait_timeout: This option has an unusually long value, which can cause system overload if locks are not being released.
 
# WARN innodb_log_buffer_size: The InnoDB log buffer size generally should not be set larger than 16MB.
 
# NOTE innodb_max_dirty_pages_pct: The innodb_max_dirty_pages_pct is lower than the default.
 
# NOTE log_warnings-2: Log_warnings must be set greater than 1 to log unusual events such as aborted connections.
 
# NOTE max_connect_errors: max_connect_errors should probably be set as large as your platform allows.
 
# NOTE port: The server is listening on a non-default port.
 
# NOTE query_cache_size-1: The query cache does not scale to large sizes and can cause unstable performance when larger than 128MB, especially on multi-core machines.
 
# NOTE read_buffer_size-1: The read_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.
 
# NOTE read_rnd_buffer_size-1: The read_rnd_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.
 
# WARN read_rnd_buffer_size-2: The read_rnd_buffer_size variable should not be larger than 4M.
 
# WARN slave_net_timeout: This variable is set too high.
 
# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.
 
# NOTE sort_buffer_size-2: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.
 
# NOTE tx_isolation-1: This server's transaction isolation level is non-default.
 
# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.
 
# NOTE innodb_flush_method: Most production database servers that use InnoDB should set innodb_flush_method to O_DIRECT to avoid double-buffering, unless the I/O system is very low performance.
 
# WARN myisam_recover_options: myisam_recover_options should be set to some value such as BACKUP,FORCE to ensure that table corruption is noticed.
 
# WARN sync_binlog: Binary logging is enabled, but sync_binlog isn't configured so that every transaction is flushed to the binary log for durab

Slow query log analysis with pt-query-digest:

[root@server1 ~]# pt-query-digest --report-format header,profile /mysql/server1-slow.log
 
# Overall: 1.99k total, 149 unique, 0.00 QPS, 0.00x concurrency __________
# Time range: 2010-03-22 13:13:59 to 2013-03-11 16:06:57
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          4981s       0    280s      3s   403us     18s    33us
# Lock time          292ms       0    54ms   147us   273us     2ms    26us
# Rows sent          2.62M       0   1.58M   1.35k    0.99  39.97k       0
# Rows examine     275.16M       0  62.27M 141.80k    1.96   2.56M       0
# Query size       664.74k       6   3.16k  342.58   1.33k  524.12  143.84
 
# Profile
# Rank Query ID           Response time   Calls R/Call   V/M   Item
# ==== ================== =============== ===== ======== ===== ===========
#    1 0x1CCF735CEB39D6D5 1270.5568 25.5%     9 141.1730 33.05 DELETE BI_Exposition
#    2 0x8060026A31BB3EC7  701.3049 14.1%     5 140.2610  7.94 SELECT produitCatalogue ProduitChimique
#    3 0xB4C1CE0917987F93  620.8775 12.5%   287   2.1633 96.42 UPDATE QRTZ_TRIGGERS
#    4 0xCAB0EDEC13D7BC50  521.2124 10.5%     4 130.3031 20.58 DELETE BI_Situation
#    5 0x19C8068B5C1997CD  366.4479  7.4%    14  26.1749 10... ROLLBACK
#    6 0x67A347A2812914DF  316.5318  6.4%     3 105.5106 51.53 SELECT ec_db_rousset.bi_exposition
#    7 0x813031B8BBC3B329  219.3753  4.4%    30   7.3125 98.03 COMMIT
#    8 0x9B6CE34F9DC60D98  132.1539  2.7%     2  66.0769  0.13 SELECT BI_Mesures BI_Situation BI_Exposition BI_UniteSituation BI_Danger BI_Unite BI_Personne
#    9 0x9FFBE835F98924C6  123.4319  2.5%     2  61.7160  0.02 SELECT BI_Mesures BI_Situation BI_Exposition BI_UniteSituation BI_Danger BI_Unite BI_Personne
#   10 0x6DBBD3E799B73D38   88.5905  1.8%     1  88.5905  0.00 SELECT BI_Mesures BI_Situation BI_Exposition BI_UniteSituation BI_Danger BI_Unite BI_Personne
#   11 0x54A6329BC9802FDE   87.0001  1.7%     1  87.0001  0.00 SELECT BI_Mesures BI_Situation BI_Exposition BI_UniteSituation BI_Danger BI_Unite BI_Personne
#   12 0x28571A5084976F56   83.3081  1.7%     1  83.3081  0.00 SELECT BI_Mesures BI_Situation BI_Exposition BI_Danger BI_Personne
#   13 0x287670D5BF5891D4   81.2447  1.6%     4  20.3112 59.22 SHOW INNODB STATUS
#   14 0xE069C0E62CA10202   79.0131  1.6%     1  79.0131  0.00 SELECT BI_Mesures BI_Situation BI_Exposition BI_Danger BI_Personne
#   15 0x40A20900E614B0F6   77.1559  1.5%     1  77.1559  0.00 SELECT BI_Situation BI_Exposition BI_UniteSituation BI_Unite BI_Personne
# MISC 0xMISC              213.2583  4.3%  1622   0.1315   0.0 <134 ITEMS>

Visual tree representation with pt-visual-explain of a query taken from the slow query log output:

[mysql51@server1~]$  mysql -e "explain SELECT * FROM piwik.piwik_archive_blob_2012_01" | pt-visual-explain
Table scan
rows           199068
+- Table
   table          piwik_archive_blob_2012_01

References

About Post Author

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>