MySQLの物理削除によるパフォーマンスの悪化とその回避策について

はじめまして、Yahoo!ショッピングでシステム開発を担当している村上です。
Yahoo!ショッピングでは数億件にのぼる商品が日々更新されています。
今回はそれを支える巨大なDBの運用の中で遭遇したMySQLのアンチパターンと、回避した方法について紹介いたします。

特定のテーブルをJoinするとすごく遅くなる

Yahoo!ショッピングでは商品を出品するためのツールがあります。
商品情報には「商品名」「価格」といった、任意で設定可能な項目のほか、「ブランド」「商品種別」など、製品ごとに入力する内容が決まっている項目を、マスター情報としてテーブルで管理しています。
このマスター情報を利用して、出品の際に入力情報が正確であるかどうか確かめる処理があります。

この中の特定のテーブルをJoinすると、通常であれば10秒もあれば完了するクエリが、1000秒たっても完了しない、といった現象が発生しました。

原因

MySQLのテーブルの統計情報が想定と異なる挙動をしていた事が原因でした。
以下のクエリは、itemというテーブルに対して、masterデータを格納したテーブル「tbl1」をJOINし、お互いのmaster_idが突合できれば、itemテーブルのis_permitカラムに「1」を付与する。という処理です。

UPDATE
  item
INNER JOIN tbl1 ←問題のテーブル
 ON item.master_id = tbl1.master_id
SET
  item.is_permit = "1";
WHERE item.id = "00AB1"

ここで、「EXPLAIN」というコマンドを使ってクエリの実行計画を取得してみると、適切にINDEXが使用されていない事がわかりました。

           id: 1
  select_type: SIMPLE
        table: tbl1                ←tbl1のJOINに関する実行計画
         type: range
possible_keys: PRIMARY, key1, key2 ←このキーがINDEXの候補として上がっているのに、
          key: NULL                ←ここにNULLが入っている部分が想定と異なる。
      key_len: key_len
          ref: const
         rows: 611296
        Extra: Using where; Using temporary; Using fil

この実行計画の何が良くないのか解説していきます。

MySQLのオプティマイザと実行計画について

より具体的な事象の説明に入る前に、MySQLにおけるオプティマイザと実行計画について紹介します。
MySQLは記述されたSQLを解釈して最適にテーブルを操作するための手順を作り出す、オプティマイザと呼ばれるコンポーネントが搭載されています。
同じクエリでもオプティマイザがどのように解釈するかによって実行時間は大きく変わります。
オプティマイザの中身は統計情報やテーブルの状態によって常に変化しており、ユーザーが直接オプティマイザの状態の確認はできません。

また、クエリを実行する際はオプティマイザを使ってどのようにクエリを実行していくかを示した「実行計画」が払い出されます。
こちらはユーザーも参照可能で、実行計画を取りたいクエリの先頭に「EXPLAIN」を付与する事によって実行計画を参照できます。

参考: 8.8.2 EXPLAIN 出力フォーマット
https://dev.mysql.com/doc/refman/5.6/ja/explain-output.html

以下は実行計画の表示例です。

           id: 1
  select_type: SIMPLE
        table: tbl1
         type: range
possible_keys: PRIMARY, key1, key2
          key: key1
      key_len: key_len
          ref: const
         rows: 1
        Extra: Using where; Using temporary; Using filesort

いろいろなパラメータがありますが、この記事を読み進めるにあたって以下の項目についておさえておいてください。

possible_keys: 利用候補のキー一覧（主キー、INDEX含む）
          key: 実際に候補から選択される想定のキー
         rows: 操作するレコードの合計行数の見積もり

取得した実行計画の問題点

問題のクエリの実行計画を通常通り動いているサーバーと、パフォーマンスが落ちているサーバーで比較してみました。
まずはパフォーマンスが落ちているサーバーで実行計画を取得したところ、以下の結果を得ました。
これは候補となるキーが「PRIMARY」「key1」「key2」と挙げられているにもかかわらず、実際にはキーを選択していないため、INDEXを使わずテーブル内を全件操作しています。

           id: 1
  select_type: SIMPLE
        table: tbl1
         type: range
