Friday, June 14, 2013

max_binlog_cache_size

As you evaluate your database performance and stability it is very likely that you will start to review your variables.

At a glance the typical first reaction to the variables below is.. WAIT something is wrong my box does not have that much RAM or even disk space to meet that MAX limits listed below....

MariaDB [(none)]> select @@max_write_lock_count, @@max_binlog_cache_size, @@max_seeks_for_key, @@myisam_max_sort_file_size\G
*************************** 1. row ***************************
     @@max_write_lock_count: 4294967295                -- 4GB
    @@max_binlog_cache_size: 1844674407370954752      --1.6EB
        @@max_seeks_for_key: 429496729                 -- 4GB
@@myisam_max_sort_file_size: 9223372036853727232        --8EB


You are not alone in concerns with these variables as a few bugs have been listed about these variables over the years. Below are just a few of some legacy ones.


"MySQL currently cannot work with binary log positions greater than 4GB."
Keep in mind that these are just the DEFAULT and MAX settings. You can adjust them to make you feel more comfortable.

MariaDB [(none)]> SET GLOBAL max_binlog_cache_size = 4294967296;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT @@max_binlog_cache_size;
+-------------------------+
| @@max_binlog_cache_size |
+-------------------------+
|              4294967296 |  -- 4GB
+-------------------------+
1 row in set (0.00 sec)


Why you would want to... That is an entirely different topic. This is just the upper limit allowed and transactions get split into 4GB anyway. "The maximum recommended value is 4GB", so you can update it if you so choose too.

Read more about your options with this in the MySQL Documentation:
http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_max_binlog_cache_size