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系统。

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

相关文章
MySQL 脚本优化工具tuning-primer.sh使用教程说明
今天推荐给大家一个不错的脚本tuning-primer.sh,可以帮助我们去查看一下msyql的运行情况,产生报告和给出一些建议,我们可以根据这些建议,结合mysql服务器的实际情况,对mysql进行优化。
797 0
《懒人Shell脚本》之八——定期备份Mysql数据库表的实现
0.背景 实际开发环境中,前端程序需要在给定时间段内,将数据更新/插入到mysql指定的库表中。随着数据量的增多,基础库表基数的增长,每更新一次都会有5s左右的卡顿。
6 0
power designer pdm生成sql脚本和数据库以及逆向工程
正向工程:PDM--->sql or database 项目中我们经常需要根据PDM生成sql脚本,然后由sql脚本一起执行到数据库中,如果PDM中表比较多,我们一个个去建的话会很费时间,根据需要我们也可以直接将pdm生成到数据库中。 逆向工程:sql or database --->PDM 有时我们也需要根据sql脚本生成pdm图,或者是数据库的最新信息生成到PDM中
1444 0
阿里云服务器Linux系统如何安装MySQL数据库?
我之前写过阿里云服务器ECS如何安装宝塔面板 ,安装完宝塔控制面板之后,里面自带有mysql数据库,非常方便······
2310 0
gdy
C#调用vbs脚本实现Windows版Siri
  最近新加入,把自己一些有意思的小东西分享给大家,我是一个学生,代码写得少,哪里不规范,希望大家见谅。   这事我封装好的一个类,可以直接实例化对象之后,调用"对象.Talk()"方法,让机器读出你传递进去的字符串。
900 0
10059
文章
0
问答
来源圈子
更多
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载