Kristofer, hello.
I suggested for you to be content with one error message suitable for
both branches of fix_query_cache_size(). If it's difficult to
calculate the max in order to report the range - and that'd be ideal
to have it along with the min - you can format a new message
" can not make %s than %ul" to call the print
func with "less" or "as big as" for %s , and the min or the requested
size for %ul.
Wrt to calculation of the min, please look at my comments further.
> Below is the list of changes that have just been committed into a local
> 5.0 repository of thek. When thek does a push these changes
> will be propagated to the main repository and, within 24 hours after the
> push, to the public repository.
> For information on how to access the public repository
> see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
>
> ChangeSet@stripped, 2008-03-11 14:50:41+01:00, thek@adventure.(none) +5 -0
> Bug#30087 Set query_cache_size, if the value is too small, get a unclear warning
>
> This bugs clarifies a warning message issued when the query cache data
> size becomes smaller than the minium allowed size.
>
> mysql-test/r/query_cache.result@stripped, 2008-03-11 14:50:39+01:00,
> thek@adventure.(none) +4 -4
> New warning message when a too small value has been set for query cache
> size.
>
> sql/set_var.cc@stripped, 2008-03-11 14:50:39+01:00, thek@adventure.(none) +13 -4
> If the query cache data size becomes smaller than the minium allocation
> unit, issue a warning indicating that the size should be made bigger.
>
> sql/share/errmsg.txt@stripped, 2008-03-11 14:50:40+01:00, thek@adventure.(none) +4 -0
> New error message created
>
> sql/sql_cache.cc@stripped, 2008-03-11 14:50:39+01:00, thek@adventure.(none) +16 -0
> If the query cache data size becomes smaller than the minium allocation
> unit, issue a warning indicating that the size should be made bigger.
>
> Added interface method Query_cache::get_minimal_size_limit().
>
> sql/sql_cache.h@stripped, 2008-03-11 14:50:39+01:00, thek@adventure.(none) +2 -0
> If the query cache data size becomes smaller than the minium allocation
> unit, issue a warning indicating that the size should be made bigger.
>
> Added interface method Query_cache::get_minimal_size_limit().
>
> diff -Nrup a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
> --- a/mysql-test/r/query_cache.result 2007-08-21 17:47:03 +02:00
> +++ b/mysql-test/r/query_cache.result 2008-03-11 14:50:39 +01:00
> @@ -564,7 +564,7 @@ select * from t1;
> a
> set GLOBAL query_cache_size=1024;
> Warnings:
> -Warning 1282 Query cache failed to set size 1024; new query cache size is 0
> +Warning 1476 Cache size 1024 is too small to be efficient; minimal value is 74040.
> New size is 0
> show global variables like "query_cache_size";
> Variable_name Value
> query_cache_size 0
> @@ -572,7 +572,7 @@ select * from t1;
> a
> set GLOBAL query_cache_size=10240;
> Warnings:
> -Warning 1282 Query cache failed to set size 10240; new query cache size is 0
> +Warning 1476 Cache size 10240 is too small to be efficient; minimal value is 74040.
> New size is 0
> show global variables like "query_cache_size";
> Variable_name Value
> query_cache_size 0
> @@ -580,7 +580,7 @@ select * from t1;
> a
> set GLOBAL query_cache_size=20480;
> Warnings:
> -Warning 1282 Query cache failed to set size 20480; new query cache size is 0
> +Warning 1476 Cache size 20480 is too small to be efficient; minimal value is 74040.
> New size is 0
> show global variables like "query_cache_size";
> Variable_name Value
> query_cache_size 0
> @@ -588,7 +588,7 @@ select * from t1;
> a
> set GLOBAL query_cache_size=40960;
> Warnings:
> -Warning 1282 Query cache failed to set size 40960; new query cache size is 0
> +Warning 1476 Cache size 40960 is too small to be efficient; minimal value is 74040.
> New size is 0
I got curious and looked in the test to find out that the first
success to set up a new value is in right next to the 40960
set GLOBAL query_cache_size=51200;
show global variables like "query_cache_size";
Variable_name Value
query_cache_size 51200
Looking at the new error message
minimal value is 74040
should make us suspecious if
everything is all right with calculations.
> show global variables like "query_cache_size";
> Variable_name Value
> query_cache_size 0
> diff -Nrup a/sql/set_var.cc b/sql/set_var.cc
> --- a/sql/set_var.cc 2008-01-10 12:01:24 +01:00
> +++ b/sql/set_var.cc 2008-03-11 14:50:39 +01:00
> @@ -1344,11 +1344,20 @@ static void fix_query_cache_size(THD *th
> {
> #ifdef HAVE_QUERY_CACHE
> ulong requested= query_cache_size;
> - query_cache.resize(query_cache_size);
> + query_cache_size= query_cache.resize(query_cache_size);
> if (requested != query_cache_size)
> - push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
> - ER_WARN_QC_RESIZE, ER(ER_WARN_QC_RESIZE),
> - requested, query_cache_size);
> + {
> + if (requested < query_cache.get_minimal_size_limit())
> + push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
> + ER_WARN_QC_RESIZE2, ER(ER_WARN_QC_RESIZE2),
> + requested,
> + query_cache.get_minimal_size_limit(),
> + query_cache_size);
> + else
> + push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
> + ER_WARN_QC_RESIZE, ER(ER_WARN_QC_RESIZE),
> + requested, query_cache_size);
> + }
> #endif
> }
>
> diff -Nrup a/sql/share/errmsg.txt b/sql/share/errmsg.txt
> --- a/sql/share/errmsg.txt 2007-10-23 13:15:27 +02:00
> +++ b/sql/share/errmsg.txt 2008-03-11 14:50:40 +01:00
> @@ -5641,3 +5641,7 @@ ER_NAME_BECOMES_EMPTY
> eng "Name '%-.64s' has become ''"
> ER_AMBIGUOUS_FIELD_TERM
> eng "First character of the FIELDS TERMINATED string is ambiguous; please use
> non-optional and non-empty FIELDS ENCLOSED BY"
> +ER_WARN_QC_RESIZE2
> + eng "Cache size %lu is too small to be efficient; minimal value is %lu. New size is
> %lu"
> + swe "Cache-storleken %lu Дr fЖr liten fЖr att vara effektiv; minsta vДrdet Дr
> %lu. Ny storlek Дr %lu"
> +
> diff -Nrup a/sql/sql_cache.cc b/sql/sql_cache.cc
> --- a/sql/sql_cache.cc 2007-10-01 12:44:28 +02:00
> +++ b/sql/sql_cache.cc 2008-03-11 14:50:39 +01:00
> @@ -1614,6 +1614,22 @@ void Query_cache::init()
> }
>
>
> +/**
> + Return the lowest possible query cache size.
> +*/
> +
> +ulong Query_cache::get_minimal_size_limit()
> +{
> + ulong approx_additional_data_size= (sizeof(Query_cache) +
> + sizeof(gptr)*(def_query_hash_size+
> + def_table_hash_size));
> +
> + ulong data_size= min_allocation_unit*2 << QUERY_CACHE_MEM_BIN_STEP_PWR2
> <<
> + QUERY_CACHE_MEM_BIN_FIRST_STEP_PWR2;
> +
> + return(data_size + approx_additional_data_size);
> +}
> +
Thanks to your explanations I was able to make my own formula which
basicly as yours except no multipler 2 and three addings due to division
without reminder by ALIGN_SIZE(1), (2^ QUERY_CACHE_MEM_BIN_STEP_PWR2), and
(2^QUERY_CACHE_MEM_BIN_FIRST_STEP_PWR2), ^ stands for power.
data_size = min_allocation_unit << QUERY_CACHE_MEM_BIN_STEP_PWR2 <<
QUERY_CACHE_MEM_BIN_FIRST_STEP_PWR2
+ ALIGN_SIZE(1) - 1
+ (1 << QUERY_CACHE_MEM_BIN_STEP_PWR2) - 1
+ (1 << QUERY_CACHE_MEM_BIN_FIRST_STEP_PWR2) - 1
I test it somehow but can not claim for its the absolute preciseness.
> ulong Query_cache::init_cache()
> {
> uint mem_bin_count, num, step;
> diff -Nrup a/sql/sql_cache.h b/sql/sql_cache.h
> --- a/sql/sql_cache.h 2007-08-17 16:55:19 +02:00
> +++ b/sql/sql_cache.h 2008-03-11 14:50:39 +01:00
> @@ -396,6 +396,8 @@ protected:
> /* register query in cache */
> void store_query(THD *thd, TABLE_LIST *used_tables);
>
> + ulong get_minimal_size_limit();
> +
> /*
> Check if the query is in the cache and if this is true send the
> data to client.
>
Please address my finding,
cheers,
Andrei
