MySQLのレプリケーション設定で起きたトラブルの原因とその解決策

はじめまして、Yahoo!ショッピングでシステム開発を担当している井出です。
Yahoo!ショッピングのストアの入稿データを支えるデータベースでは、冗長構成を取るためにsource・replica構成を取っています。
今回はsource・replica構成のレプリケーション設定で実際にトラブルが起きた時の話とその解決方法について紹介します。

binlog_formatをROWからSTATEMENTにしたら更新できなくなった

前提として、Yahoo!ショッピングのストアの入稿データを支えるデータベースでは、MySQLのレプリケーションによってsourceサーバーからreplicaサーバーにデータを複製しています。



今回sourceサーバーでトラブルが起こったため、新たに2台あるreplicaサーバーのうちを1台を新sourceに昇格させ、新source・新replica間でレプリケーションを開始したところ、レプリケーションが追いつかず、どんどん遅延していることに気がつきました。
他の正常のデータベースと設定値比較すると、正常なサーバーのbinlog_formatの設定はSTATEMENTに対し、対象のsourceサーバーではROWになっていることに気がつきました。

1.正常なMySQLサーバーのbinlog_formatの値

2.問題が起こったMySQLサーバーのbinlog_formatの値

そのため、binlog_formatの設定を合わせるため問題が起こったのsourceサーバーをSTATEMENTにして対処したところ、今度は更新処理で以下のようなエラーが出ていました。

ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

これによりsourceに書き込む処理ができなくなってしまいました。

原因

これは、対象のデータベースサーバーのトランザクション分離レベルがREAD-COMMITTEDになっていたのが根本的な原因でした。
MySQLのInnoDBテーブルでは、binlog_formatをSTATEMENTにする時は、トランザクション分離レベルをREPEATABLE READに設定しないといけません。

参考
https://dev.mysql.com/doc/refman/5.6/ja/binary-log-setting.html
ここに気づかず、今回sourceに書き込み処理がができなくなっていました。
この設定がなぜいけないのかを解説していきます。

レプリケーションとbinlog_formatの関係

前述した通り、MySQLではレプリケーションを行うことで冗長構成を取ることができます。
簡単にレプリケーションの仕組みを解説すると、sourceサーバーでレコードの追加や更新、削除が行われるとバイナリログが発行され、replicaサーバーに転送されreplicaサーバーでは送られてきたバイナリログを元にsourceサーバーで実行された追加、更新、削除の処理を再現し実行します。そうすることによって、sourceとreplicaサーバーでデータの整合性を担保します。
MySQL5系からはbinlog_formatの設定値で、バイナリログのフォーマットを指定できる機能が増えました。それぞれの設定値とその説明を説明します。

参考
https://dev.mysql.com/doc/refman/5.6/ja/binary-log-setting.html

また、ROWのデメリットとして、変更される行が多い場合、レプリケーションがとても遅くなります。Yahoo!ショッピングのストアの入稿では、1回の更新で数万のレコードが変更される場合もよくあります。
そのため、冒頭で書いたレプリケーションが遅延していたのはこれが原因でした。

トランザクション分離レベルとは

次に、トランザクション分離レベルについてです。
トランザクションは、他のトランザクションに影響を与える現象があります。それぞれの名前と意味を説明します。
例えば以下の様なitemテーブルがあった場合の例を交えて紹介します。

• ダーティリード
  トランザクション1,2がある時、トランザクション2でコミットされていないデータをトランザクション1で読み取ってしまう現象のことです。
  

  transaction1                                        transaction2
    |                                                   |
  SELECT price FROM item WHERE id = 1;                  |
  →100が取得される                                        |
    |                                                   |
    |                                               UPDATE item SET price = 200 WHERE id = 1;
    |                                                   |
  SELECT price FROM item WHERE id = 1;                  |
  →200が取得される(コミット前のデータ)                        |
                                                    COMMIT;

