Could anyone tell me why the insertion speed slows down as the data volume of a table increases?

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 请问大家，随着一个表的数据量增加，插入也开始变慢了，是什么原因

| username: breakyang

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Problem Phenomenon and Impact]
[Resource Configuration]
[Attachments: Screenshots / Logs / Monitoring]
We have a table that needs to store 6 billion records. During testing, we found that after the data increased to 3 billion, the insertion speed slowed down by half. What could be the reason? How can we investigate and resolve this?

2
| username: zhanggame1 | Original post link

Check if there is any change in disk IOPS before and after.

3
| username: Jellybean | Original post link

From the point in time when it started to slow down, were there any changes in the cluster monitoring? How about the hotspot issue?

4
| username: 啦啦啦啦啦 | Original post link
  1. Check if data distribution is uneven, causing write hotspots.
  2. Check for lock conflicts.
5
| username: Kongdom | Original post link

What method is used for insertion? How many records are inserted at a time?

6
| username: breakyang | Original post link

One or two

7
| username: 像风一样的男子 | Original post link

You can refer to this troubleshooting process for slow read and write performance:

8
| username: tidb菜鸟一只 | Original post link

Check the dashboard to see if there are any write hotspots.

9
| username: cy6301567 | Original post link

Is the hotspot caused by the primary key being auto-incremented or randomly assigned? Check if the underlying region distribution is even.

10
| username: breakyang | Original post link

Regions are evenly distributed, with random primary keys. However, there is a date index, and I’m not sure if it is affecting it.

11
| username: zhanggame1 | Original post link

Indexes might also have some impact. Is the table partitioned?

12
| username: breakyang | Original post link

What benefits can partitioning bring if there are no partitions?

13
| username: TiDBer_vfJBUcxl | Original post link

Benefits of Table Partitioning:

  1. Improved Query Performance: Queries on partitioned objects can search only the relevant partitions, increasing retrieval speed.
  2. Enhanced Availability: If a partition of the table fails, the data in other partitions remains available.
  3. Easier Maintenance: If a partition of the table fails and data needs to be repaired, only that partition needs to be fixed.
  4. Balanced I/O: Different partitions can be mapped to different disks to balance I/O, improving overall system performance.
14
| username: 昵称想不起来了 | Original post link

You can refer to the troubleshooting guide, 【SOP】Best Practices for Analyzing Slow TiDB Write Operations.

15
| username: redgame | Original post link

The larger it gets, the slower it becomes. This is often the norm. You can partition it.

16
| username: zhanggame1 | Original post link

You can think of partitioning as a single table being physically multiple tables, with each partition being a separate table. Generally speaking, large tables of your scale should be partitioned. The most obvious benefit is the ease of deleting data. Deleting is slow, but dropping a partition is fast and can also free up disk space.

17
| username: cy6301567 | Original post link

Some possible reasons:

  1. Index Updates: When a table has indexes (including primary key indexes and other indexes), each insert operation requires updating the indexes. As the amount of data increases, the maintenance cost of the indexes also increases, which can cause the insert operations to slow down. This may affect performance after a large number of insert operations.
  2. Lock Contention: In a concurrent environment, multiple insert operations may compete for the same resources, such as row-level locks, table-level locks, etc. As concurrent inserts increase, lock contention may lead to a decline in insert performance.
  3. Write Amplification: TiDB uses the Raft protocol to ensure data consistency, which may lead to write amplification issues. When multiple insert operations are batched together for submission, the data may need to be replicated across different replicas, resulting in write amplification and reduced performance.
  4. Statistics Updates: TiDB uses statistics to optimize query plans. As the amount of data increases, the statistics may become outdated, leading to suboptimal query plan choices, which in turn affects insert performance.
  5. Disk IO Bottleneck: As the amount of data increases, disk IO becomes a performance bottleneck. Insert operations need to write to disk, and if disk performance is insufficient, the insert speed will slow down.
  6. Write Buffer: TiDB uses a write buffer to cache data to be written, thereby improving write performance. However, when the write buffer is insufficient, insert operations may be restricted, leading to slower inserts.
18
| username: breakyang | Original post link

The CPU of TiKV is unbalanced.
In TiDB, the prewrite in KV request duration and kv_prewrite in TiKV differ by 100 ms.

19
| username: caiyfc | Original post link

Check the heatmap in the dashboard to see if there is a write hotspot. If there is, use split region to divide the region.

20
| username: breakyang | Original post link

Previously, there was an index on the timestamp, which has now been changed to a composite index. Currently observing the results.