possible_keys: PRIMARY, key1, key2
          key: NULL
      key_len: key_len
          ref: const
         rows: 611296
        Extra: Using where; Using temporary; Using filesort

一方で、通常通り動いているサーバーで同じクエリの実行計画を取得してみたところ、以下のように表示されました。
候補となるキーは変わらないのですが、実際に「key1」を選択する事ができており、
このテーブルの操作に関する見積もり行数は1行と、先程の611296行の操作とはかなりの差があります。

           id: 1
  select_type: SIMPLE
        table: tbl1
         type: range
possible_keys: PRIMARY, key1, key2
          key: key1
      key_len: key_len
          ref: const
         rows: 1
        Extra: Using where; Using temporary; Using filesort

つまり、テーブル定義やヒント句などでINDEXを利用するように指定していたとしても、
オプティマイザはINDEXを使わない方が良いと誤判断しているため、計算は非常に遅いものとなります。

本質的な原因

直接的な原因は実行計画が想定と異なる状態を示していた事が理由ですが、本質的には大量のDELETE/INSERTによって、意図しない統計情報に更新されていた事が原因でした。
問題のテーブルは、日時のバッチ処理でテーブル内のデータをDELETE文で全件物理削除した後、INSERTでデータ全件洗い替え更新をする。という運用をしていました。
ただ、このように一度に大量にデータを物理削除すると、統計情報が正しく更新できなくなる可能性があります。

具体的に説明します。
例えば、以下のようなレコードが入ったテーブルがあるとします。
ここで2行目のデータBを物理削除し、データDを追加するとします。

［表a.］データ長:3行

1: データA
2: データB ←削除
3: データC

するとテーブルが格納された物理ファイル内では、2行目を空欄にして4行目に新しくデータを書き出す事があります。
これを断片化（フラグメンテーション）と呼びます。

［表b.］データ長:4行

1: データA
2: *空欄*
3: データC
4: データD

オプティマイザは統計情報から実行計画を生成するのですが、［表a.］では1,2,3行目のデータを参照して、統計情報を更新し、［表b.］では、1,2,3,4行目のデータを参照して統計情報を更新します。
ここで重要なポイントとして挙げられるのが、MySQLは統計情報を更新する際に、全てのデータをサンプリングする訳ではないという事です。
例えば、テーブルに50万件のデータがあっても、サンプリングとして利用されるのはinnodb_stats_persistent_sample_pagesに記載された数のレコードだけにとどまり、デフォルトでは20件しか選択されません。

参考: 4.13.16.1 永続的オプティマイザ統計のパラメータの構成
https://dev.mysql.com/doc/refman/5.6/ja/innodb-persistent-stats.html

そこで、実際にはデータが存在しない行を統計情報の更新に使う情報としてサンプリングした場合、実態とは異なる統計情報を更新してしまい、オプティマイザが正しく実行計画を払い出す事ができなくなります。
今回のようなケースでは大量のデータを物理削除していた関係上、［表b.］における、2行目のような欠番状態のレコードが大量に発生し、統計情報の更新に使われる可能性が高くなっていました。
事象が発生した際には、実際に断片化されたテーブルで、欠番状態のレコードが選択されて統計情報が更新され、極端なパフォーマンスの悪化が発生していたと考えられます。

対応方法

以下のように OPTIMIZE TABLE コマンドを利用し、テーブルが格納された物理ファイル内の空間が［表c.］のように実体のあるレコードのみとなるように再構築する事でクエリのパフォーマンスが改善しました。

［表c.］データ長:4行  

1: データA  
2: データC  
3: データD

この状態であれば、どのレコードが統計情報の更新に使う情報としてサンプリングされても、実体と等しいため、正しく実行計画を払い出す事ができます。
possible_keysにキーが選択されている状態で、keyにNULLが入る状況がおかしいため、一度テーブル内を最適化すれば、実行計画が治ると考えました。

参考: 13.7.2.4 OPTIMIZE TABLE 構文
https://dev.mysql.com/doc/refman/5.6/ja/optimize-table.html

パフォーマンス改善の結果と、それを踏まえたロジックの修正

以下はYahoo!ショッピングで OPTIMIZE TABLE を打った前後に計測した、実際に実行速度の変化です。

条件

• テーブルを6件JOIN
• 最大2万件のレコード更新

