Debian Codegraph @

mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_func.test

raw
1 ###############################################################################
2 # #
3 # Variable Name: innodb_max_dirty_pages_pct #
4 # Scope: GLOBAL #
5 # Access Type: Dynamic #
6 # Data Type: Numeric #
7 # Default Value: 90 #
8 # Range: 0-100 #
9 # #
10 # #
11 # Creation Date: 2008-03-08 #
12 # Author: Rizwan #
13 # Modified: HHunger 2009-01-29 Fix for bug#39382, replaced sleep by wait cond.#
14 # added comments, beautifications. #
15 # #
16 # Description: #
17 # Test Cases of Dynamic System Variable innodb_max_dirty_pages_pct that #
18 # checks the behavior of this variable #
19 # #
20 # Reference: #
21 # http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html #
22 # #
23 ###############################################################################
24
25
26 # safe initial value
27 SET @innodb_max_dirty_pages_pct = @@global.innodb_max_dirty_pages_pct;
28
29 --echo '#--------------------FN_DYNVARS_044_02-------------------------#'
30 ############################################################################
31 # Check if setting innodb_max_dirty_pages_pct is changed in new connection #
32 ############################################################################
33
34 SET @@global.innodb_max_dirty_pages_pct = 80;
35 --echo 'connect (con1,localhost,root,,,,)'
36 connect (con1,localhost,root,,,,);
37 --echo 'connection con1'
38 connection con1;
39 SELECT @@global.innodb_max_dirty_pages_pct;
40 SET @@global.innodb_max_dirty_pages_pct = 70;
41 --echo 'connect (con2,localhost,root,,,,)'
42 connect (con2,localhost,root,,,,);
43 --echo 'connection con2'
44 connection con2;
45 SELECT @@global.innodb_max_dirty_pages_pct;
46 --echo 'connection default'
47 connection default;
48 --echo 'disconnect con2'
49 disconnect con2;
50 --echo 'disconnect con1'
51 disconnect con1;
52 # restore initial value
53 SET @@global.innodb_max_dirty_pages_pct = @innodb_max_dirty_pages_pct;
54
55 --echo '#--------------------FN_DYNVARS_044_02-------------------------#'
56 ###################################################################
57 # Begin the functionality Testing of innodb_max_dirty_pages_pct #
58 ###################################################################
59
60
61 --disable_warnings
62 DROP PROCEDURE IF EXISTS add_records;
63 DROP PROCEDURE IF EXISTS add_until;
64 DROP PROCEDURE IF EXISTS check_pct;
65 DROP FUNCTION IF EXISTS dirty_pct;
66 DROP TABLE IF EXISTS t1;
67 --enable_warnings
68
69 DELIMITER //;
70 CREATE PROCEDURE add_records(IN num INT)
71 BEGIN
72 START TRANSACTION;
73 WHILE (num > 0) DO
74 INSERT INTO t1(b) VALUES('MYSQL');
75 SET num = num - 1;
76 END WHILE;
77 COMMIT;
78 END//
79
80 CREATE FUNCTION dirty_pct() RETURNS DECIMAL(20,17)
81 BEGIN
82 DECLARE res DECIMAL(20,17);
83 DECLARE a1, b1 VARCHAR(256);
84 DECLARE a2, b2 VARCHAR(256);
85 DECLARE dirty CURSOR FOR SELECT * FROM performance_schema.global_status
86 WHERE variable_name LIKE 'Innodb_buffer_pool_pages_dirty';
87 DECLARE total CURSOR FOR SELECT * FROM performance_schema.global_status
88 WHERE variable_name LIKE 'Innodb_buffer_pool_pages_total';
89
90 OPEN dirty;
91 OPEN total;
92
93 FETCH dirty INTO a1, b1;
94 FETCH total INTO a2, b2;
95
96 SET res = (CONVERT(b1,DECIMAL) * 100) / CONVERT(b2,DECIMAL);
97
98 CLOSE dirty;
99 CLOSE total;
100 RETURN res;
101 END//
102
103 CREATE PROCEDURE add_until(IN num DECIMAL)
104 BEGIN
105 DECLARE pct,last DECIMAL(20,17);
106 SET pct = dirty_pct();
107 SET last = 0;
108 WHILE (pct < num AND pct < 100) DO
109 CALL add_records(500);
110 SET pct = dirty_pct();
111 IF (pct < last) THEN
112 SET pct = num + 1;
113 ELSE
114 SET last = pct;
115 END IF;
116 END WHILE;
117 END//
118
119 CREATE PROCEDURE check_pct(IN success_on_wait BOOLEAN)
120 BEGIN
121 IF (success_on_wait > 0) THEN
122 SELECT 'BELOW_MAX' AS PCT_VALUE;
123 ELSE
124 SELECT 'ABOVE_MAX or TimeOut Of The Test' AS PCT_VALUE;
125 END IF;
126 END//
127
128 DELIMITER ;//
129
130 CREATE TABLE t1(
131 a INT AUTO_INCREMENT PRIMARY KEY,
132 b CHAR(200)
133 ) ENGINE = INNODB;
134
135 #==========================================================
136 --echo '---Check when innodb_max_dirty_pages_pct is 10---'
137 #==========================================================
138
139 SET @@global.innodb_max_dirty_pages_pct = 10;
140
141 FLUSH STATUS;
142
143 # add rows until dirty pages pct is about @@global.innodb_max_dirty_pages_pc
144 CALL add_until(10);
145
146 # Give the server some time to flush dirty pages
147 FLUSH TABLES;
148
149 # Add more pages to be over @@global.innodb_max_dirty_pages_pc
150 CALL add_records(500);
151
152 # Give server time to write pages to disk (depends on performance of the system)
153 let $wait_condition= SELECT (dirty_pct() <= @@global.innodb_max_dirty_pages_pct);
154 --source include/wait_condition.inc
155
156 --echo 'We expect dirty pages pct to be BELOW_MAX after some time depending on performance'
157 # Value For $sucess will be set from include/wait_condition.inc file. It can have values 1 or 0. It will be 1 if dirty_pct() <= @@global.innodb_max_dirty_pages_pct else it will be 0.
158 eval CALL check_pct($success);
159 DROP PROCEDURE add_records;
160 DROP PROCEDURE add_until;
161 DROP PROCEDURE check_pct;
162 DROP FUNCTION dirty_pct;
163 DROP TABLE t1;
164
165 # restore initial value
166 SET @@global.innodb_max_dirty_pages_pct = @innodb_max_dirty_pages_pct;
167
168 ##################################################################
169 # End of functionality Testing for innodb_max_dirty_pages_pct #
170 ##################################################################
171