MySQL Bugs: #25996: MySQL goes to 99% CPU usage when client is disconnecting

Bug #25996	MySQL goes to 99% CPU usage when client is disconnecting
Submitted:	31 Jan 2007 23:03	Modified:	2 Mar 2007 7:47
Reporter:	bbkr	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.0.32	OS:	Linux (Gentoo Linux x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	cpu, usage

Description:
CPU usage goes to 99% and nearly kills system when some client is disconnecting (even mysql console tool). i did fresh installation. no old tables were present.

How to repeat:
############# HOW TO REPEAT ######################
1. install MySQL-5.0.32 database
2. run 'mysql_install_db'
3. run 'mysql' to enter the console
4. type \q to quit console
############# /HOW TO REPEAT #####################

here is some additional info checked on 2nd mysql console while CPU was at 99%:

no suspicious processes 

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  2 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

and here is InnoDB status:

mysql> show innodb status\G;
*************************** 1. row ***************************
Status:
=====================================
070131 23:54:36 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 34 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2, signal count 2
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 4, OS waits 2; RW-excl spins 1, OS waits 0
------------
TRANSACTIONS
------------
Trx id counter 0 1280
Purge done for trx's n:o < 0 0 undo n:o < 0 0
History list length 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 1281, OS thread id 1141680464
MySQL thread id 2, query id 6 localhost root
show innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
41 OS file reads, 3 OS file writes, 3 OS fsyncs
1.21 reads/s, 74027 avg bytes/read, 0.09 writes/s, 0.09 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 0, node heap has 0 buffer(s)
0.00 hash searches/s, 0.53 non-hash searches/s
---
LOG
---
Log sequence number 0 43655
Log flushed up to   0 43655
Last checkpoint at  0 43655
0 pending log writes, 0 pending chkp writes
8 log i/o's done, 0.24 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 37557328; in additional pool allocated 1724672
Buffer pool size   1024
Free buffers       989
Database pages     35
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 35, created 0, written 0
1.03 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 685 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 1281, id 1140881744, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.00 sec)

Thank you for the report.

Which package do you use? (I.e. provide accurate url you download it from)

you were right. here is piece of emerge mysql package under Gentoo. 

>>> Emerging (1 of 1) dev-db/mysql-5.0.32 to /
 * mysql-extras-20070108.tar.bz2 MD5 ;-) ...                                                                                                                   [ ok ]
 * mysql-extras-20070108.tar.bz2 RMD160 ;-) ...                                                                                                                [ ok ]
 * mysql-extras-20070108.tar.bz2 SHA1 ;-) ...                                                                                                                  [ ok ]
 * mysql-extras-20070108.tar.bz2 SHA256 ;-) ...                                                                                                                [ ok ]
 * mysql-extras-20070108.tar.bz2 size ;-) ...                                                                                                                  [ ok ]
 * mysql-5.0.32.tar.gz MD5 ;-) ...                                                                                                                             [ ok ]
 * mysql-5.0.32.tar.gz RMD160 ;-) ...                                                                                                                          [ ok ]
 * mysql-5.0.32.tar.gz SHA1 ;-) ...                                                                                                                            [ ok ]
 * mysql-5.0.32.tar.gz SHA256 ;-) ...                                                                                                                          [ ok ]
 * mysql-5.0.32.tar.gz size ;-) ...                                                                                                                            [ ok ]
 * checking ebuild checksums ;-) ...                                                                                                                           [ ok ]
 * checking auxfile checksums ;-) ...                                                                                                                          [ ok ]
 * checking miscfile checksums ;-) ...                                                                                                                         [ ok ]
 * checking mysql-5.0.32.tar.gz ;-) ...                                                                                                                        [ ok ]
 * checking mysql-extras-20070108.tar.bz2 ;-) ...                                                                                                              [ ok ]
 * Berkeley DB support is deprecated and will be removed in future versions!
>>> Unpacking source...
 * MySQL DATADIR is /var/lib/mysql
 * Previous datadir found, it's YOUR job to change
 * ownership and take care of it
>>> Unpacking mysql-5.0.32.tar.gz to /var/tmp/portage/dev-db/mysql-5.0.32/work
>>> Unpacking mysql-extras-20070108.tar.bz2 to /var/tmp/portage/dev-db/mysql-5.0.32/work
 * using '035_x86_asm-pic-fixes-4.1.12.patch'
 * >    remove page relocations
 * >    Most part of the original patch has already been accepted by MysQL,
 * >    here is the remaining.
 * >    _many_ thanks to pageexec@freemail.hu
 * using '105_all_mysql_config_cleanup.patch'
 * >    fix bug #156301 mysql_config wrongly retains too much info from CFLAGS
 * using '702_all_trigger-rename-fail-as-root-5.0.26.patch'
 * >    portage normally ran as root, MySQL tests are designed to be used
 * >    with lower priviledges
 * using '703_all_test-rpl_rotate_logs-5.0.21.patch'
 * using '704_all_disable_mybug_9735_test.patch'
 * >    disable a test that fail on longtext field lenght, the expected value is
 * >    three times the returned one, look like a multibyte character related
 * >    failure.
 * using '705_all_we-are-in-2007-now.patch'
 * >    the view.test is year dependant
 * Applying various patches (bugfixes/updates) ...
 *   105_all_mysql_config_cleanup.patch ...                                                                                                                    [ ok ]
 *   702_all_trigger-rename-fail-as-root-5.0.26.patch ...                                                                                                      [ ok ]
 *   703_all_test-rpl_rotate_logs-5.0.21.patch ...                                                                                                             [ ok ]
 *   704_all_disable_mybug_9735_test.patch ...                                                                                                                 [ ok ]
 *   705_all_we-are-in-2007-now.patch ...                                                                                                                      [ ok ]
 * Done with patching
 * Reconfiguring dir '.'
....

they use mysql-5.0.32.tar.gz (i dont know if it's your official package, md5 is ccdcddf97fab88d7d6bd43dff73bf61b) from gentoo sources repository but they also add some patches to it. ill try to remove those patches and build it again to see what will happen.

>ill try to remove those patches and build it again to see what will happen.

Even would be better if you try with our 5.0.33 sources accessible from http://dev.mysql.com/downloads/mysql/5.0.html

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".