, ,

Whenever we load data into a MySQL database the following settings can help speed up the import.

innodb_flush_log_at_trx_commit = 0

innodb_support_xa = 0


If the host doesn’t have slaves, and if log-bin is turned on, turning it off using set SQL_LOG_BIN=0 or commenting out log-bin in my.cnf will also help. Similarly disabling other logs like update log, query log, slow query log will also help.

These parameters help in reducing frequent syncs to disk, but ofcourse we need to have the memory configurations tuned optimally. Also disabling key checks can help speed up the import.

SET UNIQUE_CHECKS=0; SET FOREIGN_KEY_CHECKS=0; at the top of the import will do the trick.