Skip to content

Commit e6abc82

Browse files
AliSQLAliSQL
AliSQL
authored and
AliSQL
committed
[Feature] Issue#9: PROVIDE ADAPTIVE ALGORITHM FOR INNODB CONCURRENCY TICKETS
Description: ------------ To avoide too many concurrency threads，InnoDB use innodb_thread_concurrency to control the number of operating system threads concurrently inside InnoDB. When a thread is permitted to enter InnoDB, it is given a number of "free tickets" equal to the value of innodb_concurrency_tickets, and the thread can enter and leave InnoDB freely until it has used up its tickets. innodb_concurrency_tickets is a fixed number, and it is hard to choose a proper value fit for both small transactions and large transactons. With a small innodb_concurrency_tickets value, large transactons will acquire tickets many times, which extends the length of time required to complete their task. With a large innodb_concurrency_tickets value, small transactons may get starved by too many concurrent large transactions. This patch provides a method to adaptively adjust tickets assigned to readonly SELECT SQL statement. For large SELECT query, it may acquire tickets many times if the previous assigned tickets are exhausted. To avoid starving smally quries, the tickets assiged to large query are decreased exponentially according to the number of times it acquire tickets, i.e. the more times large query acquire tickets, the smaller tickets it will be assigned. To avoid it getting a too small value, innodb_rds_min_concurrency_tickets is used as the lower bound of the tickets number assigned. Two global InnoDB variables are added, 1. innodb_rds_adaptive_tickets_algo: control whether adaptive algorithm is enabled. 2. innodb_rds_min_concurrency_tickets: the lower bound of tickets can be assiged under adaptive adjusting.
1 parent f9753b5 commit e6abc82

11 files changed

Lines changed: 252 additions & 4 deletions

File tree

‎mysql-test/mysql-test-run.pl‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -163,7 +163,7 @@ END
163163

164164
# If you add a new suite, please check TEST_DIRS in Makefile.am.
165165
#
166-
my $DEFAULT_SUITES= "main,sys_vars,binlog,federated,rpl,innodb,innodb_fts,innodb_zip,perfschema,funcs_1,opt_trace,parts,auth_sec";
166+
my $DEFAULT_SUITES= "main,sys_vars,binlog,federated,rpl,innodb,innodb_fts,innodb_zip,perfschema,funcs_1,opt_trace,parts,auth_sec,rds";
167167
my $opt_suites;
168168

169169
our $opt_verbose= 0; # Verbose output, enable with --verbose

‎mysql-test/suite/rds/my.cnf‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,8 @@
1+
# Use settings from rpl_1slave_base.cnf
2+
# add setting to connect the slave to the master by default
3+
!include ../rpl/rpl_1slave_base.cnf
4+
!include include/default_client.cnf
5+
6+
7+
[mysqld.2]
8+

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

