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=172.20.134.1,P=5722,u=repl,p=repl,D=sbtest,t=sbtest1,A=utf8 --where "id<=1000" --dest h=172.20.134.3,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=172.20.134.1,P=5722,u=repl,p=repl,D=sbtest,t=sbtest1,A=utf8 --no-delete --where "id<=1000" --dest h=172.20.134.3,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=172.20.134.1,P=5722,u=repl,p=repl,D=sbtest,t=sbtest1,A=utf8 --file=/tmp/archive.save --where "id<=1000" # Archive to file, do not delete the corresponding row in the original table pt-archiver --source h=172.20.134.1,P=5722,u=repl,p=repl,D=sbtest,t=sbtest1,A=utf8 --no-delete --file=/tmp/archive.save --where "id<=1000" # Import Archive mysql> load data infile "/tmp/archive.save" 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=172.20.134.1,P=5722,u=repl,p=repl h=172.20.134.3,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 172.20.134.1,P=5722,u=repl,p=repl # Check replication latency from specified library pt-heartbeat -D delay_checker --check 172.20.134.2,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=172.20.134.1,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(libc.so.6),vio_io_wait(viosocket.c:797),vio_socket_io_wait(viosocket.c:88),vio_read(viosocket.c:143),net_read_raw_loop(net_serv.cc:694),net_read_packet_header(net_serv.cc:778),net_read_packet(net_serv.cc:778),my_net_read(net_serv.cc:921),Protocol_classic::read_packet(protocol_classic.cc:815),Protocol_classic::get_command(protocol_classic.cc:972),do_command(sql_parse.cc:971),handle_connection(connection_handler_per_thread.cc:313),pfs_spawn_thread(pfs.cc:2197),start_thread(libpthread.so.0),clone(libc.so.6) 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(connection_handler_per_thread.cc:344),pfs_spawn_thread(pfs.cc:2197),start_thread(libpthread.so.0),clone(libc.so.6) 30 __io_getevents_0_4(libaio.so.1),LinuxAIOHandler::collect(os0file.cc:2513),LinuxAIOHandler::poll(os0file.cc:2673),os_aio_linux_handler(os0file.cc:2729),os_aio_handler(os0file.cc:2729),fil_aio_wait(fil0fil.cc:5862),io_handler_thread(srv0start.cc:319),start_thread(libpthread.so.0),clone(libc.so.6) 9 pthread_cond_wait,wait(os0event.cc:179),os_event::wait_low(os0event.cc:179),srv_worker_thread(srv0srv.cc:2527),start_thread(libpthread.so.0),clone(libc.so.6) 9 pthread_cond_wait,wait(os0event.cc:179),os_event::wait_low(os0event.cc:179),buf_flush_page_cleaner_worker(buf0flu.cc:3507),start_thread(libpthread.so.0),clone(libc.so.6) 3 sigwait(libpthread.so.0),signal_hand(mysqld.cc:2132),pfs_spawn_thread(pfs.cc:2197),start_thread(libpthread.so.0),clone(libc.so.6) 3 sigwaitinfo(libc.so.6),timer_notify_thread_func(posix_timers.c:89),pfs_spawn_thread(pfs.cc:2197),start_thread(libpthread.so.0),clone(libc.so.6) 3 pthread_cond_wait,wait(os0event.cc:179),os_event::wait_low(os0event.cc:179),srv_purge_coordinator_suspend(srv0srv.cc:2683),srv_purge_coordinator_thread(srv0srv.cc:2683),start_thread(libpthread.so.0),clone(libc.so.6) 3 pthread_cond_wait,wait(os0event.cc:179),os_event::wait_low(os0event.cc:179),buf_resize_thread(buf0buf.cc:3027),start_thread(libpthread.so.0),clone(libc.so.6) 3 pthread_cond_wait,wait(os0event.cc:179),os_event::wait_low(os0event.cc:179),buf_dump_thread(buf0dump.cc:792),start_thread(libpthread.so.0),clone(libc.so.6) 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(pfs.cc:2197),start_thread(libpthread.so.0),clone(libc.so.6) 3 pthread_cond_timedwait,os_event::timed_wait(os0event.cc:316),os_event::wait_time_low(os0event.cc:488),srv_monitor_thread(srv0srv.cc:1592),start_thread(libpthread.so.0),clone(libc.so.6) 3 pthread_cond_timedwait,os_event::timed_wait(os0event.cc:316),os_event::wait_time_low(os0event.cc:488),srv_error_monitor_thread(srv0srv.cc:1758),start_thread(libpthread.so.0),clone(libc.so.6) 3 pthread_cond_timedwait,os_event::timed_wait(os0event.cc:316),os_event::wait_time_low(os0event.cc:488),pc_sleep_if_needed(buf0flu.cc:2700),buf_flush_page_cleaner_coordinator(buf0flu.cc:2700),start_thread(libpthread.so.0),clone(libc.so.6) 3 pthread_cond_timedwait,os_event::timed_wait(os0event.cc:316),os_event::wait_time_low(os0event.cc:488),lock_wait_timeout_thread(lock0wait.cc:497),start_thread(libpthread.so.0),clone(libc.so.6) 3 pthread_cond_timedwait,os_event::timed_wait(os0event.cc:316),os_event::wait_time_low(os0event.cc:488),ib_wqueue_timedwait(ut0wqueue.cc:168),fts_optimize_thread(fts0opt.cc:2910),start_thread(libpthread.so.0),clone(libc.so.6) 3 pthread_cond_timedwait,os_event::timed_wait(os0event.cc:316),os_event::wait_time_low(os0event.cc:488),dict_stats_thread(dict0stats_bg.cc:435),start_thread(libpthread.so.0),clone(libc.so.6) 3 poll(libc.so.6),Mysqld_socket_listener::listen_for_connection_event(socket_connection.cc:859),connection_event_loop(connection_acceptor.h:73),mysqld_main(connection_acceptor.h:73),__libc_start_main(libc.so.6),_start 3 nanosleep(libpthread.so.0),os_thread_sleep(os0thread.cc:287),srv_master_sleep(srv0srv.cc:2337),srv_master_thread(srv0srv.cc:2337),start_thread(libpthread.so.0),clone(libc.so.6)
summary
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
wget https://www.percona.com/downloads/percona-toolkit/3.1.0/binary/tarball/percona-toolkit-3.1.0_x86_64.tar.gz tar -xvf percona-toolkit-3.1.0_x86_64.tar.gz -C /usr/local