Skip to content

Instantly share code, notes, and snippets.

@molotovbliss
Created November 29, 2016 18:05
Show Gist options
  • Save molotovbliss/27919f6af96a88c827f50d529f6de59c to your computer and use it in GitHub Desktop.
Save molotovbliss/27919f6af96a88c827f50d529f6de59c to your computer and use it in GitHub Desktop.
Importing Large DB faster MySQL Settings
Source: https://dba.stackexchange.com/questions/83125/mysql-any-way-to-import-a-huge-32-gb-sql-dump-faster
innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0
Why these settings ?
innodb_buffer_pool_size will cache frequently read data
innodb_log_buffer_size : Larger buffer reduces write I/O to Transaction Logs
innodb_log_file_size : Larger log file reduces checkpointing and write I/O
innodb_write_io_threads : Service Write Operations to .ibd files. According to MySQL Documentation on Configuring the Number of Background InnoDB I/O Threads, each thread can handle up to 256 pending I/O requests. Default for MySQL is 4, 8 for Percona Server. Max is 64.
innodb_flush_log_at_trx_commit
In the event of a crash, both 0 and 2 can lose once second of data.
The tradeoff is that both 0 and 2 increase write performance.
I choose 0 over 2 because 0 flushes the InnoDB Log Buffer to the Transaction Logs (ib_logfile0, ib_logfile1) once per second, with or without a commit. Setting 2 flushes the InnoDB Log Buffer only on commit. There are other advantages to setting 0 mentioned by @jynus, a former Percona instructor.
Restart mysql like this
service mysql restart --innodb-doublewrite=0
@molotovbliss
Copy link
Author

@mkyenze https://dev.mysql.com/doc/refman/5.6/en/innodb-init-startup-configuration.html typically it's my.cnf or such in the [mysqld] space of the configuration.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment