1387258 - Use MySQL 5.7's max_execution_time to limit the maximum SELECT query duration

Status: RESOLVED FIXED

(Tree Management :: Treeherder: Infrastructure, enhancement, P1)

Description

[Ed Morley [:emorley]]

Once we're using MySQL 5.7 we can use the new setting to prevent runaway SELECT queries like the ones that caused bug 1386331:
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_time

Enabling this setting will be easy, picking a value that doesn't break known longer-running background tasks will be harder.

Comment 1

[Ed Morley [:emorley]]

(In reply to Ed Morley [:emorley] from comment #0)
> Enabling this setting will be easy, picking a value that doesn't break known
> longer-running background tasks will be harder.

From first glance:
* Web transactions already get cut off at <30 seconds, so won't be affected by anything higher than that
* The max_execution_time setting only applies to SELECTs not any queries that cause data modification
* The only non-web transactions that get anywhere close to say 60 seconds are the calculate_durations task (which is already broken so can be ignored), the runnable_jobs API (occasionally), and possibly parts of cycle data (though I can't tell since that's already broken at the moment it would appear, sigh).

As such I think we're safe to go with a value like 60s with little impact on legitimate queries.

Comment 2

[Ed Morley [:emorley]]

We're now using MySQL 5.7 so can do this :-)

Comment 3

[Ed Morley [:emorley]]

Attachment: devservices-aws PR#65: Limit MySQL SELECT query execution time to 90s

Comment 4

[Kendall Libby [:fubar] (he/him)]

Merged and deployed.

Comment 5

[Ed Morley [:emorley]]

Many thanks!