Deva,

it is probably this insert operation that is stuck, and is holding an S-latch of an index tree in emails_history_30_30:

---TRANSACTION 0 1856588555, ACTIVE 858 sec, process no 1792, OS thread
id 2445845440 inserting, thread declared inside InnoDB 318
mysql tables in use 1, locked 1
1 lock struct(s), heap size 320, undo log entries 684
MySQL thread id 42, query id 6575 192.168.1.36 webserver update
INSERT INTO emails_history_30_30(email_id, mailing_id, action_type,
xtime) VALUES (

The insert operation below wants to get an X-latch on the tree. Apparently, it wants to make a B-tree page split.

---TRANSACTION 0 1856601297, ACTIVE 801 sec, process no 1792, OS thread
id 2450451904 inserting, thread declared inside InnoDB 160
mysql tables in use 1, locked 1
1 lock struct(s), heap size 320, undo log entries 341
MySQL thread id 148, query id 22800 192.168.1.36 webserver update
INSERT INTO emails_history_30_30(email_id, mailing_id, action_type,
xtime) VALUES (

The question is why the first insert is does no progress.

"undo log entries 684"

If you look further into the .err log, do you see the first insert operation progressing at all, or does the number of undo log entries stay at 684?

The output below shows that InnoDB is probably doing a very active insert buffer merge.

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1303861436; in additional pool allocated 12728448
Buffer pool size   65536
Free buffers       0
Database pages     65532
Modified db pages  14
Pending reads 22
Pending writes: LRU 0, flush list 0, single page 0
Pages read 91019, created 152, written 113129
91.49 reads/s, 0.00 creates/s, 135.55 writes/s
Buffer pool hit rate 944 / 1000
--------------
ROW OPERATIONS
--------------
8 queries inside InnoDB, 288 queries in queue
Main thread process no. 1792, id 2434898496, state: doing insert buffer

The question is why several inserts are stuck and do not progress. They are not waiting for any semaphore.

Do you have enough RAM in your computer?

Regards,

Heikki


----- Original Message ----- From: "Devananda" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Thursday, October 06, 2005 11:02 PM
Subject: Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)


Heikki Tuuri wrote:
Deva,

please post those transactions which have been active more than 800
seconds. The thread holding the RW-latch at 0x448286bc should be among
those.

Regards,

Heikki


The thread holding that RW-latch is the first of these. I've removed any
sensitive information but left as much of the query as possible.


---TRANSACTION 0 1856601297, ACTIVE 801 sec, process no 1792, OS thread
id 2450451904 inserting, thread declared inside InnoDB 160
mysql tables in use 1, locked 1
1 lock struct(s), heap size 320, undo log entries 341
MySQL thread id 148, query id 22800 192.168.1.36 webserver update
INSERT INTO emails_history_30_30(email_id, mailing_id, action_type,
xtime) VALUES (
---TRANSACTION 0 1856601294, ACTIVE 802 sec, process no 1792, OS thread
id 2445059520 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 167, query id 22813 192.168.1.52 webserver statistics
SELECT * FROM companies WHERE company_id =
Trx read view will not see trx with id >= 0 1856601295, sees < 0 1856587424
---TRANSACTION 0 1856601293, ACTIVE 802 sec, process no 1792, OS thread
id 2450255424 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 163, query id 22802 192.168.1.52 webserver statistics
SELECT * FROM emails_9_19 WHERE
Trx read view will not see trx with id >= 0 1856601294, sees < 0 1856587424
---TRANSACTION 0 1856601280, ACTIVE 802 sec, process no 1792, OS thread
id 2451766592 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 170, query id 22788 192.168.1.37 webserver Sending data
SELECT * FROM mta_servers WHERE ip IN(
Trx read view will not see trx with id >= 0 1856601281, sees < 0 1856587424
---TRANSACTION 0 1856601279, ACTIVE 802 sec, process no 1792, OS thread
id 2446827840 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 169, query id 22786 192.168.1.37 webserver Sending data
SELECT * FROM mta_servers WHERE ip IN(
Trx read view will not see trx with id >= 0 1856601280, sees < 0 1856587424
---TRANSACTION 0 1856601274, ACTIVE 803 sec, process no 1792, OS thread
id 2449862464 waiting in InnoDB queue
mysql tables in use 1, locked 1
1 lock struct(s), heap size 320, undo log entries 1002
MySQL thread id 127, query id 22756 192.168.1.231 webserver update
INSERT INTO emails_history_30_6(email_id, mailing_id, action_type,
xtime) VALUES (
---TRANSACTION 0 1856601269, ACTIVE 803 sec, process no 1792, OS thread
id 2450058944 waiting in InnoDB queue
mysql tables in use 2, locked 0
MySQL thread id 162, query id 22751 192.168.1.232 webserver Sending data
SELECT e.email_id, e.email_address, e.status, e.first_name, e.last_name,
e.gender, e.dob, e.state, e.zipcode, e.country, e.list_source_id,
e.bounce_timestamp, e.openclick, e.last_bounce_code FROM
email.emails_17_13 AS e LEFT JOIN suppression_lists.client_284 AS csl ON
e.email_address = csl.email_add
Trx read view will not see trx with id >= 0 1856601270, sees < 0 1856587424
---TRANSACTION 0 1856588861, ACTIVE 852 sec, process no 1792, OS thread
id 2446238400 inserting, thread declared inside InnoDB 267
mysql tables in use 1, locked 1
1 lock struct(s), heap size 320, undo log entries 234
MySQL thread id 36, query id 7077 192.168.1.38 webserver update
INSERT INTO emails_history_30_19(email_id, mailing_id, action_type,
xtime) VALUES (
---TRANSACTION 0 1856588853, ACTIVE 854 sec, process no 1792, OS thread
id 2446434880 inserting, thread declared inside InnoDB 274
mysql tables in use 1, locked 1
1 lock struct(s), heap size 320, undo log entries 227
MySQL thread id 39, query id 7076 192.168.1.38 webserver update
INSERT INTO emails_history_30_27(email_id, mailing_id, action_type,
xtime) VALUES (
---TRANSACTION 0 1856588834, ACTIVE 855 sec, process no 1792, OS thread
id 2449665984 inserting, thread declared inside InnoDB 15
mysql tables in use 1, locked 1
1 lock struct(s), heap size 320, undo log entries 486
MySQL thread id 45, query id 7029 192.168.1.231 webserver update
INSERT INTO emails_history_30_9(email_id, mailing_id, action_type,
xtime) VALUES (
---TRANSACTION 0 1856588820, ACTIVE 855 sec, process no 1792, OS thread
id 2446631360 inserting, thread declared inside InnoDB 73
mysql tables in use 1, locked 1
1 lock struct(s), heap size 320, undo log entries 428
MySQL thread id 60, query id 6997 192.168.1.37 webserver update
INSERT INTO emails_history_30_16(email_id, mailing_id, action_type,
xtime) VALUES (
---TRANSACTION 0 1856588589, ACTIVE 857 sec, process no 1792, OS thread
id 2444273600 inserting, thread declared inside InnoDB 395
mysql tables in use 1, locked 1
1 lock struct(s), heap size 320, undo log entries 607
MySQL thread id 25, query id 6645 192.168.1.36 webserver update
INSERT INTO emails_history_30_5(email_id, mailing_id, action_type,
xtime) VALUES (
---TRANSACTION 0 1856588555, ACTIVE 858 sec, process no 1792, OS thread
id 2445845440 inserting, thread declared inside InnoDB 318
mysql tables in use 1, locked 1
1 lock struct(s), heap size 320, undo log entries 684
MySQL thread id 42, query id 6575 192.168.1.36 webserver update
INSERT INTO emails_history_30_30(email_id, mailing_id, action_type,
xtime) VALUES (
---TRANSACTION 0 1856587424, ACTIVE 861 sec, process no 1792, OS thread
id 2444470080 inserting, thread declared inside InnoDB 480
mysql tables in use 1, locked 1
1 lock struct(s), heap size 320, undo log entries 2025
MySQL thread id 3, query id 5280 192.168.1.38 webserver update
INSERT INTO emails_history_30_17(email_id, mailing_id, action_type,
xtime) VALUES (




----- Original Message ----- From: "Devananda" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Thursday, October 06, 2005 10:14 AM
Subject: Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)


Heikki Tuuri wrote:

Deva,

please post the complete output except the transaction data.

Regards,

Heikki


Please let me know if there is any additional data I can provide to help
resolve this.


=====================================
051005  7:18:17 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 31277, signal count 29361
--Thread 2450451904 has waited at btr0cur.c line 340 for 800.00 seconds
the semaphore:
X-lock on RW-latch at 0x448286bc created in file dict0dict.c line 3593
a writer (thread id 2450451904) has reserved it in mode  wait exclusive
number of readers 1, waiters flag 1
Last time read locked in file btr0cur.c line 347
Last time write locked in file btr0cur.c line 340
Mutex spin waits 294649, rounds 709548, OS waits 19013
RW-shared spins 10614, OS waits 5357; RW-excl spins 14659, OS waits 6152
------------
TRANSACTIONS
------------
Trx id counter 0 1856601574
Purge done for trx's n:o < 0 1856601271 undo n:o < 0 0
History list length 9
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
<< section cut >>
--------
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: complete io for buf page (read thread) ev set
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 11, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 1; buffer pool: 0
56937 OS file reads, 79359 OS file writes, 13851 OS fsyncs
49.00 reads/s, 30490 avg bytes/read, 96.68 writes/s, 18.50 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 30091, free list len 19083, seg size 49175, is
not empty
Ibuf for space 0: size 30091, free list len 19083, seg size 49175,
24395 inserts, 318393 merged recs, 60206 merges
Hash table size 4425293, used cells 3168, node heap has 4 buffer(s)
0.00 hash searches/s, 281.36 non-hash searches/s
---
LOG
---
Log sequence number 115 1818376139
Log flushed up to   115 1818375712
Last checkpoint at  115 1818375695
0 pending log writes, 0 pending chkp writes
7992 log i/o's done, 10.69 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1303861436; in additional pool allocated 12728448
Buffer pool size   65536
Free buffers       0
Database pages     65532
Modified db pages  14
Pending reads 22
Pending writes: LRU 0, flush list 0, single page 0
Pages read 91019, created 152, written 113129
91.49 reads/s, 0.00 creates/s, 135.55 writes/s
Buffer pool hit rate 944 / 1000
--------------
ROW OPERATIONS
--------------
8 queries inside InnoDB, 288 queries in queue
Main thread process no. 1792, id 2434898496, state: doing insert buffer
merge
Number of rows inserted 26399, updated 7272, deleted 0, read 851771
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to