Closed Bug 1635790 Opened 6 years ago Closed 1 year ago

Investigate options for reducing column count in main_v4

Categories

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

Product:
Data Platform and Tools
Component:
General
Type:
task
Points:
5

Tracking

(Not tracked)

Status:
RESOLVED FIXED

People

(Reporter: klukas, Unassigned)

Details

Per a query developed for us by Google support, we are about to pass the 9000 column mark in main_v4 and we know there is a hard limit at 10,000 columns.

As a first order of business, we need to determine:

  • Rough estimate of how long we have until we hit 10k columns based on history of adding new fields; we can likely figure this out from history of the generated-schemas branch of mozilla-pipeline-schemas, or from looking at probe info service graphs; note that we tend to add probes in large chunks for new releases, so perhaps the most important thing is to do is identify the handful of largest probe increase events over the past year

We then need to get a sense of what it would look like operationally to delete/reconfigure existing fields in main_v4:

  • Could we provision a new main_v5 table and update the pipeline to point at the new one? Would we be able to update the telemetry.main view to union the two, ignoring some fields we consider deprecated, or does this run into query complexity / view size issues?

And finally, we need to do creative investigation of options for reducing the column count. Some options:

  • Reenable table splitting, so we have multiple main ping tables with different subsets of probes
  • Drop older probes without otherwise changing structure
  • Group together related discrete histograms into a single keyed histogram
  • Let's think through lots of other ideas

The main goal here should be to develop either a repeatable process such that we can drop expired probes on some regular cadence without breaking the world, or to introduce a reimagined structured that consolidates column count far enough that it will be able to handle new probes for many years.

One of the things to consider is that the current shape of the table is effectively the API for Desktop GLAM. The script for generating the clients daily histogram aggregates walks the tree looking for valid probes in the data. One problem in GLAM is that there are a lot of low value probes, whether they be from old versions or non-existent processes. I think a sustainable solution to this in the medium-long term as we wait for FOG (Firefox on Glean) to become the primary mechanism of data collection in the browser is to prune old probes. This would allow current jobs and analyses to rely on the current structure of the ping without having to migrate to yet another table.

Pruning would involve removing a large portion of the expired probes and unused process types, which will require a version bump. The easiest thing to do would be to limit the history on the set of probes that we are materializing. The current schema has been artificially set to collect probes since Firefox 30. Moving this to a more recent release would save on a number of columns. There are also likely probes reported in several processes with data only being collected in a single process. Bug 1423446 provides several examples of probes that may exhibit this behavior. Counting the number of empty columns would also be useful for building channel specific versions of the main ping as proposed in mozilla/gcp-ingestion#1216.

We should also consider adding additional_properties into nested columns. The histograms, scalars, and processes section of the main ping are structured well enough that inserting additional properties would provide value for both table migrations (e.g. v5 -> v6) and for end users. If we are pruning probes, then the structure of the overall ping will remain the same, with the values inside of the histogram, scalars, and processes changing based on the output of the probe info service. If a probe is an additional field, adding it to additional properties would make it easily accessible even when it doesnt fall inside the structured portion of the ping. Since it exists in a nested field, it should be much cheaper to scan. It can be used to create compatible views between two versions of the main ping. Finally, it would provide access to dynamic probes, which have been difficult to work with in the raw ping tables.

We are looking at a time-scale of 2-3 years before we can actually deprecate the main ping, and we will continue to receive data for the foreseeable future. Pruning expired probes on a regular basis may provide us with enough leeway to avoid the 10k column limit. We can prune columns on an ongoing basis, with a process that is repeatable to do once every few releases.

Use counters

Use counters make up 4128 of the 9000 fields in main. Of those, 2584 of them are related to CSS (use_counter2_css*), so we may want to focus our efforts specifically on use counters.

Of these 4128 fields, 952 appear to always be null based on looking at 1 hour of main ping data from a recent day. For future reference, the query for nulls was generated by the following incantation:

( echo 'with a as (SELECT [' && bq show --schema moz-fx-data-shared-prod:telemetry_stable.main_v4 | jq -r '.[] | recurse(.fields[]? + {parent: ((.parent? // []) + [.name])}) | .parent + [.name] | join(".")' | grep 'use_counter2' | sed 's/\(.*\)/struct("\1" as key, count(\1) AS value)/' | paste -d, -s - && echo '] as arr FROM `moz-fx-data-backfill-4.telemetry_stable.main_control`) select key from a, unnest(arr) WHERE value = 0' ) | pbcopy

Each of these fields is a "type 2" histogram, which appears to be boolean, so can be encoded as simply a count for the "0" bucket and a count for the "1" bucket. We may be able to "pack" many of these fields together in an efficient string encoding. The names are long, so I want to avoid encoding the names as string keys, as this is many wasted bytes. We may want to pack this with ordering being significant, and then have UDFs that are able to assign logical names based on the order.

