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 [EMAIL PROTECTED]
Best regards,
Heikki Tuuri
Vice President, server technology
Oracle Corp.
----- Original Message -----
From: "Devananda" <[EMAIL PROTECTED]>
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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
