500 views

PARTITIONでDBのデータをまるっと消しちゃおう

タグ: /

はじめまして!エンジニアのまてぃこです。
ゆるりと更新していこうと思うので
これからよろしくお願いします!!

早速ですが、今日はこの前対応したおはなしでも。

やりたいこと

MySQLにユーザーサーポート用のログデータを保存していたのですが
運用していくうちに1億レコードくらいあるテーブルの存在に気づいた訳です。

数ヶ月前のものまで保存しておく必要はないので
過去のものはcronで削除するようにしたいなーと。

どうしたものか。

懸念点

データ量が多すぎる (1億レコードくらいある)。
delete テーブル名 where 日付 とかだと時間かかりそう。

対応案

テーブルにパーティションはって
1か月分とかをまとめてサクッと削除したい。

パーティション削除するだけだからこんな感じで一瞬で終わるしね。

ALTER TABLE log_table DROP PARTITION p201605;

まぁ、最初からパーティション貼っておけば良かったのだが
もう手遅れ…
なのでメンテナンス中にテーブルを変更することに。

作業内容

対象のテーブルはこんな感じ。

パーティション張るためにはカラムがprimaryに含まれてる必要がある。
ただ、現在のPRIMARY KEYがAUTO_INCREMENT になってるので

  1. idにindexを追加
  2. PRIMARY KEY削除
  3. created_atでPRIMARY KEY作成

の手順じゃないとPRIMARY KEYを変更できない
実際にはこんな感じ。

1.indexを追加

2.PRIMARY KEY削除

3.id, created_atでPRIMARY KEY 作成

4.index削除

5.パーティション作成

やってみた

やはりデータ量多いので終わるかが不安。

そして案の定3の
alter table log_table add primary key(id, created_at);
あたりからメンテナンスの残り時間が気になりだした。

MySQLのalter tabeの処理件数は
ステータス変数 Handler_write に記録されるので

で進捗状況をチェックしてみるが
進みが悪い・・・
このままじゃいつ終わるかわからないよね。

作業変更

これはテーブル作り直した方が早いじゃないかと気づく…
実行中のalterのプロセスをkill…
1) 既存のテーブルをリネーム

2) パーティション付きのテーブルを作成

あとは数ヶ月だったらテーブルをDROPするだけでoK
管理画面でログデータ見れるようにしてたので、そこだけ調整しました。

結論

  • データ量多いテーブルにalterはしない方がいい
  • 最初からパーティションつけよう
  • メンテナンスの時間は余裕を持って。

参考

http://blog.takanabe.tokyo/2016/04/08/2382/
https://dev.mysql.com/doc/refman/5.6/ja/alter-table-partition-operations.html

シェアする

プロフィール

まてぃこ Lv 1

戦士

HP : 250 /250

SP : 50 /50

遊ぶこと&筋トレ&お酒が大好き。
どうやったら楽して仕事ができるかしか考えていない。

週に4日は運動しないと発狂します。

▼ おすすめ記事

  • クリエイティブ・オブ・ゴッド

    アニメーション基礎② 意外と動いて見えるかも

  • クリエイティブ・オブ・ゴッド

    #2.イラストクエスト★仕様書の読み方(経験値100pt)

  • クリエイティブ・オブ・ゴッド

    ストリートファイター裏話連発!

  • 開発の謎

    トシムーニョの開発雑記:第2回 面白さのロジック2「足し算」