backfill: rate limit progress updating

[pbardea]

Describe the problem

Recently we witnessed the following scenario: a create index job with a 1MB payload was being updated every 10s. This lead to the range in the jobs' table to start backpressuring writes and thus blocking writes. See #62627. (100 KB/s write write to the table pretty quickly fills up the range.)

The index backfiller should be less agressive with it's job progress updating. Rather than just updating at a fixed rate (today's one update every 10s), it should backpressure based on how fast it's writing to the jobs table. This is likely exacerbated since the index backfiller maintains a list of spans that it still needs to process. As completed work comes in from all of the 100 nodes it chops up the list of todo spans so the progress can grow quite large...

Instead of writing every 10s, we should at least throttle the write rate when updating the jobs record to some throughput rather than once every 10s regardless of how large the payload is. We may also want to consider limiting the size of the todoSpans at the cost of losing some progress to prevent these payloads from growing too large.

gz#9008

Epic CRDB-8816

gz#9032

gz#8907

Jira issue: CRDB-8588

[dankinder]

Hey @pbardea would this issue have a bigger effect when creating a partial index?

For background, I originally filed #67487 where I hit "split failed while applying backpressure" on the jobs table during an index build, and @ajwerner pointed me to this ticket and gave me a workaround.

I'm building a simple partial index, like create index on mytable (the_column) where the_column is not null; where the_column is NULL for a majority of rows.

Here is what I'm setting currently to try to work around it:

-- Note: @ajwerner recommended 300 for this one but that failed so I lowered even further. Should be safe since I am not using incremental backups
ALTER TABLE system.jobs CONFIGURE ZONE USING gc.ttlseconds = 30;

ALTER TABLE system.jobs CONFIGURE ZONE USING range_max_bytes = 2<<30;
SET CLUSTER SETTING kv.range.backpressure_range_size_multiplier = 8;

This got it much further (failing at 70% instead of 5%), but the index build still fails with an error like this: job-update: split failed while applying backpressure to Put [/Table/15/1/677975849697181697/1/1,/Min), [txn: 3dbf0eeb] on range r812869:/Table/15/1/677975849{697181697-721102337} [(n4,s23):1, (n41,s245):2, (n37,s217):3, (n23,s137):4, (n44,s261):5, next=6, gen=384]: operation "split queue process replica 812869" timed out after 1m0s: could not find valid split key

What's odd is that, right around the time the index build stalls, I start getting timeouts trying to view the jobs table in the console. Presumably it's getting tons of write activity and has a lot of tombstones right then.

So I was theorizing that, because it's a partial index, maybe it is hitting big swaths that don't meet the condition (the_column is null), and progress is advancing too fast at that point, overwhelming the job table. Anyway, just a guess.

If you guys know of any other workaround to make this index build succeed I'd love to hear it. Thanks.

[pbardea]

Hi @dankinder -- sorry for the delay! The workarounds that you provided are a good start. A cluster setting, bulkio.index_backfill.checkpoint_interval, was recently added (#68215) which can help further reduce the frequency of the checkpointing which may help out the backfills in this case. It's also been backported to the 21.1 release branch.