Bug #62117 Large Batch Update causes apparent large memory leaks and crashes server
Submitted: 8 Aug 2011 19:53 Modified: 10 Aug 2011 10:47
Reporter: Greg Kemnitz Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S2 (Serious)
Version:5.1.58-enterprise OS:Solaris
Assigned to: CPU Architecture:Any

[8 Aug 2011 19:53] Greg Kemnitz
Description:
We're using mysql-connector-java-5.1.17.jar.  Our bulk loader application issues up to 500,000 batch updates at a time, and has recently started crashing the database engine on a particular dataset.  Since the db process size occasionally grows by 4GB before crashing, and this growth correlates with the batch updates firing, this appears to be a memory leak.  

If we disable "rewriteBatchedStatements=true", the memory leak appears to go away (although our app runs much slower).

This looks like this bug:

http://bugs.mysql.com/bug.php?id=47387.

How to repeat:
We're not sure why this happens infrequently, but on this dataset, the crash is pretty much constant.  To dup, try various combinations of very large bulk update statements with "rewriteBatchedStatements=true" set.

Suggested fix:
Fix the memory leaks :)
[8 Aug 2011 21:14] Miguel Solorzano
Could you please provide the *.err file. Thanks.
[8 Aug 2011 23:53] Greg Kemnitz
Mysqld run log

Attachment: mysqld.log (application/octet-stream, text), 7.47 KiB.

[9 Aug 2011 14:25] Shane Bester
Hi Greg, 

The libc function pthread_create crashed because the server was out of memory.  This isn't suprizing really.

/lib/amd64/libc.so.1:0xdc6c6
/lib/amd64/libc.so.1:0xd0fa2
/lib/amd64/libc.so.1:0xdc2ce [ Signal 10 (BUS)]
/lib/amd64/libc.so.1:0xd87b5
/lib/amd64/libc.so.1:pthread_create+0xfe
/opt/mysql/mysql-advanced-5.1.58-solaris10-x86_64/bin/mysqld:__1cbIcreate_thread_to_handle_connection6FpnDTHD__v_+0x8d
<cut>

Are you sure this isn't just a huge allocation of memory due to the number of items prepared, rather than a leak?  How much memory does this server have?
[9 Aug 2011 18:01] Greg Kemnitz
innodb is configured to use 30GB, and when not encountering this problem (but running other apps that actively load the database for days), the mysql process grows to 35GB.  When running this, this mysql process grows to about 100GB before failing, either by crashing or by aborting the query with an out of memory error.

This machine has 128GB of RAM on it and is running one other idle mysql process.
[9 Aug 2011 19:09] Sveta Smirnova
Thank you for the feedback.

Bug you mentioned is most likely duplicate of bug #27863 fixed in version 5.5.5-m3. Can you test your batch insert with latest 5.5 (5.5.14) to check if you hit same issue?
[10 Aug 2011 2:08] Greg Kemnitz
Our preliminary tests with 5.5.14 appear to be working.  We'll need to go through our acceptance cycle and such, but things look good so far.  Thanks.
[10 Aug 2011 10:47] Sveta Smirnova
Thank you for the feedback.

Closing as duplicate of bug #27863