できるのか? その謎を解き明かすためスタッフはアマゾンのお口へと向かった……。
関係ないけど MacBook Air 2020 出ましたね。
想定するパターン
つまり mysqldump
してデータ移す前後で、「無事に成功してるよなあ、ああーん?」と相手にイチャモンつけられた時用です。(経験済み)
事前に知っておくこと
mysqldump
を実行すると、出力された結果は OPTIMIZE
されたものになります。
Another way to perform a defragmentation operation is to use mysqldump to dump the table to a text file, drop the table, and reload it from the dump file.
訳文: 最適化操作を実行する別の方法は、mysqldumpを使用してテーブルをテキストファイルにダンプし、テーブルを削除して、ダンプファイルからテーブルをリロードすることです。
参考: dev.mysql.com
つまり、定期的に OPTIMIZE
を用いてメンテナンスしているような環境でない想定です(今回の想定するパターンだと無管理状態とします)。
これがわかってなくて、一回敗北しました……。
確認方法
MySQL のクエリに
CHECKSUM TABLE
というのがあるので、これで確認できる。MySQL CLI からデータベースとテーブルのサイズを取得する、という方法もある
- 以下に記述する内容によってほぼすべての場合で役に立たないので、ファイルサイズ比較は当てにしない方が良い
MySQL CLI からサイズを取得する
mysqldump
を実行すると、 OPTIMIZE
によるデフラグが実行された後に SQL が生成される。
そのため、移行前と移行後で以下のようなクエリでファイルサイズ取得すると、差分が生じて「アババババッ!!」となる。
データベース全体の容量を取得する SQL
SELECT table_schema, SUM(data_length+index_length) /1024 /1024 AS MB FROM information_schema.tables GROUP BY table_schema ORDER BY SUM(data_length+index_length) DESC;
テーブル毎の容量を取得する SQL
USE データベース名; SELECT table_name, engine, table_rows AS tbl_rows, avg_row_length AS rlen, FLOOR((data_length+index_length)/1024/1024) AS allMB, FLOOR((data_length)/1024/1024) AS dMB, FLOOR((index_length)/1024/1024) AS iMB FROM information_schema.tables WHERE table_schema=database() ORDER BY (data_length+index_length) DESC;
CHECKSUM TABLE による比較
CHECKSUM TABLE
構文は、以下のように SELECT 権限で実行されるとのことなので、フラグメンテーション対象の領域を利用しないはずです。
このテーブルに対する SELECT 権限が必要です。
参考: dev.mysql.com
こちらを用いる事で、 OPTIMIZE
による未使用領域回収の影響を受けない、比較に利用できる値を取得できるはずです。
ということで、 mysqldump
実行前後で値を取って、これでなんとかしましょう。
クエリ実行例
- users: ユーザ情報の入ったテーブル
USE データベース名; mysql> CHECKSUM TABLE users; +-------------------------------+------------+ | Table | Checksum | +-------------------------------+------------+ | example_test_grateful.users | 1234567890 | +-------------------------------+------------+ 1 row in set (0.00 sec)