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
|
let flush_hwm=`SELECT @@GLOBAL.innodb_log_wait_for_flush_spin_hwm`;
let cpu_lwm=`SELECT @@GLOBAL.innodb_log_spin_cpu_abs_lwm`;
let cpu_hwm=`SELECT @@GLOBAL.innodb_log_spin_cpu_pct_hwm`;
--echo '#-----------------------------------------------------------#'
--echo 'Test what happens when the innodb_log_wait_for_flush_spin_hwm is changed'
--echo ' in runtime (both decreased and increased).'
--disable_query_log
CREATE TABLE t (a INT NOT NULL PRIMARY KEY, b BLOB);
DELIMITER |;
CREATE PROCEDURE insert_rows()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE no INT DEFAULT 0;
SET no = (SELECT @conn_counter);
WHILE i < 100 DO
INSERT INTO t VALUES (no*100+i, REPEAT('.', 5000));
SET i = i + 1;
END WHILE;
END|
DELIMITER ;|
--enable_query_log
INSERT INTO t VALUES (1000, 'a');
SET GLOBAL innodb_log_wait_for_flush_spin_hwm = 100;
SELECT @@innodb_log_wait_for_flush_spin_hwm;
INSERT INTO t VALUES (2000, 'b');
SET GLOBAL innodb_log_wait_for_flush_spin_hwm = 0;
SELECT @@innodb_log_wait_for_flush_spin_hwm;
INSERT INTO t VALUES (3000, 'c');
SET GLOBAL innodb_log_wait_for_flush_spin_hwm = 80;
SELECT @@innodb_log_wait_for_flush_spin_hwm;
INSERT INTO t VALUES (4000, 'd');
let $n = 9;
let $i = 0;
while ($n) {
connect(con$n, localhost, root,,);
eval SET @conn_counter = $n;
send CALL insert_rows();
inc $i;
dec $n;
}
let $n = 9;
let $i = 0;
while ($n) {
--connection con$n
--reap
--disconnect con$n
inc $i;
dec $n;
}
--connection default
--echo Enable spinning always
SET GLOBAL innodb_log_spin_cpu_pct_hwm = 100;
SET GLOBAL innodb_log_spin_cpu_abs_lwm = 0;
SET GLOBAL innodb_log_wait_for_flush_spin_hwm = 100000;
SELECT @@innodb_log_wait_for_flush_spin_hwm;
SELECT @@innodb_log_spin_cpu_abs_lwm;
SELECT @@innodb_log_spin_cpu_pct_hwm;
--echo Modifying innodb_log_wait_for_flush_spin_hwm
SET GLOBAL innodb_log_wait_for_flush_spin_hwm = 0;
SELECT @@innodb_log_wait_for_flush_spin_hwm;
SET GLOBAL innodb_log_wait_for_flush_spin_hwm = 50;
SELECT @@innodb_log_wait_for_flush_spin_hwm;
SET GLOBAL innodb_log_wait_for_flush_spin_hwm = 1000;
SELECT @@innodb_log_wait_for_flush_spin_hwm;
SET GLOBAL innodb_log_wait_for_flush_spin_hwm = 50;
SELECT @@innodb_log_wait_for_flush_spin_hwm;
SET GLOBAL innodb_log_wait_for_flush_spin_hwm = 0;
SELECT @@innodb_log_wait_for_flush_spin_hwm;
--echo Modifying innodb_log_spin_cpu_abs_lwm
SET GLOBAL innodb_log_spin_cpu_abs_lwm = 0;
SELECT @@innodb_log_spin_cpu_abs_lwm;
SET GLOBAL innodb_log_spin_cpu_abs_lwm = 50;
SELECT @@innodb_log_spin_cpu_abs_lwm;
SET GLOBAL innodb_log_spin_cpu_abs_lwm = 100;
SELECT @@innodb_log_spin_cpu_abs_lwm;
SET GLOBAL innodb_log_spin_cpu_abs_lwm = 50;
SELECT @@innodb_log_spin_cpu_abs_lwm;
SET GLOBAL innodb_log_spin_cpu_abs_lwm = 0;
SELECT @@innodb_log_spin_cpu_abs_lwm;
--echo Modifying innodb_log_spin_cpu_pct_hwm
SET GLOBAL innodb_log_spin_cpu_pct_hwm = 0;
SELECT @@innodb_log_spin_cpu_pct_hwm;
SET GLOBAL innodb_log_spin_cpu_pct_hwm = 5;
SELECT @@innodb_log_spin_cpu_pct_hwm;
SET GLOBAL innodb_log_spin_cpu_pct_hwm = 100;
SELECT @@innodb_log_spin_cpu_pct_hwm;
SET GLOBAL innodb_log_spin_cpu_pct_hwm = 5;
SELECT @@innodb_log_spin_cpu_pct_hwm;
SET GLOBAL innodb_log_spin_cpu_pct_hwm = 0;
SELECT @@innodb_log_spin_cpu_pct_hwm;
--disable_query_log
DROP TABLE t;
DROP PROCEDURE insert_rows;
--enable_query_log
eval SET @@GLOBAL.innodb_log_spin_cpu_abs_lwm=$cpu_lwm;
eval SET @@GLOBAL.innodb_log_spin_cpu_pct_hwm=$cpu_hwm;
eval SET @@GLOBAL.innodb_log_wait_for_flush_spin_hwm=$flush_hwm;
SELECT @@innodb_log_wait_for_flush_spin_hwm;
SELECT @@innodb_log_spin_cpu_abs_lwm;
SELECT @@innodb_log_spin_cpu_pct_hwm;
|