Lines changed: 87 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,87 @@
1+
SET @global_start_value1 = @@global.innodb_rds_adaptive_tickets_algo;
2+
SELECT @global_start_value1;
3+
@global_start_value1
4+
0
5+
SET @global_start_value2 = @@global.innodb_rds_min_concurrency_tickets;
6+
SELECT @global_start_value2;
7+
@global_start_value2
8+
50
9+
SET @global_start_value3 = @@global.innodb_thread_concurrency;
10+
SELECT @global_start_value3;
11+
@global_start_value3
12+
0
13+
SET @global_start_value4 = @@global.innodb_concurrency_tickets;
14+
SELECT @global_start_value4;
15+
@global_start_value4
16+
5000
17+
SET @global_start_value5 = @@global.innodb_thread_sleep_delay;
18+
SELECT @global_start_value5;
19+
@global_start_value5
20+
10000
21+
SET @@global.innodb_rds_adaptive_tickets_algo = TRUE;
22+
SELECT @@global.innodb_rds_adaptive_tickets_algo;
23+
@@global.innodb_rds_adaptive_tickets_algo
24+
1
25+
SET @@global.innodb_rds_min_concurrency_tickets = 5;
26+
SELECT @@global.innodb_rds_min_concurrency_tickets;
27+
@@global.innodb_rds_min_concurrency_tickets
28+
5
29+
SET @@global.innodb_thread_concurrency = 2;
30+
SELECT @@global.innodb_thread_concurrency;
31+
@@global.innodb_thread_concurrency
32+
2
33+
SET @@global.innodb_concurrency_tickets=8;
34+
create table t1(c1 int, c2 int) engine=innodb;
35+
begin;
36+
insert into t1
37+
values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
38+
commit;
39+
select * from t1;
40+
c1 c2
41+
1 1
42+
2 2
43+
3 3
44+
4 4
45+
5 5
46+
6 6
47+
7 7
48+
8 8
49+
9 9
50+
10 10
51+
SET @@global.innodb_rds_adaptive_tickets_algo = FALSE;
52+
SELECT @@global.innodb_rds_adaptive_tickets_algo;
53+
@@global.innodb_rds_adaptive_tickets_algo
54+
0
55+
select * from t1;
56+
c1 c2
57+
1 1
58+
2 2
59+
3 3
60+
4 4
61+
5 5
62+
6 6
63+
7 7
64+
8 8
65+
9 9
66+
10 10
67+
drop table t1;
68+
SET @@global.innodb_rds_adaptive_tickets_algo = @global_start_value1;
69+
SELECT @global_start_value1;
70+
@global_start_value1
71+
0
72+
SET @@global.innodb_rds_min_concurrency_tickets = @global_start_value2;
73+
SELECT @global_start_value2;
74+
@global_start_value2
75+
50
76+
SET @@global.innodb_thread_concurrency = @global_start_value3;
77+
SELECT @global_start_value3;
78+
@global_start_value3
79+
0
80+
SET @@global.innodb_concurrency_tickets= @global_start_value4;
81+
SELECT @global_start_value4;
82+
@global_start_value4
83+
5000
84+
SET @@global.innodb_thread_sleep_delay= @global_start_value5;
85+
SELECT @global_start_value5;
86+
@global_start_value5
87+
10000

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

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,49 @@
1+
--source include/have_innodb.inc
2+
--source include/load_sysvars.inc
3+
4+
SET @global_start_value1 = @@global.innodb_rds_adaptive_tickets_algo;
5+
SELECT @global_start_value1;
6+
SET @global_start_value2 = @@global.innodb_rds_min_concurrency_tickets;
7+
SELECT @global_start_value2;
8+
SET @global_start_value3 = @@global.innodb_thread_concurrency;
9+
SELECT @global_start_value3;
10+
SET @global_start_value4 = @@global.innodb_concurrency_tickets;
11+
SELECT @global_start_value4;
12+
SET @global_start_value5 = @@global.innodb_thread_sleep_delay;
13+
SELECT @global_start_value5;
14+
15+
SET @@global.innodb_rds_adaptive_tickets_algo = TRUE;
16+
SELECT @@global.innodb_rds_adaptive_tickets_algo;
17+
18+
SET @@global.innodb_rds_min_concurrency_tickets = 5;
19+
SELECT @@global.innodb_rds_min_concurrency_tickets;
20+
21+
SET @@global.innodb_thread_concurrency = 2;
22+
SELECT @@global.innodb_thread_concurrency;
23+
24+
SET @@global.innodb_concurrency_tickets=8;
25+
26+
create table t1(c1 int, c2 int) engine=innodb;
27+
begin;
28+
insert into t1
29+
values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
30+
commit;
31+
32+
select * from t1;
33+
34+
SET @@global.innodb_rds_adaptive_tickets_algo = FALSE;
35+
SELECT @@global.innodb_rds_adaptive_tickets_algo;
36+
37+
select * from t1;
38+
39+
drop table t1;
40+
SET @@global.innodb_rds_adaptive_tickets_algo = @global_start_value1;
41+
SELECT @global_start_value1;
42+
SET @@global.innodb_rds_min_concurrency_tickets = @global_start_value2;
43+
SELECT @global_start_value2;
44+
SET @@global.innodb_thread_concurrency = @global_start_value3;
45+
SELECT @global_start_value3;
46+
SET @@global.innodb_concurrency_tickets= @global_start_value4;
47+
SELECT @global_start_value4;
48+
SET @@global.innodb_thread_sleep_delay= @global_start_value5;
49+
SELECT @global_start_value5;

