Fixed problem with very slow shutdown when using 100,000 MyISAM tables with delay_key_write

Reason for the problem was that the hash of changed files in the key cache was too small (was 128). Fixed by making the hash size larger and changeable.

- Introduced key-cache-file-hash-size (default 512) for MyISAM and aria_pagecache_file_hash_size (default 512) for Aria.
- Added new status variable "Feature_delay_key_write" which counts number of tables opened that are using delay_key_write


mysql-test/r/features.result:
  Added test of Feature_delay_key_write
mysql-test/r/key_cache.result:
  Updated tests as the number of blocks has changed
mysql-test/r/mysqld--help.result:
  Updated result
mysql-test/suite/maria/maria3.result:
  Updated result
mysql-test/suite/sys_vars/r/key_cache_file_hash_size_basic.result:
  Test new variable
mysql-test/suite/sys_vars/t/aria_pagecache_file_hash_size_basic.test:
  Test new variable
mysql-test/suite/sys_vars/t/key_cache_file_hash_size_basic.test:
  Test new variable
mysql-test/t/features.test:
  Added test of Feature_delay_key_write
mysql-test/t/key_cache.test:
  Updated tests as the number of blocks has changed
mysys/mf_keycache.c:
  Made CHANGED_BLOCKS_HASH dynamic
sql/handler.cc:
  Updated call to init_key_cache()
sql/mysqld.cc:
  Added "Feature_delay_key_write"
  Added support for key-cache-file-hash-size
sql/mysqld.h:
  Added support for key-cache-file-hash-size
sql/sql_class.h:
  Added feature_files_opened_with_delayed_keys
sql/sys_vars.cc:
  Added key_cache_file_hash_size
storage/maria/ha_maria.cc:
  Added pagecache_file_hash_size
  Added counting of files with delay_key_write
storage/maria/ma_checkpoint.c:
  Fixed compiler warning
storage/maria/ma_pagecache.c:
  Made PAGECACHE_CHANGED_BLOCKS_HASH into a variable
storage/maria/ma_pagecache.h:
  Made PAGECACHE_CHANGED_BLOCKS_HASH into a variable
storage/maria/ma_rt_test.c:
  Updated parameters for init_pagecache()
storage/maria/ma_test1.c:
  Updated parameters for init_pagecache()
storage/maria/ma_test2.c:
  Updated parameters for init_pagecache()
storage/maria/ma_test3.c:
  Updated parameters for init_pagecache()
storage/maria/maria_chk.c:
  Updated parameters for init_pagecache()
storage/maria/maria_ftdump.c:
  Updated parameters for init_pagecache()
storage/maria/maria_pack.c:
  Updated parameters for init_pagecache()
storage/maria/maria_read_log.c:
  Updated parameters for init_pagecache()
storage/maria/unittest/ma_pagecache_consist.c:
  Updated parameters for init_pagecache()
storage/maria/unittest/ma_pagecache_rwconsist.c:
  Updated parameters for init_pagecache()
storage/maria/unittest/ma_pagecache_rwconsist2.c:
  Updated parameters for init_pagecache()
storage/maria/unittest/ma_pagecache_single.c:
  Updated parameters for init_pagecache()
storage/maria/unittest/ma_test_loghandler-t.c:
  Updated parameters for init_pagecache()
storage/maria/unittest/ma_test_loghandler_first_lsn-t.c:
  Updated parameters for init_pagecache()
storage/maria/unittest/ma_test_loghandler_max_lsn-t.c:
  Updated parameters for init_pagecache()
storage/maria/unittest/ma_test_loghandler_multigroup-t.c:
  Updated parameters for init_pagecache()
storage/maria/unittest/ma_test_loghandler_multithread-t.c:
  Updated parameters for init_pagecache()
storage/maria/unittest/ma_test_loghandler_noflush-t.c:
  Updated parameters for init_pagecache()
storage/maria/unittest/ma_test_loghandler_nologs-t.c:
  Updated parameters for init_pagecache()
storage/maria/unittest/ma_test_loghandler_pagecache-t.c:
  Updated parameters for init_pagecache()
storage/maria/unittest/ma_test_loghandler_purge-t.c:
  Updated parameters for init_pagecache()
storage/myisam/ha_myisam.cc:
  Added counting of files with delay_key_write
storage/myisam/mi_check.c:
  Updated call to init_key_cache()
