Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 使用hash分区，为什么写入的数据还是在一个分区表上呢
[Test Environment for TiDB]
[TiDB Version]
[Reproduction Path] Using an auto-increment ID as the primary key can cause write hotspot issues. We need to keep the auto-increment ID as the primary key and consider using hash partitioning. Manually insert four pieces of data. These four pieces are indeed on four partition tables. Then use sysbench for stress testing. The result shows a large number of writes in one partition table, and the auto-increment IDs are 4, 8, 12, etc.
Why is this happening? If we need to keep the auto-increment ID as the primary key, what are some good solutions to this write hotspot issue?
You can use auto_random to solve it.
Please provide the Chinese text you would like translated into English.
If you need to use an auto-increment field and sort by chronological order, it is better to use a timestamp. TiDB recommends using auto_random to avoid hotspots.
It seems that this kind of hotspot writing cannot be avoided. How about trying with more partitions?
Using hash partitioning, but the result is still written to a single partition table.
auto random is the first choice.
If it can’t be done, then it can only be
CREATE TABLE t (
Id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (Id) /*T![clustered_index] NONCLUSTERED */,
) ENGINE=InnoDB /*T! SHARD_ROW_ID_BITS=3 PRE_SPLIT_REGIONS=2 */
/*T![clustered_index] NONCLUSTERED */ non-clustered table
/*T! SHARD_ROW_ID_BITS=3 PRE_SPLIT_REGIONS=2 */
The relationship between write hotspots and partitioning is not significant; it’s still auto_random.
Was it tested with a single thread and single connection?
It was not tested with sysbench, the configuration is for 64 threads.
To summarize: There is currently no suitable solution for this issue, so we can only consider the following points:
- Improve the machine’s performance to achieve higher TPS.
- Implement auto-increment IDs yourself.
- Use rate limiting to smooth out peaks.
|
username: madcoder
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: