[ctide]

I'm guessing MySQL doesn't support this (hence the need for a cron job), but postgres lets you set a statement_timeout on the connection. It will force kill queries that go beyond that timeout. I worked on an app not too long enough that occasionally would have some queries go off the rails and start blocking everything. We set up postgres to just kill off anything taking 30s automatically, and then were able to root out the issues without worrying about everything blocking on these broken queries and taking down our systems.

[greenleafjacob]

Author here. We thought about using statement_timeout, but we didn't like the lack of flexibility when we do have long-running queries that aren't necessarily deleterious to performance. Instead we opted to use two different users ("cron" for long running jobs, and a normal read/write user pair for normal) that the long query killer script will kill at different timeouts, effectively implementing a per-user timeout rather than a global timeout.

I bet the best approach might be to have the statement_timeout be the largest of all of your per-user timeouts (in case your watchdog script fails, can't connect, etc. for whatever reason).

[e12e]

I've not tried it, but according to PostgreSQL documentation, this should be possible (and recommended) to set on a per role basis?

> The ALTER ROLE command allows both global and per-database settings to be overridden with user-specific values. (...)

> The SET command allows modification of the current value of those parameters that can be set locally to a session; it has no effect on other sessions. The corresponding function is set_config(setting_name, new_value, is_local). (...)

> Setting statement_timeout in postgresql.conf is not recommended because it would affect all sessions.

[nickpsecurity]

Clever hack.

[majormajor]

If that's set on the connection, could a misbehaving/poorly-written client still give you trouble (obviously it would be better to not have any other teams connecting directly to your DBs, but have to you work with what you find in place, sometimes...)? Or is this something specified at the DB side of the connection, not the client?

My practice with long-running query killer scripts is to have them ignore queries that are known to be long by running those queries on a specific user/machine(s) and then hoarding and protecting those credentials.

[zytek]

For MySQL, this is one of many things handled by excellent Percona Toolkit: pt-kill