Bug #57275 binlog_cache_size affects trx- and stmt-cache and gets twice the expected memory
Submitted: 6 Oct 2010 8:53 Modified: 5 Jan 2011 5:50
Reporter: Alfranio Junior Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.5, next-mr OS:Any
Assigned to: Alfranio Junior CPU Architecture:Any
Tags: binlog cache, transaction cache, WL2687

[6 Oct 2010 8:53] Alfranio Junior
Description:
After the WL#2687, the binlog_cache_size and max_binlog_cache_size affect both the stmt-cache and the trx-cache. This means that the resource used is twice the amount expected/defined by the user.

The binlog_cache_use is incremented when the stmt-cache or the trx-cache is used
and binlog_cache_disk_use is incremented when the disk space from the stmt-cache or the trx-cache is used. This behavior does not allow to distinguish which cache may be harming performance due to the extra disk accesses and needs to have its in-memory cache increased.

How to repeat:
For instance, check the following code:

int THD::binlog_setup_trx_data()
{
  DBUG_ENTER("THD::binlog_setup_trx_data");
  binlog_cache_mngr *cache_mngr=
    (binlog_cache_mngr*) thd_get_ha_data(this, binlog_hton);

  if (cache_mngr)
    DBUG_RETURN(0);                             // Already set up

  cache_mngr= (binlog_cache_mngr*) my_malloc(sizeof(binlog_cache_mngr), MYF(MY_ZEROFILL));
  if (!cache_mngr ||
      open_cached_file(&cache_mngr->stmt_cache.cache_log, mysql_tmpdir,
                       LOG_PREFIX, binlog_cache_size, MYF(MY_WME)) ||
      open_cached_file(&cache_mngr->trx_cache.cache_log, mysql_tmpdir,
                       LOG_PREFIX, binlog_cache_size, MYF(MY_WME)))
  {
    my_free(cache_mngr);
    DBUG_RETURN(1);                      // Didn't manage to set it up
  }
...

Suggested fix:
Introduce two new options and status variables related to the stmt-cache:

Options:

. binlog_stmt_cache_size

. max_binlog_stmt_cache_size

Status Variables:

. binlog_stmt_cache_use

. binlog_stmt_cache_disk_use
[12 Oct 2010 17:17] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/120590
[5 Nov 2010 17:49] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/122998

3122 Alfranio Correia	2010-11-05
      BUG#57275 binlog_cache_size affects trx- and stmt-cache and gets twice the expected memory
            
      After the WL#2687, the binlog_cache_size and max_binlog_cache_size affect both the
      stmt-cache and the trx-cache. This means that the resource used is twice the amount
      expected/defined by the user.
            
      The binlog_cache_use is incremented when the stmt-cache or the trx-cache is used
      and binlog_cache_disk_use is incremented when the disk space from the stmt-cache or the
      trx-cache is used. This behavior does not allow to distinguish which cache may be harming
      performance due to the extra disk accesses and needs to have its in-memory cache
      increased.
            
      To fix the problem, we introduced two new options and status variables related to the
      stmt-cache:
            
        Options:
            
          . binlog_stmt_cache_size
          . max_binlog_stmt_cache_size
            
          Status Variables:
            
          . binlog_stmt_cache_use
          . binlog_stmt_cache_disk_use
      
      So there are
      
        . binlog_cache_size that defines the size of the transactional cache for
        updates to transactional engines for the binary log.
      
        . binlog_stmt_cache_size that defines the size of the statement cache for
        updates to non-transactional engines for the binary log.
      
        . max_binlog_cache_size that sets the total size of the transactional
        cache.
      
        . max_binlog_stmt_cache_size that sets the total size of the statement
        cache.
      
        . binlog_cache_use that identifies the number of transactions that used the
        temporary transactional binary log cache.
      
        . binlog_cache_disk_use that identifies the number of transactions that used
        the temporary transactional binary log cache but that exceeded the value of
        binlog_cache_size.
      
        . binlog_stmt_cache_use that identifies the number of statements that used the
        temporary non-transactional binary log cache.
      
        . binlog_stmt_cache_disk_use that identifies the number of statements that used
        the temporary non-transactional binary log cache but that exceeded the value of
        binlog_stmt_cache_size.
     @ include/my_sys.h
        Updated message on disk_writes' usage.
     @ mysql-test/extra/binlog_tests/binlog_cache_stat.test
        Updated the test case and added code to check the new status variables
        binlog_stmt_cache_use and binlog_stmt_cache_disk_use.
     @ mysql-test/extra/rpl_tests/rpl_binlog_max_cache_size.test
        Updated the test case to use the new system variables max_binlog_stmt_cache_size and binlog_stmt_cache_size.
     @ mysql-test/r/mysqld--help-notwin.result
        Updated the result file.
     @ mysql-test/suite/binlog/r/binlog_mixed_cache_stat.result
        Updated the result file.
     @ mysql-test/suite/binlog/r/binlog_row_cache_stat.result
        Updated the result file.
     @ mysql-test/suite/binlog/r/binlog_stm_cache_stat.result
        Updated the result file.
     @ mysql-test/suite/rpl/r/rpl_mixed_binlog_max_cache_size.result
        Update the result file.
     @ mysql-test/suite/rpl/r/rpl_row_binlog_max_cache_size.result
        Update the result file.
     @ mysql-test/suite/rpl/r/rpl_stm_binlog_max_cache_size.result
        Updated the result file.
     @ mysql-test/suite/sys_vars/inc/binlog_stmt_cache_size_basic.inc
        Added a test case to check the binlog_stmt_cache_size.
     @ mysql-test/suite/sys_vars/r/binlog_stmt_cache_size_basic_32.result
        Updated the result file.
     @ mysql-test/suite/sys_vars/r/binlog_stmt_cache_size_basic_64.result
        Updated the result file.
     @ mysql-test/suite/sys_vars/r/max_binlog_stmt_cache_size_basic.result
        Updated the result file.
     @ mysql-test/suite/sys_vars/t/binlog_stmt_cache_size_basic_32.test
        Added a test case to check the binlog_stmt_cache_size.
     @ mysql-test/suite/sys_vars/t/binlog_stmt_cache_size_basic_64.test
        Added a test case to check the binlog_stmt_cache_size.
     @ mysql-test/suite/sys_vars/t/max_binlog_cache_size_func-master.opt
        Removed because there is no test case max_binlog_cache_size_func.
     @ mysql-test/suite/sys_vars/t/max_binlog_stmt_cache_size_basic.test
        Added a test case to check the system variable max_binlog_stmt_cache_size.
     @ sql/log.cc
        There two main changes in here:
        
          . Changed the set_write_error() as an error message is set according
          to the type of the cache.
        
          . Created the function set_binlog_cache_info where references to the
          appropriate status and system variables are set and the server can
          smoothly compute statistics and set the maximum size for each cache.
     @ sql/log.h
        Changed the signature of the function in order to identify the error message
        to be printed out as there is a different error code for each type of cache.
     @ sql/mysqld.cc
        Added new status variables binlog_stmt_cache_use and binlog_stmt_cache_disk_use.
     @ sql/mysqld.h
        Added new system variables max_binlog_stmt_cache_size and binlog_stmt_cache_size.
     @ sql/share/errmsg-utf8.txt
        Added new error message related to the statement cache.
     @ sql/sys_vars.cc
        Added new system variables max_binlog_stmt_cache_size and binlog_stmt_cache_size.
[5 Dec 2010 23:16] Alfranio Junior
Pushed to mysql-5.5-bugteam and mysql-trunk-bugfixing.
[6 Dec 2010 11:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/126125

3168 Alfranio Correia	2010-12-06
      Post-push fix for BUG#57275.
[17 Dec 2010 12:54] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:georgi.kodinov@oracle.com-20101217124733-p1ivu6higouawv8l) (version source revid:alfranio.correia@oracle.com-20101206115536-bpfezo8t2y6xmy1x) (merge vers: 5.5.8) (pib:24)
[17 Dec 2010 12:57] Bugs System
Pushed into mysql-trunk 5.6.1 (revid:georgi.kodinov@oracle.com-20101217125013-y8pb3az32rtbplc9) (version source revid:anitha.gopi@sun.com-20101210041312-50t9adyhwwybsm6x) (merge vers: 5.6.1) (pib:24)
[5 Jan 2011 5:50] Jon Stephens
Documented fix in the 5.5.9 and 5.6.1 changelogs as follows:

        Due to changes made in MySQL 5.5.3, settings made in the
        binlog_cache_size and max_binlog_cache_size server system
        variables affected both the binary log statement cache (also
        introduced in that version) and the binary log transactional
        cache (formerly known simply as the binary log cache). This
        meant that the resources used as a result of setting either or
        both of these variables were double the amount expected. To
        rectify this problem, these variables now affect only the
        transactional cache. The fix for this issue also introduces two
        new system variables binlog_stmt_cache_size and
        max_binlog_stmt_cache_size, which affect only the binary log 
        statement cache.

        In addition, the Binlog_cache_use status variable was
        incremented whenever either cache was used, and
        Binlog_cache_disk_use was incremented whenever the disk space
        from either cache was used, which caused problems with
        performance tuning of the statement and transactional caches,
        because it was not possible to determine which of these was
        being exceeeded when attempting to troubleshoot excessive disk
        seeks and related problems. This issue is solved by changing the
        behavior of these two status variables such that they are
        incremented only in response to usage of the binary log
        transactional cache, as well as by introducing two new status
        variables Binlog_stmt_cache_use and Binlog_stmt_cache_disk_use,
        which are incremented only by usage of the binary log statement
        cache.

        For more information, see "System variables used with the binary 
        log", and "Server Status Variables".

Added info to the indicated sections about the new system and status variables, and updated the descriptions of the existing ones.

For complete documentation changes, see commit here: 

http://lists.mysql.com/commits/127931

Closed.