我有Nginx接收POST请求和一个小的
PHP脚本,将请求体放到MysqL.当我每秒有300个POST时,问题是MysqL cpu使用率非常高.我希望MysqL是一个快速的东西,可以处理每秒300次插入更多.我使用亚马逊EC2小实例,亚马逊Linux.
@H_404_1@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
@H_404_1@<?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”,…) – 仍然是一样的.除了这些插入之外,没有对数据库运行任何查询.
这是我的表：
@H_404_1@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,除了多个插入之外什么都没有：
@H_404_1@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：
@H_404_1@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输出：
最明显的是批量插入并将它们全部一起提交.但我不能这样做,因为每个插入都是一个单独的POST请求,单独的PHP脚本执行.它们都是同时执行的,不会相互干扰.
似乎是一个非常简单的任务,我的cpu实际上做得如此之难？没有太多的MysqL,PHP,linux经验.可能我只是想念一些东西.谢谢你的任何想法！
