It seems that in some cases the current automatic query killer gets stuck or stalled when under high load, which arguably is when we need the query killer the most. Would having an external query killer (pt-kill?) help here? Are there other options to make this more robust/reliable?
Description
Description
| Status | Subtype | Assigned | Task | ||
|---|---|---|---|---|---|
| Resolved | Ladsgroup | T293530 Improve slow read query handling | |||
| Open | None | T293532 Improve automatic query killer under high load |
Event Timeline
Comment Actions
I wouldn't like to have an external process running all the time "just in case" and from what I have seen during outages, pt-kill struggles too when there's high load, so it might even add more load than reduce it.
I believe this is partially mitigated with @jcrespo's db-kill command available on cumin* hosts.
Comment Actions
The external process was just a suggestion. To clearly state the problem: from my POV, I expect the query killer to automatically stop any query that runs over 60 seconds. In some outages, it hasn't worked and we've had to go in manually to kill those slow queries and then stuff recovers. My thinking is that if those queries had automatically been killed, manual intervention may not have been required.
So what needs to be done to get the query killer to a state that it functions under high load? Is that simply infeasible? Or are client-side things like T293536: MediaWiki should support setting a read query time limit going to be more impactful?
...and from what I have seen during outages, pt-kill struggles too when there's high load, so it might even add more load than reduce it.
I believe this is partially mitigated with @jcrespo's db-kill command available on cumin* hosts.
I'm not really sure how to reconcile these two statements given db-kill is just a wrapper around pt-kill.
Comment Actions
The only real solution is getting the client to set per-query timeouts.
...and from what I have seen during outages, pt-kill struggles too when there's high load, so it might even add more load than reduce it.
I believe this is partially mitigated with @jcrespo's db-kill command available on cumin* hosts.
I'm not really sure how to reconcile these two statements given db-kill is just a wrapper around pt-kill.
Manuel is referring to running pt-kill permanently instead of the query killer. db-kill is intended to be used for an emergency, for a limited duration.
Comment Actions
The only server-side thing we can tweak further is reducing the query killer time (meaning being more aggressive, but that might not fully prevent outages it might simply delay them a few minutes, which already has its own task: T293533:). The query killer simply cannot kill fast enough and that problem has always been there (we are simply seeing it more often now with those incidents), but there's no way to help it.
Exactly
Comment Actions
I use this opportunity to write down something I have been pitching to basically everyone. Use PID controller in query killer.
The current state is that basically an arbitrary threshold has been chosen (60 seconds) and if the load goes higher (to yet another arbitrary threshold), it reduces the value to, you guessed it, another arbitrary threshold.
One problem is that it doesn't take into account how the load is changing (derivative) and this crucial given that reducing threshold doesn't immediately lead to lower threshold (killing a query takes some time, in high load even a minute) which is exactly what PID controller (and other control systems) are built for.