TiDB插入速度大概是什么水平

[江湖故人]

众所周知，TiDB 使⽤的 LSM Tree，LSM Tree 牺牲了⼀部分读的性能和增加了合并的开销，换取了⾼效的写性能，想知道一个大致的插入速度。
我的使用经验中，常见物理服务器，3节点ES集群大概是几千行每秒，单机Mysql和Oracle大概是几万到几十万行每秒。

有测试环境的大佬能否参考下面方式测试下：
mysql和oracle性能简单测试-CSDN博客

[zhanggame1 (Ti D Ber G I13ecx U)]

相同配置我测没插入mysql快

[xfworld (魔幻之翼)]

参考测试工具的使用，直接开测就好了
https://docs.pingcap.com/zh/tidb/stable/benchmark-tidb-using-sysbench

[随缘天空 (Ti D Ber Ivw R7o Pj)]

我之前使用sysbench做过压力测试，使用官方脚本创建16张表，每个表1000w数据，大概需要四十多分钟，32个表，每个表1000w数据的情况下大概六十多分钟

[春风十里 (Ti D Ber F Vf Ce7m B)]

论单机性能我估计TiDB赶不上MySQL，毕竟TiDB集群多干了很多活

[Jellybean (李文杰)]

之前的老版本我们测试过，sysbench tpcc TiDB纯插入性能有大几万以上。

如果你的业务表可以保证在5000万行以下，建议使用MySQL。它的性能表现会更好。

如果有更大量的数据存储和访问性能要求，使用TiDB是你要考虑的方案。

所以你的问题，由于性能表现和数据规模、业务访问情况是紧密相关，直接测试验证自己的场景拿到数据最有参考价值

[像风一样的男子]

数据库插入性能影响因素太多了，磁盘影响最大，这个是我之前的测试结果，每个人测得都会不一样

[托马斯滑板鞋]

按照上面的方法测了下，tidb从16384 增长到32768时，时间开始增多
1677W达到了1min18s

3节点
存储为普通SSD，64K随机写为440MB/s，99.95%=506msec

mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38113
Server version: 5.7.25-TiDB-v7.1.2 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

Copyright (c) 2009-2023 Percona LLC and/or its affiliates
Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table test_table (
    -> IDEN_STR1 varchar(32),
    -> IDEN_STR2 varchar(32),
    -> IDEN_STR3 varchar(32),
    -> IDEN_STR4 varchar(32),
    -> IDEN_STR5 varchar(32)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> insert into test_table values("1","2","3","4","5");
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> insert into test_table (select * from test_table);
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 8 rows affected, 1 warning (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 16 rows affected, 1 warning (0.01 sec)
Records: 16  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 32 rows affected, 1 warning (0.01 sec)
Records: 32  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 64 rows affected, 1 warning (0.01 sec)
Records: 64  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 128 rows affected, 1 warning (0.01 sec)
Records: 128  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 256 rows affected, 1 warning (0.01 sec)
Records: 256  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 512 rows affected, 1 warning (0.03 sec)
Records: 512  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 1024 rows affected, 1 warning (0.02 sec)
Records: 1024  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 2048 rows affected, 1 warning (0.03 sec)
Records: 2048  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 4096 rows affected, 1 warning (0.05 sec)
Records: 4096  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 8192 rows affected, 1 warning (0.11 sec)
Records: 8192  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
^[[AQuery OK, 16384 rows affected, 1 warning (0.49 sec)
Records: 16384  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 32768 rows affected, 1 warning (0.41 sec)
Records: 32768  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 65536 rows affected, 1 warning (0.63 sec)
Records: 65536  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 131072 rows affected, 1 warning (1.18 sec)
Records: 131072  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 262144 rows affected, 1 warning (2.22 sec)
Records: 262144  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 524288 rows affected, 1 warning (4.66 sec)
Records: 524288  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 1048576 rows affected, 1 warning (9.18 sec)
Records: 1048576  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 2097152 rows affected, 1 warning (18.65 sec)
Records: 2097152  Duplicates: 0  Warnings: 1

mysql> insert into test_table (select * from test_table);
Query OK, 4194304 rows affected, 1 warning (37.44 sec)
Records: 4194304  Duplicates: 0  Warnings: 1

mysql> alter table test_table set tiflash replica 1;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into test_table (select * from test_table);
Query OK, 8388608 rows affected, 1 warning (1 min 18.08 sec)
Records: 8388608  Duplicates: 0  Warnings: 1

mysql> select count(*) from test_table;
+----------+
| count(*) |
+----------+
| 16777216 |
+----------+
1 row in set, 1 warning (0.04 sec)

[托马斯滑板鞋]

加个索引，insert时间直接翻倍

mysql> update test_table set IDEN_STR4='10' where _tidb_rowid=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> alter table test_table compact;
Query OK, 0 rows affected (1.15 sec)

mysql> select * from test_table where iden_str4='10';
+-----------+-----------+-----------+-----------+-----------+
| IDEN_STR1 | IDEN_STR2 | IDEN_STR3 | IDEN_STR4 | IDEN_STR5 |
+-----------+-----------+-----------+-----------+-----------+
| 1         | 2         | 3         | 10        | 5         |
+-----------+-----------+-----------+-----------+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from test_table where iden_str4='10';
+-----------+-----------+-----------+-----------+-----------+
| IDEN_STR1 | IDEN_STR2 | IDEN_STR3 | IDEN_STR4 | IDEN_STR5 |
+-----------+-----------+-----------+-----------+-----------+
| 1         | 2         | 3         | 10        | 5         |
+-----------+-----------+-----------+-----------+-----------+
1 row in set, 1 warning (0.00 sec)

[Billmay表妹 (表妹)]

你自己测试的结果可以发出来，共享一下~

[托马斯滑板鞋]

不过看仓库里貌似有import into xxx select * from xxx的提案了，如果是直接底层物理拷贝，速度直接起飞

[zhanggame1 (Ti D Ber G I13ecx U)]

tidb额外加一个二级索引，insert性能降低一半，我测过

[TiDBer_小阿飞]

可以的，可以的，测试说干就干

[oceanzhang (张存海)]

测试了下，单机性能还没有mysql高，还没开始测集群

[Billdi表弟]

集群远远比mysql单机性能高的多，毕竟一群人打一个，哈哈哈

[Billdi表弟]

tidb写入性能还是还不错的

[Billdi表弟]

记得逐渐auto_random

[江湖故人]

很久以前测试的了

[andone (Ti D Ber Lq Q Mt Rp V)]

这个还是和数据量有关吧

[Kongdom]

建议使用SSD盘估计也是有提升插入速度这方面的考虑