Skip to content

Commit 97d77c9

Browse files
AliSQLAliSQL
AliSQL
authored and
AliSQL
committed
[Feature] Issue#25 THROTTLE IOPS FOR SQL STATEMENT
Description: ------------ Export three status variables: logical_read, phsical_read and physical_async_read for buffer pool hit read, buffer pool miss sync read, and buffer pool miss async read respectively Export session variable rds_sql_max_iops to control the IO usage for single sql statement ex: set session rds_sql_max_iops=100; When sql execution returning from innodb to server layer, it will check whether IOPS is oversubscribed, if so it might sleep some delta time to prevent from overkilling I/O bandwith for single sql statement
1 parent cb42733 commit 97d77c9

24 files changed

Lines changed: 350 additions & 41 deletions

‎include/mysql/plugin.h‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -549,11 +549,14 @@ struct st_mysql_value
549549
extern "C" {
550550
#endif
551551

552+
enum enum_io_type {LOGICAL_READ, PHYSICAL_SYNC_READ, PHYSICAL_ASYNC_READ};
553+
void thd_add_io_stats(enum enum_io_type io_type);
552554
int thd_in_lock_tables(const MYSQL_THD thd);
553555
int thd_tablespace_op(const MYSQL_THD thd);
554556
long long thd_test_options(const MYSQL_THD thd, long long test_options);
555557
int thd_sql_command(const MYSQL_THD thd);
556558
long long thd_wait_time(const MYSQL_THD thd);
559+
int thd_is_limit_io();
557560
const char *thd_proc_info(MYSQL_THD thd, const char *info);
558561
void **thd_ha_data(const MYSQL_THD thd, const struct handlerton *hton);
559562
void thd_storage_lock_wait(MYSQL_THD thd, long long value);

‎include/mysql/plugin_audit.h.pp‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -221,11 +221,14 @@
221221
int (*val_int)(struct st_mysql_value *, long long *intbuf);
222222
int (*is_unsigned)(struct st_mysql_value *);
223223
};
224+
enum enum_io_type {LOGICAL_READ, PHYSICAL_SYNC_READ, PHYSICAL_ASYNC_READ};
225+
void thd_add_io_stats(enum enum_io_type io_type);
224226
int thd_in_lock_tables(const void* thd);
225227
int thd_tablespace_op(const void* thd);
226228
long long thd_test_options(const void* thd, long long test_options);
227229
int thd_sql_command(const void* thd);
228230
long long thd_wait_time(const void* thd);
231+
int thd_is_limit_io();
229232
const char *thd_proc_info(void* thd, const char *info);
230233
void **thd_ha_data(const void* thd, const struct handlerton *hton);
231234
void thd_storage_lock_wait(void* thd, long long value);

‎include/mysql/plugin_auth.h.pp‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -221,11 +221,14 @@
221221
int (*val_int)(struct st_mysql_value *, long long *intbuf);
222222
int (*is_unsigned)(struct st_mysql_value *);
223223
};
224+
enum enum_io_type {LOGICAL_READ, PHYSICAL_SYNC_READ, PHYSICAL_ASYNC_READ};
225+
void thd_add_io_stats(enum enum_io_type io_type);
224226
int thd_in_lock_tables(const void* thd);
225227
int thd_tablespace_op(const void* thd);
226228
long long thd_test_options(const void* thd, long long test_options);
227229
int thd_sql_command(const void* thd);
228230
long long thd_wait_time(const void* thd);
231+
int thd_is_limit_io();
229232
const char *thd_proc_info(void* thd, const char *info);
230233
void **thd_ha_data(const void* thd, const struct handlerton *hton);
231234
void thd_storage_lock_wait(void* thd, long long value);

