MySQLが数千秒遅れた元凶を捕まえる
5856 ワード
MySQLが数千秒遅れた元凶を捕まえる
背景
Part 1:一番前に書く
MySQLの遅延警報は皆さんもよく知られていると思いますが、MySQLがライブラリからの遅延を引き起こす原因はたくさんあります.ハードウェアから言えば、NIC、ディスク、メモリがボトルネックに達している可能性があります.データベースの面から言えば、SQLの効率が低下しているか、大量の書き込みが原因かもしれません.本文のケースはbinlogフォーマットによる遅延問題を分析し、本文を見てから、このような警告に出会ったとき、あなたが瞬時に問題の所在に位置づけることができると信じています.
Part 2:重点パラメータ分析
Property
Value
Command-Line Format
System Variable
Scope
Global, Session
Dynamic
Yes
Type (>= 5.5.31-ndb-7.2.13)
enumeration
Type (>= 5.5.15-ndb-7.2.1, <= 5.5.30-ndb-7.2.12)
enumeration
Type
enumeration
Default (>= 5.5.31-ndb-7.2.13)
Default (>= 5.5.15-ndb-7.2.1, <= 5.5.30-ndb-7.2.12)
Default
Valid Values (>= 5.5.31-ndb-7.2.13)
Valid Values (>= 5.5.15-ndb-7.2.1, <= 5.5.30-ndb-7.2.12)
Valid Values
周知のようにbinlog_formatはbinlogフォーマットを設定するパラメータで、STATEMENT、MIXED、ROWの3つのフォーマットに構成でき、動的に調整できます.3つのフォーマットにはそれぞれ欠けている.私たちのオンライン生産ライブラリはMIXED形式で統一的に構成されています.MIXED形式は、STATEMENT形式とROW形式でシーンによって異なる形式で切り替えられます.
Part 3:知識備蓄
MIXED形式では、次のような場合にbinlogが自動的にROW形式のレコードに変換されます
1.NDBエンジン
2.SQL文にはUUID()関数が含まれています.
3.自己成長フィールドが更新されました.
4.insert delayed文が含まれています.
5.ユーザ定義関数(UDF)が使用されている.
6.テンポラリ・テーブルが使用されています.
7.?もう1つはmixedフォーマットをROWに変換する場合であり,本稿では再現する.
実戦
Part 1:モニタリング
午前2時にはライブラリからの遅延が急増したが,ライブラリからの機器負荷やネットワークカードはボトルネックに達していないことが分かった.
Part 2:遅延原因分析
2時06分からbinlogのリフレッシュが非常に速く、ほぼ数十秒で1.1 GBのbinlogファイルがいっぱい書けることがわかります.これにより、書き込み量が大きすぎるため、基本的に特定できる.
書き込み量が多すぎる場合は、次の2つがあります.単純な業務量が急増し、QPSの増加が引き起こした. binlogがROW形式に移行すると、ストレージコンテンツが急増する.
ptツールpt-query-digestまたはコマンドラインを使用して、binlogがどのような操作をしたかを分析できます.pt-query-digestを使用するとmysqlbinlogコマンドと組み合わせてログを分析できます.
Part3:rootcase
delete from tablename where xxxx limit 100;
この構文はMIXED形式のbinlogをROW形式のレコードに変換しますが、筆者のケースのこのテーブルにはTEXTの大きなフィールドが含まれており、deleteのたびにTEXTの大きなフィールド全体をbinlogに持ち込み、binlogが急増し、ライブラリからメインライブラリに追いつかずに遅延が発生する場合があります.
Part 4:解決策
根本的な原因が見つかった後、解決して手応えがあって、関連する開発を見つけて、delete from table where xxx limitのこのような使い方を取り除いて、rowフォーマットの記録を避けることができます.
Warning:警告実はdelete/update limit、insert.....select limitという使い方は危険で、問題が発生しやすい.本当にこの方法を使うには、order by文を組み合わせてlimitの有効性を保証する必要があります.
このような文に遭遇した場合:
STATEMENTモードを使用すると、文ベースのコピーに対して安全ではないことを示す警告が表示されます.
STATEMENTモードを使用すると、ORDER BY句が1つあっても(したがって確定的である)、LIMITを含むDML文に警告が発行されます.これは既知の問題です.(BUG＃42851)
MIXEDモードを使用する場合、文はrowのモードコピーを使用します.
Part 5:公式文書
公式文書では、MIXEDフォーマットがROWフォーマットに変換されるのはいつなのか、limit文がMIXEDフォーマットをROWに変換するのは言及されていない.国内の多くの書籍やブログでも言及されていないが、このケースを記録し、この問題と将来この問題に遭遇する可能性がある読者に対して処理時間を節約し、できるだけ早く根源に位置づけることを望んでいる.
公式文書は、MIXED形式がlimit構文を使用するときにROW形式に変換されて他の章に記録されていることについて、以下のように説明しています.
Statement-based replication of When using When using
——まとめ——
このケースではbinlog_formatはMIXEDフォーマットからROWフォーマットに変換され、一般的な遅延原因と解決策があります.筆者のレベルが限られているため、編纂時間も急いでいるため、文の中には間違いや不正確なところが避けられない.筆者の文章が好きで、右上に注目して、ありがとうございます!
背景
Part 1:一番前に書く
MySQLの遅延警報は皆さんもよく知られていると思いますが、MySQLがライブラリからの遅延を引き起こす原因はたくさんあります.ハードウェアから言えば、NIC、ディスク、メモリがボトルネックに達している可能性があります.データベースの面から言えば、SQLの効率が低下しているか、大量の書き込みが原因かもしれません.本文のケースはbinlogフォーマットによる遅延問題を分析し、本文を見てから、このような警告に出会ったとき、あなたが瞬時に問題の所在に位置づけることができると信じています.
Part 2:重点パラメータ分析
binlog_format Property
Value
Command-Line Format
--binlog-format=format System Variable
binlog_format Scope
Global, Session
Dynamic
Yes
Type (>= 5.5.31-ndb-7.2.13)
enumeration
Type (>= 5.5.15-ndb-7.2.1, <= 5.5.30-ndb-7.2.12)
enumeration
Type
enumeration
Default (>= 5.5.31-ndb-7.2.13)
MIXED Default (>= 5.5.15-ndb-7.2.1, <= 5.5.30-ndb-7.2.12)
STATEMENT Default
STATEMENT Valid Values (>= 5.5.31-ndb-7.2.13)
ROW STATEMENT MIXED Valid Values (>= 5.5.15-ndb-7.2.1, <= 5.5.30-ndb-7.2.12)
ROW STATEMENT MIXED Valid Values
ROW STATEMENT MIXED 周知のようにbinlog_formatはbinlogフォーマットを設定するパラメータで、STATEMENT、MIXED、ROWの3つのフォーマットに構成でき、動的に調整できます.3つのフォーマットにはそれぞれ欠けている.私たちのオンライン生産ライブラリはMIXED形式で統一的に構成されています.MIXED形式は、STATEMENT形式とROW形式でシーンによって異なる形式で切り替えられます.
mysql> show global variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.08 sec)Part 3:知識備蓄
MIXED形式では、次のような場合にbinlogが自動的にROW形式のレコードに変換されます
1.NDBエンジン
2.SQL文にはUUID()関数が含まれています.
3.自己成長フィールドが更新されました.
4.insert delayed文が含まれています.
5.ユーザ定義関数(UDF)が使用されている.
6.テンポラリ・テーブルが使用されています.
7.?もう1つはmixedフォーマットをROWに変換する場合であり,本稿では再現する.
実戦
Part 1:モニタリング
午前2時にはライブラリからの遅延が急増したが,ライブラリからの機器負荷やネットワークカードはボトルネックに達していないことが分かった.
Part 2:遅延原因分析
2時06分からbinlogのリフレッシュが非常に速く、ほぼ数十秒で1.1 GBのbinlogファイルがいっぱい書けることがわかります.これにより、書き込み量が大きすぎるため、基本的に特定できる.
書き込み量が多すぎる場合は、次の2つがあります.
ptツールpt-query-digestまたはコマンドラインを使用して、binlogがどのような操作をしたかを分析できます.pt-query-digestを使用するとmysqlbinlogコマンドと組み合わせてログを分析できます.
Part3:rootcase
delete from tablename where xxxx limit 100;
この構文はMIXED形式のbinlogをROW形式のレコードに変換しますが、筆者のケースのこのテーブルにはTEXTの大きなフィールドが含まれており、deleteのたびにTEXTの大きなフィールド全体をbinlogに持ち込み、binlogが急増し、ライブラリからメインライブラリに追いつかずに遅延が発生する場合があります.
Part 4:解決策
根本的な原因が見つかった後、解決して手応えがあって、関連する開発を見つけて、delete from table where xxx limitのこのような使い方を取り除いて、rowフォーマットの記録を避けることができます.
Warning:警告実はdelete/update limit、insert.....select limitという使い方は危険で、問題が発生しやすい.本当にこの方法を使うには、order by文を組み合わせてlimitの有効性を保証する必要があります.
このような文に遭遇した場合:
STATEMENTモードを使用すると、文ベースのコピーに対して安全ではないことを示す警告が表示されます.
STATEMENTモードを使用すると、ORDER BY句が1つあっても(したがって確定的である)、LIMITを含むDML文に警告が発行されます.これは既知の問題です.(BUG＃42851)
MIXEDモードを使用する場合、文はrowのモードコピーを使用します.
Part 5:公式文書
When running in MIXED logging format, the server automatically switches from statement-based to row-based logging under the following conditions:
When a DML statement updates an NDBCLUSTER table.
When a function contains UUID().
When one or more tables with AUTO_INCREMENT columns are updated and a trigger or stored function is invoked. Like all other unsafe statements, this generates a warning if binlog_format = STATEMENT.
When any INSERT DELAYED is executed.
When a call to a UDF is involved.
If a statement is logged by row and the session that executed the statement has any temporary tables, logging by row is used for all subsequent statements (except for those accessing temporary tables) until all temporary tables in use by that session are dropped.
This is true whether or not any temporary tables are actually logged.
Temporary tables cannot be logged using row-based format; thus, once row-based logging is used, all subsequent statements using that table are unsafe. The server approximates this condition by treating all statements executed during the session as unsafe until the session no longer holds any temporary tables.
When FOUND_ROWS() or ROW_COUNT() is used. (Bug #12092, Bug #30244)
When USER(), CURRENT_USER(), or CURRENT_USER is used. (Bug #28086)
When a statement refers to one or more system variables. (Bug #31168)公式文書では、MIXEDフォーマットがROWフォーマットに変換されるのはいつなのか、limit文がMIXEDフォーマットをROWに変換するのは言及されていない.国内の多くの書籍やブログでも言及されていないが、このケースを記録し、この問題と将来この問題に遭遇する可能性がある読者に対して処理時間を節約し、できるだけ早く根源に位置づけることを望んでいる.
公式文書は、MIXED形式がlimit構文を使用するときにROW形式に変換されて他の章に記録されていることについて、以下のように説明しています.
Statement-based replication of
LIMIT clauses in DELETE , UPDATE , and INSERT ... SELECT statements is unsafe since the order of the rows affected is not defined. (Such statements can be replicated correctly with statement-based replication only if they also contain an ORDER BY clause.) When such a statement is encountered: STATEMENT mode, a warning that the statement is not safe for statement-based replication is now issued.When using STATEMENT mode, warnings are issued for DML statements containing LIMIT even when they also have an ORDER BY clause (and so are made deterministic). This is a known issue. (Bug #42851) MIXED mode, the statement is now automatically replicated using row-based mode. ——まとめ——
このケースではbinlog_formatはMIXEDフォーマットからROWフォーマットに変換され、一般的な遅延原因と解決策があります.筆者のレベルが限られているため、編纂時間も急いでいるため、文の中には間違いや不正確なところが避けられない.筆者の文章が好きで、右上に注目して、ありがとうございます!