Optimize the Performance of Bulk Deletion

[scsldb]

Description

Optimize the performance and efficiency of bulk deletion, a typical query is:

delete * from table where id < ?

Category

Feature, Stability

Value

Implement a special SQL using offset in session to improve performance delete * from table where t < xxx (backgroud & no-automic).

Time

GanttStart: 2020-07-31
GanttDue: 2020-08-15

[zhangjinpeng87]

The original scenario should be

affect_rows = 1
while affect_rows > 0 {
    affect_rows = `delete * from table where t < xxx limit 5000`
}

[zhangjinpeng87]

At first, we can test how about the performance when enabling coprocessor cache.

[zhangjinpeng87]

#18308 may be a common solution for this issue. We can also enable cop cache
at the same time if the transaction is extremely huge.