【MySQL】binlogキャッシュの問題とパフォーマンス

以前、ライブラリを準備していない場合、more than'max_に遭遇したことがあります.binlog_cache_size'bytes of storageのエラーは、今日、レプリケーションの準備中にまたこの問題に遭遇しました.

Last_SQL_Errno: 1197
Last_SQL_Error: Worker 14 failed executing transaction '' at master log mysql-bin.050101, end_log_pos 2669492980; Could not execute Delete_rows_v1 event on table time_dispatcher_0029.ttd_task_1889; Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again, Error_code: 1197; handler error HA_ERR_RBR_LOGGING_FAILED; the event's master log mysql-bin.050101, end_log_pos 2669492980

slave

root@(none) 10:11:38>show variables like '%binlog_%size%';
+----------------------------+----------------------+
| Variable_name              | Value                |
+----------------------------+----------------------+
| binlog_cache_size          | 32768                |
| binlog_stmt_cache_size     | 32768                |
| max_binlog_cache_size      | 2147483648           |
| max_binlog_size            | 524288000            |
| max_binlog_stmt_cache_size | 18446744073709547520 |
+----------------------------+----------------------+
root@(none) 09:51:00>show status like 'binlog_%';   
+----------------------------+-----------+
| Variable_name              | Value     |
+----------------------------+-----------+
| Binlog_cache_disk_use      | 86332     |
| Binlog_cache_use           | 320562298 |
| Binlog_stmt_cache_disk_use | 0         |
| Binlog_stmt_cache_use      | 145       |
+----------------------------+-----------+

master

root@(none) 10:07:17>show variables like '%binlog_cache_size%';
+-----------------------+-------------+
| Variable_name         | Value       |
+-----------------------+-------------+
| binlog_cache_size     | 32768       |
| max_binlog_cache_size | 10737418240 |
+-----------------------+-------------+

プライマリ・スタンバイ設定が一致しないため、プライマリ・ライブラリのbinlogの書き込みに成功し、スタンバイ・ライブラリはrelogを復元する際に同様にbinlogを記録しますが、プライマリ・スタンバイcacheのサイズ設定が異なるため、上記の問題が発生します.
マスターパラメータの一貫性が重要!!!
しゅうふく

root@(none) 10:08:49>stop slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
root@(none) 10:08:32>set global max_binlog_cache_size=10737418240;         
Query OK, 0 rows affected (0.00 sec)
root@(none) 10:08:49>start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)

