希望有一个对tidb查询和插入性能的基本概念

[Qiuchi (Ti D Ber T Hwl2t Uf)]

因笔者之前使用oracle，本问题中将以oracle作为对比基准，测试用表为一张数据量为4千万的表，约10字段，有id主键

tidb 集群 3pd 8c 16g， 3db 16c 32g，6kv 16c 32g 1T
oracle 单机 16c 32g

第一个测试关于insert操作，在向测试用表插入数据时，使用单个连接插入，和使用多个连接同时插入，在一定数量范围内，连接数越多，插入的总速度就越快（每个连接插入的速度相加，最大速度基本为2w/s）。这个行为对比oracle，在同样使用批处理时，oracle单连接速度就已经很快（在测试用例中同样基本为2w/s），若使用多个连接同时插入，单连接的速度反而会下降，总速度和单连接基本持平。

第二个测试关于update操作，在使用主键id查询数据，并更新其中两个字段时，单个连接下，oracle更新速度约5k/s，tidb速度为500/s。若提高连接数量，oracle行为和插入时基本一致——单连接速度被平分到多个连接中。但对于tidb，多个连接同时更新时，总体速度并没有提升，原先单连接的的速度也被平分至了多个连接中。

请问对于tidb，这种行为是否属实且为正常现象？因为测试场景比较通用所以未提供测试数据，如果需要的话我再上传，感谢

[tidb狂热爱好者]

tidb需要关闭事务支持
我给你找找关键词

[Qiuchi (Ti D Ber T Hwl2t Uf)]

可是我需要事务。。oracle也是在有事务的条件下测试

[magic]

不应该啊，压测表结构，还有压测方式贴一下吧

[tidb狂热爱好者]

https://docs.pingcap.com/zh/tidb/stable/high-concurrency-best-practices
你看这个最佳实践
批量插入关闭事务
[txn-local-latches]
enabled = false

insert 语句改造为 insert ignore，当出现相同的数据时，报 1062，并返回 tidb。用户反馈，调整语句后，现象消失，qps 以及 duration 恢复正常

[tidb狂热爱好者]

你最关键是用了自增主键 得关闭

[Qiuchi (Ti D Ber T Hwl2t Uf)]

其实没有专门进行压测，是在业务并跑测试时发现的问题，调用方式都是jdbc，再上层有mybatis和一些etl工具，但现象上都是类似的，不同的表也都是如此

create table "point_get_benchmark"
(
    "id"                          bigint(20)  not null,
    "sale_ta_cfm_serial_no"       varchar(64) not null,
    "buy_ta_cfm_serial_no"        varchar(64) not null,
    "sale_share"                  decimal(16, 4),
    "create_time"                 datetime(6) not null,
    "update_time"                 datetime(6) not null,
    "sale_amount"                 decimal(16, 4),
    "holding_period_yield"        decimal(18, 4),
    "holding_period_annual_yield" decimal(18, 4),
    primary key ("id") /*T![clustered_index] CLUSTERED */,
    key "idx_sale_buy_map_serialno" ("sale_ta_cfm_serial_no", "buy_ta_cfm_serial_no")
) engine = InnoDB
  default charset = utf8mb4
  collate = utf8mb4_bin
  auto_increment = 44096363

[Qiuchi (Ti D Ber T Hwl2t Uf)]

emmm可是这个表的region已经基本平均分在6个kv上了

[tidb狂热爱好者]

这表创建时是在一个regoin的 然后tidb里面关闭事务管理 专门处理插入

[BraveChen (我人傻了)]

分布式是有一些固定成本的

[人如其名]

因为存在分布式事务的原因，tidb一个连接update操作每秒500条属于正常，单线程oracle5k条太高了(感觉应该1k左右啊，毕竟有commit)，但是并发上来后tidb是可提升吞吐的。
这是我自己电脑上的一个测试情况，每一个事务一条update语句，update a set col1=xx,col2=yy where pk=zz，这种形式的，随着并发数的增加基本是会往上增长的（我这里在应用层面确定了不会update相同记录，避免锁冲突），并没有500tps那么差。

