この記事では、「sync_binlog」、「binlog_cache_size」、「max_binlog_cache_size」に関連する問題など、mysql でバイナリログを記述するプロセスに関する関連知識を提供します。皆様のお役に立てれば幸いです。

MySql でバイナリログを作成するプロセスを完全にマスターする

バイナリログの書き込みプロセス

まず、公式ドキュメントの sync_binlog 設定の説明を見てみましょう。

sync_binlog

# #コマンドライン形式--sync-binlog=#システム変数sync_binlog影響範囲グローバル#動的#YesSET_VAR プロンプトが適用されるNoInteger##デフォルト値1最小値0最大値2^32=4294967295






	##Type



	MySQL サーバーがバイナリログをディスクに同期する頻度を制御します。

sync_binlog=0: MySQL サーバーによるバイナリログのディスクへの同期を無効にします。代わりに、MySQL サーバーは、他のファイルと同様に、オペレーティングシステムに依存してバイナリログを時々ディスクにフラッシュします。この設定では最高のパフォーマンスが得られますが、停電やオペレーティングシステムのクラッシュが発生した場合、サーバーはまだフラッシュされていないトランザクションをコミットする可能性があります。

sync_binlog=1: トランザクションをコミットする前に、バイナリログとディスクの同期を有効にします。これは最も安全な設定ですが、ディスクへの書き込みが増加するため、パフォーマンスに悪影響を及ぼす可能性があります。停電やオペレーティングシステムのクラッシュが発生した場合、バイナリログで失われたトランザクションは準備済みの状態にあるだけです。これにより、定期的な自動回復によってトランザクションをロールバックできるため、バイナリログからトランザクションが失われないことが保証されます。
sync_binlog=N、0 または 1 以外の値: N 個のバイナリログ送信グループが収集された後、バイナリログはディスクに同期されます。停電やオペレーティングシステムのクラッシュが発生した場合、サーバーはまだバイナリログにフラッシュされていないトランザクションをコミットしている可能性があります。この設定は、ディスクへの書き込みが増加するため、パフォーマンスに悪影響を与える可能性があります。値を大きくするとパフォーマンスは向上しますが、データ損失のリスクが増加します。
InnoDB
トランザクションで使用されるレプリケーションセットアップで可能な限り最大の耐久性と一貫性を得るには、次の設定を使用します。

sync_binlog =1.innodb_flush_log_at_trx_commit=1.

警告

#多くのオペレーティングシステムと一部のディスクハードウェアの不正行為は、ディスクへのフラッシュ操作に使用されます。リフレッシュがまだ発生していない場合でも、リフレッシュが発生したことを mysqld に通知する場合があります。この場合、推奨設定であってもトランザクションの耐久性は保証されず、最悪の場合、停電により

InnoDB
データが破損する可能性があります。 SCSI ディスクコントローラまたはディスク自体でバッテリバックアップ式ディスクキャッシュを使用すると、ファイルの更新が高速化され、操作がより安全になります。ハードウェアキャッシュでのディスク書き込みのキャッシュを無効にしてみることもできます。
概要
sync_binlog 設定タイプは符号なし整数です。

一般的には 0 に設定されません。0 はシステム動作と不規則な fsync に依存します。停電やシステムクラッシュが発生した場合はより危険です。トランザクションは送信されますが、バイナリログが失われます。

これを 1 に設定し、可能な限り最大限の耐久性と一貫性を確保し、その後のマスター/スレーブのレプリケーションとリカバリを確実にする方が安全です。ただし、これはパフォーマンスに悪影響を与えるため、ビジネスに必要な IOPS が高くない場合に設定できます。

1 より大きい値を設定する目的は、パフォーマンスを向上させることです。トランザクションをコミットする代わりに、fsync はバッチフラッシュと同等です。これは賢い方法ですが、停電やシステムクラッシュが発生した場合は、バイナリログは失われますが、さらに存在します。ディスク自体がバッテリーバックアップのディスクキャッシュを使用していればより安全です。したがって、ビジネスで要求される IOPS が比較的高い場合に設定できますが、一般的にはあまり大きく設定せず、[100, 1000] の範囲に設定できます。
また、sync_binlog=0 の記述から、実際にはトランザクションが送信された時点で、すぐには fsync は発生していないものの、実際にページに書き込まれていることも大まかに感じられます。ファイルシステムのキャッシュ。実際、mysql には、トランザクションの実行中にトランザクションで生成されたバイナリログをキャッシュするキャッシュもあります。
引き続き、トランザクション実行時のバイナリログのキャッシュ関連の構成を見てみましょう。
binlog_cache_size

--binlog-cache-size=#システム変数binlog_cache_size #スコープ#動的#はい#いいえ##整数##デフォルト値##32768##最小値4096最大値(64ビットプラットフォーム)2^64=18446744073709547520最大値 (32 ビットプラットフォーム)2^32=4294967295ブロックサイズ4096

バイナリログを保持するメモリバッファのサイズは、トランザクション中に変化します。値は 4096 の倍数である必要があります。

