Re: Restore large database ERROR 5 (HY000)... Out of memory

[Justin Yaple]

I have tried about everything here. Increased memory to 6GB with PAE kernel added a dedicated 8GB swap disk. Even though no swap gets used at any point in the restore. I have tried a whole mess of my.cnf settings but I am still getting this error when restoring the mysqldump file.

Would really appreciate any help here at this point. Thanks.

ERROR 5 (HY000) at line 263354: Out of memory (Needed 904793952 bytes)

[mysqld]
character-set-server = utf8
user = mysql
port = 3306
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
log-error = /var/log/mysql/mysqld.err
basedir = /usr
datadir = /var/lib/mysql
skip-external-locking
key_buffer = 1024M #256M
max_allowed_packet = 1024M
table_cache = 1024
sort_buffer_size = 16M #16M
net_buffer_length = 1M #8K
read_buffer_size = 16M #256K
read_rnd_buffer_size = 16M #512K
myisam_sort_buffer_size = 128M #8M
language = /usr/share/mysql/english
thread_stack = 128K
myisam_use_mmap = yes

# security:
# using "localhost" in connects uses sockets by default
# skip-networking
bind-address = 127.0.0.1
query_cache_type = 1
query_cache_size = 256M #256M

#log-bin
server-id = 1

# point the following paths to different dedicated disks
tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname

# you need the debug USE flag enabled to use the following directives,
# if needed, uncomment them, start the server and issue
# #tail -f /tmp/mysqld.sql /tmp/mysqld.trace
# this will show you *exactly* what's happening in your server ;)

#log = /tmp/mysqld.sql
#gdb
#debug = d:t:i:o,/tmp/mysqld.trace
#one-thread

# uncomment the following directives if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000

# the following is the InnoDB configuration
# if you wish to disable innodb instead
# uncomment just the next line
skip-innodb
#
# the rest of the innodb config follows:
# don't eat too much memory, we're trying to be safe on 64Mb boxes
# you might want to bump this up a bit on boxes with more RAM
innodb_buffer_pool_size = 16M
# this is the default, increase it if you have lots of tables
innodb_additional_mem_pool_size = 2M
#
# i'd like to use /var/lib/mysql/innodb, but that is seen as a database :-(
# and upstream wants things to be under /var/lib/mysql/, so that's the route
# we have to take for the moment
#innodb_data_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
#innodb_log_group_home_dir = /var/lib/mysql/
# you may wish to change this size to be more suitable for your system
# the max is there to avoid run-away growth on your machine
innodb_data_file_path = ibdata1:10M:autoextend:max:128M
# we keep this at around 25% of of innodb_buffer_pool_size
# sensible values range from 1MB to (1/innodb_log_files_in_group*innodb_buffer_pool_size)
innodb_log_file_size = 5M
# this is the default, increase it if you have very large transactions going on
innodb_log_buffer_size = 8M
# this is the default and won't hurt you
# you shouldn't need to tweak it
innodb_log_files_in_group=2
# see the innodb config docs, the other options are not always safe
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_file_per_table