SHARD_ROW_ID_BITS

This document introduces the SHARD_ROW_ID_BITS table attribute, which is used to set the number of bits of the shards after the implicit _tidb_rowid is sharded.

Concept

For the tables with a non-clustered primary key or no primary key, TiDB uses an implicit auto-increment row ID. When a large number of INSERT operations are performed, the data is written into a single Region, causing a write hot spot.

To mitigate the hot spot issue, you can configure SHARD_ROW_ID_BITS. The row IDs are scattered and the data are written into multiple different Regions.

• SHARD_ROW_ID_BITS = 4 indicates 16 shards
• SHARD_ROW_ID_BITS = 6 indicates 64 shards
• SHARD_ROW_ID_BITS = 0 indicates the default 1 shard

When you set SHARD_ROW_ID_BITS = S, the structure of _tidb_rowid is as follows:

• The values of the auto-increment bits are stored in TiKV and allocated sequentially. Each time a value is allocated, the next value is incremented by 1. The auto-increment bits ensure that the column values of _tidb_rowid are unique globally. When the value of the auto-increment bits is exhausted (that is, when the maximum value is reached), subsequent automatic allocations fail with the error Failed to read auto-increment value from storage engine.
• The value range of _tidb_rowid: the maximum number of bits for the final generated value = shard bits + auto-increment bits, so the maximum value is (2^63)-1.

For details on the usage, see the Troubleshoot Hotspot Issues guide.

Examples

CREATE TABLE t (
    id INT PRIMARY KEY NONCLUSTERED
) SHARD_ROW_ID_BITS = 4;

ALTER TABLE t SHARD_ROW_ID_BITS = 4;