diff options
author | Dmitry Shulga <Dmitry.Shulga@oracle.com> | 2012-01-25 15:59:30 +0600 |
---|---|---|
committer | Dmitry Shulga <Dmitry.Shulga@oracle.com> | 2012-01-25 15:59:30 +0600 |
commit | d460f1689da6081ea06ee25e0c7553c74dd50c30 (patch) | |
tree | d08a6d9fc67a58677ef59ca1cb6202256483f6bd | |
parent | 7bec00e131689f3601b0b176bf331e88bf8b7e1c (diff) | |
download | mariadb-git-d460f1689da6081ea06ee25e0c7553c74dd50c30.tar.gz |
Fixed bug#11753187 (formerly known as bug 44585): SP_CACHE BEHAVES AS
MEMORY LEAK.
Background:
- There are caches for stored functions and stored procedures (SP-cache);
- There is no similar cache for events;
- Triggers are cached together with TABLE objects;
- Those SP-caches are per-session (i.e. specific to each session);
- A stored routine is represented by a sp_head-instance internally;
- SP-cache basically contains sp_head-objects of stored routines, which
have been executed in a session;
- sp_head-object is added into the SP-cache before the corresponding
stored routine is executed;
- SP-cache is flushed in the end of the session.
The problem was that SP-cache might grow without any limit. Although this
was not a pure memory leak (the SP-cache is flushed when session is closed),
this is still a problem, because the user might take much memory by
executing many stored routines.
The patch fixes this problem in the least-intrusive way. A soft limit
(similar to the size of table definition cache) is introduced. To represent
such limit the new runtime configuration parameter 'stored_program_cache'
is introduced. The value of this parameter is stored in the new global
variable stored_program_cache_size that used to control the size of SP-cache
to overflow.
The parameter 'stored_program_cache' limits number of cached routines for
each thread. It has the following min/default/max values given from support:
min = 256, default = 256, max = 512 * 1024.
Also it should be noted that this parameter limits the size of
each cache (for stored procedures and for stored functions) separately.
The SP-cache size is checked after top-level statement is parsed.
If SP-cache size exceeds the limit specified by parameter
'stored_program_cache' then SP-cache is flushed and memory allocated for
cache objects is freed. Such approach allows to flush cache safely
when there are dependencies among stored routines.
-rw-r--r-- | mysql-test/r/mysqld--help-notwin.result | 4 | ||||
-rw-r--r-- | mysql-test/r/mysqld--help-win.result | 4 | ||||
-rw-r--r-- | mysql-test/suite/sys_vars/r/stored_program_cache_basic.result | 59 | ||||
-rw-r--r-- | mysql-test/suite/sys_vars/t/stored_program_cache_basic.test | 59 | ||||
-rw-r--r-- | sql/mysqld.cc | 5 | ||||
-rw-r--r-- | sql/mysqld.h | 1 | ||||
-rw-r--r-- | sql/sp_cache.cc | 29 | ||||
-rw-r--r-- | sql/sp_cache.h | 1 | ||||
-rw-r--r-- | sql/sql_parse.cc | 2 | ||||
-rw-r--r-- | sql/sql_prepare.cc | 6 | ||||
-rw-r--r-- | sql/sys_vars.cc | 7 |
11 files changed, 177 insertions, 0 deletions
diff --git a/mysql-test/r/mysqld--help-notwin.result b/mysql-test/r/mysqld--help-notwin.result index cb727984ec0..8dacb035f36 100644 --- a/mysql-test/r/mysqld--help-notwin.result +++ b/mysql-test/r/mysqld--help-notwin.result @@ -671,6 +671,9 @@ The following options may be given as the first argument: replication. --sql-mode=name Syntax: sql-mode=mode[,mode[,mode...]]. See the manual for the complete list of valid sql modes + --stored-program-cache=# + The soft upper limit for number of cached stored routines + for one connection. -s, --symbolic-links Enable symbolic link support. --sync-binlog=# Synchronously flush binary log to disk after every #th @@ -935,6 +938,7 @@ slow-query-log FALSE sort-buffer-size 2097152 sporadic-binlog-dump-fail FALSE sql-mode +stored-program-cache 256 symbolic-links FALSE sync-binlog 0 sync-frm TRUE diff --git a/mysql-test/r/mysqld--help-win.result b/mysql-test/r/mysqld--help-win.result index 2ef52355f90..1e7e877e29f 100644 --- a/mysql-test/r/mysqld--help-win.result +++ b/mysql-test/r/mysqld--help-win.result @@ -679,6 +679,9 @@ The following options may be given as the first argument: --sql-mode=name Syntax: sql-mode=mode[,mode[,mode...]]. See the manual for the complete list of valid sql modes --standalone Dummy option to start as a standalone program (NT). + --stored-program-cache=# + The soft upper limit for number of cached stored routines + for one connection. -s, --symbolic-links Enable symbolic link support. --sync-binlog=# Synchronously flush binary log to disk after every #th @@ -946,6 +949,7 @@ slow-start-timeout 15000 sort-buffer-size 2097152 sporadic-binlog-dump-fail FALSE sql-mode +stored-program-cache 256 symbolic-links FALSE sync-binlog 0 sync-frm TRUE diff --git a/mysql-test/suite/sys_vars/r/stored_program_cache_basic.result b/mysql-test/suite/sys_vars/r/stored_program_cache_basic.result new file mode 100644 index 00000000000..f1638520f72 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/stored_program_cache_basic.result @@ -0,0 +1,59 @@ +# Saving initial value of stored_program_cache in a temporary variable +SET @start_value = @@global.stored_program_cache; +SELECT @start_value; +@start_value +256 +# Display the DEFAULT value of stored_program_cache +SET @@global.stored_program_cache = DEFAULT; +SELECT @@global.stored_program_cache; +@@global.stored_program_cache +256 +# Verify default value of variable +SELECT @@global.stored_program_cache = 256; +@@global.stored_program_cache = 256 +1 +# Change the value of stored_program_cache to a valid value +SET @@global.stored_program_cache = 512; +SELECT @@global.stored_program_cache; +@@global.stored_program_cache +512 +# Change the value of stored_program_cache to invalid value +SET @@global.stored_program_cache = -1; +Warnings: +Warning 1292 Truncated incorrect stored_program_cache value: '-1' +SELECT @@global.stored_program_cache; +@@global.stored_program_cache +256 +SET @@global.stored_program_cache =100000000000; +Warnings: +Warning 1292 Truncated incorrect stored_program_cache value: '100000000000' +SELECT @@global.stored_program_cache; +@@global.stored_program_cache +524288 +SET @@global.stored_program_cache = 0; +Warnings: +Warning 1292 Truncated incorrect stored_program_cache value: '0' +SELECT @@global.stored_program_cache; +@@global.stored_program_cache +256 +SET @@global.stored_program_cache = 10000.01; +ERROR 42000: Incorrect argument type to variable 'stored_program_cache' +SET @@global.stored_program_cache = ON; +ERROR 42000: Incorrect argument type to variable 'stored_program_cache' +SET @@global.stored_program_cache= 'test'; +ERROR 42000: Incorrect argument type to variable 'stored_program_cache' +SET @@global.stored_program_cache = ''; +ERROR 42000: Incorrect argument type to variable 'stored_program_cache' +# Test if accessing session stored_program_cache gives error +SET @@session.stored_program_cache = 0; +ERROR HY000: Variable 'stored_program_cache' is a GLOBAL variable and should be set with SET GLOBAL +# Check if accessing variable without SCOPE points to same global variable +SET @@global.stored_program_cache = 512; +SELECT @@stored_program_cache = @@global.stored_program_cache; +@@stored_program_cache = @@global.stored_program_cache +1 +# Restore initial value +SET @@global.stored_program_cache = @start_value; +SELECT @@global.stored_program_cache; +@@global.stored_program_cache +256 diff --git a/mysql-test/suite/sys_vars/t/stored_program_cache_basic.test b/mysql-test/suite/sys_vars/t/stored_program_cache_basic.test new file mode 100644 index 00000000000..9a9ba2ebf81 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/stored_program_cache_basic.test @@ -0,0 +1,59 @@ +# Variable Name: stored_program_cache +# Scope: GLOBAL +# Access Type: Dynamic +# Data Type: numeric +# Default Value: 256 +# Range: 256-524288 + +--source include/load_sysvars.inc + +--echo # Saving initial value of stored_program_cache in a temporary variable +SET @start_value = @@global.stored_program_cache; +SELECT @start_value; + +--echo # Display the DEFAULT value of stored_program_cache +SET @@global.stored_program_cache = DEFAULT; +SELECT @@global.stored_program_cache; + +--echo # Verify default value of variable +SELECT @@global.stored_program_cache = 256; + +--echo # Change the value of stored_program_cache to a valid value +SET @@global.stored_program_cache = 512; +SELECT @@global.stored_program_cache; + +--echo # Change the value of stored_program_cache to invalid value +SET @@global.stored_program_cache = -1; +SELECT @@global.stored_program_cache; + +SET @@global.stored_program_cache =100000000000; +SELECT @@global.stored_program_cache; + +SET @@global.stored_program_cache = 0; +SELECT @@global.stored_program_cache; + +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.stored_program_cache = 10000.01; + +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.stored_program_cache = ON; +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.stored_program_cache= 'test'; + +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.stored_program_cache = ''; + +--echo # Test if accessing session stored_program_cache gives error + +--Error ER_GLOBAL_VARIABLE +SET @@session.stored_program_cache = 0; + +--echo # Check if accessing variable without SCOPE points to same global variable + +SET @@global.stored_program_cache = 512; +SELECT @@stored_program_cache = @@global.stored_program_cache; + +--echo # Restore initial value + +SET @@global.stored_program_cache = @start_value; +SELECT @@global.stored_program_cache; diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 74c5c23aaa2..5e347959d9b 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -511,6 +511,11 @@ uint sync_binlog_period= 0, sync_relaylog_period= 0, sync_relayloginfo_period= 0, sync_masterinfo_period= 0; ulong expire_logs_days = 0; ulong rpl_recovery_rank=0; +/** + Soft upper limit for number of sp_head objects that can be stored + in the sp_cache for one connection. +*/ +ulong stored_program_cache_size= 0; const double log_10[] = { 1e000, 1e001, 1e002, 1e003, 1e004, 1e005, 1e006, 1e007, 1e008, 1e009, diff --git a/sql/mysqld.h b/sql/mysqld.h index eb885417da3..67e39b6a46b 100644 --- a/sql/mysqld.h +++ b/sql/mysqld.h @@ -181,6 +181,7 @@ extern ulonglong max_binlog_cache_size, max_binlog_stmt_cache_size; extern ulong max_binlog_size, max_relay_log_size; extern ulong opt_binlog_rows_event_max_size; extern ulong rpl_recovery_rank, thread_cache_size; +extern ulong stored_program_cache_size; extern ulong back_log; extern char language[FN_REFLEN]; extern "C" MYSQL_PLUGIN_IMPORT ulong server_id; diff --git a/sql/sp_cache.cc b/sql/sp_cache.cc index 8972303ef03..d6445799a36 100644 --- a/sql/sp_cache.cc +++ b/sql/sp_cache.cc @@ -57,6 +57,20 @@ public: { my_hash_delete(&m_hashtable, (uchar *)sp); } + + /** + Remove all elements from a stored routine cache if the current + number of elements exceeds the argument value. + + @param[in] upper_limit_for_elements Soft upper limit of elements that + can be stored in the cache. + */ + void enforce_limit(ulong upper_limit_for_elements) + { + if (m_hashtable.records > upper_limit_for_elements) + my_hash_reset(&m_hashtable); + } + private: void init(); void cleanup(); @@ -228,6 +242,21 @@ ulong sp_cache_version() } +/** + Enforce that the current number of elements in the cache don't exceed + the argument value by flushing the cache if necessary. + + @param[in] c Cache to check + @param[in] upper_limit_for_elements Soft upper limit for number of sp_head + objects that can be stored in the cache. +*/ +void +sp_cache_enforce_limit(sp_cache *c, ulong upper_limit_for_elements) +{ + if (c) + c->enforce_limit(upper_limit_for_elements); +} + /************************************************************************* Internal functions *************************************************************************/ diff --git a/sql/sp_cache.h b/sql/sp_cache.h index 39448568a76..db70066587a 100644 --- a/sql/sp_cache.h +++ b/sql/sp_cache.h @@ -62,5 +62,6 @@ sp_head *sp_cache_lookup(sp_cache **cp, sp_name *name); void sp_cache_invalidate(); void sp_cache_flush_obsolete(sp_cache **cp, sp_head **sp); ulong sp_cache_version(); +void sp_cache_enforce_limit(sp_cache *cp, ulong upper_limit_for_elements); #endif /* _SP_CACHE_H_ */ diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 32ccb8f2c5f..a5d23858a8d 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -5632,6 +5632,8 @@ void mysql_parse(THD *thd, char *rawbuf, uint length, query_cache_abort(&thd->query_cache_tls); } thd_proc_info(thd, "freeing items"); + sp_cache_enforce_limit(thd->sp_proc_cache, stored_program_cache_size); + sp_cache_enforce_limit(thd->sp_func_cache, stored_program_cache_size); thd->end_statement(); thd->cleanup_after_query(); DBUG_ASSERT(thd->change_list.is_empty()); diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 48b6886f6f2..cfdf7d27e58 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -2195,6 +2195,9 @@ void mysqld_stmt_prepare(THD *thd, const char *packet, uint packet_length) thd->protocol= save_protocol; + sp_cache_enforce_limit(thd->sp_proc_cache, stored_program_cache_size); + sp_cache_enforce_limit(thd->sp_func_cache, stored_program_cache_size); + /* check_prepared_statemnt sends the metadata packet in case of success */ DBUG_VOID_RETURN; } @@ -2560,6 +2563,9 @@ void mysqld_stmt_execute(THD *thd, char *packet_arg, uint packet_length) stmt->execute_loop(&expanded_query, open_cursor, packet, packet_end); thd->protocol= save_protocol; + sp_cache_enforce_limit(thd->sp_proc_cache, stored_program_cache_size); + sp_cache_enforce_limit(thd->sp_func_cache, stored_program_cache_size); + /* Close connection socket; for use with client testing (Bug#43560). */ DBUG_EXECUTE_IF("close_conn_after_stmt_execute", vio_close(thd->net.vio);); diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index f5a9ab3b55c..b640bdba491 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -3253,6 +3253,13 @@ static Sys_var_tz Sys_time_zone( SESSION_VAR(time_zone), NO_CMD_LINE, DEFAULT(&default_tz), NO_MUTEX_GUARD, IN_BINLOG); +static Sys_var_ulong Sys_sp_cache_size( + "stored_program_cache", + "The soft upper limit for number of cached stored routines for " + "one connection.", + GLOBAL_VAR(stored_program_cache_size), CMD_LINE(REQUIRED_ARG), + VALID_RANGE(256, 512 * 1024), DEFAULT(256), BLOCK_SIZE(1)); + /**************************************************************************** Used templates |