Linux集群和自动化维1.5.2 利用tuning-primer脚本来调优MySQL数据库-阿里云开发者社区

开发者社区> 华章出版社> 正文

Linux集群和自动化维1.5.2 利用tuning-primer脚本来调优MySQL数据库

简介:

1.5.2 利用tuning-primer脚本来调优MySQL数据库


MySQL在线上稳定运行一段时间后,就可以调用MySQL调优脚本tuning-primer.sh来检查参数的设置是否合理,该脚本的下载地址为:

http://www.day32.com/MySQL/tuning-primer.sh。

该脚本使用“SHOW STATUS LIKE…”和“SHOW VARIABLES LIKE…”命令获得MySQL的相关变量和运行状态。然后根据推荐的调优参数对当前的MySQL数据库进行测试。最后根据不同颜色的标识来提醒用户需要注意的各个参数设置。

当前版本会处理如下这些推荐的参数:

Slow Query Log(慢查询日志)

Max Connections(最大连接数)

Worker Threads(工作线程)

Key Buffer(Key缓冲)

Query Cache(查询缓存)

Sort Buffer(排序缓存)

Joins(连接)

Temp Tables(临时表)

Table(Open & Definition)Cache(表缓存)

Table Locking(表锁定)

Table Scans(read_buffer)(表扫描,读缓冲)

InnoDB Status(InnoDB状态)

笔者之前所在公司的主营业务是CPA电子广告平台,公司规模比较小,所以没有配备专业的MySQL DBA,线上的MySQL数据库(四核CPU)服务器问题比较多,用tuning-primer.sh脚本扫描后发现有如下问题:

MySQL数据库有时连接非常慢,严重时会被拖死。

通过show full processlist命令可以发现大量的“unauthenticated user”连接,数据库肯定每次都要响应,所以速度越来越慢,解决方法其实很简单:在mysql.cnf里添加skip-name-resolve,即不启用DNS反向解析。

发生这种情况的原因其实也很简单,MySQL的认证实际上是user+host的形式(也就是说user可以相同),所以MySQL在处理新连接时会试着去解析客户端连接的IP,启用参数skip-name-resolve后MySQL授权的时候就只能使用纯IP的形式了。

数据库在繁忙期间负载很大,长期达到了13,远远超过了系统平均负载4,这个肯定是不正常的。

通过脚本扫描,发现没有新建thread_cache_size,所以加上了thread_cache_size=256,然后重启数据库,数据库的平均负载一下子降到了5~6。

发现数据库里有张new_cheat_id表,读取很频繁,而且长期处于Sending data状态。

怀疑是磁盘I/O压力过大所致,所以操作如下:

explain SELECT count(new_cheat_id)  FROM new_cheat WHERE account_id = '14348612' AND offer_id = '689'\G;

显示结果如下所示:

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: new_cheat

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 2529529

        Extra: Using where

1 row in set (0.00 sec)

上面出现的这种问题很严重,new_cheat没有建好索引,导致每次都要全表扫描2 529 529行记录,严重消耗了服务器的I/O资源,所以立即建好索引,并用show index命令查看了表索引:

show index from new_cheat;

命令显示结果如下所示:

+-----------+------------+------------+--------------+--------------+-

| Table     | Non_unique | Key_name   | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+-----------+------------+------------+--------------+--------------+-

| new_cheat |          0 | PRIMARY    |            1 | new_cheat_id | A         |     2577704 |     NULL | NULL   |      | BTREE      |         |

| new_cheat |          1 | ip         |            1 | ip           | A         |     1288852 |     NULL | NULL   |      | BTREE      |         |

| new_cheat |          1 | account_id |            1 | account_id   | A         |     1288852 |     NULL | NULL   |      | BTREE      |         |

+-----------+------------+------------+--------------+--------------+-

3 rows in set (0.01 sec)

再来查看explain结果:

explain SELECT count(new_cheat_id)  FROM new_cheat WHERE account_id = '14348612' AND offer_id = '689'\G;

显示结果如下所示:

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: new_cheat

         type: ref

possible_keys: account_id

          key: account_id

      key_len: 4

          ref: const

         rows: 6

        Extra: Using where

1 row in set (0.00 sec)

大家可以发现,加好了索引后,此SQL通过account_id索引直接读取了6条记录(请对比关注rows这行)就获得了查询结果,系统负载由5~6直接降到了3.07~3.66了,这个负载还是能在可接受范围之内的。

MySQL的explain命令可用于SQL语句的查询执行计划(QEP)。这条命令的输出结果能够让我们了解MySQL 优化器是如何执行SQL 语句的。这条命令并没有提供任何调整建议,但它提供的重要信息能够帮助你做出调优决策。

最后要说明一点的是,对于网站来说,MySQL单机优化对整体性能提升的作用毕竟有限,尤其是在MySQL单机写入方面,如果在工作中遇到了那种对MySQL即时写入和读取速度要求很高的场景,建议大家可以多关注分布式的SQL解决方案,例如Hadoop的HBase和AWS的RedShift等分布式SQL系统。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:

华章出版社

官方博客
官网链接