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

MySQL: Optimize Table

MySQL Optimize Table Command

The Optimize table command defragments, reclaims free space and makes concurrent inserts wrk again. For MyISAM table, optimize table command performs the following tasks:

• Repaire the table if the table has deleted or split rows. This, in trun, help defragment and reclaim free spaces.
• Sort index pages if it is needed.
• Update the table’s statistics.

 One way to find out if one should run optimize table on a table is to compare the data length and data free values from the show table status command.

•   Data_length is the length of the data file.
•   Data_free is the the number of allocated but unused byte. These included those deleted records.

 When a data_free value is relative large in comparing to date_length, optimize table should be performed agaist the table. Please see the following example:

    mysql> show table status like '%table_name%' \G;

*************************** 1. row ***************************

 Name: table_name

 Engine: MyISAM
 Version: 10
 Row_format: Dynamic
Rows: 1473839
Avg_row_length: 186
Data_length: 420392440
Max_data_length: 281474976710655
Index_length: 52712448
Data_free: 145156624
Auto_increment: NULL
 Create_time: 2010-05-18 18:35:16
 Update_time: 2010-05-20 13:44:47
 Check_time: 2010-05-20 02:21:54
 Collation: latin1_swedish_ci
 Checksum: NULL
 Create_options: delay_key_write=1
 Comment:

   mysql> optimize table table_name;

+--------------------------------+----------+----------+----------+
Table                                           Op             Msg_type  Msg_text
 +--------------------------------+----------+----------+----------+
 dwstgdb.table_name                   optimize      status          OK
 +--------------------------------+----------+----------+----------+
 1 row in set (50.08 sec)

   mysql> show table status like '%table_name%' \G;

*************************** 1. row ***************************
 Name: table_name
 Engine: MyISAM
 Version: 10
 Row_format: Dynamic
 Rows: 1473839
 Avg_row_length: 186
 Data_length: 275235816
 Max_data_length: 281474976710655
 Index_length: 30075904
 Data_free: 0
 Auto_increment: NULL
 Create_time: 2010-05-18 18:35:16
 Update_time: 2010-05-21 00:33:58
 Check_time: 2010-05-21 00:34:14
 Collation: latin1_swedish_ci
 Checksum: NULL
 Create_options: delay_key_write=1
 Comment:

Now we can see that the data_free value down to 0 from 145156624 after performing optimize table. This will indeed deframent the table and speep up the queries. In general, optimize table command should be performed on a reqular basis.

Referemces:

• MySQL Manual: OPTIMIZE TABLE Syntax
• Other References On The Web