sql: use background QoS for COPY

[sumeerbhola]

See https://github.com/cockroachlabs/support/issues/2681#issuecomment-1789595737 and @mgartner's response in https://github.com/cockroachlabs/support/issues/2681#issuecomment-1791409479

Many uses of COPY will cause high write load. We could change our documentation to tell users to use background QoS https://www.cockroachlabs.com/docs/v23.1/admission-control#set-quality-of-service-level-for-a-session. However, users will forget to do this, and so will still cause problems since regular QoS is not subject to replication admission control. We could default (via a cluster setting in the SQL layer) to COPY executing at background QoS.

Jira issue: CRDB-33116

[sumeerbhola]

Should we also do the same for DELETE? @erikgrinaker you mentioned in https://github.com/cockroachdb/cockroach/issues/101992 that DELETE operations were causing instability.

[erikgrinaker]

I think we've primarily/always seen this cause problems once the txn commits and we begin resolving intents. The cluster's doing fine while the intent count increases, then it totally collapses once the intent count starts dropping.

We should be able to verify this by loading up a medium-sized cluster and doing a bulk-delete. Worth trying a bulk update too.

[sumeerbhola]

then it totally collapses once the intent count starts dropping

That will not happen now because intent resolution is subject to AC. But intent resolution will use the priority of the resolving txn, which if it is the txn that wrote the intent could affect foreground latency unnecessarily.

[mgartner]

Let's try to get this in to v23.2.

[blathers-crl[bot]]

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

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}