Using TiDB version 4.0.9, batch replace into data insertion is slow, how to optimize?

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

Original topic: tidb使用的是4.0.9版本,批量replace into插入数据慢,如何优化?

| username: TiDBer_y9IRzLWc

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version]
[Reproduction Path] What operations were performed to encounter the issue
[Encountered Issue: Problem Phenomenon and Impact]
[Resource Configuration] Enter TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachment: Screenshot/Log/Monitoring]

As shown in the above image, a large number of batch replace into data insertions are found to be slow when queried from the Dashboard. Upon checking the details, it is mainly due to the slow execution time in the first phase (prewrite phase) of the two-phase transaction commit. What optimization solutions are available for this situation? Reducing the amount of data in batch insertions?

| username: zhanggame1 | Original post link

Is “replace into” inserting one row at a time or multiple values at once? If possible, inserting multiple values in a single SQL statement is much faster.

| username: 大飞哥online | Original post link

The first stage pre includes: modifying data + lock information.

| username: 大飞哥online | Original post link

The write process can be analyzed step by step as follows:

  1. Scheduler CPU utilization
  2. Storage async: disk IO, machine load, etc.
  3. Raftstore CPU
  4. Apply CPU
    and other monitoring.
| username: TiDBer_y9IRzLWc | Original post link

It’s about inserting multiple values together.

| username: zhanggame1 | Original post link

Check the hard disk I/O situation, it might be that the hard disk performance is too low. Also, take a look at the TiKV CPU load.

| username: Kongdom | Original post link

Is it a one-time insertion or handled within the program? For one-time insertion, it is recommended to use ETL tools.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.