]>
Commit | Line | Data |
---|---|---|
734d6226 AM |
1 | # name : innodb_kill_idle_transaction.patch |
2 | # introduced : 5.5.15 | |
3 | # maintainer : Yasufumi | |
4 | # | |
5 | #!!! notice !!! | |
6 | # Any small change to this file in the main branch | |
7 | # should be done or reviewed by the maintainer! | |
8 | --- a/include/mysql/plugin.h | |
9 | +++ b/include/mysql/plugin.h | |
10 | @@ -634,6 +634,12 @@ | |
11 | */ | |
12 | void thd_set_ha_data(MYSQL_THD thd, const struct handlerton *hton, | |
13 | const void *ha_data); | |
14 | + | |
15 | +int thd_command(const MYSQL_THD thd); | |
16 | +long long thd_start_time(const MYSQL_THD thd); | |
17 | +void thd_kill(unsigned long id); | |
18 | +#define EXTENDED_FOR_KILLIDLE | |
19 | + | |
20 | #ifdef __cplusplus | |
21 | } | |
22 | #endif | |
23 | --- a/include/mysql/plugin_audit.h.pp | |
24 | +++ b/include/mysql/plugin_audit.h.pp | |
25 | @@ -196,6 +196,9 @@ | |
26 | void *thd_get_ha_data(const void* thd, const struct handlerton *hton); | |
27 | void thd_set_ha_data(void* thd, const struct handlerton *hton, | |
28 | const void *ha_data); | |
29 | +int thd_command(const void* thd); | |
30 | +long long thd_start_time(const void* thd); | |
31 | +void thd_kill(unsigned long id); | |
32 | struct mysql_event_general | |
33 | { | |
34 | unsigned int event_subclass; | |
35 | --- a/include/mysql/plugin_auth.h.pp | |
36 | +++ b/include/mysql/plugin_auth.h.pp | |
37 | @@ -196,6 +196,9 @@ | |
38 | void *thd_get_ha_data(const void* thd, const struct handlerton *hton); | |
39 | void thd_set_ha_data(void* thd, const struct handlerton *hton, | |
40 | const void *ha_data); | |
41 | +int thd_command(const void* thd); | |
42 | +long long thd_start_time(const void* thd); | |
43 | +void thd_kill(unsigned long id); | |
44 | #include <mysql/plugin_auth_common.h> | |
45 | typedef struct st_plugin_vio_info | |
46 | { | |
47 | --- a/include/mysql/plugin_ftparser.h.pp | |
48 | +++ b/include/mysql/plugin_ftparser.h.pp | |
49 | @@ -149,6 +149,9 @@ | |
50 | void *thd_get_ha_data(const void* thd, const struct handlerton *hton); | |
51 | void thd_set_ha_data(void* thd, const struct handlerton *hton, | |
52 | const void *ha_data); | |
53 | +int thd_command(const void* thd); | |
54 | +long long thd_start_time(const void* thd); | |
55 | +void thd_kill(unsigned long id); | |
56 | enum enum_ftparser_mode | |
57 | { | |
58 | MYSQL_FTPARSER_SIMPLE_MODE= 0, | |
59 | --- a/sql/sql_class.cc | |
60 | +++ b/sql/sql_class.cc | |
61 | @@ -713,6 +713,42 @@ | |
62 | return buffer; | |
63 | } | |
64 | ||
65 | +/* extend for kill session of idle transaction from engine */ | |
66 | +extern "C" | |
67 | +int thd_command(const THD* thd) | |
68 | +{ | |
69 | + return (int) thd->command; | |
70 | +} | |
71 | + | |
72 | +extern "C" | |
73 | +long long thd_start_time(const THD* thd) | |
74 | +{ | |
75 | + return (long long) thd->start_time; | |
76 | +} | |
77 | + | |
78 | +extern "C" | |
79 | +void thd_kill(ulong id) | |
80 | +{ | |
81 | + THD *tmp; | |
82 | + mysql_mutex_lock(&LOCK_thread_count); | |
83 | + I_List_iterator<THD> it(threads); | |
84 | + while ((tmp=it++)) | |
85 | + { | |
86 | + if (tmp->command == COM_DAEMON) | |
87 | + continue; | |
88 | + if (tmp->thread_id == id) | |
89 | + { | |
90 | + mysql_mutex_lock(&tmp->LOCK_thd_data); | |
91 | + break; | |
92 | + } | |
93 | + } | |
94 | + mysql_mutex_unlock(&LOCK_thread_count); | |
95 | + if (tmp) | |
96 | + { | |
97 | + tmp->awake(THD::KILL_CONNECTION); | |
98 | + mysql_mutex_unlock(&tmp->LOCK_thd_data); | |
99 | + } | |
100 | +} | |
101 | ||
102 | /** | |
103 | Implementation of Drop_table_error_handler::handle_condition(). | |
104 | --- a/storage/innobase/handler/ha_innodb.cc | |
105 | +++ b/storage/innobase/handler/ha_innodb.cc | |
106 | @@ -2860,6 +2860,10 @@ | |
107 | ||
108 | innobase_commit_concurrency_init_default(); | |
109 | ||
110 | +#ifndef EXTENDED_FOR_KILLIDLE | |
111 | + srv_kill_idle_transaction = 0; | |
112 | +#endif | |
113 | + | |
114 | #ifdef HAVE_PSI_INTERFACE | |
115 | /* Register keys with MySQL performance schema */ | |
116 | if (PSI_server) { | |
117 | @@ -11686,6 +11690,57 @@ | |
118 | return(false); | |
119 | } | |
120 | ||
121 | +/*********************************************************************** | |
122 | +functions for kill session of idle transaction */ | |
123 | +extern "C" | |
124 | +ibool | |
125 | +innobase_thd_is_idle( | |
126 | +/*=================*/ | |
127 | + const void* thd) /*!< in: thread handle (THD*) */ | |
128 | +{ | |
129 | +#ifdef EXTENDED_FOR_KILLIDLE | |
130 | + return(thd_command((const THD*) thd) == COM_SLEEP); | |
131 | +#else | |
132 | + return(FALSE); | |
133 | +#endif | |
134 | +} | |
135 | + | |
136 | +extern "C" | |
137 | +ib_int64_t | |
138 | +innobase_thd_get_start_time( | |
139 | +/*========================*/ | |
140 | + const void* thd) /*!< in: thread handle (THD*) */ | |
141 | +{ | |
142 | +#ifdef EXTENDED_FOR_KILLIDLE | |
143 | + return((ib_int64_t)thd_start_time((const THD*) thd)); | |
144 | +#else | |
145 | + return(0); /*dummy value*/ | |
146 | +#endif | |
147 | +} | |
148 | + | |
149 | +extern "C" | |
150 | +void | |
151 | +innobase_thd_kill( | |
152 | +/*==============*/ | |
153 | + ulong thd_id) | |
154 | +{ | |
155 | +#ifdef EXTENDED_FOR_KILLIDLE | |
156 | + thd_kill(thd_id); | |
157 | +#else | |
158 | + return; | |
159 | +#endif | |
160 | +} | |
161 | + | |
162 | +extern "C" | |
163 | +ulong | |
164 | +innobase_thd_get_thread_id( | |
165 | +/*=======================*/ | |
166 | + const void* thd) | |
167 | +{ | |
168 | + return(thd_get_thread_id((const THD*) thd)); | |
169 | +} | |
170 | + | |
171 | + | |
172 | static SHOW_VAR innodb_status_variables_export[]= { | |
173 | {"Innodb", (char*) &show_innodb_vars, SHOW_FUNC}, | |
174 | {NullS, NullS, SHOW_LONG} | |
175 | @@ -11977,6 +12032,15 @@ | |
176 | "Number of times a thread is allowed to enter InnoDB within the same SQL query after it has once got the ticket", | |
177 | NULL, NULL, 500L, 1L, ~0L, 0); | |
178 | ||
179 | +static MYSQL_SYSVAR_LONG(kill_idle_transaction, srv_kill_idle_transaction, | |
180 | + PLUGIN_VAR_RQCMDARG, | |
181 | +#ifdef EXTENDED_FOR_KILLIDLE | |
182 | + "If non-zero value, the idle session with transaction which is idle over the value in seconds is killed by InnoDB.", | |
183 | +#else | |
184 | + "No effect for this build.", | |
185 | +#endif | |
186 | + NULL, NULL, 0, 0, LONG_MAX, 0); | |
187 | + | |
188 | static MYSQL_SYSVAR_LONG(file_io_threads, innobase_file_io_threads, | |
189 | PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY | PLUGIN_VAR_NOSYSVAR, | |
190 | "Number of file I/O threads in InnoDB.", | |
191 | @@ -12279,6 +12343,7 @@ | |
192 | MYSQL_SYSVAR(fast_checksum), | |
193 | MYSQL_SYSVAR(commit_concurrency), | |
194 | MYSQL_SYSVAR(concurrency_tickets), | |
195 | + MYSQL_SYSVAR(kill_idle_transaction), | |
196 | MYSQL_SYSVAR(data_file_path), | |
197 | MYSQL_SYSVAR(doublewrite_file), | |
198 | MYSQL_SYSVAR(data_home_dir), | |
199 | --- a/storage/innobase/include/srv0srv.h | |
200 | +++ b/storage/innobase/include/srv0srv.h | |
201 | @@ -296,6 +296,7 @@ | |
202 | extern ulint srv_activity_count; | |
203 | extern ulint srv_fatal_semaphore_wait_threshold; | |
204 | extern ulint srv_dml_needed_delay; | |
205 | +extern lint srv_kill_idle_transaction; | |
206 | ||
207 | extern mutex_t* kernel_mutex_temp;/* mutex protecting the server, trx structs, | |
208 | query threads, and lock table: we allocate | |
209 | --- a/storage/innobase/include/trx0trx.h | |
210 | +++ b/storage/innobase/include/trx0trx.h | |
211 | @@ -594,6 +594,8 @@ | |
212 | replication has processed */ | |
213 | const char* mysql_relay_log_file_name; | |
214 | ib_int64_t mysql_relay_log_pos; | |
215 | + time_t idle_start; | |
216 | + ib_int64_t last_stmt_start; | |
217 | /*------------------------------*/ | |
218 | ulint n_mysql_tables_in_use; /* number of Innobase tables | |
219 | used in the processing of the current | |
220 | --- a/storage/innobase/srv/srv0srv.c | |
221 | +++ b/storage/innobase/srv/srv0srv.c | |
222 | @@ -87,6 +87,12 @@ | |
223 | #include "mysql/plugin.h" | |
224 | #include "mysql/service_thd_wait.h" | |
225 | ||
226 | +/* prototypes of new functions added to ha_innodb.cc for kill_idle_transaction */ | |
227 | +ibool innobase_thd_is_idle(const void* thd); | |
228 | +ib_int64_t innobase_thd_get_start_time(const void* thd); | |
229 | +void innobase_thd_kill(ulong thd_id); | |
230 | +ulong innobase_thd_get_thread_id(const void* thd); | |
231 | + | |
232 | /* prototypes for new functions added to ha_innodb.cc */ | |
233 | ibool innobase_get_slow_log(); | |
234 | ||
235 | @@ -97,6 +103,9 @@ | |
236 | /* The following is the maximum allowed duration of a lock wait. */ | |
237 | UNIV_INTERN ulint srv_fatal_semaphore_wait_threshold = 600; | |
238 | ||
239 | +/**/ | |
240 | +UNIV_INTERN lint srv_kill_idle_transaction = 0; | |
241 | + | |
242 | /* How much data manipulation language (DML) statements need to be delayed, | |
243 | in microseconds, in order to reduce the lagging of the purge thread. */ | |
244 | UNIV_INTERN ulint srv_dml_needed_delay = 0; | |
245 | @@ -2834,6 +2843,36 @@ | |
246 | old_sema = sema; | |
247 | } | |
248 | ||
249 | + if (srv_kill_idle_transaction && trx_sys) { | |
250 | + trx_t* trx; | |
251 | + time_t now; | |
252 | +rescan_idle: | |
253 | + now = time(NULL); | |
254 | + mutex_enter(&kernel_mutex); | |
255 | + trx = UT_LIST_GET_FIRST(trx_sys->mysql_trx_list); | |
256 | + while (trx) { | |
257 | + if (trx->conc_state == TRX_ACTIVE | |
258 | + && trx->mysql_thd | |
259 | + && innobase_thd_is_idle(trx->mysql_thd)) { | |
260 | + ib_int64_t start_time = innobase_thd_get_start_time(trx->mysql_thd); | |
261 | + ulong thd_id = innobase_thd_get_thread_id(trx->mysql_thd); | |
262 | + | |
263 | + if (trx->last_stmt_start != start_time) { | |
264 | + trx->idle_start = now; | |
265 | + trx->last_stmt_start = start_time; | |
266 | + } else if (difftime(now, trx->idle_start) | |
267 | + > srv_kill_idle_transaction) { | |
268 | + /* kill the session */ | |
269 | + mutex_exit(&kernel_mutex); | |
270 | + innobase_thd_kill(thd_id); | |
271 | + goto rescan_idle; | |
272 | + } | |
273 | + } | |
274 | + trx = UT_LIST_GET_NEXT(mysql_trx_list, trx); | |
275 | + } | |
276 | + mutex_exit(&kernel_mutex); | |
277 | + } | |
278 | + | |
279 | /* Flush stderr so that a database user gets the output | |
280 | to possible MySQL error file */ | |
281 | ||
282 | --- a/storage/innobase/trx/trx0trx.c | |
283 | +++ b/storage/innobase/trx/trx0trx.c | |
284 | @@ -143,6 +143,9 @@ | |
285 | trx->mysql_relay_log_file_name = ""; | |
286 | trx->mysql_relay_log_pos = 0; | |
287 | ||
288 | + trx->idle_start = 0; | |
289 | + trx->last_stmt_start = 0; | |
290 | + | |
291 | mutex_create(trx_undo_mutex_key, &trx->undo_mutex, SYNC_TRX_UNDO); | |
292 | ||
293 | trx->rseg = NULL; | |
294 | --- /dev/null | |
295 | +++ b/mysql-test/r/percona_innodb_kill_idle_trx.result | |
296 | @@ -0,0 +1,41 @@ | |
297 | +DROP TABLE IF EXISTS t1; | |
298 | +SET autocommit=0; | |
299 | +CREATE TABLE t1 (a INT) ENGINE=InnoDB; | |
300 | +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction'; | |
301 | +Variable_name Value | |
302 | +innodb_kill_idle_transaction 0 | |
303 | +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction'; | |
304 | +VARIABLE_NAME VARIABLE_VALUE | |
305 | +INNODB_KILL_IDLE_TRANSACTION 0 | |
306 | +SET GLOBAL innodb_kill_idle_transaction=1; | |
307 | +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction'; | |
308 | +Variable_name Value | |
309 | +innodb_kill_idle_transaction 1 | |
310 | +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction'; | |
311 | +VARIABLE_NAME VARIABLE_VALUE | |
312 | +INNODB_KILL_IDLE_TRANSACTION 1 | |
313 | +BEGIN; | |
314 | +INSERT INTO t1 VALUES (1),(2),(3); | |
315 | +COMMIT; | |
316 | +SELECT * FROM t1; | |
317 | +a | |
318 | +1 | |
319 | +2 | |
320 | +3 | |
321 | +BEGIN; | |
322 | +INSERT INTO t1 VALUES (4),(5),(6); | |
323 | +SELECT * FROM t1; | |
324 | +ERROR HY000: MySQL server has gone away | |
325 | +SELECT * FROM t1; | |
326 | +a | |
327 | +1 | |
328 | +2 | |
329 | +3 | |
330 | +DROP TABLE t1; | |
331 | +SET GLOBAL innodb_kill_idle_transaction=0; | |
332 | +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction'; | |
333 | +Variable_name Value | |
334 | +innodb_kill_idle_transaction 0 | |
335 | +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction'; | |
336 | +VARIABLE_NAME VARIABLE_VALUE | |
337 | +INNODB_KILL_IDLE_TRANSACTION 0 | |
338 | --- /dev/null | |
339 | +++ b/mysql-test/r/percona_innodb_kill_idle_trx_locks.result | |
340 | @@ -0,0 +1,45 @@ | |
341 | +DROP TABLE IF EXISTS t1; | |
342 | +SET autocommit=0; | |
343 | +CREATE TABLE t1 (a INT) ENGINE=InnoDB; | |
344 | +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction'; | |
345 | +Variable_name Value | |
346 | +innodb_kill_idle_transaction 0 | |
347 | +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction'; | |
348 | +VARIABLE_NAME VARIABLE_VALUE | |
349 | +INNODB_KILL_IDLE_TRANSACTION 0 | |
350 | +SET GLOBAL innodb_kill_idle_transaction=5; | |
351 | +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction'; | |
352 | +Variable_name Value | |
353 | +innodb_kill_idle_transaction 5 | |
354 | +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction'; | |
355 | +VARIABLE_NAME VARIABLE_VALUE | |
356 | +INNODB_KILL_IDLE_TRANSACTION 5 | |
357 | +BEGIN; | |
358 | +INSERT INTO t1 VALUES (1),(2),(3); | |
359 | +COMMIT; | |
360 | +SELECT * FROM t1; | |
361 | +a | |
362 | +1 | |
363 | +2 | |
364 | +3 | |
365 | +### Locking rows. Lock should be released when idle trx is killed. | |
366 | +BEGIN; | |
367 | +SELECT * FROM t1 FOR UPDATE; | |
368 | +a | |
369 | +1 | |
370 | +2 | |
371 | +3 | |
372 | +UPDATE t1 set a=4; | |
373 | +SELECT * FROM t1; | |
374 | +a | |
375 | +4 | |
376 | +4 | |
377 | +4 | |
378 | +DROP TABLE t1; | |
379 | +SET GLOBAL innodb_kill_idle_transaction=0; | |
380 | +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction'; | |
381 | +Variable_name Value | |
382 | +innodb_kill_idle_transaction 0 | |
383 | +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction'; | |
384 | +VARIABLE_NAME VARIABLE_VALUE | |
385 | +INNODB_KILL_IDLE_TRANSACTION 0 | |
386 | --- /dev/null | |
387 | +++ b/mysql-test/t/percona_innodb_kill_idle_trx.test | |
388 | @@ -0,0 +1,28 @@ | |
389 | +--source include/have_innodb.inc | |
390 | +--disable_warnings | |
391 | +DROP TABLE IF EXISTS t1; | |
392 | +--enable_warnings | |
393 | + | |
394 | +SET autocommit=0; | |
395 | +CREATE TABLE t1 (a INT) ENGINE=InnoDB; | |
396 | + | |
397 | +--source include/percona_innodb_kill_idle_trx_show.inc | |
398 | +SET GLOBAL innodb_kill_idle_transaction=1; | |
399 | +--source include/percona_innodb_kill_idle_trx_show.inc | |
400 | + | |
401 | +BEGIN; | |
402 | +INSERT INTO t1 VALUES (1),(2),(3); | |
403 | +COMMIT; | |
404 | +SELECT * FROM t1; | |
405 | + | |
406 | +BEGIN; | |
407 | +INSERT INTO t1 VALUES (4),(5),(6); | |
408 | +sleep 3; | |
409 | + | |
410 | +--enable_reconnect | |
411 | +--error 2006 --error CR_SERVER_GONE_ERROR | |
412 | +SELECT * FROM t1; | |
413 | +SELECT * FROM t1; | |
414 | +DROP TABLE t1; | |
415 | +SET GLOBAL innodb_kill_idle_transaction=0; | |
416 | +--source include/percona_innodb_kill_idle_trx_show.inc | |
417 | --- /dev/null | |
418 | +++ b/mysql-test/t/percona_innodb_kill_idle_trx_locks.test | |
419 | @@ -0,0 +1,31 @@ | |
420 | +--source include/have_innodb.inc | |
421 | +--disable_warnings | |
422 | +DROP TABLE IF EXISTS t1; | |
423 | +--enable_warnings | |
424 | + | |
425 | +SET autocommit=0; | |
426 | +CREATE TABLE t1 (a INT) ENGINE=InnoDB; | |
427 | + | |
428 | +--source include/percona_innodb_kill_idle_trx_show.inc | |
429 | +SET GLOBAL innodb_kill_idle_transaction=5; | |
430 | +--source include/percona_innodb_kill_idle_trx_show.inc | |
431 | + | |
432 | +connect (conn1,localhost,root,,); | |
433 | +connection conn1; | |
434 | + | |
435 | +BEGIN; | |
436 | +INSERT INTO t1 VALUES (1),(2),(3); | |
437 | +COMMIT; | |
438 | +SELECT * FROM t1; | |
439 | + | |
440 | +--echo ### Locking rows. Lock should be released when idle trx is killed. | |
441 | +BEGIN; | |
442 | +SELECT * FROM t1 FOR UPDATE; | |
443 | + | |
444 | +connection default; | |
445 | +UPDATE t1 set a=4; | |
446 | + | |
447 | +SELECT * FROM t1; | |
448 | +DROP TABLE t1; | |
449 | +SET GLOBAL innodb_kill_idle_transaction=0; | |
450 | +--source include/percona_innodb_kill_idle_trx_show.inc | |
451 | --- /dev/null | |
452 | +++ b/mysql-test/include/percona_innodb_kill_idle_trx_show.inc | |
453 | @@ -0,0 +1,2 @@ | |
454 | +SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction'; | |
455 | +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction'; |