Further FreeBSD 4.3 Issues (CPU hogging)

[Institute For Social Ecology]

Ken and all,

I wrote around a month back about some problems I was having with my
MySQL install in a freeBSD 4.2 system.  I received allot of helpful
advice on troubleshooting the problems (memory hogging issues).
Thanks to all.  To date, we have recently upgraded to mysqld Ver
3.23.39 for freebsd4.2, and freeBSD 4.3.

The problem I am now experiencing relates to CPU hogging.  When we did
the above upgrade, mysqld seemed to be acting reasonable in terms of
it's CPU and memory usage.  For whatever reason, mysqld is now
occupying 92.48% of one of our 266 mhz processors, even though mysqld
is presently receiving only 1-5 queries per minute (and pretty simple
queries at that).

Why then is mysqld taking up 92.48% of one of our processors, when a
week back it was taking up on average 10%?

I have included some output below that might be of use.  Any insight
would be of great help.

Regards,

Michael Caplan
Institute for Social Ecology

1118 Maple Hill Rd.
Plainfield, Vermont, 05667
USA

Tel./Fax. [phone]

http://www.social-ecology.org/


Mem: 34M Active, 2240K Inact, 20M Wired, 4156K Cache, 15M Buf, 884K
Free
Swap: 260M Total, 40M Used, 219M Free, 15% Inuse, 20K In

  PID USERNAME PRI NICE  SIZE    RES STATE  C   TIME   WCPU    CPU
COMMAND
  537 mysql     56   0 39388K  2460K CPU1   1  98.8H 92.48% 92.48%
mysqld


Processes running:

+-------+-------+-----------+------+---------+------+-------+---------
---------+
| Id    | User  | Host      | db   | Command | Time | State | Info
|
+-------+-------+-----------+------+---------+------+-------+---------
---------+
| 17665 | jan   | localhost | jan  | Sleep   | 626  |       |
|
| 17671 | daver | localhost | flag | Sleep   | 452  |       |
|
| 17672 | daver | localhost | flag | Sleep   | 167  |       |
|
| 17675 | daver | localhost | flag | Sleep   | 86   |       |
|
| 17676 | daver | localhost | flag | Sleep   | 136  |       |
|
| 17682 | root  | localhost |      | Query   | 0    |       | show
processlist |
+-------+-------+-----------+------+---------+------+-------+---------
---------+



+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| Aborted_clients          | 2954     |
| Aborted_connects         | 3        |
| Bytes_received           | 16332450 |
| Bytes_sent               | 51625490 |
| Connections              | 17684    |
| Created_tmp_disk_tables  | 0        |
| Created_tmp_tables       | 348      |
| Created_tmp_files        | 0        |
| Delayed_insert_threads   | 0        |
| Delayed_writes           | 0        |
| Delayed_errors           | 0        |
| Flush_commands           | 2        |
| Handler_delete           | 428      |
| Handler_read_first       | 2605     |
| Handler_read_key         | 97129    |
| Handler_read_next        | 618052   |
| Handler_read_prev        | 273      |
| Handler_read_rnd         | 194703   |
| Handler_read_rnd_next    | 3373669  |
| Handler_update           | 46570    |
| Handler_write            | 13392    |
| Key_blocks_used          | 801      |
| Key_read_requests        | 607043   |
| Key_reads                | 572      |
| Key_write_requests       | 14286    |
| Key_writes               | 5834     |
| Max_used_connections     | 31       |
| Not_flushed_key_blocks   | 0        |
| Not_flushed_delayed_rows | 0        |
| Open_tables              | 99       |
| Open_files               | 184      |
| Open_streams             | 0        |
| Opened_tables            | 228      |
| Questions                | 209184   |
| Select_full_join         | 6        |
| Select_full_range_join   | 0        |
| Select_range             | 785      |
| Select_range_check       | 0        |
| Select_scan              | 16327    |
| Slave_running            | OFF      |
| Slave_open_temp_tables   | 0        |
| Slow_launch_threads      | 1        |
| Slow_queries             | 0        |
| Sort_merge_passes        | 0        |
| Sort_range               | 17111    |
| Sort_rows                | 192823   |
| Sort_scan                | 8413     |
| Table_locks_immediate    | 92585    |
| Table_locks_waited       | 91       |
| Threads_cached           | 0        |
| Threads_created          | 17683    |
| Threads_connected        | 6        |
| Threads_running          | 1        |
| Uptime                   | 582432   |
+--------------------------+----------+