Created
November 29, 2016 18:05
-
-
Save molotovbliss/27919f6af96a88c827f50d529f6de59c to your computer and use it in GitHub Desktop.
Importing Large DB faster MySQL Settings
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
@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
where is specifically am i going to find this file to edit these
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