1500473 - Add monitoring for the re:dash query queue

Status: RESOLVED WONTFIX

(Data Platform and Tools :: Monitoring & Alerting, enhancement)

Description

[Mark Reid [:mreid]]

As a user of STMO, it can be frustrating when a query that is normally quick to run gets stuck in the queue and ends up taking much longer.

We should add some monitoring to get a sense of the impact of queueing on STMO users.

Comment 1

[Jason Thomas [:jason]]

We already have monitoring for queues and alert based on thresholds. I would give you the link to the datadog page but the page is down right now :\

I don't think we need additional monitoring here, I think we need to provide feedback to users when their jobs are queued and possibly the position they are in the queue.

Comment 2

[Jason Thomas [:jason]]

https://app.datadoghq.com/dash/336190/stmo-sqltelemetrymozillaorg-prod?live=true&page=0&is_auto=false&from_ts=1537372055433&to_ts=1539964055433&tile_size=m&fullscreen=226527315

Comment 3

[Mark Reid [:mreid]]

Which of these series corresponds to the queue for interactive querying? As I read it, it looks like "redash.queues.queries", but that consistently shows zero.

Comment 4

[Jason Thomas [:jason]]

big_queries = athena interactive queries, specifically, Athena and Athena Search data sources
presto_queries = presto interactive queries, specifically, Presto and Presto Search data sources

If you mouse over the tool tip it will list which data source is associated with the queue.

There is a 'waiting' queue but I can't really find much documentation how this relates to actual scheduling [1]. Even though some queues are empty there are query tasks in the 'waiting' queue. I am assuming queries get pulled off normal queues and put into the waiting queue when they are about to get executed. CC'ing :jezdez for possible insight.

[1] https://sql.telemetry.mozilla.org/admin/queries/tasks#waiting

Comment 5

[bugzilla]

Is it possible to quantify some sort of average time in queue? Queue length is one thing, but I do feel like length of time in queue is a better barometer of user impact and queue times can change fairly dramatically even though the query size stays consistent if our average query time grows

Comment 6

[Mark Reid [:mreid]]

Just got another report on slack of queries being stuck in queue. All the queues shown on under "Queues" on the Admin Status page[1] are showing zero, but there are several queries that appear in the "Queries Queue" page[2].

This query queue can be monitored using the Redash API at [3], is that something we can easily add to DataDog? Inside that JSON response, we're interested in tracking the length of the "waiting" element.

It would also be interesting to keep track of the age of the oldest item in that array as an indicator of "time in queue" per Sunah's suggestion above.

[1] https://sql.telemetry.mozilla.org/admin/status
[2] https://sql.telemetry.mozilla.org/admin/queries/tasks#waiting
[3] https://sql.telemetry.mozilla.org/api/admin/queries/tasks

Comment 7

[Arkadiusz Komarzewski [:akomar]]

I can't verify if that's the case here (don't see how many workers we have), but this sounds somewhat similar: https://github.com/getredash/redash/issues/1782#issuecomment-313104463

tldr: by default Celery worker is prefetching 4 tasks at a time, if the 1st one is long running (scheduled queries?) then others need to wait.

Comment 8

[Jason Thomas [:jason]]

(In reply to Mark Reid [:mreid] from comment #6)

This query queue can be monitored using the Redash API at [3], is that
something we can easily add to DataDog? Inside that JSON response, we're
interested in tracking the length of the "waiting" element.

It would also be interesting to keep track of the age of the oldest item in
that array as an indicator of "time in queue" per Sunah's suggestion above.

[1] https://sql.telemetry.mozilla.org/admin/status
[2] https://sql.telemetry.mozilla.org/admin/queries/tasks#waiting
[3] https://sql.telemetry.mozilla.org/api/admin/queries/tasks

Thanks I didn't realize that this data was available. We should be able to poll this, add it into datadog and add alerting around it.

A large waiting queue may indicate data sources are overloaded and based on that we can figure out what to do next.

Comment 9

[Jeff Klukas [:klukas] (UTC-4)]

We won't be putting further investment into redash in favor of Looker.