mysql数据库cpu使用情况查询_看U看云服务 - 数据库MySQL案例：一次单核CPU占用过高问题的处理，数据库CPU占用100%...

最近有客户反响，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设置的衔接