业务高峰期，mysql负载高，连接数过多，导致服务器CPU I/O很大

lvchenotl

首先说下数据库和服务器的配置：
    操作系统：Red Hat Enterprise Linux Server release 5.6
    cpu核数：8
    内存：16G
    数据库版本：5.5.28
    数据库引擎：Innodb，但是其中有一张很小的表为MyISAM

    数据库的最大连接数设置的是1024，在业务高峰期的时候，经常会收到告警短信，报告processlist连接数过高，能达到600-800
这时我登上数据库show processlist查看，几乎都是sleep状态的会话。
    同时服务器的磁盘读写会非常高，而且cpu只有其中2-3个核的I/O会变得很高，以下为部分状况截图：








通过压测发现，磁盘的读写会被一个读进程给堵着，导致个别CPU的核的I/O很高，但是其他核都没有被使用



下面是数据库的配置：
[client]
port                = 3328
socket                = /data/mysql/egame_user/3328/mysql.sock

[mysqld]
user                = mysql
port                = 3328
socket                = /data/mysql/egame_user/3328/mysql.socki
server-id = 33281
skip-external-locking
skip-name-resolve
key_buffer = 16M
max_allowed_packet = 16M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-error = /opt/srv/mysql/egame_user/3328/mysql_error.log
pid-file = /opt/srv/mysql/egame_user/3328/egame_user/3328.pid
datadir = /data/mysql/egame_user/3328
character_set_server = utf8
log-slow-queries = /data/mysql/egame_user/3328/slowquery.log
long_query_time = 0.05
binlog_format = MIXED

log-bin=mysql-bin
log_slave_updates=1

innodb_data_home_dir = /data/mysql/egame_user/3328/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/mysql/egame_user/3328/
innodb_buffer_pool_size = 6G
innodb_additional_mem_pool_size = 2M

innodb_log_file_size = 200M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
wait_timeout=360000
interactive_timeout=360000
max_connections=1024
max_binlog_size = 1048576000
expire-logs-days=14

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout



现在急需优化和解决，不知道是不是数据库配置的问题，还是服务器的性能问题呢？
请问该如何解决呢？

cjfeii

重点调整这个参数：
innodb_flush_log_at_trx_commit = 2

如果有很多空闲的链接，可以考虑减少超时时间：
wait_timeout=3600

cjfeii

CPU 利用率低，说明瓶颈在 IO，可以考虑一下sql优化（可以从slowlog入手）

cjfeii

innodb_buffer_pool_size
可以再跳大一点

cjfeii

要考虑读写分离了

lvchenotl

innodb_flush_log_at_trx_commit = 2
这个参数之前在测试环境里测过，貌似修改了影响不大
我查了下慢查询日志，其中有大部分是在重复不停的在查询一张表，表数据量大概16万，虽然sql语句很简单，但是我上午看了下sql的执行计划，每次查询都是全表扫描的...难道是因为这个导致IO高的吗？

cjfeii

线上还是调成：
innodb_flush_log_at_trx_commit = 2
比较好。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。


重复不停全表扫描的那张表可以考虑优化以及增加索引了。

回复 6# lvchenotl


   

zhaiyuzhen

没加索引？
回复 6# lvchenotl