MySQL High CPU Usage

[dreams3577]

Hi All
Centos 6 server that is showing high CPU usage in top for “mysqld” by user “mysql”

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
23506 mysql     20   0 1203m  58m 5384 S 142.2  0.2  22:27.25 mysqld 

Can anyone help me find out what is causing high mysqld cpu usage please?

I ran

strace -p 23506

and get the below over and over again

===============================================
getsockname(65, {sa_family=AF_FILE, path="/var/lib/mysql\1"}, [28]) = 0
fcntl(65, F_SETFL, O_RDONLY)            = 0
fcntl(65, F_GETFL)                      = 0x2 (flags O_RDWR)
fcntl(65, F_SETFL, O_RDWR|O_NONBLOCK)   = 0
setsockopt(65, SOL_IP, IP_TOS, [8], 4)  = -1 EOPNOTSUPP (Operation not supported)
clone(child_stack=0x7ff050882f10, flags=CLONE_VM|CLONE_FS|CLONE_FILES|CLONE_SIGHAND|CLONE_THREAD|CLONE_SYSVSEM|CLONE_SETTLS|CLONE_PARENT_SETTID|CLONE_CHILD_CLEARTID, parent_tidptr=0x7ff0504dc9d0, tls=0x7ff0504dc700, child_tidptr=0x7ff0504dc9d0) = 30933
select(13, [10 12], NULL, NULL, NULL)   = 1 (in [12])
fcntl(12, F_SETFL, O_RDWR|O_NONBLOCK)   = 0
accept(12, {sa_family=AF_FILE, NULL}, [2]) = 66
fcntl(12, F_SETFL, O_RDWR)              = 0
===================================

Thanks
Steve

[molona]

@dreams3577

strace

I truly don’t know if this article will help you at all because their traces are not similar to yours.

https://code.mixpanel.com/2012/12/07/debugging-mysql-performance-at-scale/

The interesting part is what he says at the end

It turns out there are known problems with mutexes in MySQL 5.5, with a couple of proposed solutions: tweak the innodb_sync_spin_loops and innodb_spin_wait_delay variables (which affect mutex loops), or manually specify a value for innodb_thread_concurrency

I wonder if that may have anything to do with your issue.

[SpacePhoenix]

Is the high CPU usage constant? If it’s not does it coincide with when any cron jobs are due to run?

[dreams3577]

Hi, Yes it is constant… the lowest it gets is about 30% cpu and upto 180% cpu

[SpacePhoenix]

Are there any queries in your slow query log?

Had any queries been changed or added around the time the CPU usage went crazy?

[dreams3577]

slow query log?? where do I find that?

Nothing that we know of has changed,…