MySQLの最適化でdata_freeの件数が0にならない

Question

前提・実現したいこと

MySQLの断片化を解消して、最適化したいと考えています。

data_freeの件数が0になれば断片化を解消できると思っていますが、
なにをやってもdata_freeが0になりません。エラーも発生しません。

やり方が間違っているのでしょうか？
それとも、そもそもの考え方が間違っているのでしょうか？

発生している問題

ALTERやOPTIMIZEのSQLを実行しても、data_freeが0になりません。
コマンドのmysqlcheckも同じです。
Dumpしてデータベースを再構築しても、他の実行環境も同じです。

試したこと

SQL

ALTER TABLE test5.Categories ENGINE INNODB;
OPTIMIZE TABLE test5.Categories;

コマンド

mysqlcheck -o test5 -u root -p

その他

• データベースの再構築
• 異なる実行環境（以下の補足にある開発環境とサーバ環境ともに同じでした。）

補足情報

開発環境

• macOS Big Sur 11.2.1
• MySQL：5.7.32

サーバ環境

• さくらインターネット
• FreeBSD 11.2
• MySQL 5.7

実行した結果（例

以下に、OPTIMIZEを使った場合の状況を記載しました。

断片化を確認

mysql> SELECT table_schema, table_name, data_free, table_rows FROM information_schema.tables WHERE table_schema = 'test5';
+--------------+------------------------+-----------+------------+
| table_schema | table_name             | data_free | table_rows |
+--------------+------------------------+-----------+------------+
| test5        | Addresses              |         0 |       1896 |
| test5        | Categories             |   2097152 |      20923 |

OPTIMIZEを実行

mysql> OPTIMIZE TABLE test5.Categories;                                                                        
+------------------+----------+----------+-------------------------------------------------------------------+
| Table            | Op       | Msg_type | Msg_text                                                          |
+------------------+----------+----------+-------------------------------------------------------------------+
| test5.categories | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test5.categories | optimize | status   | OK                                                                |
+------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.10 sec)

断片化が解消されたか確認

mysql> SELECT table_schema, table_name, data_free, table_rows FROM information_schema.tables WHERE table_schema = 'test5';
+--------------+------------------------+-----------+------------+
| table_schema | table_name             | data_free | table_rows |
+--------------+------------------------+-----------+------------+
| test5        | Addresses              |         0 |       1896 |
| test5        | Categories             |   2097152 |      20923 |

data_freeの件数が0にならず、変化も見られません。

以上。
ご多忙だと思いますが、助言をいただけると非常に助かります。
よろしくお願い致します。

Answer 1

OPTIMIZE TABLEの実行にはMySQLの設定が必要だった気がします。
設定が不要な、以下を試してみて下さい。

ALTER TABLE test5.Categories ENGINE INNODB;

追記

手元の環境で試してみました。
・データ件数によりdata_freeの最小値がある。
・圧縮されるのはその最小値まで。

ですので、圧縮してもdata_free=0にならない場合はあるようです。

Comments

[novonovo]

早速ありがとうございます。
ALTER TABLE test5.Categories ENGINE INNODB;
こちらも試しましたが、同じでした。

mysql> ALTER TABLE test5.Categories ENGINE INNODB;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SELECT table_schema, table_name, data_free, table_rows FROM information_schema.tables WHERE table_schema = 'test5';
+--------------+------------------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------------------+-----------+------------+
| test5 | Addresses | 0 | 1896 |
| test5 | Categories | 2097152 | 20923 |

[sazi]

最適化するための資源自体が不足しているのかもしれませんね。
空き容量はdata_freeのサイズより大きいですか？

[sazi]

根本的な解決にはなりませんが、一旦データをCSVなどで退避して、テーブルを再度createして退避したデータをロードする事で改善されないでしょうか。
後はRESETしてみるとか。
https://dev.mysql.com/doc/refman/5.6/ja/reset.html

[novonovo]

返答が遅くなり申し訳ございません。
空き容量は100GBとかあるので大丈夫だと思います。

RESET MASTER;
RESET QUERY CACHE;
この2つを実行しましたが、状況は変わりませんでした。

RESET SLAVE;
こちらはよくわからないのですが、以下のようにエラーが出ます。
mysql> RESET SLAVE;
ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.

CSVも試しましたが、以下のように、おかしなことが起こっています。
Categoriesからエクスポートしたデータを新たに作成したCategories_newにインポートしたのですが、
以下のようにdata_freeが増えました。table_rows一致しません。
+--------------+------------------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------------------+-----------+------------+
| test5 | Addresses | 0 | 1896 |
| test5 | Categories | 2097152 | 20923 |
| test5 | Categories_new | 4194304 | 20815 |

以下を実行しました。
mysql> ALTER TABLE test5.Categories_new ENGINE INNODB; Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

すると元のCategoriesと同じ値になりました。
+--------------+------------------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------------------+-----------+------------+
| test5 | Addresses | 0 | 1896 |
| test5 | Categories | 2097152 | 20923 |
| test5 | Categories_new | 2097152 | 20923 |

どういうことなのでしょうか？？

[novonovo]

