1457152 - Query search delivers empty results and/or is slow

Status: RESOLVED FIXED

(Data Platform and Tools :: General, defect, P1)

Description

[Jannis Leidel [:jezdez]]

The currently deployed version of Redash (eb1b3cbcfb9fd01bcef5c5f0d70779bd5abb3a5b) is showing unexpected behavior when searching queries.

Bugs were filed here: https://github.com/mozilla/redash/issues/384 and https://github.com/mozilla/redash/issues/385

My gut says that the search vector column wasn't populated correctly during the database migration and may need to be updated by hand.

The migration triggering the column update could help do that in prod:

   https://github.com/mozilla/redash/blob/c151935051a5f6653c57a75115f47528a05ae139/migrations/versions/6b5be7e0a0ef_.py#L23-L38

and for creating the GIN index:

   https://github.com/mozilla/redash/blob/c151935051a5f6653c57a75115f47528a05ae139/migrations/versions/5ec5c84ba61e_.py#L24-L25

That said, assuming the index was correctly created in the past, according to the docs all that needs to happen is retriggering is using the sync_trigger function:

   https://sqlalchemy-searchable.readthedocs.io/en/latest/alembic_migrations.html#sqlalchemy_searchable.sync_trigger

A possible helper:

  import sqlalchemy_searchable as ss
  from redash.models import db

  ss.sync_trigger(db.engine.connect(), 'queries', 'search_vector', ['name', 'description', 'query'])

Comment 1

[Jannis Leidel [:jezdez]]

Hey Blake, do you remember seeing failed migrations in past prod deploys?

Comment 2

[Blake Imsland [:robotblake]]

So the migrations had run successfully, with that said I just re-ran the sync trigger for everything just in case and I'm still seeing errors when searching for "active" or "active profile" but not if I do "active distribution". We're getting errors in Sentry [1] about the gunicorn worker exiting so I wonder if we're hitting a memory limit or some weird exception with one of the queries in that result set. Maybe we can talk early next week about digging in and seeing if we can figure out what's going on.

[1] https://sentry.prod.mozaws.net/operations/data-stmo-prod/issues/3475604/

Comment 3

[Jason Thomas [:jason]]

A request like `https://sql.telemetry.mozilla.org/api/queries/search?include_drafts=true&q=profile` returns HTTP 502 'Bad Gateway', which is what nginx will throw when the upstream service disappears. Since we are using gunicorn it looks like the default request timeout is 30 seconds. Based on that I assuming the query being generated to search the table is taking longer than 30 seconds.

Comment 4

[Jason Thomas [:jason]]

Adding irc conversation for reference:

2018-05-03 15:26:30     jason   I can replicate the issue with 'https://sql.telemetry.mozilla.org/api/queries/search?include_drafts=true&q=test' and tracing the request shows that search query actually returns fairly quickly, but there are a lot of subsequent requests which I am not sure where they are coming from
2018-05-03 15:26:49     jason   https://jason.pastebin.mozilla.org/9084554 is an example
2018-05-03 15:28:06     jason   and so after about 30 seconds the request timeouts and gunicorn process exits
2018-05-03 16:49:06     ~jezdez jason: yeah, I concur, been trying to reproduce and this seems to be basically a too large response set
2018-05-03 16:49:29     ~jezdez been trying to figure out to find the culprit and ways to prevent this from happening


We could increase the gunicorn timeout to something larger but we will eventually hit this issue again as the response set grows larger.

Comment 5

[Jannis Leidel [:jezdez]]

Okay, I've made some progress to finding the culprit, because UI doesn't support pagination we've not limited the number of results when running a search in the backend: https://github.com/mozilla/redash/blob/fc5437fdd8f5240f789a105b9fc381f9d65c4fe9/redash/handlers/queries.py#L60

I've actually reproduced such an unlimited search query using the "re:dash metadata" query runner in Redash itself and it does NOT show signs of slow responses when executing it: https://sql.telemetry.mozilla.org/queries/52857

So my guess right now is that it's an issue when serializing the unlimited number of results on the app side as it does a bunch of recursive serialization of related data: https://github.com/mozilla/redash/blob/fc5437fdd8f5240f789a105b9fc381f9d65c4fe9/redash/models.py#L892-L935

The real solution is to a) limit the queries b) use pagination

I think we can easily have a) implemented quickly to fix the main issues, e.g. to say 50 results.
Not sure about b) on the frontend, I'll keep digging.

Comment 6

[Jason Thomas [:jason]]

Since there is no additional action here from Ops I am marking this fixed. Bug 1459224 to upgrade the STMO RDS instance.