diff options
Diffstat (limited to 'mysql-test')
19 files changed, 103 insertions, 179 deletions
diff --git a/mysql-test/suite/encryption/t/innodb_encryption_discard_import.opt b/mysql-test/suite/encryption/t/innodb_encryption_discard_import.opt index bcff011eb82..9fe990f7260 100644 --- a/mysql-test/suite/encryption/t/innodb_encryption_discard_import.opt +++ b/mysql-test/suite/encryption/t/innodb_encryption_discard_import.opt @@ -3,6 +3,5 @@ --innodb-encryption-rotate-key-age=15 --innodb-encryption-threads=4 --innodb-tablespaces-encryption +--innodb-max-dirty-pages-pct_lwm=0 --innodb-max-dirty-pages-pct=0.001 - - diff --git a/mysql-test/suite/innodb/r/ibuf_not_empty.result b/mysql-test/suite/innodb/r/ibuf_not_empty.result index 3382c74174e..d1b8203b063 100644 --- a/mysql-test/suite/innodb/r/ibuf_not_empty.result +++ b/mysql-test/suite/innodb/r/ibuf_not_empty.result @@ -1,4 +1,3 @@ -SET GLOBAL innodb_purge_rseg_truncate_frequency=1; CREATE TABLE t1( a INT AUTO_INCREMENT PRIMARY KEY, b CHAR(1), @@ -6,26 +5,11 @@ c INT, INDEX(b)) ENGINE=InnoDB STATS_PERSISTENT=0; SET GLOBAL innodb_change_buffering_debug = 1; -BEGIN; -INSERT INTO t1 VALUES(0,'x',1); -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -COMMIT; -InnoDB 0 transactions not purged +INSERT INTO t1 SELECT 0,'x',1 FROM seq_1_to_1024; # restart: --innodb-force-recovery=6 --innodb-change-buffer-dump check table t1; Table Op Msg_type Msg_text -test.t1 check Warning InnoDB: Index 'b' contains #### entries, should be 4096. +test.t1 check Warning InnoDB: Index 'b' contains 990 entries, should be 1024. test.t1 check error Corrupt # restart SET GLOBAL innodb_fast_shutdown=0; diff --git a/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result b/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result index d795b516d5e..678c8c67be5 100644 --- a/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result +++ b/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result @@ -50,5 +50,5 @@ Table Op Msg_type Msg_text test.t1 check status OK SHOW ENGINE INNODB STATUS; Type Name Status -InnoDB insert 79, delete mark 1 +InnoDB DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result b/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result index 9ab72408274..6a597a919e1 100644 --- a/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result +++ b/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result @@ -90,14 +90,10 @@ buffer_flush_neighbor_pages buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL N buffer_flush_n_to_flush_requested buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of pages requested for flushing. buffer_flush_n_to_flush_by_age buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of pages target by LSN Age for flushing. buffer_flush_adaptive_avg_time_slot buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Avg time (ms) spent for adaptive flushing recently per slot. -buffer_LRU_batch_flush_avg_time_slot buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Avg time (ms) spent for LRU batch flushing recently per slot. buffer_flush_adaptive_avg_time_thread buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Avg time (ms) spent for adaptive flushing recently per thread. -buffer_LRU_batch_flush_avg_time_thread buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Avg time (ms) spent for LRU batch flushing recently per thread. buffer_flush_adaptive_avg_time_est buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Estimated time (ms) spent for adaptive flushing recently. -buffer_LRU_batch_flush_avg_time_est buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Estimated time (ms) spent for LRU batch flushing recently. buffer_flush_avg_time buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Avg time (ms) spent for flushing recently. buffer_flush_adaptive_avg_pass buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of adaptive flushes passed during the recent Avg period. -buffer_LRU_batch_flush_avg_pass buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of LRU batch flushes passed during the recent Avg period. buffer_flush_avg_pass buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of flushes passed during the recent Avg period. buffer_LRU_get_free_loops buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Total loops in LRU get free. buffer_LRU_get_free_waits buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Total sleep waits in LRU get free. @@ -124,9 +120,6 @@ buffer_LRU_batch_flush_pages buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL buffer_LRU_batch_evict_total_pages buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 set_owner Total pages evicted as part of LRU batches buffer_LRU_batches_evict buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 set_member Number of LRU batches buffer_LRU_batch_evict_pages buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 set_member Pages queued as an LRU batch -buffer_LRU_single_flush_scanned buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 set_owner Total pages scanned as part of single page LRU flush -buffer_LRU_single_flush_num_scan buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 set_member Number of times single page LRU flush is called -buffer_LRU_single_flush_scanned_per_call buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 set_member Page scanned per single LRU flush buffer_LRU_single_flush_failure_count Buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of times attempt to flush a single page from LRU failed buffer_LRU_get_free_search Buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of searches performed for a clean page buffer_LRU_search_scanned buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 set_owner Total pages scanned as part of LRU search diff --git a/mysql-test/suite/innodb/r/monitor.result b/mysql-test/suite/innodb/r/monitor.result index 0c0168fb266..4aeab1a8402 100644 --- a/mysql-test/suite/innodb/r/monitor.result +++ b/mysql-test/suite/innodb/r/monitor.result @@ -56,14 +56,10 @@ buffer_flush_neighbor_pages disabled buffer_flush_n_to_flush_requested disabled buffer_flush_n_to_flush_by_age disabled buffer_flush_adaptive_avg_time_slot disabled -buffer_LRU_batch_flush_avg_time_slot disabled buffer_flush_adaptive_avg_time_thread disabled -buffer_LRU_batch_flush_avg_time_thread disabled buffer_flush_adaptive_avg_time_est disabled -buffer_LRU_batch_flush_avg_time_est disabled buffer_flush_avg_time disabled buffer_flush_adaptive_avg_pass disabled -buffer_LRU_batch_flush_avg_pass disabled buffer_flush_avg_pass disabled buffer_LRU_get_free_loops disabled buffer_LRU_get_free_waits disabled @@ -90,9 +86,6 @@ buffer_LRU_batch_flush_pages disabled buffer_LRU_batch_evict_total_pages disabled buffer_LRU_batches_evict disabled buffer_LRU_batch_evict_pages disabled -buffer_LRU_single_flush_scanned disabled -buffer_LRU_single_flush_num_scan disabled -buffer_LRU_single_flush_scanned_per_call disabled buffer_LRU_single_flush_failure_count disabled buffer_LRU_get_free_search disabled buffer_LRU_search_scanned disabled diff --git a/mysql-test/suite/innodb/r/purge_secondary.result b/mysql-test/suite/innodb/r/purge_secondary.result index 7c2b4151e76..a583e46418d 100644 --- a/mysql-test/suite/innodb/r/purge_secondary.result +++ b/mysql-test/suite/innodb/r/purge_secondary.result @@ -148,10 +148,6 @@ SELECT (variable_value > 0) FROM information_schema.global_status WHERE LOWER(variable_name) LIKE 'INNODB_BUFFER_POOL_PAGES_FLUSHED'; (variable_value > 0) 1 -SELECT NAME, SUBSYSTEM FROM INFORMATION_SCHEMA.INNODB_METRICS -WHERE NAME="buffer_LRU_batch_evict_total_pages" AND COUNT > 0; -NAME SUBSYSTEM -buffer_LRU_batch_evict_total_pages buffer # Note: The OTHER_INDEX_SIZE does not cover any SPATIAL INDEX. # To test that all indexes were emptied, replace DROP TABLE # with the following, and examine the root pages in t1.ibd: diff --git a/mysql-test/suite/innodb/t/ibuf_not_empty.combinations b/mysql-test/suite/innodb/t/ibuf_not_empty.combinations index 729380593f3..c4b45dcca32 100644 --- a/mysql-test/suite/innodb/t/ibuf_not_empty.combinations +++ b/mysql-test/suite/innodb/t/ibuf_not_empty.combinations @@ -1,5 +1,9 @@ [strict_crc32] --innodb-checksum-algorithm=strict_crc32 +--innodb-page-size=4k +--innodb-force-recovery=2 [strict_full_crc32] --innodb-checksum-algorithm=strict_full_crc32 +--innodb-page-size=4k +--innodb-force-recovery=2 diff --git a/mysql-test/suite/innodb/t/ibuf_not_empty.test b/mysql-test/suite/innodb/t/ibuf_not_empty.test index a3f4ad9ac5c..3b254177497 100644 --- a/mysql-test/suite/innodb/t/ibuf_not_empty.test +++ b/mysql-test/suite/innodb/t/ibuf_not_empty.test @@ -3,10 +3,8 @@ --source include/have_debug.inc # Embedded server tests do not support restarting --source include/not_embedded.inc -# The test is not big enough to use change buffering with larger page size. ---source include/have_innodb_max_16k.inc +--source include/have_sequence.inc -SET GLOBAL innodb_purge_rseg_truncate_frequency=1; --disable_query_log call mtr.add_suppression("InnoDB: Failed to find tablespace for table `test`\\.`t1` in the cache\\. Attempting to load the tablespace with space id"); call mtr.add_suppression("InnoDB: Allocated tablespace ID \\d+ for test.t1, old maximum was"); @@ -30,27 +28,10 @@ SET GLOBAL innodb_change_buffering_debug = 1; # Create enough rows for the table, so that the change buffer will be # used for modifying the secondary index page. There must be multiple # index pages, because changes to the root page are never buffered. -BEGIN; -INSERT INTO t1 VALUES(0,'x',1); -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -INSERT INTO t1 SELECT 0,b,c FROM t1; -COMMIT; +INSERT INTO t1 SELECT 0,'x',1 FROM seq_1_to_1024; let MYSQLD_DATADIR=`select @@datadir`; let PAGE_SIZE=`select @@innodb_page_size`; -# Ensure that purge will not access the truncated .ibd file ---source include/wait_all_purged.inc - --source include/shutdown_mysqld.inc # Corrupt the change buffer bitmap, to claim that pages are clean @@ -87,7 +68,6 @@ EOF --let $restart_parameters= --innodb-force-recovery=6 --innodb-change-buffer-dump --source include/start_mysqld.inc ---replace_regex /contains \d+ entries/contains #### entries/ check table t1; --source include/shutdown_mysqld.inc diff --git a/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test b/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test index 79d9cc814a0..a12ca43cec1 100644 --- a/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test +++ b/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test @@ -76,6 +76,6 @@ SET GLOBAL innodb_fast_shutdown=0; --let $restart_parameters= --source include/restart_mysqld.inc CHECK TABLE t1; -replace_regex /.*operations:.* (insert.*), delete \d.*discarded .*/\1/; +replace_regex /.*operations:.* insert [1-9][0-9]*, delete mark [1-9][0-9]*, delete \d.*discarded .*//; SHOW ENGINE INNODB STATUS; DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/purge_secondary.test b/mysql-test/suite/innodb/t/purge_secondary.test index 34b4ce06f5f..f2c85ce10e7 100644 --- a/mysql-test/suite/innodb/t/purge_secondary.test +++ b/mysql-test/suite/innodb/t/purge_secondary.test @@ -131,9 +131,6 @@ ALTER TABLE t1 FORCE, ALGORITHM=INPLACE; SELECT (variable_value > 0) FROM information_schema.global_status WHERE LOWER(variable_name) LIKE 'INNODB_BUFFER_POOL_PAGES_FLUSHED'; -SELECT NAME, SUBSYSTEM FROM INFORMATION_SCHEMA.INNODB_METRICS -WHERE NAME="buffer_LRU_batch_evict_total_pages" AND COUNT > 0; - --echo # Note: The OTHER_INDEX_SIZE does not cover any SPATIAL INDEX. --echo # To test that all indexes were emptied, replace DROP TABLE --echo # with the following, and examine the root pages in t1.ibd: diff --git a/mysql-test/suite/perfschema/t/show_sanity.test b/mysql-test/suite/perfschema/t/show_sanity.test index 8f73c38eab8..61161ea5df6 100644 --- a/mysql-test/suite/perfschema/t/show_sanity.test +++ b/mysql-test/suite/perfschema/t/show_sanity.test @@ -422,7 +422,6 @@ insert into test.sanity values ("JUNK: GLOBAL-ONLY", "I_S.SESSION_VARIABLES", "INNODB_DISABLE_RESIZE_BUFFER_POOL_DEBUG"), ("JUNK: GLOBAL-ONLY", "I_S.SESSION_VARIABLES", "INNODB_DISABLE_SORT_FILE_CACHE"), ("JUNK: GLOBAL-ONLY", "I_S.SESSION_VARIABLES", "INNODB_DOUBLEWRITE"), - ("JUNK: GLOBAL-ONLY", "I_S.SESSION_VARIABLES", "INNODB_DOUBLEWRITE_BATCH_SIZE"), ("JUNK: GLOBAL-ONLY", "I_S.SESSION_VARIABLES", "INNODB_FAST_SHUTDOWN"), ("JUNK: GLOBAL-ONLY", "I_S.SESSION_VARIABLES", "INNODB_FILE_PER_TABLE"), ("JUNK: GLOBAL-ONLY", "I_S.SESSION_VARIABLES", "INNODB_FILL_FACTOR"), diff --git a/mysql-test/suite/sys_vars/r/innodb_doublewrite_batch_size_basic.result b/mysql-test/suite/sys_vars/r/innodb_doublewrite_batch_size_basic.result deleted file mode 100644 index cec90ea8950..00000000000 --- a/mysql-test/suite/sys_vars/r/innodb_doublewrite_batch_size_basic.result +++ /dev/null @@ -1,24 +0,0 @@ -select @@global.innodb_doublewrite_batch_size between 1 and 127; -@@global.innodb_doublewrite_batch_size between 1 and 127 -1 -select @@global.innodb_doublewrite_batch_size; -@@global.innodb_doublewrite_batch_size -120 -select @@session.innodb_doublewrite_batch_size; -ERROR HY000: Variable 'innodb_doublewrite_batch_size' is a GLOBAL variable -show global variables like 'innodb_doublewrite_batch_size'; -Variable_name Value -innodb_doublewrite_batch_size 120 -show session variables like 'innodb_doublewrite_batch_size'; -Variable_name Value -innodb_doublewrite_batch_size 120 -select * from information_schema.global_variables where variable_name='innodb_doublewrite_batch_size'; -VARIABLE_NAME VARIABLE_VALUE -INNODB_DOUBLEWRITE_BATCH_SIZE 120 -select * from information_schema.session_variables where variable_name='innodb_doublewrite_batch_size'; -VARIABLE_NAME VARIABLE_VALUE -INNODB_DOUBLEWRITE_BATCH_SIZE 120 -set global innodb_doublewrite_batch_size=1; -ERROR HY000: Variable 'innodb_doublewrite_batch_size' is a read only variable -set @@session.innodb_doublewrite_batch_size='some'; -ERROR HY000: Variable 'innodb_doublewrite_batch_size' is a read only variable diff --git a/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_basic.result b/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_basic.result index 20b619972dd..ad0ffe9855a 100644 --- a/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_basic.result @@ -7,7 +7,7 @@ SELECT @global_start_value; SET @global_start_max_dirty_lwm_value = @@global.innodb_max_dirty_pages_pct_lwm; SELECT @global_start_max_dirty_lwm_value; @global_start_max_dirty_lwm_value -0 +75 SET @@global.innodb_max_dirty_pages_pct_lwm = 0; SELECT @@global.innodb_max_dirty_pages_pct_lwm; @@global.innodb_max_dirty_pages_pct_lwm @@ -17,13 +17,13 @@ SET @@global.innodb_max_dirty_pages_pct = 0; SET @@global.innodb_max_dirty_pages_pct = DEFAULT; SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct -75.000000 +90.000000 '#---------------------FN_DYNVARS_046_02-------------------------#' SET innodb_max_dirty_pages_pct = 1; ERROR HY000: Variable 'innodb_max_dirty_pages_pct' is a GLOBAL variable and should be set with SET GLOBAL SELECT @@innodb_max_dirty_pages_pct; @@innodb_max_dirty_pages_pct -75.000000 +90.000000 SELECT local.innodb_max_dirty_pages_pct; ERROR 42S02: Unknown table 'local' in field list SET global innodb_max_dirty_pages_pct = 0; @@ -171,5 +171,5 @@ SELECT @@global.innodb_max_dirty_pages_pct; SET @@global.innodb_max_dirty_pages_pct_lwm = @global_start_max_dirty_lwm_value; SELECT @@global.innodb_max_dirty_pages_pct_lwm; @@global.innodb_max_dirty_pages_pct_lwm -0.000000 +75.000000 SET @@global.innodb_max_dirty_pages_pct=@save_innodb_max_dirty_pages_pct; diff --git a/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_func.result b/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_func.result index 8b68f182789..43cdf17ee27 100644 --- a/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_func.result +++ b/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_func.result @@ -1,26 +1,28 @@ +SET @innodb_max_dirty_pages_pct_lwm = @@global.innodb_max_dirty_pages_pct_lwm; SET @innodb_max_dirty_pages_pct = @@global.innodb_max_dirty_pages_pct; '#--------------------FN_DYNVARS_044_02-------------------------#' +SET @@global.innodb_max_dirty_pages_pct_lwm = 0; SET @@global.innodb_max_dirty_pages_pct = 80; -'connect (con1,localhost,root,,,,)' +SET @@global.innodb_max_dirty_pages_pct_lwm = 80; connect con1,localhost,root,,,,; -'connection con1' connection con1; SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct 80.000000 SET @@global.innodb_max_dirty_pages_pct = 70; -'connect (con2,localhost,root,,,,)' +Warnings: +Warning 1210 innodb_max_dirty_pages_pct cannot be set lower than innodb_max_dirty_pages_pct_lwm. +Warning 1210 Lowering innodb_max_dirty_page_pct_lwm to 70.000000 +SELECT @@global.innodb_max_dirty_pages_pct_lwm; +@@global.innodb_max_dirty_pages_pct_lwm +70.000000 connect con2,localhost,root,,,,; -'connection con2' connection con2; SELECT @@global.innodb_max_dirty_pages_pct; @@global.innodb_max_dirty_pages_pct 70.000000 -'connection default' connection default; -'disconnect con2' disconnect con2; -'disconnect con1' disconnect con1; SET @@global.innodb_max_dirty_pages_pct = @innodb_max_dirty_pages_pct; '#--------------------FN_DYNVARS_044_02-------------------------#' @@ -85,6 +87,22 @@ b CHAR(200) ) ENGINE = INNODB; '---Check when innodb_max_dirty_pages_pct is 10---' SET @@global.innodb_max_dirty_pages_pct = 10; +Warnings: +Warning 1210 innodb_max_dirty_pages_pct cannot be set lower than innodb_max_dirty_pages_pct_lwm. +Warning 1210 Lowering innodb_max_dirty_page_pct_lwm to 10.000000 +SELECT @@global.innodb_max_dirty_pages_pct_lwm; +@@global.innodb_max_dirty_pages_pct_lwm +10.000000 +SET GLOBAL innodb_max_dirty_pages_pct_lwm = 15; +Warnings: +Warning 1210 innodb_max_dirty_pages_pct_lwm cannot be set higher than innodb_max_dirty_pages_pct. +Warning 1210 Setting innodb_max_dirty_page_pct_lwm to 10.000000 +SELECT @@global.innodb_max_dirty_pages_pct_lwm; +@@global.innodb_max_dirty_pages_pct_lwm +10.000000 +SELECT @@global.innodb_max_dirty_pages_pct; +@@global.innodb_max_dirty_pages_pct +10.000000 FLUSH STATUS; CALL add_until(10); FLUSH TABLES; @@ -98,4 +116,6 @@ DROP PROCEDURE add_until; DROP PROCEDURE check_pct; DROP FUNCTION dirty_pct; DROP TABLE t1; +SET GLOBAL innodb_max_dirty_pages_pct_lwm = 0; SET @@global.innodb_max_dirty_pages_pct = @innodb_max_dirty_pages_pct; +SET @@global.innodb_max_dirty_pages_pct_lwm = @innodb_max_dirty_pages_pct_lwm; diff --git a/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_lwm_basic.result b/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_lwm_basic.result index 641386d5f23..313bdf28e82 100644 --- a/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_lwm_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_lwm_basic.result @@ -3,7 +3,7 @@ set @@global.innodb_max_dirty_pages_pct=75; SET @pct_lwm_start_value = @@global.innodb_max_dirty_pages_pct_lwm; SELECT @pct_lwm_start_value; @pct_lwm_start_value -0 +75 SET @pct_start_value = @@global.innodb_max_dirty_pages_pct; SELECT @pct_start_value; @pct_start_value @@ -13,13 +13,13 @@ SET @@global.innodb_max_dirty_pages_pct_lwm = 0; SET @@global.innodb_max_dirty_pages_pct_lwm = DEFAULT; SELECT @@global.innodb_max_dirty_pages_pct_lwm; @@global.innodb_max_dirty_pages_pct_lwm -0.000000 +75.000000 '#---------------------FN_DYNVARS_046_02-------------------------#' SET innodb_max_dirty_pages_pct_lwm = 1; ERROR HY000: Variable 'innodb_max_dirty_pages_pct_lwm' is a GLOBAL variable and should be set with SET GLOBAL SELECT @@innodb_max_dirty_pages_pct_lwm; @@innodb_max_dirty_pages_pct_lwm -0.000000 +75.000000 SELECT local.innodb_max_dirty_pages_pct_lwm; ERROR 42S02: Unknown table 'local' in field list SET global innodb_max_dirty_pages_pct_lwm = 0; @@ -130,5 +130,5 @@ SELECT @@global.innodb_max_dirty_pages_pct; SET @@global.innodb_max_dirty_pages_pct_lwm = @pct_lwm_start_value; SELECT @@global.innodb_max_dirty_pages_pct_lwm; @@global.innodb_max_dirty_pages_pct_lwm -0.000000 +75.000000 SET @@global.innodb_max_dirty_pages_pct=@save_innodb_max_dirty_pages_pct; diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff b/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff index 2f39a472b99..0a558e77923 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff +++ b/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff @@ -85,16 +85,7 @@ VARIABLE_COMMENT Percentage of empty space on a data page that can be reserved to make the page compressible. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 75 -@@ -661,7 +661,7 @@ - SESSION_VALUE NULL - DEFAULT_VALUE 120 - VARIABLE_SCOPE GLOBAL --VARIABLE_TYPE BIGINT UNSIGNED -+VARIABLE_TYPE INT UNSIGNED - VARIABLE_COMMENT Number of pages reserved in doublewrite buffer for batch flushing - NUMERIC_MIN_VALUE 1 - NUMERIC_MAX_VALUE 127 -@@ -757,7 +757,7 @@ +@@ -745,7 +745,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 600 VARIABLE_SCOPE GLOBAL @@ -103,7 +94,7 @@ VARIABLE_COMMENT Maximum number of seconds that semaphore times out in InnoDB. NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 4294967295 -@@ -805,7 +805,7 @@ +@@ -793,7 +793,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 0 VARIABLE_SCOPE GLOBAL @@ -112,7 +103,7 @@ VARIABLE_COMMENT Make the first page of the given tablespace dirty. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -817,7 +817,7 @@ +@@ -805,7 +805,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 30 VARIABLE_SCOPE GLOBAL @@ -121,7 +112,7 @@ VARIABLE_COMMENT Number of iterations over which the background flushing is averaged. NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 1000 -@@ -841,7 +841,7 @@ +@@ -829,7 +829,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 1 VARIABLE_SCOPE GLOBAL @@ -130,7 +121,7 @@ VARIABLE_COMMENT Controls the durability/speed trade-off for commits. Set to 0 (write and flush redo log to disk only once per second), 1 (flush to disk at each commit), 2 (write to log at commit but flush to disk only once per second) or 3 (flush to disk at prepare and at commit, slower and usually redundant). 1 and 3 guarantees that after a crash, committed transactions will not be lost and will be consistent with the binlog and other transactional engines. 2 can get inconsistent and lose transactions if there is a power failure or kernel crash but not if mysqld crashes. 0 has no guarantees in case of crash. 0 and 2 can be faster than 1 or 3. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 3 -@@ -865,7 +865,7 @@ +@@ -853,7 +853,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 1 VARIABLE_SCOPE GLOBAL @@ -139,7 +130,7 @@ VARIABLE_COMMENT Set to 0 (don't flush neighbors from buffer pool), 1 (flush contiguous neighbors from buffer pool) or 2 (flush neighbors from buffer pool), when flushing a block NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 2 -@@ -913,7 +913,7 @@ +@@ -901,7 +901,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 0 VARIABLE_SCOPE GLOBAL @@ -148,7 +139,7 @@ VARIABLE_COMMENT Helps to save your data in case the disk image of the database becomes corrupt. Value 5 can return bogus data, and 6 can permanently corrupt data. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 6 -@@ -937,7 +937,7 @@ +@@ -925,7 +925,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 8000000 VARIABLE_SCOPE GLOBAL @@ -157,7 +148,7 @@ VARIABLE_COMMENT InnoDB Fulltext search cache size in bytes NUMERIC_MIN_VALUE 1600000 NUMERIC_MAX_VALUE 80000000 -@@ -973,7 +973,7 @@ +@@ -961,7 +961,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 84 VARIABLE_SCOPE GLOBAL @@ -166,7 +157,7 @@ VARIABLE_COMMENT InnoDB Fulltext search maximum token size in characters NUMERIC_MIN_VALUE 10 NUMERIC_MAX_VALUE 84 -@@ -985,7 +985,7 @@ +@@ -973,7 +973,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 3 VARIABLE_SCOPE GLOBAL @@ -175,7 +166,7 @@ VARIABLE_COMMENT InnoDB Fulltext search minimum token size in characters NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 16 -@@ -997,7 +997,7 @@ +@@ -985,7 +985,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 2000 VARIABLE_SCOPE GLOBAL @@ -184,7 +175,7 @@ VARIABLE_COMMENT InnoDB Fulltext search number of words to optimize for each optimize table call NUMERIC_MIN_VALUE 1000 NUMERIC_MAX_VALUE 10000 -@@ -1009,10 +1009,10 @@ +@@ -997,10 +997,10 @@ SESSION_VALUE NULL DEFAULT_VALUE 2000000000 VARIABLE_SCOPE GLOBAL @@ -197,7 +188,7 @@ NUMERIC_BLOCK_SIZE 0 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -1033,7 +1033,7 @@ +@@ -1021,7 +1021,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 2 VARIABLE_SCOPE GLOBAL @@ -206,7 +197,7 @@ VARIABLE_COMMENT InnoDB Fulltext search parallel sort degree, will round up to nearest power of 2 number NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 16 -@@ -1045,7 +1045,7 @@ +@@ -1033,7 +1033,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 640000000 VARIABLE_SCOPE GLOBAL @@ -215,7 +206,7 @@ VARIABLE_COMMENT Total memory allocated for InnoDB Fulltext Search cache NUMERIC_MIN_VALUE 32000000 NUMERIC_MAX_VALUE 1600000000 -@@ -1069,7 +1069,7 @@ +@@ -1057,7 +1057,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 100 VARIABLE_SCOPE GLOBAL @@ -224,7 +215,7 @@ VARIABLE_COMMENT Up to what percentage of dirty pages should be flushed when innodb finds it has spare resources to do so. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 100 -@@ -1105,22 +1105,22 @@ +@@ -1093,22 +1093,22 @@ SESSION_VALUE NULL DEFAULT_VALUE 200 VARIABLE_SCOPE GLOBAL @@ -252,7 +243,7 @@ NUMERIC_BLOCK_SIZE 0 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -1165,7 +1165,7 @@ +@@ -1153,7 +1153,7 @@ SESSION_VALUE 50 DEFAULT_VALUE 50 VARIABLE_SCOPE SESSION @@ -261,7 +252,7 @@ VARIABLE_COMMENT Timeout in seconds an InnoDB transaction may wait for a lock before being rolled back. Values above 100000000 disable the timeout. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 1073741824 -@@ -1177,10 +1177,10 @@ +@@ -1165,10 +1165,10 @@ SESSION_VALUE NULL DEFAULT_VALUE 16777216 VARIABLE_SCOPE GLOBAL @@ -274,7 +265,7 @@ NUMERIC_BLOCK_SIZE 1024 ENUM_VALUE_LIST NULL READ_ONLY YES -@@ -1225,7 +1225,7 @@ +@@ -1213,7 +1213,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 1 VARIABLE_SCOPE GLOBAL @@ -283,7 +274,7 @@ VARIABLE_COMMENT Deprecated parameter with no effect. NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 100 -@@ -1273,7 +1273,7 @@ +@@ -1261,7 +1261,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 8192 VARIABLE_SCOPE GLOBAL @@ -292,6 +283,19 @@ VARIABLE_COMMENT Redo log write ahead unit size to avoid read-on-write, it should match the OS cache block IO size NUMERIC_MIN_VALUE 512 NUMERIC_MAX_VALUE 16384 +@@ -1273,10 +1273,10 @@ + SESSION_VALUE NULL + DEFAULT_VALUE 100 + VARIABLE_SCOPE GLOBAL +-VARIABLE_TYPE BIGINT UNSIGNED ++VARIABLE_TYPE INT UNSIGNED + VARIABLE_COMMENT How many pages to flush on LRU eviction + NUMERIC_MIN_VALUE 1 +-NUMERIC_MAX_VALUE 18446744073709551615 ++NUMERIC_MAX_VALUE 4294967295 + NUMERIC_BLOCK_SIZE 0 + ENUM_VALUE_LIST NULL + READ_ONLY NO @@ -1285,10 +1285,10 @@ SESSION_VALUE NULL DEFAULT_VALUE 1024 diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result index 5b532addaa8..767d31c033e 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result +++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result @@ -657,18 +657,6 @@ NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST OFF,ON READ_ONLY YES COMMAND_LINE_ARGUMENT NONE -VARIABLE_NAME INNODB_DOUBLEWRITE_BATCH_SIZE -SESSION_VALUE NULL -DEFAULT_VALUE 120 -VARIABLE_SCOPE GLOBAL -VARIABLE_TYPE BIGINT UNSIGNED -VARIABLE_COMMENT Number of pages reserved in doublewrite buffer for batch flushing -NUMERIC_MIN_VALUE 1 -NUMERIC_MAX_VALUE 127 -NUMERIC_BLOCK_SIZE 0 -ENUM_VALUE_LIST NULL -READ_ONLY YES -COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME INNODB_ENCRYPTION_ROTATE_KEY_AGE SESSION_VALUE NULL DEFAULT_VALUE 1 @@ -1281,9 +1269,21 @@ NUMERIC_BLOCK_SIZE 512 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED +VARIABLE_NAME INNODB_LRU_FLUSH_SIZE +SESSION_VALUE NULL +DEFAULT_VALUE 32 +VARIABLE_SCOPE GLOBAL +VARIABLE_TYPE BIGINT UNSIGNED +VARIABLE_COMMENT How many pages to flush on LRU eviction +NUMERIC_MIN_VALUE 1 +NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_BLOCK_SIZE 0 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME INNODB_LRU_SCAN_DEPTH SESSION_VALUE NULL -DEFAULT_VALUE 1024 +DEFAULT_VALUE 1536 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT How deep to scan LRU to keep it clean @@ -1307,7 +1307,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME INNODB_MAX_DIRTY_PAGES_PCT SESSION_VALUE NULL -DEFAULT_VALUE 75.000000 +DEFAULT_VALUE 90.000000 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE DOUBLE VARIABLE_COMMENT Percentage of dirty pages allowed in bufferpool. @@ -1319,7 +1319,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME INNODB_MAX_DIRTY_PAGES_PCT_LWM SESSION_VALUE NULL -DEFAULT_VALUE 0.000000 +DEFAULT_VALUE 75.000000 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE DOUBLE VARIABLE_COMMENT Percentage of dirty pages at which flushing kicks in. diff --git a/mysql-test/suite/sys_vars/t/innodb_doublewrite_batch_size_basic.test b/mysql-test/suite/sys_vars/t/innodb_doublewrite_batch_size_basic.test deleted file mode 100644 index 5e9104b5335..00000000000 --- a/mysql-test/suite/sys_vars/t/innodb_doublewrite_batch_size_basic.test +++ /dev/null @@ -1,24 +0,0 @@ ---source include/have_innodb.inc ---source include/have_debug.inc - -# -# exists as global only -# -select @@global.innodb_doublewrite_batch_size between 1 and 127; -select @@global.innodb_doublewrite_batch_size; ---error ER_INCORRECT_GLOBAL_LOCAL_VAR -select @@session.innodb_doublewrite_batch_size; -show global variables like 'innodb_doublewrite_batch_size'; -show session variables like 'innodb_doublewrite_batch_size'; ---disable_warnings -select * from information_schema.global_variables where variable_name='innodb_doublewrite_batch_size'; -select * from information_schema.session_variables where variable_name='innodb_doublewrite_batch_size'; ---enable_warnings - -# -# show that it's read-only -# ---error ER_INCORRECT_GLOBAL_LOCAL_VAR -set global innodb_doublewrite_batch_size=1; ---error ER_INCORRECT_GLOBAL_LOCAL_VAR -set @@session.innodb_doublewrite_batch_size='some'; diff --git a/mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_func.test b/mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_func.test index c7a9e567e69..0720aca65b9 100644 --- a/mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_func.test +++ b/mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_func.test @@ -25,6 +25,7 @@ --source include/have_innodb.inc # safe initial value +SET @innodb_max_dirty_pages_pct_lwm = @@global.innodb_max_dirty_pages_pct_lwm; SET @innodb_max_dirty_pages_pct = @@global.innodb_max_dirty_pages_pct; --echo '#--------------------FN_DYNVARS_044_02-------------------------#' @@ -32,23 +33,19 @@ SET @innodb_max_dirty_pages_pct = @@global.innodb_max_dirty_pages_pct; # Check if setting innodb_max_dirty_pages_pct is changed in new connection # ############################################################################ +SET @@global.innodb_max_dirty_pages_pct_lwm = 0; SET @@global.innodb_max_dirty_pages_pct = 80; ---echo 'connect (con1,localhost,root,,,,)' +SET @@global.innodb_max_dirty_pages_pct_lwm = 80; connect (con1,localhost,root,,,,); ---echo 'connection con1' connection con1; SELECT @@global.innodb_max_dirty_pages_pct; SET @@global.innodb_max_dirty_pages_pct = 70; ---echo 'connect (con2,localhost,root,,,,)' +SELECT @@global.innodb_max_dirty_pages_pct_lwm; connect (con2,localhost,root,,,,); ---echo 'connection con2' connection con2; SELECT @@global.innodb_max_dirty_pages_pct; ---echo 'connection default' connection default; ---echo 'disconnect con2' disconnect con2; ---echo 'disconnect con1' disconnect con1; # restore initial value SET @@global.innodb_max_dirty_pages_pct = @innodb_max_dirty_pages_pct; @@ -138,6 +135,10 @@ b CHAR(200) #========================================================== SET @@global.innodb_max_dirty_pages_pct = 10; +SELECT @@global.innodb_max_dirty_pages_pct_lwm; +SET GLOBAL innodb_max_dirty_pages_pct_lwm = 15; +SELECT @@global.innodb_max_dirty_pages_pct_lwm; +SELECT @@global.innodb_max_dirty_pages_pct; FLUSH STATUS; @@ -164,7 +165,9 @@ DROP FUNCTION dirty_pct; DROP TABLE t1; # restore initial value +SET GLOBAL innodb_max_dirty_pages_pct_lwm = 0; SET @@global.innodb_max_dirty_pages_pct = @innodb_max_dirty_pages_pct; +SET @@global.innodb_max_dirty_pages_pct_lwm = @innodb_max_dirty_pages_pct_lwm; ################################################################## # End of functionality Testing for innodb_max_dirty_pages_pct # |