Skip to content

Commit ac2f202

Browse files
AliSQLAliSQL
AliSQL
authored and
AliSQL
committed
[Feature] Issue#14 THREAD RUNNING CONTROL
This feature is used to reject throughput when database has a high load so as to keep database safe. We introduced two system variables to help limit the database throughput. One of the variables is 'threads_running_high_watermark', which is used to control how many connections the database allowes. The other variable is 'threads_running_ctl_mode', which defines what type of query we would like to block. It can be only SELECT query or all kinds queries(such as all DMLs etc.). When connections are beyond 'thread_running_high_watermark', all of the new sessions or the newly input queries(up to 'threads_running_ctl_mode'), will be rejected, i.e. an error will be returned to client. Note when 'threads_running_high_watermark' is 0, its value will be the same as the value of system variable 'max_connections'.
1 parent ec83e7c commit ac2f202

9 files changed

Lines changed: 596 additions & 0 deletions

File tree

‎mysql-test/r/mysqld--help-notwin.result‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -719,6 +719,12 @@ The following options may be given as the first argument:
719719
--range-alloc-block-size=#
720720
Allocation block size for storing ranges during
721721
optimization
722+
--rds-threads-running-ctl-mode=name
723+
Control which statements will be affected by threads
724+
running control, Values: SELECTS(default), ALL.
725+
--rds-threads-running-high-watermark=#
726+
When threads_running exceeds this limit, query that isn't
727+
in active transaction should quit.
722728
--read-buffer-size=#
723729
Each thread that does a sequential scan allocates a
724730
buffer of this size for each table it scans. If you do
@@ -1244,6 +1250,8 @@ query-cache-type OFF
12441250
query-cache-wlock-invalidate FALSE
12451251
query-prealloc-size 8192
12461252
range-alloc-block-size 4096
1253+
rds-threads-running-ctl-mode SELECTS
1254+
rds-threads-running-high-watermark 151
12471255
read-buffer-size 131072
12481256
read-only FALSE
12491257
read-rnd-buffer-size 262144

‎mysql-test/suite/rds/r/rds_threads_running_control.result‎

