0

Every day during the generation of some reports the mysql process takes up 1 or 2 cores (100%). The problem is that the whole server becomes unresponsive during that time. I can't ssh, postfix doesn't work, apache neither. I can however ping the server. Cronjobs also work, so I'm currently saving top's output every other minute to a file.

There are more cores available, the memory usage is not that high so I don't know why this happens. There is not much I can do about the application that does the reports, but I find it very strange that everything else stops working when mysql is overloaded. Load average goes to around 1,5. After everything is over things start to behave normally. Is there anything I can do about it?

Ubuntu 12.04 with kernel 3.13 on a physical server.

top - 21:51:02 up 82 days, 13:57,  8 users,  load average: 1.19, 1.21, 1.15
Tasks: 367 total,   1 running, 364 sleeping,   0 stopped,   2 zombie
Cpu(s):  1.2%us,  0.9%sy,  1.4%ni, 95.7%id,  0.8%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  16274920k total, 13190728k used,  3084192k free,   284272k buffers
Swap:  4095996k total,  1006148k used,  3089848k free,  4570996k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
20794 mysql     20   0 3904m 893m 5260 S  100  5.6   2810:00 mysqld
19807 otrs      20   0  211m  46m 5560 S   75  0.3   0:00.39 otrs.PostMaster
   14 root      20   0     0    0    0 S    2  0.0  20:49.52 rcuos/6
Jure1873
  • 3,692
  • 1
  • 21
  • 28

2 Answers2

1

Install some proper monitoring and use it to gather information about how your system is performing under normal and load circumstances. Use the monitoring to see which resources are consumed. Take appropriate action based on the facts you uncover.

Read your logs , their entries are time-stamped, check to see if anything relevant is logged when you are running the report.

Learn to use scientific method or similar rather than guessing or expecting the internet to guess.

Community
  • 1
user9517
  • 114,104
  • 20
  • 206
  • 289
  • I see cpu is the bottleneck, but I don't see other problems...I've got munin on the server, but the only anomaly I can see is the cpu load... no major things in the logs, except for the cronjob that runs the reports. I don't want to guess, but I don't know what parameters to look for now... io is apparently not a problem. – Jure1873 Dec 31 '15 at 09:34
  • Present evidence that CPU is a bottleneck. What you show suggests a lightly loaded system. This may help http://unix.stackexchange.com/a/146090/591 – user9517 Dec 31 '15 at 09:43
0

One note I would recommend here is you mention you are saving tops output to a file every other minute. Why not just use atop? You can configure atop to log every second of activity if you want (note this will take up a lot of disk usage). Then you can just view the generated atop logs later using atop -r logfile. That will give you a better idea of what is going on. Atop has a great man page so just read through that and you can easily look at your server and get a better idea of resource usage.

The other thing to note here is that it will be almost impossible for us to really help you troubleshoot this. MySQL issues CAN be related to configuration issues SOMETIMES but it can also be related to your databases themselves. If you have a lot of fragmented tables it can slow things down during certain processes. There is any number of things that can contribute to issues with your MySQL instance that will slow things down.

I would recommend, if you aren't familiar with MySQL, that you download and use mysqltuner.pl. A simple perl script for getting a basic overview of your MySQL instance that you can look through and further troubleshoot from there.

DO NOT TAKE mysqltuner.pl OUTPUT AS RELIGION

What I mean by that is don't just do everything it says. Use it to get a better idea of what is going on and help further in your google fu.

stobiewankenobi
  • 143
  • 3
  • Thanks for the tip about atop, I didn't know about it. My problem is not exactly mysql, but why does the whole system lock up just because of mysql. I will do some optimizations in mysql, but I would like to adjust the priority of mysql so that it can't stop other processes on the same server. – Jure1873 Jan 01 '16 at 16:38