‎mysql-test/suite/sys_vars/r/all_vars.result‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,8 @@ There should be *no* long test name listed below:
1212
select variable_name as `There should be *no* variables listed below:` from t2
1313
left join t1 on variable_name=test_name where test_name is null ORDER BY variable_name;
1414
There should be *no* variables listed below:
15+
INNODB_RDS_ADAPTIVE_TICKETS_ALGO
16+
INNODB_RDS_ADAPTIVE_TICKETS_ALGO
1517
INNODB_RDS_COLUMN_COMPRESSION_LEVEL
1618
INNODB_RDS_COLUMN_COMPRESSION_LEVEL
1719
INNODB_RDS_COLUMN_ZIP_MEM_USE_HEAP
@@ -22,5 +24,7 @@ INNODB_RDS_COLUMN_ZLIB_STRATEGY
2224
INNODB_RDS_COLUMN_ZLIB_STRATEGY
2325
INNODB_RDS_COLUMN_ZLIB_WRAP
2426
INNODB_RDS_COLUMN_ZLIB_WRAP
27+
INNODB_RDS_MIN_CONCURRENCY_TICKETS
28+
INNODB_RDS_MIN_CONCURRENCY_TICKETS
2529
drop table t1;
2630
drop table t2;

‎storage/innobase/handler/ha_innodb.cc‎

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1267,6 +1267,26 @@ innobase_get_type(void)
12671267
{
12681268
return (innodb_hton_ptr->db_type);
12691269
}
1270+
/******************************************************************//**
1271+
Returns true if transaction is read-only up to current SELECT statement.
1272+
This function is different from thd_trx_is_auto_commit() in that
1273+
transaction can be non-auto-commit as long as there is no update statement
1274+
before current SELECT.
1275+
@return true if transacction is read-only */
1276+
UNIV_INTERN
1277+
ibool
1278+
innobase_trx_is_read_only(
1279+
/*======================*/
1280+
trx_t* trx) /*!< in: transaction handle */
1281+
{
1282+
const THD* thd = (const THD*) trx->mysql_thd;
1283+
return (thd != NULL
1284+
&& (!thd_test_options(thd, OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)
1285+
|| (trx->lock.trx_locks.count == 0
1286+
&& trx->mysql_n_tables_locked == 0))
1287+
&& thd_is_select(thd));
1288+
}
1289+
12701290
/******************************************************************//**
12711291
Save some CPU by testing the value of srv_thread_concurrency in inline
12721292
functions. */
@@ -12523,6 +12543,10 @@ ha_innobase::external_lock(
1252312543

1252412544
if (trx->n_mysql_tables_in_use == 0) {
1252512545

12546+
/* Set the n_acquire_tickets_to_enter to zero because it has
12547+
ended the statement at this moment */
12548+
trx->n_acquire_tickets_to_enter = 0;
12549+
1252612550
trx->mysql_n_tables_locked = 0;
1252712551
prebuilt->used_in_HANDLER = FALSE;
1252812552

@@ -16283,6 +16307,16 @@ static MYSQL_SYSVAR_ULONG(concurrency_tickets, srv_n_free_tickets_to_enter,
1628316307
"Number of times a thread is allowed to enter InnoDB within the same SQL query after it has once got the ticket",
1628416308
NULL, NULL, 5000L, 1L, ~0UL, 0);
1628516309

16310+
static MYSQL_SYSVAR_BOOL(rds_adaptive_tickets_algo, srv_adaptive_tickets_algo,
16311+
PLUGIN_VAR_RQCMDARG,
16312+
"Whether to enable the adaptive tickets algorithm",
16313+
NULL, NULL, FALSE);
16314+
16315+
static MYSQL_SYSVAR_ULONG(rds_min_concurrency_tickets, srv_min_n_free_tickets_to_enter,
16316+
PLUGIN_VAR_RQCMDARG,
16317+
"The lower bound of the concurrency_tickets when rds_adaptive_tickets_algo is enabled",
16318+
NULL, NULL, 50L, 1L, ~0UL, 0);
16319+
1628616320
static MYSQL_SYSVAR_LONG(file_io_threads, innobase_file_io_threads,
1628716321
PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY | PLUGIN_VAR_NOSYSVAR,
1628816322
"Number of file I/O threads in InnoDB.",
@@ -16744,6 +16778,8 @@ static struct st_mysql_sys_var* innobase_system_variables[]= {
1674416778
MYSQL_SYSVAR(checksums),
1674516779
MYSQL_SYSVAR(commit_concurrency),
1674616780
MYSQL_SYSVAR(concurrency_tickets),
16781+
MYSQL_SYSVAR(rds_adaptive_tickets_algo),
16782+
MYSQL_SYSVAR(rds_min_concurrency_tickets),
1674716783
MYSQL_SYSVAR(compression_level),
1674816784
MYSQL_SYSVAR(data_file_path),
1674916785
MYSQL_SYSVAR(data_home_dir),

‎storage/innobase/include/ha_prototypes.h‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -437,11 +437,25 @@ ibool
437437
thd_trx_is_auto_commit(
438438
/*===================*/
439439
THD* thd); /*!< in: thread handle, or NULL */
440+
440441
/*******************************************************************//**
441442
@return the engine type of innodb. */
442443
UNIV_INTERN
443444
int
444445
innobase_get_type(void);
446+
447+
/******************************************************************//**
448+
Returns true if transaction is read-only up to current SELECT statement.
449+
This function is different from thd_trx_is_auto_commit() in that
450+
transaction can be non-auto-commit as long as there is no update statement
451+
before current SELECT.
452+
@return true if transacction is read-only */
453+
UNIV_INTERN
454+
ibool
455+
innobase_trx_is_read_only(
456+
/*======================*/
457+
trx_t* trx); /*!< in: transaction handle */
458+
445459
/*****************************************************************//**
446460
A wrapper function of innobase_convert_name(), convert a table or
447461
index name to the MySQL system_charset_info (UTF-8) and quote it if needed.

‎storage/innobase/include/srv0srv.h‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -388,6 +388,10 @@ extern ulong srv_spin_wait_delay;
388388
extern ibool srv_priority_boost;
389389
extern my_bool srv_explicit_redo_gc;
390390

391+
/* Whether to enable adaptive tickets algorithm */
392+
extern my_bool srv_adaptive_tickets_algo;
393+
extern ulong srv_min_n_free_tickets_to_enter;
394+
391395
extern ulint srv_truncated_status_writes;
392396
extern ulint srv_available_undo_logs;
393397

‎storage/innobase/include/trx0trx.h‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -858,6 +858,14 @@ struct trx_t{
858858
field contains the end offset of the
859859
binlog entry */
860860
/*------------------------------*/
861+
ulint n_acquire_tickets_to_enter;
862+
/*!< number of times the current SQL
863+
statement has entered innodb to acquire
864+
tickets, increase each time, reset
865+
to zero at the end of current SQL
866+
statement. This is used for adaptive
867+
tickets algorithm */
868+
/*------------------------------*/
861869
ulint n_mysql_tables_in_use; /*!< number of Innobase tables
862870
used in the processing of the current
863871
SQL statement in MySQL */

‎storage/innobase/srv/srv0conc.cc‎

Lines changed: 39 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -47,6 +47,9 @@ Created 2011/04/18 Sunny Bains
4747
SQL query after it has once got the ticket. */
4848
UNIV_INTERN ulong srv_n_free_tickets_to_enter = 500;
4949

50+
UNIV_INTERN my_bool srv_adaptive_tickets_algo = FALSE;
51+
UNIV_INTERN ulong srv_min_n_free_tickets_to_enter = 50;
52+
5053
#ifdef HAVE_ATOMIC_BUILTINS
5154
/** Maximum sleep delay (in micro-seconds), value of 0 disables it. */
5255
UNIV_INTERN ulong srv_adaptive_max_sleep_delay = 150000;
@@ -162,6 +165,36 @@ srv_conc_free(void)
162165
#endif /* !HAVE_ATOMIC_BUILTINS */
163166
}
164167

168+
/*********************************************************************//**
169+
Calculate how many tickets should assigned to current statement.
170+
If adaptive algorithm(innodb_rds_adaptive_tickets_algo) is disabled,
171+
return srv_n_free_tickets_to_enter as Oracle does; otherwise tickets number
172+
is decreased exponentially according to the number of times current statement
173+
entered innodb to acquire tickets.
174+
*/
175+
static inline
176+
ulint
177+
srv_conc_calc_tickets(
178+
/*================*/
179+
trx_t* trx) /*!< in: transaction that wants to
180+
enter InnoDB */
181+
{
182+
ulint tickets = srv_n_free_tickets_to_enter;
183+
if (!srv_adaptive_tickets_algo) {
184+
return tickets;
185+
}
186+
187+
if (innobase_trx_is_read_only(trx)
188+
&& tickets > srv_min_n_free_tickets_to_enter) {
189+
tickets >>= (trx->n_acquire_tickets_to_enter - 1);
190+
}
191+
192+
return ((tickets < srv_min_n_free_tickets_to_enter)
193+
? srv_min_n_free_tickets_to_enter
194+
: tickets);
195+
}
196+
197+
165198
#ifdef HAVE_ATOMIC_BUILTINS
166199
/*********************************************************************//**
167200
Note that a user thread is entering InnoDB. */
@@ -173,7 +206,8 @@ srv_enter_innodb_with_tickets(
173206
to enter InnoDB */
174207
{
175208
trx->declared_to_be_inside_innodb = TRUE;
176-
trx->n_tickets_to_enter_innodb = srv_n_free_tickets_to_enter;
209+
trx->n_acquire_tickets_to_enter++;
210+
trx->n_tickets_to_enter_innodb = srv_conc_calc_tickets(trx);
177211
}
178212

179213
/*********************************************************************//**
@@ -378,7 +412,8 @@ srv_conc_enter_innodb_without_atomics(
378412

379413
srv_conc.n_active++;
380414
trx->declared_to_be_inside_innodb = TRUE;
381-
trx->n_tickets_to_enter_innodb = srv_n_free_tickets_to_enter;
415+
trx->n_acquire_tickets_to_enter++;
416+
trx->n_tickets_to_enter_innodb = srv_conc_calc_tickets(trx);
382417

383418
os_fast_mutex_unlock(&srv_conc_mutex);
384419

@@ -487,7 +522,8 @@ srv_conc_enter_innodb_without_atomics(
487522
UT_LIST_REMOVE(srv_conc_queue, srv_conc_queue, slot);
488523

489524
trx->declared_to_be_inside_innodb = TRUE;
490-
trx->n_tickets_to_enter_innodb = srv_n_free_tickets_to_enter;
525+
trx->n_acquire_tickets_to_enter++;
526+
trx->n_tickets_to_enter_innodb = srv_conc_calc_tickets(trx);
491527

492528
os_fast_mutex_unlock(&srv_conc_mutex);
493529
}

0 commit comments

Comments
 (0)