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

<b>1.5.2　利用tuning-primer脚本来调优mysql数据库</b>

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 &amp; 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      |         |

|          1 | ip         |            1 | ip           | a         |    

1288852 |     null | null   |     

|          1 | account_id |            1 | account_id   | a        

|     1288852 |     null | null   |     

3 rows in set

(0.01 sec)

再来查看explain结果：

         type: ref

account_id

          key: account_id

      key_len: 4

          ref: const

         rows: 6

大家可以发现，加好了索引后，此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系统。