High RES usage for MySQL/MariaDB

Forum Moderators: open

Message Too Old, No Replies

High RES usage for MySQL/MariaDB

csdude55

7:15 pm on Feb 7, 2022 (gmt 0)

I'm using MariaDB 10.3, which appears to be pretty much the same as MySQL but theoretically faster.

Yesterday, the server load skyrocketed for no apparent reason. Using "top", I saw that the RES load for MySQL was slowly going up and eventually reached 1.7G. I've never really looked before, so I don't know what a normal load is.

I understand that RES means "Resident size; the non-swapped physical memory a task has used."

It took about 2 hours, but I was able to run backups and restart the server. After that, the RES load for MySQL dropped to about 400M.

As of 2pm today (prime time), the RES load for MySQL is back up to 1.0G. The overall server load is OK, though, if not a little lower than usual (about 0.80).

Any suggestions on how I can determine what a "normal" RES for MySQL should be?

If this is high, what does it imply?

robzilla

7:58 pm on Feb 7, 2022 (gmt 0)

I've never really looked before, so I don't know what a normal load is.

That's why you should have some sort of monitoring in place. I like Munin but there are alternatives. There's only so much top can tell you (I prefer htop), you'll want to see a history as well. That way, you may be able to see if anything preceded or coincided with the high load, such as a sudden increase in page requests.

1.7G is not necessarily a problem, and may not even relate to the high load. Unless you're actually running out of memory, of course. Were you? A monitoring tool could even alert you to such events.

csdude55

8:28 pm on Feb 7, 2022 (gmt 0)

I have Munin, but I'm not sure what to look at. Nothing says RES or anything like it.

MySQL queries and throughput were normal until it spiked right before the server load jumped to around 80 yesterday; it all looks normal now, too. So I guess that's a good thing?

Yesterday at around 5pm, everything seemed normal until I got a text from the server that MySQL had restarted several times. I went to check it out and saw that the server load was at around 40, and when I tried to open anything on any of my sites MySQL was timing out. I couldn't find an apparent reason for it, so I restarted Apache.

After a few minutes the server load dropped back down to around 5, and reading MySQL was back to normal. But then I tried to write something, and it took a LONG time to process! It didn't time out, but it took about 4 minutes to write. That's when I noticed the high RES load.

From there I used mysqldump to create backups (I've had MySQL issues before and didn't want to lose data), which took about an hour. When it was done I restarted the entire server, and when it came up everything seemed OK again.

I don't have any automated processes at 6pm (they're all after midnight), so I have no clue what caused the spike at 5pm.

Those problems are the ones that I hate the most: no clue what caused it, and no clue what fixed it >:-( Which means that it can happen again without warning, and I'm not entirely sure what to do about it.

robzilla

10:06 pm on Feb 7, 2022 (gmt 0)

I have Munin, but I'm not sure what to look at.

Anything that stands out, I guess. Disk latency, iowait, swap in/out, memory usage, httpd requests. Lots of things can cause a high CPU load.

MySQL queries and throughput were normal until it spiked right before the server load jumped

Queries and throughput spiked? What about the number of Apache requests?

Did you actually run out of memory or did you still have plenty to spare? (i.e. unused memory and/or cache)