由于mysql cpu使用率高，平均负载高

Question

在过去的一周里，我收到了两次警告，说我的平均负载很高，显然它必须对mysql做些什么

我每天访问的次数增加了10-20,000次(每天总共180-150,000次)，而且从上个月起我就开始使用触发器。

我的桌子没那么大，大的大约有10万行。

因此，这里有一个警告：

 This is an automated message notifying you that the 5 minute load average on your system is 10.93.
This has exceeded the 10 threshold.

One Minute      - 40.6
Five Minutes    - 10.93
Fifteen Minutes - 4.78

top - 20:33:02 up 22 days, 22:38,  0 users,  load average: 40.60, 10.93, 4.78
Tasks: 667 total,  83 running, 580 sleeping,   0 stopped,   4 zombie
Cpu(s): 18.5%us,  0.4%sy,  0.0%ni, 80.7%id,  0.1%wa,  0.0%hi,  0.3%si,  0.0%st
Mem:  32840004k total, 32478112k used,   361892k free,   364564k buffers
Swap:  1050616k total,   532240k used,   518376k free, 22111016k cached

 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
7577 mysql     20   0 6680m 1.4g 5680 S 243.8  4.6  13448:23 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/ns4008353.ip-198-27-68.net.err --open-files-limit=7296 --pid-file=/var/lib/mysql/ns4008353.ip-198-27-68.net.pid                                                                                                                                                                                                                                                                             
1296 apache    20   0  262m  72m 7076 R  7.0  0.2   3:55.66 /usr/sbin/httpd -k start -DSSL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

另一个：

One Minute      - 73.75
Five Minutes    - 18.41
Fifteen Minutes - 6.96

top - 11:09:03 up 14 days, 13:14,  0 users,  load average: 73.75, 18.41, 6.96
Tasks: 483 total, 111 running, 369 sleeping,   0 stopped,   3 zombie
Cpu(s): 18.2%us,  0.4%sy,  0.0%ni, 81.1%id,  0.1%wa,  0.0%hi,  0.3%si,  0.0%st
Mem:  32840004k total, 32671524k used,   168480k free,   378116k buffers
Swap:  1050616k total,   428900k used,   621716k free, 22453492k cached

 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
7577 mysql     20   0 7391m 2.2g 5644 S 355.7  6.9   8113:40 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/ns4008353.ip-198-27-68.net.err --open-files-limit=7296 --pid-file=/var/lib/mysql/ns4008353.ip-198-27-68.net.pid                                                                                                                                                                                                                                                                             
3480 apache    20   0  211m  19m 3708 R  5.0  0.1   0:00.30 /usr/sbin/httpd -k start -DSSL                                                                                                                                                                                                                                

显然，mysql有问题。

当我在ssh中执行top时，偶尔会看到mysql的使用率高达90%-110% (8种诅咒中的1种核心)，即使站点上的流量不多。

所以我做了一个SHOW FULL PROCESSLIST，这是结果

