MySQL Bugs: #56904: Out of memory (Needed 294920 bytes)

Bug #56904	Out of memory (Needed 294920 bytes)
Submitted:	21 Sep 2010 17:17	Modified:	26 Jan 2011 16:21
Reporter:	Cyrill Müller	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	5.1.41 (64bit)	OS:	Linux (2.6.16.60)
Assigned to:		CPU Architecture:	Any

Description:
Hello
We have in production sometime the problem, that a client receives the message
"Out of memory (Needed XXX bytes)", we don't see the message in the server.log nor do we have a memory problem on the server (still 20G Memory free and no message in the message log). If we restart the session the same error occures. After a restart from the mysql server, everything is working fine again. The statments which makes problems are delete statments with a huge "in list" (10'000 and more elements). If we make the "in list" smaller and it is working also.
Now I've some question, as I didn't find any really usefull information.

In the MySQL documentation is written:
"...While a statement is running, a copy of the current statement string is also allocated." Now, where is it allocated, in which buffer? (Probably on the server side, as reconnect doesn't fix the error?) Is there any kind of fragmentation in this memory structure (how to defrag?)? In Oracle I know the shared pool which is used also for the sql statments, is there also some kind of pool in MySQL (Documentation?) how to size it, and how is defragmentation/coalescing done (or is there no problem for defragmentation, is there no need for a continous memory for a sql statement?)? We didn't have this problem on solaris (older mysql 32bit).

Sorry for those question, but I couldn't find any usefull information, if this is normal or a bug?

How to repeat:
I don't have any testcase, as we also have this only on the production system, where there is much more workload.

Thank you for the bug report. Duplicate of: http://bugs.mysql.com/bug.php?id=52272.

Please, check if the same problem ever happens with a newer version, 5.1.50.

Thanks, for the information. I know this is for bugs, but in my opinion that is a bug. Because of the following:
- We us 64bit MySQL we still have 20G Free Memory at the time of occurence.
- We have a delete statement, which doesn't us result caching.
- I didn't find any documentation about the memory structure, for sql statement (NOT results) caching.
- If we know there is some kind of known issue in my current version, we have to upgrade, but than we need to know why this kind of thing happens.

Changed version to 64bit

Please, send your my.cnf file content, entire error log, exact DELETE statement that leads to this error message and EXPLAIN results for SELECT * statement with the same WHERE clause as in that DELETE, and the results of

free
uname -a

Linux commands. All these is needed to check for possible known bugs that you are affected by on your old 5.1.41.

It may be much faster and easier to check if 5.1.50 is also affected, as if this is an old bug that is already fixed you will have to upgrade anyway...

I've still not found any information, about the parsing and optimization process in mysql, where the memory allocation for the shared thing ist done. The "needed size" in the error message is almost exactly the size of the statement.

I will attach the error, the explain, and all other requested information.

my.cnf:
###########################################################
[mysqld1]
port                = 3306
user                = mysql
log-bin             = /dblog/mylog/mysqld1/mysqld-bin
sync_binlog         = 1
#max_binglog_size -> Default and Max 1G
binlog_format         = ROW
transaction-isolation = READ-COMMITTED

# Log/Socket files
socket              = /tools/mysql/admin/mysqld1/socket/mysqld.sock
pid-file            = /tools/mysql/admin/mysqld1/socket/mysqld.pid
log-error           = /tools/mysql/admin/mysqld1/log/mysqld.err
slow_query_log_file = /tools/mysql/admin/mysqld1/log/mysql_slow_query.log
slow_query_log      = ON

# Directories
tmpdir              = /dbdata/mydata/mysqld1/tmp
datadir             = /dbdata/mydata/mysqld1
basedir             = /usr

#Div
max_allowed_packet  = 1G
sort_buffer_size    = 8M
read_buffer_size    = 2M
read_rnd_buffer_size= 8M
net_buffer_length   = 2K
thread_cache_size   = 350
max_connections     = 350
long_query_time     = 1
query_cache_size    = 32000000

#Replication
server-id       = 1

#MyISAM
myisam_sort_buffer_size   = 64M
myisam_max_sort_file_size = 128G
key_buffer_size           = 10G
table_cache               = 32

#InnoDB
innodb_data_home_dir      = /dbdata/mydata/mysqld1/innodb
innodb_data_file_path     = ibdata1:1000M:autoextend
innodb_log_group_home_dir = /dbdata/mydata/mysqld1/innodblog
innodb_buffer_pool_size   = 37000M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size      = 500M
innodb_log_buffer_size    = 8M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method       = O_DIRECT
innodb_lock_wait_timeout  = 150
innodb_file_per_table
innodb_support_xa         = 1

###########################################################

other information.

Attachment: error_and_explain.zip (application/x-zip-compressed, text), 252.00 KiB.

Hello
I still believe it is not a duplicate of the other bug, as we are doing a delete (no result set problem).
regards
Cyrill

Hello,
I really don't think this is a duplicate of Bug 52272. Please remove this, or tell me why this should be a duplicate? As only the error message is the same?
thanks and regards
Cyrill

We are still waiting for the paste of output of:
free
uname -a

Sorry for that!

# uname -a
Linux xxx 2.6.16.60-0.42.7-smp #1 SMP Tue Nov 3 11:20:42 UTC 2009 x86_64 x86_64 x86_64 GNU/Linux

# free -m
             total       used       free     shared    buffers     cached
Mem:         72435      72219        215          0       1816      21030
-/+ buffers/cache:      49373      23062
Swap:        24583      11435      13148

# cat /proc/meminfo
MemTotal:     74174280 kB
MemFree:        233608 kB
Buffers:       1860684 kB
Cached:       21529404 kB
SwapCached:   10233736 kB
Active:       52444892 kB
Inactive:     19553400 kB
HighTotal:           0 kB
HighFree:            0 kB
LowTotal:     74174280 kB
LowFree:        233608 kB
SwapTotal:    25173812 kB
SwapFree:     13463752 kB
Dirty:            7428 kB
Writeback:           0 kB
AnonPages:    48561776 kB
Mapped:         138752 kB
Slab:          1765704 kB
CommitLimit:  62260952 kB
Committed_AS: 60103884 kB
PageTables:     113224 kB
VmallocTotal: 34359738367 kB
VmallocUsed:    275952 kB
VmallocChunk: 34359462359 kB
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB

Please, check if the same problem is repeatable with a newer version, 5.1.54.

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".