MySQL テーブルサイズの上限

MySQLのMyISAMテーブルではオプションを指定せずにテーブルを作成するとサイズの上限は4GBとなる(*1)。テーブルサイズの設定に関するメモ。

(*1) 対象Ver.は4.0〜。ファイルシステムのファイルサイズ上限により4GBまで使えない場合もある。

テーブルサイズの上限の表示

テーブルサイズの上限は'SHOW TABLE STATUS'で表示できる。Max_data_lengthのカラムがテーブルサイズの上限値。データベース、テーブル名を指定して表示したい場合は、SHOW TABLE STATUS FROM DB名 LIKE 'テーブル名'で表示できる。

mysql> SHOW TABLE STATUS FROM test LIKE 'sample' \G
*************************** 1. row ***************************
           Name: sample
         Engine: MyISAM
        Version: 9
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 4294967295
   Index_length: 1024
      Data_free: 0
 Auto_increment: 1
    Create_time: 2006-10-03 15:08:20
    Update_time: 2006-10-03 15:08:20
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

上限を指定してテーブルを作る

テーブル作成時にサイズを指定する場合は、MAX_ROWS,AVG_ROW_LENGTHオプションを指定する。MAX_ROWSは最大レコード数でAVG_ROW_LENGTHはレコード長の平均値となる。MAX_ROWS * AVG_ROW_LENGTHの計算によってテーブルサイズが計算されるので、この積が必要サイズになるように指定すればよい。ただし、MySQLの最適化のためか(?)実際のテーブルサイズはMAX_ROWS * AVG_ROW_LENGTHよりはずっと大きい値となる。

以下は20GB(MAX_ROWS=500000*AVG_ROW_LENGTH=40000)の上限値でテーブルを作成する例。作成されたテーブルのMax_data_lengthは1TB近くになっている。

mysql> CREATE TABLE tst_tbl (id SERIAL, content TEXT)
       MAX_ROWS=500000,AVG_ROW_LENGTH=40000;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'tst_tbl' \G
*************************** 1. row ***************************
           Name: tst_tbl
         Engine: MyISAM
        Version: 9
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 1099511627775
   Index_length: 1024
      Data_free: 0
 Auto_increment: 1
    Create_time: 2006-10-03 15:38:57
    Update_time: 2006-10-03 15:38:57
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: max_rows=500000 avg_row_length=40000
        Comment:
1 row in set (0.01 sec)

なお、テーブルにTEXTやBLOBなど可変長のものがなければ、AVG_ROW_LENGTHは指定する必要はない。MAX_ROWSだけでテーブルサイズを算出してくれる。

既存のテーブルのサイズ上限を変更する

既存のテーブルのサイズ上限を変更する場合は、ALTER TABLE テーブル名 MAX_ROWS=<最大レコード数>, AVG_ROW_LENGTH=<レコードの平均長>で設定できる。CREATE TABLEと同じくテーブルサイズの上限はMAX_ROWS*AVG_ROW_LENGTHによって計算される。

以下は20GB(MAX_ROWS=500000*AVG_ROW_LENGTH=40000)に設定する例。既にテーブルサイズが大きくなっている場合は、実行に時間がかかる。

mysql> SHOW TABLE STATUS FROM Contents LIKE 'tst_tbl' \G
*************************** 1. row ***************************
           Name: tst_tbl
         Engine: MyISAM
        Version: 9
     Row_format: Dynamic
           Rows: 188558
 Avg_row_length: 22777
    Data_length: 4294967264
Max_data_length: 4294967295
   Index_length: 4639744
      Data_free: 0
 Auto_increment: 188559
    Create_time: 2006-09-29 20:41:41
    Update_time: 2006-10-03 12:30:46
     Check_time: 2006-09-29 20:44:44
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> ALTER TABLE tst_tbl MAX_ROWS=500000, AVG_ROW_LENGTH=40000;
Query OK, 188435 rows affected (4 min 21.11 sec)
Records: 188435  Duplicates: 0  Warnings: 0

mysql> SHOW TABLE STATUS FROM Contents LIKE 'tst_tbl' \G
*************************** 1. row ***************************
           Name: tst_tbl
         Engine: MyISAM
        Version: 9
     Row_format: Dynamic
           Rows: 188435
 Avg_row_length: 22776
    Data_length: 4291895168
Max_data_length: 1099511627775
   Index_length: 5040128
      Data_free: 0
 Auto_increment: 188559
    Create_time: 2006-10-03 14:43:31
    Update_time: 2006-10-03 14:46:40
     Check_time: 2006-10-03 14:47:46
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: max_rows=500000 avg_row_length=40000
        Comment:
1 row in set (0.01 sec)