mysql toolkit 是一個小插件，用於對數據庫進行監控，分析的小工具， 通常都是調用 perl 對當前的環境，數據庫進行分析，比對，值得學習一下。
下載地址
http://www.percona.com/redir/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.5-2.noarch.rpm
安裝時可能需要補充下面軟件
yum install -y perl-DBD-mysql perl-IO-Socket-SSL rpm -ivh percona-toolkit-2.2.5-2.noarch.rpm
命令列表
/usr/bin/pt-agent /usr/bin/pt-align /usr/bin/pt-archiver /usr/bin/pt-config-diff /usr/bin/pt-deadlock-logger /usr/bin/pt-diskstats /usr/bin/pt-duplicate-key-checker /usr/bin/pt-fifo-split /usr/bin/pt-find /usr/bin/pt-fingerprint /usr/bin/pt-fk-error-logger /usr/bin/pt-heartbeat /usr/bin/pt-index-usage /usr/bin/pt-ioprofile /usr/bin/pt-kill /usr/bin/pt-mext /usr/bin/pt-mysql-summary /usr/bin/pt-online-schema-change /usr/bin/pt-pmp /usr/bin/pt-query-digest /usr/bin/pt-show-grants /usr/bin/pt-sift /usr/bin/pt-slave-delay /usr/bin/pt-slave-find /usr/bin/pt-slave-restart /usr/bin/pt-stalk /usr/bin/pt-summary /usr/bin/pt-table-checksum /usr/bin/pt-table-sync /usr/bin/pt-table-usage /usr/bin/pt-upgrade /usr/bin/pt-variable-advisor /usr/bin/pt-visual-explain
語法及作用 (顏色紅色表示需關注，藍色表示作用不大)
/usr/bin/pt-align
用於對文件進行格式化輸出
如文件 t 內容如下
[root@db2 aaa]# cat t a abc aaa 123d2 5 d2wf wwwxcc a233 ddcqq 55 23ds2 55aaa 5tty655 ccqqq
格式化後輸出如下
[root@db2 aaa]# /usr/bin/pt-align t a abc aaa 123d2 5 d2wf wwwxcc a233 ddcqq 55 23ds2 55aaa 5tty655 ccqqq
/usr/bin/pt-archiver
把一個庫中的表復制到另外一個服務器中
下面例子把 192.168.200.163 中 dbd.t1 復制至 192.168.200.171 中
[root@db2 ~]# /usr/bin/pt-archiver --source h=192.168.200.163,D=dbd,t=t1,u=terry,p=123 --dest h=192.168.200.171,D=dbd,t=t1,u=terry,p=123 --where "1=1"
注，必須在 source 中為表創建索引，否則報下面錯誤信息
Cannot find an ascendable index in table at /usr/bin/pt-archiver line 3175.
/usr/bin/pt-config-diff
用於顯示兩台數據庫中配置文件不一樣的地方
[root@db2 ~]# /usr/bin/pt-config-diff h=192.168.200.163 h=192.168.200.171 --user=root --password=123 5 config differences Variable db2.mytest.com db4 ========================= ======================= ============ general_log_file db2.log db4.log hostname db2.mytest.com db4 log_error /mdb/db2.mytest.com.err /mdb/db4.err slow_query_log_file db2-slow.log db4-slow.log wsrep_node_name db2.mytest.com db4
/usr/bin/pt-deadlock-logger
能夠長期記錄死鎖信息到另外一個表中, 創建表格式如下
CREATE TABLE monitor.deadlocks (
server char(20) NOT NULL,
ts datetime NOT NULL,
thread int unsigned NOT NULL,
txn_id bigint unsigned NOT NULL,
txn_time smallint unsigned NOT NULL,
user char(16) NOT NULL,
hostname char(20) NOT NULL,
ip char(15) NOT NULL, -- alternatively, ip int unsigned NOT NULL
db char(64) NOT NULL,
tbl char(64) NOT NULL,
idx char(64) NOT NULL,
lock_type char(16) NOT NULL,
lock_mode char(1) NOT NULL,
wait_hold char(1) NOT NULL,
victim tinyint unsigned NOT NULL,
query text NOT NULL,
PRIMARY KEY (server,ts,thread)
) ENGINE=InnoDB
語法
pt-deadlock-logger h=10.1.1.29 --dest h=10.1.1.29,D=monitor,t=deadlocks --user=terry --ask-pass
注: 當前需安裝 perl-Term-ReadKey 應用才能夠支持密碼輸入
用戶 terry 除授權對 daedlocks 表具備寫操作還需要具備 process 權限
參考發生死鎖時候的信息:
mysql> select * from monitor.deadlocks \G
*************************** 1. row ***************************
server: 10.1.1.29
ts: 2013-12-16 03:41:05
thread: 24473
txn_id: 0
txn_time: 5
user: mini189
hostname:
ip: 10.1.1.25
db: 189mini
tbl: index_species_situaction
idx: GEN_CLUST_INDEX
lock_type: RECORD
lock_mode: X
wait_hold: w
victim: 0
query: update index_species_situaction s set s.new_version= '13121603410024300001' where s.new_version != 'DELETE' and s.species_id ='W6021601'
*************************** 2. row ***************************
server: 10.1.1.29
ts: 2013-12-16 03:41:05
thread: 25270
txn_id: 0
txn_time: 5
user: mini189
hostname:
ip: 10.1.1.25
db: 189mini
tbl: index_species_situaction
idx: GEN_CLUST_INDEX
lock_type: RECORD
lock_mode: X
wait_hold: w
victim: 1
query: update INDEX_SPECIES_SITUACTION h set h.new_version='DELETE' where h.specification_id = NAME_CONST('_specification_id',_utf8'S5F60811' COLLATE 'utf8_general_ci') and h.species_id= NAME_CONST('_species_id',_utf8'W6045FF3' COLLATE 'utf8_general_ci')
2 rows in set (0.00 sec)
/usr/bin/pt-diskstats
調用 /proc/diskstats 文件用於監控磁盤io
直接執行命令則返回下面信息
/usr/bin/pt-diskstats #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.0 sda 0.0 0.0 0.0 0% 0.0 0.0 7.0 8.0 0.1 50% 0.1 8.6 4% 0 7.0 5.7 2.9 1.0 sda2 0.0 0.0 0.0 0% 0.0 0.0 7.0 8.0 0.1 50% 0.1 8.6 4% 0 7.0 5.7 2.9 1.0 dm-0 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0 1.0 dm-2 0.0 0.0 0.0 0% 0.0 0.0 4.0 4.0 0.0 0% 0.1 20.0 3% 0 4.0 11.2 8.8 1.0 dm-3 0.0 0.0 0.0 0% 0.0 0.0 10.0 4.0 0.0 0% 0.2 19.0 4% 0 10.0 14.9 4.1 1.0 dm-4 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0 1.0 dm-6 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0 1.0 drbd0 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0 1.0 sda 0.0 0.0 0.0 0% 0.0 0.0 4.0 6.0 0.0 33% 0.0 5.3 3% 0 4.0 0.0 5.3 1.0 sda2 0.0 0.0 0.0 0% 0.0 0.0 4.0 6.0 0.0 33% 0.0 5.3 3% 0 4.0 0.0 5.3 1.0 dm-0 0.0 0.0 0.0 0% 0.0 0.0 3.0 4.0 0.0 0% 0.0 7.3 1% 0 3.0 2.7 4.7 1.0 dm-2 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0 1.0 dm-3 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0 1.0 dm-4 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0 1.0 dm-6 0.0 0.0 0.0 0% 0.0 0.0 3.0 4.0 0.0 0% 0.0 10.0 2% 0 3.0 4.0 6.0 1.0 drbd0 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0
/usr/bin/pt-duplicate-key-checker
能夠快速校驗某個數據庫或表具有多少個索引
[root@mini189a ~]# pt-duplicate-key-checker h=10.1.1.29 --database=189mini --user=terry --password=terry123 # ######################################################################## # Summary of indexes # ######################################################################## # Total Indexes 262
/usr/bin/pt-find
類似 linux 下 find 功能，能夠根據需要對數據庫中表進行過濾，搜索
查詢表大小
[root@mini189a ~]# pt-find --socket=/var/run/mysqld/mysql5.socket --user=terry --password=terry123 --tablesize +1M `189mini`.`pre_ware_broswers` `189mini`.`system_regions` `crazy`.`sys_log_bs`
最近 3 天內創建的表
[root@mini189a ~]# pt-find --socket=/var/run/mysqld/mysql5.socket --user=terry --password=terry123 --ctime -3 `monitor`.`deadlocks` `xszl`.`appreleasetable` `xszl`.`bulletintable` `xszl`.`companytable`
最近 35 分鐘內修改過的表
[root@mini189a ~]# pt-find --socket=/var/run/mysqld/mysql5.socket --user=terry --password=terry123 --mmin -35 `mysql`.`db` `mysql`.`user`
數據庫中空的表
[root@mini189a ~]# pt-find --socket=/var/run/mysqld/mysql5.socket --user=terry --password=terry123 --empty `189mini`.`ware_tag_assign` `189mini`.`ware_tag_content` `monitor`.`deadlocks` `mysql`.`columns_priv` `mysql`.`event`
/usr/bin/pt-fk-error-logger
用於檢測外鍵報錯信息, 需創建下面相關表進行數據存儲
CREATE TABLE foreign_key_errors ( ts datetime NOT NULL, error text NOT NULL, PRIMARY KEY (ts) )
語法測試
pt-fk-error-logger h=10.1.1.29 --dest h=10.1.1.29,D=monitor,t=foreign_key_errors --user=terry --ask-pass
注: 用戶 terry 除授權對 foreign_key_errors 表具備寫操作還需要具備 process 權限
參考下面返回信息
MariaDB [terry]> select * from monitor.foreign_key_errors \G *************************** 1. row *************************** ts: 2013-12-20 10:32:42 error: Transaction: TRANSACTION 2F1D0, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 3 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1 MySQL thread id 1329, OS thread handle 0x7fe89805a700, query id 7612 localhost root update insert into t4 values (5, 'aaaaa') Foreign key constraint fails for table `terry`.`t4`: , CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t3` (`id`) Trying to add in child table, in index `id` tuple: DATA TUPLE: 2 fields; 0: len 4; hex 80000005; asc ;; 1: len 6; hex 0000000007e9; asc ;; But in parent table `terry`.`t3`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 6; hex 00000002f1cd; asc ;; 2: len 7; hex f4000001a90134; asc 4;; 3: len 1; hex 64; asc d;; 1 row in set (0.00 sec)
/usr/bin/pt-heartbeat
用於監控 ab 復制延時信息，需創建下面表用於信息存儲
CREATE TABLE heartbeat (
ts varchar(26) NOT NULL,
server_id int unsigned NOT NULL PRIMARY KEY,
file varchar(255) DEFAULT NULL, -- SHOW MASTER STATUS
position bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS
relay_master_log_file varchar(255) DEFAULT NULL, -- SHOW SLAVE STATUS
exec_master_log_pos bigint unsigned DEFAULT NULL -- SHOW SLAVE STATUS
);
/usr/bin/pt-index-usage
根據日志分析查詢過程中是否使用到索引
/usr/bin/pt-ioprofile
分析並打印最近活躍的 IO 與相關進程信息 （只針對 mysql 進程）
[root@mini189a ~]# pt-ioprofile
2013年 12月 20日 星期五 11:27:52 CST
Tracing process ID 44802
total read pwrite write fsync open close getdents lseek ftruncate filename
0.190553 0.000000 0.000903 0.000000 0.189650 0.000000 0.000000 0.000000 0.000000 0.000000 /data/ibdata1
0.107560 0.000000 0.000297 0.000000 0.107263 0.000000 0.000000 0.000000 0.000000 0.000000 /data/ib_logfile0
0.027070 0.000000 0.000000 0.027070 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 /var/log/mysqld/mysql5-access.log
0.015691 0.000000 0.000000 0.000000 0.000000 0.000689 0.000480 0.014522 0.000000 0.000000 /data/189mini/
0.000362 0.000058 0.000000 0.000159 0.000000 0.000000 0.000000 0.000000 0.000081 0.000064 /tmp/ib4I3wV6
0.000192 0.000000 0.000000 0.000192 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 /data/mysql5.000097
0.000030 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000030 0.000000 /tmp/ibiybk6Q
/usr/bin/pt-kill
用於 kill 某些指定的 SQL 查詢
/usr/bin/pt-mext
用於記錄固定時間間隔內 status 返回值的變化 (下面例子將會計算 10 秒內的變化)
pt-mext -r -- mysqladmin -p'password' --socket=/var/run/mysqld/mysql5.socket ext -i10 -c2 | awk '{ if ($3 !~0) print $0}'
Com_select 19446417 198
Com_show_status 11 1
Created_tmp_tables 1015031 1
Handler_commit 20829923 198
Handler_read_first 21935275 198
Handler_read_key 83253660 198
Handler_read_rnd_next 25490412028 257632
Handler_write 63925147 294
Innodb_buffer_pool_read_requests 25739644905 259716
Innodb_rows_read 25421266296 257116
Qcache_not_cached 18701233 198
Queries 28860341 199
Questions 22720514 199
Select_scan 19915354 199
Table_locks_immediate 21238032 19
/usr/bin/pt-mysql-summary
給當前數據庫進行一些數據統計，不一一對統計進行討論
注: 執行該命令時，需要調用 mysqldump, 需具備 mysqldump 命令執行路徑
pt-mysql-summary --socket=/var/run/mysqld/mysql5.socket --password='password' Database Tables Views SPs Trigs Funcs FKs Partn 189mini 266 10 crazy 14 login 1 monitor 1 mysql 24 #mysql50#lost+found openne 18 test xszl 29 60 Database InnoDB MyISAM CSV 189mini 266 10 crazy 14 login 1 monitor 1 mysql 22 2 #mysql50#lost+found openne 18 test xszl 29 Database BTREE 189mini 280 crazy 14 login 1 monitor 1 mysql 31 #mysql50#lost+found openne 25 test xszl 149
/usr/bin/pt-online-schema-change
允許在不鎖定表條件下修改表
/usr/bin/pt-query-digest
從日志中進行 SQL 分析
/usr/bin/pt-show-grants
必須要以管理員身份登錄，能夠把數據庫之前授權信息進行打印
pt-show-grants -p'mini189!QAZ' --socket=/var/run/mysqld/mysql5.socket -- Grants dumped by pt-show-grants -- Dumped from server Localhost via UNIX socket, MySQL 5.5.34-debug-log at 2013-12-20 14:43:22 -- Grants for 'crazy'@'10.1.1.%' GRANT USAGE ON *.* TO 'crazy'@'10.1.1.%' IDENTIFIED BY PASSWORD '*4C1B9FACE717B2947CB8D52B32C3CFE8DA8DD8CB'; GRANT ALL PRIVILEGES ON `crazy`.* TO 'crazy'@'10.1.1.%'; -- Grants for 'mini145'@'%' GRANT ALL PRIVILEGES ON *.* TO 'mini145'@'%' IDENTIFIED BY PASSWORD '*AA2442B2AE9D3C29F895E57F366092819A3F6738'; -- Grants for 'mini145'@'10.1.1.%' GRANT ALL PRIVILEGES ON *.* TO 'mini145'@'10.1.1.%' IDENTIFIED BY PASSWORD '*AA2442B2AE9D3C29F895E57F366092819A3F6738'; -- Grants for 'minibackup'@'10.1.1.%' GRANT REPLICATION SLAVE, SUPER ON *.* TO 'minibackup'@'10.1.1.%' IDENTIFIED BY PASSWORD '*D5A75D5F1208A6763F64193744F94E57373C128E';
/usr/bin/pt-slave-delay
令從服務器延時復制
/usr/bin/pt-slave-find
校驗 mysql 從服務器信息
/usr/bin/pt-slave-restart
監控並重啟從服務器
/usr/bin/pt-summary
統計計算機信息，如內存，CPU，網卡，分區，文件節點，IP地址，網線連接狀態，端口連接情況，網絡連接狀態
/usr/bin/pt-summary
# Percona Toolkit System Summary Report ######################
Date | 2013-12-20 07:03:47 UTC (local TZ: CST +0800)
Hostname | mini189a
Uptime | 42 days, 15:11, 2 users, load average: 0.49, 0.47, 0.39
System | HP; ProLiant DL380p Gen8; vNot Specified (Rack Mount Chassis)
Service Tag | 6CU304WPZN
Platform | Linux
Release | Red Hat Enterprise Linux Server release 6.3 (Santiago)
Kernel | 2.6.32-358.23.2.el6.centos.plus.x86_64
Architecture | CPU = 64-bit, OS = 64-bit
Threading | NPTL 2.12
Compiler | GNU CC version 4.4.7 20120313 (Red Hat 4.4.7-4).
SELinux | Disabled
Virtualized | No virtualization detected
# Processor ##################################################
Processors | physical = 2, cores = 12, virtual = 24, hyperthreading = yes
Speeds | 24x1994.866
Models | 24xIntel(R) Xeon(R) CPU E5-2620 0 @ 2.00GHz
Caches | 24x15360 KB
/usr/bin/pt-table-checksum
MySQL 主從復制校驗
/usr/bin/pt-table-sync
進行兩個主機中的 MySQL 數據庫，或者表數據同步
注， 目標服務器中必須存在與源服務器一樣的數據庫及表，否則，不存在的不表無法進行同步
pt-table-sync --execute h=192.168.200.163 --database terry h=192.168.200.171 --user=root --password=123
/usr/bin/pt-variable-advisor
給與對數據庫參數定義的建議
pt-variable-advisor h=localhost -p'password' --socket=/var/run/mysqld/mysql5.socket # 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. # 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. # WARN slave_net_timeout: This variable is set too high.
/usr/bin/pt-visual-explain
對 sql 進行 explain 分析並以樹目錄結構顯示