max_binlog_cache_size
トランザクションがこのバイト数を超えるメモリを必要とする場合、サーバは、次のエラーERROR 1197 (HY000): Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage;max_binlog_cache_sizeを生成します.最小値は4096で、最大可能値は16 EB(アイバイト)です.推奨される最大値は4 GBです.現在のMySQLでは、バイナリ・ログの場所が4 GBより大きい場合は有効ではありません.
Note:
Prior to MySQL 5.6.7, 64-bit Windows platforms truncated the stored value for this variable to 4G, even when it was set to a greater value (Bug #13961678).
MySQL 5.6では、セッションはmax_binlog_cache_sizeの感知はbinlog_に依存するcache_sizeシステム変数;すなわち、値を変更すると、後続の新しいセッションにのみ影響します.
max_binlog_size
バイナリ・ログを書き込み、現在のログ・ファイルのサイズがこの変数の値を超えた場合、サーバはバイナリ・ログを回転します(現在のファイルを閉じ、次を開きます).の最小値は4096バイトです.最大値とデフォルト値は1 GBです.
トランザクションはブロック単位でバイナリ・ログに書き込まれるため、いくつかのバイナリ・ログに分割されません.大きな取引があればmaxよりもbinlog_sizeの大きいバイナリログファイル.
max_の場合relay_log_sizeは0、max_binlog_sizeの値は中継ログに適用されます.
max_binlog_stmt_cache_size
トランザクション内の非トランザクション文がこの数よりも多くのメモリを必要とする場合、サーバはエラーを発生します.最小値は4096で、32ビットプラットフォームの最大値とデフォルト値は4 GBです.64ビットプラットフォームの最大値とデフォルト値は416 EB(アイバイト)です.
Note
Prior to MySQL 5.6.7, 64-bit Windows platforms truncated the stored value for this variable to 4G, even when it was set to a greater value (Bug #13961678).
max_binlog_cache_sizeトランザクション文、max_binlog_stmt_cache_size非トランザクション文に対してBinlog_を発見するとcache_disk_useまたはBinlog_stmt_cache_disk_useが大きい場合はcacheのサイズを大きくすることを考慮する必要があります
トランザクション文&非トランザクション文
トランザクション文の実行中に、サーバは追加の処理を行い、サーバの実行時に複数のトランザクションが並列に実行されます.これらのトランザクションを記録するには、トランザクションキャッシュの概念を導入する必要があります.トランザクションがコミットされたときにバイナリ・ログにリフレッシュします.非トランザクション文の処理.次の3つのルールに従います.

非トランザクション文がトランザクションとしてマークされている場合、トランザクション・バッファに書き込まれます.

トランザクション文としてマークされておらず、トランザクションキャッシュにマークされていない場合は、バイナリ・ログに直接書き込まれます.

トランザクション文としてマークされていないが、トランザクションキャッシュに存在する場合は、トランザクションバッファに書き込まれます.

注意1つのトランザクションに非トランザクション文がある場合は、ルール2を使用して、影響を及ぼす非トランザクションテーブル文をバイナリ・ログに直接書き込むことが優先されます.
すべてが正常であれば、トランザクションでトランザクション文(DML)が実行されるので、これらの文によって形成されたeventはキャッシュに記録され、commitトランザクションが完了すると、このキャッシュされたデータはすぐにディスク上のbinlogファイルにブラシされ、slaveリクエストによってslaveに送信され、slaveで繰り返し実行されます.
ちなみにmysqlのbinlogのトランザクションは互いに独立して順番に並べられています.
今から意外なことが起こったと仮定します.
通常の意外な1:トランザクション実行中にrollbackが発生しました.
1つのトランザクションで、すべての実行文がinnodbテーブル上で実行される場合(tokudbなどの複数のトランザクションエンジンでテストされていない場合)、rollbackを直接実行すると、mysqlはキャッシュを直接クリアし、binlogにeventレコードを書き込むことはありません.
通常のインシデント2:トランザクション実行で非トランザクションテーブル(myisam)が使用され、rollbackになります.
トランザクションには、トランザクション・テーブルと非トランザクション・テーブルがあり、正常に実行されている場合は問題ありません.非トランザクション・テーブルでの変更が実行され、レプリケーションがstatementに設定されている場合、binlogはトランザクションと非トランザクション文を含むすべての実行イベントをログに記録し、最後尾にrollbackを追加します.レプリケーション・フォーマットがrowに設定されている場合、トランザクション・テーブルのデータではなく、すべてのトランザクション・テーブルに関連するデータがbinlogに書き込まれたとマークされます.
通常の予期せぬ3:トランザクションの実行順序は次のとおりです:トランザクション文1-」非トランザクション文2-「save point-」トランザクション文3-「rollback to savepoint-」トランザクション文4-」コミット
上記の場合、statementの場合、savepointとrollbackを含むすべての実行文がbinlogに順番に記録されます.rowの場合、非トランザクション文とトランザクション文はbinlogにそれぞれ記録され、非トランザクション実行eventはすべて記録され、トランザクション実行eventはsavepointとrollbackとともに記録されます.
通常の予期せぬ4:トランザクションの実行順序は次のとおりです:トランザクション文1-"savepoint-"非トランザクション文2-"トランザクション文3-"rollback to savepoint-"トランザクション文4-"commit
同上.
例外1:masterは正常にコミットされましたが、slaveで実行に失敗しました.
master実行が完了し、コミット後、slaveはrelayを受信して実行し、実行中に予期せぬ理由で失敗しました.このときstatement形式ではmysqlはトランザクションbeginに戻って再実行され、再び失敗するとエラーが報告されます.rowモードでは、非トランザクションテーブルで実行されるトランザクションが含まれている場合、非トランザクションテーブルのeventは再実行されません.
リファレンス
Binary Log Options and Variables
max_binlog_stmt_cache_size
mysqlレプリケーションによるトランザクションの処理
mysqlバイナリ・ログ処理トランザクションと非トランザクション文の違い