1611249 - Queries in GLAM dag scan ~80TB per day

Status: RESOLVED WORKSFORME

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

Description

[Ben Wu [:benwu]]

The queries in the glam dag run daily and scan about 80TB per day which accounts for almost all of the data scanned by airflow dags. It appears that some of the queries are scanning though all of history everyday and aren't taking advantage of partitioning/clustering. It would be worth seeing if we can make better use of clustering or make the queries incremental but this may require rethinking the entire query. Another optimization would be to reduce the number of intermediate tables created if possible but they may be necessary due to query complexity.

If it turns out the data scanned can't be reduced significantly, this dag may be a candidate to run on reserved slots, assuming it would complete in a day.

Comment 1

[Mark Reid [:mreid]]

Marina, is this something you're already looking into?

Comment 2

[Marina Samuel [:emtwo]]

I have not yet started to investigate how much we can reduce the cost.

I explained here https://bugzilla.mozilla.org/show_bug.cgi?id=1609772#c4 which queries are clustered/filtered and which aren't. In discussion with Rob, it sounds like this is lower priority relative to fenix + final touches on desktop data for Glam. If someone else has cycles to look at this sooner, that'd be great! If not, I think I may get to it in a few weeks, once we have the fenix timeline figured out.

Comment 3

[Marina Samuel [:emtwo]]

Btw. 1 potential low hanging fruit could be to merge clients_scalar_aggregates_v1 + clients_scalar_bucket_counts_v1 and clients_histogram_aggregates_v1 + clients_histogram_bucket_counts_v1 into the same query.

The reason I separated them initially was due to "complex query" errors and timeouts. The queries have all changed a bunch since then so they may be easier to merge now. This is simple enough that I could try it out relatively soon.

Comment 4

[Ben Wu [:benwu]]

Less now but still a lot: https://sql.telemetry.mozilla.org/queries/79047/source