#At file:///work/bzr/mysql-5.1-39382/
2761 Horst Hunger 2009-01-30
Fix for bug#39382 including review results after pulling the bugteam tree now using the new mtr.
modified:
mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_func.result
mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_func.test
=== modified file 'mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_func.result'
--- a/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_func.result 2009-01-29 16:59:46 +0000
+++ b/mysql-test/suite/sys_vars/r/innodb_max_dirty_pages_pct_func.result 2009-01-30 16:59:10 +0000
@@ -1,24 +1,88 @@
-SET @start_value= @@global.innodb_max_dirty_pages_pct;
+SET @innodb_max_dirty_pages_pct = @@global.innodb_max_dirty_pages_pct;
'#--------------------FN_DYNVARS_044_02-------------------------#'
SET @@global.innodb_max_dirty_pages_pct = 80;
'connect (con1,localhost,root,,,,)'
+'connection con1'
SELECT @@global.innodb_max_dirty_pages_pct;
@@global.innodb_max_dirty_pages_pct
80
SET @@global.innodb_max_dirty_pages_pct = 70;
'connect (con2,localhost,root,,,,)'
+'connection con2'
SELECT @@global.innodb_max_dirty_pages_pct;
@@global.innodb_max_dirty_pages_pct
70
-'#--------------------FN_DYNVARS_044_02-------------------------#'
'connection default'
+'disconnect con2'
+'disconnect con1'
+SET @@global.innodb_max_dirty_pages_pct = @innodb_max_dirty_pages_pct;
+'#--------------------FN_DYNVARS_044_02-------------------------#'
+DROP PROCEDURE IF EXISTS add_records;
+DROP PROCEDURE IF EXISTS add_until;
+DROP PROCEDURE IF EXISTS check_pct;
+DROP FUNCTION IF EXISTS dirty_pct;
+DROP TABLE IF EXISTS t1;
+CREATE PROCEDURE add_records(IN num INT)
+BEGIN
+START TRANSACTION;
+WHILE (num > 0) DO
+INSERT INTO t1(b) VALUES('MYSQL');
+SET num = num - 1;
+END WHILE;
+COMMIT;
+END//
+CREATE FUNCTION dirty_pct() RETURNS DECIMAL(20,17)
+BEGIN
+DECLARE res DECIMAL(20,17);
+DECLARE a1, b1 VARCHAR(256);
+DECLARE a2, b2 VARCHAR(256);
+DECLARE dirty CURSOR FOR SELECT * FROM information_schema.global_status
+WHERE variable_name LIKE 'Innodb_buffer_pool_pages_dirty';
+DECLARE total CURSOR FOR SELECT * FROM information_schema.global_status
+WHERE variable_name LIKE 'Innodb_buffer_pool_pages_total';
+OPEN dirty;
+OPEN total;
+FETCH dirty INTO a1, b1;
+FETCH total INTO a2, b2;
+SET res = (CONVERT(b1,DECIMAL) * 100) / CONVERT(b2,DECIMAL);
+CLOSE dirty;
+CLOSE total;
+RETURN res;
+END//
+CREATE PROCEDURE add_until(IN num DECIMAL)
+BEGIN
+DECLARE pct,last DECIMAL(20,17);
+SET pct = dirty_pct();
+SET last = 0;
+WHILE (pct < num AND pct < 100) DO
+CALL add_records(500);
+SET pct = dirty_pct();
+IF (pct < last) THEN
+SET pct = num + 1;
+ELSE
+SET last = pct;
+END IF;
+END WHILE;
+END//
+CREATE PROCEDURE check_pct(IN num DECIMAL)
+BEGIN
+IF (dirty_pct() < num) THEN
+SELECT 'BELOW_MAX' AS PCT_VALUE;
+ELSE
+SELECT 'ABOVE_MAX' AS PCT_VALUE;
+END IF;
+END//
+CREATE TABLE t1(
+a INT AUTO_INCREMENT PRIMARY KEY,
+b CHAR(200)
+) ENGINE = INNODB;
'---Check when innodb_max_dirty_pages_pct is 10---'
SET @@global.innodb_max_dirty_pages_pct = 10;
FLUSH STATUS;
CALL add_until(10);
FLUSH TABLES;
CALL add_records(500);
-'We expect dirty pages pct to be BELOW_MAX'
+'We expect dirty pages pct to be BELOW_MAX after some time depending on performance'
CALL check_pct(10);
PCT_VALUE
BELOW_MAX
@@ -27,4 +91,4 @@ DROP PROCEDURE add_until;
DROP PROCEDURE check_pct;
DROP FUNCTION dirty_pct;
DROP TABLE t1;
-SET @@global.innodb_max_dirty_pages_pct= @start_value;
+SET @@global.innodb_max_dirty_pages_pct = @innodb_max_dirty_pages_pct;
=== modified file 'mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_func.test'
--- a/mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_func.test 2009-01-29 16:59:46 +0000
+++ b/mysql-test/suite/sys_vars/t/innodb_max_dirty_pages_pct_func.test 2009-01-30 16:59:10 +0000
@@ -10,9 +10,11 @@
# #
# Creation Date: 2008-03-08 #
# Author: Rizwan #
+# Modified: HHunger 2009-01-29 Fix for bug#39382, replaced sleep by wait cond.#
+# added comments, beautifications. #
# #
# Description: #
-# Test cases of Dynamic System Variable innodb_max_dirty_pages_pct that #
+# Test Cases of Dynamic System Variable innodb_max_dirty_pages_pct that #
# checks the behavior of this variable #
# #
# Reference: #
@@ -22,7 +24,8 @@
--source include/have_innodb.inc
-SET @start_value= @@global.innodb_max_dirty_pages_pct;
+# safe initial value
+SET @innodb_max_dirty_pages_pct = @@global.innodb_max_dirty_pages_pct;
--echo '#--------------------FN_DYNVARS_044_02-------------------------#'
############################################################################
@@ -32,23 +35,29 @@ SET @start_value= @@global.innodb_max_di
SET @@global.innodb_max_dirty_pages_pct = 80;
--echo 'connect (con1,localhost,root,,,,)'
connect (con1,localhost,root,,,,);
+--echo 'connection con1'
+connection con1;
SELECT @@global.innodb_max_dirty_pages_pct;
SET @@global.innodb_max_dirty_pages_pct = 70;
--echo 'connect (con2,localhost,root,,,,)'
connect (con2,localhost,root,,,,);
+--echo 'connection con2'
+connection con2;
SELECT @@global.innodb_max_dirty_pages_pct;
+--echo 'connection default'
+connection default;
+--echo 'disconnect con2'
disconnect con2;
+--echo 'disconnect con1'
disconnect con1;
+# restore initial value
+SET @@global.innodb_max_dirty_pages_pct = @innodb_max_dirty_pages_pct;
--echo '#--------------------FN_DYNVARS_044_02-------------------------#'
###################################################################
# Begin the functionality Testing of innodb_max_dirty_pages_pct #
###################################################################
---echo 'connection default'
-connection default;
-
---disable_query_log
--disable_warnings
DROP PROCEDURE IF EXISTS add_records;
@@ -58,18 +67,13 @@ DROP FUNCTION IF EXISTS dirty_pct;
DROP TABLE IF EXISTS t1;
--enable_warnings
-CREATE TABLE t1(
-a INT AUTO_INCREMENT PRIMARY KEY,
-b CHAR(200)
-)ENGINE=INNODB;
-
DELIMITER //;
-CREATE PROCEDURE add_records(IN NUM INT)
+CREATE PROCEDURE add_records(IN num INT)
BEGIN
START TRANSACTION;
- WHILE (NUM>0) DO
+ WHILE (num > 0) DO
INSERT INTO t1(b) VALUES('MYSQL');
- SET NUM = NUM - 1;
+ SET num = num - 1;
END WHILE;
COMMIT;
END//
@@ -77,15 +81,11 @@ END//
CREATE FUNCTION dirty_pct() RETURNS DECIMAL(20,17)
BEGIN
DECLARE res DECIMAL(20,17);
- DECLARE a1,b1 VARCHAR(256);
- DECLARE a2,b2 VARCHAR(256);
+ DECLARE a1, b1 VARCHAR(256);
+ DECLARE a2, b2 VARCHAR(256);
DECLARE dirty CURSOR FOR SELECT * FROM information_schema.global_status
- WHERE variable_name LIKE 'Innodb_buffer_pool_pages_dirty'
- UNION SELECT * FROM information_schema.session_status
WHERE variable_name LIKE 'Innodb_buffer_pool_pages_dirty';
DECLARE total CURSOR FOR SELECT * FROM information_schema.global_status
- WHERE variable_name LIKE 'Innodb_buffer_pool_pages_total'
- UNION SELECT * FROM information_schema.session_status
WHERE variable_name LIKE 'Innodb_buffer_pool_pages_total';
OPEN dirty;
@@ -94,33 +94,32 @@ BEGIN
FETCH dirty INTO a1, b1;
FETCH total INTO a2, b2;
- SET res = ( CONVERT(b1,DECIMAL)*100)/CONVERT(b2,DECIMAL);
+ SET res = (CONVERT(b1,DECIMAL) * 100) / CONVERT(b2,DECIMAL);
CLOSE dirty;
CLOSE total;
RETURN res;
END//
-CREATE PROCEDURE add_until(IN NUM DECIMAL)
+CREATE PROCEDURE add_until(IN num DECIMAL)
BEGIN
DECLARE pct,last DECIMAL(20,17);
-
SET pct = dirty_pct();
SET last = 0;
- WHILE (pct>NUM and pct<100) DO
+ WHILE (pct < num AND pct < 100) DO
CALL add_records(500);
SET pct = dirty_pct();
- IF (pct<last) THEN
- SET pct = NUM+1;
+ IF (pct < last) THEN
+ SET pct = num + 1;
ELSE
SET last = pct;
END IF;
END WHILE;
END//
-CREATE PROCEDURE check_pct(IN NUM DECIMAL)
+CREATE PROCEDURE check_pct(IN num DECIMAL)
BEGIN
- IF (dirty_pct() < NUM) THEN
+ IF (dirty_pct() < num) THEN
SELECT 'BELOW_MAX' AS PCT_VALUE;
ELSE
SELECT 'ABOVE_MAX' AS PCT_VALUE;
@@ -129,7 +128,10 @@ END//
DELIMITER ;//
---enable_query_log
+CREATE TABLE t1(
+a INT AUTO_INCREMENT PRIMARY KEY,
+b CHAR(200)
+) ENGINE = INNODB;
#==========================================================
--echo '---Check when innodb_max_dirty_pages_pct is 10---'
@@ -139,28 +141,29 @@ SET @@global.innodb_max_dirty_pages_pct
FLUSH STATUS;
-# Add rows until dirty pages pct is less than this value
+# add rows until dirty pages pct is about @@global.innodb_max_dirty_pages_pc
CALL add_until(10);
# Give the server some time to flush dirty pages
FLUSH TABLES;
+
+# Add more pages to be over @@global.innodb_max_dirty_pages_pc
CALL add_records(500);
-# Execute dirty_pct (wait) until dirty pages < 10%
-# Use polling to execute dirty_pct ina loop
-let $wait_condition= SELECT dirty_pct() < 10;
+# Give server time to write pages to disk (depends on performance of the system)
+let $wait_condition= SELECT (dirty_pct() <= @@global.innodb_max_dirty_pages_pct);
--source include/wait_condition.inc
---echo 'We expect dirty pages pct to be BELOW_MAX'
+--echo 'We expect dirty pages pct to be BELOW_MAX after some time depending on performance'
CALL check_pct(10);
-
DROP PROCEDURE add_records;
DROP PROCEDURE add_until;
DROP PROCEDURE check_pct;
DROP FUNCTION dirty_pct;
DROP TABLE t1;
-SET @@global.innodb_max_dirty_pages_pct= @start_value;
+# restore initial value
+SET @@global.innodb_max_dirty_pages_pct = @innodb_max_dirty_pages_pct;
##################################################################
# End of functionality Testing for innodb_max_dirty_pages_pct #
| Thread |
|---|
| • bzr commit into mysql-5.1-bugteam branch (horst:2761) Bug#39382 | Horst Hunger | 30 Jan |
