MariaDB/MySQLデータベースの圧縮・デフラグ・最適化の完全ガイド

本記事では、MySQL/MariaDBにおけるテーブルやデータベースの圧縮・デフラグメンテーション(断片化解消)の手法を詳しく解説します。これらの方法を活用することで、データベースが配置されているディスクの容量を大幅に節約できます。

大規模プロジェクトのデータベースは時間とともに急激に肥大化していき、「どう対処すべきか」という課題が必ず発生します。解決策としては、古いデータを削除してデータ量を減らす、データベースを小さく分割する、サーバーのディスク容量を増設する、テーブルを圧縮・縮小する、といった方法が挙げられます。

また、データベース運用においてもう一つ重要なのが、パフォーマンス向上のために定期的にテーブルやデータベースをデフラグ(最適化)することです。

InnoDBテーブルの圧縮と最適化

ibdata1ファイルとib_logファイルの問題

InnoDBテーブルを使用している多くのプロジェクトでは、ibdata1やib_logファイルが巨大化する問題に悩まされています。原因の多くは、MySQL/MariaDBの設定ミスか、データベース設計上の問題にあります。InnoDBテーブルのすべての情報はibdata1ファイルに格納されますが、このファイルの領域は自動的には解放されません。そこでおすすめしたいのが、テーブルデータを個別のibd*ファイルに保存する方法です。そのためには、my.cnfに以下の行を追加します。

innodb_file_per_table

または

innodb_file_per_table=1

すでにサーバーが稼働しており、InnoDBテーブルを含む運用中のデータベースがある場合は、以下の手順を実行します。

1. サーバー上のすべてのデータベース(mysqlとperformance_schemaを除く)をバックアップします。ダンプは以下のコマンドで取得できます:# mysqldump -u [ユーザー名] -p[パスワード] [データベース名] > [ダンプファイル.sql]
2. バックアップ作成後、mysql/mariadbサーバーを停止します。
3. my.cnfの設定を変更します。
4. ibdata1およびib_logファイルを削除します。
5. mysql/mariadbデーモンを起動します。
6. バックアップからすべてのデータベースを復元します:# mysql -u [ユーザー名] -p[パスワード] [データベース名] < [ダンプファイル.sql]

これらの手順を実行すると、すべてのInnoDBテーブルが個別のファイルとして保存され、ibdata1が際限なく肥大化するのを防げます。

InnoDBテーブルの圧縮

テキストやBLOBデータを含むテーブルは圧縮することで、かなりのディスク容量を節約できます。

ここでは、圧縮効果が期待できるテーブルを含む「innodb_test」データベースを例に説明します。作業を始める前に、必ずすべてのデータベースをバックアップしておきましょう。まずMySQLサーバーに接続します。

# mysql -u root -p

MySQLコンソールで対象のデータベースを選択します。

# use innodb_test;

テーブル一覧とそれぞれのサイズを表示するには、以下のクエリを実行します。

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in (MB)"
FROM information_schema.TABLES
WHERE table_schema = "innodb_test"
ORDER BY (data_length + index_length) DESC;

「innodb_test」の部分は、実際のデータベース名に置き換えてください。

いくつかのテーブルは圧縮可能です。ここでは「b_crm_event_relations」テーブルを例に取り上げます。以下のクエリを実行します。

mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT=COMPRESSED;

実行後、テーブルサイズが26MBから11MBまで圧縮されたことが確認できます。

テーブルを圧縮すればホストのディスク容量を大きく節約できます。ただし、圧縮テーブルを操作する際はCPU負荷が高まる点に注意が必要です。ディスク容量は不足しているがCPUリソースには余裕がある場合に、圧縮を検討するとよいでしょう。

MyISAMテーブルの圧縮(MySQL/MariaDB)

MyISAMテーブルを圧縮するには、MySQLコンソールではなくサーバーコンソールで専用コマンドを実行します。テーブルを圧縮するには、以下のコマンドを実行します。

# myisampack -b /var/lib/mysql/test/modx_session

「/var/lib/mysql/test/modx_session」は対象テーブルへのパスです。今回は大きなテーブルがなかったため小さなテーブルで試しましたが、それでも効果は確認できました(25MBから18MBへ圧縮)。

