CentOS6.2_x86_64+mysql-5.5.21数据库出现严重的性能问题，CPU飙升700%，内存占用4G

龍行天下

CentOS6.2_x86_64+mysql-5.5.21数据库出现严重的性能问题，CPU飙升700%，内存占用4G.

大家好：
我的数据库最近CPU有时候会飙升到700%，内存占用却并不多。服务器的配置是，16核心，32G内存。操作系统：centos6.2_x86_64,mysql-5.5.21版本。硬盘IO 15000转\s。开启服务器的慢查询，想抓取消耗时间的SQL，但是，其中慢查询相关参数我配置如下：
slow_query_log = 1
log-queries-not-using-indexes = 1
long_query_time = 5
slow_launch_time = 5
slow_query_log_file = mysql-slow.log
重启数据库后，在mysql-slow.log日志里，看到好多1s以内的查询也记录进去了。没有发现有5s以上的记录。

网上查询都说是由这两个参数long_query_time = 5 ，slow_launch_time = 5来控制慢查询的时间设定的，但是，为什么我的数据库里设置后，却不起作用呢？
现在，是5s以上查询日志里一个也没有，反倒是1s左右的有N多。

有人说，mysql-5.5版本以后，支持毫秒级别的慢查询监控了。那我这里的设置，数据库系统会不会默认当成5ms了呢？本人接触数据库时间不长，好多方面比较菜一些。
还请有知道的大虾们，能指教一二！非常感谢！

在这里有一个疑问，现在，数据库在5.5版本以后，
据说，long_query_time = 5
slow_launch_time = 5
这两个参数支持毫秒级的了，那里上面配置的参数，系统默认会认为是超过5ms的查询都记录到了日志中呢？请大虾指教？谢谢

龍行天下

数据库使用的是INNODB的引擎，下面是我的数据库的配置文件：

[client]
port            = 3306
socket          = /data/mysql-3306/mysql.sock
[mysqld]
port            = 3306
socket          = /data/mysql-3306/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 4M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 32
wait_timeout = 172800
interactive-timeout = 172800
max_connections=5000
max_connect_errors=100000
slow_query_log = 1
log-queries-not-using-indexes = 1
long_query_time = 5
slow_launch_time = 5
slow_query_log_file = mysql-slow.log
skip-host-cache
skip-name-resolve
server-id = 1
log-bin= mysql-bin
innodb_data_home_dir = /data/mysql-3306/dbdata
innodb_data_file_path = ibdata1:2000M;ibdata2:100M:autoextend
innodb_log_group_home_dir = /data/mysql-3306/dbdata
innodb_buffer_pool_size = 10240M
innodb_additional_mem_pool_size = 50M
innodb_log_file_size = 512M
innodb_log_buffer_size = 20M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

大家帮忙分析看一下，我的配置是否存在不合理的地方呢？不胜感谢！

龍行天下

查看slow日志，如下：
# cat /data/mysql-3306/data/mysql-slow.log|grep Query_time|sort -rn|more