(In reply to Jeff Klukas [:klukas] (UTC-4) from comment #2)

Use counters

Use counters make up 4128 of the 9000 fields in main. Of those, 2584 of them are related to CSS (use_counter2_css*), so we may want to focus our efforts specifically on use counters.

Of these 4128 fields, 952 appear to always be null based on looking at 1 hour of main ping data from a recent day.

Each of these fields is a "type 2" histogram, which appears to be boolean, so can be encoded as simply a count for the "0" bucket and a count for the "1" bucket.

[...]

We may be able to "pack" many of these fields together in an efficient string encoding. The names are long, so I want to avoid encoding the names as string keys, as this is many wasted bytes. We may want to pack this with ordering being significant, and then have UDFs that are able to assign logical names based on the order.

I checked on the contents of all 4128 use counters for 2020-05-18 and the average number of use counters reported per ping was only 330, or ~8%.

I also found that unlike other "type 2" histograms, use counters only reported non-zero values in the "1" bucket (with 2 exceptions).

Some ways we could pack this, assuming we only need the "1" bucket:

with any counter value:

Pack all use counters together as an ordered CSV, it would use 4128-1 separators, 330 values (assuming an average size of 1 byte), and 2 bytes overhead per string, or 4459 bytes per row, and that would reduce the 8,557 GiB of use counters reported on 2020-05-18 into a single 1,275 GiB column, but we would lose the ability to scan a single use counter, which currently averages only 2 GiB.

Pack use counters into a list of key-value pairs, then it could be 330 values*(fixed 4 byte decimal key (no key-value separator needed) + avg 1 byte value + 1 byte separator) - 1 byte for no trailing separator + 2 bytes string overhead => 1981 bytes per row => one 566 GiB column.

Split that into 45 columns with 100 values per column, then we could reduce the key size to fixed 2 bytes, then it could be 330 values*(2-byte key + 1 byte key + 1 byte pair separator) - 1 byte for no trailing separator + (2 bytes string overhead * 45 columns) => 1409 bytes per row => 403GiB, 9GiB per column.

with a fixed 1 or 2 byte counter value (i.e. imposing a max of 256 or 65536):

Pack use counters into an array of integers, with a fixed 2 bytes per key and 2 bytes per value, and 2 key-value pairs per element, then it could be 330 values *(2 byte key + 2 byte value) => 1320 bytes per row => one 377 GiB column.

Pack use counters into 17 columns, each a byte string with fixed 1-byte keys and 2-byte values, then it could be 330 values * (1 byte key + 2 byte value) + 17 columns * (2 byte overhead per byte string) => 1024 bytes per row => 293GiB, 17GiB per column.

Pack use counters into 17 columns, each an array of integers, 1-byte keys and 1-byte values, then it could be 330 values * (1 byte key + 1 byte value) => 189 GiB, 11GiB per column.

I chatted with :tdsmith about use counters, and he indicates that there are no current dashboarding use cases on top of them, so radically changing the representation in main pings would not cause a burden. There is some interest in building automated reporting around use counters in the future which may evolve from DS-281.

Outline of operational process for populating tables with packed use counters

We would need to prep the following in advance:

  • Changes to mozilla-schema-generator to produce a stable listing of use counters (based on when they were added) and to replace use counter fields in main pings with a few packed fields
    • This would also need to address somehow publishing the list of counters alongside the schema tarball
  • Changes to gcp-ingestion for sinks to load the use counter listing and using that to materialize the packed use counter fields
  • Stage a main_v5 table with the new schema with packed use counter fields
  • Script for generating a Shredder-compatible query for reading from main_v4 and writing to main_v5
  • Stage a main_use_counters view that parses out concrete use counter fields from the packed representation and ignores all other probes

When we're ready to execute the change:

  • Stop the schema generation pipeline
  • Initiate a Shredder run for main pings, but populating main_v5 using the custom query rather than overwriting main_v4
  • Wait several days for the Shredder process to complete
  • Validate that the contents of main_v5 are complete such that we're comfortable dropping main_v4

Then final steps:

  • Stop the production live sinks, letting them drain
  • Drop the telemetry_live.main_v4 table and recreate with the new schema with packed use counters
  • Restart the production live sinks
  • Temporarily update the telemetry.main view to reference main_v5
  • Drop the telemetry_stable.main_v4 table and copy main_v5 into its place
  • Drop main_v5
  • Update the telemetry.main view to point to the normal main_v4 and also deploy the new main_use_counters view

At this point, we should be good to go, with copy_deduplicate seeing the updated schemas for both tables, etc.

:wlach pointed to a recent instance where histograms.json was updated to reflect a set of probes that were only ever collected in one process: https://hg.mozilla.org/mozilla-central/rev/e2bfc4560e97dc9b65e73ef332c01deea7208622

Component: Datasets: General → General

Use counters were split into a separate data set, main_v5 is now being used. main_v4 still exists but isn't being populated or used.

Status: NEW → RESOLVED
Closed: 1 year ago
Resolution: --- → FIXED
You need to log in before you can comment on or make changes to this bug.