• ファジーリード(ノンリピータブルリード)
  トランザクション1,2がある時、トランザクション2でコミットされたデータをトランザクション1で読み取ってしまう現象のことです。トランザクション1ではトランザクション2の内容が途中で変わる危険性があります。

  transaction1                                        transaction2
    |                                                   |
  SELECT price FROM item WHERE id = 1;                  |
  →100が取得される                                        |
    |                                                   |
    |                                               UPDATE item SET price = 200 WHERE id = 1;
    |                                               COMMIT;
    |                                                   
  SELECT price FROM item WHERE id = 1;                     
  →200が取得される(トランザクション途中に内容が変わる)

• ファントムリード
  トランザクション1,2がある時、トランザクション2で新規レコード追加(または削除)されたデータを、トランザクション1で読み取ってしまう現象のことです。

  transaction1                                        transaction2
    |                                                   |
  SELECT * FROM item WHERE price BETWEEN 100 AND 200;   |
  →レコードが2件取得される                                  |
    |                                                   |
    |                                               INSERT INTO item(id,name,price) VALUES ( 3, '商品３', 150 );
    |                                               COMMIT;
    |                                                   
  SELECT * FROM item WHERE price BETWEEN 100 AND 200;
  →レコードが3件取得される(トランザクション途中に内容が変わる)

参考
https://en.wikipedia.org/wiki/Isolation_(database_systems)#Read_phenomena

トランザクション分離レベルによって、上記の発生する現象が変わり、MySQLではtransaction-isolationという設定値で設定が可能です。
トランザクション分離レベルには、READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLEの4つのレベルがあります。

参考
https://dev.mysql.com/doc/refman/5.6/ja/set-transaction.html

以下がトランザクション分離レベルごとに違いをまとめた表です。

MySQLのinnodbではREPEATABLE READがデフォルトになっています。
また、InnoDBでREPEATABLE READの設定の場合ではファントムリードも発生しません。これは、MVCCをサポートするためです。

参考
https://en.wikipedia.org/wiki/Isolation_(database_systems)#Isolation_levels,_read_phenomena,_and_locks

本質的な原因と解決策

MySQLではsourceは並列でプロセスを処理していくのに対して、replicaでは流れてきたバイナリログを直列で実行していきます。そのため、sourceのようにトランザクションが並列で流れるようなことはありません。
仮にSQLベースでレプリケーションをするSTATEMENTの場合、sourceでダーティリード、ファジーリード、ファントムリードが起こる状態ではreplicaでトランザクション中に同様のダーティリード、ファジーリード、ファントムリードが起こらないため、データの整合性が合わないケースが考えられます。
これを防ぐために、MySQLのInnoDBではbinlog_formatをSTATEMENTに設定する時は、トランザクション分離レベルをREPEATABLE READに設定しないとエラーになるような仕組みが導入されています。
上記の仕組みを踏まえて、レプリケーションが進まない現象を解決するため、sourceサーバーのトランザクション分離レベルをREPEATABLE READに設定してから、binlog_formatをSTATEMENTに設定することで、問題なくsourceサーバーでレコードの追加、更新、削除が行われるようになりました。
また、これが起こった原因として、運用している複数のデータベースサーバーの中で、my.cnfの設定値がずれているサーバーが存在し、再起動時に間違った設定が読み込まれていた事も発覚しました。
うっかり忘れがちな問題ですが、MySQLの設定値を確認する際には、SHOW VARIABLESで設定値を確認するほか、my.cnfに書かれている設定値についても問題がないことを確認する必要があります。

参考
https://dev.mysql.com/doc/refman/5.6/ja/show-variables.html

まとめ

今回はYahoo!ショッピングで実際に起きた、MySQLのレプリケーション周りの設定の注意点をご紹介いたしました。
バイナリログのフォーマットはシステムの特性に合わせた設定にすることが大切です。今回、特に多くのレコードが更新する様な大規模システムにはROWが合わないことがよくわかりました。
そしてInnoDBでバイナリログのフォーマットをSTATEMENTにするときには、トランザクション分離レベルをREPEATABLE READに設定しないと追加、更新、削除が実行できなくなることもわかっていただけたのではないかと思います。
もし同様なことがあれば参考にしていただけたら幸いです。