php – MySql插入高CPU负载

Nginx 前端之家
前端之家收集整理的这篇文章主要介绍了php – MySql插入高CPU负载，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

我有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代码：

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数据以供以后处理.在大多数情况下,“数据”字段大约为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经验.可能我只是想念一些东西.谢谢你的任何想法！

最佳答案
通过“后期”处理,您可能意味着1小时或1天后？
如果是这种情况,那么我会将信息写入CSV文件,您每小时左右旋转一次,然后当您需要进行“后期”处理时,可以使用LOAD DATA INFILE将文件加载到MysqL中

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

我有LOAD DATA INFILE在不到一分钟的时间内加载了100 MB的信息,这种方法将是加快网络响应速度的好方法.

原文链接：https://www.f2er.com/nginx/434897.html
cpu-usage

猜你在找的Nginx相关文章

Nginx基础知识学习（安装/进程模型/事件处理机制/详细配置/定时切割日志）
一、Linux下Nginx的安装 1.去官网 http://nginx.org/download/下载对应的Nginx安...
Nginx配置文件nginx.conf中location的匹配原则
一、空格：默认匹配、普通匹配 location / { root /home; } 二、= ：精确匹配（表示匹配到...
nginx指定配置文件启动
``` nginx -c 配置文件路径 ``` ``` /usr/local/nginx/sbin/nginx -c /usr/local/nginx/co...
你真的了解负载均衡中间件nginx吗？
前言 nginx可所谓是如今最好用的软件级别的负载均衡了。通过nginx的高性能，并发能力强，占...
Ngnix负载均衡安装及配置
1.ngnix概念 Nginx是一款高性能的http 服务器/反向代理服务器及电子邮件（IMAP/POP3）代理...
[Linux] nginx的try_files指令实现隐藏index.php的重写
1.nginx的try_files指令 ，核心功能是替代rewrite，并且比rewrite更强大的是可以按顺序查找...
[日常] 前端资源测试机上忽略版本号的的nginx配置
利用nginx的rewrite的指令，可以实现url的重新跳转，rewrtie有四种不同的flag，分别是redi...
[Nginx] 1.17.9中的更改日志
1. 不允许多个Host请求头 2. 忽略额外的Transfer-Encoding请求头 3.修复在HTTP/2时的socke...
[Linux] 解决nginx: [emerg] directive "rewrite" is not terminated by ";"
解决nginx: [emerg] directive "rewrite" is not terminated by &q...
[Nginx] location与rewrite配合处理项目的重写和路径问题
某个项目中路由是通过$_SERVER['REQUEST_URI']来进行的匹配处理 , 并且隐...
LinuxWindowsCentOSUbuntuNginxWebServiceScalaMemcacheApacheRedisDockerBashAzureTomcatLNMPShell数据结构服务器运维网络安全
xebugnodemondocker-copydcoselasticsearcwindows-contdocker-windodocker-awsamazon-cloudenvoyproxyhashicorp-vaswisscomdevkafka-pythonzscalerphoton-osdocker-swarmkamongoogle-cloudconcoursewso2-ampersistent-vapi-managerprocess-manamanjarojenkins-workhypriotremoteapikeystonejsbitcoindbitcoin-test