はじめに

mysqldumpを利用してデータをコピーする前に、予めデータの容量を確認したく、方法を調べました。

(mysqldumを利用したデータの移行方法はこちらの記事に記載しました。)

環境は以下の通りです。

OS: Ubuntu
MySQL version: 5.6

方法

以下のSQLを実行することで、各DBのデータ容量を確認できます。

confirm-data-size.sql

## GB単位で取得
SELECT 
    SUM(ROUND(((data_length + index_length) / 1024 / 1024 / 1024),
            2)) `Total Size in GB`
FROM
    information_schema.TABLES
GROUP BY table_schema;

WHERE句で対象テーブルや対象スキーマを取得することも可能です。以下のSQLは、あるDBを指定した上で前方一致でデータ容量を取得します。

confirm-data-size-2.sql

## MB単位で取得
SELECT 
    SUM(ROUND(((data_length + index_length) / 1024 / 1024),
            2)) `Total Size in MB`
FROM
    information_schema.TABLES
WHERE
    table_schema = 'db_name'
        AND table_name LIKE 'table_prefix_%';

容量が正しく取得されているのか検証する

ここからは蛇足ですが、SQLで取得した結果がディスクの容量と一致しているのかが気になり、検証してみました。

前提

前提として、Dockerを利用してMySQLを動かしており、/var/lib/mysqlがローカルの ~/mount_dirにマウントされているものとします。

このマウントされているディレクトリの容量を確認しました。

SQL実行

上記のSQLを利用して、「test」というDBのサイズを確認します。

mysql> SELECT table_schema AS 'DB Name', ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 1) AS 'DB Size in GB' FROM information_schema.tables WHERE table_schema = 'test';
+---------+---------------+
| DB Name | DB Size in GB |
+---------+---------------+
| test    |         122.0 |
+---------+---------------+

マウント先の容量確認

次に、マウントしている先のディレクトリでDB名ごとにフォルダがあるのを見つけたので、そちらの容量を確認します。

$ sudo du -sh ~/mount_dir/test
139G	/home/ubuntu/mount_dir/test
（以下略)

サイズが合わない…。

なぜサイズが異なるのか？

以下の記事を見つけました。

ubuntu - Substantial difference between database size under MySQL and actual size on disk - Database Administrators Stack Exchange

If you are using InnoDB tables, the size of your ibdata files will grow over time. So, if you issue DELETE statement, your database size will reduce, but the ibdata file will remain the same (not reduce).

つまり、ibdata（共有テーブルスペース）のサイズは時間を経過すると増加することが原因です。例えば、DELETE文を発行してもibdataは削除されない（ので、容量が増えていく）、とのことでした。

また、他にもログがあるはずなので、これもサイズ増大の原因になり得るかと思います。（違ったら、コメントで教えてください。）

結論

SQLでデータ容量は正しく確認可能ですが、ibdata等他にもMySQL上で増大するデータがあり、そちらもある程度考慮した上で十分なディスク容量を確保することが必要です。

参考

MySQL - How to get the database size in MySQL? | TablePlus
ケーワン・エンタープライズのエンジニアメモ(｀･ω･´)ゞﾋﾞｼｯ!!: テーブルスペースとログファイルについて（ibdata*、ib_logfile*)