最近有客户反响,top的例行检查结果中,一个CPU的占用不时是100%。实践上现场有4个CPU,而且这个效劳器是mysql专属效劳器。
我的第一反响是io_thread一类的参数设置有问题,检查以后发现read和write的thread设置都是4,这和CPU数分歧,因而能够判定这并不是单颗CPU占用过高的问题。
接下来需求确认MySQL究竟有没有应用到多核CPU,这个时分需求的工具叫做pidstat,命令如下:
pidstat -u -t -p 18158
得到的结果如下图所示:
能够看出其实mysqld是能够应用到多核CPU的,那么此时能够得到一个推断:
某个CPU上做的事情太占资源了
普通这种最占资源的工作一定会在INNODB_TRX里留下一些端倪,因而检查一下:
反好的检查TRX,发现mysql在不停的执行这个SQL,只是where条件里的值发作了变化,至此我能够推断出业务应该是写了一个循环来遍历一个list,然后对每个item都执行update操作。
应该是写了这么一段代码在处置问题:
for (item in list) {
update_db(item);
}
检查这个表并没有索引,给where条件中的列加上索引,再次检查CPU的占用,发现往常的占用曾经降低到了16%左右,固然还是很高,但是曾经实践上处置了该问题。
这里我有点慨叹,DBA并不是你会写SQL就能够干的,DBA实践上是运维人员的一种,运维要控制几种技艺恐怕只需运维小同伴们分明,其实技术难度并不比写Java代码低。DBA控制几种检查询题的伎俩,DBA面对问题时能不能第一时间找准方向,这都是阅历和功力的展示。
处置原理:
MySQL负载居高不下,假如翻开了慢查询日志功用,最好的办法就是针对慢查询日志里执行慢的sql语句中止优化,假如sql语句用了大量的group by等语句,union分离查询等肯定会将mysql的占用率进步。所以就需求优化sql语句
除了优化sql语句外,也能够做一些配置上的优化。在mysql中运转show proceslist;呈现下面回显结果:1.查询有大量的Copying to tmp table on disk状态明显是由于暂时表过大招致mysql将暂时表写入硬盘影响了整体性能。
1.Mysql中tmp_table_size的默许值仅为16MB,在当前的状况下显然是不够用的。mysql> show variables like "%tmp%";+-------------------+----------+| Variable_name | Value |+-------------------+----------+| max_tmp_tables | 32 || slave_load_tmpdir | /tmp || tmp_table_size | 16777216 || tmpdir | /tmp |+-------------------+----------+4 rows in set (0.00 sec)
处置办法:调整暂时表大小1)进mysql终端命令修正,加上global,下次进mysql就会生效mysql> set global tmp_table_size=33554432;Query OK, 0 rows affected (0.00 sec)
再次登陆mysqlmysql> show variables like "%tmp%";+-------------------+----------+| Variable_name | Value |+-------------------+----------+| max_tmp_tables | 32 || slave_load_tmpdir | /tmp || tmp_table_size | 33554432 || tmpdir | /tmp |+-------------------+----------+4 rows in set (0.01 sec)
2)my.cnf配置文件修正[root@www ~]# vim my.cnf.....tmp_table_size = 32M
重启mysql[root@www ~]# /etc/init.d/mysqld restart
2.show processlist;命令的输出结果显现了有哪些线程在运转,能够辅佐辨认出有问题的查询语句。比如下面结果:Id User Host db Command Time State Info 207 root 192.168.1.25:51718 mytest Sleep 5 NULL 先简单说一下各列的含义和用处,第一列,id,不用说了吧,一个标识,你要kill一个语句的时分很有用。user列,显现单前用户,假如不是root,这个命令就只显现你权限范围内的sql语句。host列,显现这个语句是从哪个ip的哪个端口上发出的。呵呵,能够用来追踪出问题语句的用户。db列,显现这个进程目前衔接的是哪个数据库 。command列,显现当前衔接的执行的命令,普通就是休眠(sleep),查询(query),衔接(connect)。time列,此这个状态持续的时间,单位是秒。state列,显现运用当前衔接的sql语句的状态,很重要的列,后续会有一切的状态的描画,请留意,state只是语句执行中的某一个状态,一个sql语句,已查询为例,可能需求经过copying to tmp table,Sorting result,Sending data等状态才能够完成,info列,显现这个sql语句,由于长度有限,所以长的sql语句就显现不全,但是一个判别问题语句的重要依据。常见问题:普通是睡眠衔接过多,严重耗费mysql效劳器资源(主要是cpu, 内存),并可能招致mysql解体。
处置办法 :在mysql的配置my.cnf文件中,有一项wait_timeout参数设置.即可设置睡眠衔接超时秒数,假如某个衔接超时,会被mysql自然终止。 wait_timeout过大有弊端,其表现就是MySQL里大量的SLEEP进程无法及时释放,拖累系统性能,不过也不能把这个指设置的过小,否则你可能会遭遇到“MySQL has gone away”之类的问题。通常来说,把wait_timeout设置为10小时是个不错的选择,但某些状况下可能也会出问题,比如说有一个CRON脚本,其中两次SQL查询的距离时间大于10秒的话,那么这个设置就有问题了(当然,这也不是不能处置的问题,你能够在程序里时不时mysql_ping一下,以便效劳器知道你还活着,重新计算wait_timeout时间):
MySQL效劳器默许的“wait_timeout”是28800秒即8小时,意味着假如一个衔接的闲暇时间超越8个小时,MySQL将自动断开该衔接。但是衔接池却以为该衔接还是有效的(由于并未校验衔接的有效性),当应用申请运用该衔接时,就会招致下面的报错:The last packet successfully received from the server was 596,688 milliseconds ago.mysql> show variables like 'wait_timeout';+---------------+-------+| Variable_name | Value |+---------------+-------+| wait_timeout | 28800 |+---------------+-------+1 row in set (0.00 sec)
28800seconds,也就是8小时。假如在wait_timeout秒期间内,数据库衔接(java.sql.Connection)不时处于等候状态,mysql就将该衔接关闭。这时,你的Java应用的衔接池依然合法地持有该衔接的援用。当用该衔接来中止数据库操作时,就碰到上述错误。能够将mysql全局变量wait_timeout的缺省值改大。查看mysql手册,发现对wait_timeout的最大值分别是24天/365天(windows/linux)。
比如将其改成30天mysql> set global wait_timeout=124800;Query OK, 0 rows affected (0.00 sec)
小结:
Mysql占用CPU过高的时分,该从哪些方面下手中止优化?占用CPU过高,能够做如下思索:1)普通来讲,扫除高并发的要素,还是要找到招致你CPU过高的哪几条在执行的SQL,show processlist语句,查找负荷最重的SQL语句,优化该SQL,比如恰当树立某字段的索引;2)翻开慢查询日志,将那些执行时间过长且占用资源过多的SQL拿来中止explain剖析,招致CPU过高,多数是GroupBy、OrderBy排序问题所招致,然后慢慢中止优化改进。比如优化insert语句、优化group by语句、优化order by语句、优化join语句等等;3)思索定时优化文件及索引;4)定期剖析表,运用optimize table;5)优化数据库对象;6)思索能否是锁问题;7)调整一些MySQL Server参数,比如key_buffer_size、table_cache、innodb_buffer_pool_size、innodb_log_file_size等等;8)假如数据量过大,能够思索运用MySQL集群或者搭建高可用环境。9)可能由于内存latch(泄露)招致数据库CPU高10)在多用户高并发的状况下,任何系统都会hold不住的,所以,运用缓存是必需的,运用memcached或者redis缓存都能够;11)看看tmp_table_size大小能否偏小,假如允许,恰当的增大一点;12)假如max_heap_table_size配置的过小,增大一点;13)mysql的sql语句睡眠衔接超时时间设置问题(wait_timeout)14)运用show processlist查看mysql衔接数,看看能否超越了mysql设置的衔接