上記について、以下のSQLでテーブルを作成後に、CSVをインポートしました。
CREATE TABLE test5.Categories_new LIKE test5.Categories;

改めて、インポート時にテーブルを作成（MySQL Workbench）しALTER TABLEを実行しましたが、同じような結果です。
しかし、table_rowsの値が異なります。
+--------------+------------------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------------------+-----------+------------+
| test5 | Addresses | 0 | 1896 |
| test5 | Categories | 2097152 | 20923 |
| test5 | Categories_new | 2097152 | 20923 |
| test5 | categories_new2 | 2097152 | 21060 |

それと気がついたのですが、以下の3つのテーブルもdata_freeが同じ値で、table_rowsは異なります。
Propertiesだけが異なります。
| test5 | Images | 2097152 | 128132 |
| test5 | Properties | 6291456 | 19826 |
| test5 | PropertyWebPages | 2097152 | 23021 |
| test5 | Towns | 2097152 | 117171 |

table_rowsが異なるのに、data_freeがまったく同じ値になるってことがあるのでしょうか？

[sazi]

innodb_file_per_tableはONになっていますか？
ONになっていないと、圧縮はされません。
https://dev.mysql.com/doc/refman/5.6/ja/tablespace-enabling.html

[novonovo]

上記のPropertiesはALTER TABLEを実行しています。

試しに、新たにtest6というDBを作成し、上記と同じようにインポート時にテーブルを作成（MySQL Workbench）しALTER TABLEを実行しましたが、同じような結果です。
mysql> SELECT table_schema, table_name, data_free, table_rows FROM information_schema.tables WHERE table_schema = 'test6';
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| test6 | categories | 4194304 | 20532 |
+--------------+------------+-----------+------------+

mysql> ALTER TABLE test6.categories ENGINE INNODB;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SELECT table_schema, table_name, data_free, table_rows FROM information_schema.tables WHERE table_schema = 'test6';
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| test6 | categories | 2097152 | 21060 |
+--------------+------------+-----------+------------+

何かが根本的におかしいと思うのですが。。。新しい環境でも駄目なので、さっぱりわかりません。

ちなみに、本投稿をするまえに、壊れているのかなと思って、PCだけでなく、サーバ（さくらインターネット）で試しても同じ結果ということは、壊れているというは無さそうな気がします。
サーバで試した際はDB作成後に、SQLでテーブル作成、データインポートを実行しています。

[novonovo]

> innodb_file_per_tableはONになっていますか？
以下のように、ONになっています。

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)

[sazi]

> table_rowsが異なるのに、data_freeがまったく同じ値になる
内容的に管理データとしての最小値のような感じですね。

再起動は試してみているでしょうか？

[novonovo]

MySQLとパソコンの再起動をしましたが、結果は変わらないようです。

[sazi]

回答に追記しましたので、確認下さい。
違うテーブルでも同じ値になる事からも、管理ページに収まり切れない場合は、data_freeが0にならないような印象です。

[novonovo]

ありがとうございます。
| Categories | InnoDB | 2097152 | 20923 |
| Images | InnoDB | 2097152 | 136155 |
と、件数は異なりますが、最小値があるということですね。
ALTER TABLEによって、data_freeの数が減ってはいますので、これが限界ということですね。

ちなみに、面白い現象を発見しました。
MyISAMに変換してやるとdata_freeが0になりました。

mysql> ALTER TABLE test5.Categories ENGINE MyISAM;
Query OK, 20892 rows affected (0.12 sec)
Records: 20892 Duplicates: 0 Warnings: 0

mysql> select table_name, engine, data_free, table_rows from information_schema.tables where table_schema = 'test5';
+------------------------+--------+-----------+------------+
| table_name | engine | data_free | table_rows |
+------------------------+--------+-----------+------------+
| Addresses | InnoDB | 0 | 1896 |
| Categories | MyISAM | 0 | 20892 |

しかし、またInnoDBに戻すと同じになります。

mysql> ALTER TABLE test5.Categories ENGINE InnoDB;
Query OK, 20892 rows affected (0.12 sec)
Records: 20892 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE test5.Categories ENGINE InnoDB;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select table_name, engine, data_free, table_rows from information_schema.tables where table_schema = 'test5';
+------------------------+--------+-----------+------------+
| table_name | engine | data_free | table_rows |
+------------------------+--------+-----------+------------+
| Addresses | InnoDB | 0 | 1896 |
| Categories | InnoDB | 2097152 | 20923 |

saziさん、長々とありがとうございました。
今回は、これを限界とするか、MyISAMに変更するかを検討します。

[sazi]

> MyISAMに変換してやるとdata_freeが0になりました。
data_freeはInnoDBで管理されるものですからね。
それを理由に変更するのは得策ではないと思います。

[novonovo]

そうなのですね。では、これを限界として様子を見ます。
WEBサイトのレスポンスを改善しようと模索していたのですが、どうもDBが遅いような感じでした。
それで、今回の最適化に行き着いたのですが、data_freeが0になれば、少しは改善するかと考えました。
data_freeは減りましたが、レスポンスの体感では、あまり変わらないような感じです。
不要なJOINを減らした方がレスポンスはよくなりました。
他にもボトルネックがないか探してみます。

ありがとうございました。