热点
【独家】服务器安全：数据中心的物理安全性
【独家】服务器安全：审计和日志管理的关键性
【独家】服务器安全：合规性和法规遵守
【独家】服务器安全：员工培训和教育的重要性
【独家】服务器安全：灾难恢复计划的重要性
如何优化查询语句的性能？
使用快照恢复数据库的数据（续）
使用数据库镜像保护数据完整性
通过复制功能同步数据库数据
在MS SQL Server中使用全文搜索
4 5 月 2025, 周日
Nginx

php – MySql插入高CPU负载

由 dawei 没有评论 #cpu-usage #insert #MySQL #nginx #p

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

由 dawei

【声明】：唐山站长网内容转载自互联网，其相关言论仅代表作者个人观点绝非权威，不代表本站立场。如您发现内容存在版权问题，请提交相关链接至邮箱：bqsm@foxmail.com，我们将及时予以处理。

相关文章

Nginx

nuberx中的kubernetes dns解析器

dawei
Nginx

php – 负载均衡：Nginx,Varnish和HTTP会话

dawei
Nginx

使用Nginx反向代理应用程序及其静态文件

dawei

您错过了

服务器

【独家】服务器安全：数据中心的物理安全性

服务器

【独家】服务器安全：审计和日志管理的关键性

服务器

【独家】服务器安全：合规性和法规遵守

服务器

【独家】服务器安全：员工培训和教育的重要性