List:General Discussion« Previous MessageNext Message »
From:Devananda Date:October 7 2005 7:37pm
Subject:Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)
View as plain text  
Heikki Tuuri wrote:
> 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?

This particular entry was part of the last output before the server came 
down. If I look back in the log, I see that this transaction first 
appears when it had been active for 314 sec, and at that time, it had 
684 undo log entries (just the same number it had at 858 sec).

Looking at the error log, none of the active threads had any increase in 
undo log entries during the 9 minutes between the first innodb monitor 
output and the server crashing. After mysql restarted, innodb rolled 
back each of those transactions, undoing precisely as many rows as 
appeared in the first innodb monitor output for each transaction ID.

> 
> 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?

4GB RAM in the computer, dual Xeon 2.8GHz processors. I posted the full 
my.cnf and server details in my first post on this topic, but here again 
are the innodb specific settings:

innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_additional_mem_pool_size = 128M
innodb_buffer_pool_size = 1G
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 30

Is anything wrong with this? The server had been running fine for months 
with these settings ....

> 
> Regards,
> 
> Heikki
> 

Thanks for all your time!
Devananda vdv
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