我有nginx接收POST请求和一个小的PHP脚本,将请求体放到MySql.当我每秒有300个POST时,问题是MySql CPU使用率非常高.我希望MySql是一个快速的东西,可以处理每秒300次插入更多.我使用亚马逊EC2小实例,亚马逊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代码：
我试过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数据以供以后处理.在大多数情况下,“数据”字段大约为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,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,'{"exceptions":[{"stac |
| 3902053 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902054 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902055 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902056 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902057 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902058 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902059 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902060 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"protocol_version":" |
| 3902061 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902062 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902063 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902064 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902065 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902066 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902067 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902068 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902069 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902070 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902071 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902072 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902073 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902074 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902075 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902076 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902077 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902078 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902079 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902080 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902081 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902082 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902083 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902084 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902085 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902086 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date,'{"exceptions":[{"stac |
| 3902087 | unauthenticated user | localhost | NULL | Connect | NULL | Reading from net | NULL |
+---------+----------------------+-----------+-----------------+---------+------+------------------+------------------------------------------------------------------------------------------------------+
39 rows in set (0.00 sec)
当我在服务器仍然处于压力下时手动执行相同的插入时,这是PROFILE：
set profiling=1;
insert into rawreports(date,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经验.可能我只是想念一些东西.谢谢你的任何想法！
