remove unused secondary indices from sync

[data-sync-user]

There are some indices that are no longer required by Sync. These should be removed. -May need to talk with SRE and confirm indices with- [~accountid:5d264129831d7b0bcfbe3cfb]-.-

This is about our -3-2 secondary indices:

• BsoModified - 34.91TiB
• BsoExpiry - 34.9TiB
• -BatchExpireId- https://github.com/mozilla-services/syncstorage-rs/pull/2310

The latter were created solely for use by the purge script, the former was brought over from the original mysql implementation, generally utilized for larger get_bsos queries.

We pay a larger cost in terms of space for each of these, they’re implemented as basically separate, hidden tables.

┆Issue is synchronized with this Jira Task

[data-sync-user]

Philip Jenvey commented from Jira:

This is in relation to our 2 secondary indices. Their usefulness is questionable, we may be able to utilize https://docs.cloud.google.com/spanner/docs/index-advisor or (some kind of metrics from Spanner) to confirm so.

Marking this as blocked by https://mozilla-hub.atlassian.net/browse/STOR-121 (or the work that follows that) because one of our secondary indices is solely for purging old records – it’s also questionable how much it’s utilized – but we certainly aim to move to managed TTLs vs our purge script, which would 100% negate the need for that index.

[data-sync-user]

Barry Chen commented from Jira:

The index advisor “Only provides CREATE INDEX and ALTER INDEX recommendations. Doesn't provide DROP INDEX recommendations for existing indexes.” fwiw.

[data-sync-user]

Barry Chen commented from Jira:

The recommendation from Query Insights is to add a covering index, completely ballooning the storage.

[data-sync-user]

Philip Jenvey commented from Jira:

[~accountid:60482ee0b2ea1201159d2194] Covering index for what? 😆

[data-sync-user]

Philip Jenvey commented from Jira:

So “index advisor” isn’t really what I thought it would be, like a separate tool, just an advise on other tools like query insights.

With secondary indices being a separate table I was able to find some good metrics (for tables):

• System Insights
  ** Has Database Storage by Table (which I updated the summary with)
  *** Both of these indices are using about 35TB currently
  ** Most/Least-used tables by operations
  *** Writes/Deletes on them are about what you’d expect (not insignificant but not as much as bsos)
  *** BsoModified shows 0 reads, and BsoExpiry has a fair amount
  *** These values are read from system tables https://docs.cloud.google.com/spanner/docs/introspection/table-operations-statistics , so we can look at them more closely

          read_query_count,
          write_count,
          delete_count
    FROM spanner_sys.table_operations_stats_minute
    WHERE table_name = "BsoModified"
    ORDER BY interval_end DESC;{noformat}

on prod shows me not even close to nothing for {{BsoModified}} {{read_query_count}} – but literally nothing. And {{BsoExpiry}}’s read is high enough to see similar results in queries on it.

So I’d say that’s confirmation we can totally kill {{BsoModified}}, and probably best to leave {{BsoExpiry}} until we kill the purge scripts.

[data-sync-user]

Barry Chen commented from Jira:

[~accountid:5d264129831d7b0bcfbe3cfb] not sure why I cannot find the recommendation in query insights anymore. anyway iirc it was on one of the expired bso selects. it wasn’t suggesting creating a new covering index; it suggested adding three(?) new columns into the current secondary index, effectively making it a covering index.