List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:October 9 2005 6:11am
Subject:Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)
View as plain text  
Deva,

hmm... this is mysterious. This might also be an OS bug. If you have the 
.err log from other crashes, please send it to heikki.tuuri@stripped

Best regards,

Heikki Tuuri
Vice President, server technology
Oracle Corp.

----- Original Message ----- 
From: "Devananda" <karnah805@stripped>
Newsgroups: mailing.database.myodbc
Sent: Friday, October 07, 2005 10:38 PM
Subject: Re: crashes in InnoDB with MySQL 4.1.14 (semaphore wait timeout)


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