1
###############################################################################
3
# Variable Name: innodb_max_dirty_pages_pct #
5
# Access Type: Dynamic #
11
# Creation Date: 2008-03-08 #
13
# Modified: HHunger 2009-01-29 Fix for bug#39382, replaced sleep by wait cond.#
14
# added comments, beautifications. #
17
# Test Cases of Dynamic System Variable innodb_max_dirty_pages_pct that #
18
# checks the behavior of this variable #
21
# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html #
23
###############################################################################
25
--source include/have_innodb.inc
28
SET @innodb_max_dirty_pages_pct = @@global.innodb_max_dirty_pages_pct;
30
--echo '#--------------------FN_DYNVARS_044_02-------------------------#'
31
############################################################################
32
# Check if setting innodb_max_dirty_pages_pct is changed in new connection #
33
############################################################################
35
SET @@global.innodb_max_dirty_pages_pct = 80;
36
--echo 'connect (con1,localhost,root,,,,)'
37
connect (con1,localhost,root,,,,);
38
--echo 'connection con1'
40
SELECT @@global.innodb_max_dirty_pages_pct;
41
SET @@global.innodb_max_dirty_pages_pct = 70;
42
--echo 'connect (con2,localhost,root,,,,)'
43
connect (con2,localhost,root,,,,);
44
--echo 'connection con2'
46
SELECT @@global.innodb_max_dirty_pages_pct;
47
--echo 'connection default'
49
--echo 'disconnect con2'
51
--echo 'disconnect con1'
53
# restore initial value
54
SET @@global.innodb_max_dirty_pages_pct = @innodb_max_dirty_pages_pct;
56
--echo '#--------------------FN_DYNVARS_044_02-------------------------#'
57
###################################################################
58
# Begin the functionality Testing of innodb_max_dirty_pages_pct #
59
###################################################################
63
DROP PROCEDURE IF EXISTS add_records;
64
DROP PROCEDURE IF EXISTS add_until;
65
DROP PROCEDURE IF EXISTS check_pct;
66
DROP FUNCTION IF EXISTS dirty_pct;
67
DROP TABLE IF EXISTS t1;
71
CREATE PROCEDURE add_records(IN num INT)
75
INSERT INTO t1(b) VALUES('MYSQL');
81
CREATE FUNCTION dirty_pct() RETURNS DECIMAL(20,17)
83
DECLARE res DECIMAL(20,17);
84
DECLARE a1, b1 VARCHAR(256);
85
DECLARE a2, b2 VARCHAR(256);
86
DECLARE dirty CURSOR FOR SELECT * FROM information_schema.global_status
87
WHERE variable_name LIKE 'Innodb_buffer_pool_pages_dirty';
88
DECLARE total CURSOR FOR SELECT * FROM information_schema.global_status
89
WHERE variable_name LIKE 'Innodb_buffer_pool_pages_total';
94
FETCH dirty INTO a1, b1;
95
FETCH total INTO a2, b2;
97
SET res = (CONVERT(b1,DECIMAL) * 100) / CONVERT(b2,DECIMAL);
104
CREATE PROCEDURE add_until(IN num DECIMAL)
106
DECLARE pct,last DECIMAL(20,17);
107
SET pct = dirty_pct();
109
WHILE (pct < num AND pct < 100) DO
110
CALL add_records(500);
111
SET pct = dirty_pct();
120
CREATE PROCEDURE check_pct(IN success_on_wait BOOLEAN)
122
IF (success_on_wait > 0) THEN
123
SELECT 'BELOW_MAX' AS PCT_VALUE;
125
SELECT 'ABOVE_MAX or TimeOut Of The Test' AS PCT_VALUE;
132
a INT AUTO_INCREMENT PRIMARY KEY,
136
#==========================================================
137
--echo '---Check when innodb_max_dirty_pages_pct is 10---'
138
#==========================================================
140
SET @@global.innodb_max_dirty_pages_pct = 10;
144
# add rows until dirty pages pct is about @@global.innodb_max_dirty_pages_pc
147
# Give the server some time to flush dirty pages
150
# Add more pages to be over @@global.innodb_max_dirty_pages_pc
151
CALL add_records(500);
153
# Give server time to write pages to disk (depends on performance of the system)
154
let $wait_condition= SELECT (dirty_pct() <= @@global.innodb_max_dirty_pages_pct);
155
--source include/wait_condition.inc
157
--echo 'We expect dirty pages pct to be BELOW_MAX after some time depending on performance'
158
# Value For $sucess will be set from include/wait_condition.inc file. It can have values 1 or 0. It will be 1 if dirty_pct() <= @@global.innodb_max_dirty_pages_pct else it will be 0.
159
eval CALL check_pct($success);
160
DROP PROCEDURE add_records;
161
DROP PROCEDURE add_until;
162
DROP PROCEDURE check_pct;
163
DROP FUNCTION dirty_pct;
166
# restore initial value
167
SET @@global.innodb_max_dirty_pages_pct = @innodb_max_dirty_pages_pct;
169
##################################################################
170
# End of functionality Testing for innodb_max_dirty_pages_pct #
171
##################################################################