MySQL slow, but speed up after drop disk caches

Question

I have very busy Linux MySQL server with huge database.

Server usually work on load average 2 or less.

The server have software raid (mdadm) that is currently rebuilding.

Several times today, MySQL get slower and load average reach 200-300-500.

In same time iostat is not big - 20-30% maximum.

Swap is currently turned off, because I thought it causes the problem.

Restart MySQL do not help. If we reboot the server, everything get back to normal for couple of hours.

Last time, instead of reboot I decided to stop MySQL and drop caches. I did in this way:

sync; echo 1 > /proc/sys/vm/drop_caches
sync; echo 2 > /proc/sys/vm/drop_caches
sync; echo 3 > /proc/sys/vm/drop_caches

(Yes I did three in a row)

For my surprise, when I started MySQL, everything get back to normal.

What could be the problem and can I automate the "drop caches" somehow to prevent the server of slow down at least for next couple of days.

Once you get to this point the problem is resource exhaustion. Shot in the dark: if dropping disk caches makes it *faster* then you're probably low on RAM. If you're not monitoring your server with something like Nagios, you should be. You need to look at historical Memory usage and Disk IO stats in order to properly diagnose something like this instead of subjective break/fix judgements like you've done. — Sammitch, Aug 10 '17 at 00:58
seems like it was from raid rebuilding. rebuild finished and for the moment server works OK. if next 2-3 days all OK, will post answer. — Nick, Aug 10 '17 at 07:45

score 0 · Accepted Answer · edited Sep 20 '17 at 07:18

0

Once RAID was rebuilt, we restarted the server once more and then things were back to normal.

We believe, during RAID rebuild, Linux used lots of memory and the server was actually running out of memory.

edited Sep 20 '17 at 07:18

Wilson Hauck

answered Aug 12 '17 at 18:21

Nick

score -1 · Answer 2 · edited Sep 20 '17 at 07:17

-1

Answer could be provided with posting to your original question the following:
RAM available on the HOST

SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES;
SHOW ENGINE INNODB STATUS;

edited Sep 20 '17 at 07:17

HBruijn

answered Sep 20 '17 at 05:37

Wilson Hauck

2 Answers2