1303433 - "title" filter on jobdetail endpoint can generate very slow queries

Status: RESOLVED FIXED

(Tree Management :: Treeherder, defect)

Description

[William Lachance (:wlach)]

For bug 1301729 I added the ability to filter on a job detail's title, in addition to id/guid. Unfortunately if you access this endpoint *without* also specifying a job, this can be extremely slow (because it tries to find the matching items and then order them by id, and there are no indexes to speed this operation up). 

It seems like in the last day someone has fired off a bunch of queries with these characteristics (for experimentation?), which led to production slowing down. 

Options to make sure this doesn't happen:

1. Remove the name filter and reimplement this filtering client side (a little less satisfying, but should fix issue)
2. Require the query to request either an id or guid

However, I have to wonder if there's also a deeper issue going on here, in that a client can fire off queries that can easily bring the db to its knees. At the very least, shouldn't we be able to time out queries which come from the treeherder webheads. It's not like there's any value in continuing to run the query when the web request has already timed out!

Comment 1

[GitHub Autolander Bot]

Attachment: [treeherder] wlach:1303433 > mozilla:master

Comment 2

[William Lachance (:wlach)]

Comment on attachment 8792102 [details] [review]
[treeherder] wlach:1303433 > mozilla:master

I'm just going to go ahead and merge this and push to production, since it's a serious issue. However, a review couldn't hurt (for broadening familiarity with the issue, at least).

Comment 3

[Treeherder GitHub Bugbot]

Commit pushed to master at https://github.com/mozilla/treeherder

https://github.com/mozilla/treeherder/commit/a4cf29f966cd1c50104c58dbe69b3dafbac63c99
Bug 1303433 - Fix cancelling a single job from the UI (#1861)

The /jobdetail/ endpoint doesn't currently support filtering by job id,
we need to pass the guid instead.