Lines changed: 173 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,173 @@
1+
drop table if exists t;
2+
create table t(id int auto_increment primary key, a int)engine=innodb;
3+
insert into t(a) values(1),(2),(3),(4);
4+
grant select,insert,update,delete on test.* to tr_test@localhost;
5+
flush privileges;
6+
set @lock_wait_timeout=@@global.innodb_lock_wait_timeout;
7+
set global innodb_lock_wait_timeout=1;
8+
set @tr_high_watermark=@@global.rds_threads_running_high_watermark;
9+
set @tr_ctl_mode=@@global.rds_threads_running_ctl_mode;
10+
show global variables like 'rds_threads_running_ctl_mode';
11+
Variable_name Value
12+
rds_threads_running_ctl_mode SELECTS
13+
set global rds_threads_running_high_watermark=0;
14+
show global variables like 'rds_threads_running_high_watermark';
15+
Variable_name Value
16+
rds_threads_running_high_watermark 151
17+
show global variables like 'max_connections';
18+
Variable_name Value
19+
max_connections 151
20+
begin;
21+
select * from t;
22+
id a
23+
1 1
24+
2 2
25+
3 3
26+
4 4
27+
begin;
28+
select sleep(100);
29+
select sleep(100);
30+
select sleep(100);
31+
select sleep(100);
32+
select sleep(100);
33+
select sleep(100);
34+
select sleep(100);
35+
select sleep(100);
36+
show status like 'Threads_rejected';
37+
Variable_name Value
38+
Threads_rejected 0
39+
set global rds_threads_running_ctl_mode=DEFAULT;
40+
set global rds_threads_running_high_watermark=8;
41+
show global variables like 'rds_threads_running_high_watermark';
42+
Variable_name Value
43+
rds_threads_running_high_watermark 8
44+
show global variables like 'rds_threads_running_ctl_mode';
45+
Variable_name Value
46+
rds_threads_running_ctl_mode SELECTS
47+
select * from t;
48+
ERROR HY000: MySQL Sever is too busy.
49+
insert into t values(5,5);
50+
update t set a=10 where id=5;
51+
delete from t where id=5;
52+
commit;
53+
rollback;
54+
show status like 'Threads_rejected';
55+
Variable_name Value
56+
Threads_rejected 1
57+
select * from t;
58+
id a
59+
1 1
60+
2 2
61+
3 3
62+
4 4
63+
#For SELECT...FROM UPDATE...(rds_threads_running_ctl_mode=DEFAULT)
64+
show status like 'Threads_running';
65+
Variable_name Value
66+
Threads_running 9
67+
select * from t;
68+
ERROR HY000: MySQL Sever is too busy.
69+
update t set a=a+1 where id=1;
70+
show status like 'Threads_rejected';
71+
Variable_name Value
72+
Threads_rejected 2
73+
commit;
74+
insert into t values (5,5);
75+
update t set a=10 where id=5;
76+
delete from t where id=5;
77+
select * from t;
78+
ERROR HY000: MySQL Sever is too busy.
79+
show status like 'Threads_rejected';
80+
Variable_name Value
81+
Threads_rejected 3
82+
set global rds_threads_running_ctl_mode=ALL;
83+
show global variables like 'rds_threads_running_ctl_mode';
84+
Variable_name Value
85+
rds_threads_running_ctl_mode ALL
86+
select * from t;
87+
ERROR HY000: MySQL Sever is too busy.
88+
insert into t values(7,7);
89+
ERROR HY000: MySQL Sever is too busy.
90+
update t set a=14 where id=7;
91+
ERROR HY000: MySQL Sever is too busy.
92+
delete from t where id=7;
93+
ERROR HY000: MySQL Sever is too busy.
94+
commit;
95+
rollback;
96+
show status like 'Threads_rejected';
97+
Variable_name Value
98+
Threads_rejected 7
99+
#For SELECT...FROM UPDATE...(rds_threads_running_ctl_mode=ALL)
100+
update t set a=a+1 where id=1;
101+
ERROR HY000: MySQL Sever is too busy.
102+
show status like 'Threads_rejected';
103+
Variable_name Value
104+
Threads_rejected 8
105+
select * from t;
106+
id a
107+
1 1
108+
2 2
109+
3 3
110+
4 4
111+
insert into t values (9,9);
112+
update t set a=18 where id=9;
113+
delete from t where id=9;
114+
commit;
115+
select * from t;
116+
ERROR HY000: MySQL Sever is too busy.
117+
insert into t values (10,10);
118+
ERROR HY000: MySQL Sever is too busy.
119+
update t set a=20 where id=10;
120+
ERROR HY000: MySQL Sever is too busy.
121+
delete from t where id=10;
122+
ERROR HY000: MySQL Sever is too busy.
123+
commit;
124+
show status like 'Threads_rejected';
125+
Variable_name Value
126+
Threads_rejected 12
127+
kill query ID;
128+
kill query ID;
129+
kill query ID;
130+
kill query ID;
131+
kill query ID;
132+
kill query ID;
133+
kill query ID;
134+
set global rds_threads_running_high_watermark=1;
135+
set global debug= "+d,thread_running_change_before_doublecheck";
136+
select * from t;;
137+
kill query ID;
138+
id a
139+
1 2
140+
2 2
141+
3 3
142+
4 4
143+
set global rds_threads_running_high_watermark=8;
144+
set global debug= default;
145+
sleep(100)
146+
1
147+
show global variables like 'rds_threads_running_%';
148+
Variable_name Value
149+
rds_threads_running_ctl_mode ALL
150+
rds_threads_running_high_watermark 8
151+
show global variables like 'rds_threads_running_%';
152+
Variable_name Value
153+
rds_threads_running_ctl_mode ALL
154+
rds_threads_running_high_watermark 8
155+
sleep(100)
156+
1
157+
sleep(100)
158+
1
159+
sleep(100)
160+
1
161+
sleep(100)
162+
1
163+
sleep(100)
164+
1
165+
sleep(100)
166+
1
167+
sleep(100)
168+
1
169+
set global rds_threads_running_high_watermark=@tr_high_watermark;
170+
set global rds_threads_running_ctl_mode=@tr_ctl_mode;
171+
set global innodb_lock_wait_timeout=@lock_wait_timeout;
172+
drop user tr_test@localhost;
173+
drop table t;

0 commit comments

Comments
 (0)