maria-developers team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

Hi guys, what's the "MySQL variables auto-tuning code" means? i didn't saw
that in mysql source and at mysql docs, any idea what this means?


2013/9/18 Sergey Vojtovich <svoj@xxxxxxxxxxx>

> At lp:maria/10.0
>
> ------------------------------------------------------------
> revno: 3816
> revision-id: svoj@xxxxxxxxxxx-20130918071716-yu2a5489uldzfuy9
> parent: sanja@xxxxxxxxxxxx-20130917190515-g3xncb1slqwy4cix
> committer: Sergey Vojtovich <svoj@xxxxxxxxxxx>
> branch nick: 10.0-mdev4883
> timestamp: Wed 2013-09-18 11:17:16 +0400
> message:
>   MDEV-4883 - Not all host_cache tests have been merged
>
>   Merged main.host_cache_size_functionality test.
>
>   Test had to be adjusted because MariaDB doesn't have
>   MySQL variables auto-tuning code.
> === added file 'mysql-test/r/host_cache_size_functionality.result'
> --- a/mysql-test/r/host_cache_size_functionality.result 1970-01-01
> 00:00:00 +0000
> +++ b/mysql-test/r/host_cache_size_functionality.result 2013-09-18
> 07:17:16 +0000
> @@ -0,0 +1,78 @@
> +'#________________________VAR_06_Host_Cache_Size__________________#'
> +echo '##'
> +--echo '#---------------------WL6372_VAR_6_01----------------------#'
> +####################################################################
> +#   Checking default value                                         #
> +####################################################################
> +SELECT COUNT(@@GLOBAL.Host_Cache_Size)
> +1 Expected
> +set @Default_host_cache_size=128;
> +select @@global.Host_Cache_Size=@Default_host_cache_size;
> +@@global.Host_Cache_Size=@Default_host_cache_size
> +1
> +1 Expected
> +'#---------------------WL6372_VAR_6_02----------------------#'
> +# Restart server with Host_Cache_Size 1
> +SELECT @@GLOBAL.Host_Cache_Size;
> +@@GLOBAL.Host_Cache_Size
> +1
> +1 Expected
> +set @Default_host_cache_size=128;
> +SET @@GLOBAL.Host_Cache_Size=DEFAULT;
> +select @@global.Host_Cache_Size=@Default_host_cache_size;
> +@@global.Host_Cache_Size=@Default_host_cache_size
> +1
> +1 Expected
> +'#---------------------WL6372_VAR_6_03----------------------#'
> +SET @@local.Host_Cache_Size=1;
> +ERROR HY000: Variable 'host_cache_size' is a GLOBAL variable and should
> be set with SET GLOBAL
> +Expected error 'Global variable'
> +SET @@session.Host_Cache_Size=1;
> +ERROR HY000: Variable 'host_cache_size' is a GLOBAL variable and should
> be set with SET GLOBAL
> +Expected error 'Global variable'
> +SET @@GLOBAL.Host_Cache_Size=1;
> +SET @@GLOBAL.Host_Cache_Size=DEFAULT;
> +SELECT COUNT(@@GLOBAL.Host_Cache_Size);
> +COUNT(@@GLOBAL.Host_Cache_Size)
> +1
> +1 Expected
> +select @@global.Host_Cache_Size=@Default_host_cache_size;
> +@@global.Host_Cache_Size=@Default_host_cache_size
> +1
> +1 Expected
> +'#---------------------WL6372_VAR_6_04----------------------#'
> +SELECT @@GLOBAL.Host_Cache_Size = VARIABLE_VALUE
> +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
> +WHERE VARIABLE_NAME='Host_Cache_Size';
> +@@GLOBAL.Host_Cache_Size = VARIABLE_VALUE
> +1
> +1 Expected
> +SELECT COUNT(@@GLOBAL.Host_Cache_Size);
> +COUNT(@@GLOBAL.Host_Cache_Size)
> +1
> +1 Expected
> +SELECT COUNT(VARIABLE_VALUE)
> +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
> +WHERE VARIABLE_NAME='Host_Cache_Size';
> +COUNT(VARIABLE_VALUE)
> +1
> +1 Expected
> +'#---------------------WL6372_VAR_6_05----------------------#'
> +SELECT @@Host_Cache_Size = @@GLOBAL.Host_Cache_Size;
> +@@Host_Cache_Size = @@GLOBAL.Host_Cache_Size
> +1
> +1 Expected
> +SELECT COUNT(@@local.Host_Cache_Size);
> +ERROR HY000: Variable 'host_cache_size' is a GLOBAL variable
> +Expected error 'Variable is a GLOBAL variable'
> +SELECT COUNT(@@SESSION.Host_Cache_Size);
> +ERROR HY000: Variable 'host_cache_size' is a GLOBAL variable
> +Expected error 'Variable is a GLOBAL variable'
> +SELECT COUNT(@@GLOBAL.Host_Cache_Size);
> +COUNT(@@GLOBAL.Host_Cache_Size)
> +1
> +1 Expected
> +SELECT Host_Cache_Size = @@SESSION.Host_Cache_Size;
> +ERROR 42S22: Unknown column 'Host_Cache_Size' in 'field list'
> +Expected error 'Unknown column Host_Cache_Size in field list'
> +SET @@GLOBAL.Host_Cache_Size=DEFAULT;
>
> === added file 'mysql-test/t/host_cache_size_functionality.test'
> --- a/mysql-test/t/host_cache_size_functionality.test   1970-01-01
> 00:00:00 +0000
> +++ b/mysql-test/t/host_cache_size_functionality.test   2013-09-18
> 07:17:16 +0000
> @@ -0,0 +1,185 @@
>
> +###############################################################################
> +#
>     #
> +# Variable Name: Host_Cache_Size
>      #
> +# Scope: Global
>     #
> +# Access Type: Dynamic
>      #
> +# Data Type: numeric
>      #
> +#
>     #
> +#
>     #
> +# Creation Date: 2012-08-31
>     #
> +# Author : Tanjot Singh Uppal
>     #
> +#
>     #
> +#
>     #
> +# Description:Test Cases of Dynamic System Variable Host_Cache_Size
>     #
> +#             that checks the behavior of this variable in the following
> ways #
> +#              * Value Check
>      #
> +#              * Scope Check
>      #
> +#              * Functionality Check
>      #
> +#              * Accessability Check
>      #
> +#
>     #
> +# This test does not perform the crash recovery on this variable
>      #
> +# For crash recovery test on default change please run the ibtest
>     #
>
> +###############################################################################
> +
> +-- source include/have_innodb.inc
> +-- source include/not_embedded.inc
> +-- source include/have_innodb_16k.inc
> +--disable_warnings
> +echo '#________________________VAR_06_Host_Cache_Size__________________#'
> +echo '##'
> +--echo '#---------------------WL6372_VAR_6_01----------------------#'
> +####################################################################
> +#   Checking default value                                         #
> +####################################################################
> +SELECT COUNT(@@GLOBAL.Host_Cache_Size);
> +--echo 1 Expected
> +
> +#set @Default_host_cache_size=(select
> if(if(@@global.max_connections<500,128+@@global.max_connections,128+@
> @global.max_connections+floor((@@global.max_connections-500)/20))>2000,2000,if(@@global.max_connections<500,128+@
> @global.max_connections,128+@
> @global.max_connections+floor((@@global.max_connections-500)/20))));
> +set @Default_host_cache_size=128;
> +
> +select @@global.Host_Cache_Size=@Default_host_cache_size;
> +--echo 1 Expected
> +
> +
> +--echo '#---------------------WL6372_VAR_6_02----------------------#'
>
> +#################################################################################
> +# Checking the Default value post starting the server with other value
>        #
>
> +#################################################################################
> +--echo # Restart server with Host_Cache_Size 1
> +
> +let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect;
> +--exec echo "wait" > $restart_file
> +--shutdown_server 10
> +--source include/wait_until_disconnected.inc
> +-- exec echo "restart:--host_cache_size=1  " >
> $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
> +-- enable_reconnect
> +-- source include/wait_until_connected_again.inc
> +
> +--disable_warnings
> +
> +SELECT @@GLOBAL.Host_Cache_Size;
> +--echo 1 Expected
> +
> +#set @Default_host_cache_size=(select
> if(if(@@global.max_connections<500,128+@@global.max_connections,128+@
> @global.max_connections+floor((@@global.max_connections-500)/20))>2000,2000,if(@@global.max_connections<500,128+@
> @global.max_connections,128+@
> @global.max_connections+floor((@@global.max_connections-500)/20))));
> +set @Default_host_cache_size=128;
> +SET @@GLOBAL.Host_Cache_Size=DEFAULT;
> +select @@global.Host_Cache_Size=@Default_host_cache_size;
> +--echo 1 Expected
> +
> +
> +--echo '#---------------------WL6372_VAR_6_03----------------------#'
> +####################################################################
> +#   Checking Value can be set - Dynamic                            #
> +####################################################################
> +--error ER_GLOBAL_VARIABLE
> +SET @@local.Host_Cache_Size=1;
> +--echo Expected error 'Global variable'
> +
> +--error ER_GLOBAL_VARIABLE
> +SET @@session.Host_Cache_Size=1;
> +--echo Expected error 'Global variable'
> +
> +SET @@GLOBAL.Host_Cache_Size=1;
> +SET @@GLOBAL.Host_Cache_Size=DEFAULT;
> +
> +SELECT COUNT(@@GLOBAL.Host_Cache_Size);
> +--echo 1 Expected
> +
> +select @@global.Host_Cache_Size=@Default_host_cache_size;
> +--echo 1 Expected
> +
> +--echo '#---------------------WL6372_VAR_6_04----------------------#'
> +#################################################################
> +# Check if the value in GLOBAL Table matches value in variable  #
> +#################################################################
> +SELECT @@GLOBAL.Host_Cache_Size = VARIABLE_VALUE
> +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
> +WHERE VARIABLE_NAME='Host_Cache_Size';
> +--echo 1 Expected
> +
> +SELECT COUNT(@@GLOBAL.Host_Cache_Size);
> +--echo 1 Expected
> +
> +SELECT COUNT(VARIABLE_VALUE)
> +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
> +WHERE VARIABLE_NAME='Host_Cache_Size';
> +--echo 1 Expected
> +
> +--echo '#---------------------WL6372_VAR_6_05----------------------#'
>
> +################################################################################
> +#  Checking Variable Scope
>       #
>
> +################################################################################
> +SELECT @@Host_Cache_Size = @@GLOBAL.Host_Cache_Size;
> +--echo 1 Expected
> +
> +--Error ER_INCORRECT_GLOBAL_LOCAL_VAR
> +SELECT COUNT(@@local.Host_Cache_Size);
> +--echo Expected error 'Variable is a GLOBAL variable'
> +
> +--Error ER_INCORRECT_GLOBAL_LOCAL_VAR
> +SELECT COUNT(@@SESSION.Host_Cache_Size);
> +--echo Expected error 'Variable is a GLOBAL variable'
> +
> +SELECT COUNT(@@GLOBAL.Host_Cache_Size);
> +--echo 1 Expected
> +
> +--Error ER_BAD_FIELD_ERROR
> +SELECT Host_Cache_Size = @@SESSION.Host_Cache_Size;
> +--echo Expected error 'Unknown column Host_Cache_Size in field list'
> +
> +#The below check has been commented out as the IP fetch is different in a
> P2P connection than BroadBand connection
> +#--echo '#---------------------WL6372_VAR_6_06----------------------#'
>
> +###############################################################################
> +# Checking the Host   cahce functionality
>     #
>
> +###############################################################################
> +
> +#SET @@GLOBAL.Host_Cache_Size=2;
> +#--disable_warnings
> +
> +#--perl
> +#my $ip=`ifconfig | egrep "inet addr|inet" | sed -e 's/^.*inet addr://'
> -e 's/^.*inet//'| sed 's/ .*\$//'|egrep -i "broadcast|bcast"|head -1|awk
> '{print $1}'`;
> +#open (LOGFH, ">" . $ENV{'MYSQL_TMP_DIR'} . "/bind_ip");
> +#print LOGFH "let \$bind_ip = $ip;\n";
> +#close LOGFH;
> +#EOF
> +
> +#--source $MYSQL_TMP_DIR/bind_ip
> +#--remove_file $MYSQL_TMP_DIR/bind_ip
> +
> +#let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect;
> +#--exec echo "wait" > $restart_file
> +#--shutdown_server 10
> +#--source include/wait_until_disconnected.inc
> +#-- exec echo "restart:--bind-address=$bind_ip  " >
> $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
> +#-- enable_reconnect
> +#-- source include/wait_until_connected_again.inc
> +
> +#connection default;
> +#--disable_warnings
> +
> +#create user binduser;
> +#grant all on *.* to binduser;
> +
> +#select count(IP) from performance_schema.host_cache;
> +#--echo 0 Expected
> +
> +#connect (con1,$bind_ip,binduser,,);
> +#select count(IP) from performance_schema.host_cache;
> +#--echo 1 Expected
> +
> +#disconnect con1;
> +#connection default;
> +
> +#--disable_warnings
> +
> +# The below check is hashed until the BUG14689561# is fixed
> +#
> +#flush hosts;
> +
> +#connect (con2,$bind_ip,binduser,,);
> +#select count(IP) from performance_schema.host_cache;
> +#--echo 1 Expected
> +
> +#disconnect con2;
> +
> +SET @@GLOBAL.Host_Cache_Size=DEFAULT;
>
> _______________________________________________
> commits mailing list
> commits@xxxxxxxxxxx
> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
>



-- 
Roberto Spadim
SPAEmpresarial