@KOfori this task is considered high priority and we'll need support from Traffic, could you triage and let us know who we can coordinate with?

Change #1078733 had a related patch set uploaded (by Milimetric; author: Milimetric):

[analytics/refinery@master] Shift is_redirect_to_pageview upstream to webrequest

https://gerrit.wikimedia.org/r/1078733

@odimitrijevic: here is the Data Quality report for this issue.

Change #1082543 had a related patch set uploaded (by Mforns; author: Mforns):

[analytics/refinery@master] Modify the automated traffic detection pipeline to include redirects

https://gerrit.wikimedia.org/r/1082543

I finished testing the changes.

The tl;dr is:

• The changes work properly AFAICS.
• About 0.04% of current user pageviews will be marked as automated after this change.
• The data size for the 3 webrequest_actor tables approximately doubles with this change, but to compensate we can reduce their retention period to 1/3 without damage or feature loss, since those are (materialized) intermediate tables.
• It is difficult to confirm to what extent these changes fix the unique devices issue without running it for a large interval of time, which is impractical given that it includes running refine_webrequest.

And below is the long report:

The date I chose is 2024-10-20. I needed to also generate webrequest for most of the previous day to account for the webrequest_actor_metrics_rollup window.
The generated data is under hdfs:///user/mforns/data/wmf, and the tables are in the mforns database. I set permissions for analytics-privatedata-users to be able to read.

refine_webrequest_hourly

spark3-sql \
    --master yarn \
    --executor-memory 12G \
    --executor-cores 2 \
    --driver-memory 4G \
    --driver-cores 1 \
    --conf spark.dynamicAllocation.maxExecutors=128 \
    --conf spark.executor.memoryOverhead=2048 \
    --conf spark.yarn.maxAppAttempts=1 \
    --name test-refine-webrequest-hourly \
    -f refine_webrequest_hourly.hql \
    -d refinery_jar=hdfs:///wmf/refinery/current/artifacts/org/wikimedia/analytics/refinery/refinery-hive-0.2.53-shaded.jar \
    -d source_table=wmf_raw.webrequest \
    -d destination_table=mforns.webrequest \
    -d webrequest_source=text \
    -d record_version=0.0.1 \
    -d coalesce_partitions=256 \
    -d spark_sql_shuffle_partitions=256 \
    -d excluded_row_ids= \
    -d year=2024 \
    -d month=10 \
    -d day=20 \
    -d hour=4

Checked that:

• Select * limit 10 looks good overall
• The number of rows matches production exactly, pageviews count matches too
• The is_redirect_to_pageview field is populated with the right values at the expected proportion
• is_pageview and is_redirect_to_pageview are mutually exclusive
• The pageview_info field is populated also when is_redirect_to_pageview=TRUE

Observations

• 5.5% of webrequests are marked as redirects, whereas 9.7% of webrequests are marked as pageviews. Redirects are 57% the size of pageviews.
• Data size went from 1.4TB to 1.5TB, I imagine it's because now redirect rows have pageview_info populated.
• I didn't need to change the Spark configurations to account for the extra calculation effort of the added is_redirect_to_pageview UDF.
• Generated data LGTM

webrequest_actor_metrics_hourly

spark3-sql \
    --master yarn \
    --executor-memory 12G \
    --executor-cores 2 \
    --driver-memory 4G \
    --driver-cores 1 \
    --conf spark.dynamicAllocation.maxExecutors=128 \
    --conf spark.executor.memoryOverhead=2048 \
    --conf spark.yarn.maxAppAttempts=1 \
    --name test-compute-webrequest-actor-metrics-hourly \
    -f compute_webrequest_actor_metrics_hourly.hql \
    -d refinery_hive_jar_path=hdfs:///wmf/refinery/current/artifacts/refinery-hive-shaded.jar \
    -d source_table=mforns.webrequest \
    -d destination_table=mforns.webrequest_actor_metrics_hourly \
    -d version=0.1 \
    -d year=2024 \
    -d month=10 \
    -d day=20 \
    -d hour=0 \
    -d coalesce_partitions=2

Checked that:

• Select * limit 10 looks good overall
• The row count for is_pageview=TRUE matches production exactly
• The row count for is_redirect_to_pageview makes sense
• is_pageview and is_redirect_to_pageview are mutually exclusive

Observations:

