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]
