Optimizing MySQL Configuration |优化MySQL配置(二)

2019独角兽企业重金招聘Python工程师标准>>>

这里推荐了两个工具 mysqltuner  pt-variable-advisor 这两个工具网上的详解很多，自行百度谷歌 借助 MySQLTuner 优化 MySQL 性能 pt-variable-advisor

官方的一个自动生成mysql配置文件的工具

议程 MySQL 配置文件优化基础知识 配置MySQL的工具 介绍部分重要的变量选项

现在我们来看下配置选项 不同类别的配置选项 常规选项 MyISAM的选项 InnoDB的选项 可见性（应该理解为UI可视化的配置）和日志选项

获取状态变量 我们针对 SHOW GLOBAL STATUS 输出做了多样化阐述 Percona Toolkit工具集里面的 pt-mext 对你很有帮助 pt-mext -r --mysqladmin ext -i100 -c4

常规选项 max_connections 链接的允许（应该翻译成合理）范围是多少 观察 max_used_connections 状态值 thread_cache 使用cache来防止过度的（短链接[长连接不明显]）线程创建（带来的性能消耗） 50-100是个不错的范围。关注threads_created状态值 table_cache/table_open_cache mariadb官方 mysql官方 缓存打开表的实例（缓存的是文件描述符） 单个表可以有多个打开文件描述符 观察 opened_tables 状态值（如果发现open_tables等于table_open_cache，并且opened_tables在不断增长，那么就需要增加table_open_cache的值，但如果table_open_cache设置成很大，可能会造成文件描述符不足，造成性能不稳定或者连接失败） 从4096开始 MySQL只在需要的地方使用

常规选项 open_files_limit MyISAM 表require 最多2个文件句柄 每个连接都是文件句柄（file handle，可以理解为用来方便控制file[文件]的东西,这玩意是开山祖师爷们的神翻译，和socket翻译成套字一样逆天） 在大多数系统里面设置成65535是安全的 table_definition_cache 缓存表定义（CREATE TABLE） 每个表只能有一个记录 观察 opened_table_definitions 设置表的数量 + 10% 除非表数量50K+

常规选项 back_log 如果每秒有很多connection连接过来，需要调整 2048是合理的值 max_allowed_packet 限制查询的最大大小 限制内部字符串变量的大小 16MB是个不错的大小 max_connect_errors 防止密码暴力破解 可引起“Host Blocked” 错误信息 值在1000000范围可以接受

常规选项 skip_name_resolve 避免链接的时候查找DNS。这样更快和安全 不要使用主机名在 GRANTs（授权语句） old_passwords 不应该启用。将导致不安全的密码散列被使用。

常规选项 log_bin 启用可以用来做主从复制和定点还原 设置“mysql-bin”来避免默认命名 sync_binlog 使Binlog持久化。如果有 RAID带BBU或者Flash，设置成1 这可能成为磁盘慢的真正性能杀手 expire_log_days 在这个天数后清除旧的二进制日志 14（2周）配合周备份是很好的一个值

常规选项 tmp_table_size max_heap_table_size 通常设置为相同的值（基于工作负载） 留意Created_tmp_disk_tables 状态值 （这里有叶金荣的一篇文章） 当心任意大小的BLOB/TEXT字段引起磁盘临时表生成 query_cache_size 只有当经过测试提供了显著的收益时，才启用查询缓存（MYSQL的查询缓存用于缓存select查询结果，并在下次接收到同样的查询请求时，不再执行实际查询处理而直接返回结果，有这样的查询缓存能提高查询的速度，使查询性能得到优化，前提条件是你有大量的相同或相似的查询，而很少改变表里的数据，否则没有必要使用此功能。可以通过Qcache_lowmem_prunes变量的值来检查是否当前的值满足你目前系统的负载。注意：如果你查询的表更新比较频繁，而且很少有相同的查询，最好不要使用查询缓存。） 常引起stalls（没弄懂这里代表什么）和争用 不要设置超过512MB

常规选项 sort_buffer_size 在内存里面作为排序缓存区 观察 sort_merge_passes(sort_merge_passes 包括两步。mysql 首先会尝试在内存中做排序，使用的内存大小由系统变量 sort_buffer_size 决定，如果它的大小不够把所有的记录都读到内存中，mysql 就会把每次在内存中排序的结果存到临时文件中，等 mysql 找到所有记录之后，再把临时文件中的记录做一次排序。这再次排序就会增加 sort_merge_passes。实际上，mysql 会用另一个临时文件来存再次排序的结果，所以通常会看到 sort_merge_passes 增加的数值是建临时文件数的两倍) 考虑在大的查询会话里面增大 值在1MB周围是一个不错的默认值 较大值在小查询会降低性能 join_buffer_size 对没有索引的表连接提高性能 能获取到关联主键在每个表连接更好 8MB是一个合理的值 default_storage_engine 如果建表的时候未指定，则使用此引擎表

常规选项 read_rnd_buffer_size 用于在排序中读取行的缓冲区 指定最大值 通常设置16MB左右的值 不要和 read_buffer_size 混淆 Tmpdir 指定临时目录的位置 tmpfs是常不错的选择，除非需要非常大的临时空间。 tmpdir=/dev/shm（/dev/shm/是linux下一个目录，/dev/shm目录不在磁盘上，而是在内存里，因此使用linux /dev/shm/的效率非常高，直接写进内存）

---

PPT在这里

转载请标明出处，谢谢

转载于:https://my.oschina.net/u/778602/blog/719295