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 --source include/have_innodb.inc
26
27 # safe initial value
28 SET @innodb_max_dirty_pages_pct_lwm = @@global.innodb_max_dirty_pages_pct_lwm;
29 SET @innodb_max_dirty_pages_pct = @@global.innodb_max_dirty_pages_pct;
30
31 --echo '#--------------------FN_DYNVARS_044_02-------------------------#'
32 ############################################################################
33 # Check if setting innodb_max_dirty_pages_pct is changed in new connection #
34 ############################################################################
35
36 SET @@global.innodb_max_dirty_pages_pct_lwm = 0;
37 SET @@global.innodb_max_dirty_pages_pct = 80;
38 SET @@global.innodb_max_dirty_pages_pct_lwm = 80;
39 connect (con1,localhost,root,,,,);
40 connection con1;
41 SELECT @@global.innodb_max_dirty_pages_pct;
42 SET @@global.innodb_max_dirty_pages_pct = 70;
43 SELECT @@global.innodb_max_dirty_pages_pct_lwm;
44 connect (con2,localhost,root,,,,);
45 connection con2;
46 SELECT @@global.innodb_max_dirty_pages_pct;
47 connection default;
48 disconnect con2;
49 disconnect con1;
50 # restore initial value
51 SET @@global.innodb_max_dirty_pages_pct = @innodb_max_dirty_pages_pct;
52
53 --echo '#--------------------FN_DYNVARS_044_02-------------------------#'
54 ###################################################################
55 # Begin the functionality Testing of innodb_max_dirty_pages_pct #
56 ###################################################################
57
58
59 --disable_warnings
60 DROP PROCEDURE IF EXISTS add_records;
61 DROP PROCEDURE IF EXISTS add_until;
62 DROP PROCEDURE IF EXISTS check_pct;
63 DROP FUNCTION IF EXISTS dirty_pct;
64 DROP TABLE IF EXISTS t1;
65 --enable_warnings
66
67 DELIMITER //;
68 CREATE PROCEDURE add_records(IN num INT)
69 BEGIN
70 START TRANSACTION;
71 WHILE (num > 0) DO
72 INSERT INTO t1(b) VALUES('MYSQL');
73 SET num = num - 1;
74 END WHILE;
75 COMMIT;
76 END//
77
78 CREATE FUNCTION dirty_pct() RETURNS DECIMAL(20,17)
79 BEGIN
80 DECLARE res DECIMAL(20,17);
81 DECLARE a1, b1 VARCHAR(256);
82 DECLARE a2, b2 VARCHAR(256);
83 DECLARE dirty CURSOR FOR SELECT * FROM information_schema.global_status
84 WHERE variable_name LIKE 'Innodb_buffer_pool_pages_dirty';
85 DECLARE total CURSOR FOR SELECT * FROM information_schema.global_status
86 WHERE variable_name LIKE 'Innodb_buffer_pool_pages_total';
87
88 OPEN dirty;
89 OPEN total;
90
91 FETCH dirty INTO a1, b1;
92 FETCH total INTO a2, b2;
93
94 SET res = (CONVERT(b1,DECIMAL) * 100) / CONVERT(b2,DECIMAL);
95
96 CLOSE dirty;
97 CLOSE total;
98 RETURN res;
99 END//
100
101 CREATE PROCEDURE add_until(IN num DECIMAL)
102 BEGIN
103 DECLARE pct,last DECIMAL(20,17);
104 SET pct = dirty_pct();
105 SET last = 0;
106 WHILE (pct < num AND pct < 100) DO
107 CALL add_records(500);
108 SET pct = dirty_pct();
109 IF (pct < last) THEN
110 SET pct = num + 1;
111 ELSE
112 SET last = pct;
113 END IF;
114 END WHILE;
115 END//
116
117 CREATE PROCEDURE check_pct(IN success_on_wait BOOLEAN)
118 BEGIN
119 IF (success_on_wait > 0) THEN
120 SELECT 'BELOW_MAX' AS PCT_VALUE;
121 ELSE
122 SELECT 'ABOVE_MAX or TimeOut Of The Test' AS PCT_VALUE;
123 END IF;
124 END//
125
126 DELIMITER ;//
127
128 CREATE TABLE t1(
129 a INT AUTO_INCREMENT PRIMARY KEY,
130 b CHAR(200)
131 ) ENGINE = INNODB;
132
133 #==========================================================
134 --echo '---Check when innodb_max_dirty_pages_pct is 10---'
135 #==========================================================
136
137 SET @@global.innodb_max_dirty_pages_pct = 10;
138 SELECT @@global.innodb_max_dirty_pages_pct_lwm;
139 SET GLOBAL innodb_max_dirty_pages_pct_lwm = 15;
140 SELECT @@global.innodb_max_dirty_pages_pct_lwm;
141 SELECT @@global.innodb_max_dirty_pages_pct;
142
143 FLUSH STATUS;
144
145 # add rows until dirty pages pct is about @@global.innodb_max_dirty_pages_pc
146 CALL add_until(10);
147
148 # Give the server some time to flush dirty pages
149 FLUSH TABLES;
150
151 # Add more pages to be over @@global.innodb_max_dirty_pages_pc
152 CALL add_records(500);
153
154 # Give server time to write pages to disk (depends on performance of the system)
155 let $wait_condition= SELECT (dirty_pct() <= @@global.innodb_max_dirty_pages_pct);
156 --source include/wait_condition.inc
157
158 --echo 'We expect dirty pages pct to be BELOW_MAX after some time depending on performance'
159 # Value For $success 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.
160 eval CALL check_pct($success);
161 DROP PROCEDURE add_records;
162 DROP PROCEDURE add_until;
163 DROP PROCEDURE check_pct;
164 DROP FUNCTION dirty_pct;
165 DROP TABLE t1;
166
167 # restore initial value
168 SET GLOBAL innodb_max_dirty_pages_pct_lwm = 0;
169 SET @@global.innodb_max_dirty_pages_pct = @innodb_max_dirty_pages_pct;
170 SET @@global.innodb_max_dirty_pages_pct_lwm = @innodb_max_dirty_pages_pct_lwm;
171
172 ##################################################################
173 # End of functionality Testing for innodb_max_dirty_pages_pct #
174 ##################################################################
175