蛙鳜鸡鹳狸猿

蛙鳜鸡鹳狸猿

Percona Toolkit使用之pt-variable-advisor

     pt-variable-advisor的功能是分析MySQL系统变量配置然后对潜在问题提出建议。

     用法如下:

pt-variable-advisor [OPTIONS] [DSN]

     pt-variable-advisor分析系统变量配置然后对潜在问题提出建议。

     ①从localhost获取SHOW VARIABLES。

pt-variable-advisor localhost

     ②从保存于vars.txt的文件中获取SHOW VARIABLES输出。

pt-variable-advisor --source-of-variables vars.txt

     pt-variable-advisor检测“ SHOW VARIABLES ”来查找不好的变量值和设置,判断标准基于若干规则。它会报告匹配到这些规则的变量,因此你可以在自己的MySQL服务器上找到差的配置。

     截止当前的版本,pt-variable-advisor只检测“ SHOW VARIABLES ”,但是其他的输入源,例如“ SHOW STATUS ”和“ SHOW SLAVE STATUS ”,正在计划中。

     有一些pt-variable-advisor用于SHOW VARIABLES检测的规则。每个规则包含三个部分:ID、严重性和描述。

     规则ID是该规则的一个简短的唯一命名。它通常与该规则检测的变量相关。如果变量被若干个规则检测,那么规则ID将被编号,例如“-1”、“-2”、“-N”。

     规则严重性表明该规则与查询匹配的重要性。我们使用NOTE、WARN和CRIT来表示其程度。

     规则描述是一个文本的、人类可读的、当变量匹配该规则时意味着什么的说明。根据生成的报告的冗余程度,你将在描述中看到更多的文本。默认情况下,你只会看到首句,这可以说是规则含义的简述。在更高级别的冗余度下,你将看到附加语句。

     以下为个人本地环境的测试数据。

     首先是简单检测。

root@ubuntu:~# pt-variable-advisor h=192.168.112.129, P=3306, u=root, p=123456
# 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 nece
ssary 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.

     查看具体的解释信息(--verbose)。

root@ubuntu:~# pt-variable-advisor h=192.168.112.129, P=3306, u=root, p=123456 --verbose --verbose
# WARN delay_key_write: MyISAM index blocks are never flushed until necessary.  If there is a server crash, data corruption on MyISAM t
ables can be much worse than usual.
# WARN key_buffer_size: The key buffer size is set to its default value, which is not good  for most production systems.  In a producti
on environment, key_buffer_size should be larger than the default 8MB size.
# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is nece
ssary to change it.
# WARN expire_logs_days: Binary logs are enabled, but automatic purging is not enabled.  If you do not purge binary logs, your disk wil
l fill up.  If you delete binary logs externally to MySQL, you will cause unwanted behaviors. Always ask MySQL to purge obsolete logs, never delete them externally.
# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.  Som
e people prefer to set innodb_file_per_table and allocate a fixed-size file for ibdata1.
# 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.



     参考:

https://www.percona.com/doc/percona-toolkit/LATEST/pt-variable-advisor.html

阅读更多
版权声明:本文为博主原创文章,转载敬请作出引用声明方便相互交流学习! https://blog.csdn.net/sweeper_freedoman/article/details/80695930
个人分类: MySQL
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