Speeding up MySQL Import on FreeBSD

Wed 28 September 2016 by feld

I was recently tasked with rebuilding a readonly slave database server which only slaves a couple of the available databases. The backup/dump is straightforward and fast, but the restore was being excruciatingly slow. I didn't want to wait a week for this thing to finish, so I had to compile a list of optimizations that would speed up the process. This is the best way to do it on FreeBSD, assuming you're working with InnoDB. Additional optimizations may be required if you're using a different database engine.

Please note this is assuming no other databases are running on this MySQL instance. Some of these are rather dangerous and you wouldn't want to put other live data at risk.


innodb_buffer_pool_size = 38G  # roughly 70-80% of your available memory
innodb_flush_method = O_DIRECT
sync_binlog = 0 # Don't keep this permanently
innodb_flush_log_at_trx_commit = 0 # Don't keep this permanently
innodb_log_file_size = 1G
innodb_log_buffer_size = 256M
innodb_write_io_threads = 16


# Only use this during imports

The actual import command in use:

# cat dump.sql.gz | { echo "set sql_log_bin=0; set autocommit=0; set
unique_checks=0; set foreign_key_checks=0;"; zcat; } | mysql -u root -p

And now I've gone from a tens of MBs imported per minute to several GBs imported per minute.