MariaDB / MySQLデータベースの圧縮、デフラグ、最適化

この記事では、MySQL / MariaDBでのテーブル/データベースの圧縮と最適化のいくつかの方法について説明します。これは、データベースが配置されているディスクのスペースを節約するのに役立ちます。

大規模なプロジェクトのデータベースは時間とともに非常に大きくなり、それをどうするかという疑問が常に生じます。問題を解決する方法はいくつかあります。古い情報を削除したり、データベースを小さなデータベースに分割したり、サーバーのディスクサイズを増やしたり、テーブルを圧縮/縮小したりすることで、データベース内のデータ量を減らすことができます。

データベース機能のもう1つの重要な側面は、パフォーマンスを向上させるために、テーブルとデータベースを時々最適化する必要があることです。

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

ibdata1およびib_logファイル

InnoDBを使用するほとんどのプロジェクト テーブルには大きなibdata1の問題があります およびib_log ファイル。ほとんどの場合、これは間違ったMySQL/MariaDB構成またはDBアーキテクチャに関連しています。 InnoDBテーブルのすべての情報は、ibdata1ファイルに保存されます。このファイルのスペースは、それ自体では再利用されません。テーブルデータを個別のibd*に保存することを好みます ファイル。これを行うには、次の行を my.cnfに追加します ：

innodb_file_per_table

または

innodb_file_per_table=1

サーバーが構成されていて、InnoDBテーブルを備えた生産的なデータベースがある場合は、次の手順を実行します。

1. サーバー上のすべてのデータベースをバックアップします（mysqlとperformance_schemaを除く）。次のコマンドを使用してデータベースダンプを取得できます。# mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
2. データベースバックアップを作成したら、mysql/mariadbサーバーを停止します;
3. my.cfgの設定を変更します;
4. ibdata1を削除します およびib_log ファイル;
5. mysql/mariadbデーモンを起動します;
6. バックアップからすべてのデータベースを復元します：# mysql -u [username] –p[password] [database_name] < [dump_file.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リソースに問題はないが、ディスク容量に問題がある場合は、dbテーブルに圧縮を使用します。

MySQL/MariDBでのMyISAMテーブル圧縮

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

+-------------------------------+----------------+--------------+
| 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 |

-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

+-------------------------------+----------------+--------------+
| 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 |

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

2.5G

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

1.7G