Debian Codegraph @

mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_func.result

raw
1 SET @innodb_max_dirty_pages_pct_lwm = @@global.innodb_max_dirty_pages_pct_lwm;
2 SET @innodb_max_dirty_pages_pct = @@global.innodb_max_dirty_pages_pct;
3 '#--------------------FN_DYNVARS_044_02-------------------------#'
4 SET @@global.innodb_max_dirty_pages_pct_lwm = 0;
5 SET @@global.innodb_max_dirty_pages_pct = 80;
6 SET @@global.innodb_max_dirty_pages_pct_lwm = 80;
7 connect con1,localhost,root,,,,;
8 connection con1;
9 SELECT @@global.innodb_max_dirty_pages_pct;
10 @@global.innodb_max_dirty_pages_pct
11 80.000000
12 SET @@global.innodb_max_dirty_pages_pct = 70;
13 Warnings:
14 Warning 1210 innodb_max_dirty_pages_pct cannot be set lower than innodb_max_dirty_pages_pct_lwm.
15 Warning 1210 Lowering innodb_max_dirty_page_pct_lwm to 70.000000
16 SELECT @@global.innodb_max_dirty_pages_pct_lwm;
17 @@global.innodb_max_dirty_pages_pct_lwm
18 70.000000
19 connect con2,localhost,root,,,,;
20 connection con2;
21 SELECT @@global.innodb_max_dirty_pages_pct;
22 @@global.innodb_max_dirty_pages_pct
23 70.000000
24 connection default;
25 disconnect con2;
26 disconnect con1;
27 SET @@global.innodb_max_dirty_pages_pct = @innodb_max_dirty_pages_pct;
28 '#--------------------FN_DYNVARS_044_02-------------------------#'
29 DROP PROCEDURE IF EXISTS add_records;
30 DROP PROCEDURE IF EXISTS add_until;
31 DROP PROCEDURE IF EXISTS check_pct;
32 DROP FUNCTION IF EXISTS dirty_pct;
33 DROP TABLE IF EXISTS t1;
34 CREATE PROCEDURE add_records(IN num INT)
35 BEGIN
36 START TRANSACTION;
37 WHILE (num > 0) DO
38 INSERT INTO t1(b) VALUES('MYSQL');
39 SET num = num - 1;
40 END WHILE;
41 COMMIT;
42 END//
43 CREATE FUNCTION dirty_pct() RETURNS DECIMAL(20,17)
44 BEGIN
45 DECLARE res DECIMAL(20,17);
46 DECLARE a1, b1 VARCHAR(256);
47 DECLARE a2, b2 VARCHAR(256);
48 DECLARE dirty CURSOR FOR SELECT * FROM information_schema.global_status
49 WHERE variable_name LIKE 'Innodb_buffer_pool_pages_dirty';
50 DECLARE total CURSOR FOR SELECT * FROM information_schema.global_status
51 WHERE variable_name LIKE 'Innodb_buffer_pool_pages_total';
52 OPEN dirty;
53 OPEN total;
54 FETCH dirty INTO a1, b1;
55 FETCH total INTO a2, b2;
56 SET res = (CONVERT(b1,DECIMAL) * 100) / CONVERT(b2,DECIMAL);
57 CLOSE dirty;
58 CLOSE total;
59 RETURN res;
60 END//
61 CREATE PROCEDURE add_until(IN num DECIMAL)
62 BEGIN
63 DECLARE pct,last DECIMAL(20,17);
64 SET pct = dirty_pct();
65 SET last = 0;
66 WHILE (pct < num AND pct < 100) DO
67 CALL add_records(500);
68 SET pct = dirty_pct();
69 IF (pct < last) THEN
70 SET pct = num + 1;
71 ELSE
72 SET last = pct;
73 END IF;
74 END WHILE;
75 END//
76 CREATE PROCEDURE check_pct(IN success_on_wait BOOLEAN)
77 BEGIN
78 IF (success_on_wait > 0) THEN
79 SELECT 'BELOW_MAX' AS PCT_VALUE;
80 ELSE
81 SELECT 'ABOVE_MAX or TimeOut Of The Test' AS PCT_VALUE;
82 END IF;
83 END//
84 CREATE TABLE t1(
85 a INT AUTO_INCREMENT PRIMARY KEY,
86 b CHAR(200)
87 ) ENGINE = INNODB;
88 '---Check when innodb_max_dirty_pages_pct is 10---'
89 SET @@global.innodb_max_dirty_pages_pct = 10;
90 Warnings:
91 Warning 1210 innodb_max_dirty_pages_pct cannot be set lower than innodb_max_dirty_pages_pct_lwm.
92 Warning 1210 Lowering innodb_max_dirty_page_pct_lwm to 10.000000
93 SELECT @@global.innodb_max_dirty_pages_pct_lwm;
94 @@global.innodb_max_dirty_pages_pct_lwm
95 10.000000
96 SET GLOBAL innodb_max_dirty_pages_pct_lwm = 15;
97 Warnings:
98 Warning 1210 innodb_max_dirty_pages_pct_lwm cannot be set higher than innodb_max_dirty_pages_pct.
99 Warning 1210 Setting innodb_max_dirty_page_pct_lwm to 10.000000
100 SELECT @@global.innodb_max_dirty_pages_pct_lwm;
101 @@global.innodb_max_dirty_pages_pct_lwm
102 10.000000
103 SELECT @@global.innodb_max_dirty_pages_pct;
104 @@global.innodb_max_dirty_pages_pct
105 10.000000
106 FLUSH STATUS;
107 CALL add_until(10);
108 FLUSH TABLES;
109 CALL add_records(500);
110 'We expect dirty pages pct to be BELOW_MAX after some time depending on performance'
111 CALL check_pct(1);
112 PCT_VALUE
113 BELOW_MAX
114 DROP PROCEDURE add_records;
115 DROP PROCEDURE add_until;
116 DROP PROCEDURE check_pct;
117 DROP FUNCTION dirty_pct;
118 DROP TABLE t1;
119 SET GLOBAL innodb_max_dirty_pages_pct_lwm = 0;
120 SET @@global.innodb_max_dirty_pages_pct = @innodb_max_dirty_pages_pct;
121 SET @@global.innodb_max_dirty_pages_pct_lwm = @innodb_max_dirty_pages_pct_lwm;