sql,server: reduce SQL Stats RPC overhead #71829
Description
Currently we re-use the same RPC endpoint (/Statements) to serve both virtual table as well as Frontend API calls.
However, the existing /Statements endpoint returns both statement statistics and transaction statistics in a single gRPC payload. Ideally, this should reduces network roundtrips, but this is not the case in reality, due to various reasons,
Currently, this is how virtual table is being populated
Virtual Table Code Path for crdb_internal.statement_statistics
SQL SELECT
|
v
Gateway node: -------+
/ \
/ \
v v
RPC fanout to /Statement endpoint system table (system.statement_statistics)
| |
| |
(discards the txn portion from RPC response) +
| /
+ /
\ /
v v
Instantiateing a combined iterator
|
|
v
Stream out results
Now consider the Frontend API call code path
At a high level, the code path looks like this:
Frontend API Request
|
v
Status Server RPC endpoint -------------+
/ \
/ \
+ +
| |
v v
reads `crdb_interanl.statement_statistics` reads `crdb_internal.transaction_statistics`
\ /
\ /
Generate a single RPC resposne
At a quick glance, this might look fine, however, if we expand the two "read* operations on the virtual table, we start to see the problem:
Frontend API Request
|
v
Status Server RPC endpoint ----------------+
/ \
/ \
+ +
| |
v v
reads `crdb_interanl.statement_statistics` reads `crdb_internal.transaction_statistics`
| \
| \
| \
v \
SQL SELECT on statement_statistics SQL SELECT on transaction_statistics
| |
v v
Gateway node: -------+ Gateway node: -------+
/ \ / \
/ \ / \
v v v v
RPC fanout to /Statement endpoint system table (system.statement_statistics) RPC fanout to /Statement endpoint system table (system.transaction_statistics)
| | | |
| | | |
(discards the txn portion from RPC response) + (discards the stmt portion from RPC response) +
| / | /
+ / + /
\ / \ /
v v v v
Instantiateing a combined iterator Instantiateing a combined iterator
| |
| |
v v
Stream out results Stream out results
| |
| |
+----------------------------------------------*-------------------------------------+
|
|
v
Populate a single RPC response
This means that we are effectively making two RPC calls to /Statement endpoint, each time we discard 50% of the RPC response, this is extremely wasteful.
We can address this by splitting /Statement endpoint into smaller endpoints, /StatementStats and /TransactionStats, where each endpoint only returns the statement statistics and transaction statistics specificlly. These two endpoints will be only used to power the RPC fanout.
The frontend should move to depend on the CombinedStmtStats endpoint. Then we can finally deprecate the existing /Statements endpoint.