1. Percona-toolkit简介
Percona Toolkit is a collection of advanced command-line tools used by Percona) support staff to perform a variety of MySQL, MongoDB, 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.
Percona Toolkit was derived from Maatkit and Aspersa, two of the best-known toolkits for MySQL server administration. It is developed and supported by Percona. For more information and other free, open-source software developed by Percona, visit http://www.percona.com/software/.
2. 安装Percona-toolkit
2.1 安装perl依赖包
[root@study /root/software]# rpm -ivh percona-toolkit-3.0.3-1.el6.x86_64.rpm warning: percona-toolkit-3.0.3-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY error: Failed dependencies: perl(DBI) >= 1.13 is needed by percona-toolkit-3.0.3-1.el6.x86_64 perl(DBD::mysql) >= 1.0 is needed by percona-toolkit-3.0.3-1.el6.x86_64 perl(Time::HiRes) is needed by percona-toolkit-3.0.3-1.el6.x86_64 perl(IO::Socket::SSL) is needed by percona-toolkit-3.0.3-1.el6.x86_64 perl(Term::ReadKey) is needed by percona-toolkit-3.0.3-1.el6.x86_64 yum -y install perl-DBI yum -y install perl-DBD-MySQL yum -y install perl-Time-HiRes -y yum -y install perl-IO-Socket-SSL -y yum install perl-TermReadKey.x86_64
2.2 安装Percona-toolkit
wget https://www.percona.com/downloads/percona-toolkit/3.0.3/binary/redhat/6/x86_64/percona-toolkit-3.0.3-1.el6.x86_64.rpm rpm -ivh percona-toolkit-3.0.3-1.el6.x86_64.rpm
3. Percona-toolkit使用
3.1 使用pt-summary查看系统状态，可以用于系统监控（很详细）
[root@study /root]# pt-summary # Percona Toolkit System Summary Report ###################### Date | 2017-06-03 02:10:32 UTC (local TZ: CST +0800) Hostname | study Uptime | 16 days, 12:48, 1 user, load average: 0.00, 0.00, 0.00 System | Alibaba Cloud; Alibaba Cloud ECS; vpc-i440fx-2.1 (Other) Service Tag | da3082c9-57d1-4912-9ead-2b190832b182 Platform | Linux Release | CentOS release 6.8 (Final) Kernel | 2.6.32-642.13.1.el6.x86_64 Architecture | CPU = 64-bit, OS = 64-bit Threading | NPTL 2.12 SELinux | Disabled Virtualized | QEmu # Processor ################################################## Processors | physical = 1, cores = 1, virtual = 1, hyperthreading = no Speeds | 1x2500.024 Models | 1xIntel(R) Xeon(R) CPU E5-2682 v4 @ 2.50GHz Caches | 1x40960 KB # Memory ##################################################### Total | 1.8G Free | 357.0M Used | physical = 1.5G, swap allocated = 2.0G, swap used = 620.0k, virtual = 1.5G Shared | 32.2M Buffers | 164.6M Caches | 823.6M Dirty | 92 kB UsedRSS | 987.6M Swappiness | 60 DirtyPolicy | 20, 10 DirtyStatus | 0, 0 Locator Size Speed Form Factor Type Type Detail ========= ======== ================= ============= ============= =========== DIMM 0 2048 MB Unknown DIMM RAM Other # Mounted Filesystems ######################################## Filesystem Size Used Type Opts Mountpoint /dev/vda1 40G 23% ext3 rw / tmpfs 939M 0% tmpfs rw /dev/shm # Disk Schedulers And Queue Size ############################# sr0 | [cfq] 128 vda | [cfq] 128 # Disk Partioning ############################################ Device Type Start End Size ============ ==== ========== ========== ================== /dev/vda Disk 42949672960 /dev/vda1 Part 1 5222 42944186880 # Kernel Inode State ######################################### dentry-state | 51809 46095 45 0 0 0 file-nr | 736 0 65535 inode-nr | 33412 4427 # LVM Volumes ################################################ Unable to collect information # LVM Volume Groups ########################################## Unable to collect information # RAID Controller ############################################ Controller | No RAID controller detected # Network Config ############################################# Controller | Red Hat, Inc Virtio network device FIN Timeout | 20 Port Range | 65000 # Interface Statistics ####################################### interface rx_bytes rx_packets rx_errors tx_bytes tx_packets tx_errors ========= ========= ========== ========== ========== ========== ========== lo 35000 250 0 35000 250 0 eth0 1000000000 2250000 0 2250000000 2000000 0 # Network Devices ############################################ Device Speed Duplex ========= ========= ========= eth0 # Network Connections ######################################## Connections from remote IP addresses 14.131.234.27 1 71.79.35.85 1 106.11.68.13 1 140.205.140.205 1 Connections to local IP addresses 172.17.178.209 4 Connections to top 10 local ports 18913 1 22 1 443 1 61345 1 States of connections CLOSE_WAIT 1 ESTABLISHED 3 LISTEN 6 # Top Processes ############################################## PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 1 root 20 0 19236 1396 1136 S 0.0 0.1 0:00.70 init 2 root 20 0 0 0 0 S 0.0 0.0 0:00.01 kthreadd 3 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0 4 root 20 0 0 0 0 S 0.0 0.0 0:00.82 ksoftirqd/0 5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 stopper/0 6 root RT 0 0 0 0 S 0.0 0.0 0:00.99 watchdog/0 7 root 20 0 0 0 0 S 0.0 0.0 0:38.10 events/0 8 root 20 0 0 0 0 S 0.0 0.0 0:00.00 events/0 9 root 20 0 0 0 0 S 0.0 0.0 0:00.00 events_long/0 # Notable Processes ########################################## PID OOM COMMAND 1108 -17 sshd # Simplified and fuzzy rounded vmstat (wait please) ########## procs ---swap-- -----io---- ---system---- --------cpu-------- r b si so bi bo ir cs us sy il wa st 2 0 0 0 1 5 25 10 0 0 99 0 0 0 0 0 0 35 0 300 1000 6 6 88 0 0 0 0 0 0 0 25 125 300 0 0 100 0 0 0 0 0 0 0 0 125 250 0 0 100 0 0 0 0 0 0 0 400 150 300 0 1 99 0 0 # Memory mamagement ########################################## Transparent huge pages are enabled. # The End ####################################################
3.2 使用pt-mysql-summary查看MySQL详细信息及状态
[root@study /root]# pt-mysql-summary --user=root --password=xxxxxx Warning: Using a password on the command line interface can be insecure. # Percona Toolkit MySQL Summary Report ####################### System time | 2017-06-03 02:21:31 UTC (local TZ: CST +0800) # Instances ################################################## Port Data Directory Nice OOM Socket ===== ========================== ==== === ====== 3306 /data/mysql 0 0 /tmp/mysql.sock # MySQL Executable ########################################### Path to executable | /usr/local/mysql/bin/mysqld Has symbols | Yes # Report On Port 3306 ######################################## User | root@localhost Time | 2017-06-03 10:21:31 (CST) Hostname | study Version | 5.6.34-log MySQL Community Server (GPL) Built On | linux-glibc2.5 x86_64 Started | 2017-06-03 00:00 (up 0+10:21:27) Databases | 4 Datadir | /data/mysql/ Processes | 1 connected, 1 running Replication | Is not a slave, has 0 slaves connected Pidfile | /data/mysql/mysql.pid (exists) # Processlist ################################################ Command COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- Query 1 1 0 0 User COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- root 1 1 0 0 Host COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- localhost 1 1 0 0 db COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- NULL 1 1 0 0 State COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- init 1 1 0 0 # Status Counters (Wait 10 Seconds) ########################## Variable Per day Per second 10 secs Aborted_connects 10 Binlog_cache_use 700 Binlog_stmt_cache_use 6 Bytes_received 5000000 60 400 Bytes_sent 800000000 9000 2250 Com_change_db 700 Com_delete 70 Com_insert 125 Com_select 12500 1 Com_set_option 1500 Com_show_fields 400 Com_show_status 4 Com_show_variables 2 Com_update 500 Connections 700 1 Created_tmp_disk_tables 2000 1 Created_tmp_files 15 Created_tmp_tables 3000 6 Flush_commands 2 Handler_commit 12500 Handler_delete 70 Handler_external_lock 30000 Handler_prepare 1250 Handler_read_first 1500 Handler_read_key 1250000 15 Handler_read_last 2 Handler_read_next 300000 3 Handler_read_prev 12500 Handler_read_rnd 150000 1 Handler_read_rnd_next 3000000 35 40 Handler_update 400000 4 Handler_write 250000 3 40 Innodb_buffer_pool_bytes_data 45000000 500 Innodb_buffer_pool_pages_flushed 2500 Innodb_buffer_pool_read_requests 2250000 25 Innodb_buffer_pool_reads 2500 Innodb_buffer_pool_write_requests 6000 Innodb_data_fsyncs 1750 Innodb_data_read 45000000 500 Innodb_data_reads 3000 Innodb_data_writes 4000 Innodb_data_written 80000000 1000 Innodb_dblwr_pages_written 2500 Innodb_dblwr_writes 350 Innodb_log_write_requests 2000 Innodb_log_writes 1000 Innodb_os_log_fsyncs 700 Innodb_os_log_written 1500000 15 Innodb_pages_created 20 Innodb_pages_read 2500 Innodb_pages_written 2500 Innodb_rows_deleted 70 Innodb_rows_inserted 100 Innodb_rows_read 4000000 45 Innodb_rows_updated 500 Innodb_num_open_files 60 Innodb_available_undo_logs 300 Key_read_requests 1250000 15 Key_reads 6 Key_write_requests 125000 1 Open_table_definitions 200 Opened_files 8000 5 Opened_table_definitions 200 Opened_tables 200 Qcache_hits 22500 Qcache_inserts 10000 Qcache_lowmem_prunes 4500 Qcache_not_cached 1500 1 Queries 40000 4 Questions 40000 4 Select_range 3500 Select_scan 1750 Sort_range 2000 Sort_rows 150000 1 Sort_scan 2500 Table_locks_immediate 15000 Table_open_cache_hits 15000 Table_open_cache_misses 200 Threads_created 9 Uptime 90000 1 1 # Table cache ################################################ Size | 256 Usage | 30% # Key Percona Server features ################################ Table & Index Stats | Not Supported Multiple I/O Threads | Enabled Corruption Resilient | Not Supported Durable Replication | Not Supported Import InnoDB Tables | Not Supported Fast Server Restarts | Not Supported Enhanced Logging | Not Supported Replica Perf Logging | Disabled Response Time Hist. | Not Supported Smooth Flushing | Not Supported HandlerSocket NoSQL | Not Supported Fast Hash UDFs | Unknown # Percona XtraDB Cluster ##################################### # Plugins #################################################### InnoDB compression | ACTIVE # Query cache ################################################ query_cache_type | ON Size | 16.0M Usage | 100% HitToInsertRatio | 200% # Schema ##################################################### Specify --databases or --all-databases to dump and summarize schemas # Noteworthy Technologies #################################### SSL | No Explicit LOCK TABLES | No Delayed Insert | No XA Transactions | No NDB Cluster | No Prepared Statements | No Prepared statement count | 0 # InnoDB ##################################################### Version | 5.6.34 Buffer Pool Size | 128.0M Buffer Pool Fill | 15% Buffer Pool Dirty | 0% File Per Table | ON Page Size | 16k Log File Size | 3 * 32.0M = 96.0M Log Buffer Size | 2M Flush Method | Flush Log At Commit | 2 XA Support | ON Checksums | ON Doublewrite | ON R/W I/O Threads | 4 4 I/O Capacity | 200 Thread Concurrency | 0 Concurrency Tickets | 5000 Commit Concurrency | 0 Txn Isolation Level | REPEATABLE-READ Adaptive Flushing | ON Adaptive Checkpoint | Checkpoint Age | 0 InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue Oldest Transaction | 0 Seconds History List Len | 1294 Read Views | 0 Undo Log Entries | 0 transactions, 0 total undo, 0 max undo Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (0 log, 0 chkp); 0 pwrites Pending I/O Flushes | 0 buf pool, 0 log Transaction States | 1xnot started # MyISAM ##################################################### Key Cache | 16.0M Pct Used | 20% Unflushed | 0% # Security ################################################### Users | 2 users, 0 anon, 0 w/o pw, 0 old pw Old Passwords | 0 # Binary Logging ############################################# Binlogs | 8 Zero-Sized | 0 Total Size | 5.0M binlog_format | MIXED expire_logs_days | 7 sync_binlog | 0 server_id | 1 binlog_do_db | binlog_ignore_db | # Noteworthy Variables ####################################### Auto-Inc Incr/Offset | 1/1 default_storage_engine | InnoDB flush_time | 0 init_connect | SET NAMES utf8mb4 init_file | sql_mode | NO_ENGINE_SUBSTITUTION join_buffer_size | 8M sort_buffer_size | 8M read_buffer_size | 2M read_rnd_buffer_size | 8M bulk_insert_buffer | 0.00 max_heap_table_size | 8M tmp_table_size | 32M max_allowed_packet | 500M thread_stack | 256k log | log_error | /data/mysql/mysql-error.log log_warnings | 1 log_slow_queries | log_queries_not_using_indexes | OFF log_slave_updates | OFF # Configuration File ######################################### Config File | /etc/my.cnf [client] port = 3306 socket = /tmp/mysql.sock default-character-set = utf8mb4 [mysql] prompt = "MySQL [\d]> " no-auto-rehash [mysqld] port = 3306 socket = /tmp/mysql.sock basedir = /usr/local/mysql datadir = /data/mysql pid-file = /data/mysql/mysql.pid user = mysql bind-address = 0.0.0.0 server-id = 1 init-connect = 'SET NAMES utf8mb4' character-set-server = utf8mb4 skip-name-resolve back_log = 300 max_connections = 938 max_connect_errors = 6000 open_files_limit = 65535 table_open_cache = 256 max_allowed_packet = 500M binlog_cache_size = 1M max_heap_table_size = 8M tmp_table_size = 32M read_buffer_size = 2M read_rnd_buffer_size = 8M sort_buffer_size = 8M join_buffer_size = 8M key_buffer_size = 16M thread_cache_size = 16 query_cache_type = 1 query_cache_size = 16M query_cache_limit = 2M ft_min_word_len = 4 log_bin = mysql-bin binlog_format = mixed expire_logs_days = 7 log_error = /data/mysql/mysql-error.log slow_query_log = 1 long_query_time = 1 slow_query_log_file = /data/mysql/mysql-slow.log performance_schema = 0 explicit_defaults_for_timestamp skip-external-locking default_storage_engine = InnoDB innodb_file_per_table = 1 innodb_open_files = 500 innodb_buffer_pool_size = 128M innodb_write_io_threads = 4 innodb_read_io_threads = 4 innodb_thread_concurrency = 0 innodb_purge_threads = 1 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 32M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 16M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 interactive_timeout = 28800 wait_timeout = 28800 [mysqldump] quick max_allowed_packet = 500M [myisamchk] key_buffer_size = 16M sort_buffer_size = 8M read_buffer = 4M write_buffer = 4M # Memory management library ################################## jemalloc enabled in MySQL config for process with ID 21815 Using jemalloc from /usr/lib64/libjemalloc.so.1 # The End ####################################################
3.3 使用pt-query-digest查看分析MySQL慢查询日志
[root@study /root]# pt-query-digest /data/mysql/mysql-slow.log # 130ms user time, 20ms system time, 24.38M rss, 203.53M vsz # Current date: Sat Jun 3 10:25:20 2017 # Hostname: study # Files: /data/mysql/mysql-slow.log # Overall: 1 total, 1 unique, 0 QPS, 0x concurrency ______________________ # Time range: all events occurred at 2017-04-22 17:43:09 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 1s 1s 1s 1s 1s 0 1s # Lock time 42us 42us 42us 42us 42us 0 42us # Rows sent 0 0 0 0 0 0 0 # Rows examine 8 8 8 8 8 0 8 # Query size 92 92 92 92 92 0 92 # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============= ===== ====== ===== =============== # 1 0x49F186DD58DC05CC 1.1287 100.0% 1 1.1287 0.00 UPDATE wp_postmeta # Query 1: 0 QPS, 0x concurrency, ID 0x49F186DD58DC05CC at byte 0 ________ # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: all events occurred at 2017-04-22 17:43:09 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 100 1 # Exec time 100 1s 1s 1s 1s 1s 0 1s # Lock time 100 42us 42us 42us 42us 42us 0 42us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 100 8 8 8 8 8 0 8 # Query size 100 92 92 92 92 92 0 92 # String: # Databases chegva # Hosts localhost # Users root # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms # 1s ################################################################ # 10s+ # Tables # SHOW TABLE STATUS FROM `chegva` LIKE 'wp_postmeta'\G # SHOW CREATE TABLE `chegva`.`wp_postmeta`\G UPDATE `wp_postmeta` SET `meta_value` = '27' WHERE `post_id` = 1790 AND `meta_key` = 'views'\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select `meta_value` = '27' from `wp_postmeta` where `post_id` = 1790 AND `meta_key` = 'views'\G
3.4 使用pt-slave-find查看MySQL所有的从库信息
Function
pt-slave-find - Find and print replication hierarchy tree of MySQL slaves.
Usage
pt-slave-find [OPTIONS] [DSN]pt-slave-find finds and prints a hierarchy tree of MySQL slaves.
Examples
pt-slave-find --host master-host
3.5 使用pt-online-schema-change在线更新表（常用）
Function
pt-online-schema-change - ALTER tables without locking them.
Usage
pt-online-schema-change [OPTIONS] DSNpt-online-schema-change alters a table’s structure without blocking reads or writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully.
Add a column to sakila.actor:
pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor
Change sakila.actor to InnoDB, effectively performing OPTIMIZE TABLE in a non-blocking fashion because it is already an InnoDB table:
pt-online-schema-change --alter "ENGINE=InnoDB" D=sakila,t=actor
执行步骤
创建一个和你执行alter表相同的表结构tmp
执行tmp表结构修改，从原表copy数据到修改的表
在原表上创建触发器，将copy过程中产生的数据，更新到新表
copy完成，释放相关资源，再执行rename表
注意：
通过从原表select *插入到临时表，然后rename表来实现更新表，DLL不会影响正常业务，100条以上数据更改建议用此工具来执行,数据量大会造成主从延时，业务低峰更改。
使用示例：
pt-online-schema-change -uroot -h xxxx -p xxxx --alter='add colume vid int' --execute D=dbname,t=tablename
MySQL大数据删除或者转存
1. mysqldump的时候，要去掉drop table语句
2. 删除的时候，按ID分批删除，最好写个脚本放在screen里面。（减小主从延时）
3.7 常用命令
pt工具各个命令的基本功能介绍，可以使用 command --help 来查看每个命令的具体作用和使用方法；
工具类别 | 工具命令 | 工具作用 | 备注 |
开发类 | pt-duplicate-key-checker | 列出并删除重复的索引和外键 | |
pt-online-schema-change | 在线修改表结构 | ||
pt-query-advisor | 分析查询语句，并给出建议，有bug | 已废弃 | |
pt-show-grants | 规范化和打印权限 | ||
pt-upgrade | 在多个服务器上执行查询，并比较不同 | ||
性能类 | pt-index-usage | 分析日志中索引使用情况，并出报告 | |
pt-pmp | 为查询结果跟踪，并汇总跟踪结果 | ||
pt-visual-explain | 格式化执行计划 | ||
pt-table-usage | 分析日志中查询并分析表使用情况 | pt 2.2新增命令 | |
配置类 | pt-config-diff | 比较配置文件和参数 | |
pt-mysql-summary | 对mysql配置和status进行汇总 | ||
pt-variable-advisor | 分析参数，并提出建议 | ||
监控类 | pt-deadlock-logger | 提取和记录mysql死锁信息 | |
pt-fk-error-logger | 提取和记录外键信息 | ||
pt-mext | 并行查看status样本信息 | ||
pt-query-digest | 分析查询日志，并产生报告 | 常用命令 | |
pt-trend | 按照时间段读取slow日志信息 | 已废弃 | |
复制类 | pt-heartbeat | 监控mysql复制延迟 | |
pt-slave-delay | 设定从落后主的时间 | ||
pt-slave-find | 查找和打印所有mysql复制层级关系 | ||
pt-slave-restart | 监控salve错误，并尝试重启salve | ||
pt-table-checksum | 校验主从复制一致性 | ||
pt-table-sync | 高效同步表数据 | ||
系统类 | pt-diskstats | 查看系统磁盘状态 | |
pt-fifo-split | 模拟切割文件并输出 | ||
pt-summary | 收集和显示系统概况 | ||
pt-stalk | 出现问题时，收集诊断数据 | ||
pt-sift | 浏览由pt-stalk创建的文件 | pt 2.2新增命令 | |
pt-ioprofile | 查询进程IO并打印一个IO活动表 | pt 2.2新增命令 | |
实用类 | pt-archiver | 将表数据归档到另一个表或文件中 | |
pt-find | 查找表并执行命令 | ||
pt-kill | Kill掉符合条件的sql | 常用命令 | |
pt-align | 对齐其他工具的输出 | pt 2.2新增命令 | |
pt-fingerprint | 将查询转成密文 | pt 2.2新增命令 |
官方文档：
参考：
https://www.percona.com/doc/percona-toolkit/LATEST/index.html
