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