cpu reach 600% /usr/sbin/mysqld

[user4629]

Every 2-3 hours vps hangs with cpu always > 150% --> sometimes over 600%
with the following top stats:

always the usr/sbin/mysqld command.


Pid Owner Priority CPU % Memory % Command
50418 (Trace) (Kill) mysql 0 100+% - 600% 8.90 /usr/sbin/mysqld

at the times cpu > 200%
in the Show MySQL Processes
We dont have more than 6-7 proccesses.

server info:

Total processors: 8
Processor #1
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) Silver 4114 CPU @ 2.20GHz
Speed
2200.000 MHz
Cache
16384 KB
Processor #2
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) Silver 4114 CPU @ 2.20GHz
Speed
2200.000 MHz
Cache
16384 KB
Processor #3
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) Silver 4114 CPU @ 2.20GHz
Speed
2200.000 MHz
Cache
16384 KB
Processor #4
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) Silver 4114 CPU @ 2.20GHz
Speed
2200.000 MHz
Cache
16384 KB
Processor #5
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) Silver 4114 CPU @ 2.20GHz
Speed
2200.000 MHz
Cache
16384 KB
Processor #6
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) Silver 4114 CPU @ 2.20GHz
Speed
2200.000 MHz
Cache
16384 KB
Processor #7
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) Silver 4114 CPU @ 2.20GHz
Speed
2200.000 MHz
Cache
16384 KB
Processor #8
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) Silver 4114 CPU @ 2.20GHz
Speed
2200.000 MHz
Cache
16384 KB

Memory Information
[ 0.000000] Memory: 357924K/1048032K available (12293K kernel code, 2225K rwdata, 7712K rodata, 2476K init, 14048K bss, 160656K reserved, 0K cma-reserved)

Current Memory Usage
total used free shared buff/cache available
Mem: 16647148 2666468 8332024 38556 5648656 13745316
Swap: 0 0 0
Total: 16647148 2666468 8332024

 

[user4629]

mysqladmin stat
gives

Uptime: 28730 Threads: 22 Questions: 26826680 Slow queries: 2998 Opens: 558 Flush tables: 1 Open tables: 532 Queries per second avg: 933.751

and currently

owner cpu memory command
mysql 201.66 10.04 /usr/sbin/mysqld

 

[user4629]

Also
For the specific pid that currently overloads cpu (1646)

11:28:25 PM UID PID %usr %system %guest %wait %CPU CPU Command
11:28:25 PM 993 1646 143.22 80.57 0.00 0.01 223.79 0 mysqld

 

[ffeingol]

The only thing that might be remotely helpful would be:

show processlist

or

show full processlist

From within MySQL when the issue is happening. You need to be able to see what's happening in MySQL and the show processlist will show you that.

 

[user4629]

I have tried that.
for 212% cpu for example

1646 (Trace) (Kill) mysql 0 212.80 10.29 /usr/sbin/mysqld

I have only the following processes

Sending data [Info] => SELECT DISTINCT ps.product_id, (SELECT AVG(rating) FROM oc_review r1 WHERE r1.product_id = ps.produc [Progress] => 0.000 )

 

[cPRex]

I think the answer to your problem is contained in the "mysqladmin" output you provided. Your server is getting one slow query almost every 9 seconds that it has been online, and this is likely causing major processing issues since the server is receiving over 900 queries per second.

You'll want to work with a MySQL admin to temporarily enable the slow query log, see what users and queries are causing that, get that resolved, and then optimize the server for the amount of traffic that your database is getting.