Take the 2-minute tour ×
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/mysqld.pid
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. –  edvinas.me Mar 13 '14 at 22:00

4 Answers 4


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

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.