MySQL 4.0.6/InnoDB database stops executing queries

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:

>>> here goes very long list of transactions all of which have status that
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




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13464@lists.mysql.com
To unsubscribe, e-mail

Re: MySQL 4.0.6/InnoDB database stops executing queries

Maciek,

looks like InnoDB believes 8 queries are still inside it.

This line shows that at least there are uncommitted transactions:

> Total number of lock structs in row lock hash table 12

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
heikki.tuuri@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:

>>> here goes very long list of transactions all of which have status that
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




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13469@lists.mysql.com
To unsubscribe, e-mail

Re: MySQL 4.0.6/InnoDB database stops executing queries

Maciek,

I could add still some advice which will help in diagnosing this. Set
max_connections to some smaller value, e.g., 100, so that when the hang
comes you can use

SHOW INNODB STATUS;

to print the transaction info and it is not truncated. In 4.0.6 it prints
max 100 kB. I raised it to 200 kB in 4.0.10.

Also, you can try setting

set-variable=innodb_thread_concurrency=30

so that the concurrency counter is still enabled, but the hang will come
more rarely, if at all. Then you can use SHOW INNODB STATUS to trace from
day to day if the counter has increased. That would reveal whether there is
a 'leak' in the counter or if the hang is caused by some short peak in
activity.

Regards,

Heikki

----- Original Message -----
From: "Heikki Tuuri"
To:
Sent: Monday, January 13, 2003 12:25 AM
Subject: Re: MySQL 4.0.6/InnoDB database stops executing queries


> Maciek,
>
> looks like InnoDB believes 8 queries are still inside it.
>
> This line shows that at least there are uncommitted transactions:
>
> > Total number of lock structs in row lock hash table 12
>
> 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
> heikki.tuuri@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:
>
> >>> here goes very long list of transactions all of which have status that
> 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
>
>



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13470@lists.mysql.com
To unsubscribe, e-mail