MySQLゆるふわ運用のためのアグレッシブ開発 ～ データを増やさないための設計と運用方針について(主にパーティション活用の話)
MySQL Casual Talks #6
Jul 11th, 2014
Profile
Recent Output
- App::LLEvalBot
- App::CPANGhq
- MySQL::Partition
- Test::mysqld::DatadirDumper
今日のお話
- MySQLのデータ量を如何に抑えて、メモリに載せきるかという話をします
- 結構パーティションの話をしますが、当たり前の話も多いかもしれません
- 運用よりも開発者向け
- かじゅある！
ゆるふわMySQL運用
- スケールアップで頑張りたい気持ち
- アプリケーションやアーキテクチャを複雑にする前にお金で解決する
- シャーディングとか絶対したくない
- slave参照も極力したくない
- データをメモリに載せきる!(←重要)
最近のハードウェア事情
- ちゃんとインデックスの効いたクエリを投げてる分には数万QPSとかいける
- メモリ100Gとかカジュアルに使える
- マスタ1台 + ホットバックアップ用slaveでかなりいける
- ダウンタイムを許容できるならRDSでも
データをメモリに載せきるために
- パーティションの活用
- 論理削除を避ける
- データを増やさない
- データを効率よく消す仕組みをつくる
パーティションの活用
- MySQLのDELETEは遅いので一度の大量のデータを消すのに時間がかかる
- パーティションを設定することで、大量のデータを瞬時に消しこむことができる
パーティションの制限
- パーティション対象カラムは全てのユニークキー制約に含まれていないといけない
- 外部キー制約を使えない
- 個数制限(1024/8192)
- パーティションを切っていない領域にデータを格納できない
パーティションの種類
- RANGEパーティション
- デイリーで切る
- id, created_dateとかでprimary key
- 保持期間を設定して、期間が過ぎたデータを消す
- LISTパーティション
- 期間データを持っているマスタ系のデータのid(event_idなど)毎に切る
- id, event_idなどでprimary key
- マスタデータのend_atが経過して一定期間が経過したら消す
RANGEパーティション
ALTER TABLE hoge PARTITION BY RANGE COLUMNS (created_date)
( 'p20140711' VALUES LESS THAN '2014-07-11' )
(RANGE COLUMNSはMySQL5.5以降)
LISTパーティション
ALTER TABLE hoge PARTITION BY LIST (event_id)
( 'p1' VALUES IN (1) )
created_dateでRANGEパーティションを切ることの功罪
- autoincrementのidのユニーク制約が「全く」担保されなくなる
- idだけでロックをかけると変なところ(別のパーティション)にもロックがかかる(WHERE INとか)
対策(1)
created_dateをDATETIMEじゃなくてDATEにする
メリット
- WHERE条件にcreated_dateを含めればユニーク制がある程度含まれる
デメリット
- 複数日分をWHERE INで取ってきたい時とかに破綻する
- 一日未満のパーティションを切れない
対策(2)
created_dateを使わずにidでRANGE PARTITIONを切る
メリット
- idのユニーク制約が保たれ、データの堅牢性が維持される
デメリット
- 先々のパーティションを余裕を見て作っておかないとエラーになる
(LESS THAN MAXVALUEしておくことはできるけどそこにデータが入ったら負け)
- 定期的にautoincrementの値を監視して自動でつくるなどの仕組みが必要
- 厳密に一日単位でパーティションを区切る等ができない
等々
色々メリット・デメリットはあるが、カジュアルにINSERTできるようになるのがパーティションのメリットの一つ
KVSを使って頑張って引き回していた一時セッション情報とかも、カジュアルにINSERTして後で簡単に消せると思えばINSERTへの抵抗感もない
パーティションの運用
Rangeパーティション
- 毎日未明に翌日分のパーティションを作成するバッチをcronで回す
Listパーティション
- マスタデータ投入時に関連テーブルにパーティションを設定
データを増やさない仕組み
論理削除を避ける
- MySQLのDELETEは遅いのでは？
- 案外なんとかなる(数百行くらいまで一気に消すとかであれば)
- DELETEして履歴テーブルに(BULK)INSERTする
- 履歴テーブルはパーティションを切って消していく
- → 削除フラグのカラムを意識して変なindexを張らずに済む
- → クエリもアプリケーションもシンプルになる
そのバッチ処理は必要か
例えばユーザー全員プレゼント等をする場合
- × バッチで全員に付与する
- → 死にレコードが増えてしまう
- → 時間がかかる(ヘタをしたら終わらない)
- ◯ ユーザーアクセスのタイミングで付与する
- giftテーブルのようなマスターテーブルを作成し、付与期間を設定する
- user_gift_hisoryのようなテーブルでユーザーの受け取り履歴を管理する
- gift_idでLISTパーティションを設定しておく
- 付与期間が終了したらパーティションを削除する
データ型に気をつける
- マスタデータなどでSMALLINTで良いところはSMALLINTにするとか
- リレーションとかで地味に効いてくる(かも)
- ユーザー関連テーブルとかは余裕を見てBIGINTにはしておきましょう
- 余り神経質になる必要はないし、気にしすぎて設計に時間取られても負け
- 日付をunsigned intにするとかはあまり好きではない
パーティションに関する懸念事項
古いデータをそう簡単に削除できないんですが…
- ECとかだとあるある
- MySQL5.6以降には
EXCHANGE PARTITIONてのがあります(今日知った)
- パーティションをごそっと別テーブルに移すことができる
- 削除データをEXCHANGEするテーブル作って、プロダクション用はBLACKHOLEにしておいて、データ保全用のSLAVEには保存されるようにするとかすると良さそう！
以上