MySQL Bugs: #62117: Large Batch Update causes apparent large memory leaks and crashes server

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

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

Could you please provide the *.err file. Thanks.

Mysqld run log

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

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?

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.

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?

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.

Thank you for the feedback.

Closing as duplicate of bug #27863