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.

1 files changed, 14 insertions, 6 deletions

diff --git a/mysql-test/r/mysqld--help-notwin.result b/mysql-test/r/mysqld--help-notwin.result
index 63e17e1c188..2d2a3cb919a 100644
--- a/mysql-test/r/mysqld--help-notwin.result
+++ b/mysql-test/r/mysqld--help-notwin.result
@@ -31,10 +31,10 @@ The following options may be given as the first argument:
  file (Solves most 'table full' errors)
  --bind-address=name IP address to bind to.
  --binlog-cache-size=# 
- The size of the cache to hold the SQL statements for the
- binary log during a transaction. If you often use big,
- multi-statement transactions you can increase this to get
- more performance
+ The size of the transactional cache for updates to
+ transactional engines for the binary log. If you often
+ use transactions containing many statements, you can
+ increase this to get more performance
  --binlog-direct-non-transactional-updates 
  Causes updates to non-transactional engines using
  statement format to be written directly to binary log.
@@ -65,6 +65,11 @@ The following options may be given as the first argument:
  The maximum size of a row-based binary log event in
  bytes. Rows will be grouped into events smaller than this
  size if possible. The value has to be a multiple of 256.
+ --binlog-stmt-cache-size=# 
+ The size of the statement cache for updates to
+ non-transactional engines for the binary log. If you
+ often use statements updating a great number of rows, you
+ can increase this to get more performance
  --bootstrap         Used by mysql installation scripts.
  --bulk-insert-buffer-size=# 
  Size of tree cache used in bulk insert optimisation. Note
@@ -277,14 +282,15 @@ The following options may be given as the first argument:
  --max-allowed-packet=# 
  Max packet length to send to or receive from the server
  --max-binlog-cache-size=# 
- Can be used to restrict the total size used to cache a
- multi-transaction query
+ Sets the total size of the transactional cache
  --max-binlog-dump-events=# 
  Option used by mysql-test for debugging and testing of
  replication.
  --max-binlog-size=# Binary log will be rotated automatically when the size
  exceeds this value. Will also apply to relay logs if
  max_relay_log_size is 0
+ --max-binlog-stmt-cache-size=# 
+ Sets the total size of the statement cache
  --max-connect-errors=# 
  If there is more than this number of interrupted
  connections from a host this host will be blocked from
@@ -733,6 +739,7 @@ binlog-cache-size 32768
 binlog-direct-non-transactional-updates FALSE
 binlog-format STATEMENT
 binlog-row-event-max-size 1024
+binlog-stmt-cache-size 32768
 bulk-insert-buffer-size 8388608
 character-set-client-handshake TRUE
 character-set-filesystem binary
@@ -812,6 +819,7 @@ max-allowed-packet 1048576
 max-binlog-cache-size 18446744073709547520
 max-binlog-dump-events 0
 max-binlog-size 1073741824
+max-binlog-stmt-cache-size 18446744073709547520
 max-connect-errors 10
 max-connections 151
 max-delayed-threads 20