Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

port            = 3306
socket          = /var/run/mysqld/mysqld.sock

socket          = /var/run/mysqld/mysqld.sock
nice            = 0

# * Basic Settings
user            = mysql
pid-file        = /var/run/mysqld/
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            =
# * 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
# 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_log_buffer_size = 8M

max_allowed_packet      = 16M

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

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 '
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"]

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

share|improve this question
You cannot really limit usage, please check my answer. – Mar 13 '14 at 22:00

5 Answers 5



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.


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


share|improve this answer

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:

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:


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

share|improve this answer

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.

share|improve this answer
  • 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.

share|improve this answer
Hi Rick - it might be a nice idea to try to summarize the changes you think might be appropriate. Link-only answers are generally to be avoided since eventually links will go away. – Max Vernon Feb 2 at 14:17

Some people tell me about mydumper. I have not used it before but it is very hopeful. About mydumper:
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.