changefeedccl: improve planning heuristics

[jayshrivastava]

See https://github.com/cockroachlabs/support/issues/2679.

The problem:

In this scenario, there's a changefeed running with execution_locality = foo on a table which is configured to with a leaseholder preference in region bar. The table foo has 150k ranges. Region bar has ~20 nodes and region foo has ~10.

We observed the following problems:

• One node in region foo was assigned ~60k ranges to watch with the next highest being ~3k ranges. This imbalance makes the changefeed run slower. When the leaseholders are non-local, we don't know what heuristics distsql will use to assign work to change aggregators. We should investigate and consider making our own planning logic. We should consider using changefeed.balance_range_distribution.enable always, even after initial scans.
• Even if we balance the ranges, we might end up with too many ranges per node. Instead of distributing 150k ranges over 30 nodes, we have to balance them across 10 nodes due to the execution locality. As we've seen before, assigning too many ranges to one node can cause OOMs when not using mux rangefeed (we alloc a 2MB buffer per range). There could also be too much of a fan-in factor, preventing catchup scans from completing (with or without mux).

The solution:

We want to add "planning modes" where we can choose how to distribute work to nodes when we plan a changefeed. Namely, we want 3 modes:

• let distsql plan everything (ie. let distsql plan the changefeed)
• let distsql create the initial plan and rebalance it
• create our own plan which uniformly distributes work among live nodes

Jira issue: CRDB-33248

[blathers-crl[bot]]

cc @cockroachdb/cdc

[miretskiy]

@jayshrivastava FYI: with https://github.com/cockroachdb/cockroach/pull/114710 the work on this issue might become obsolete, and we may just get rid of balanced range distribution altogether.

[jayshrivastava]

Linking this related issue: https://cockroachlabs.slack.com/archives/C0KB9Q03D/p1701101549923459

[rharding6373]

Reducing priority level to P-3. We already have a rebalancing strategy (balanced simple distribution) that addresses the overload concerns in the support issue, so we do not have an urgent need for additional strategies.