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.

1 comment:

jojo said...

I have read your writings and I have read articles on this topic in several articles from other sources. I got a lot of information from your writing, is there any other suggestions you can convey regarding the theme of your writing? so that I can get more and more complete information.