1609772 - Determine on-demand vs reserved cost of bigquery-etl

Status: RESOLVED FIXED

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

Description

[Daniel Thorn [:relud]]

on-demand cost can be determined by dry-running every query in bigquery-etl job that is also used in telemetry-airflow and summing the data scanned.

reserved cost would be $284/day for 500 slots. if on-demand costs exceed $284/day then we can run a day of telemetry-airflow with the billing project set to moz-fx-data-bq-batch-prod, and compare how long that takes vs on-demand to determine if 500 slots is sufficient. This needs to happen before the morning of Tuesday Jan 22nd (note that Jan 21st is a holiday), at which time shredder will be going live and need all of the slots in moz-fx-data-bq-batch-prod.

complications: some queries require special permissions, or take advantage of clustering, in which case ops may be needed to collect the bytes billed from existing actual runs.

Comment 1

[Daniel Thorn [:relud]]

also script/dryrun contains information about what queries need to be dry run, including the project they should be run in. the missing query parameter for experiments_v1 is an array of strings, and dry-running it with a non-empty array, like [""] should get a valid estimate.

Comment 2

[Ben Wu [:benwu]]

It took a little longer than expected because of all the special cases but the number I got is 90.74 TB for all the bigquery_etl_query calls in airflow on 2020-01-10. The total for every query (no init.sql) in the bigquery-etl run on 2020-01-10 which was 87.5 TB. The difference is because sql/telemetry_derived/fxa_users_services_first_seen_v1/init.sql is being run in airflow and it scans 3 TB.

The scripts I put together can be found here https://gist.github.com/Ben-Wu/df81460ba0e6ea6c5677a23f02319c8a. There are some assumptions made like all queries are in bigquery_etl_query function calls and all are run once per day.

The only queries I couldn't run are the ones that use tables in the fxa prod project. Either way the total cost of the queries are more than the cost of reserved slots. However, almost all of the data scanned is done by queries in the glam dag:

e.g.
sql/telemetry_derived/clients_scalar_aggregates_v1/query.sql 12.79 TB
sql/telemetry_derived/clients_histogram_bucket_counts_v1/query.sql 20.52 TB
sql/telemetry_derived/clients_scalar_bucket_counts_v1/query.sql 7.68 TB
sql/telemetry_derived/clients_histogram_aggregates_v1/query.sql 22.68 TB
sql/telemetry_derived/scalar_percentiles_v1/query.sql 7.68 TB

It would probably be worth seeing if we can reduce these before trying a real day run on reserved slots because it might not be worth using reserved slots at all. It doesn't look like these queries are taking advantage of clustering so that would be the real data scanned. I haven't taken a deep look at the queries yet but I will verify this and ask appropriate people if needed next week.
This probably won't happen before shredder goes online though.

Comment 3

[Ben Wu [:benwu]]

Marina, can you give an overview on what the queries in the glam dag are doing? And is there any way you can think of that can reduce the total data scanned? The more obvious things would be to make the queries run incrementally per day or see if there's any redundancy in computing the tables, which may have already been considered.

Right now all the queries combined are scanning close to 80 TB per day which seems suspiciously high. Also do you know if the data scanned is actually less due to clustering?

Comment 4

[Marina Samuel [:emtwo]]

I will look into what can be done to reduce the data scanned. However....

As of now, the following tables from those mentioned above are partitioned and clustered already and they also reference partitioned/clustered tables:

• sql/telemetry_derived/clients_scalar_aggregates_v1/query.sql
• sql/telemetry_derived/clients_histogram_aggregates_v1/query.sql

The other 3 tables mentioned above query data from these 2 clustered tables, but they themselves are not clustered. So basically every query listed here is querying a clustered table.

I think we may need to ask ops about the true bytes scanned from the runs in this case?

Comment 5

[Marina Samuel [:emtwo]]

Ok I just took a closer look and actually 3 of these tables query clustered tables but do not filter appropriately so I will need to fix that.

In the meantime, I have paused the glam dag.

Comment 6

[Ben Wu [:benwu]]

We now have moz-fx-data-shared-prod.monitoring_derived.bigquery_etl_scheduled_queries_cost_v1 if we want to check this
e.g.

SELECT
  SUM(cost_usd)
FROM
  `moz-fx-data-shared-prod.monitoring_derived.bigquery_etl_scheduled_queries_cost_v1`
WHERE
  submission_date = "2021-03-25"

Depending on how many slots are needed, reserved slots could possibly save money especially now with 100 slot increments and flex slots https://cloud.google.com/bigquery/pricing#flat-rate_pricing