Variable's Day Out #15: bulk_insert_buffer_size

Properties:

Applicable To	MyISAM
Server Startup Option	--bulk_insert_buffer_size=<value>
Scope	Both
Dynamic	Yes
Possible Values	Integer: Range: 0 - 4294967295 (4G)
Default Value	8388608 (8M)
Category	Performance

Description:

This cache is used by MyISAM to optimize bulk inserts. This cache is a special tree-like structure. Bulk inserts include statements like LOAD DATA INFILE..., INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., (...)

Bulk inserts are often used to minimize disk writes etc and are very common in applications inserting lots of data. I often use bulk inserting using the following technique: (pseudocode)

• CREATE TEMPORARY TABLE tmp_table LIKE main_table
• LOOP:
  • INSERT ROWS INTO TEMPORARY TABLE
• INSERT INTO main_table SELECT * FROM tmp_table

If you are using any technique for bulk insertion, you should be carefully setting this parameter.

Optimal Value:

This depends on the way your bulk inserts are structured. If you are doing bulk inserts with data being close to or more than 10k, this comes really handy.

Read more:

• MySQL manual entry on bulk_insert_buffer_size
• LOAD DATA INFILE - performance case study (Venu Anuganti)

Hope you enjoyed reading this.