テクノロジー

2020.07.27

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

“トップ画像”

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

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

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

MySQLのレプリケーション構図

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

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

sourceサーバー replicaサーバー
STATEMENT STATEMENT

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

sourceサーバー replicaサーバー
ROW STATEMENT

そのため、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の設定値で、バイナリログのフォーマットを指定できる機能が増えました。それぞれの設定値とその説明を説明します。

設定値 説明
STATEMENT SQLベースでレプリケーションをする設定。
sourceで実行したSQLをそのままbinlogとして書き込み、
replicaでそのSQLを実行することでデータの整合性を担保します。
ROW 行ベースでレプリケーションをする設定。
sourceで変更された行データをバイナリログに書き込み、
replicaでその変更を読み込みデータの整合性を担保します。
MIXED STATEMENTとROWをMySQLが自動で使い分けて
レプリケーションする設定。
基本的にはSTATEMENTでロギングするが、
LIMITがついた時など特定の条件下では、
ROWでロギングされます。

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

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

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

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

id name price
1 商品1 100
2 商品2 150
  • ダーティリード
    トランザクション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, '商品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

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

トランザクション分離レベル ダーティリード ファジーリード ファントムリード
READ UNCOMMITTED 起こる 起こる 起こる
READ COMMITTED 起こらない 起こる 起こる
REPEATABLE READ 起こらない 起こらない 起こる
SERIALIZABLE 起こらない 起こらない 起こらない

出典(著者訳)
Isolation (database systems)(外部サイト)

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に設定しないと追加、更新、削除が実行できなくなることもわかっていただけたのではないかと思います。
もし同様なことがあれば参考にしていただけたら幸いです。


井出 諒
Yahoo!ショッピング ストア領域バックエンドエンジニア
Yahoo!ショッピングの商品を支えるストアツールの開発と運用をしています。

Yahoo! JAPANでは情報技術を駆使して人々や社会の課題を一緒に解決していける方を募集しています。詳しくは採用情報をご覧ください。

関連記事

このページの先頭へ