List:General Discussion« Previous MessageNext Message »
From:Devananda Date:October 6 2005 7:58pm
Subject:Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)
View as plain text  
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" <karnah805@stripped>
> 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/mysql?unsub=1
>>
> 
> 

Thread
crashes in InnoDB with MySQL 4.1.14Devananda5 Oct
  • Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)Devananda5 Oct
Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)Heikki Tuuri6 Oct
  • Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)Devananda6 Oct
Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)Heikki Tuuri6 Oct
  • Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)Devananda6 Oct
Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)Heikki Tuuri7 Oct
  • Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)Devananda7 Oct
Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)Heikki Tuuri9 Oct