Environment

• YugabyteDB Anywhere - Tuned in 2024.1 but the concepts are equally valid for currently supported releases

Issue

When trying to create an index on a very large table (>35 billion rows), the process timed out so the index never completed.

Resolution

Overview

In this case, all items were tuned and the index completed .

Tuning all settings will not be necessary in all cases.

We made a number of changes to gflags and, after this, the index was able to be completed in 303864128.368 ms (3 days 12 hours 24 minutes and 24.128 seconds).

Steps

1. Set gflag leader_failure_max_missed_heartbeat_periods to '10' on all masters

See: https://docs.yugabyte.com/preview/reference/configuration/yb-master/#leader-failure-max-missed-heartbeat-periods for details of this gflag.

Note: This will need to be reset to 6 after the index completes.

2. Set gflag backfill_index_client_rpc_timeout_ms to 691200000

See: https://docs.yugabyte.com/stable/reference/configuration/all-flags-yb-master/#backfill-index-client-rpc-timeout-ms for details of this gflag.

Note: 691200000 = 8 days expressed in milliseconds

The default value is 86400000 = 1 day expressed in milliseconds

3. change gflag backfill_index_timeout_grace_margin_ms from -1 to 60000 (effectively changing the timeout from 1 second to 1 minute)

See: https://docs.yugabyte.com/stable/reference/configuration/all-flags-yb-master/#backfill-index-timeout-grace-margin-ms for details of this gflag.

Note: The default value is -1, which uses 1 second timeout.

The value 60000 is one minute in milliseconds.

You may wish to test different values for this as a timeout of 30, 15 or 5 seconds may suffice depending on the circumstances.

4. change gflag backfill_index_write_batch_size from 128 to 32 (reduce the amount of data being indexed in each batch.)

See: https://docs.yugabyte.com/stable/reference/configuration/all-flags-yb-master/#backfill-index-write-batch-size for details of this gflag.

Note: smaller batches are less likely to time out but the actual size of the batches required could be optimised depending on the resources.

Additional Information

https://docs.yugabyte.com/preview/api/ysql/the-sql-language/statements/ddl_create_index/