Slow store initialization due to acs size queries

[moritzkiefer-da]

Seen in the logs here

The logs don't make it all that clear what is happening there but looking at the code the only thing that comes to mind is this query

        acsSizeInDb <- acsInitResult match {
          case StoreHasData(acsStoreId, _) =>
            storage
              .querySingle(
                sql"""
                  select count(*)
                  from #$acsTableName
                  where store_id = ${acsStoreId} and migration_id = $domainMigrationId
                  """.as[Int].headOption,
                "initialize.getAcsCount",
              )
              .getOrElse(0)
          case _ => FutureUnlessShutdown.pure(0)

This also seems to be in line with these metrics that also show initialize taking 30s

We definitely need to fix that, a store reinitialization should be a noop and it very much is not atm.

[moritzkiefer-da]

We currently use this in two places:

1. In logs, I honestly have never really found the size number in there to be all that useful.
2. In metrics, We have used those a fair bit. add ACS snapshot size metric #6108 covers some of them but probably not all so I'm a bit hesitant to give it up completely.

Only real option I see for making it faster would be to actually store the size in the table and update it on ingestion. But then we also have to worry about potential performance impacts of that. Maybe one row update per batch is not all that bad though?

[meiersi-da]

Can we initialize that metric lazily? kick-off the query for the initial size in a future, and have the acs store track the diff to the initial count in a separate atomic variable?

[moritzkiefer-da]

that would work but I'm a bit worried about those queries then causing load issues at some point.

[meiersi-da]

that would work but I'm a bit worried about those queries then causing load issues at some point.

Good point. What about changing our approach and using Postgres' estimates: see n_live_tup in https://www.postgresql.org/docs/current/monitoring-stats.html

It assumes that one table only has one store in there, but is fast to query.

If we want to be more precise, but still fast then we could parse the query planners JSON output for the query that does the filtering on the store_id.

[moritzkiefer-da]

Yeah I considered this but I think this assumption doesn't work well in our current setup

It assumes that one table only has one store in there, but is fast to query.

We somewhat regularly bump store versions and don't clean out old data. But maybe fixing that is the better investment.

[moritzkiefer-da]

some input from @OriolMunoz-da on slack:

• maybe non-dso stores don't matter that much and for the dso store the acs snapshots should be good enough (a good data point for or against this may be if the acs store size for the tbar stores is useful)
• temporarily breaking the metric during some transition seems fine

[rautenrieth-da]

Another idea, if you want to keep a real-time dso acs size metric: move the metric into AcsSnapshotTrigger. There we already update the table acs_incremental_snapshot (one metadata row) every time we update the incremental snapshot. This happens roughly once every 30sec, and we read the table every 30sec as well. We would just need to add an additional column to that table. You could also make it self-healing by (re-)initializing the acs size every time we copy the incremental snapshot, as that task already fetches the number of rows copied.

This would probably conflict with #6108.

if the acs store size for the tbar stores is useful

We have an alert on too many rows in table scan_rewards_reference_store_active: https://github.com/canton-network/splice/blob/main/cluster/pulumi/observability/grafana-alerting/traffic_based_rewards_alerts.yaml#L19, which is supposed to warn if there are too many featured app rights. That store is not a DbMultiDomainAcsStore though.