【ERROR 1197】max_binlog_cache_sizeが足らないって怒られた時の対処方法

注意

MariaDB5.5で発生・対応した内容です。
実際にMySQL5.5で試してはいないですが、参照したリファレンスはMySQL5.5で、コマンドもMySQL5.5と変わらないものを使用したので、MySQL5.5でも同様の動きをすると思われます。

現象

1800万行程のCSVをloadしたら下記のエラーが出た。

ERROR 1197 (HY000) at line 5: Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again

load処理のバイナリログのサイズが max_binlog_cache_size を超えてしまうと出るエラーとのこと。
この変数のデフォルト設定値は4GBであり、このサイズを超えるとトランザクションが効かなくなってしまうためエラーになってしまう。

show variables like 'max_binlog_cache_size'; 

/*
+-----------------------+------------+
| Variable_name         | Value      |
+-----------------------+------------+
| max_binlog_cache_size | 4294963200 |
+-----------------------+------------+
1 row in set (0.01 sec)
*/

対応

リファレンスによると

The maximum possible value is 16EB (exabytes). The maximum recommended value is 4GB; this is due to the fact that MySQL currently cannot work with binary log positions greater than 4GB.

一応16EB（エクサバイト）まで設定はできるようなのだが、結局4GBを超えるとトランザクションが効かなくなるっぽいので、推奨は4GBらしい。

変更自体は下記SQLでできるが、あまり気軽に変更するような値ではないだろう。

SET global max_binlog_cache_size = 25769779200;

設定してロードした場合、バイナリログのファイルサイズが跳ね上がる。
（ちなみにMySQLのデフォルトは1GBでローテートされる設定になっている）

ls -la /var/lib/mysql

…
-rw-rw----  1 mysql mysql  1073741897  7月 29 18:59 mysql-bin.000117
-rw-rw----  1 mysql mysql  1073742056  7月 30 03:59 mysql-bin.000118
-rw-rw----  1 mysql mysql  1073742399  7月 30 12:31 mysql-bin.000119
-rw-rw----  1 mysql mysql 13954453678  7月 30 16:52 mysql-bin.000120

結果

load自体はこれで問題無く完了した。
また、slaveにも問題無くレプリケーションされていた。
MySQL5.1ではmax_binlog_cache_sizeを超えるトランザクションを実行するとslaveがクラッシュするバグがあったらしい。

参考: http://d.hatena.ne.jp/sh2/20101215

結論

素直にファイル分割してロードするほうが無難かな…