MySQL Bugs: #48621: Out of memory (Needed 2514512 bytes)

Bug #48621	Out of memory (Needed 2514512 bytes)
Submitted:	9 Nov 2009 6:40	Modified:	9 Dec 2009 16:54
Reporter:	Ashokkumar S	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.1.36	OS:	Linux (CentOs)
Assigned to:		CPU Architecture:	Any
Tags:	innodb, Memory

Description:
Hi,

    We had taken the mysql data backup from one mysql server and tried to restored in another mysql server, but we got the following error while restore data. 

    "Out of memory (Needed 2514512 bytes)"

   Both servers have same mysql version 5.1.36, Table engine : InnoDB.
   RAM : 3GB
   OS : CentOS

   Mysql servers takes 100% of memory usage while restore.

   We have taken the backup using --extended-insert, while restore we got that error.

   We have tried in many ways to optimize the memory usage, but no improvement.

   Is it bug? or Is it have solution?

   Help us soon.

Thanks.
Ashokkumar.

How to repeat:
We have taken the backup using --extended-insert, while restore we got that error.

backup file size 23GB

Thank you for the problem report. Please, send your my.cnf file content and (corresponding part of) MySQL error log content.

# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /var/lib/mysql/mysql.sock

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking

# Cache & Buffer Size
max_allowed_packet = 1G
key_buffer_size =768M
table_cache =256M
sort_buffer_size =64M
read_buffer_size =64M
read_rnd_buffer_size =64M
myisam_sort_buffer_size = 64M
tmp_table_size=256M
query_cache_type=1
query_cache_limit=32M
connect_timeout=100000

# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_buffer_pool_size = 768M
innodb_additional_mem_pool_size = 512M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 32M
sort_buffer_size = 32M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

Error Log Content:
==================

091103 18:43:16 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2514512 bytes)
091103 18:43:16 [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

Please, send the results of:

uname -a
free
file mysqld

Linux commands. I need to know is it 32-bit or 64-bit environment. 

Note that some settings like:

innodb_additional_mem_pool_size = 512M

are NOT any reasonable in any case. Make this pool 16M at most. Maybe you'll have enough memory for loading your dump then...

ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
max nice                        (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 65536
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
max rt priority                 (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 65536
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

free
             total       used       free     shared    buffers     cached
Mem:       3238660    3114548     124112          0      25640    1787748
-/+ buffers/cache:    1301160    1937500
Swap:      2031608         76    2031532

Its a 32bit environment.

Please, check if the problem is repeatable with the following settings in my.cnf (leave others in place):

key_buffer_size =128M
table_cache =256
sort_buffer_size =2M
read_buffer_size =2M
read_rnd_buffer_size =2M
tmp_table_size=16M
innodb_buffer_pool_size = 128M
innodb_additional_mem_pool_size = 8M
innodb_log_buffer_size = 1M

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