MySql插入高CPU负载

Question

我有一个接收POST请求的nginx和一个将请求主体放入MySql的小PHP脚本。问题是当我每秒有300个帖子时，MySql的CPU使用率非常高。我预计MySql会是一个速度很快的东西，每秒可以处理超过300次插入。我使用的是亚马逊EC2小型实例Amazon Linux。

top - 18:27:06 up 3 days,  1:43,  2 users,  load average: 4.40, 5.39, 5.76
Tasks: 178 total,   4 running, 174 sleeping,   0 stopped,   0 zombie
Cpu(s): 24.6%us, 13.4%sy,  0.0%ni,  0.0%id,  1.1%wa,  0.0%hi,  4.9%si, 56.0%st
Mem:   1717480k total,  1640912k used,    76568k free,   193364k buffers
Swap:   917500k total,     5928k used,   911572k free,   824136k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 7677 mysql     20   0  313m 153m 6124 S 39.0  9.2 393:49.11 mysqld
16529 nginx     20   0  157m 151m  820 R 15.2  9.0  28:36.50 nginx
29793 php       20   0 36780 3240 1896 S  2.5  0.2   0:00.34 php-fpm
29441 php       20   0 36780 3204 1892 S  2.2  0.2   0:00.78 php-fpm
29540 php       20   0 36780 3204 1900 S  2.2  0.2   0:00.82 php-fpm
29603 php       20   0 36780 3220 1892 S  2.2  0.2   0:00.61 php-fpm
29578 php       20   0 36780 3200 1900 S  1.9  0.2   0:00.42 php-fpm
29950 php       20   0 36780 3192 1900 S  1.9  0.2   0:00.48 php-fpm
30030 php       20   0 36780 3180 1888 S  1.9  0.2   0:00.08 php-fpm
30025 php       20   0 36780 3200 1888 S  1.6  0.2   0:00.11 php-fpm
29623 php       20   0 36780 3184 1892 S  1.3  0.2   0:00.49 php-fpm
29625 php       20   0 36780 3236 1900 S  1.3  0.2   0:00.46 php-fpm
29686 php       20   0 36780 3364 1900 R  1.3  0.2   0:00.51 php-fpm
29863 php       20   0 36780 3184 1892 S  1.3  0.2   0:00.23 php-fpm
30018 php       20   0 36780 3192 1892 S  1.3  0.2   0:00.19 php-fpm
29607 php       20   0 36780 3224 1900 S  1.0  0.2   0:00.42 php-fpm
29729 php       20   0 36780 3180 1888 R  1.0  0.2   0:00.41 php-fpm

下面是我的PHP代码：

<?php
    $mysqli=new mysqli("localhost", "root", "", "errorreportsraw");
    $project_id=$_REQUEST["project_id"];
    $data=$_REQUEST["data"];
    $date=date("Y-m-d H-i-s");
    $mysqli->query("insert into rawreports(date, data, project_id) values ('$date', '$data', '$project_id')")
?>

我尝试了mysql_connect，mysql_pconnect，mysqli("localhost"，...)，mysqli("p:localhost"，...) -仍然一样。除了这些插入之外，没有对数据库运行任何查询。

下面是我的表格：

CREATE TABLE `rawreports` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `data` mediumtext NOT NULL,
  `project_id` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
);

它非常简单，没有索引，只是为了存储POST数据以供以后处理。“‘data”字段在大多数情况下大约是3千字节。试用了innodb和myisam -还是一样的。

下面是我的SHOW PROCESSLIST，除了多个插入：

