bulk_insert_buffer_size and InnoDB

Q:

I read the following on this page

http://dev.mysql.com/doc/mysql/en/server-system-variables.html

bulk_insert_buffer_size MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. Note: This cache is used only when adding data to a non-empty table. The default value is 8MB. This variable was added in MySQL 4.0.3. This variable previously was named myisam_bulk_insert_tree_size.

I take this to mean that bulk_insert_buffer_size has no bearing on InnoDB tables, only on MyISAM tables. Is this correct?

A:

1. Yes, you are correct. I wrote about this before: How can I optimize this mySQL table that will need to hold 5+ million rows?

   WHen it comes to bulk loading InnoDB tables, one of the most frequently ignored settings to disable is innodb_doublewrite.

　　　　InnoDB Architecture

　　Please note the double write buffer inside the system tablespace file ibdata1. Every time a 16K page is written to an InnoDB table or transaction log, the page is written to the double wrote buffer first. All you need to do is disable it, load your data, and reenable it.

 

　　STEP 01 : Disable Fast Shutdown

　　mysql -uroot -p...-ANe"SET GLOBAL innodb_fast_shutdown = 0"

　　STEP 02 : Restart mysql without the double write buffer　　

service mysql restart --innodb-doublewrite=0

　　STEP 03 : Load the InnoDB data

　　STEP 04 : Restart mysql

　service mysql restart

　　CAVEAT

　　You can disable the foreign key checks and the unique checks as well. Any standard mysqldump will do that for you already.

　　2. For Innodb we need to diable unique_checks and foreign_key_checks using

　　　　set unique_checks=0; set foreign_key_checks=0; diable auto_commit.

　　　　inserts......

　　　set unique_checks=1; set foreign_key_checks=1; enable auto_commit.

　　　　Which improves bulk inserts performance.

 

 

---

 

 

bulk_insert_buffer_size：

MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

innodb_doublewrite：

If this variable is enabled (the default), InnoDB stores all data twice, first to the doublewrite buffer, then to the actual data files. This variable can be turned off with --skip-innodb_doublewrite for benchmarks or cases when top performance is needed rather than concern for data integrity or possible failures.

innodb_fast_shutdown：

The InnoDB shutdown mode. If the value is 0, InnoDB does a slow shutdown, a full purge and an insert buffer merge before shutting down. If the value is 1 (the default), InnoDB skips these operations at shutdown, a process known as a fast shutdown. If the value is 2, InnoDB flushes its logs and shuts down cold, as if MySQL had crashed; no committed transactions are lost, but the crash recovery operation makes the next startup take longer.

The slow shutdown can take minutes, or even hours in extreme cases where substantial amounts of data are still buffered. Use the slow shutdown technique before upgrading or downgrading between MySQL major releases, so that all data files are fully prepared in case the upgrade process updates the file format.

Use innodb_fast_shutdown=2 in emergency or troubleshooting situations, to get the absolute fastest shutdown if data is at risk of corruption.

 

参考：

http://dba.stackexchange.com/questions/49646/bulk-insert-buffer-size-and-innodb

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_doublewrite