MySQL パーティションで大規模なレコードを管理しよう

ホーム > 業務日誌 > スタッフ別 > > MySQL パーティションで大規模なレコードを管理しよう

 

MySQLで運用していると100万レコードほどになってくるとレスポンスが悪くなります。WEBサービスのチャットや履歴といったログデータを、ユーザ側で『最初から最後まで見れて当たり前』な設計をすると、ユーザが増加してチャットデータが爆発的に増えた場合にデータベースが膨らみます。そのまま全部保存でいくといつかは会話の時系列でサーバをわけることになります。

 

そもそも1年前の会話ログとか本当に必要かな?

確かにごく一部消したくない必要なチャットログもあるはずです。恋人同士の特別な記念になる会話とか、そんな時は特別な会話だけ保存して、他は期間を決めて削除されていく仕様に切り替えるといった仕様に割り切ると、データもスリムにお財布にも良いです。

 

MySQL パーティション

時間が経つにつれ価値がなくなっていくログ系データに適用しやすい、レコードを効率的に管理できるようにすることが出来るMySQLのパーティション機能のご紹介。

 

※某大規模チャットアプリも2週間~1か月程度で自動削除される仕様なはず。ユーザ参照用のDBはスリムにしておいて、管理用に別のストレージ用のDBを作って全て貯めておけば蓄積データとして活用できます。

 

パーティションの効用

  • SELECT時の全体スキャンを部分スキャンで探索できる。
    WHEREでパーティションのカラムを指定することで、適切な範囲にオプティマイザがアクセスするのでパーティション内の検索が早い。
  • DELETEに強い。パーティションで切れば大量DELETEをDROPに置き換えて実行出来るので超高速!

 

パーティション使用上の注意

  •  運用面が変わります、本当に必要かよく考えて導入する。
  • レンジパーティションの範囲に指定するカラムをPRIMARY KEYに含めておかなければいけない。その為主キーの再設定が必要になる場合があります。
  • 未指定のパーティション範囲にデータが入らなくなるので、管理業務が発生する。メンテナンス時にREORGNIZEで再編成するか、LESS THAN MAXVALUEで受け皿にする、数年単位の分を予めパーティションを切っておくなど考える必要があります。

今回使ったミニサーバ

  • MySQLバージョン5.6.34
  • スペック1CPU メモリ1GB SSD

特別に良いサーバでなくても大丈夫。

 

 

まずはテーブルの作成

 

 

適当にデータを10件入れます。

 

中身を見てみる

 

結合を使って1000万レコードを追加します。

レコード総数の確認を行います。

1000万と10レコード入っているのがわかります。結合指定を増やせば、1億でも10億でもこのように簡単にテスト用のレコードを追加出来ます。

 

試しに100件のデータを表示してみます。

 

パーティション作成

今回は100万レコードずつレンジパーティションを切ることにします。基本的にパーティションは細かくざくざく切っていって、参照時に1つのパーティション内で参照が済むようになっているとパフォーマンスがいいです。idの数や時系列でレンジで範囲指定したパーティションを設定出来ます。今回は単純にidの数でパーティションを切ります。

 

1千万件のレコードのパーティションで1分44秒かかる。

Query OK, 10000010 rows affected (1 min 43.88 sec)
Records: 10000010 Duplicates: 0 Warnings: 0

 

1億レコードの場合 ※1CPU 1GB
Query OK, 100000010 rows affected (13 min 47.08 sec)
Records: 100000010 Duplicates: 0 Warnings: 0

 

【パーティションの消し方】

パーティションを消す前に該当のパーティションに含まれる範囲のレコードをdumpしておくと安心(*’▽’)

パーティション削除

消したパーティションの範囲のレコードを表示

空ですね。

 

確認

レコード総数を見てみましょう。

余分なデータまで削除されていないかな?っと次のパーティションの範囲を確認。

次のパーティションのレコードは無事です。

 

レコード総数の確認してみましょう。

綺麗に100万レコード消えています。

 

パーティション追加

REORGNIZEを使ってpoverパーティションの間に割り込ませるように再編成することで、パーティションの追加設定が可能です。

 

 

パーティションを導入すると、パーティション管理が加わり運用が大きくかわります。時系列で組む場合は数年分まとめてパーティションを切っておくなど、システム設計をよく考えて導入して下さいね。

 

優 ( エンジニア )

この記事を書いた人:優 ( エンジニア )

システムガーディアン爆弾処理班。アクセス負荷対策やNginxへの移行案件が多いこの頃。IBM SoftLayerやAWSなどクラウド案件も多くなってきました。

この記事に関してのお問い合わせ
御連絡・ご返信は原則2営業日以内を予定しております。
お急ぎの場合は、お手数ですが下記電話でもご対応をしております。

システムガーディアン株式会社
受付時間:平日9:00~18:00
受付担当:坪郷(つぼごう)・加藤
電話:03-6758-9166