# Query_time: 2.217110  Lock_time: 0.000138 Rows_sent: 71  Rows_examined: 403624
# Query_time: 1.834154  Lock_time: 0.000120 Rows_sent: 19  Rows_examined: 416850
# Query_time: 1.776122  Lock_time: 0.000158 Rows_sent: 19  Rows_examined: 416850
# Query_time: 1.499109  Lock_time: 0.000120 Rows_sent: 69  Rows_examined: 414278
# Query_time: 1.466662  Lock_time: 0.000060 Rows_sent: 5  Rows_examined: 341737
# Query_time: 1.455110  Lock_time: 0.000042 Rows_sent: 5  Rows_examined: 341737
# Query_time: 1.443143  Lock_time: 0.000042 Rows_sent: 5  Rows_examined: 341737
# Query_time: 1.426235  Lock_time: 0.000091 Rows_sent: 10  Rows_examined: 416891
# Query_time: 1.425995  Lock_time: 0.000051 Rows_sent: 5  Rows_examined: 341737
# Query_time: 1.393112  Lock_time: 0.000063 Rows_sent: 5  Rows_examined: 341737
# Query_time: 1.370813  Lock_time: 0.000044 Rows_sent: 5  Rows_examined: 341737
# Query_time: 1.360219  Lock_time: 0.000063 Rows_sent: 5  Rows_examined: 341737
# Query_time: 1.352684  Lock_time: 0.000069 Rows_sent: 5  Rows_examined: 341737
# Query_time: 1.349179  Lock_time: 0.000062 Rows_sent: 5  Rows_examined: 341737
# Query_time: 1.336438  Lock_time: 0.000129 Rows_sent: 32  Rows_examined: 418371
# Query_time: 1.323875  Lock_time: 0.000136 Rows_sent: 40  Rows_examined: 448992
# Query_time: 1.300740  Lock_time: 0.000210 Rows_sent: 69  Rows_examined: 414140
# Query_time: 1.296301  Lock_time: 0.000041 Rows_sent: 5  Rows_examined: 341303
# Query_time: 1.288829  Lock_time: 0.000055 Rows_sent: 5  Rows_examined: 341737
# Query_time: 1.286062  Lock_time: 0.000272 Rows_sent: 5  Rows_examined: 341737
# Query_time: 1.275065  Lock_time: 0.000042 Rows_sent: 5  Rows_examined: 341737
# Query_time: 1.272895  Lock_time: 0.000039 Rows_sent: 5  Rows_examined: 341119
# Query_time: 1.268689  Lock_time: 0.000110 Rows_sent: 76  Rows_examined: 409551
# Query_time: 1.263442  Lock_time: 0.000108 Rows_sent: 0  Rows_examined: 418589
# Query_time: 1.258622  Lock_time: 0.000039 Rows_sent: 5  Rows_examined: 341737
# Query_time: 1.254235  Lock_time: 0.000053 Rows_sent: 5  Rows_examined: 341737
# Query_time: 1.249714  Lock_time: 0.000041 Rows_sent: 5  Rows_examined: 341737
# Query_time: 1.244553  Lock_time: 0.000043 Rows_sent: 5  Rows_examined: 341737
# Query_time: 1.239757  Lock_time: 0.000040 Rows_sent: 5  Rows_examined: 341737
# Query_time: 1.234983  Lock_time: 0.000103 Rows_sent: 69  Rows_examined: 396521
# Query_time: 1.227564  Lock_time: 0.000175 Rows_sent: 5  Rows_examined: 341119
# Query_time: 1.223147  Lock_time: 0.000039 Rows_sent: 5  Rows_examined: 341737
# Query_time: 1.222323  Lock_time: 0.000135 Rows_sent: 57  Rows_examined: 416776
# Query_time: 1.221613  Lock_time: 0.000131 Rows_sent: 71  Rows_examined: 403624
# Query_time: 1.212581  Lock_time: 0.000163 Rows_sent: 6  Rows_examined: 448894

龍行天下

mysql占用CPU最近4小时的监控情况如下：



mysql占用CPU最近24小时的监控情况如下：



mysql占用CPU最近一周的监控情况如下：

kider

你的问题可能与你设置的 log-queries-not-using-indexes = 1 此参数有关系，把这个参数先去掉吧。

把这个slow_launch_time = 5参数也去掉吧。

以下3个就可以了。
slow_query_log = 1
slow_query_log_file = mysql.slow
long_query_time = 2

设置成2秒试试。

至于毫秒，5.5是支持的：
The minimum value is 0, and a resolution of microseconds is supported when logging to a file. However, the microseconds part is ignored and only in-teger values are written when logging to tables. The default value is 10. See Section 5.2.5, “The Slow Query Log”.

cumtyux

记录了很多1秒左右的，应该是log-queries-not-using-indexes = 1 这个配置导致。

另long_query_time = 5
slow_launch_time = 5
这两个参数支持毫秒级的了，那里上面配置的参数，系统默认会认为是超过5ms的查询都记录到了日志中呢？请大虾指教？  ---这2个参数的单位是s .你甚至可以动态修改slow query 阀值。 set global long_query_time=0.05    。如果短时间内很多应用访问没有走索引导致很慢的话，cpu瞬间飙高，这个跟MySQL bug没一点关，不要轻易怀疑是MySQL的bug,多半是应用太烂，或者DBA没做好自己的工作

龍行天下

哦，多谢kider和cumtyux 两位的建议。现网的情况，跟cumtyux 说情况很神似。我这边现排查一下。非常感谢！