Hoping to Have a Basic Understanding of TiDB Query and Insert Performance

Translated
1

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

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

| username: Qiuchi

Since I previously used Oracle, I will use Oracle as a benchmark for comparison in this issue. The test table is a table with 40 million rows and about 10 fields, with an ID primary key.

TiDB cluster: 3 PD nodes with 8 cores and 16 GB RAM, 3 DB nodes with 16 cores and 32 GB RAM, 6 KV nodes with 16 cores and 32 GB RAM, and 1 TB storage.
Oracle single machine: 16 cores and 32 GB RAM.

The first test is about the insert operation. When inserting data into the test table, using a single connection to insert and using multiple connections to insert simultaneously, within a certain range, the more connections there are, the faster the total insertion speed (the speed of each connection added together, with a maximum speed of about 20,000 rows per second). Compared to Oracle, when using batch processing, the single connection speed of Oracle is already very fast (also about 20,000 rows per second in the test case). If multiple connections are used to insert simultaneously, the speed of a single connection will decrease, and the total speed will be roughly the same as that of a single connection.

The second test is about the update operation. When querying data using the primary key ID and updating two of the fields, under a single connection, Oracle’s update speed is about 5,000 rows per second, while TiDB’s speed is 500 rows per second. If the number of connections is increased, Oracle’s behavior is basically the same as during insertion—the speed of a single connection is divided among multiple connections. However, for TiDB, when multiple connections update simultaneously, the overall speed does not increase, and the speed of the original single connection is divided among multiple connections.

Is this behavior normal for TiDB? Since the test scenario is quite general, I did not provide test data. If needed, I can upload it. Thank you.

2
| username: tidb狂热爱好者 | Original post link

TiDB needs to disable transaction support.
I’ll find some keywords for you.

3
| username: Qiuchi | Original post link

But I need transactions… Oracle is also tested under conditions with transactions.

4
| username: magic | Original post link

It shouldn’t be. Please post the table structure and the stress testing method.

5
| username: tidb狂热爱好者 | Original post link

Check out this best practice:
Batch insert with transactions disabled
[txn-local-latches]
enabled = false

Modify the insert statement to insert ignore. When the same data appears, it reports 1062 and returns to TiDB. Users have reported that after adjusting the statement, the issue disappeared, and QPS and duration returned to normal.

6
| username: tidb狂热爱好者 | Original post link

The most crucial thing is that you used an auto-increment primary key, which needs to be disabled.

7
| username: Qiuchi | Original post link

Actually, there was no dedicated stress testing. The issue was discovered during business parallel testing. The invocation method is JDBC, with MyBatis and some ETL tools on the upper layer, but the phenomena are similar, and it is the same for different tables.

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
8
| username: Qiuchi | Original post link

Hmm, but the regions of this table are already evenly distributed across 6 KVs.

9
| username: tidb狂热爱好者 | Original post link

This table was created in a single region, and then transaction management was disabled in TiDB specifically to handle inserts.

10
| username: BraveChen | Original post link

There are some fixed costs associated with distributed systems.

11
| username: 人如其名 | Original post link

Due to the existence of distributed transactions, it is normal for TiDB to perform 500 update operations per second with a single connection. A single-threaded Oracle performing 5k updates per second is too high (it should be around 1k, considering there is a commit), but TiDB can increase throughput with concurrency.

This is a test situation on my own computer, where each transaction contains one update statement in the form of update a set col1=xx, col2=yy where pk=zz. As the number of concurrent connections increases, the throughput generally increases (I have ensured at the application level that the same record is not updated to avoid lock conflicts), and it is not as bad as 500 TPS.

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
12
| username: Qiuchi | Original post link

I tried again later and found that TiDB indeed increased the overall speed after the concurrency went up, but I found that Oracle actually increased even faster with concurrency. There were some issues with the test results in the original topic. However, it feels like for a single connection and a single transaction, TiDB is still significantly slower than Oracle. Can this be considered an inherent overhead due to the architecture?

Using the primary key to query a non-indexed field, commit size = 1000

Query Situation

Both databases under a single connection

image
image1380×822 66.4 KB

After increasing TiDB connections to 10, the total speed is close to 18,000/s

image
image1380×968 167 KB

After increasing Oracle connections to 10, the speed exceeds 40,000/s

image
image1380×836 216 KB

Update Situation

Oracle single connection speed is about 3,700/s, previously 5,000 because some update values were set inappropriately causing skips

image
image1380×466 43 KB

TiDB single connection speed is about 450/s

image
image1380×760 51.2 KB

Oracle 10 connections combined speed is about 23,000/s

image
image1380×518 113 KB

TiDB 10 connections speed is about 3,300/s

image
image1380×854 153 KB

13
| username: 人如其名 | Original post link

To achieve Oracle’s speed, first, the table needs to be relatively large, and secondly, there need to be enough nodes. Specifically, how large the table should be and how many nodes are required? It needs to be tested in actual scenarios.

14
| username: h5n1 | Original post link

sysbench oltp_insert with 1 table under different threads, measuring TPS and average latency, each run lasting 10 minutes. k8s PDx3 4C 12G, tidbx3 12C 16G, tikv*3 16C 32G, regular SSD.

image
image869×321 14.8 KB

15
| username: system | Original post link

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