kv: move split and merge cluster settings to be zone/span configs

[data-matt]

Is your feature request related to a problem? Please describe.

For hot ranges, we suggest tuning cluster level settings:

• kv.range_split.by_load_enabled
• kv.range_split.load_qps_threshold
• kv.range_merge.queue_enabled

However, these settings will have impact on all workloads across the cluster, whilst the hot ranges issue appears within individual databases/tables. From a blast radius perspective:

• We don’t want change these settings and impact a whole cluster at once.
• Different workloads (against different tables) might want these settings to be tuned differently.

This shows up in internal incidents as well: https://github.com/cockroachlabs/support/issues/1590.

Describe the solution you'd like

We want to be able to tweak these settings on a per-schema object level. We already have per-schema object configurations provided through zone configs; we could introduce new fields there. We could also audit other cluster settings that would benefit from the same treatment.

Jira issue: CRDB-10140
Epic: CRDB-26686

[irfansharif]

Edited some of the issue text. This work becomes more relevant in the context of secondary tenants looking to fine-tune performance in the same way, except they don't have access to the kind of privileged cluster settings to let them do so (for the blast radius they can have). Given our recent work to support zone configs for secondary tenants (#67679), building on top of it to fold in these tuning knobs seems like the right way to go.

[irfansharif]

It'd be nice to also be able to more selectively set closed timestamp target durations for individual rangefeed. For internal subsystems that make use of rangefeeds (spanconfig.KVSubscriber from #69614) is a recent one, we probably want a very tight target duration. This would reduce how long it takes for a zone config to be fully applied, something that may come in handy if we decide to poll for it to provide better guarantees (ala #71977). Yet another setting that can be applied on a per-schema level of granularity is whether or not rangefeeds are enabled for the given table (+cc @ajwerner).

[irfansharif]

Going to move this to the SQL schema board so it has an owner, this feels worth doing (+cc @ajwerner).

[ajwerner]

For future readers: this issue is not about the general concept of cluster settings and instead it notices that there are some cluster settings which control KV configuration and can be applied on a per-range basis. These could instead be propagated in a more targeted way to the key-value store via the span configuration subsystem.

[data-matt]

Yes @ajwerner that would be great, only settings that make sense to be set at a lower level :)

[ajwerner]

Maybe @mwang1026 or @devadvocado, y'all want to get your heads together and decide whether we want to do this? Relates also to supporting range feed/changefeed per table (there's an issue for that somewhere I can dig up).

[dt]

I think we should close this, or reframe it to be specifically "change some kv configurations to be zone/span configs instead of cluster settings". Cluster settings are just that, cluster settings, and are doing what they're supposed to do, but are being used in a place where a different mechanism (zone configs) would be a better fit.

[knz]

Relevant Slack thread: https://cockroachlabs.slack.com/archives/C0KB9Q03D/p1687779261954189

It appears the span config infra is already able to enable rangefeeds on a per-table basis. So we can expose this via zone configs to end-users.

[knz]

Idea from @dt:

Do we actually even need zone/span configs for rangefeed? can we not just derive the fact it is enabled/disabled by the fact a client is connected to it or not?

Further discussion in aforementioned slack thread.

[dt]

I've retitled the issue to be specific to the settings named in it since as I mentioned above, cluster settings themselves are working as intended, just being used for something that doesn't want to be cluster-wide in these cases.