【MySQL】ALTER TABLEする際の空き容量の確認方法

はじめに

Zabbix3.0から4.0へのバージョンアップの際、DB(MySQL)のテーブルをALTER TABLEする必要があります。
テーブル同程度のディスク空き容量が必要とのことだが、具体的にどうやって算出すれば良いのか分からなかったため、調べました。

まとめ

  • ALTER TABLEの際は元のテーブルと同程度のディスク空き容量が必要
  • テーブルのコピーは元テーブルと同じディレクトリに作成されるため、元テーブルの格納ディレクトリがマウントされているディスクの空き容量を確認する

検証環境

  • MySQL:5.7.32

調べたこと

まず、以下の記事にもある通り、ALTER TABLEコマンドを実行すると、対象テーブルのコピーを作成するため、元テーブルのサイズと同程度のディスク空き容量が必要となる。

「[ALTER TABLEを上手に使いこなそう。」漢のコンピュータ道,2009-05-11(参照:2021-01-12)]

ALTER TABLEでは完全なテーブルのコピーを作成する必要があるので、元のテーブルのサイズと同じぐらいのディスク空き容量が必要であることにも注意しなければいけない。

また、元テーブルのコピーは、元テーブルと同じディレクトリに作成される。

「巨大なサイズのテーブルに対するALTER TABLE実行時間の概算方法」PAYFORWARD,2016-04-07(参照:2021-01-12)

50GBのテーブルを対象としているときは50GB以上の空き領域が必要。ディスク上に一時テーブルの格納場所だが、ほとんどの場合、元のテーブルと同じディレクトリにsql_XXXXというファイル名で生成する。

つまり、元テーブルの格納ディレクトリがマウントされているディスクの空き容量を確認する必要がある。

テーブルの格納ディレクトリの確認

MySQLにログインし、以下コマンドを実行する。

YumaInaura「MySQL | データディレクトリの場所を確認する ( select @@datadir )」Qiita,2017-05-16(参照:2021-01-13)

mysql> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

ディスクの空き容量確認

「dfコマンドについてまとめました【Linuxコマンド集】」エンジニアの入り口,リナックスアカデミー,2016-10-04(参照:2021-01-13)

以下のコマンドを実施する。

# df -h /var/lib/mysql
Filesystem Size  Used Avail Use% Mounted on
/dev/sda1  5.1G  2.4G  2.5G  49% /

テーブルの使用量確認

ikenji「MySQLでDBとテーブルのサイズを確認するSQL」Qiita,2019-12-13(参照:2021-01-13)

MySQLにログインし、以下のコマンドを実行する。

>  SELECT  
    table_name, engine, table_rows AS tbl_rows,
    avg_row_length AS rlen,  
    (data_length+index_length)/1024/1024 AS all_mb,
    (data_length)/1024/1024 AS data_mb,
    (index_length)/1024/1024 AS index_mb
FROM 
    information_schema.tables  
WHERE
    table_schema='{$DATABASE}' 
ORDER BY
    (data_length+index_length) DESC;
+----------------------------+--------+----------+-------+-------------+------------+------------+
| table_name                 | engine | tbl_rows | rlen  | all_mb      | data_mb    | index_mb   |
+----------------------------+--------+----------+-------+-------------+------------+------------+
| history_uint               | InnoDB |    48725 |   107 | 10.03125000 | 5.01562500 | 5.01562500 |
(省略)
| housekeeper                | InnoDB |        0 |     0 |  0.01562500 | 0.01562500 | 0.00000000 |
+----------------------------+--------+----------+-------+-------------+------------+------------+
113 rows in set (0.03 sec)

ディスク空き容量は足りるか?

例えば、history_uintALTER TABLEすると考える。

  • ディスク空き容量 : 2.5GB
  • テーブル使用量 : 10.03125000MB=0.009796142578GB≒0.001GB

余裕で足りそうです。
実際、Zabbix 3.0からZabbix 4.0にアップデートする場合は、ALTER TABLEが必要なテーブル分、計算する必要があります。

参考資料

「[ALTER TABLEを上手に使いこなそう。」漢のコンピュータ道,2009-05-11(参照:2021-01-12)]

「巨大なサイズのテーブルに対するALTER TABLE実行時間の概算方法」PAYFORWARD,2016-04-07(参照:2021-01-12)

YumaInaura「MySQL | データディレクトリの場所を確認する ( select @@datadir )」Qiita,2017-05-16(参照:2021-01-13)

「dfコマンドについてまとめました【Linuxコマンド集】」エンジニアの入り口,リナックスアカデミー,2016-10-04(参照:2021-01-13)

ikenji「MySQLでDBとテーブルのサイズを確認するSQL」Qiita,2019-12-13(参照:2021-01-13)

ツイッターでシェア
みんなに共有、忘れないようにメモ

batica@初心者はここからわかりません。

Crieitは誰でも投稿できるサービスです。 是非記事の投稿をお願いします。どんな軽い内容でも投稿できます。

また、「こんな記事が読みたいけど見つからない!」という方は是非記事投稿リクエストボードへ!

有料記事を販売できるようになりました!

こじんまりと作業ログやメモ、進捗を書き残しておきたい方はボード機能をご利用ください。
ボードとは?

コメント