生产环境服务器堵塞怎么办
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
可以看到各项指标已经明显趋于正常了,这只是紧急状态下的处理措施，后续还需要完善好代码，让功能正常使用，且尽可能减少服务器和数据库的开销。
