The MySQL DB CPU is running at 95% basically at all times, even when there's seemingly no activity in the app. It doesn't happen right away. Only once the app has been running for a while, but then it keeps at 95% CPU even once there's seemingly no activity.
The number of active sessions / connections gradually climbs from dozens to even hundreds. Looking at the MySQL processes on RDS reveals a dozen processes trying to use 8% of the DB CPU each for some reason.
I've checked for Laravel jobs via php artisan queue:listen but nothing appears.
Checked the database and query logs, and there are many DB logs which suggest a job or something occurring in a loop, but no indication as to what the source of those jobs are as the queries being ran are generic queries and could be called from many different places in the application.
We do not believe this is due to user activity, but if it is, it's some kind of user action whicih results in some kind of a server loop.
Checked application and error logs and nothing in particular stands out.
CodePudding user response：
I still don't know the root cause of why this is occurring, but I have discovered the following which is enough for me to solve the problem:
There is a scheduled custom command ($schedule->command(...)->everyTenMinutes()) that runs specified in app/Console/Commands/Kernel.php
At some point, the job either fails to complete on time (and thus the commands running gradually build up over time) and/or there is an error and it gets stuck processing essentially the same records again and again and again in a loop.
protected $signature = 'minute:mycustomjob';
Over a period of several hours, the multiple instances of the same command running ends up using 100% DB CPU due to the never-ending loop of queries. I verified the running processes by running the following in the CLI: ps -ef | grep 'artisan' which listed about a dozen instances of this very CPU and DB-intensive process running on the server during peak load times.
Killing the process by killing artisan jobs with the command's "signature" name dropped the CPU usage back down to 0%, further proving the job as the culprit:
sudo kill -9 `ps -ef | awk '/[a]rtisan minute:mycustomjob/{print $2}'`
The potential solutions I have in mind are as follows: Re-write the job to not error, re-write the job to be more efficient and complete within 10 minutes or less, lower the frequency at which the job executes, upgrade Laravel to a newer version which supports preventing task overlaps: https://laravel.com/docs/9.x/scheduling#preventing-task-overlaps