To date we have observed several errors from the /statments and /combined-statements endpoints.

Issue 1: /Statements response too large for gRPC

Currently, /Statements, returns all cluster-wide in-memory statement stats.
Since the virtual statement tables call /Statements, this is also an issue for the combined stats API.
The virtual tables calling /Statement are particularly wasteful as only one of the statements or transactions portion of the response is used, and the other part discarded. We should at least work to remove the unnecessary collection and return of statement / transaction stats when not necessary (see this issue for more context).

We have seen reported issues where this might be the cause: https://github.com/cockroachlabs/support/issues/1263.

Potential solutions:

1. gRPC streaming: If we could stream the statement stats from each node, and perform an ordered merge of those results, we could avoid having to buffer the entire cluster-wide stmt stats results. However, we would need to open connections to all nodes concurrently, this may be an issue on large clusters with many nodes. Not too sure on what our limitations here are for maximum number of connections, noted the max number of requests are currently capped at 100
2. Aggregate in-memory statement stats on nodeId: this could reduce the size of the response considerably if many nodes share the same statement keys. This would require more investigation to see if the nodeId field in each individual statement stat is still necessary.

Issue 2: Full scans on system stats tables might be causing timeouts

Another issue we are seeing are timeouts. One cause for this error may be due to the fact that the virtual stats tables (crdb_internal.statement_statistics and crdb_internal.transaction_statistics) performs a full-scan on the system stats tables (system.statement_statistics and system.transaction_statistics). After we have been flushing stats for a while, these tables can grow large enough that a full scan might be causing timeouts to occur.

Possible solution to both problems

@Azhng proposed a solution that might address both of the problems above.
In our sqlstats subsystem, we currently flush in-memory stats to the system tables on an hourly interval, or when they grow too large. Instead of buffering cluster-wide in-memory sql stats and merging them with the system tables, we can:

1. Perform a flush on every request to retrieve combined stats, thus system tables now have the most up to date stats at time of request
2. Directly query from the system tables with the necessary filters
3. Paginate results (can be now be done without worrying about in-memory stats changing)

This avoids the full table scans, and the buffering of in-memory stats, removing the necessity for the virtual tables entirely. Some performance testing is necessary here, as we'd be flushing much more often here than the current hourly interval.

Epic CRDB-11997

gz#10996