Optimization Issues of Rewrite && Commit in Insert Statements

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

Original topic: insert 语句中rewrite && commit优化问题

| username: jeff

[TiDB Usage Environment] Production Environment
[TiDB Version] 4.0
[Encountered Issue: Problem Phenomenon and Impact]
The INSERT statement takes a long time during the prewrite and commit phases. How can this be optimized? The average prewrite time is 99% 30 milliseconds. Disk latency is not high. After checking the async process, most of the time is consumed in the raft phase (log commit phase takes about half, around 15 milliseconds). The apply phase consumes very little time. In TiDB 4.0, is it possible to increase the store-pool-size to enhance production?

SQL:

INSERT INTO
  nova_transaction_order_record (
    id,
    customer_id,
    account_id,
    symbol,
    side,
    TYPE,
    STATUS,
    price,
    average_price,
    amount,
    filled_fee,
    fee_rule,
    fee_mark,
    spend_currency,
    spend_account_id,
    spend_amount,
    deal_spend_amount,
    get_currency,
    get_account_id,
    get_amount,
    deal_get_amount,
    source,
    channel,
    canceled_at,
    finished_at
  )
VALUES
  (..., NULL,..., NULL, NULL)

[Attachments: Screenshots/Logs/Monitoring]






| username: redgame | Original post link

This is difficult to optimize, adjust the transaction concurrency.

| username: zhanggame1 | Original post link

It feels like a disk performance issue, evaluate the disk IOPS.

| username: h5n1 | Original post link

Is it just this Insert that’s slow or are all operations slow? Check the monitoring under tikv detail → thread CPU, scheduler-prewrite → scheduler latch wait duration, and network latency black exporter.

| username: jeff | Original post link

The latch is very fast, and the raft CPU load is not high at 50%, so I am considering whether to increase the store-pool-size.

| username: zhanggame1 | Original post link

Returning to the original question, is the system currently bottlenecked and unable to keep up with the business volume, or are you just seeing this high value and want to optimize it?

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

Previously, my 4.0.9 cluster also had high insertion latency. Upgrading to a higher version improved the performance.

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

The official test reports provided show that with each version upgrade, the read and write performance has improved.

| username: h5n1 | Original post link

There are many thread CPUs. What about network latency?

| username: 有猫万事足 | Original post link

I still strongly recommend this video.
My own feeling is that as concurrency increases, the proportion of time spent in the parse and compile stages will grow rapidly.
Right now, you see parsing takes only 80 microseconds. With high concurrency, it could potentially reach 10+ milliseconds.
Under the Raft protocol, it’s still very difficult to improve the prewrite stage aside from network or storage updates.
It’s better to focus efforts on optimizing the plan cache, which will perform better in high concurrency scenarios.

| username: system | Original post link

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