1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
|
################# mysql-test\t\innodb_max_dirty_pages_pct_func.test ##########
# #
# Variable Name: innodb_max_dirty_pages_pct #
# Scope: GLOBAL #
# Access Type: Dynamic #
# Data Type: Numeric #
# Default Value: 90 #
# Range: 0-100 #
# #
# #
# Creation Date: 2008-03-08 #
# Author: Rizwan #
# #
#Description: Test Cases of Dynamic System Variable innodb_max_dirty_pages_pct#
# that checks the behavior of this variable #
# #
# Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
# server-system-variables.html #
# #
###############################################################################
--source include/have_innodb.inc
--echo '#--------------------FN_DYNVARS_044_02-------------------------#'
###########################################################################
# Check if setting innodb_max_dirty_pages_pct is changed in new connection#
###########################################################################
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;
disconnect con2;
disconnect con1;
--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;
DROP PROCEDURE IF EXISTS add_until;
DROP PROCEDURE IF EXISTS check_pct;
DROP FUNCTION IF EXISTS dirty_pct;
DROP TABLE IF EXISTS t1;
--enable_warnings
DELIMITER //;
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'
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;
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//
DELIMITER ;//
CREATE TABLE t1(
a INT AUTO_INCREMENT PRIMARY KEY,
b CHAR(200)
)ENGINE=INNODB;
--enable_query_log
#==========================================================
--echo '---Check when innodb_max_dirty_pages_pct is 10---'
#==========================================================
SET @@global.innodb_max_dirty_pages_pct = 10;
FLUSH STATUS;
# add rows until dirty pages pct is less than this value
CALL add_until(10);
# give server some time to flush dirty pages
FLUSH TABLES;
CALL add_records(500);
--echo '--sleep 5'
--sleep 5
--echo 'We expect dirty pages pct to be BELOW_MAX'
CALL check_pct(10);
#SHOW STATUS LIKE 'innodb%';
DROP PROCEDURE add_records;
DROP PROCEDURE add_until;
DROP PROCEDURE check_pct;
DROP FUNCTION dirty_pct;
##################################################################
# End of functionality Testing for innodb_max_dirty_pages_pct #
##################################################################
|
