1.pt-variable-advisor pt-variable-advisor 可以分析MySQL变量并就可能出现的问题提出建议。
pt-variable-advisor localhost -u root -p root123 --socket=/opt/mysql/mysql.sock # WARN delay_key_write: MyISAM index blocks are never flushed until necessary. # WARN innodb_lock_wait_timeout: This option has an unusually long value, which can cause system overload if locks are not being released. # NOTE max_connect_errors: max_connect_errors should probably be set as large as your platform allows. # NOTE read_buffer_size-1: The read_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it. # NOTE read_rnd_buffer_size-1: The read_rnd_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it. # WARN read_rnd_buffer_size-2: The read_rnd_buffer_size variable should not be larger than 4M. # 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. # NOTE sort_buffer_size-2: 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. # NOTE innodb_flush_method: Most production database servers that use InnoDB should set innodb_flush_method to O_DIRECT to avoid double-buffering, unless the I/O system is very low performance. # WARN myisam_recover_options: myisam_recover_options should be set to some value such as BACKUP,FORCE to ensure that table corruption is noticed.
2. pt-query-digest pt-query-digest 主要功能是从日志、进程列表和tcpdump分析MySQL查询。
常见用法分析
1）直接分析慢查询文件:
pt-query-digest /var/lib/mysql/slowtest-slow.log > slow_report.log
2）分析最近12小时内的查询：
pt-query-digest --since=12h /var/lib/mysql/slowtest-slow.log > slow_report2.log
3）分析指定时间范围内的查询：
pt-query-digest /var/lib/mysql/slowtest-slow.log --since '2017-01-07 09:30:00' --until '2017-01-07 10:00:00'> > slow_report3.log
4）分析指含有select语句的慢查询
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /var/lib/mysql/slowtest-slow.log> slow_report4.log
5）针对某个用户的慢查询
pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' /var/lib/mysql/slowtest-slow.log> slow_report5.log
6）查询所有所有的全表扫描或full join的慢查询
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' /var/lib/mysql/slowtest-slow.log> slow_report6.log
