Bug #61536 MySQL intentional server crash on semaphore wait from INSERT
Submitted: 16 Jun 2011 13:46 Modified: 30 Jan 2013 14:21
Reporter: Jeff Gilchrist Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.5.12, 5.5.16 OS:Windows (Windows XP 64bit)
Assigned to: CPU Architecture:Any
Tags: crash, innodb, insert, lock, Semaphore

[16 Jun 2011 13:46] Jeff Gilchrist
Description:
Periodically my MySQL 5.5.12 server (64bit version running on Windows with 4GB RAM, configured as MySQL server) will crash intentionally from a long semaphore wait. The InnoDB table is about 4.7GB and there are almost exclusively INSERT INTO operations performed on that table so I'm not sure why the table would have any locks.

The MySQL server log shows warnings about semaphore waits, most of them release before the server intentionally crashes itself, but periodically the server will crash itself.

This also happened with 5.1.56, upgrading to 5.5 did not help.  I installed a bran new 5.5.12 server instance, exported the data from the 5.1 server into an .sql container, then imported the data back into the new 5.5.12 server so started fresh with the database but am still seeing the same problem.  I left the default settings during the MySQL server configuration the only options I enabled were the following:

#This option makes InnoDB to store each created table into its own .ibd file.
innodb_file_per_table

#Enter a name for the error log file. Otherwise a default name will be used.
log-error

How to repeat:
Just let the server run and the errors will show up in the log, and the server will crash maybe once a week.  I will attache the error log and my configuration file.
[16 Jun 2011 13:47] Jeff Gilchrist
MySQL server configuration file

Attachment: my.ini (text/css), 8.90 KiB.

[16 Jun 2011 13:49] Jeff Gilchrist
MySQL log with intentional crash output

Attachment: crashlog.txt (text/plain), 6.20 KiB.

[16 Jun 2011 14:03] Valeriy Kravchuk
How much RAM do you have on that machine?
[16 Jun 2011 14:07] Jeff Gilchrist
As mentioned at the top of the comments, the machine has 4GB of RAM, task manager typically reports about 2GB physical RAM in use and just under 2GB free.
[16 Jun 2011 14:33] Valeriy Kravchuk
OK, got it. This looks really similar to bug #48192, bug #24745 and other similar reports. 

Please, check if loading the same data with innodb_file_per_table commented out solves the problem.
[16 Jun 2011 14:51] Jeff Gilchrist
Thanks for the comments. Just to clarify, do I just need to comment out "innodb_file_per_table" and restart the server, or do I need to export the data and re-import it again after that feature has been disabled?
[16 Jun 2011 14:54] Valeriy Kravchuk
You have to comment that setting out, restart server and re-import data (with dropping and re-creating the table). Otherwise that separate .ibd file will still be used for old table.
[17 Jun 2011 14:37] Jeff Gilchrist
I have several tables on this DB, exported the main 4GB+ table, commented out the separate tables command, and restarted MySQL.  I dropped the old table, and imported the data back in, so it is all now in the ibdata1 file.

I checked the logs today and did not see any warning messages about semaphore waits.  I needed to insert some data into another table which for now is still in the separate innodb file, and I noticed almost immediately there were wait issues using WorkBench, I could see two inserts, one for each table that happened at a similar time be stuck waiting.  Eventually they were resolved before the server crashed itself but it seems the problem is happening with INSERT statements to two different tables near the same time.

Is this a known problem?  Is it because the second table is still sitting as a separate innodb table and I would not see this if all of the data was in the single ibdata1 file?

I will now attach the log file with the semaphore warnings in case it helps give you more info.

Jeff.
[17 Jun 2011 14:38] Jeff Gilchrist
Semaphore wait warnings from INSERTs to two different tables at same time

Attachment: warning_log.txt (text/plain), 38.84 KiB.

[21 Jun 2011 13:31] Jeff Gilchrist
To give an update, it has now been 4 days since my previous comment and there has been no further semaphore warnings.

I highly suspect the problem has to do with using separate files for each table in InnoDB.

Is there anything else that a developer wants me to try to help track down this problem?  Or can anyone else reproduce this by having more than one InnoDB database using separate files and trying to insert a lot of data to both at the same time?
[23 Jun 2011 2:42] James Day
I mentioned this to the InnoDB developers because it's possible that there may be more tuning of the watchdog thread to reduce false alarm trips that can be done. The most recent builds (last couple) already have some work in that area.

They may ask you for InnoDB monitor output during before and during the problem to better see what the server was doing but do wait for them to do that.

When using innodb_file_per_table space is added as required. Without that it's pre-allocated. That makes adding lots of data with innodb_file_per_table slower and is a possible reason why the waits could become long enough to be problematic. Still seems unlikely to be the root cause, though - it's a very common operation.
[23 Jun 2011 13:50] Jeff Gilchrist
Thanks for the info.  It has now been a week and no more warning messages at all, no crashes, so whatever the reason whether it is delays in expanding the innodb file or not, keeping everything in one file has stopped the server crashing for me.

If any developers want further log files, I am happy to help debug the situation.
[17 Oct 2011 14:15] Mike Nicewarner
I am using MySQL 5.5.16 on Windows XP 64bit, 16GB RAM, mirrored 1TB drives for the database, and I'm experiencing the exact same problem.  I had never seen a semaphore wait warning, until I added the "innodb_file_per_table" line to my my.ini file.  Now I get a crash every morning at 2:30 when my main batch load process runs.  Therefore, I will confirm that this is a serious bug, and it is exactly as Jeff describes.  My logs look very similar, so I'm not sure if anyone wants to see them.

Thanks.
[25 Mar 2012 14:39] Valeriy Kravchuk
Please, check if this problem still happens with a recent version, 5.5.22.
[26 Apr 2012 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[26 Jan 2013 23:53] Victor Chang
I am having a similar problem with Version 5.5.29.  I have MySQL running on Windows XP 32-bit.  My INSERT data is around 1.6 MB.  The reason I am using "innodb_per_table" is so I can OPTIMIZE the table to reclaim disk space.
[30 Jan 2013 14:21] Matthew Lord
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not clearly the result of a verifiable bug.

If you can provide any kind of test case, then I'd be happy to try and verify it.

Without that, it will require a good deal of help to determine what happened and why, and if the watchdog thread should have fired at all, it tuning was an issue, if we had OS issues, thread thrashing issues, etc. This is not something appropriate for a bug report.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.