Debian Codegraph @

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

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