MySQLでOPTIMIZEしてみる

2022/06/20

暫く放置してデータが溜まっていたMySQLサーバがある。ストレージをいい感じに圧迫していたので、スッキリできんかなぁということでOPTIMIZEというのを試してみたい。

状況確認

現在どれだけ使っているのかをまず確認する。

mysql> SELECT table_schema, 
    ->        floor(SUM(data_length + index_length) / 1024 / 1024) AS ALL_MB,
    ->        floor(SUM((data_length) / 1024 / 1024)) AS DATA_MB,
    ->        floor(SUM((index_length) / 1024 / 1024)) AS INDEX_MB
    -> FROM information_schema.tables
    -> GROUP BY table_schema
    -> ORDER BY sum(data_length + index_length) DESC;
+--------------------+--------+---------+----------+
| table_schema       | ALL_MB | DATA_MB | INDEX_MB |
+--------------------+--------+---------+----------+
| hoge               |  13233 |    5619 |     7613 |
| information_schema |      0 |       0 |        0 |
+--------------------+--------+---------+----------+
2 rows in set (0.01 sec)

hogeデータベースが13GBほど使っているらしい。

ちなみに、使っているデータ量をテーブルごとに見るのはこうらしい。

SELECT table_name, 
       engine AS DBエンジン, 
       table_rows AS 行数,
       avg_row_length AS 平均レコード長,
       floor((data_length+index_length) / 1024 / 1024) AS ALL_MB,
       floor(data_length / 1024 / 1024) AS DATA_MB,
       floor(index_length / 1024 / 1024) AS INDEX_MB
FROM information_schema.tables
WHERE table_schema = database()
ORDER BY (data_length + index_length) DESC;

ではディスク上ではどれだけ使っているのだろうか。

/var/lib/mysql# du -sh ./* | grep hoge
17G	./mpos

一応、数GBの乖離があるみたいだ。

OPTIMIZEしてみよう

テーブルごとに見てみると、特定のテーブルが突出して利用しているのがわかるので、このテーブルについて叩いてみる。

OPTIMIZE TABLE table;
+-------------+----------+----------+-------------------------------------------------------------------+
| Table       | Op       | Msg_type | Msg_text                                                          |
+-------------+----------+----------+-------------------------------------------------------------------+
| hoge.table  | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| hoge.table  | optimize | error    | The table 'table' is full                                        |
| hoge.table  | optimize | status   | Operation failed                                                  |
+-------------+----------+----------+-------------------------------------------------------------------+
3 rows in set, 1 warning (25 min 52.57 sec)

optimizeできてないやんけ。

まず、Optimizeは使えないらしい。その代わりにrecreate + analyzeすると。でもテーブルがfullになったのでできませんと。
そのときにdf -hをなんとなく見ていたのだけど、20GBあった容量がどんどん使われて、1GBくらいになったのを目撃したので、多分これストレージ不足っぽい。

今回動いている環境はVultrのVPSなので、オブジェクトストレージなら追加できるが、メインストレージを拡げるのは、VPSプランを上げるしかないか…？