Tuesday, July 15, 2008

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:

Hope you enjoyed reading this.

No comments: