kvcoord: investigate tpch_concurrency roachtest regression after bumping default value of max_refresh_span_bytes

[arulajmani]

Describe the problem

In #80115 we bumped the default value of kv.transaction.max_refresh_span_bytes to 4MB. As @yuzefovich points out here, the test attempts to find the maximum concurrency of TPCH queries that can be sustained without OOM-ing. The regression doesn't seem entirely surprising given we're using more memory to track refresh spans, but it's still worth investigating to make sure.

We might also want to take the opportunity to add memory accounting for the spans we're tracking. One could imagine a scheme where we dynamically reduce this limit if we're running close to the limit instead of letting the cluster OOM because the static limit is too high.

Jira issue: CRDB-15176

Epic CRDB-19172

[yuzefovich]

I looked into this some time ago but forgot to write the details down. I think there were two problems:

• refresh spans not being condensed enough. At the end of the query execution we propagate this as metadata, so if the refresh spans are smaller, then we'll need to send and receive less across the network. This seems like a KV issue, but I'm also not certain that there is a bug in the code.
• LeafTxnFinalState metadata is not accounted for on the receiving side. This is a SQL issue.

For context, TPCH queries are analytical in nature, so they read a lot of data, and we need to propagate the refresh spans back to the gateway at the end of query execution. Bumping up max_refresh_span_bytes seems to have increased the amount of those refresh spans by too much. TPCH Q9 seems to be the one OOMing the nodes in tpch_concurrency/high_refresh_span_bytes roachtest the most.

[blathers-crl]

Hi @yuzefovich, please add branch-* labels to identify which branch(es) this release-blocker affects.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}