‎include/mysql/plugin_ftparser.h.pp‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -174,11 +174,14 @@
174174
int (*val_int)(struct st_mysql_value *, long long *intbuf);
175175
int (*is_unsigned)(struct st_mysql_value *);
176176
};
177+
enum enum_io_type {LOGICAL_READ, PHYSICAL_SYNC_READ, PHYSICAL_ASYNC_READ};
178+
void thd_add_io_stats(enum enum_io_type io_type);
177179
int thd_in_lock_tables(const void* thd);
178180
int thd_tablespace_op(const void* thd);
179181
long long thd_test_options(const void* thd, long long test_options);
180182
int thd_sql_command(const void* thd);
181183
long long thd_wait_time(const void* thd);
184+
int thd_is_limit_io();
182185
const char *thd_proc_info(void* thd, const char *info);
183186
void **thd_ha_data(const void* thd, const struct handlerton *hton);
184187
void thd_storage_lock_wait(void* thd, long long value);

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

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -720,6 +720,8 @@ The following options may be given as the first argument:
720720
Allocation block size for storing ranges during
721721
optimization
722722
--rds-indexstat Control INDEX_STATISTICS
723+
--rds-sql-max-iops=#
724+
restrict the sql physical iops when sql executing.
723725
--rds-tablestat Control TABLE_STATISTICS
724726
--rds-threads-running-ctl-mode=name
725727
Control which statements will be affected by threads
@@ -1262,6 +1264,7 @@ query-cache-wlock-invalidate FALSE
12621264
query-prealloc-size 8192
12631265
range-alloc-block-size 4096
12641266
rds-indexstat FALSE
1267+
rds-sql-max-iops 0
12651268
rds-tablestat FALSE
12661269
rds-threads-running-ctl-mode SELECTS
12671270
rds-threads-running-high-watermark 151

‎mysql-test/suite/funcs_1/datadict/processlist_val.inc‎

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -96,7 +96,7 @@ echo
9696
--replace_result "init" STATE "starting" STATE "cleaning up" STATE
9797
--sorted_result
9898
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
99-
--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> 9 # 10 #
99+
--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> 9 # 10 # 11 # 12 # 13 #
100100
--replace_result "init" STATE "starting" STATE "cleaning up" STATE
101101
--sorted_result
102102
SHOW FULL PROCESSLIST;
@@ -173,7 +173,7 @@ let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
173173
--replace_result "init" STATE "starting" STATE "cleaning up" STATE
174174
--sorted_result
175175
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
176-
--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> 9 # 10 #
176+
--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> 9 # 10 # 11 # 12 # 13 #
177177
--replace_result "init" STATE "starting" STATE "cleaning up" STATE
178178
--sorted_result
179179
SHOW FULL PROCESSLIST;
@@ -222,7 +222,7 @@ connection con1;
222222
--replace_result "init" STATE "starting" STATE "cleaning up" STATE
223223
--sorted_result
224224
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
225-
--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> 9 # 10 #
225+
--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> 9 # 10 # 11 # 12 # 13 #
226226
--replace_result "init" STATE "starting" STATE "cleaning up" STATE
227227
--sorted_result
228228
SHOW FULL PROCESSLIST;
@@ -260,7 +260,7 @@ connection con2;
260260
--replace_result "init" STATE "starting" STATE "cleaning up" STATE
261261
--sorted_result
262262
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
263-
--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> 9 # 10 #
263+
--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> 9 # 10 # 11 # 12 # 13 #
264264
--replace_result "init" STATE "starting" STATE "cleaning up" STATE
265265
--sorted_result
266266
SHOW FULL PROCESSLIST;
@@ -324,7 +324,7 @@ WHERE ID = @test_user_con2_id AND Command IN('Query','Execute')
324324
--replace_result "init" STATE "starting" STATE "cleaning up" STATE
325325
--sorted_result
326326
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
327-
--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> 9 # 10 #
327+
--replace_column 1 <ID> 3 <HOST_NAME> 6 <TIME> 9 # 10 # 11 # 12 # 13 #
328328
--replace_result "init" STATE "starting" STATE "cleaning up" STATE
329329
--sorted_result
330330
SHOW FULL PROCESSLIST;
@@ -460,7 +460,7 @@ echo
460460
--replace_result "init" STATE "starting" STATE "cleaning up" STATE
461461
--sorted_result
462462
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
463-
--replace_column 1 <ID> 3 <HOST_NAME> 5 <COMMAND> 6 <TIME> 7 <STATE> 9 # 10 #
463+
--replace_column 1 <ID> 3 <HOST_NAME> 5 <COMMAND> 6 <TIME> 7 <STATE> 9 # 10 # 11 # 12 # 13 #
464464
--replace_result "init" STATE "starting" STATE "cleaning up" STATE
465465
--sorted_result
466466
SHOW FULL PROCESSLIST;

