MySQL Bugs: #57275: binlog_cache_size affects trx- and stmt-cache and gets twice the expected memory

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 Tavares Correia 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 Tavares Correia Junior	CPU Architecture:	Any
Tags:	binlog cache, transaction cache, WL2687

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

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

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.

Pushed to mysql-5.5-bugteam and mysql-trunk-bugfixing.

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.

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)

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)

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.