Bug #22746 MySQL memory grows, runs out of memory and process crashes
Submitted: 27 Sep 2006 17:54 Modified: 28 Sep 2006 9:29
Reporter: Stefan Burwitz Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.24a OS:Windows (Windows Server 2003)
Assigned to: CPU Architecture:Any

[27 Sep 2006 17:54] Stefan Burwitz
Description:
I have an application that writes a fair amount of data every 60 seconds to a single table (5 columns). At last check there were about 7 million rows and about 680MB of data. Once starting MySQL and my application the MySQL process grows steadily until it runs out of memory and finally crashes. I had the same issue with 4.1.18 and 4.1.21 and thought upgrading to the latest might solve the problem. I have also tried switching from Innodb to MyIsam storage engine with the same results. With each 15 second update the process memory grows by 2-3MB.

My table has five columns and each row has a maximum size of 29bytes. Every 60 seconds might see an insert of up ten/twenty thousand rows.

The server has 16GB of RAM and 8CPUs so plenty of resources which is not causing the issue. The process seems to blow up at around 1.3GB.

I am really not sure what to do now as I have tried adjusting the startup variables to no avail. The MySQL process used to crash much sooner when the InnoDB buffer size was 256MB - I increased it to 1024MB. I have set the option to flush to disk on each SQL statement.

My application is running on .NET 1.1 with Connector/Net 1.0.7.

How to repeat:
Write 10-20k rows of data in a burst every 60 seconds to the database.

CREATE TABLE STATISTICS_HISTORY
(
	GUID_High BIGINT UNSIGNED NOT NULL,
	GUID_Low BIGINT UNSIGNED NOT NULL,
	Timestamp DATETIME NOT NULL,
	Coverage TINYINT UNSIGNED NOT NULL,
	Value FLOAT(3) NOT NULL,
	PRIMARY KEY (GUID_High, GUID_Low, Timestamp)
)
;
[28 Sep 2006 7:09] Stefan Burwitz
The MySQL server error log contains many log entries of the type below.

060928  8:53:31 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

It also appears as if the memory issues are reported on selects rather than inserts.

My application inserts 200-300k rows per minute. The same table is pruned once per minute to remove stale entries. The same table is queried at a much lower rate to produce aggregate values for specified time periods.
[27 Jan 2007 19:06] Chad Catlett
I'm curious.. did you resolve this? If so what were your steps?