admission: investigate TPC-E online index creation problem

[sumeerbhola]

Motivation in experiments run by @nvanbenschoten
in https://cockroachlabs.slack.com/archives/C038JEXC5AT/p1658247509643359?thread_ts=1657630075.576439&cid=C038JEXC5AT and https://docs.google.com/document/d/1wzkBXaA3Ap_daMV1oY1AhQqlnAjO3pIVLZTXY53m0Xk/edit#heading=h.fhho371lyula
@nvanbenschoten has packaged this in a roachtest #85002

Summary: AC was able to keep read amplification in check but foreground throughput and latency were severely affected. Our assumption is that the rate at which foreground work is being admitted has dropped.

• We expect some improvement based on pending PRs for better byte token estimation, and tracking follower writes that bypass admission control. We should wait for them to merge.
• To diagnose this we need to add labeled admission latency and admission rate metrics with a label per priority admission: additional observability #82743
• We may still see an isolation failure due to follower writes bypassing admission control, so we should set admission.kv.pause_replication_io_threshold = 0.8.

@irfansharif @nvanbenschoten

Jira issue: CRDB-18354

[irfansharif]

x-linking #82556 + #89208.

[andrewbaptist]

Some additional results from running the TPC-E index backfill experiments on the 9 node cluster:
TL/DR - Dropping the L0 file count overload to 10 and patching to disable AC on user requests resulted in at least a 20x drop in latency during index creation. With those settings on TPC-E the P99 latency only went from 25ms to 62ms, vs 3700ms with the default setting.

Specifically, I was changing two settings

1. L0 file count overload
2. Patch to disable overload on user requests (only keep for bulk requests).

The patch itself is not ready for all customers, especially multi-tenant as if the setting is enabled there are two possible drawbacks:

1. In a multi-tenant case, we won’t prioritize correctly
2. In a single-tenant case where the user traffic causes LSM inversion elastic work may get starved. (I’m not sure if we have any customers that would be in this scenario)

Based on how encouraging the results are it might be something that we backport to 22.2 in its current state (with a flag to enable/disable) and work on a better patch for 23.1. Additional work needs to happen to better understand why the default setting results are so bad. I suspect that we greatly overshoot the L0 file limit and it takes seconds of compaction to recover in some cases. During this time all work is halted and we grow large queues. Once we come under the limit we do all the KV work first but finish that pretty quickly. Then we process another large batch of AddSST, which causes it to overshoot again. One reason this hypothesis seems reasonable is that if I change the L0 setting below the current level it is at, I am unable to run any operations against the system until compaction has dropped it to the rate I expect.

The summary of results is here: https://docs.google.com/spreadsheets/d/12cE0tL5zihm8ymN_r07ag3-LzeOgRHamD8vb3RSdcR0/edit?usp=sharing (edited)

SQL Statements during index creation with current settings:

Current code / default settings:

Note the correlation between when the IO admission is red vs when SQL statement count drops. When in overload (LSM count > 1000), no new transactions can begin, when it is “at” overload, only user jobs run since they are higher priority, when it is “below” overload index AddSST also runs.

SQL Statement during the run with the patch:

Statement bundle showing a slow TX:
Note:
26899.272ms 26866.009ms event:util/admission/work_queue.go:672 [n9] admitted, waited in kv queue for 26.865980677s
stmt-bundle-807086135621877766.zip

IO Load logs on the "hot" node.
io_load.log

[andrewbaptist]

Logs from the run:
https://drive.google.com/drive/folders/1i5cBIaIdAat_cMXhQeyvhtWGmOfsymP9

[irfansharif]

Going to de-dup in favor of #95563.