storage/myisam/mi_test1.c:
  Updated call to init_key_cache()
storage/myisam/mi_test2.c:
  Updated call to init_key_cache()
storage/myisam/mi_test3.c:
  Updated call to init_key_cache()
storage/myisam/mi_test_all.sh:
  Fixed broken test
storage/myisam/myisam_ftdump.c:
  Updated call to init_key_cache()
storage/myisam/myisamchk.c:
  Updated call to init_key_cache()
storage/myisam/myisamlog.c:
  Updated call to init_key_cache()

5 files changed, 326 insertions, 0 deletions

diff --git a/mysql-test/suite/maria/maria3.result b/mysql-test/suite/maria/maria3.result
index 021cc8fc357..74eed530bd9 100644
--- a/mysql-test/suite/maria/maria3.result
+++ b/mysql-test/suite/maria/maria3.result
@@ -314,6 +314,7 @@ aria_max_sort_file_size	9223372036853727232
 aria_pagecache_age_threshold	300
 aria_pagecache_buffer_size	8388608
 aria_pagecache_division_limit	100
+aria_pagecache_file_hash_size	512
 aria_page_checksum	OFF
 aria_recover	NORMAL
 aria_repair_threads	1
diff --git a/mysql-test/suite/sys_vars/r/aria_pagecache_file_hash_size_basic.result b/mysql-test/suite/sys_vars/r/aria_pagecache_file_hash_size_basic.result
new file mode 100644
index 00000000000..0bdd56c298f
--- /dev/null
+++ b/mysql-test/suite/sys_vars/r/aria_pagecache_file_hash_size_basic.result
@@ -0,0 +1,21 @@
+select @@global.aria_pagecache_file_hash_size;
+@@global.aria_pagecache_file_hash_size
+512
+select @@session.aria_pagecache_file_hash_size;
+ERROR HY000: Variable 'aria_pagecache_file_hash_size' is a GLOBAL variable
+show global variables like 'aria_pagecache_file_hash_size';
+Variable_name	Value
+aria_pagecache_file_hash_size	512
+show session variables like 'aria_pagecache_file_hash_size';
+Variable_name	Value
+aria_pagecache_file_hash_size	512
+select * from information_schema.global_variables where variable_name='aria_pagecache_file_hash_size';
+VARIABLE_NAME	VARIABLE_VALUE
+ARIA_PAGECACHE_FILE_HASH_SIZE	512
+select * from information_schema.session_variables where variable_name='aria_pagecache_file_hash_size';
+VARIABLE_NAME	VARIABLE_VALUE
+ARIA_PAGECACHE_FILE_HASH_SIZE	512
+set global aria_pagecache_file_hash_size=200;
+ERROR HY000: Variable 'aria_pagecache_file_hash_size' is a read only variable
+set session aria_pagecache_file_hash_size=200;
+ERROR HY000: Variable 'aria_pagecache_file_hash_size' is a read only variable
diff --git a/mysql-test/suite/sys_vars/r/key_cache_file_hash_size_basic.result b/mysql-test/suite/sys_vars/r/key_cache_file_hash_size_basic.result
new file mode 100644
index 00000000000..52ebfc98cdc
--- /dev/null
+++ b/mysql-test/suite/sys_vars/r/key_cache_file_hash_size_basic.result
@@ -0,0 +1,114 @@
+SET @start_value = @@global.key_cache_file_hash_size;
+SELECT @start_value;
+@start_value
+512
+'#--------------------FN_DYNVARS_056_01------------------------#'
+SET @@global.key_cache_file_hash_size = DEFAULT;
+SELECT @@global.key_cache_file_hash_size;
+@@global.key_cache_file_hash_size
+512
+'#---------------------FN_DYNVARS_056_02-------------------------#'
+SET @@global.key_cache_file_hash_size = @start_value;
+SELECT @@global.key_cache_file_hash_size = 300;
+@@global.key_cache_file_hash_size = 300
+0
+'#--------------------FN_DYNVARS_056_03------------------------#'
+SET @@global.key_cache_file_hash_size = 128;
+SET @@global.key_cache_file_hash_size = 16384;
+SELECT @@global.key_cache_file_hash_size;
+@@global.key_cache_file_hash_size
+16384
+'#--------------------FN_DYNVARS_056_04-------------------------#'
+SET @@global.key_cache_file_hash_size = -1;
+Warnings:
+Warning	1292	Truncated incorrect key_cache_file_hash_size value: '-1'
+SELECT @@global.key_cache_file_hash_size;
+@@global.key_cache_file_hash_size
+128
+SET @@global.key_cache_file_hash_size = 42949672951;
+Warnings:
+Warning	1292	Truncated incorrect key_cache_file_hash_size value: '42949672951'
+SELECT @@global.key_cache_file_hash_size;
+@@global.key_cache_file_hash_size
+16384
+SET @@global.key_cache_file_hash_size = 10000.01;
+ERROR 42000: Incorrect argument type to variable 'key_cache_file_hash_size'
+SELECT @@global.key_cache_file_hash_size;
+@@global.key_cache_file_hash_size
+16384
+SET @@global.key_cache_file_hash_size = -1024;
+Warnings:
+Warning	1292	Truncated incorrect key_cache_file_hash_size value: '-1024'
+SELECT @@global.key_cache_file_hash_size;
+@@global.key_cache_file_hash_size
+128
+SET @@global.key_cache_file_hash_size = 99;
+Warnings:
+Warning	1292	Truncated incorrect key_cache_file_hash_size value: '99'
+SELECT @@global.key_cache_file_hash_size;
+@@global.key_cache_file_hash_size
+128
+SET @@global.key_cache_file_hash_size = ON;
+ERROR 42000: Incorrect argument type to variable 'key_cache_file_hash_size'
+SELECT @@global.key_cache_file_hash_size;
+@@global.key_cache_file_hash_size
+128
+SET @@global.key_cache_file_hash_size = 'test';
+ERROR 42000: Incorrect argument type to variable 'key_cache_file_hash_size'
+SELECT @@global.key_cache_file_hash_size;
+@@global.key_cache_file_hash_size
+128
+'#-------------------FN_DYNVARS_056_05----------------------------#'
+SET @@session.key_cache_file_hash_size = 0;
+ERROR HY000: Variable 'key_cache_file_hash_size' is a GLOBAL variable and should be set with SET GLOBAL
+SELECT @@session.key_cache_file_hash_size;
+ERROR HY000: Variable 'key_cache_file_hash_size' is a GLOBAL variable
+'#----------------------FN_DYNVARS_056_06------------------------#'
+SELECT @@global.key_cache_file_hash_size = VARIABLE_VALUE 
+FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES 
+WHERE VARIABLE_NAME='key_cache_file_hash_size';
+@@global.key_cache_file_hash_size = VARIABLE_VALUE
+1
+SELECT @@key_cache_file_hash_size = VARIABLE_VALUE 
+FROM INFORMATION_SCHEMA.SESSION_VARIABLES 
+WHERE VARIABLE_NAME='key_cache_file_hash_size';
+@@key_cache_file_hash_size = VARIABLE_VALUE
+1
+'#---------------------FN_DYNVARS_056_07----------------------#'
+SET @@global.key_cache_file_hash_size = TRUE;
+Warnings:
+Warning	1292	Truncated incorrect key_cache_file_hash_size value: '1'
+SELECT @@global.key_cache_file_hash_size;
+@@global.key_cache_file_hash_size
+128
+SET @@global.key_cache_file_hash_size = FALSE;
+Warnings:
+Warning	1292	Truncated incorrect key_cache_file_hash_size value: '0'
+SELECT @@global.key_cache_file_hash_size;
+@@global.key_cache_file_hash_size
+128
+'#---------------------FN_DYNVARS_056_08----------------------#'
+SET @@global.key_cache_file_hash_size = 150;
+SELECT @@key_cache_file_hash_size = @@global.key_cache_file_hash_size;
+@@key_cache_file_hash_size = @@global.key_cache_file_hash_size
+1
+'#---------------------FN_DYNVARS_056_09----------------------#'
+SET key_cache_file_hash_size = 8000;
+ERROR HY000: Variable 'key_cache_file_hash_size' is a GLOBAL variable and should be set with SET GLOBAL
+SELECT @@key_cache_file_hash_size;
+@@key_cache_file_hash_size
+150
+SET local.key_cache_file_hash_size = 10;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key_cache_file_hash_size = 10' at line 1
+SELECT local.key_cache_file_hash_size;
+ERROR 42S02: Unknown table 'local' in field list
+SET global.key_cache_file_hash_size = 10;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key_cache_file_hash_size = 10' at line 1
+SELECT global.key_cache_file_hash_size;
+ERROR 42S02: Unknown table 'global' in field list
+SELECT key_cache_file_hash_size = @@session.key_cache_file_hash_size;
+ERROR 42S22: Unknown column 'key_cache_file_hash_size' in 'field list'
+SET @@global.key_cache_file_hash_size = @start_value;
+SELECT @@global.key_cache_file_hash_size;
+@@global.key_cache_file_hash_size
+512
diff --git a/mysql-test/suite/sys_vars/t/aria_pagecache_file_hash_size_basic.test b/mysql-test/suite/sys_vars/t/aria_pagecache_file_hash_size_basic.test
new file mode 100644
index 00000000000..8bedb498e2c
--- /dev/null
+++ b/mysql-test/suite/sys_vars/t/aria_pagecache_file_hash_size_basic.test
@@ -0,0 +1,22 @@
+# ulong readonly
+
+--source include/have_maria.inc
+#
+# show the global and session values;
+#
+select @@global.aria_pagecache_file_hash_size;
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
+select @@session.aria_pagecache_file_hash_size;
+show global variables like 'aria_pagecache_file_hash_size';
+show session variables like 'aria_pagecache_file_hash_size';
+select * from information_schema.global_variables where variable_name='aria_pagecache_file_hash_size';
+select * from information_schema.session_variables where variable_name='aria_pagecache_file_hash_size';
+
+#
+# show that it's read-only
+#
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
+set global aria_pagecache_file_hash_size=200;
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
+set session aria_pagecache_file_hash_size=200;
+
diff --git a/mysql-test/suite/sys_vars/t/key_cache_file_hash_size_basic.test b/mysql-test/suite/sys_vars/t/key_cache_file_hash_size_basic.test
new file mode 100644
index 00000000000..deebe708d3d
--- /dev/null
+++ b/mysql-test/suite/sys_vars/t/key_cache_file_hash_size_basic.test
@@ -0,0 +1,168 @@
+################# mysql-test\t\key_cache_file_hash_size.test ##################
+#                                                                             #
+# Variable Name: key_cache_file_hash_size                                      #
+# Scope: GLOBAL                                                               #
+# Access Type: Dynamic                                                        #
+# Data Type: numeric                                                          #
+# Default Value: 300                                                          #
+# Range: 100-4294967295                                                       #
+#                                                                             #
+#                                                                             #
+# Creation Date: 2008-02-07                                                   #
+# Author:  Salman                                                             #
+#                                                                             #
+# Description: Test Cases of Dynamic System Variable key_cache_file_hash_size  #
+#              that checks the behavior of this variable in the following ways#
+#              * Default Value                                                #
+#              * Valid & Invalid values                                       #
+#              * Scope & Access method                                        #
+#              * Data Integrity                                               #
+#                                                                             #
+# Reference: http://dev.mysql.com/doc/refman/5.1/en/                          #
+#  server-system-variables.html                                               #
+#                                                                             #
+###############################################################################
+
+--source include/load_sysvars.inc
+
+######################################################################## 
+#            START OF key_cache_file_hash_size TESTS                    #
+######################################################################## 
+
+
+############################################################################# 
+#  Saving initial value of key_cache_file_hash_size in a temporary variable  #
+############################################################################# 
+
+SET @start_value = @@global.key_cache_file_hash_size;
+SELECT @start_value;
+
+
+--echo '#--------------------FN_DYNVARS_056_01------------------------#'
+################################################################################ 
+#              Display the DEFAULT value of key_cache_file_hash_size            #
+################################################################################ 
+
+SET @@global.key_cache_file_hash_size = DEFAULT;
+SELECT @@global.key_cache_file_hash_size;
+
+
+--echo '#---------------------FN_DYNVARS_056_02-------------------------#'
+############################################### 
+#     Verify default value of variable        #
+############################################### 
+
+SET @@global.key_cache_file_hash_size = @start_value;
+SELECT @@global.key_cache_file_hash_size = 300;
+
+
+--echo '#--------------------FN_DYNVARS_056_03------------------------#'
+############################################################################### 
+#        Change the value of key_cache_file_hash_size to a valid value         #
+############################################################################### 
+
+SET @@global.key_cache_file_hash_size = 128;
+SET @@global.key_cache_file_hash_size = 16384;
+SELECT @@global.key_cache_file_hash_size;
+
+--echo '#--------------------FN_DYNVARS_056_04-------------------------#'
+########################################################################### 
+#         Change the value of key_cache_file_hash_size to invalid value    #
+########################################################################### 
+
+SET @@global.key_cache_file_hash_size = -1;
+SELECT @@global.key_cache_file_hash_size;
+SET @@global.key_cache_file_hash_size = 42949672951;
+SELECT @@global.key_cache_file_hash_size;
+--Error ER_WRONG_TYPE_FOR_VAR
+SET @@global.key_cache_file_hash_size = 10000.01;
+SELECT @@global.key_cache_file_hash_size;
+SET @@global.key_cache_file_hash_size = -1024;
+SELECT @@global.key_cache_file_hash_size;
+SET @@global.key_cache_file_hash_size = 99;
+SELECT @@global.key_cache_file_hash_size;
+
+--Error ER_WRONG_TYPE_FOR_VAR
+SET @@global.key_cache_file_hash_size = ON;
+SELECT @@global.key_cache_file_hash_size;
+--Error ER_WRONG_TYPE_FOR_VAR
+SET @@global.key_cache_file_hash_size = 'test';
+SELECT @@global.key_cache_file_hash_size;
+
+
+--echo '#-------------------FN_DYNVARS_056_05----------------------------#'
+########################################################################### 
+#       Test if accessing session key_cache_file_hash_size gives error     #
+########################################################################### 
+
+--Error ER_GLOBAL_VARIABLE
+SET @@session.key_cache_file_hash_size = 0;
+--Error ER_INCORRECT_GLOBAL_LOCAL_VAR
+SELECT @@session.key_cache_file_hash_size;
+
+
+--echo '#----------------------FN_DYNVARS_056_06------------------------#'
+############################################################################## 
+# Check if the value in GLOBAL & SESSION Tables matches values in variable   #
+##############################################################################
+
+SELECT @@global.key_cache_file_hash_size = VARIABLE_VALUE 
+FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES 
+WHERE VARIABLE_NAME='key_cache_file_hash_size';
+
+SELECT @@key_cache_file_hash_size = VARIABLE_VALUE 
+FROM INFORMATION_SCHEMA.SESSION_VARIABLES 
+WHERE VARIABLE_NAME='key_cache_file_hash_size';
+
+
+--echo '#---------------------FN_DYNVARS_056_07----------------------#'
+################################################################### 
+#      Check if TRUE and FALSE values can be used on variable     #
+################################################################### 
+
+SET @@global.key_cache_file_hash_size = TRUE;
+SELECT @@global.key_cache_file_hash_size;
+SET @@global.key_cache_file_hash_size = FALSE;
+SELECT @@global.key_cache_file_hash_size;
+
+
+--echo '#---------------------FN_DYNVARS_056_08----------------------#'
+########################################################################################################
+#    Check if accessing variable with SESSION,LOCAL and without SCOPE points to same session variable  #
+########################################################################################################
+
+SET @@global.key_cache_file_hash_size = 150;
+SELECT @@key_cache_file_hash_size = @@global.key_cache_file_hash_size;
+
+
+--echo '#---------------------FN_DYNVARS_056_09----------------------#'
+########################################################################## ####### 
+#   Check if key_cache_file_hash_size can be accessed with and without @@ sign    #
+##################################################################################
+
+--Error ER_GLOBAL_VARIABLE
+SET key_cache_file_hash_size = 8000;
+SELECT @@key_cache_file_hash_size;
+--Error ER_PARSE_ERROR
+SET local.key_cache_file_hash_size = 10;
+--Error ER_UNKNOWN_TABLE
+SELECT local.key_cache_file_hash_size;
+--Error ER_PARSE_ERROR
+SET global.key_cache_file_hash_size = 10;
+--Error ER_UNKNOWN_TABLE
+SELECT global.key_cache_file_hash_size;
+--Error ER_BAD_FIELD_ERROR
+SELECT key_cache_file_hash_size = @@session.key_cache_file_hash_size;
+
+
+##############################  
+#   Restore initial value    #
+##############################
+
+SET @@global.key_cache_file_hash_size = @start_value;
+SELECT @@global.key_cache_file_hash_size;
+
+
+######################################################################## 
+#              END OF key_cache_file_hash_size TESTS                    #
+########################################################################