1

Apache/MySQL/Ubuntu stack on AWS EC2 here.

For some reason, my web server spikes to 99% and my server becomes completely unusable. It is insanely frustrating. I don't have to the ability to troubleshoot the issue, because the server is completely unresponsive, and I can't login. I know it's something to do with MYSQL, and that is it. It will slowly increase CPU usage until it gets to 100% and I can't do anything but forcefully Stop and Start the instance via the AWS UI.

My work around thought is to automatically restart MySQL/Apache if the CPU usage reaches a certain point. I was messing with cpu-limit, but I can't figure out how to get this to attach to PIDs already running.

I just want to be able to limit the CPU until it gets to that point, then I can try to read the logs or see what is going on. Thanks for the help.

Improve this question
2
  • Under /proc/PID, you find the stat file, which includes CPU usage figures. A version of that data is in /proc/PID/status (human-readable). See man proc for details. I would run my script based on the utime and stime values. To limit the CPU, try renice or put suspicious processes in a Cgroup (I don't remember, off the cuff, how you can limit CPU this way, but it's not too hard. Tutorials and kernel information are not hard to find).
    – berndbausch
    Commented May 16, 2021 at 11:27
  • You could also sort the output of ps -el according to the C column to find high CPU users, or try top's batch mode.
    – berndbausch
    Commented May 16, 2021 at 11:39

2 Answers 2

Reset to default
1

Instead, turn on the slowlog and have long_query_time=1 (or less). After the next spike in CPU, use pt-query-digest to analyze the slowlog. (We can help if needed.)

That will discover which queries are taking the longest time (hence probably using the most CPU). Often the "cure" is to make a better, composite, index for some table.

More: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

Improve this answer
0

Running an extra thing at high load requires there to be some capacity remaining. On a systemd system, make it share its slice with resource control.

systemctl edit mysql.service

Add to the unit CPU and IO weights lower than the default of 100. CPU quota is % of one CPU, and you didn't mention the number of CPUs, so this an absolute limit may need adjusting. Also enable CPU, IO, and memory accounting.

[Service]
CPUAccounting=yes
CPUWeight=50
#CPUQuota=90%
IOAccounting=yes
IOWeight=50
MemoryAccounting=yes
TasksAccounting=yes

After editing the drop in configs, systemctl daemon-reload. Use systemd-cgtop to see resource consumption by service. In particular, confirm the resource really is used by the database and not httpd.

See also: How to limit a systemd service to “play nice” with the CPU?

This doesn't explain the performance issue, only lowers the priority of mysql enough to hopefully let other things run. Use Linux performance tools to assess the workload. A starting tool set is Linux Performance Analysis in 60,000 Milliseconds from when Brendan Gregg was at Netflix. These essentials need repeating:

uptime
dmesg | tail
vmstat 1
mpstat -P ALL 1
pidstat 1
iostat -xz 1
free -m
sar -n DEV 1
sar -n TCP,ETCP 1
top

I suspect the box is starved for storage I/O or memory, but I have no evidence of that. Examine the system in detail from a web server and database perspective.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .