Debian Codegraph @

mysql-test/suite/innodb/t/innodb_idle_flush_pct.test

raw
1 ################################################################################
2 # Test script to test working of innodb_idle_flush_pct.
3 ################################################################################
4
5 --source include/have_debug.inc
6
7 --echo #########
8 --echo # Setup #
9 --echo #########
10 CREATE TABLE t1 (c INT);
11 INSERT INTO t1 VALUES (1), (2), (3), (4);
12
13 SET @innodb_io_capacity_saved = @@global.innodb_io_capacity;
14 SET @innodb_idle_flush_pct_saved = @@global.innodb_idle_flush_pct;
15
16 # Set io capacity to 200
17 SET GLOBAL innodb_io_capacity = 200;
18 SHOW VARIABLES LIKE "%innodb_io_capacity%";
19
20 # Disable page cleaner
21 SET GLOBAL innodb_page_cleaner_disabled_debug = 1;
22
23 # Set varaible to 0 so no flush happens
24 set global innodb_idle_flush_pct=0;
25
26 # Enable monitoring of Innodb
27 SET GLOBAL innodb_monitor_enable = all;
28
29 # Make sure counter are 0
30 SELECT COUNT=0 FROM INFORMATION_SCHEMA.INNODB_METRICS
31 WHERE NAME='buffer_flush_batch_total_pages';
32
33 # Every page keeps 2 recods max
34 SET GLOBAL innodb_limit_optimistic_insert_debug=2;
35
36 # Insert multiple rows to make sure we have enough pages dirty
37 insert into t1 select * from t1;
38 insert into t1 select * from t1;
39 insert into t1 select * from t1;
40 insert into t1 select * from t1;
41 insert into t1 select * from t1;
42 insert into t1 select * from t1;
43
44 # Till now we must have some dirty pages in Buffer Pool
45 --sleep 2
46
47 # Total dirty pages in buffer pool
48 SELECT COUNT>0 FROM INFORMATION_SCHEMA.INNODB_METRICS
49 WHERE NAME='buffer_pool_pages_dirty';
50
51 # Noting would have flushed yet, so counter should be 0
52 SELECT COUNT=0 FROM INFORMATION_SCHEMA.INNODB_METRICS
53 WHERE NAME='buffer_flush_batch_total_pages';
54
55 # Enable page cleaner
56 SET GLOBAL innodb_page_cleaner_disabled_debug = 0;
57
58 # Set varaible to value > 0 so that flush happens
59 SET GLOBAL innodb_idle_flush_pct=25;
60
61 # Let flush happen
62 --echo # Waiting for buffer pool pages to get flushed
63 let $wait_condition=SELECT COUNT=0 FROM INFORMATION_SCHEMA.INNODB_METRICS
64 WHERE NAME='buffer_pool_pages_dirty';
65 --source include/wait_condition.inc
66
67 # Pages should have been flushed now
68 SELECT COUNT>0 FROM INFORMATION_SCHEMA.INNODB_METRICS
69 WHERE NAME='buffer_flush_background_total_pages';
70
71 --echo ###########
72 --echo # Cleanup #
73 --echo ###########
74 SET GLOBAL innodb_io_capacity = @innodb_io_capacity_saved;
75 SET GLOBAL innodb_idle_flush_pct = @innodb_idle_flush_pct_saved;
76 SET GLOBAL innodb_limit_optimistic_insert_debug=0;
77 SET GLOBAL innodb_monitor_disable = all;
78 SET GLOBAL innodb_monitor_reset_all = all;
79 SELECT COUNT>0 FROM INFORMATION_SCHEMA.INNODB_METRICS
80 WHERE NAME='buffer_flush_background_total_pages';
81 DROP TABLE t1;
82 --source include/innodb_monitor_restore.inc