...here's the graph of CPU usage

You haven't restarted mysqld? Look at 'show innodb status', maybe you can see which background process is busy. Also try if you can get a trace.

This is from a freshly restarted slave:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 1937 mysql 20 0 815m 45m 5800 S 63.1 9.2 2:20.99 mysqld



mysql> show processlist \G*************************** 1. row *************************** Id: 1 User: system user Host: db: NULL Command: Connect Time: 0 State: Slave has read all relay log; waiting for the slave I/O thread to update it Info: NULL Rows_sent: 0Rows_examined: 0 Rows_read: 1*************************** 2. row *************************** Id: 2 User: system user Host: db: NULL Command: Connect Time: 313 State: Waiting for master to send event Info: NULL Rows_sent: 0Rows_examined: 0 Rows_read: 1*************************** 3. row *************************** Id: 4 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: show processlist Rows_sent: 0Rows_examined: 0 Rows_read: 13 rows in set (0.02 sec)



mysql> show engine innodb status \G*************************** 1. row *************************** Type: InnoDB Name:Status:=====================================1 20709 14:33:36 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 11 seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops: 159 1_second, 158 sleeps, 13 10_second, 26 background, 26 flushsrv_master_thread log flush and writes: 338----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 62, signal count 61Mutex spin waits 45, rounds 1350, OS waits 40RW-shared spins 22, rounds 660, OS waits 22RW-excl spins 0, rounds 0, OS waits 0Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl--------FILE I/O--------I/O thread 0 state: waiting for completed aio requests (insert buffer thread)I/O thread 1 state: waiting for completed aio requests (log thread)I/O thread 2 state: waiting for completed aio requests (read thread)I/O thread 3 state: waiting for completed aio requests (read thread)I/O thread 4 state: waiting for completed aio requests (read thread)I/O thread 5 state: waiting for completed aio requests (read thread)I/O thread 6 state: waiting for completed aio requests (write thread)I/O thread 7 state: waiting for completed aio requests (write thread)I/O thread 8 state: waiting for completed aio requests (write thread)I/O thread 9 state: waiting for completed aio requests (write thread)Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0Pending flushes (fsync) log: 0; buffer pool: 0671 OS file reads, 674 OS file writes, 105 OS fsyncs3.18 reads/s, 16384 avg bytes/read, 1.00 writes/s, 0.82 fsyncs/s-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf: size 1, free list len 0, seg size 2, 5 mergesmerged operations: insert 0, delete mark 5, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash table size 276671, node heap has 1 buffer(s)0.00 hash searches/s, 10.45 non-hash searches/s---LOG---Log sequence number 655868663Log flushed up to 655868486Last checkpoint at 655861832Max checkpoint age 108005254Checkpoint age target 104630090Modified age 6831Checkpoint age 68310 pending log writes, 0 pending chkp writes66 log i/o's done, 0.64 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 137887744; in additional pool allocated 0Internal hash tables (constant factor + variable factor) Adaptive hash index 2233968 (2213368 + 20600) Page hash 139112 (buffer pool 0 only) Dictionary cache 650907 (554768 + 96139) File system 83552 (82672 + 880) Lock system 333624 (332872 + 752) Recovery system 0 (0 + 0)Dictionary memory allocated 96139Buffer pool size 8191Buffer pool size, bytes 134201344Free buffers 7502Database pages 688Old database pages 273Modified db pages 60Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 660, created 28, written 5883.18 reads/s, 0.00 creates/s, 0.27 writes/sBuffer pool hit rate 925 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 688, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue1 read views open inside InnoDB---OLDEST VIEW---Normal read viewRead view low limit trx n 1E800Read view up limit trx id 1E800Read view low limit trx id 1E800Read view individually stored trx ids:-----------------Main thread process no. 1937, id 139850250225408, state: flushing logNumber of rows inserted 42, updated 278, deleted 0, read 2780.27 inserts/s, 2.09 updates/s, 0.00 deletes/s, 2.09 reads/s------------TRANSACTIONS------------Trx id counter 1E800Purge done for trx's n < 1E7FF undo n < 0History list length 1784LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0, not startedMySQL thread id 4, OS thread handle 0x7f3180241700, query id 1457 localhost rootshow engine innodb status---TRANSACTION 1E7FF, not startedMySQL thread id 1, OS thread handle 0x7f3180282700, query id 1457 Slave has read all relay log; waiting for the slave I/O thread to update it----------------------------END OF INNODB MONITOR OUTPUT============================


Can I somehow do a trace without having to recompile mysql with debugging support?

I had similar problems with some servers. This helped me a lot:
$ /etc/init.d/mysql stop
$ /etc/init.d/ntpd stop
$ date -s "`date`"
$ /etc/init.d/ntpd start
$ /etc/init.d/mysql start

There is nice blog info about it: http://blog.mozilla.org/it/2012/06/30/mysql-and-the-leap-sec ond-high-cpu-and-the-fix/

Have a nice day.

Jaromir Kuba
vpnhosting.cz

Díky, tohle mi pomohlo!

aww, the leap second

seems like that helped, for those who don't understand Czech