pt-variable-advisor（percona toolkit）

pt-variable-advisor是一款分析参数，并且给出参数设置建议的一款PT工具，基本语法

pt-variable-advisor [OPTIONS] [DSN]

 

如下我们可以获取本地参数的一些建议：

[root@localhost bin]# ./pt-variable-advisor localhost -uroot -p123456
# WARN delay_key_write: MyISAM index blocks are never flushed until necessary.
# WARN key_buffer_size: The key buffer size is set to its default value, which is not good for most production systems.
# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.
# 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 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.

 

可以看到这是一些基本的建议，我们设置max_binlog_size，再次运行：

[root@localhost bin]# ./pt-variable-advisor localhost -uroot -p123456
# WARN delay_key_write: MyISAM index blocks are never flushed until necessary.
# WARN key_buffer_size: The key buffer size is set to its default value, which is not good for most production systems.
# 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 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.

 

发现已经是OK了，我们也可以先生成文件在分析：

[root@localhost bin]# mysql -uroot -p123456 -e'show global variables'>/home/sa/vars.txt
[root@localhost bin]# ./pt-variable-advisor --source-of-variables /home/sa/vars.txt

 

输出结果分为三个等级：

规则的严重性表示该规则与查询匹配的重要性。我们使用NOTE，WARN和CRIT来表示这些级别。不同的参数对应不同的等级，其实我们发现这个工具使用起来意义并不是很大，也不能解决大部分问题，只能检查到我们很明显的错误信息而已。