サーバーでバイナリログが有効になっている (log_bin システム変数が ON に設定されている) 場合、サーバーがトランザクションストレージエンジンをサポートしていれば、各クライアントにバイナリログキャッシュが割り当てられます。トランザクションのデータがメモリバッファ内のスペースを超える場合、超過したデータは一時ファイルに保存されます。サーバー上でバイナリログ暗号化がアクティブな場合、メモリバッファは暗号化されませんが、(MySQL 8.0.17 以降では) バイナリログキャッシュを保持するために使用される一時ファイルは暗号化されます。各トランザクションがコミットされた後、メモリバッファをクリアし、一時ファイル (使用されている場合) を切り捨てることによって、バイナリログキャッシュがリセットされます。

大規模なトランザクションを頻繁に使用する場合は、一時ファイルへの書き込みの必要性を減らすか排除することで、このキャッシュサイズを増やしてパフォーマンスを向上させることができます。 Binlog_cache_use (サービスステータス変数 - バイナリログキャッシュを使用するトランザクションの数) および Binlog_cache_disk_use (サービスステータス変数 - 一時バイナリログキャッシュを使用するが、binlog_cache_size 値を超え、トランザクションステートメントを保存するために一時ファイルを使用するトランザクションの数) ステータス変数この変数サイズを調整するために使用できます。「バイナリログ」を参照してください。

binlog_cache_sizeトランザクションキャッシュのサイズのみを設定します。ステートメントキャッシュのサイズは、binlog_stmt_cache_size システム変数によって制御されます。

概要

binlog_cache_size 設定タイプは符号なし整数です。
は、各トランザクション中にバイナリログをキャッシュするために使用されるサイズを示すために使用されます。デフォルトは 32k で、4096 の倍数である必要があります。この値を超えると、一時ファイルストレージが使用されます。
ビジネスでは大規模なトランザクションを使用しないようにしてください。トランザクションが大きすぎる場合は、それが合理的かどうかを検討する必要があります。通常、binlog_cache_size を変更する必要はなく、32k で十分です。
binlog_cache_size が足りない場合、一時ファイルが保存されますが、パフォーマンスが低下しますので、以下で紹介する max_binlog_cache_size=binlog_cache_size を設定して一時ファイルを使用しないようにすることもできます。

max_binlog_cache_size

	# #コマンド形式

グローバル
	SET_VARプロンプトが適用されます
タイプ

##コマンド形式--max-binlog-cache-size=システム変数max_binlog_cache_size範囲グローバル動的はいSET_VARプロンプト適用可能Noタイプ整数デフォルト値2^ 64=18446744073709547520 #最小値最大値ブロックサイズ

トランザクションがこのバイト数を超えるメモリを必要とする場合、サーバーは、「max_binlog_cache_size」バイトを超えるストレージエラーを必要とするマルチステートメントトランザクションを生成します。最小値は 4096 です。可能な最大値は 16EiB (exbibytes) です。推奨される最大値は 4GB です。これは、MySQL が現在 4GB を超えるバイナリログの場所を処理できないためです。値は 4096 の倍数である必要があります。

max_binlog_cache_sizeトランザクションキャッシュのサイズのみを設定します。ステートメントキャッシュの上限は、max_binlog_stmt_cache_size システム変数によって制御されます。

セッションの可視性 max_binlog_cache_size binlog_cache_size システム変数の可視性と一致します。つまり、値の変更は、値の変更後に開始された新しいセッションにのみ影響します。

概要

max_binlog_cache_size は安全な値であり、通常はサーバーによって割り当てられるメモリに応じて設定されます。

概要

上記の構成から、バイナリログの書き込みプロセスは大まかに次のように結論付けることができます。

トランザクションはランタイムに変更され、配置されます。各トランザクションのバイナリログキャッシュに保存されます。
トランザクション送信後は設定に従ってトランザクションが実行され、sync_binlog=1 の場合、fsync が実行されるたびにキャッシュが解放されます。 sync_binlog=0 の場合、オペレーティングシステムに依存してバイナリログを随時フラッシュし、システムファイルのページキャッシュに直接書き込まれます。 sync_binlog=N (N>1) の場合はバッチフラッシュに相当し、当然ながら各トランザクションが保持していたバイナリログキャッシュは解放されます。

したがって、一般的なプロセスは次のとおりです。

要約

これまでのところ、プロセスについては一般的に理解できています。 Mysql をバイナリに書き込むには、Through: 各トランザクションが保持するバイナリキャッシュ -> ファイルシステムのページキャッシュ -> ディスクが必要です。特定の実行戦略は、sync_binlog を通じて制御できます。

Use

sync_binlog: 最大限の耐久性と一貫性を得る必要がある場合は、1 に設定します。パフォーマンスの問題に関しては、ハードウェアやその他の方法を調整できます。バイナリログ、損失は許容されます。または、他の方法で制御を失い、現在のサーバーリソースに基づいて最適化したい場合は、[100,1000] の範囲に設定します。
binlog_cahe_size: 前述したように、実際のビジネスではトランザクション粒度の制御に注意が必要ですが、ほとんどの場合、デフォルトの 32k で十分です。

推奨学習: mysql ビデオチュートリアル