時系列データの削除について
サービスを構築する場合、どのようなサービスでも時系列に沿ってデータを記録するログのようなテーブルは、必ずと言って良いほど存在しています。
この時系列でデータを記録するログテーブルは、サービスが稼働し続ける限り追記され続けます。データが溜まり続けるとデータベースのストレージ容量を圧迫するため、ある程度の期間を過ぎた古いデータはデータベース上からは削除することが一般的です。
テーブル定義例
多くのサービスでは、Primary keyに相当する一意となるデータがログにはないので、下記のテーブルのように AUTO_INCREMENT で一意となるログ用のIDを採番することが多いと思います。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE logs ( log_id BIGINT AUTO_INCREMENT, reg_date DATETIME NOT NULL, /* ログ記録日時 */ server_name VARCHAR(63) NOT NULL, service_name VARCHAR(63) NOT NULL, function_name VARCHAR(63) NOT NULL, log_level TINYINT NOT NULL, log_message TEXT NOT NULL, /* その他カラム */ PRIMARY KEY (log_id), INDEX idx_reg_date (reg_date) /* 検索範囲指定 & 削除用 */ /* 要件次第に合わせて追加のIndex */ )ENGINE=InnoDB; |
そして、削除バッチなどで一定期間が経過した古いデータを削除しようと下記のようなDELETE文を実行した場合、稼働中のサービスで以下のようなトラブルが起きることがあります。
|
1 2 3 4 |
DELETE FROM logs WHERE reg_date < DATE_SUB(now(), interval 3 month); /* Index idx_reg_date を利用 */ |
- DELETE文の実行に時間がかかり、実行中はサービスのレスポンスが悪化する
- DELETE文を実行した後も暫くレスポンスが悪い
- レプリケーションの遅延が非常に大きくなってしまう
1番目の原因は、削除対象の古いログデータがInnoDB buffer poolのキャッシュにないため対象のデータをストレージから読み取りが必要になってストレージへのI/Oがボトルネックになっている場合が多いです
2番目の原因は、InnoDB buffer poolのキャッシュヒット率の低下が原因である場合が多いです。削除対象のデータを探し出すためにInnoDB buffer poolに大量にロードした結果、サービスで頻繁にアクセスしていたデータが追い出されてしまいキャッシュヒット率が低下してしまいます。
3番目の原因は、1つのトランザクション分の変更を反映するに時間がかかるためなので、レプリケーションソース側で実行するSQLを工夫する必要があります。
これらの問題の原因は、『一度に大量のデータ』を『DELETE 文』で削除した結果なので、対策として良く取られる手段としては2通りの方法があります。
検証用サンプルデータ
対策の検証用に、1ヵ月１００万行で３か月分のテストデータを用意してみました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> SELECT -> SUM(IF(reg_date >= "2023-01-01 00:00:00" AND reg_date < "2023-02-01 00:00:00", 1, 0)) AS at_jan, -> SUM(IF(reg_date >= "2023-02-01 00:00:00" AND reg_date < "2023-03-01 00:00:00", 1, 0)) AS at_feb, -> SUM(IF(reg_date >= "2023-03-01 00:00:00" AND reg_date < "2023-04-01 00:00:00", 1, 0)) AS at_mar, -> COUNT(reg_date) -> FROM -> logs; +---------+---------+---------+-----------------+ | at_jan | at_feb | at_mar | COUNT(reg_date) | +---------+---------+---------+-----------------+ | 1000000 | 1000000 | 1000000 | 3000000 | +---------+---------+---------+-----------------+ 1 row in set (1.37 sec) |
仮想マシン環境なので比較が難しいですが参考までに、単純に日付を使って1か月分(100万件)を削除してみるとこれぐらいの時間がかかりました。
|
1 2 3 4 5 |
mysql> DELETE FROM -> logs -> WHERE -> reg_date <= "2023-02-01 00:00:00"; Query OK, 1000000 rows affected (41.51 sec) |
対策1: AUTO_INCREMENTのPrimary Keyを利用して一括削除はしない(細かく分割して削除する)
Primary keyがAUTO_INCREMENTなので、削除対象の古いログデータの最小値から最大値までの間を分割して細かく分けてデータの削除を行います。
|
1 2 3 4 5 6 7 |
SELECT MIN(log_id), /* ここから */ MAX(log_id) /* ここまでを適当な単位に分割して消す */ FROM logs WHERE reg_date < DATE_SUB(now(), interval 3 month); |
1回のトランザクションで削除する量を減らして、削除の間に待機時間を挟み込むことでサービスへの影響を減らすことができます。
|
1 2 3 4 5 6 7 |
DELETE FROM logs WHERE log_id >= 1000 /* 削除対象PK */ AND log_id <= 2000 /* 削除対象PK */ AND reg_date < DATE_SUB(now(), interval 3 month); /* 境界日時付近の安全策 */ |
この方法をとる場合の注意点としては、サービスに影響が出ない負荷になるように細かく削除する量を分割しているだけなので、削除を行う時間帯や削除速度をサービスの特性やサーバー性能に合わせて調整する必要があります。
対策1の効果検証
削除バッチを作成して実行中にほかのサービスに影響が出るかどうかの検証することはできないので、使用するSQLを実行してみてどの程度の差が出るか推測してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
mysql> SELECT -> MIN(log_id), -> MAX(log_id) -> FROM -> logs -> WHERE -> reg_date < "2023-02-01 00:00:00"; +-------------+-------------+ | MIN(log_id) | MAX(log_id) | +-------------+-------------+ | 1 | 1000000 | +-------------+-------------+ 1 row in set (0.26 sec) mysql> DELETE FROM -> logs -> WHERE -> log_id >= 1 -> AND log_id <= 1000 -> AND reg_date < "2023-02-01 00:00:00"; Query OK, 1000 rows affected (0.02 sec) |
1000行の削除で0.02秒なら100万件の削除をするには1000回繰り返せばよいので、削除バッチそのものの実行時間も早くなりそうです。
繰り返し実行するDELETE文の間に適切な待ち時間を設定できれば、他サービスへの悪影響を抑えられるでしょう。
対策2: パーティション機能を利用する
下記のように時間軸でパーティションを分割してパーティション単位で削除する方法です。
(例では月単位で分割)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TABLE logs_parted ( log_id BIGINT AUTO_INCREMENT, reg_date DATETIME NOT NULL, server_name VARCHAR(63) NOT NULL, service_name VARCHAR(63) NOT NULL, function_name VARCHAR(63) NOT NULL, log_level TINYINT NOT NULL, log_message TEXT NOT NULL, PRIMARY KEY (log_id, reg_date), INDEX idx_reg_date (reg_date) /* 検索範囲指定 */ )ENGINE=InnoDB PARTITION BY RANGE COLUMNS(reg_date) ( PARTITION 'logs_2023-01' VALUES LESS THAN ('2023-02-01 00:00:00'), PARTITION 'logs_2023-02' VALUES LESS THAN ('2023-03-01 00:00:00'), PARTITION 'logs_2023-03' VALUES LESS THAN ('2023-04-01 00:00:00'), PARTITION 'logs_2023-04' VALUES LESS THAN ('2023-05-01 00:00:00'), PARTITION 'logs_9999-12-31' VALUES LESS THAN MAXVALUE ); |
古いログの削除はパーティションのDROPになるので、大量のデータを削除しても一瞬で終わり InnoDB buffer poolに古い削除対象のデータを読み込む必要もなくなります。
|
1 2 3 4 |
ALTER TABLE logs_parted DROP PARTITION 'logs_2023-01'; |
その代わりパーティション機能を使う場合にはいくつか注意点があります。
- 確実に未来分のパーティションを作成して、MAXVALUEを設定したパーティション(上記例の場合
logs_9999-12-31)にはデータが保存されないように運用＆監視を行う必要があります。 - 削除単位がパーティション単位になるので、後から細かい調整をすることは困難です。
- 複数のパーティションを跨いだ検索は遅くなるので、大量のパーティションを跨ぐ検索処理等が必要な場合には向いていません。
- Primary keyにパーティションの分割に使うreg_dateカラムを含めた複合Indexで定義する必要があるため、元の定義に比べてIndexのサイズが大きく必要となるメモリやストレージのサイズも大きくなります
上記の通りいろいろと制約も多く適用できる範囲は狭いですが、パーティションで分割されているので一つのパーティション内で収まる場合には通常のテーブルより素早く動作します。
またinnodb_file_per_table を有効にしていた場合、パーティション単位で生成と削除が行われるためInnoDBデータファイルの断片化も防げます。
対策2の効果検証
logs_partedテーブルにもlogsテーブルと全く同じデータを入れてパーティションを削除します。
|
1 2 3 4 5 6 |
mysql> ALTER TABLE -> logs_parted -> DROP PARTITION -> 'logs_2023-01'; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 |
100万件分のデータ削除が一瞬で終わるので利用可能な場面では有効なテクニックではないでしょうか。
まとめ
時系列でデータを貯めて古いデータを消す仕組みはログテーブル以外でも必要とされる場面は多くあります。
サービスが成長して扱うデータ量が増えてくると様々な場所でパフォーマンスチューニングを必要とされる場面が増えてきます。その中であまり注目のされない古いデータの削除で起こりがちなトラブルとその対策を取り上げてみました。
この記事が読者の方の参考になれば幸いです。
