Technology sharing | Percona Toolkit usage scenario sharing

Keywords: Database MySQL

Author: Du Jinyang

More convenient data archiving Pt Archiver

Some time effective data need to be archived and recycled when certain conditions are met. PT archiver tool can help us to archive and recycle data lines quickly

# Archive to another database and delete the corresponding rows in the original table
pt-archiver --source h=,P=5722,u=repl,p=repl,D=sbtest,t=sbtest1,A=utf8  --where "id<=1000" --dest h=,P=5722,u=dba,p=dba,D=sbtest,t=sbtest1,A=utf8

# Archive to other databases without deleting the corresponding rows in the original table
pt-archiver --source h=,P=5722,u=repl,p=repl,D=sbtest,t=sbtest1,A=utf8 --no-delete --where "id<=1000" --dest h=,P=5722,u=dba,p=dba,D=sbtest,t=sbtest1,A=utf8

# Archive to file and delete the corresponding row in the original table
pt-archiver --source h=,P=5722,u=repl,p=repl,D=sbtest,t=sbtest1,A=utf8 --file=/tmp/ --where "id<=1000"
# Archive to file, do not delete the corresponding row in the original table
pt-archiver --source h=,P=5722,u=repl,p=repl,D=sbtest,t=sbtest1,A=utf8 --no-delete --file=/tmp/ --where "id<=1000"

# Import Archive
mysql> load data infile "/tmp/" into table sbtest.sbtest1;
Query OK, 1000 rows affected (0.49 sec)
Records: 1000  Deleted: 0  Skipped: 0  Warnings: 0

Faster configuration vs. Pt config diff

In our daily work, you must have met the following scenarios:

  • There are only one set of MySQL environments:
    • Abnormal behavior, suspected trigger bug
    • Abnormal performance, lower than other environments

In this scenario, our general approach is to first control variables, view the software and hardware configuration, and MySQL parameter configuration. For MySQL parameter configuration comparison, if we compare manually, we will only focus on some key parameters, but lack the comparison in overall details. Here we recommend Pt config diff, a tool in Percona Toolkit

# Specify DSN and compare all runtime parameters
# Specify -- report width 200 to prevent some parameters from being truncated if they are too long
[root@172-20-134-1 /]#  pt-config-diff h=,P=5722,u=repl,p=repl  h=,P=5722,u=dba,p=dba --report-width 200
4 config differences
Variable                   172-20-134-1                             172-20-134-3
========================== ======================================== ========================================
general_log_file           /data/mysql/data/5.7.22/172-20-134-1.log /data/mysql/data/5.7.22/172-20-134-3.log
gtid_executed              234303e2-20cb-11ea-a5a3-0242ac148601:1   2348904f-20cb-11ea-a565-0242ac148603:1
hostname                   172-20-134-1                             172-20-134-3
server_uuid                234303e2-20cb-11ea-a5a3-0242ac148601     2348904f-20cb-11ea-a565-0242ac148603

# Specify profile, compare profile differences
[root@172-20-134-1 /]# pt-config-diff /data/mysql/etc/5.7.22.cnf /tmp/5.7.22.cnf --report-width 200
2 config differences
Variable                  /data/mysql/etc/5.7.22.cnf /tmp/5.7.22.cnf
========================= ========================== ===============
max_allowed_packet        16777216                   67108864
relay_log_recovery        1                          0

More accurate replication delay Pt heartbeat

In MySQL, replication latency can be understood as two parts:

  • 1. BINLOG has been generated in the master database, but it has not been sent to the slave database -- we call it log delay here
  • 2. BINLOG has been received from the library, but the application has not been completed -- we call it application delay here

MySQL's native way to view replication latency is: show slave status\G, the second "behind" master. This observation technique can only observe the application delay. In asynchronous replication or degraded semi synchronous replication, the error is large, which can not accurately reflect the overall replication delay.

PT heartbeat provides a more accurate observation method, and the brief logic is as follows:

  • 1. Insert on the Master: insert into database.heartbeat (master_now) values(NOW())
  • 2. The change of database.heartbeat will follow the master-slave replication to the slave database
  • 3. System current time - time in slave database table = actual replication delay from the database

Use as follows

# Turn on cycle insert timestamp to Master, interval is the interval of each insert
pt-heartbeat -D delay_checker --create-table --interval=5 --update -h,P=5722,u=repl,p=repl

# Check replication latency from specified library
pt-heartbeat -D delay_checker  --check,P=5722,u=repl,p=repl
# PS: the above command will use the current system time to subtract the time in the delay_checker.heartbeat table
# If it is executed on the slave database, you need to ensure that the system time of the slave database is consistent with that of the master database. Otherwise, it will cause delay calculation errors

Simpler parameter configuration suggestions Pt variable Advisor

toolkit contains a simple MySQL parameter optimizer, which can make simple optimization suggestions for parameter configuration