計測結果

• 実施前: 174768秒
• 実施後: 2.7秒

これを踏まえて、洗い替え処理の改修が済むまでの間、Yahoo!ショッピングでは以下の暫定対応を行いました。

マスターテーブル更新ロジックの修正

• マスターテーブル更新直後、トランザクションCOMMITする直前に OPTIMIZE TABLE を自動で実行するように修正

結果、ほぼ毎日発生していた急激なパフォーマンス悪化があまり発生しなくなりました。

ただ、このような修正を行ってもたまにパフォーマンス悪化が発生する場面がありました。
原因を調査したところ、JOIN先のテーブルではなくJOIN元のテーブルの統計情報も不調であったようでした。

JOIN元のテーブルもワーキングのテーブルとして利用しており、DELETE-INSERTが実行されていました。
ただし、テーブル全体の件数が少ないため、マスターテーブルほどは障害が発生しませんでした。
こちらは発生ベースで修復クエリを打つような運用を暫定的に実施する事で一時的に安定しました。

このようにJOIN先のテーブルだけではなく、JOIN元のテーブルにも問題がないか確認する事が重要です。

サービス稼働中にOPTIMIZE TABLEを打たなければいけなくなったら

サービスインしているDBがリアルタイムで障害を起こしていた際にも OPTIMIZE TABLE を行う事で事象が改善しますが、注意が必要です。
OPTIMIZE TABLE を実行するとテーブル全体にロックをかけてしまう事から、トランザクションを完全に止めてしまうのです。

そのため、 OPTIMIZE TABLE を実行したい際には可能な限り該当DBをサービスアウトしてから実行する事や、サービスアクセスが比較的緩やかな時間帯に実行する事をおすすめします。
ただし、マスターサーバーで障害が発生した場合など、どうしても今すぐに事態を収めたい場合は、オンライントランザクションを止めていい時間と相談しながら OPTIMIZE TABLE を実行する事となります。

また、想定よりも長い時間のトランザクションを止めてしまった場合、途中でコマンドを強制終了しても問題ありません。
ストレージエンジンとしてInnoDBを利用している場合、OPTIMIZE TABLE は、途中までワーキング領域にクローンテーブルを生成し、完成したら実テーブルと入れ替えるような動きします。
そのため、途中でコマンドを止めてしまっても復元できるような作りになっています。

参考: 13.7.2.4 OPTIMIZE TABLE 構文
https://dev.mysql.com/doc/refman/5.6/ja/optimize-table.html

また、OPTIMIZE TABLE を実行する前に該当テーブルでトランザクションが走ってない事を確認する事も大切です。
OPTIMIZE TABLE はトランザクションを待ってしまう上に、後続のクエリを全てせき止めてしまうためです。

Yahoo!ショッピングでは、この問題に対処するため、1秒おきに SELECT * FROM information_schema.PROCESSLIST を実行して、修復したいテーブルを操作する更新クエリが走ってない瞬間に OPTIMIZE TABLE を実行するスクリプトを作成しました。
実際に何度か運用しましたが、人が何度も SHOW PROCESSLIST を実行して実行のタイミングを図るよりも効率的に対処できました。

正しい姿にするために

今回のケースではデータを大量に物理削除した後にOPTIMIZEをせず、テーブル内に空の領域を残したままにしておいた事で、統計情報の更新がうまく行かず、実行計画の払い出しの動作に影響を与えたと考えられます。
そのため、MySQLではバッチ処理などでテーブル内のデータを全件洗い替えるような操作は控えるべきです。
どうしても必要な場合は、DELETE後に必ず OPTIMIZE TABLE をするか、一度テーブルをDROPしてからデータを作り直すような作りにすべきであると考えます。

まとめ

今回はMySQLの物理DELETEでパフォーマンスが悪化する現象と、その対策方法をご紹介いたしました。
大規模に物理DELETEを行うと統計情報が想定と異なる状態になる事があります。そのような際には OPTIMIZE TABLE を実行し、テーブルを再構築する事で一定の効果が得られます。
また、将来的には大量の物理DELETEを行わなくて済むような設計をするのが良いと思います。
このような問題で困っている人がいれば、ぜひ参考にしていただけるとうれしいです。