From: Ben Clewett
Date: January 17 2008 8:25am
Subject: MySql at 100% CPU
List-Archive: http://lists.mysql.com/mysql/210901
Message-Id: <478F110A.5050007@clewett.org.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Dear MySql,

I hope you can help me trace a problem.

Every few days one of my 5.0.45 will stick at 100% CPU whilst not doing 
very much.

The server is a replication master and slave, but idle at this time.  I 
am running 100% InnoDB and there is nothing in the error log.

It will stay that way for minutes or hours, or until it is restarted. 
This load cannot be accounted for by the processlist.

I need to know why MySql does this.  If any person knows how I an 
identify the problem, please let me know!

Thanks!

Ben Clewett.


# top

top - 08:19:50 up 5 days,  9:52,  1 user,  load average: 1.40, 1.76, 1.09
Tasks: 129 total,   1 running, 128 sleeping,   0 stopped,   0 zombie
Cpu(s): 25.3%us,  0.2%sy,  0.0%ni, 74.5%id,  0.0%wa,  0.0%hi,  0.0%si, 
0.0%st
Mem:  10235936k total,  9946508k used,   289428k free,   179584k buffers
Swap: 10490436k total,      124k used, 10490312k free,  5449908k cached

   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
  6296 mysql     15   0 4163m 3.6g 5728 S  100 37.1 342:14.36 mysqld



mysql> show processlist;

+--------+-------------+----------------------------------+-------+-------------+------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id     | User        | Host                             | db    | 
Command     | Time | State 
                    | Info 
                                                    |
+--------+-------------+----------------------------------+-------+-------------+------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 386295 | system user |                                  | NULL  | 
Connect     |  488 | Waiting for master to send event 
                    | NULL 
                                                    |
| 386296 | system user |                                  | NULL  | 
Connect     |    0 | Has read all relay log; waiting for the slave I/O 
thread to update it | NULL 
                                                    |
| 386340 | slave       | hp-tm-07.roadtech.private:53998  | NULL  | 
Binlog Dump |  408 | Has sent all binlog to slave; waiting for binlog to 
be updated        | NULL 
                                                  |
| 386667 | root        | localhost                        | NULL  | 
Query       |    0 | NULL 
                    | show processlist 
                                                    |
| 386669 | webservice  | fls-16-05.roadtech.private:44277 | par_4 | 
Query       |    1 | Sending data 
                    | SELECT COUNT(*) FROM --------- WHERE comp_code = 
'---'  AND veh_code =  '-------'  AND t BETWEEN   |
+--------+-------------+----------------------------------+-------+-------------+------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+