search

mozilla / redash

This is a Mozilla fork of the re:dash project (https://redash.io/), where we do work to be contributed back to the upstream project and for our own custom needs.
BSD 2-Clause "Simplified" License
20 stars 21 forks source link

Add a few database indices and a caching layer for schema browser tables. #995

Closed jezdez closed 4 years ago

jezdez commented 4 years ago

Refs https://bugzilla.mozilla.org/show_bug.cgi?id=1596252.

Those indices should reduce the query time drastically since so far the queries have been scanning all rows on every query which is incredibly expensive given the use of these tables in the schema browser.

jezdez commented 4 years ago

@jasonthomas To verify this we could also create the indices in staging manually.

jezdez commented 4 years ago

Not yet, I had to reset Docker and now the metadata is being fetched in the new setup. More later..

jezdez commented 4 years ago

This contains a bit more that allowed me to reduce the response time to <500ms for the schema view by adding a separate Redis cache step that retains the data fetched from the database. The amount of data sent to the client is still staggering considering the number of columns and tables it serializes, bit it'll at least fix the issues in a way that is scalable.

@jasonthomas Important bit: This will also make refreshing the schema via the UI use locking since so far the code was written in a way that would trigger updating all schema from the data source API (e.g. Big Query) every time a user click on the "refresh schema" button. There is likely a lot less API traffic against Big Query with this applied.

emtwo commented 4 years ago

I tried out this branch locally with my moz-fx-data-derived-datasets json key file. @jezdez is this the project you were testing with locally or something else?

I couldn't locally reproduce the complete blank schema browser like we see in prod, but I could tell that this PR made load time faster.

jezdez commented 4 years ago

@emtwo Yah, I used that json key file, that's where I saw the view timeout locally. I've just bumped up my docker config quite a bit and with enough memory 6GB it doesn't fail anymore, just shows that the query previous query was ineffective about memory.