MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Horst Hunger Date:January 30 2009 4:59pm
Subject:bzr commit into mysql-5.1-bugteam branch (horst:2761) Bug#39382
View as plain text  
#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#39382Horst Hunger30 Jan