Maciek,

looks like InnoDB believes 8 queries are still inside it.

This line shows that at least there are uncommitted transactions:

There is a very easy workaround to the problem. Set

set-variable=innodb_thread_concurrency=500

in my.cnf. That disables the concurrency checking. I added the limit 8 in an attempt to solve the Linux 'thread thrashing' problem, but the problem seems to have disappeared from recent glibc's.

But I would like to know why there was a hang. Please send me the full output of the InnoDB Monitor. Please send me also your my.cnf.

You get the output of the InnoDB Monitor to the client with

SHOW INNODB STATUS;

Run that sometimes and look if the number of queries inside InnoDB seems to drift upwards, showing a 'leak' in the bookkeeping.

Look what SHOW PROCESSLIST and SHOW INNODB STATUS say when there is a hang.

I have also added diagnostic code to upcoming 4.0.10 to track down this.

Do you use LOCK TABLES? If you wrap several SELECTs inside LOCK TABLES, the hang is possible.

Regards,

Heikki heik...@innodb.com

............................. Subject: MySQL 4.0.6/InnoDB database stops executing queries From: Maciek Dobrzanski Date: Sun, 12 Jan 2003 20:13:21 +0100

--------------------------------------------------------------------------

--

----

Hi,

I've got MySQL 4.0.6 w/InnoDB running on Linux server (Slackware 8.1, glibc 2.2.5). Recently strange thing started to happen. Almost every two days MySQL simply stops responding to the queries, which causes that @@global.max_connections number of clients connect to the db and they just hang there waiting for the reply which does not come. Today I was there when it all happend again, so I ran InnoDB monitor. The query (CREATE TABLE innodb_monitor...) hung just as all other did (I had to kill MySQL monitor), but it must have been executed by innodb engine itself, because entries started to show up in the error log:

===================================== 030112 19:31:01 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 16 seconds

---------- SEMAPHORES

---------- OS WAIT ARRAY INFO: reservation count 62627, signal count 52952 Mutex spin waits 837952, rounds 4832012, OS waits 37017 RW-shared spins 15305, OS waits 8375; RW-excl spins 2900, OS waits 978

------------ TRANSACTIONS

------------ Trx id counter 0 1088252910 Purge done for trx's n:o < 0 1088252908 undo n:o < 0 0 Total number of lock structs in row lock hash table 12 LIST OF TRANSACTIONS FOR EACH SESSION:

says 'not started' <<<

... output truncated!

-------- FILE I/O

--------

------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX

------------------------------------- Ibuf for space 0: size 1, free list len 55, seg size 57, 2445 inserts, 2445 merged recs, 2353 merges Hash table size 3735439, used cells 247946, node heap has 253 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s

--- LOG

--- Log sequence number 19 3457418715 Log flushed up to 19 3457418715 Last checkpoint at 19 3457418715 0 pending log writes, 0 pending chkp writes 5167 log i/o's done, 0.00 log i/o's/second

---------------------- BUFFER POOL AND MEMORY

---------------------- Total memory allocated 1047435910; in additional pool allocated 5987840

-------------- ROW OPERATIONS

-------------- 8 queries inside InnoDB, 590 queries in queue Main thread process no 3196, state: waiting for server activity Number of rows inserted 2709, updated 343091, deleted 14, read 6550629 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

---------------------------- END OF INNODB MONITOR OUTPUT ============================

"8 queries inside InnoDB, 590 queries in queue" - this does not seem to change when it all happens...

Any ideas ?

(I do not know How-To-Repeat:)

Regards, Maciek