mysql> show processlist;
+---------+----------------------+-----------+-----------------+---------+------+------------------+------------------------------------------------------------------------------------------------------+
| Id      | User                 | Host      | db              | Command | Time | State            | Info                                                                                                 |
+---------+----------------------+-----------+-----------------+---------+------+------------------+------------------------------------------------------------------------------------------------------+
| 3872248 | root                 | localhost | NULL            | Query   |    0 | NULL             | show processlist                                                                                     |
| 3901991 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902003 | root                 | localhost | errorreportsraw | Sleep   |    0 |                  | NULL                                                                                                 |
| 3902052 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902053 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902054 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902055 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902056 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902057 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902058 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902059 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902060 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"protocol_version":" |
| 3902061 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902062 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902063 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902064 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902065 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902066 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902067 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902068 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902069 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902070 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902071 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902072 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902073 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902074 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902075 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902076 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902077 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902078 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902079 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902080 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902081 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902082 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902083 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902084 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902085 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902086 | root                 | localhost | errorreportsraw | Query   |    0 | update           | insert into rawreports(date, data, project_id) values ('2012-05-05 17-01-37', '{"exceptions":[{"stac |
| 3902087 | unauthenticated user | localhost | NULL            | Connect | NULL | Reading from net | NULL                                                                                                 |
+---------+----------------------+-----------+-----------------+---------+------+------------------+------------------------------------------------------------------------------------------------------+
39 rows in set (0.00 sec)

下面是当我在服务器仍然承受压力的情况下手动执行相同插入时的配置文件：

set profiling=1;
insert into rawreports(date, data, project_id) values('2012-05-04 00:58:08','[3000-chars-data-here]','5');
show profile ALL for query 1;

Status                          Duration    CPU_user    CPU_system  Context_voluntary    Context_involuntary    Block_ops_in    Block_ops_out   Messages_sent   Messages_received   Page_faults_major   Page_faults_minor   Swaps   Sourc
starting                        0.000231    0.000000    0.000000    0                    0                      0               0               0               0                   0                   0                   0       NULL    NULL    NULL
checking permissions            0.000030    0.000000    0.000000    0                    0                      0               0               0               0                   0                   0                   0       check_access    sql_parse.cc    4745
Opening tables                  0.000057    0.001000    0.000000    0                    0                      0               0               0               0                   0                   0                   0       open_tables     sql_base.cc     4836
System lock                     0.000030    0.000000    0.000000    0                    0                      0               0               0               0                   0                   0                   0       mysql_lock_tables       lock.cc 299
init                            0.000037    0.000000    0.000000    0                    0                      0               0               0               0                   0                   0                   0       mysql_insert    sql_insert.cc   721
update                          0.075716    0.001999    0.011998    166                  2                      0               0               0               0                   0                   0                   0       mysql_insert    sql_insert.cc   806
Waiting for query cache lock    0.000087    0.000000    0.000000    0                    0                      0               0               0               0                   0                   0                   0       lock    sql_cache.cc    552
update                          0.000037    0.000000    0.000000    0                    0                      0               0               0               0                   0                   0                   0       NULL    NULL    NULL
end                             0.000024    0.000000    0.000000    0                    0                      0               0               0               0                   0                   0                   0       mysql_insert    sql_insert.cc   1049
query end                       0.000042    0.000000    0.000000    0                    0                      0               0               0               0                   0                   0                   0       mysql_execute_command   sql_parse.cc    4434
closing tables                  0.000031    0.000000    0.001000    0                    0                      0               0               0               0                   0                   0                   0       mysql_execute_command   sql_parse.cc    4486
freeing items                   0.000126    0.000000    0.000000    0                    1                      0               0               0               0                   0                   0                   0       mysql_parse     sql_parse.cc    5634
logging slow query              0.000030    0.000000    0.000000    0                    0                      0               0               0               0                   0                   0                   0       log_slow_statement      sql_parse.cc    1460
cleaning up                     0.000024    0.000000    0.000000    0                    0                      0               0               0               0                   0                   0                   0       dispatch_command        sql_parse.cc    1416

我使用MySql 5.5.20。我同时尝试了InnoDB和MyISAM -两者都是一样的。

下面是我的iostat输出：

# iostat -x
Linux 3.2.12-3.2.4.amzn1.i686 05/15/2012      _i686_  (1 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          23.67    0.03   18.39    4.09   52.87    0.95

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvdap1            0.00     1.74    0.03    0.78     1.50    25.12    32.85     0.01   14.03   5.92   0.48
xvdap3            0.00     0.00    0.01    0.01     0.05     0.08    10.47     0.00    5.72   1.09   0.00
xvdf              0.40    18.59   23.14  117.55   753.12  3465.25    29.98     0.53    3.74   2.38  33.46

最明显的是批处理、插入和提交它们，而不是一个接一个。但我不能这样做，因为每次插入都是一个单独的POST请求，是单独的PHP脚本执行。它们都是同时执行的，并且不会相互干扰。

似乎是一个相当简单的任务，我的CPU到底在做什么这么难？我没有太多使用mysql，php，linux的经验。也许我只是错过了一些东西。谢谢你的任何想法！

Answer 1

所谓“稍后”处理，您可能指的是1小时或1天后处理？如果是这样的话，我会将信息写入一个CSV文件，您大约每隔一个小时轮换一次，然后当您需要进行“稍后”处理时，您可以使用LOAD DATA INFILE将文件加载到MySQL中。

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

我已经让LOAD DATA INFILE在不到一分钟的时间里加载了100MB的信息，这种方法将是一个很好的方法来加速你的web响应。

Answer 2

你是在循环中运行PHP代码吗？您可以在单个查询中一次插入多行，这可能有助于降低CPU负载。您所需要做的就是提供一个逗号分隔的值列表，如下所示：

insert into rawreports(date, data, project_id) values (('$date1', '$data1', '$project_id1'),('$date2', '$data2', '$project_id2'), .....)")

此外，如果您是在循环中运行，则不需要在每次迭代中重复new mysqli()。

Answer 3

没有索引，

这并不完全正确。

我首先会删除一个索引。

在对数类型的表中，这没有任何意义。

顺便说一下，您也可以使用文本日志。用于以后的处理。

要获取一些详细信息，您可以在服务器处于通常负载下时从mysql控制台运行以下命令：

> SET profiling = 1;
> INSERT an example query
> SHOW PROFILE ALL

也试着傻一下

SHOW PROCESSLIST;

这无疑会揭示一些有用的东西，但至少值得一试。