‎mysql-test/suite/funcs_1/r/processlist_val_ps.result‎

Lines changed: 18 additions & 18 deletions
Large diffs are not rendered by default.

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

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,13 @@
1+
create database test_1;
2+
create table test_1.t1(
3+
id int primary key auto_increment,
4+
col1 varchar(100),
5+
col2 int,
6+
key(col2)
7+
)engine= innodb;
8+
Table Checksum
9+
test_1.t1 1724218332
10+
Id User Host db Command Time State Info Memory_used Memory_used_by_query Logical_read Physical_sync_read Physical_async_read
11+
# root localhost test Sleep xxx # # # # # # #
12+
# root localhost test Query xxx # # # # # # #
13+
# root localhost test Sleep xxx # # # # # # #

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

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,41 @@
1+
create database test_1;
2+
create table test_1.t1(
3+
id int primary key auto_increment,
4+
col1 varchar(1000),
5+
col2 int,
6+
key(col2)
7+
)engine= innodb;
8+
set session rds_sql_max_iops=10;
9+
checksum table test_1.t1;
10+
Table Checksum
11+
test_1.t1 3791299187
12+
show full processlist;
13+
Id User Host db Command Time State Info Memory_used Memory_used_by_query Logical_read Physical_sync_read Physical_async_read
14+
# root localhost test Query xxx # # # # # # #
15+
show status like '%IO_limit_count%';
16+
Variable_name Value
17+
IO_limit_count #
18+
select count(*) from test_1.t1 where id= 2;
19+
count(*)
20+
1
21+
select count(*) from test_1.t1 where id> 2;
22+
count(*)
23+
1998
24+
select count(*) from test_1.t1 where id< 100;
25+
count(*)
26+
98
27+
select count(*) from test_1.t1 where col2= 2;
28+
count(*)
29+
2
30+
select count(*) from test_1.t1 where col2> 2;
31+
count(*)
32+
1997
33+
select count(*) from test_1.t1 where col2< 100;
34+
count(*)
35+
196
36+
show status like '%IO_limit_count%';
37+
Variable_name Value
38+
IO_limit_count #
39+
set session rds_sql_max_iops=0;
40+
drop table test_1.t1;
41+
drop database test_1;

‎mysql-test/suite/rds/t/feature_checksum_restrict_io_issue25.test‎

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,33 @@
1+
connect(conn1, localhost, root,,);
2+
connect(conn2, localhost, root,,);
3+
4+
connection conn1;
5+
create database test_1;
6+
create table test_1.t1(
7+
id int primary key auto_increment,
8+
col1 varchar(100),
9+
col2 int,
10+
key(col2)
11+
)engine= innodb;
12+
13+
--disable_query_log
14+
let $loop=1000;
15+
while($loop)
16+
{
17+
eval insert into test_1.t1(col1, col2) values(concat('test', $loop), $loop);
18+
dec $loop;
19+
}
20+
21+
insert into test_1.t1(col1,col2)select col1, col2 from test_1.t1;
22+
commit;
23+
24+
set session rds_sql_max_iops=2;
25+
checksum table test_1.t1;
26+
27+
--replace_column 1 # 6 xxx 7 # 8 # 9 # 10 # 11 # 12 # 13 #
28+
show full processlist;
29+
30+
31+
drop table test_1.t1;
32+
drop database test_1;
33+
disconnect conn2;

0 commit comments

Comments
 (0)