Download address

www.percona.com/downloads/p…

introduce

Percona – Toolkit is a toolkit with many commands. For example, 👇

  1. pt-online-schema-change

    You can organize table structures online, collect fragments, and add fields and indexes to large tables. Avoid operations that lock tables and block read and write operations. For MySQL 5.7, you can do DDL online without using this command.

./pt-online-schema-change --user=root --password=123456 
--host=localhost  --alter="ADD COLUMN city_id INT" D=test,t=su --execute
Copy the code
  1. Pt-query-digest a tool that analyzes slow mysql queries, including binlog, General log, and slowlog, as well as SHOW PROCESSLIST and tcpdump data.
./pt-query-digest --since=24h /data/mysql/slow.log > 1.log
Copy the code

  1. Pt-heartbeat monitors primary-slave latency. Monitor how much time the slave is behind the master.

  2. Pt-table-checksum Checks the primary and secondary replication consistency

  3. Pt-slave-restart Monitors master/slave errors and tries to restart the MySQL master/slave

  4. Pt-ioprofile locates I/O problems based on I/O throughput.

Online documentation: www.percona.com/doc/percona…

pt-kill

This section focuses on the Pt-kill tool. In production environments we often encounter situations where database performance is poor and you need to kill all sessions at once or the database will hang. We can start by looking for the output of show ProcessList to kill the session, but it’s a bit more cumbersome. Pt-kill solves the kill session problem for us. Detailed documentation: www.percona.com/doc/percona…

  1. Check every 10 seconds and kill processes with Query
# Print only - check every 10 seconds, Pt-kill --host=127.0.0.1 --port=3306 --user=root --password=rootpwd --match-db='db222' --match-command="Query" --busy-time 30 --victims all --interval 10 --daemonize --print --log=/ TMP /1.log --host=127.0.0.1 --port=3306 --user=root --password= rootPwd --match-db='db222' --match-command="Query" --busy-time 30 --victims all --interval 10 --daemonize --kill --log=/tmp/kill.logCopy the code
  1. Example Check and kill the session whose select is greater than 30s
Pt-kill --host=127.0.0.1 --port=3306 --user=root --password=rootpwd --match-db='db222' --match-info "select|SELECT" --busy-time 30 --victims all --interval 10 --daemonize --print --log=/tmp/pt_select.log # Execute the kill operation - Check and kill the session that is longer than 30s pt-kill --host=127.0.0.1 --port=3306 --user=root --password= rootPWd --match-db='db222' --match-info "select|SELECT" --busy-time 30 --victims all --interval 10 --daemonize --kill --log=/tmp/pt_select_kill.logCopy the code
  1. Example Check the session from an IP address
Pt-kill --host=127.0.0.1 --port=3306 --user=root --password= rootPwd --match-db='db222' --match-host "192.168.65.129" --busy-time 30 -- Victims all --interval 10 --daemonize --print --log=/ TMP /pt_select.log # Executing the kill operation - Querying the session from an IP address pt-kill --host=127.0.0.1 --port=3306 --user=root --password= rootPwd --match-db='db222' --match-host "192.168.65.129" --busy-time 30 -- Victims all --interval 10 --daemonize --kill --log=/ TMP /pt_select_kill.logCopy the code
  1. Checks and kills sessions that access a user
Pt-kill --host=127.0.0.1 --port=3306 --user=root --password= rootPwd --match-db='db222' --match-user "U2" --busy-time 30 -- Victims all --interval 10 --daemonize --print --log=/ TMP/pt_self. logCopy the code
  1. Kill SQL running filesort
Pt-kill --host=127.0.0.1 --port=3306 --user=root --password=rootpwd --match-db='db222' --match-command Query --match-state "Sorting result" --run-time 1 --busy-time 30 --victims all --interval 10 --daemonize --print --log=/ TMP /pt_select.log --print --log=/ TMP /pt_select.log --password=rootpwd --match-db='db222' --match-command Query --match-state "Sorting result" --run-time 1 --busy-time 30 --victims all --interval 10 --daemonize --kill --log=/tmp/pt_select_kill.logCopy the code
  1. Kill the SQL that is creating the index
Pt-kill --host=127.0.0.1 --port=3306 --user=root --password= rootPwd --match-db='db222' --match-command Query --match-state "Creating sort index" --run-time 1 --busy-time 30 --victims all --interval 10 --daemonize --print --log=/ TMP /pt_select.log # pt-kill --host=127.0.0.1 --port=3306 --user=root --password=rootpwd --match-db='db222' --match-command Query --match-state "Creating sort index" --run-time 1 --busy-time 30 --victims all --interval 10 --daemonize --kill --log=/tmp/pt_select_kill.logCopy the code