一次生产环境服务器负载和CPU使用率过高后的紧急处理

生产环境服务器堵塞怎么办

image.png

猜测
检查是哪个进程占用了大量CPU
1、mysqld： 尝试优化mysql
2、php-fpm： 适当限制php并发
3、其它进程： 检查是否中了挖矿木马4、被DDOS：换到高防机房
5、配置过低： 升级服务器

我们直接输入命令 top，查看资源占用列表：

image.png

# Query_time: 131.491479  Lock_time: 0.000020 Rows_sent: 5  Rows_examined: 387723
SET timestamp=1631768069;
select `nickname`, `avatar`, sum(user_change_money) as awary_money from `goldcat_user_money_log` as `guml` left join `goldcat_user_login_auth` as `gula` on `guml`.`uid` = `gula`.`uid` and `gula`.`auth_type` = 0 where (`type` = 9 or `type` = 10 or `type` = 18) group by `guml`.`uid` order by `awary_money` desc limit 5;
# Time: 2021-09-16T05:00:23.496068Z
# User@Host: goldcat[goldcat] @ localhost [127.0.0.1]  Id: 16590871
# Query_time: 5.387877  Lock_time: 0.000019 Rows_sent: 1  Rows_examined: 23840
SET timestamp=1631768423;
SELECT  COUNT(shops.id) AS count
                                    FROM shops
                                       LEFT JOIN goldcat_user_info ON shops.user_id = goldcat_user_info.uid
                                    WHERE goldcat_user_info.is_certification = 1;
# Time: 2021-09-16T05:01:01.586305Z
# User@Host: goldcat[goldcat] @ localhost [127.0.0.1]  Id: 16590604
# Query_time: 143.510621  Lock_time: 0.000023 Rows_sent: 5  Rows_examined: 387729
SET timestamp=1631768461;
select `nickname`, `avatar`, sum(user_change_money) as awary_money from `goldcat_user_money_log` as `guml` left join `goldcat_user_login_auth` as `gula` on `guml`.`uid` = `gula`.`uid` and `gula`.`auth_type` = 0 where (`type` = 9 or `type` = 10 or `type` = 18) group by `guml`.`uid` order by `awary_money` desc limit 5;
# Time: 2021-09-16T05:04:48.075339Z
# User@Host: goldcat[goldcat] @ localhost [127.0.0.1]  Id: 16591288
# Query_time: 136.774026  Lock_time: 0.000040 Rows_sent: 5  Rows_examined: 387746
SET timestamp=1631768688;

select `nickname`, `avatar`, sum(user_change_money) as awary_money from `goldcat_user_money_log` as `guml` left join `goldcat_user_login_auth` as `gula` on `guml`.`uid` = `gula`.`uid` and `gula`.`auth_type` = 0 where (`type` = 9 or `type` = 10 or `type` = 18) group by `guml`.`uid` order by `awary_money` desc limit 5;
# Time: 2021-09-16T06:09:48.328364Z
# User@Host: goldcat[goldcat] @ localhost [127.0.0.1]  Id: 16598401
# Query_time: 128.021622  Lock_time: 0.000022 Rows_sent: 5  Rows_examined: 387845
SET timestamp=1631772588;

select `nickname`, `avatar`, sum(user_change_money) as awary_money from `goldcat_user_money_log` as `guml` left join `goldcat_user_login_auth` as `gula` on `guml`.`uid` = `gula`.`uid` and `gula`.`auth_type` = 0 where (`type` = 9 or `type` = 10 or `type` = 18) group by `guml`.`uid` order by `awary_money` desc limit 5;
# Time: 2021-09-16T06:55:02.676388Z
# User@Host: goldcat[goldcat] @ localhost [127.0.0.1]  Id: 16603352
# Quer

MySQL占用内存较大与CPU过高测试与解决办法

在服务器上执行mysql -u root -p之后，输入show full processlist; 可以看到正在执行的语句。

发现有一条

select `nickname`, `avatar`, sum(user_change_money) as awary_money from 
`goldcat_user_money_log` as `guml` left join `goldcat_user_login_auth` as `gula` on `guml`.`uid` = 
`gula`.`uid` and `gula`.`auth_type` = 0 where (`type` = 9 or `type` = 10 or `type` = 18) group by 
`guml`.`uid` order by `awary_money` desc limit 5

这条记录不光出现在了正在执行的当中，还频频出现在慢日志当中，所以这条语句一定是有很大问题的

在业务代码中找到相关代码,于是赶紧注释了这个路由，暂时解决了负载和cpu使用率过高的问题

Route::get('/user/count/award', 'NewUser\UserController@count_award');  //统计注册,开店奖励排名 });

修改过后服务器整体情况立马得到了明显改善

image.png
image.png

可以看到各项指标已经明显趋于正常了,这只是紧急状态下的处理措施，后续还需要完善好代码，让功能正常使用，且尽可能减少服务器和数据库的开销。