dalouis wrote:

> I saw this a bit too late but i got the error again and i made sure to
> do a 'top' and show full processlist before and during the problem:
>
> show full processlist doesnt seem to show anything very different
> before(normal state)/during the problem, this list is the same before
> or after the problem. There is one gui client connected and a few
> clients using jdbc which reflect the connection pool from apache.
>
> mysql> show full processlist;
>
+----+-----------+--------------------+-----------+---------+------+-------+----
> -------------------+
> | Id | User | Host | db | Command | Time |
> State | Inf
> o |
>
+----+-----------+--------------------+-----------+---------+------+-------+----
> -------------------+
> | 1 | mydb| 10.10.0.143:1678 | mydb| Sleep | 0 | NULL | NUL
> L |
> | 2 | mydb| localhost:7846 | mydb| Sleep | 176 | NULL | NUL
> L |
> | 11 | mydb| 10.10.136.16:52470 | mydb| Sleep | 892 | NULL | NUL
> L |
> | 12 | mydb| 10.10.136.16:52471 | mydb| Sleep | 408 | NULL | NUL
> L |
> | 13 | mydb| 10.10.135.16:52711 | mydb| Sleep | 3273 | NULL | NUL
> L |
> | 14 | mydb| 10.10.135.16:52712 | mydb| Sleep | 4162 | NULL | NUL
> L |
> | 19 | mydb| 10.10.0.28:4827 | mydb| Sleep | 147 | NULL | NUL
> L |
> | 22 | mydb| 10.10.129.16:53925 | mydb| Sleep | 839 | NULL | NUL
> L |
> | 27 | root | localhost | NULL | Query | 0 |
> NULL | sho
> w full processlist |
<snip>
> 13:42:46 up 72 days, 1:41, 3 users, load average: 1.04, 1.05, 1.00
> 64 processes: 63 sleeping, 1 running, 0 zombie, 0 stopped
> CPU states: cpu user nice system irq softirq iowait
> idle
> total 25.1% 0.0% 0.0% 0.0% 0.0% 0.0%
> 74.8%
> cpu00 100.0% 0.0% 0.0% 0.0% 0.0% 0.0%
> 0.0%
> cpu01 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
> 100.0%
> cpu02 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
> 100.0%
> cpu03 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
> 100.0%
> Mem: 2037012k av, 2019280k used, 17732k free, 0k shrd,
> 136212k buff
> 1523004k actv, 283060k in_d, 42728k in_c
> Swap: 2097144k av, 1017248k used, 1079896k free
> 470020k cached
>
> PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU
> COMMAND
> 4158 mysql 15 0 806M 710M 3348 S 24.9 35.7 2412m 2
> mysqld
> 32647 mysql 15 0 1360 1360 968 R 0.3 0.0 0:00 3 top
> 1 root 15 0 520 468 444 S 0.0 0.0 0:33 2 init
<snip>
>
> Thanks for any insight into this problem.


The status of the process is S (sleep) but it has consumed 24.9% of the
available CPU cycles (99.6% of 1 CPU) in the last time slice evaluated by
top. This means that it is not constantly active.

To me this suggests that one of your connections is issuing thousands upon
thousands of very fast queries one after another. I have had this happen
to me before, for some reason SHOW PROCESSLIST seems to be unable to show
the status of a query that returns very quickly, opting only to show the
very short sleep state between queries, perhaps there is a flaw in the way
it is implemented.

You can see if this is the case by asking:

SHOW STATUS LIKE "Questions";

If it increases by several thousand per second then what I have said is
indeed the case.


--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png