生產(chǎn)環(huán)境服務器堵塞怎么辦
image.png
猜測
檢查是哪個進程占用了大量CPU
1、mysqld： 嘗試優(yōu)化mysql
2、php-fpm： 適當限制php并發(fā)
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占用內(nèi)存較大與CPU過高測試與解決辦法
在服務器上執(zhí)行mysql -u root -p之后矛缨，輸入show full processlist; 可以看到正在執(zhí)行的語句爹脾。
發(fā)現(xiàn)有一條
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
這條記錄不光出現(xiàn)在了正在執(zhí)行的當中，還頻頻出現(xiàn)在慢日志當中箕昭，所以這條語句一定是有很大問題的
在業(yè)務代碼中找到相關(guān)代碼,于是趕緊注釋了這個路由灵妨，暫時解決了負載和cpu使用率過高的問題
Route::get('/user/count/award', 'NewUser\UserController@count_award'); //統(tǒng)計注冊,開店獎勵排名 });
修改過后服務器整體情況立馬得到了明顯改善
image.png
image.png
可以看到各項指標已經(jīng)明顯趨于正常了,這只是緊急狀態(tài)下的處理措施，后續(xù)還需要完善好代碼落竹，讓功能正常使用泌霍，且盡可能減少服務器和數(shù)據(jù)庫的開銷。
