いきさつ
レポート用として1時間毎にデータを作成しているのですが、レコード数が増え続けた結果、ストレージが予想以上に減ってました。
しかも、このままのペースで減ると、3ヶ月後には残ストレージがゼロに。。。
現状の確認
MySQLのバージョンは5.6.34です。
RDSのt2.smallで起動しています。
まずは下記SQLでサイズの大きいテーブルを確認
SELECT
table_name,
table_rows,
ROUND((data_length + index_length) / 1024 / 1024) AS total_size_MB, -- 合計サイズ
ROUND(data_length / 1024 / 1024) AS data_size_MB, -- データサイズ
ROUND(index_length / 1024 / 1024) AS index_size_MB -- インデックスサイズ
FROM
INFORMATION_SCHEMA.TABLES
ORDER BY
total_size_MB DESC
;
一番大きなテーブルで約5200万レコードありました。
このテーブルからデータを一部残して削除したいと思います。
削除その1 単純にDELETE(結果:失敗)
LIMITで件数指定しながら削除しました。
が、時間がかかり過ぎます。
10万件の削除で10分以上かかり、そんなこと何度もやってられませんね。。。
削除その2 既存テーブルにパーティション追加(結果:未実施)
年月単位のパーティションを作成し、古いものから消していこう!
と思ったのですが、パーティション作成中は書き込みロックがかかりますね。
試しに試験系の1600万レコードでパーティション作成してみると、完了まで約40分。
本番の5200万レコードだと単純計算でも約3倍。つまり120分以上。
冒頭でお話しした1時間毎のINSERT処理に影響が出るので、別の方法を考えることに。
削除その3 一時テーブル(結果:OK!)
残すレコードを一時テーブルに保存。
その後、テーブル名の変更で対応することができました。
なお、数ヶ月後に同じ状況になることが予想されるので、削除しやすいようパーティションも作成しておきます。
一時テーブルを作成
PKやINDEXの定義ごとコピーした、空のテーブルを作成します。
CREATE TABLE
tbl_name_tmp -- 一時テーブル
LIKE tbl_name -- 元のテーブル
;
パーティション作成
パーティションを作っておけば、次からの削除が楽になります。
ALTER TABLE
tbl_name_tmp
PARTITION BY RANGE COLUMNS(date_column) (
PARTITION p201701 VALUES LESS THAN ('2017-02-01 00:00:00'),
PARTITION p201702 VALUES LESS THAN ('2017-03-01 00:00:00'),
PARTITION p201703 VALUES LESS THAN ('2017-04-01 00:00:00'),
(省略)
PARTITION p202110 VALUES LESS THAN ('2021-11-01 00:00:00'),
PARTITION p202111 VALUES LESS THAN ('2021-12-01 00:00:00'),
PARTITION p202112 VALUES LESS THAN ('2022-01-01 00:00:00'),
PARTITION p999912 VALUES LESS THAN (MAXVALUE)
);
(参考)パーティション指定の削除
パーティション指定の削除は一瞬で終わります。
ALTER TABLE
tbl_name_tmp
DROP PARTITION
p201701 -- 削除するパーティション名
;
データのコピー
件数が多い場合は条件を指定し、何回かに分けたほうが早いかもしれません。
INSERT INTO
tbl_name_tmp -- 一時テーブル
SELECT
*
FROM
tbl_name -- 元のテーブル
WHERE
date_column >= '2017-02-01 00:00:00' -- 残すデータの抽出条件
AND date_column <= '2017-03-01 00:00:00'
;
テーブル名を変更
ALTER TABLE tbl_name RENAME TO tbl_name_old; -- 元のテーブルに「_old」をつける
ALTER TABLE tbl_name_tmp RENAME TO tbl_name; -- 一時テーブルの「_tmp」を消す
これで次回からはパーティション単位で削除できるはず。
つまり、作業時間は数分で終わるはず。
できればパーティションのDROPを定期実行するのが良いのですが、そこは後日調べます。(^ ^;)