#100% CPU Usage in MySQL DB, too many connections, up to 150+ sessions before entire app crashes

46 messages · Page 1 of 1 (latest)

restive rover
#

The project we inherited is having very strange issues. Basically, 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 users actually get into the app, but then it keeps at 95% CPU even once there's basically no activity.

We've confirmed this behavior in our UAT / Staging server as well. Basically at 3% CPU until users are in it for a while, then 95%+ CPU on the DB indefinitely even though it doesn't seem as though that many queries are running at a time.

One thing I notice is that there will be 150+ active sessions / connections and a look at the MySQL processes will reveal a dozen processes trying to use 8%+ of the CPU for some reason.

We suspect some kind of task fires off and keeps running in a loop. I'm watching for Laravel jobs via php artisan queue:listen but nothing appears.

It's been a while since I've worked in PHP but it seems to be a bit painful to get in-depth logging information. One of our theories is that some process is running in an infinite loop and requesting the same data from the database over and over and over again.

Is there a way to tell current Laravel code executing, or PHP code executing in general? Is there a way to get an active trace of all PHP executing in the application, especially Laravel code?

Given the scope of this project we inherited, and the fact that it's already in Production, narrowing down exactly what task is causing these queries to run will be next to impossible without some kind of tracing tool. Otherwise we would have to add manual debugging to probably hundreds of code files, which I would very much so like to avoid having to do.

Any advice on how I can gather some evidence or traces as to what the heck is going on?

static gale
#

🤦 Just said don't post the same question multiple times. This is 3 places now which is working you up to getting timed out by a mod

restive rover
static gale
restive rover
loud karma
#

You could try and report back to Cole's questions/assistance in one of the 3 channels you've now decided to post in (and not clean up yourself) instead of being a dick. The rules are very clear. And even if they weren't, it's just really common sense to not spam in forums/chats etc.

restive rover
unkempt night
restive rover
# unkempt night Yeah, don’t be a dick when someone’s just pointing out that you’re breaking a ru...

They didn't do that. They face-palmed me and said they had already told me not to post multiple times, which they didn't even do originally. My problem was with them being a dick (even if they were also being helpful), not with them pointing out rules. If they wanted to redirect me, they could have pointed to the rules initially when giving their non-technical feedback.

I'm happy to abide by rules if they're actually pointed out to me and clarified, but even so far no one's done that, although I get it by now. I read the rules carefully as I could, and just didn't understand them. My apologies. I'll do better moving forward.

unkempt night
# restive rover They didn't do that. They face-palmed me and said they had already told me not t...

You are presented with the rules and asked to accept them when you joined. They told you not to post in multiple channels when you posted this a second time in #help, and then got a little exasperated when you posted here, the third time. They weren’t a dick to you. At all. But you were a smart-ass back to them. So you’re not off to a great start with first the username and then the attitude

#

I mean, the #rules are pretty clear:

  1. Don’t spam any channel, including posting the same or similar messages in multiple channels.
restive rover
unkempt night
#

Stop being argumentative.

restive rover
#

@unkempt night Yes, that came later.

#

I see it now, as I already stated, prior to you highlighting the rule recently, even.

#

And while I'm thankful for the help and community, if we count the number of messages, there is certainly more effort seemingly willing to be spent here arguing with me rather than addressing the question or not. I'm not really in the mood to be argumentative, just stating information. As already stated, I'm happy to do better moving forward and I understand the rules now.

#

So thank you 🙂

#

I appreciate it.

glacial inlet
#

@restive rover
Hello, I have a problem similar to yours, I have API and FRONT with CPU at 100%, however, I solved the issue of the Database that also had the CPU at 100%. Some steps I took: - I disabled Laravel Telescope; - Created Index in fields widely used in Research; - I added more routes with the Cache (With Redis) in the requests in the database, reducing the load of it. - Perform some other actions related to optimization. My problem is similar to yours but different, I'm still looking for the target location of the problem, what I suggest to you, the same I'm doing is to optimize as much as possible, including using Cache. Another suggestion is to disable some pages and check if there has been a decrease in the load on your system. I hope something of what I said makes sense and helps you, good luck.