[root@172-20-134-1 /]# pt-variable-advisor h=,P=5722,u=repl,p=repl 
# WARN delay_key_write: MyISAM index blocks are never flushed until necessary.
# 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 max_binlog_size: The max_binlog_size is smaller than the default of 1GB.
# NOTE port: The server is listening on a non-default port.
# 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.
# WARN expire_logs_days: Binary logs are enabled, but automatic purging is not enabled.
# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.
# WARN log_output: Directing log output to tables has a high performance impact.
# WARN myisam_recover_options: myisam_recover_options should be set to some value such as BACKUP,FORCE to ensure that table corruption is noticed.

Easier to use debugging tool Pt PMP

In some cases, we will definitely encounter some failures that can not be found from the log, as well as from the status command, and need to drill down to the program logic level. Or we need to recover the failure database immediately by unconventional means, but we want to keep enough failure information. In order to avoid the headache of the problem of recurrence afterwards, PT PMP is the tool to help us in this scenario. It will use gdb to print the stack information of mysqld and merge the thread stacks with the same call chain. Stack merging is very helpful for MySQL, which is a multithreaded application. It will save us a lot of time.

# pt-pmp --binary /path/to/bin/mysqld --pid 11788 
Sat Dec 21 23:14:06 CST 2019
#   The first column is the number of threads and the second column is the thread call stack information
    180 poll(,vio_io_wait(viosocket.c:797),vio_socket_io_wait(viosocket.c:88),vio_read(viosocket.c:143),net_read_raw_loop(,net_read_packet_header(,net_read_packet(,my_net_read(,Protocol_classic::read_packet(,Protocol_classic::get_command(,do_command(,handle_connection(,pfs_spawn_thread(,start_thread(,clone(
     57 pthread_cond_wait,native_cond_wait(thr_cond.h:147),my_cond_wait(thr_cond.h:147),inline_mysql_cond_wait(thr_cond.h:147),Per_thread_connection_handler::block_until_new_connection(thr_cond.h:147),handle_connection(,pfs_spawn_thread(,start_thread(,clone(
     30 __io_getevents_0_4(,LinuxAIOHandler::collect(,LinuxAIOHandler::poll(,os_aio_linux_handler(,os_aio_handler(,fil_aio_wait(,io_handler_thread(,start_thread(,clone(
      9 pthread_cond_wait,wait(,os_event::wait_low(,srv_worker_thread(,start_thread(,clone(
      9 pthread_cond_wait,wait(,os_event::wait_low(,buf_flush_page_cleaner_worker(,start_thread(,clone(
      3 sigwait(,signal_hand(,pfs_spawn_thread(,start_thread(,clone(
      3 sigwaitinfo(,timer_notify_thread_func(posix_timers.c:89),pfs_spawn_thread(,start_thread(,clone(
      3 pthread_cond_wait,wait(,os_event::wait_low(,srv_purge_coordinator_suspend(,srv_purge_coordinator_thread(,start_thread(,clone(
      3 pthread_cond_wait,wait(,os_event::wait_low(,buf_resize_thread(,start_thread(,clone(
      3 pthread_cond_wait,wait(,os_event::wait_low(,buf_dump_thread(,start_thread(,clone(
      3 pthread_cond_wait,native_cond_wait(thr_cond.h:147),my_cond_wait(thr_cond.h:147),inline_mysql_cond_wait(thr_cond.h:147),compress_gtid_table(thr_cond.h:147),pfs_spawn_thread(,start_thread(,clone(
      3 pthread_cond_timedwait,os_event::timed_wait(,os_event::wait_time_low(,srv_monitor_thread(,start_thread(,clone(
      3 pthread_cond_timedwait,os_event::timed_wait(,os_event::wait_time_low(,srv_error_monitor_thread(,start_thread(,clone(
      3 pthread_cond_timedwait,os_event::timed_wait(,os_event::wait_time_low(,pc_sleep_if_needed(,buf_flush_page_cleaner_coordinator(,start_thread(,clone(
      3 pthread_cond_timedwait,os_event::timed_wait(,os_event::wait_time_low(,lock_wait_timeout_thread(,start_thread(,clone(
      3 pthread_cond_timedwait,os_event::timed_wait(,os_event::wait_time_low(,ib_wqueue_timedwait(,fts_optimize_thread(,start_thread(,clone(
      3 pthread_cond_timedwait,os_event::timed_wait(,os_event::wait_time_low(,dict_stats_thread(,start_thread(,clone(
      3 poll(,Mysqld_socket_listener::listen_for_connection_event(,connection_event_loop(connection_acceptor.h:73),mysqld_main(connection_acceptor.h:73),__libc_start_main(,_start
      3 nanosleep(,os_thread_sleep(,srv_master_sleep(,srv_master_thread(,start_thread(,clone(


Percona Toolkit integrates a lot of tools that are very useful for DBA s, and reasonable use of them will reduce part of the workload in some scenarios. I hope this sharing can help you save time and improve your quality of life.

Appendix: quick installation of Percona Toolkit

tar -xvf percona-toolkit-3.1.0_x86_64.tar.gz -C /usr/local

Posted by busnut on Thu, 09 Jan 2020 22:07:42 -0800