• 53% of rows belong to pageviews, whereas 47% of rows belong to redirects. It seems that redirect webrequests generate more rows (actors) proportionally than pageview webrequests.
• The data size in production is 9.3GB, whereas the new data size after the addition of redirects is 17.8GB. This represents an increase of 91%.
• Note: This data is not used by any other query, it is an intermediate table that we choose to keep materialized for troubleshooting. But instead of keeping 90 days, we could keep i.e. 30 days. This would compensate the size increase.
• Generated data LGTM

webrequest_actor_metrics_rollup_hourly

spark3-sql \
    --master yarn \
    --executor-memory 8G \
    --executor-cores 2 \
    --driver-memory 4G \
    --driver-cores 1 \
    --conf spark.dynamicAllocation.maxExecutors=64 \
    --conf spark.executor.memoryOverhead=2048 \
    --conf spark.yarn.maxAppAttempts=1 \
    --name test-compute-webrequest-actor-metrics-rollup-hourly \
    -f compute_webrequest_actor_metrics_rollup_hourly.hql \
    -d source_table=mforns.webrequest_actor_metrics_hourly \
    -d destination_table=mforns.webrequest_actor_metrics_rollup_hourly \
    -d version=0.1 \
    -d interval_start_year=2024 \
    -d interval_start_month=10 \
    -d interval_start_day=19 \
    -d interval_start_hour=22 \
    -d interval_end_year=2024 \
    -d interval_end_month=10 \
    -d interval_end_day=20 \
    -d interval_end_hour=21 \
    -d coalesce_partitions=16

Checked that:

• Select * limit 10 looks good overall
• The row count for is_pageview=TRUE matches production exactly
• The row count for is_redirect_to_pageview makes sense
• is_pageview and is_redirect_to_pageview are mutually exclusive

Observations:

• The proportion of pageviews vs redirects from webrequest_actor_metrics_hourly is roughly maintained: 51.5% vs 48.5%.
• Data size goes from 139GB in production to 269GB after changes. 93% increase.
• Note: This data is not used by any other query, it is an intermediate table that we choose to keep materialized for troubleshooting. But instead of keeping 90 days, we could keep i.e. 30 days. This would compensate the size increase.
• Generated data LGTM

webrequest_actor_label_hourly

spark3-sql \
    --master yarn \
    --executor-memory 16G \
    --executor-cores 4 \
    --driver-memory 4G \
    --driver-cores 2 \
    --conf spark.dynamicAllocation.maxExecutors=64 \
    --conf spark.executor.memoryOverhead=2048 \
    --conf spark.yarn.maxAppAttempts=1 \
    --name test-compute-webrequest-actor-label-hourly \
    -f compute_webrequest_actor_label_hourly.hql \
    -d source_table=mforns.webrequest_actor_metrics_rollup_hourly \
    -d destination_table=mforns.webrequest_actor_label_hourly \
    -d version=0.1 \
    -d year=2024 \
    -d month=10 \
    -d day=20 \
    -d hour=23 \
    -d coalesce_partitions=16

• Select * limit 10 looks good overall
• Row counts for user, automated and unclassified (for is_pageview=TRUE) match production exactly
• Row counts for user, automated and unclassified (for is_redirect_to_pageview=TRUE) make sense
• is_pageview and is_redirect_to_pageview are mutually exclusive

Observations:

• The amount of redirect rows matches the previous proportions.
• The split user, automated, unclassified for the redirect rows is very similar to the pageviews one, except the unclassified row count is about 1/3 the size (It seems redirects are easier to classify?).
• The data size goes from 125GB in prod to 246GB after changes. 97% increase.
• Note: This data is not used by any other query, it is an intermediate table that we choose to keep materialized for troubleshooting. But instead of keeping 90 days, we could keep i.e. 30 days. This would compensate the size increase.
• Generated data LGTM

pageview_actor

spark3-sql \
    --master yarn \
    --executor-memory 16G \
    --executor-cores 4 \
    --driver-memory 4G \
    --driver-cores 2 \
    --conf spark.dynamicAllocation.maxExecutors=128 \
    --conf spark.executor.memoryOverhead=2048 \
    --conf spark.yarn.maxAppAttempts=1 \
    --name test-pageview-actor \
    -f pageview_actor.hql \
    -d refinery_hive_jar_path=hdfs:///wmf/refinery/current/artifacts/refinery-hive-shaded.jar \
    -d source_table=mforns.webrequest \
    -d actor_label_table=mforns.webrequest_actor_label_hourly \
    -d destination_table=mforns.pageview_actor \
    -d year=2024 \
    -d month=10 \
    -d day=20 \
    -d hour=23 \
    -d coalesce_partitions=32