Id      User                Host        db         Command  Time    State                   Info
387971  sitedb_main     localhost   sitedb_main     Sleep   72      NULL
388070  sitedb_main     localhost   sitedb_main     Query   0      Copying to tmp table     SELECT * FROM `images` WHERE `confirm` = 1   AND `...
388071  sitedb_main     localhost   NULL            Query   0       NULL                    SHOW FULL PROCESSLIST

另一个：

Id      User                Host        db         Command  Time    State           Info
209521  sitedb_main     localhost   sitedb_main     Sleep   108                     NULL
209752  sitedb_main     localhost   sitedb_main     Sleep   0                       NULL
209756  sitedb_main     localhost   sitedb_main     Query   0     Sorting result    SELECT *    FROM (`images`)    WHERE `confirm` =  1
209757  sitedb_main     localhost   NULL            Query   0     NULL              SHOW FULL PROCESSLIST

在sleep列下有一个Command过程，它从时间的0开始，有时上升到100或200，有时达到300，然后逐渐消失。

当这些睡眠过程(5-6)同时运行时，mysql的负载似乎达到了峰值。

我不知道这些睡眠过程是什么(信息栏下没有)

我的数据库是索引的，我的查询是相当优化的，所以有什么建议吗？

顺便说一下，我已经启用了缓慢的查询日志(我希望如此)，并且正在安装mytop：

安装mytop时，需要一个最新的工具来监视mysql.

Processor Name  Intel(R) Xeon(R) CPU E3-1245 V2 @ 3.40GHz
Total Memory    32840004 kB
Free Memory 10416752 kB
Total Swap Memory   1050616 kB
Free Swap Memory    1050616 kB
MySQL 5.5.31    Running

这些都是无辜者的台词！来自my.cnf：

innodb_file_per_table=1
innodb_buffer_pool_size=1G
#innodb_log_file_size=256M
#innodb_log_buffer_size=4M

my.cnf：

bind-address=127.0.0.1
innodb_file_per_table=1
innodb_buffer_pool_size=1G
default-storage-engine=MYISAM
#innodb_log_file_size=256M
#innodb_log_buffer_size=4M
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
skip-external-locking
wait_timeout=1200
query_prealloc_size=16384
query_cache_type=1
sort_buffer_size=2M
key_buffer=128M
key_buffer_size=512M
max_connect_errors=10
query_cache_size=128M
read_buffer_size=8M
join_buffer_size=64M
table_cache=1500
max_connections=450
query_cache_limit=4M
max_allowed_packet=128M
tmp_table_size=128M
max_heap_table_size=128M
connect_timeout=10
myisam_sort_buffer_size=64M
thread_cache_size=64
query_alloc_block_size=16384
local-infile=0

open_files_limit=7296
slow-query-log = 1
slow-query-log-file = /var/log/mysql/log-slow-queries.log
long_query_time = 1

Answer 1

看看这个my.cnf，InnoDB for MySQL 5.5有更多的火力可以配置。

我要添加以下设置：

innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_fast_shutdown=0
innodb_log_buffer_size=256M
innodb_log_file_size=512M
innodb_flush_method=O_DIRECT
query_cache_size=0

这些是做什么用的？

• 诺姆b和诺姆b (自MySQL 5.1.38以来)为读写分配指定数量的线程。默认值为4，最大值为64。
• 将诺姆b设置为0完全清除对磁盘的事务更改。这也使得一个更快的启动。
• 默认情况下，诺姆b在MySQL 5.5中为5MB。我只是建议它们要大得多。文档显示:日志组中每个日志文件的大小(以字节为单位)。日志文件的合并大小不能超过3.99999999068677 GB，等于4294967295范围限制。例如，一对2047 MB的日志文件将允许您接近范围限制，但不超过它。默认值为5MB。合理的值从缓冲池大小的1MB到1/N不等，其中N是组中的日志文件数。值越大，缓冲池中需要的检查点刷新活动就越少，从而保存磁盘I/O，但较大的日志文件也意味着在崩溃时恢复速度更慢。
• 诺姆b告诉InnoDB绕过操作系统中的任何缓存。所有需要的缓存都是在内部完成的(在InnoDB体系结构中)
• 查询设置为128米。它应该是0，因为InnoDB非常频繁地检查查询缓存。实际上，InnoDB最好禁用此选项(请参阅我在2012年9月5日的文章：频繁查询缓存失效的开销值得吗？)
• 诺姆b设置越高，磁盘I/O越低。

您可能正在使用默认日志文件运行，因此我会这样做：

步骤01 :登录到mysql并运行如下：

mysql> SET GLOBAL innodb_fast_shutdown = 0;

步骤02 :关机MySQL

service mysql stop

步骤03 :重命名InnoDB日志文件

cd /var/lib/mysql
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak

步骤04 :启动MySQL

service mysql start

需要额外的时间重新创建新的ib_logfile0和新的ib_logfile1

这将提高InnoDB在CPU和磁盘使用方面的总体性能。

试试看！

另外，下面是InnoDB的外观：

InnoDB体系结构