01-06 performance regression on batch_insert

[mahjonp]

Bug Report

There is a 10% performance regression on batch_insert

1. What did you do?

Version:
tidb: c7165bc51839ea3563cbea989870ba8f878e7514(master) 2021-01-06 11:08:18
tikv: 1bb82f0a20035be9f92f884c7c2bdbddbdc32070(master) 2021-01-06 11:46:39
pd: 1f6f41d60a954eafe5b55586ad340c83d2f4e005(master) 2021-01-06 06:09:28

Compare:
tidb: https://github.com/pingcap/tidb/compare/4bfd10c...c7165bc
tikv: https://github.com/tikv/tikv/compare/7757bd4...1bb82f0
pd: https://github.com/pingcap/pd/compare/125d0dd...1f6f41d

tidb_max_cpu: 21.77, delta: 17.99%
tikv_max_cpu: 9.47, delta: -10.54%
tidb_max_memory: 1790.06 MiB, delta: 1.21%
tikv_max_memory: 23364.78 MiB, delta: -4.59%

tps: 546.16 ± 1.4007% (std=4.00) delta: -9.97%
qps: 546.16 ± 1.4007% (std=4.00) delta: -9.97%
lat_avg: 468.65 ± 1.4188% (std=3.44) delta: 11.09%
lat_99th: 766.86 ± 2.7233% (std=12.77) delta: 14.47%

2. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
   51794e9

3. How to re-production

Uses the sysbench to run this script with 256 threads.

if sysbench.cmdline.command == nil then
    error("Command is required. Supported commands: run")
end

sysbench.cmdline.commands = {
    prepare = {
        function()
            drv = sysbench.sql.driver()
            con = drv:connect()
            con:query([[
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `c1` varchar(31) NOT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` varchar(255) DEFAULT NULL,
  `c4` smallint(6) DEFAULT NULL,
  `c5` bigint(64) DEFAULT NULL,
  `c6` varchar(255) DEFAULT NULL,
  `c7` varchar(255) DEFAULT NULL,
  `c8` varchar(255) DEFAULT NULL,
  `c9` varchar(255) DEFAULT NULL,
  `c10` varchar(255) DEFAULT NULL,
  `c11` bigint(64) DEFAULT NULL,
  `c12` bigint(64) DEFAULT NULL,
  `c13` bigint(64) DEFAULT NULL,
  `c14` varchar(300) DEFAULT NULL,
  `c15` int(11) NOT NULL,
  `c16` bigint(64) DEFAULT NULL,
  `c17` tinyint(4) DEFAULT NULL,
  `c18` bigint(20) DEFAULT NULL,
  `c19` smallint(6) DEFAULT NULL,
  `c20` varchar(255) DEFAULT NULL,
  `c21` varchar(255) DEFAULT NULL,
  `c22` bigint(64) DEFAULT NULL,
  `c23` varchar(255) DEFAULT NULL,
  `c24` int(11) DEFAULT NULL,
  `c25` bigint(64) DEFAULT NULL,
  `c26` bigint(64) DEFAULT NULL,
  `c27` smallint(6) DEFAULT NULL,
  `c28` varchar(255) DEFAULT NULL,
  `c29` varchar(255) DEFAULT NULL,
  `c30` smallint(6) DEFAULT NULL,
  `c31` varchar(255) DEFAULT NULL,
  `c32` smallint(6) DEFAULT NULL,
  `c33` varchar(255) DEFAULT NULL,
  `c34` varchar(255) DEFAULT NULL,
  `c35` bigint(20) DEFAULT NULL,
  `c36` varchar(64) DEFAULT NULL,
  `c37` bigint(20) DEFAULT NULL,
  `c38` varchar(64) DEFAULT NULL,
  `c39` varchar(16) DEFAULT NULL,
  `c40` varchar(6) DEFAULT NULL,
  `c41` bigint(64) DEFAULT NULL,
  `c42` tinyint(8) DEFAULT NULL,
  KEY `c13` (`c13`)
) SHARD_ROW_ID_BITS = 6, PRE_SPLIT_REGIONS = 6;
]])
            print("creating schema succeed")
        end,
        false
    }
}

sysbench.cmdline.options = {
    batch_size = { "The batch size", 100 }
}

function execute_inserts()
    con:bulk_insert_init("INSERT INTO t1(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11" ..
            ",c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22" ..
            ",c23,c24,c25,c26,c27,c28,c29,c30,c31,c32,c33" ..
            ",c34,c35,c36,c37,c38,c39,c40,c41,c42) values")

    for j = 1, sysbench.opt.batch_size do
        query = string.format("('%s',%d,'%s',%d,%d,'%s','%s','%s','%s','%s',%d,%d,'%s','%s',%d,%d,%d,%d,%d,'%s'" ..
                ",'%s',%d,'%s',%d,%d,%d,%d,'%s','%s',%d,'%s',%d,'%s','%s',%d,'%s',%d,'%s','%s','%s',%d,%d)",
            "shang",
            23,
            "shang",
            3,
            1,
            "123",
            "123",
            "123",
            "c9",
            "c10",
            11,
            12,
            sysbench.rand.string(string.rep("#", 18)),
            "c9",
            math.random(500000),
            16,
            17,
            18,
            19,
            "c20",
            "c21",
            22,
            "c23",
            24,
            25,
            26,
            27,
            "c28",
            "c29",
            30,
            "c9",
            32,
            "c33",
            "c9",
            35,
            "c36",
            37,
            "c9",
            "c9",
            "c9",
            41,
            42)
        con:bulk_insert_next(query)
    end
    con:bulk_insert_done()
end

-- Called by sysbench to initialize script
function thread_init()
    -- globals for script
    drv = sysbench.sql.driver()
    con = drv:connect()
end

-- Called by sysbench when tests are done
function thread_done()
    con:disconnect()
end

-- Called by sysbench for each execution
function event()
    execute_inserts()
end

[bb7133]

It is confirmed that the regression is caused by #21275, @rebelice PTAL.

[rebelice]

/assign

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

[v4.0.10:v4.0.11]

6. Fixed versions

master