Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: tidb高并发大批量数据写入，如何提高qps
|
username: TiDBer_Xy7fsN7j
TiDB database configuration: 8-core CPU, 8GB memory, SSD solid-state drive. Currently, the test data is inserted in batches of 1000, and the QPS can only reach a maximum of 15, which means the processing capacity is only 20,000/s. How can I improve the QPS?
But batch insertion of 1000 records takes up to 2.5 seconds.
Is the CPU gone? Do you have 1000 values in a single SQL statement?
The insert statement is like this, with 1000 values.
The bottleneck is the CPU. If you don’t mind the hassle, you can try version 7.5. According to official data, the insert performance has doubled.
I don’t quite understand. The server has an 8-core CPU, and the CPU utilization is only 80%. There should still be a lot of CPU resources available, right?
This is just a value collected by the dashboard itself. You can use the dstat 1 command on the host to output the direct value. CPU usage includes not only user but also sys usage. Unless there is extremely high concurrent pressure, it is impossible to be at 100% for a long time. 85% is already considered full load.
What parameters can be set to allow the database to use the system’s CPU to the maximum?
The simplest way to improve QPS is to split the SQL and execute inserts in parallel.
You mean inserting fewer records per batch? But the final throughput = number of records per insert * QPS. If QPS goes up and the number of records per insert goes down, the product is still about the same.
Is there only one TiDB node? It looks like your cluster is a single machine.
Does your cluster have a topology, and how many TiKV nodes are there?
Try using multithreading, executing insert operations asynchronously with multiple threads in the code.
Try not to insert multiple values at once; instead, try inserting them concurrently. It seems this point is different from MySQL, right?
Here are a few issues related to Insert:
When inserting many values, the parse/compile time can be excessively long. To solve this problem, you can try the noprepare plancache feature and reduce the number of value groups. This Plan cache requires a fix to increase the length of the running values. However, it is an experimental feature.
Optimizer Fix Controls | PingCAP 文档中心
During massive inserts, IO pressure can be an issue. The solution is to use faster hard drives and add more TiKV nodes. If IO is not a problem, you can adjust several TiKV threads to increase downward pressure.
TiDB’s separation of storage and computation architecture inevitably leads to some performance loss. Additionally, it is best to connect to multiple inserts through load balancing when connecting to TiDB.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: