Online schema change best practices and troubleshooting

[awoods187]

Andrew Woods (awoods187) commented:

Schema changes, by their nature, consume additional resources. We should provide recommendations for when to run schema changes, how much available CPU is needed, and what to expect performance wise. If schema changes are run near peak capacity, latency spikes can occur. It's important we help users understand what is needed to ensure online schema changes don't impact foreground traffic.

Jira Issue: DOC-262

[awoods187]

@drewdeally suggest cpu > 55% because if a node fails, now you will bump all surviving nodes to 80% (or something high) and the system will be under stress

It can be avoided perhaps by sizing for a run rate of 20-40%

When planning we need to add in node failure overhead

[awoods187]

The new schema change index backfill is highly susceptible to spikes of latency as CPU usage of the cluster increases (cockroachdb/cockroach#34744 (comment)). We need to provide guidelines around when to run a schema change and what to do if latency spikes. It probably makes sense to do so here (https://www.cockroachlabs.com/docs/v2.1/online-schema-changes.html#main-content)

We cover running with sufficient headroom above--this is the most important piece of information.

In the event that latency spikes (either due to insufficient headroom or other occurrences) we should update the limitations section to recommend the following:

1. Cancel the index backfill job (show jobs cancel job foo).
2. Add capacity to the cluster (horizontal scale by adding nodes to the cluster)
3. Try again.
4. If this doesn't work, or you are unable to add capacity, you can turn the new index backfill off and use the older backfill implementation.

cc @lhirata @vivekmenezes @dt

[jseldess]

@lhirata, is this out of scope for 19.2 at this point, given your focus on oracle migration, training, and ops and tools features?