# du -sh modx_session.MYD

25M modx_session.MYD

# myisampack -b /var/lib/mysql/test/modx_session

Compressing /var/lib/mysql/test/modx_session.MYD: (4933 records)
- Calculating statistics
- Compressing file
29.84%
Remember to run myisamchk -rq on compressed tables

# du -sh modx_session.MYD

18M modx_session.MYD

コマンドでは-bオプションを使用しました。このオプションを付けると、圧縮前にテーブルがバックアップされ、「OLD」というラベルが付与されます。

# ls -la modx_session.OLD

-rw-r----- 1 mysql mysql 25550000 Dec 17 15:20 modx_session.OLD

# du -sh modx_session.OLD

25M modx_session.OLD

MySQL/MariaDBのテーブルとデータベースの最適化

テーブルやデータベースを最適化するには、デフラグメンテーション(断片化の解消)を行うことが推奨されます。まず、データベース内にデフラグが必要なテーブルがないか確認しましょう。

MySQLコンソールを開き、データベースを選択して、以下のクエリを実行します。

select table_name, round(data_length/1024/1024) as data_length_mb, round(data_free/1024/1024) as data_free_mb from information_schema.tables where round(data_free/1024/1024) > 50 order by data_free_mb;

これにより、未使用領域が50MB以上あるテーブルがすべて表示されます。

+-------------------------------+----------------+--------------+
| TABLE_NAME | data_length_mb | data_free_mb |
+-------------------------------+----------------+--------------+
| b_disk_deleted_log_v2 | 402 | 64 |
| b_crm_timeline_bind | 827 | 150 |
| b_disk_object_path | 980 | 72 |

data_length_mb:テーブル全体のサイズ
data_free_mb:テーブル内の未使用領域

これらがデフラグ候補のテーブルです。実際にどれだけのディスク容量を占有しているか確認してみましょう。

# ls -lh /var/lib/mysql/innodb_test/ | grep b_

-rw-r----- 1 mysql mysql 402M Oct 17 12:12 b_disk_deleted_log_v2.MYD
-rw-r----- 1 mysql mysql 828M Oct 17 13:23 b_crm_timeline_bind.MYD
-rw-r----- 1 mysql mysql 981M Oct 17 11:54 b_disk_object_path.MYD

これらのテーブルを最適化するには、MySQLコンソールで以下のコマンドを実行します。

# OPTIMIZE TABLE b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;

デフラグが正常に完了すると、次のような結果が表示されます。

+-------------------------------+----------------+--------------+
| TABLE_NAME | data_length_mb | data_free_mb |
+-------------------------------+----------------+--------------+
| b_disk_deleted_log_v2 | 74 | 0 |
| b_crm_timeline_bind | 115 | 0 |
| b_disk_object_path | 201 | 0 |

ご覧のとおり、data_free_mbが0になり、テーブルサイズも大幅に削減されました(約3〜4分の1)。

また、サーバーコンソールからmysqlcheckを使ってデフラグを実行することもできます。

# mysqlcheck -o innodb_test b_workflow_file -u root -p innodb_test.b_workflow_file

「innodb_test」はデータベース名、「b_workflow_file」はテーブル名です。

データベース内のすべてのテーブルを最適化するには、サーバーコンソールで以下のコマンドを実行します。
# mysqlcheck -o innodb_test -u root -p

さらに、サーバー上の全データベースをまとめて最適化することも可能です。

# mysqlcheck -o --all-databases -u root -p

最適化前後でデータベースサイズを比較すると、合計サイズが削減されていることがわかります。

# du -sh

2.5G

# mysqlcheck -o innodb_test -u root -p

innodb_test.b_admin_notify
note : Table does not support optimize, doing recreate + analyze instead
status : OK
innodb_test.b_admin_notify_lang
note : Table does not support optimize, doing recreate + analyze instead
status : OK
innodb_test.b_adv_banner
note : Table does not support optimize, doing recreate + analyze instead
status : OK

# du -sh

1.7G

このように、サーバーのディスク容量を節約するためには、MySQL/MariaDBのテーブルやデータベースを定期的に最適化・圧縮することが有効です。なお、最適化作業を行う前には、必ずデータベースのバックアップを取得することを忘れないでください。