MYSQL BULK_INSERT_BUFFER_SIZE Variable

MYSQL BULK_INSERT_BUFFER_SIZE SYSTEM VARIABLE

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. The BULK_INSERT_BUFFER_SIZE variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB. The maximum value is 4GB. The bulk_insert_buffer_size can be set both on system and session levels.

If data is being added to a non-empty table, tuning the bulk_insert_buffer_size variable can make data insertion faster. Normaily, it shows the improvement when the data to be inserted is more than 10k rows. But it is hard to say what is the right value, so, trail and try with incremental buffer size values.

Also, performance improvement will be more obvisoue if the following variables are also set:

• MYISAM_SORT_BUFFER_SIZE
• KEY_BUFFER_SIZE

 In addition, when doing bulk inset, consider disable the indexes before loading the data using the following alter table command:

    alter table t disable keys;

References:

• MySQL Manual: Bulk_Insert_buffer_Size variable.
• Other References On The Web

 Related Posts:

• MySQL MYISAM_SORT_BUFFER_SIZE Variable
• MySQL KEY_BUFFER_SIZE variable
• MySQL Alter Table t Disable Keys Statement
• MySQL Optimize Table Command

MYSQL MYISAM_SORT_BUFFER_SIZE Variable

MYSQL MYISAM_SORT_BUFFER_SIZE Variable

 This is the The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE. The maximum size is 4 GB.

This variable should be set as large as the largest index in the table for index builds if there is sufficient RAM and it is not over 4 GB.

This variable can be set as global variable or session variable.
 
MYSQL>set session MYISAM_SORT_BUFFER_SIZE=1073741824;
or
MYSQL>set global MYISAM_SORT_BUFFER_SIZE=1073741824;

Or, to set it in my.cnf file:
MYISAM_SORT_BUFFER_SIZE=1073741824

References:

• MySQL Manual: MyISAM_Sort_Buffer_Size
• Other References On The Web

World Expo 2010 Shanghai China

World Expo 2010 (aka World Fair) is under way in Shanghai China.

Ever since the first World Fair, which held in The Crystal Palace in Hyde Park, London, United Kingdom in 1851, there have been total 54 World Exhibition taking places in different countries according to Wikipedia. However, officially, Expo 2010 is the 41st World Expo. The Bureau International des Expositions (BIE), which governs the World Expo, provides information on organizations and history of the Word Expo. Following are some facts I had found which were related to World Expo 2010,Shanghai China.

The Participants:

• 189 countries and 57 international organizations are participating in the Shanghai World Expo. 18 corporations including Cisco and Coca-Cola.

The Site:

• The Shanghai World Expo is the largest World's Fair site ever at 5.28 square km (2.5 square miles ). It costs $46 billion USD to build. The official cost of $4.2 billion covers just the Expo site and its operations.

The Pavilions:

• The site itself is made up of a series of 100 pavilions. The China Pavilion, an inverted, imperial-red pyramid known as the Oriental Crown, built at an estimated cost of 1.5 billion RMB. Five permanent structures: China Pavilion, Expo Center, Expo Culture Center, Theme Pavilions, and Expo Boulevard designed for continual use after the Expo.

The Staff:

• There are 50,000 Expo Bureau staff and other service staffs during the Expo, There are 72,000 volunteers, age from 16 to 99, working inside the Expo site, and 4,000 Expo-exclusive taxis dedicate for the Expo. There are another 100,000 volunteers will staff more than 1,000 service centers around the city during the event.

Visits:

• The Expo will open for 184 days, from May 1st to October 3oth, 2010. It is expected to attract 70 million visitors, including an estimated 5 million foreign tourists. It also expects to receive almost 100 foreign leaders.

Tickes:

•  The ticket prices ranging from 90 to 200 yuan (or approximately $13 to $30 USD) depending on whether you visit on a standard day, peak day, or only during evening hours. All tickets are good for either 3 or 7 days, depending on which type of ticket you choose.

The Theme:

•  The World Expo 2010 theme is “Better City, Better Life”. There are five sub-themes:

               * Blending of Diverse Cultures in the City
               * Economic Prosperity in the City
               * Innovation of Science and Technology in the City
               * Remodeling of Communities in the City
               * Interactions Between Urban and Rural Areas

           Here is the Officer Video of the World Expo 2010

 The Theme Song:

•  The theme song is called "City" (成市) which was sung by movie star and  Expo Ambassador Jackie Chan. This MTV video also features NBA star Yao Ming and pianist Lang Lang. Both are Expo Ambassadors as well.

References:

• Expo 2010 Shanghai China
• The Bureau International des Expoisitions (BIE)
• Expo Shanghai Online
• Wikipedia
• World of Fairs: The Century-of-progress Expositions
• Encyclopedia of World's Fairs and Expositions
• Other References On The Web
• Jack Chan's Movies