After the whole "slow resultsdb" debacle, it seems wise to start doing slow query logging on our database host. However, I'm still not sure where we want to set the threshold for "slow" queries.
The problematic queries we saw with resultsdb in #452 seemed to start around 250ms on my local db host during testing but that seems a bit low to start logging entire queries. Figure out a good threshold and get the config changes deployed to our database host.
@tflink from what I have seen, the (I'm inclining to saying "only" but I know better than that) long query still left is getting the jobs sorted by date (aka rendering the front page of resultsdb_frontend). But that is taking ~ 60 ms (on my machine, with the data from your dump), and it is not that frequent of a query IMHO. I think that whenever query takes more than 150/200 ms, it is worth looking into it.
Also - it might be worth logging all the queries for some time (a week or two), to get the overview of what is generally going on in the DB. There are some nice tools for creating reports, e.g. http://dalibo.github.io/pgbadger/ (sample report: http://dalibo.github.io/pgbadger/samplev4.html ). But that is IMHO not something we need to do during the freeze.
Are we still interested in logging queries? We had some recent slow-downs because of programmatical based "error" in the ResultsDB (16+seconds to grab the front page), but that was feel-able from the user experience. Would have been great to have the queries logged, but in the end I still needed to get my hands dirty to find where the problem was - the queries itselves, out of the execution context, would probably not be enough anyway.
How do we stand on this?
Metadata Update from @tflink: - Issue tagged with: infrastructure
Login to comment on this ticket.