Overview

The loading speed of CCM Perspectives was noticeably slower than typical. This issue resulted in longer load times for all CCM features that utilized BigQuery, such as Perspectives, Dashboards, Overview, etc. However, the most significant reports were related to Perspectives.

Timeline (UTC)

Time Event 2023-09-11 02:18 PM The issue was initially reported, and the engineering team began investigating it. 2023-09-11 02:48 PM An internal Firehydrant incident was initiated, and PagerDuty alerts were triggered. 2023-09-11 02:59 PM BigQuery maximum reservation size was increased from 4500 to 9000 slots 2023-09-11 03:45 PM Investigation was complete after identifying the root cause for increased slot consumption in BigQuery and confirming the same with evidence from our BigQuery-Slot-Utilisation-Dashboard 2023-09-11 04:02 PM Config override (to disable the culprit query) was merged and batch-processing was restarted with the updated config 2023-09-11 04:23 PM Verifications were done to ensure the load times are under control and field team was notified of the resolution.

Resolution

The maximum reservation size for BigQuery was raised from 4500 to 9000 slots. Queries causing the issue were disabled, and the service was subsequently restarted.

Affected Users

Users in prod1 and prod2 who were using Perspectives were affected by this incident.

RCA

In a modification to batch processing, we incorporated a periodic job that queried the foundational BigQuery database. Despite the smaller data size in BigQuery, this affected slot usage. In response, GCP initiated a feature request, suggesting that there might be an issue with their slot allocation.