restive rover
#

Thanks a ton @glacial inlet that's all very helpful

restive rover
#

Hello all. I still don't know the root cause of why the following is occurring, but I have discovered the following:

There is a scheduled custom command ($schedule->command(...)->everyTenMinutes()) that runs.

At some point, it 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. I do see the same queries in our logs repeatedly, but I can't tell right now if it's just how that process always runs, or if it's just getting stuck in the same inner code loop due to an error of some nature.

(I don't see a ->tries(n) function in the command docs so I can't limit retries from there if that's an issue.)

Over a period of several hours, the multiple instances of the same command running ends up using 100% DB CPU from this never-ending loop of queries. I verified this by running: 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 command's "signature" name dropped the CPU usage back down to 0%, which is how I verified that was the issue:

sudo kill -9 "ps -ef | awk '/[a]rtisan minute:mycustomjob/{print $2}'"

(Note: I use a tic marks in the actual query instead of " marks. Discord uses tic marks for code formatting.)

Personally, I would like to know if this is an error handling issue or simply the query running too slow (ex: taking longer than 10 minutes to execute), so that we end up with a lot of the same scheduled commands.

However, I have discussed the problem job with the client and it turns out this job/command very is low priority in terms of business value, so we are collectively upset this low-priority job which really only needs to run 1x week is using so much DB CPU. We will likely rewrite the entire thing to be much more efficient or a DB task.

unkempt night
restive rover
#

@unkempt night Thanks! Doesn't seem to exist in the version of Laravel being used by this project.

#

I noticed a lot of very helpful scheduling / job / task functions do not exist in the version of Laravel being used by this project, ranging from code functions to CLI commands or helpers. We'll need to evaluate an upgrade at some point as it would have made our lives a lot easier having these options available to us.

unkempt night
restive rover
#

How do I check that? 🙂

unkempt night
#

php artisan --version

restive rover
#

"laravel/framework": "5.7.*",

#

Oh okay let me check.

unkempt night
#

The answer is 5.

#

If that’s in your Composer file.

restive rover
#

Yes. 5.7 verified in the CLI as well.

#

So... Pretty outdated, yeah?

unkempt night
restive rover
#

Great.

unkempt night
#

Updating shouldn’t take that long, though.

#

Could probably have it up to 9.x inside a day.

restive rover
#

$%#& just another thing I need to talk to this client about.

#

The problem is that the jokes who wrote this application initially did an absolute garbage job. I'm terrified of the negative potential any upgrades might have.

#

Thanks for making me aware of it, at least. Whooo-ey.

#

Stress.

#

Hey by the way @unkempt night not sure if you're involved in Laravel at all through OSS Contributions or whatever beyond this Discord, but Laravel has been a pleasure to work with so far compared to other PHP frameworks I've used in the past. I'm very happy with it.

I typically feel a sense of dread whenever PHP projects come our way, but Laravel has largely been a breeze. The fact that it's a relatively modern community with focus on logging and debugging helps a lot as well.

Still learning to navigate my way around Artisan and the fact that some of PHP's documentation standards are woefully behind modern times (which is funny because PHP used to be the de facto example of OSS manuals / community done right), but overall not much to complain about. Comparable to working in NodeJS or .NET in terms of difficulty.

unkempt night
#

I think what you’re experiencing is something that was very intentional by the creator of the framework (Taylor Otwell). He definitely has developer experience in mind, rather than just creating a framework as just being a bunch of libraries.

restive rover
# unkempt night Could probably have it up to 9.x inside a day.

Looks like upgrading beyond 6.x would have a significant development impact on us. I think the guides are perhaps a bit too optimistic about the changes that will be required (ex: saying the upgrade should take 15 minutes when it would likely take us hours or days.) 🤣