Checked that:

• Select * limit 10 looks good overall
• The overall row count, and the split is_pageview vs is_redirect_to_pageview match production exactly
• spider counts match production exactly
• The changes in the split user vs automated make sense

Observations:

• 2% of current user rows will be marked as automated after the change (those correspond mostly to redirects)
• If we look at pageviews only, only 0.04% of current user pageviews will be marked as automated after the change
• Data size went from 163GB to 172GB. I imagine it's because of the pageview_info field being populated for redirect rows.
• Generated data LGTM

pageview_hourly

This dataset has not been modified, nor is in the unique devices pipeline, but I wanted to see how much the changes affect it.

• The row count drops a bit <0.001%, which makes sense, since the changes in the agent_type field might affect how the rows are aggregated.
• The overall aggregated view_count matches production exactly, as well as spider view_counts.
• The percentage of current user pageviews that got transfered to automated with the change are the same as with pageview_actor (expectedly) 0.04%.

unique_devices_per_project_family_daily

spark3-submit \
    --name test-unique-devices-per-project-family-daily \
    --master yarn \
    --deploy-mode cluster \
    --driver-cores 1 \
    --driver-memory 4G \
    --executor-cores 2 \
    --executor-memory 8G \
    --conf spark.dynamicAllocation.maxExecutors=32 \
    --conf spark.yarn.executor.memoryOverhead=2048 \
    --conf spark.sql.shuffle.partitions=512 \
    --conf spark.yarn.maxAppAttempts=1 \
    --conf spark.yarn.archive=hdfs://analytics-hadoop/user/spark/share/lib/spark-3.1.2-assembly.jar \
    --class org.apache.spark.sql.hive.thriftserver.SparkSQLNoCLIDriver \
    hdfs://analytics-hadoop/wmf/refinery/current/artifacts/refinery-job-shaded.jar \
    -f hdfs://analytics-hadoop/user/mforns/unique_devices_per_project_family_daily.hql \
    -d source_table=mforns.pageview_actor \
    -d destination_table=mforns.unique_devices_per_project_family_daily \
    -d countries_table=canonical_data.countries \
    -d year=2024 \
    -d month=10 \
    -d day=20 \
    -d coalesce_partitions=1

Checked that:

• Select * limit 10 looks good overall
• Row count matches production, expected since they are project_family x country buckets.
• Aggregated value changes for uniques_underestimate, uniques_offset and uniques_estimate make sense.

Observations:

• unique_underestimate decreases by 0.5% overall after applying the changes to the pipeline. Note this is just for 1 day (2024-10-20).
• It is difficult to determine to which extent these changes solve the unique devices issue at large. To do that we'd need to generate a couple weeks (months?) of data and compare it with production metrics.
• However, the fact that we ended up modifying the refine_webrequest_hourly job, means we would need to re-run weeks (months?) of webrequest to be able to do a proper test, and this is a bit unrealistic.
• Joseph, however, did some tests targeting the days with a higher impact of the issue, with positive results, see https://phabricator.wikimedia.org/T376196#10230682

thank you @mforns for the detailed testing and for sharing the results here.

I agree that 0.5% is smaller than what we are expecting in the daily data as we were expecting to see a 4-8% drop in the overall monthly numbers. I believe for that to happen we may have to prioritize T377257
and see the impact.

Change #1078733 merged by Joal:

[analytics/refinery@master] Shift is_redirect_to_pageview upstream to webrequest

https://gerrit.wikimedia.org/r/1078733

Change #1082543 merged by Joal:

[analytics/refinery@master] Modify the automated traffic detection pipeline to include redirects

https://gerrit.wikimedia.org/r/1082543

The code has been deployed today.
First job runs have been successful, I'll monitor data in the next days to verify nothing crazy wrong goes on.

@VirginiaPoundstone I see that this task was 'resolved' but as a reminder, there are still outstanding actions related to this issue in T377257 and a required backfill of the data (T378852) - More details in my latest status update