MySQL Bugs: #43952: client aborted due to insufficient memory despite lots of memory available

Bug #43952	client aborted due to insufficient memory despite lots of memory available
Submitted:	30 Mar 2009 7:40	Modified:	1 Dec 2009 11:42
Reporter:	Andreas Bourges	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.67	OS:	Linux (SLES 9)
Assigned to:		CPU Architecture:	Any

Description:
mysql-server aborts client with the following message:

090328  8:05:05 [Warning] Aborted connection 1252425 to db: 'isarflow' user: 'retsaw' host: 'localhost' (Out of memory; check
if mysqld or some other process uses all av)
090328  8:08:09 [Warning] Aborted connection 1252507 to db: 'isarflow' user: 'retsaw' host: 'localhost' (Out of memory; check
if mysqld or some other process uses all av)
090328 18:34:07 [Warning] Aborted connection 1264884 to db: 'isarflow' user: 'retsaw' host: 'localhost' (Out of memory; check
if mysqld or some other process uses all av)

system-monitor shows that there's plenty of memory available. Also, the server has been running for more than 4 weeks without any problem (having always a similar load). Other servers (having even more load) don't show the problem (yet).

The complete setup contains 12 servers, 4 of them showing the problem after ~4 weeks of uptime. mysql-restart solves the problem.

Unfortunately we cannot provide show variables/show status before the mysql-restart - sorry about that.

How to repeat:
insert into memory tables (~300.000 records/minute) and do a insert-select into a myISAM table from the memory table. Problem occured after ~4 weeks of uptime on several identical configured servers within a few days.

I think the reason is simple: you allow to big temporary variables to be hold in memory. Look:

| max_heap_table_size             | 536870912     

tmp_table size is the same. So, you can use up to 512M of memory per temporary table. Very few of them needed to get out of memeory situation even with your 16G or so of RAM. Please, check.

solution is not that simple: this behaviour is related to memory fragmentation. We purchased mysql support in the meantime and after several month we finally found the solution: upgrade to newer kernel

see internal ticket [#38218] @ support.mysql.com

So, this problem was not a result of any bug in MySQL code.