In TiDB how do you work around the issue where even for indexes, you can't write continuous values to avoid hotspots?

Question

In TiDB, due to its range scan-based sharded nature, I understand that you should avoid writing continuous values. For primary keys, a standard solution is to use auto random rather than auto increment. Now what should we do if we want to add an index on a time field such as update time or create time? Certainly, we can't just put a random value here and the continuous nature is inevitable. What are some design patterns that help to work around this?

Answer 1

With TiDB hotspots can happen. With TiDB the data is stored in TiKV, which is a key-value store. In most cases a clustered primary key is used which means that the key in the TiKV layer is basically something like t_<tableid>_<pk> with the ID of the table and the primary key of the row.

Rows are stored in data regions which are around 96 MiB in size and store a range of keys.

If you use AUTO_INCREMENT then you get values like 1,2,3, etc which are going to end up in the same data region. And the primary copy of a data region is on a single server (usually with replicas on two other servers). This means this server has to process the writes for this table and gets more load than other servers. If you use AUTO_RANDOM you can avoid this as the random values are likely to be spread over all the data regions for that table. Another option is to use a NONCLUSTERED primary key, this means the primary key is basically just a regular index and is not used as key for the TiKV storage. Instead of the PK a generated _tidb_rowid will be used instead. There are a few options to influence how the _tidb_rowid gets created.

There is a document describing these solutions.

However, if you have a single table without secondary indexes and need good insert performance this might be a problem. However if you have multiple tables and multiple indexes this will probably result in a more even load in total as for one row there might be one region storing the actual row and there might be other regions (on other servers) that store the index.

If you have issues with performance you can look at the Key Visualizer, The TiDB Dashoard and Grafana and see if the load is reasonably balanced or not.

In some cases it might make sense to scale out the set of TiKV servers to reduce the total load on a single server and allow the load to be better balanced over all servers.

Note also that hotspots can happen for more than just writes, it can also happen with reads.

Disclaimer: I'm working for PingCAP, the company behind TiDB.

Answer 2

You are right. If you create a time-column-based secondary index, you will get a hotspot in TiKV. However, there are some that we can consider:

1. The secondary indexes hotspot issue is less severe than the clustered index. That's because the secondary index only records a row ID at the value part in TiKV.

2. If we want to release the secondary indexes hotspot issue anyway, we can make a composite index, which means we can build an index on the combination of the time column and a hash column together. For example, if you want to make an index on the update_at column, and you have another uuid column, you can make an index like this:

   CREATE INDEX combined_index_name ON table_name (update_at, uuid);
   

   The core part is that if the updated_at is the same, the uuid will dominate the order of the key. For example, there are two records:

   id	updated_at	uuid
   1	2023-11-05 17:50:30	"1_uuid_random"
   2	2023-11-05 17:50:30	"0_uuid_random"

   In this case, when you build the index of (update_at, uuid), you can revert the order of records. So you can somewhat break hotspots.

In a nutshell, generally speaking, the secondary index won't be your hotspot bottleneck. But when you encounter it as a severe issue, you can use the composite index to work around it.