Table Status

Checking out the state of tables of your MySQL setup in production is important for fine tuning server parameters. MySQL's SHOW TABLE STATUS command comes very handy in determining table states from a running environment. Here, I explain the output of the command.

mysql> SHOW TABLE STATUS LIKE 'mytable' \G
****************** 1. row ******************
           Name: mytable
         Engine: InnoDB
        Version: 9
     Row_format: Dynamic
           Rows: 2465222
 Avg_row_length: 370
    Data_length: 913326080
Max_data_length: NULL
   Index_length: 146817024
      Data_free: 0
 Auto_increment: 18357110
    Create_time: 2007-05-11 22:27:56
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment: InnoDB free: 869376 kB
1 row in set (0.34 sec)

Leaving behind the obvious parameters, we shall see only the more relevant ones and ones that look like more interesting.

Row_format: This describes the length behavior of the rows in this table. Can have one value out of Dynamic, Fixed or Compressed. Dynamic rows have variable length due to fields having type like BLOB, VARCHAR etc. Fixed rows are made up of fields that do not vary in length. Compressed rows exist for compressed tables as those in Compressed MyISAM.

Rows: This describes the number of rows in a table. This number is always accurate for non-transactional engines like MyISAM, but an approximate value for transactional tables like InnoDB. Ever wondered why select count(*) is faster on MyISAM than InnoDB.

Avg_row_length: The name says it all. Anyhow, it's in bytes.

Data_length: This is the total data (in bytes) in the table. For InnoDB tables, which tend to use a single tablespace, this can be a deterministic factor for splitting tables in their individual tablespaces.

Max_data_lenth: This is generally not applicable for all the engines. This describes the maximum data that a table can hold. MyISAM tables generally have this restriction.

Index_length: This parameter should be of utmost interest. This describes the total space (in bytes) taken up by the index of the table. The aggregate value of this field over all the tables under the same engine (or the engines that use same buffer for index data) can actually be a deterministic value for such buffers. For InnoDB tables, this can also play a role in splitting tables in their individual tablespaces.

As far as this moment is concerned, this much of explanation is enough. Users should take care of not going by the instinct of a single snap shot for this data. Rather this data should be collected periodically to actually see the change and decisions be made accordingly.

Hope this blog was helpful to you. Keep posting your comments.

del.icio.us Tags: MyISAM, InnoDB, MySQL, Table Status, Tablespaces, Tuning