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 

MySQL: Alter Table Disable Keys

MySQL Disable Indexes Command

When inserting,updating or deleting a high percenblege of the data from a table, it is a best pratce to disable indexes. Especaily, when doing a bulk load into a empty table.  To disable indexes, one can drop all the index keys and recreate them later. Or, we can use Alter Table table name Disable Keys to deactivate  indexes  on a MyISAM table.

To do that, before performing the sql statements that involve with insert, update or delete, issue the following command:

         mysql> alter table tablename disable keys;

Now perform the tasks; for example:

        mysql> insert into tablename(...) values( ...);

After the task has been completed, issue the following command to reactivate the indexes:

         mysql>alter table tablename enable keys;

ALTER TABLE tablename DISABLE KEYS tells MySQL to stop updating nonunique indexes. ALTER TABLE tablename ENABLE KEYS tells MySQL to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. When using ALTER TABLE tablename DISABLE KEYS, please keep in mind the following:

•  ALTER TABLE tablename DISABLE KEYS will keep UNIQUE keys active.
• Using ALTER TABLE tablename DISABLE KEYS requires ALTER, INSERT, CREATE and the INDEX privilege for that table.
• While the nonunique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them.
• ENABLE KEYS and DISABLE KEYS were not supported for partitioned tables prior to MySQL 5.1.11.

 References:

• MySQL Manual: ALTER TABLE Syntax.
• Other References On The Web. 

MySQ: Delay_Key_Write

MySQL Alter Table t Delay_ Key_ Write Command

The delay_key_write option works with MyISAM tables. Delayed key writing tells storage engine not to wtire change index data to disk at the end of any query that changes the data. Instead, the changes are kept in the key buffer until the server prunes the buffer or close the table. This option can boots performance if the MyISAM table is changing frequenry, for example, has a lot of inserts and updates.

To turn on delay_key_write option for a table, you can include the following statement in to create table script:

     Delay_key_write =1

Or for existing table, use the alter table statement:

    MySQL> ALTER TABLE table name DELAY_KEY_WRITE = 1;

Please note that,in MySQL, Alter Table statement takes time to excute and locks the table. Therefore, for large tables, so do this with care.

There are a few thing need to keep in mind when using delay_key_write option as well. First, the changes, mainly, the index changes, will be kep in memory until the buffer cache prunes or the table is closed. A table is closed when:

•   When the cache is full and a thread tries to open a table that is not in the cache.
•  When the cache contains more than table_cache entries and a thread is no longer using a table.
•  When FLUSH TABLES command is issued.

 This means that if the server or table is crashed, there will be indexes cruptions since the indexes on the index file (tablename.MYI) was not updated while the data file (tablename.MYD) was. Therefore, it is recomanded that the myisam-recover variable to be turned on during server startup. For example, set
 --myisam-recover=BACKUP,FORCE in my.cnf file or start the server with –myisam-recover option. Secondly, if the table has many write delays, it will table longer for the table to close since it needs time to flush all the caches to the disk, and it will take longer to run FLUSH TABLES statements(flush table, flush table with read lock) Hence, flush tables should be used a bit frequely if you use delay_key_write option. Lastly, queries may need to wait for the MyISAM engine to free up space in the key buffer cache because the unflushed dirty blocks in the key buffer cache take up space.

Delay_Key_write option can also be enable globally for all MyISAM tables. To do so, set the delay_key_write option to ‘all’:

   delay_key_write = all

However, it is not a commend pratics to most systems because for infrequently written tables, delay_key_write may increase the chance of index corruption.

 References:

• MySQL Manual: How MySQL Opens and Closes Tables
• High Performance MySQL: Chapter 1, MySQL Architecture. Chapter 6, Optimizing Server Settings
• Other References On The Web