High MySQL CPU Usage

[Will-NYESDigital]

PRODUCT, VERSION, OPERATING SYSTEM, ARCHITECTURE
Parallels Plesk Panel 11.5.30 MU #35 CentOS 6.3 (Final) 64bit

PROBLEM DESCRIPTION

At the same time each day we experience very High MySQL Usage on one of our servers. We have narrowed the issue down to high MySQL database access from one specific customer but are unable to detect what exactly in their config is causing such high usage to the point it slows the server down and on occasions requires a reboot.

STEPS TO REPRODUCE

MySQL CPU usage peaks between 10:00-13:00 GMT and then resumes back to normal usage. The issue doesn't seem to be related to user traffic.

ACTUAL RESULT

Peak CPU usage at 100%

EXPECTED RESULT

Usual CPU Usage should be around 15/20%

ANY ADDITIONAL INFORMATION

If anyone can suggest any ways to help diagnose what is causing this it would be appreciated. Thanks.

 

[KristianM]

Hi Russel,

could you check if this customer has setup any backups?
Are the customer DBs large in size?

Regards,
Kristian

 

[Azurel]

I have this problem since few days. All working fine and starting each day between 04:00 and 12:00/13:00 I get very high cpu peaks (mysql processes) without any reason for long times. In this time is the lowest traffic and lowest connections on server. I running MRTG to show stats.

My server is private/no reseller.. no backups with plesk, but manual backup of database with a shell-script. (this starting ~03:35 and finished long before 04:00) plesk have no support for backup pure databases. But this backup script running since months without any problems.

I have use netstat to check connections and check most unique ips in access_log, nothing founded.

But when you(startposter) problem is another.. that can you help...maybe:

Show counter of unique ips between timestamps 03:30 - 12:30 (add path to "access_log", if you need):

awk -vDate=`date -d '3:30' +[%d/%b/%Y:%H:%M:%S` -vDate2=`date -d'12:30' +[%d/%b/%Y:%H:%M:%S` ' { if ($4 > Date && $4 < Date2) print $1}' access_log | sort |uniq -c |sort -n | tail

List the number of connections the IPs are making to the server using TCP or UDP protocol:

netstat -anp |grep 'tcp\|udp' | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -n

 

[KristianM]

@Azurel

Have you tried enabling the MySQL Query Log just before it starts with high CPU Peaks?

Any cronjobs running at this time? AwStats?

Regards,
Kristian

 

[Azurel]

Examples of my problem:

few days before:

http://picload.org/image/largcrp/mrtga.png

http://img4.picload.org/image/larpiol/mrtge.png

today:

http://picload.org/image/lalaoaa/mrtgx.png

The times are different, so its not a cron. Above the connection is rising to, the last is different.

http://picload.org/image/lalacao/cpu-month.png