From 3f5a9c7ea03e5c6a62f41bb90f6b1655c59d3d75 Mon Sep 17 00:00:00 2001 From: Alfranio Correia Date: Fri, 5 Nov 2010 17:42:37 +0000 Subject: 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. --- sql/mysqld.cc | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'sql/mysqld.cc') diff --git a/sql/mysqld.cc b/sql/mysqld.cc index df4f0f95b8c..86013dace31 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -460,6 +460,8 @@ ulonglong slave_type_conversions_options; ulong thread_cache_size=0; ulong binlog_cache_size=0; ulonglong max_binlog_cache_size=0; +ulong binlog_stmt_cache_size=0; +ulonglong max_binlog_stmt_cache_size=0; ulong query_cache_size=0; ulong refresh_version; /* Increments on each reload */ query_id_t global_query_id; @@ -471,6 +473,7 @@ ulong delayed_insert_threads, delayed_insert_writes, delayed_rows_in_use; ulong delayed_insert_errors,flush_time; ulong specialflag=0; ulong binlog_cache_use= 0, binlog_cache_disk_use= 0; +ulong binlog_stmt_cache_use= 0, binlog_stmt_cache_disk_use= 0; ulong max_connections, max_connect_errors; /** Limit of the total number of prepared statements in the server. @@ -6406,6 +6409,8 @@ SHOW_VAR status_vars[]= { {"Aborted_connects", (char*) &aborted_connects, SHOW_LONG}, {"Binlog_cache_disk_use", (char*) &binlog_cache_disk_use, SHOW_LONG}, {"Binlog_cache_use", (char*) &binlog_cache_use, SHOW_LONG}, + {"Binlog_stmt_cache_disk_use",(char*) &binlog_stmt_cache_disk_use, SHOW_LONG}, + {"Binlog_stmt_cache_use", (char*) &binlog_stmt_cache_use, SHOW_LONG}, {"Bytes_received", (char*) offsetof(STATUS_VAR, bytes_received), SHOW_LONGLONG_STATUS}, {"Bytes_sent", (char*) offsetof(STATUS_VAR, bytes_sent), SHOW_LONGLONG_STATUS}, {"Com", (char*) com_status_vars, SHOW_ARRAY}, -- cgit v1.2.1