Table of contents
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