Time:2023-03-10-21.45.08           ConnInUse:1         UpdateSQLs:660
Time:2023-03-10-21.45.09           ConnInUse:1         UpdateSQLs:675
Time:2023-03-10-21.45.10           ConnInUse:2         UpdateSQLs:1073
Time:2023-03-10-21.45.11           ConnInUse:2         UpdateSQLs:1260
Time:2023-03-10-21.45.12           ConnInUse:2         UpdateSQLs:1290
Time:2023-03-10-21.45.13           ConnInUse:2         UpdateSQLs:1255
Time:2023-03-10-21.45.14           ConnInUse:2         UpdateSQLs:1232
Time:2023-03-10-21.45.15           ConnInUse:3         UpdateSQLs:1614
Time:2023-03-10-21.45.16           ConnInUse:3         UpdateSQLs:1725
Time:2023-03-10-21.45.17           ConnInUse:3         UpdateSQLs:1721
Time:2023-03-10-21.45.18           ConnInUse:3         UpdateSQLs:1264      
Time:2023-03-10-21.45.19           ConnInUse:3         UpdateSQLs:1705      
Time:2023-03-10-21.45.20           ConnInUse:4         UpdateSQLs:1906      
Time:2023-03-10-21.45.21           ConnInUse:4         UpdateSQLs:2021      
Time:2023-03-10-21.45.22           ConnInUse:4         UpdateSQLs:2007      
Time:2023-03-10-21.45.23           ConnInUse:4         UpdateSQLs:1989      
Time:2023-03-10-21.45.24           ConnInUse:4         UpdateSQLs:2030      
Time:2023-03-10-21.45.25           ConnInUse:5         UpdateSQLs:2158      
Time:2023-03-10-21.45.26           ConnInUse:5         UpdateSQLs:2175      
Time:2023-03-10-21.45.27           ConnInUse:5         UpdateSQLs:2196      
Time:2023-03-10-21.45.28           ConnInUse:5         UpdateSQLs:2213      
Time:2023-03-10-21.45.29           ConnInUse:5         UpdateSQLs:2215      
Time:2023-03-10-21.45.30           ConnInUse:6         UpdateSQLs:2368      
Time:2023-03-10-21.45.31           ConnInUse:6         UpdateSQLs:2474      
Time:2023-03-10-21.45.32           ConnInUse:6         UpdateSQLs:2558      
Time:2023-03-10-21.45.33           ConnInUse:6         UpdateSQLs:2459      
Time:2023-03-10-21.45.34           ConnInUse:6         UpdateSQLs:2537      
Time:2023-03-10-21.45.35           ConnInUse:7         UpdateSQLs:2665      
Time:2023-03-10-21.45.36           ConnInUse:7         UpdateSQLs:2839      
Time:2023-03-10-21.45.37           ConnInUse:7         UpdateSQLs:2754      
Time:2023-03-10-21.45.38           ConnInUse:7         UpdateSQLs:2696      
Time:2023-03-10-21.45.39           ConnInUse:7         UpdateSQLs:2568      
Time:2023-03-10-21.45.40           ConnInUse:8         UpdateSQLs:2781      
Time:2023-03-10-21.45.41           ConnInUse:8         UpdateSQLs:2729      
Time:2023-03-10-21.45.42           ConnInUse:8         UpdateSQLs:2388      
Time:2023-03-10-21.45.43           ConnInUse:8         UpdateSQLs:2861      
Time:2023-03-10-21.45.44           ConnInUse:8         UpdateSQLs:2393      
Time:2023-03-10-21.45.45           ConnInUse:9         UpdateSQLs:2714      
Time:2023-03-10-21.45.46           ConnInUse:9         UpdateSQLs:2661      
Time:2023-03-10-21.45.47           ConnInUse:9         UpdateSQLs:1788      
Time:2023-03-10-21.45.48           ConnInUse:9         UpdateSQLs:2437      
Time:2023-03-10-21.45.49           ConnInUse:9         UpdateSQLs:2857      
Time:2023-03-10-21.45.50           ConnInUse:10        UpdateSQLs:3039      
Time:2023-03-10-21.45.51           ConnInUse:10        UpdateSQLs:3269      
Time:2023-03-10-21.45.52           ConnInUse:10        UpdateSQLs:3367      
Time:2023-03-10-21.45.53           ConnInUse:10        UpdateSQLs:3495      
Time:2023-03-10-21.45.54           ConnInUse:10        UpdateSQLs:3258      
Time:2023-03-10-21.45.55           ConnInUse:11        UpdateSQLs:3378      
Time:2023-03-10-21.45.56           ConnInUse:11        UpdateSQLs:3499      
Time:2023-03-10-21.45.57           ConnInUse:11        UpdateSQLs:3006      
Time:2023-03-10-21.45.58           ConnInUse:11        UpdateSQLs:2980      
Time:2023-03-10-21.45.59           ConnInUse:11        UpdateSQLs:3269      
Time:2023-03-10-21.46.00           ConnInUse:12        UpdateSQLs:3244      
Time:2023-03-10-21.46.01           ConnInUse:12        UpdateSQLs:3567      
Time:2023-03-10-21.46.02           ConnInUse:12        UpdateSQLs:3674      
Time:2023-03-10-21.46.03           ConnInUse:12        UpdateSQLs:3581      
Time:2023-03-10-21.46.04           ConnInUse:12        UpdateSQLs:3772      
Time:2023-03-10-21.46.05           ConnInUse:13        UpdateSQLs:3530      
Time:2023-03-10-21.46.06           ConnInUse:13        UpdateSQLs:3738      
Time:2023-03-10-21.46.07           ConnInUse:13        UpdateSQLs:3551      
Time:2023-03-10-21.46.08           ConnInUse:13        UpdateSQLs:3851      
Time:2023-03-10-21.46.09           ConnInUse:13        UpdateSQLs:3456      
Time:2023-03-10-21.46.10           ConnInUse:14        UpdateSQLs:3953      
Time:2023-03-10-21.46.11           ConnInUse:14        UpdateSQLs:4019      
Time:2023-03-10-21.46.12           ConnInUse:14        UpdateSQLs:4018      
Time:2023-03-10-21.46.13           ConnInUse:14        UpdateSQLs:3983      
Time:2023-03-10-21.46.14           ConnInUse:14        UpdateSQLs:4117      
Time:2023-03-10-21.46.15           ConnInUse:15        UpdateSQLs:4129      
Time:2023-03-10-21.46.16           ConnInUse:15        UpdateSQLs:4184      
Time:2023-03-10-21.46.17           ConnInUse:15        UpdateSQLs:4144      
Time:2023-03-10-21.46.18           ConnInUse:15        UpdateSQLs:4131      
Time:2023-03-10-21.46.19           ConnInUse:15        UpdateSQLs:4384      
Time:2023-03-10-21.46.20           ConnInUse:16        UpdateSQLs:4471      
Time:2023-03-10-21.46.21           ConnInUse:16        UpdateSQLs:4438      
Time:2023-03-10-21.46.22           ConnInUse:16        UpdateSQLs:4058      
Time:2023-03-10-21.46.23           ConnInUse:16        UpdateSQLs:4118      
Time:2023-03-10-21.46.24           ConnInUse:16        UpdateSQLs:3957      
Time:2023-03-10-21.46.25           ConnInUse:17        UpdateSQLs:4495      
Time:2023-03-10-21.46.26           ConnInUse:17        UpdateSQLs:4615      
Time:2023-03-10-21.46.27           ConnInUse:17        UpdateSQLs:4614      
Time:2023-03-10-21.46.28           ConnInUse:17        UpdateSQLs:4614      
Time:2023-03-10-21.46.29           ConnInUse:17        UpdateSQLs:4566      
Time:2023-03-10-21.46.30           ConnInUse:18        UpdateSQLs:4799      
Time:2023-03-10-21.46.31           ConnInUse:18        UpdateSQLs:4776      
Time:2023-03-10-21.46.32           ConnInUse:18        UpdateSQLs:4761      
Time:2023-03-10-21.46.33           ConnInUse:18        UpdateSQLs:4505      
Time:2023-03-10-21.46.34           ConnInUse:18        UpdateSQLs:4939      
Time:2023-03-10-21.46.35           ConnInUse:19        UpdateSQLs:4927      
Time:2023-03-10-21.46.36           ConnInUse:19        UpdateSQLs:4705      
Time:2023-03-10-21.46.37           ConnInUse:19        UpdateSQLs:4862      
Time:2023-03-10-21.46.38           ConnInUse:19        UpdateSQLs:4935      
Time:2023-03-10-21.46.39           ConnInUse:19        UpdateSQLs:4866      
Time:2023-03-10-21.46.40           ConnInUse:20        UpdateSQLs:4901      
Time:2023-03-10-21.46.41           ConnInUse:20        UpdateSQLs:4992      
Time:2023-03-10-21.46.42           ConnInUse:20        UpdateSQLs:5061      
Time:2023-03-10-21.46.43           ConnInUse:20        UpdateSQLs:5312      
Time:2023-03-10-21.46.44           ConnInUse:20        UpdateSQLs:5122      
Time:2023-03-10-21.46.45           ConnInUse:20        UpdateSQLs:5061      
Time:2023-03-10-21.46.46           ConnInUse:20        UpdateSQLs:4737      
Time:2023-03-10-21.46.47           ConnInUse:20        UpdateSQLs:4987      
Time:2023-03-10-21.46.48           ConnInUse:20        UpdateSQLs:5061 

[Qiuchi (Ti D Ber T Hwl2t Uf)]

我后来又试了一下，发现tidb确实是并发量上去之后总速度上去了一些，但发现oracle实际上并发上去更快了。。原题目中的测试结果有些问题。但是感觉对于单个链接和一个txn来讲tidb还是比oracle都要慢不少，这个可以认为是架构上的固有的开销？

使用主键查一个无索引字段，commit size = 1000

查询的情况

单链接下两数据库

tidb链接加到10之后总速度接近1.8w/s

oracle链接加到10之后速度超过4w/s

更新的情况

oracle单链接的速度大约3700/s，之前5k是因为有的update值设置的不合适导致跳过了

tidb单链接的速度约为450/s

oracle10个链接综合约2.3w/s

tidb10链接约 3300/s

[人如其名]

想达到Oracle的速度，首先表要比较大，其次节点要足够多，具体什么表多大，节点多少？要实际场景测试才行。

[h5n1]

sysbench oltp_insert 1张表不同线程下的tps和平均延迟 ，每次持续10-分钟。k8s PDx3 4C 12G、tidbx3 12C 16G 、tikv*3 16C32G、普通ssd。