Memory, swap and mysql config requirements for importing a 50GB sql table?

NAMAssist asked:

How would one calculate/determine the best settings for memory, swap and a good mysql configuration?

I’ve been attempting for several days now to import a 50GB sql table into a database on my local machine within a virtual machine (an Oracle VM VirtualBox specifically). This has been given 12GB base memory (out of my pcs 16 max) and 2 processors (out of my pcs 4 max).

No matter what I try to import the database it will come back with mysql has gone away errors, and then I can no longer connect to mysql as innodb is trying to recover itself over-and-over again in the background.

I can tell that this issue is a MEMORY related issue, since more records get imported before it crashes as I add more memory allowance.

  • I’ve attempted to give 30GB of SWAP but it still crashed!
  • The table.ibd file (in /var/lib/mysql/mydatabase) got to 58GB in size on my last attempt)

Not sure what to try next, other than giving up and re-exporting the table with the –skip-extended-inserts flag.

My current mysql config while doing the imports:

sudo nano /etc/mysql/conf.d/mysql.cnf

innodb_doublewrite = 0
innodb_buffer_pool_size = 7G
innodb_log_buffer_size = 2G
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0
max_allowed_packet = 1073741824

After it fails I can see by running "top" command that mysqld process exists and that innodb recover is being attempted over and over with this command:

sudo strace -e trace=read,write -s 2000 -fp $(pgrep -nf mysql) 2>&1

FYI, I run a query to dump the table like this:

mysqldump -h localhost –user=user –password=password mydatabase
my50gbtable –hex-blob –single-transaction –quick
–max_allowed_packet=999M –compress > my50gbtable.sql

I appreciate any ideas you might have!

My answer:

You don’t want to use --single-transaction to dump the data. On import, the MySQL server will have to keep the entire contents of the table in memory during the import until it’s committed, thus defeating the purpose of options like --quick.

Dump the database without using --single-transaction.

View the full question and any other answers on Server Fault.

Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.