MySQL runs out of memory when importing innodb database

Question

I have a small VPS with 512MB RAM and no swap (can't have any).

I am importing a 1.5GB innodb database with

mysql -u -p < database.sql

Before the operation can finish it says : Lost connection to MySQL server. That is because the mysqld crashed trying to get more than 480MB of RAM.

This is my my.cnf

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
#
# * Fine Tuning
#
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 4
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries       = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
innodb_buffer_pool_size=80M
innodb_additional_mem_pool_size=20M

innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size = 8M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

More info:
* There are lots of tables : 30k tables
* I am running mysql 5.5 on ubuntu 12.04
* I've read a lot of stuff on mysql innodb memory usage... I must be missing something because the formula they present are not working

This script

mysql -u admin -p -e "show variables; show status" | awk '
{
VAR[$1]=$2
}
END {
MAX_CONN = VAR["max_connections"]
MAX_USED_CONN = VAR["Max_used_connections"]
BASE_MEM=VAR["key_buffer_size"] + VAR["query_cache_size"] + VAR["innodb_buffer_pool_size"] + VAR["innodb_additional_mem_pool_size"] + VAR["innodb_log_buffer_size"]
MEM_PER_CONN=VAR["read_buffer_size"] + VAR["read_rnd_buffer_size"] + VAR["sort_buffer_size"] + VAR["join_buffer_size"] + VAR["binlog_cache_size"] + VAR["thread_stack"] + VAR["tmp_table_size"]
MEM_TOTAL_MIN=BASE_MEM + MEM_PER_CONN*MAX_USED_CONN
MEM_TOTAL_MAX=BASE_MEM + MEM_PER_CONN*MAX_CONN

printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "key_buffer_size", VAR["key_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "query_cache_size", VAR["query_cache_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_buffer_pool_size", VAR["innodb_buffer_pool_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_additional_mem_pool_size", VAR["innodb_additional_mem_pool_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_log_buffer_size", VAR["innodb_log_buffer_size"]/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "BASE MEMORY", BASE_MEM/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "sort_buffer_size", VAR["sort_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "read_buffer_size", VAR["read_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "read_rnd_buffer_size", VAR["read_rnd_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "join_buffer_size", VAR["join_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "thread_stack", VAR["thread_stack"]/1048576
printf "| %40s | %15.3f MB |\n", "binlog_cache_size", VAR["binlog_cache_size"]/1048576
printf "| %40s | %15.3f MB |\n", "tmp_table_size", VAR["tmp_table_size"]/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "MEMORY PER CONNECTION", MEM_PER_CONN/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %18d |\n", "Max_used_connections", MAX_USED_CONN
printf "| %40s | %18d |\n", "max_connections", MAX_CONN
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "TOTAL (MIN)", MEM_TOTAL_MIN/1048576
printf "| %40s | %15.3f MB |\n", "TOTAL (MAX)", MEM_TOTAL_MAX/1048576
printf "+------------------------------------------+--------------------+\n"
}'

And this is the output

+------------------------------------------+--------------------+
|                          key_buffer_size |          16.000 MB |
|                         query_cache_size |          16.000 MB |
|                  innodb_buffer_pool_size |          80.000 MB |
|          innodb_additional_mem_pool_size |          20.000 MB |
|                   innodb_log_buffer_size |           8.000 MB |
+------------------------------------------+--------------------+
|                              BASE MEMORY |         140.000 MB |
+------------------------------------------+--------------------+
|                         sort_buffer_size |           2.000 MB |
|                         read_buffer_size |           0.125 MB |
|                     read_rnd_buffer_size |           0.250 MB |
|                         join_buffer_size |           0.125 MB |
|                             thread_stack |           0.188 MB |
|                        binlog_cache_size |           0.031 MB |
|                           tmp_table_size |          16.000 MB |
+------------------------------------------+--------------------+
|                    MEMORY PER CONNECTION |          18.719 MB |
+------------------------------------------+--------------------+
|                     Max_used_connections |                  1 |
|                          max_connections |                  4 |
+------------------------------------------+--------------------+
|                              TOTAL (MIN) |         158.719 MB |
|                              TOTAL (MAX) |         214.875 MB |
+------------------------------------------+--------------------+

However in top I can see mysqld memory usage going to 481m and then mysql crash.

What configuration am I missing to limit the memory usage?! Thanks

Answer 1

Workarounds

BigDump http://www.ozerov.de/bigdump/

It executes only a small part of the huge dump and restarts itself. The next session starts where the last was stopped to keep you from running into your server’s limits. Instructions for using it are on the BigDump website. Basically you place your SQL file in a folder on your server, along with the bigdump.php file. You edit that file with your database information and then visit the page on your server and set the import to go. This is a fairly quick process and will save you a lot of time.

SQLDumpSpliter http://www.rusiczki.net/2007/01/24/sql-dump-file-splitter/

Create the splits and then upload them to a directory on your server.

If you want to restore the dump, you have to execute the yourdatabase_DataStructure.sql first because it contains the Tables structure. After that, you can execute the other .sql-files as they contain the data of the now existing tables. Using SSH, CD to your directory again and make sure to send this first:

mysql -u db_user -p db_name < yourbackup_DataStructure.sql

Then your splits:

mysql -u db_user -p db_name < yourbackup_1.sql
mysql -u db_user -p db_name < yourbackup_2.sql

Source: http://www.ontwerps.nl/methods-importing-large-sql-files

Answer 2

I think Rick James is on the right track, the many 30k table handles are jamming memory.

In MySQL 5.5, InnoDB uses the system tablespace, which places all table data in a single OS file. This means that individual internal table data allocation, as well as handle management, happens inside MySQL (which is to say, completely in RAM as you have no swap space), with no assistance from the host OS (Ubuntu 12).

I would try enabling innodb_file_per_table:

https://dev.mysql.com/doc/refman/5.5/en/tablespace-enabling.html

The innodb_file_per_table option is enabled by default as of MySQL 5.6.6, so you'll have to enable it on your 5.5 server:

[mysqld]
innodb_file_per_table
...

(note that there is no value given for the setting, the presence of the key enables the feature)

This allows MySQL InnoDB to use more OS and disk resources for table management, hopefully reducing heap usage and memory management thrash.

You may want to start with either a clean install (no pre-existing table data), or at least shut down MySQL and delete the ibdata* and ib_log* files in your datadir, since MySQL will not convert existing tables to the per-file format. MySQL will rebuild the missing ib* files on startup, expect to see related error messages.

Using innodb_file_per_table, MySQL stores individual table files (.fmt and .ibd) in subdirectories of datadir, where each directory is a mysql table_schema. If you are putting all of the 30k tables into one schema/folder, you can have the same problem at the OS level, as Ubuntu tries to load the entire directory index into memory. If possible, organize your tables into several separate schema/folders, I would try to keep the number of tables per schema no greater than 1000.

Answer 3

You could try my solution to this issue - MySQLDumpSplitter. It takes a dump file and splits it into individual tables, which you may find convenient. My email is there should it not conform to your needs - I could take a look at any issues you may have in an effort to make the tool better.

Disclaimer: I wrote it, and as you'll see from the Readme, it works only under specific conditions. Use at your own discretion.

Answer 4

• There are lots of tables : 30k tables

That's really bad, even if you have a modern amount of RAM (instead of 0.5GB). The OS needs to space for them; mysql needs space; InnoDB also needs space.

I can't prove it, but I think that is the main problem.

Answer 5

Some people tell me about mydumper. I have not used it before but it is very hopeful. About mydumper:

http://imagexmedia.com/blog/2014/11/speeding-your-mysql-dumprestores-mydumper
http://www.percona.com/blog/2014/03/10/new-mydumper-0-6-1-release-offers-several-performance-and-usability-features/

Answer 6

Not your issue, but I had this same problem and my issue was that innodb_buffer_pool_size was too large. I was running it inside Docker, and I had it set to 16G, which is how it's set in production, but Docker was killing it early because it was asking for too much memory. Lowering that setting to 6G fixed the issue.

If you're running this in Docker, make sure to also increase the memory you allow Docker to allocate from the default 2. I have mine set to 8 GB. Hope this helps someone.

Answer 7

Try doing it in 2 steps

mysql -u username -p db_name

Once you are in, use the "source" command

> source database.sql