This is an automated email from the ASF dual-hosted git repository.
dataroaring pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris-website.git
The following commit(s) were added to refs/heads/master by this push:
new 37502c82224 Update group commit performance doc (#939)
37502c82224 is described below
commit 37502c82224cc594087174de8793df2aee8410c7
Author: abmdocrt <lianyuk...@selectdb.com>
AuthorDate: Wed Nov 13 10:22:25 2024 +0800
Update group commit performance doc (#939)
---
.../import/import-way/group-commit-manual.md | 346 +++++++++++++++++---
.../import/import-way/group-commit-manual.md | 363 ++++++++++++++++++---
.../import/import-way/group-commit-manual.md | 362 +++++++++++++++++---
.../import/import-way/group-commit-manual.md | 361 +++++++++++++++++---
static/images/group-commit/jmeter1.jpg | Bin 0 -> 382117 bytes
static/images/group-commit/jmeter2.jpg | Bin 0 -> 327422 bytes
.../import/import-way/group-commit-manual.md | 350 +++++++++++++++++---
.../import/import-way/group-commit-manual.md | 346 +++++++++++++++++---
8 files changed, 1836 insertions(+), 292 deletions(-)
diff --git a/docs/data-operate/import/import-way/group-commit-manual.md
b/docs/data-operate/import/import-way/group-commit-manual.md
index af2515e23d2..c0c0fde0a62 100644
--- a/docs/data-operate/import/import-way/group-commit-manual.md
+++ b/docs/data-operate/import/import-way/group-commit-manual.md
@@ -44,6 +44,8 @@ Doris groups multiple loads into one transaction commit based
on the `group_comm
Doris writes data to the Write Ahead Log (WAL) firstly, then the load is
returned. Doris groups multiple loads into one transaction commit based on the
`group_commit_interval` table property, and the data is visible after the
commit. To prevent excessive disk space usage by the WAL, it automatically
switches to `sync_mode`. This is suitable for latency-sensitive and
high-frequency writing.
+The number of WALs can be viewed through the FE HTTP interface, as detailed
[here](../../admin-manual/fe/get-wal-size-action.md). Alternatively, you can
search for the keyword `wal` in the BE metrics.
+
## Basic operations
If the table schema is:
@@ -67,7 +69,7 @@ To reduce the CPU cost of SQL parsing and query planning, we
provide the `Prepar
1. Setup JDBC url and enable server side prepared statement
```
-url = jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true
+url =
jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=500
```
2. Set `group_commit` session variable, there are two ways to do it:
@@ -75,7 +77,7 @@ url = jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true
* Add `sessionVariables=group_commit=async_mode` in JDBC url
```
-url =
jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true&sessionVariables=group_commit=async_mode
+url = url =
jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=500&sessionVariables=group_commit=async_mode
```
* Use `SET group_commit = async_mode;` command
@@ -90,7 +92,7 @@ try (Statement statement = conn.createStatement()) {
```java
private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
-private static final String URL_PATTERN =
"jdbc:mysql://%s:%d/%s?useServerPrepStmts=true";
+private static final String URL_PATTERN =
"jdbc:mysql://%s:%d/%s?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=500&sessionVariables=group_commit=async_mode";
private static final String HOST = "127.0.0.1";
private static final int PORT = 9087;
private static final String DB = "db";
@@ -99,35 +101,12 @@ private static final String USER = "root";
private static final String PASSWD = "";
private static final int INSERT_BATCH_SIZE = 10;
-private static void groupCommitInsert() throws Exception {
- Class.forName(JDBC_DRIVER);
- try (Connection conn =
DriverManager.getConnection(String.format(URL_PATTERN, HOST, PORT, DB), USER,
PASSWD)) {
- // set session variable 'group_commit'
- try (Statement statement = conn.createStatement()) {
- statement.execute("SET group_commit = async_mode;");
- }
-
- String query = "insert into " + TBL + " values(?, ?, ?)";
- try (PreparedStatement stmt = conn.prepareStatement(query)) {
- for (int i = 0; i < INSERT_BATCH_SIZE; i++) {
- stmt.setInt(1, i);
- stmt.setString(2, "name" + i);
- stmt.setInt(3, i + 10);
- int result = stmt.executeUpdate();
- System.out.println("rows: " + result);
- }
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
-}
-
private static void groupCommitInsertBatch() throws Exception {
Class.forName(JDBC_DRIVER);
// add rewriteBatchedStatements=true and cachePrepStmts=true in JDBC url
// set session variables by sessionVariables=group_commit=async_mode in
JDBC url
try (Connection conn = DriverManager.getConnection(
- String.format(URL_PATTERN +
"&rewriteBatchedStatements=true&cachePrepStmts=true&sessionVariables=group_commit=async_mode",
HOST, PORT, DB), USER, PASSWD)) {
+ String.format(URL_PATTERN, HOST, PORT, DB), USER, PASSWD)) {
String query = "insert into " + TBL + " values(?, ?, ?)";
try (PreparedStatement stmt = conn.prepareStatement(query)) {
@@ -148,7 +127,122 @@ private static void groupCommitInsertBatch() throws
Exception {
}
```
-See [Synchronize Data Using Insert Method](./insert-into-manual) for more
details about **JDBC**.
+**Note:** Due to the high frequency of `INSERT INTO` statements, a large
amount of audit logs might be printed, which could impact overall performance.
By default, the audit log for prepared statements is disabled. You can control
whether to print the audit log for prepared statements by setting a session
variable.
+
+```sql
+# Configure the session variable to enable printing the audit log for prepared
statements. By default, it is set to false, which disables printing the audit
log for prepared statements.
+set enable_prepared_stmt_audit_log=true;
+```
+
+For more usage on **JDBC**, refer to [Using Insert to Synchronize
Data](./insert-into-manual).
+
+### Using Golang for Group Commit
+
+Golang has limited support for prepared statements, so we can manually batch
the statements on the client side to improve the performance of Group Commit.
Below is an example program.
+
+```Golang
+package main
+
+import (
+ "database/sql"
+ "fmt"
+ "math/rand"
+ "strings"
+ "sync"
+ "sync/atomic"
+ "time"
+
+ _ "github.com/go-sql-driver/mysql"
+)
+
+const (
+ host = "127.0.0.1"
+ port = 9038
+ db = "test"
+ user = "root"
+ password = ""
+ table = "async_lineitem"
+)
+
+var (
+ threadCount = 20
+ batchSize = 100
+)
+
+var totalInsertedRows int64
+var rowsInsertedLastSecond int64
+
+func main() {
+ dbDSN := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?parseTime=true", user, password,
host, port, db)
+ db, err := sql.Open("mysql", dbDSN)
+ if err != nil {
+ fmt.Printf("Error opening database: %s\n", err)
+ return
+ }
+ defer db.Close()
+
+ var wg sync.WaitGroup
+ for i := 0; i < threadCount; i++ {
+ wg.Add(1)
+ go func() {
+ defer wg.Done()
+ groupCommitInsertBatch(db)
+ }()
+ }
+
+ go logInsertStatistics()
+
+ wg.Wait()
+}
+
+func groupCommitInsertBatch(db *sql.DB) {
+ for {
+ valueStrings := make([]string, 0, batchSize)
+ valueArgs := make([]interface{}, 0, batchSize*16)
+ for i := 0; i < batchSize; i++ {
+ for i = 0; i < batchSize; i++ {
+ valueStrings = append(valueStrings, "(?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?)")
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, sql.NullFloat64{Float64: 1.0,
Valid: true})
+ valueArgs = append(valueArgs, sql.NullFloat64{Float64: 1.0,
Valid: true})
+ valueArgs = append(valueArgs, sql.NullFloat64{Float64: 1.0,
Valid: true})
+ valueArgs = append(valueArgs, sql.NullFloat64{Float64: 1.0,
Valid: true})
+ valueArgs = append(valueArgs, "N")
+ valueArgs = append(valueArgs, "O")
+ valueArgs = append(valueArgs, time.Now())
+ valueArgs = append(valueArgs, time.Now())
+ valueArgs = append(valueArgs, time.Now())
+ valueArgs = append(valueArgs, "DELIVER IN PERSON")
+ valueArgs = append(valueArgs, "SHIP")
+ valueArgs = append(valueArgs, "N/A")
+ }
+ }
+ stmt := fmt.Sprintf("INSERT INTO %s VALUES %s",
+ table, strings.Join(valueStrings, ","))
+ _, err := db.Exec(stmt, valueArgs...)
+ if err != nil {
+ fmt.Printf("Error executing batch: %s\n", err)
+ return
+ }
+ atomic.AddInt64(&rowsInsertedLastSecond, int64(batchSize))
+ atomic.AddInt64(&totalInsertedRows, int64(batchSize))
+ }
+}
+
+func logInsertStatistics() {
+ for {
+ time.Sleep(1 * time.Second)
+ fmt.Printf("Total inserted rows: %d\n", totalInsertedRows)
+ fmt.Printf("Rows inserted in the last second: %d\n",
rowsInsertedLastSecond)
+ rowsInsertedLastSecond = 0
+ }
+}
+
+```
+
### INSERT INTO VALUES
@@ -434,9 +528,13 @@ We have separately tested the write performance of group
commit in high-concurre
#### Environment
-* 1 FE: 8-core CPU, 16 GB RAM, 1 200 GB SSD disk
-* 3 BE: 16-core CPU, 64 GB RAM, 1 2 TB SSD disk
-* 1 Client: 8-core CPU, 64 GB RAM, 1 100 GB SSD disk
+* 1 Front End (FE) server: Alibaba Cloud with 8-core CPU, 16GB RAM, and one
100GB ESSD PL1 SSD.
+
+* 3 Backend (BE) servers: Alibaba Cloud with 16-core CPU, 64GB RAM, and one
1TB ESSD PL1 SSD.
+
+* 1 Test Client: Alibaba Cloud with 16-core CPU, 64GB RAM, and one 100GB ESSD
PL1 SSD.
+
+* The version for testing is Doris-2.1.5.
#### DataSet
@@ -448,24 +546,24 @@ We have separately tested the write performance of group
commit in high-concurre
#### Test Method
-* Setting different single-concurrency data size and concurrency num between
`non group_commit` and `group_commit` modes.
+* Setting different single-concurrency data size and concurrency num between
`non group_commit` and `group_commit=async mode` modes.
#### Test Result
| Load Way | Single-concurrency Data Size | Concurrency | Cost
Seconds | Rows / Seconds | MB / Seconds |
-|--------------------|------------------------------|-------------|--------------------|----------------|--------------|
-| `group_commit` | 10 KB | 10 | 3707
| 66,697 | 8.56 |
-| `group_commit` | 10 KB | 30 | 3385
| 73,042 | 9.38 |
-| `group_commit` | 100 KB | 10 | 473
| 522,725 | 67.11 |
-| `group_commit` | 100 KB | 30 | 390
| 633,972 | 81.39 |
-| `group_commit` | 500 KB | 10 | 323
| 765,477 | 98.28 |
-| `group_commit` | 500 KB | 30 | 309
| 800,158 | 102.56 |
-| `group_commit` | 1 MB | 10 | 304
| 813,319 | 104.24 |
-| `group_commit` | 1 MB | 30 | 286
| 864,507 | 110.88 |
-| `group_commit` | 10 MB | 10 | 290
| 852,583 | 109.28 |
-| `non group_commit` | 1 MB | 10 | `-235
error` | | |
-| `non group_commit` | 10 MB | 10 | 519
| 476,395 | 61.12 |
-| `non group_commit` | 10 MB | 30 | `-235
error` | | |
+|------------------|-------------|--------|-------------|--------------------|-------------------|
+| group_commit | 10 KB | 10 | 3306 | 74,787 | 9.8
|
+| group_commit | 10 KB | 30 | 3264 | 75,750 | 10.0
|
+| group_commit | 100 KB | 10 | 424 | 582,447 | 76.7
|
+| group_commit | 100 KB | 30 | 366 | 675,543 | 89.0
|
+| group_commit | 500 KB | 10 | 187 | 1,318,661 |
173.7 |
+| group_commit | 500 KB | 30 | 183 | 1,351,087 |
178.0 |
+| group_commit | 1 MB | 10 | 178 | 1,385,148 |
182.5 |
+| group_commit | 1 MB | 30 | 178 | 1,385,148 |
182.5 |
+| group_commit | 10 MB | 10 | 177 | 1,396,887 |
184.0 |
+| non group_commit | 1 MB | 10 | 2824 | 87,536 |
11.5 |
+| non group_commit | 10 MB | 10 | 450 | 549,442 |
68.9 |
+| non group_commit | 10 MB | 30 | 177 | 1,396,887 |
184.0 |
In the above test, the CPU usage of BE fluctuates between 10-40%.
@@ -475,9 +573,15 @@ The `group_commit` effectively enhances import performance
while reducing the nu
#### Environment
-* 1 FE: 8-core CPU, 16 GB RAM, 1 200 GB SSD disk
-* 1 BE: 16-core CPU, 64 GB RAM, 1 2 TB SSD disk
-* 1 Client: 16-core CPU, 64 GB RAM, 1 100 GB SSD disk
+1 Front End (FE) server: Alibaba Cloud with an 8-core CPU, 16GB RAM, and one
100GB ESSD PL1 SSD.
+
+1 Backend (BE) server: Alibaba Cloud with a 16-core CPU, 64GB RAM, and one
500GB ESSD PL1 SSD.
+
+1 Test Client: Alibaba Cloud with a 16-core CPU, 64GB RAM, and one 100GB ESSD
PL1 SSD.
+
+The testing version is Doris-2.1.5.
+
+Disable the printing of prepared statement audit logs to enhance performance.
#### DataSet
@@ -495,7 +599,149 @@ The `group_commit` effectively enhances import
performance while reducing the nu
| Rows per insert | Concurrency | Rows / Second | MB / Second |
-|-----------------|-------------|---------------|-------------|
-| 100 | 20 | 106931 | 11.46 |
+|-------------------|--------|--------------------|--------------------|
+| 100 | 10 | 107,172 | 11.47 |
+| 100 | 20 | 140,317 | 14.79 |
+| 100 | 30 | 142,882 | 15.28 |
In the above test, the CPU usage of BE fluctuates between 10-20%, FE
fluctuates between 60-70%.
+
+
+### Insert into Sync Mode Small Batch Data
+
+**Machine Configuration**
+
+* 1 Front-End (FE): Alibaba Cloud, 16-core CPU, 64GB RAM, 1 x 500GB ESSD PL1
cloud disk
+* 5 Back-End (BE) nodes: Alibaba Cloud, 16-core CPU, 64GB RAM, 1 x 1TB ESSD
PL1 cloud disk.
+* 1 Testing Client: Alibaba Cloud, 16-core CPU, 64GB RAM, 1 x 100GB ESSD PL1
cloud disk
+* Test version: Doris-2.1.5
+
+**Dataset**
+
+* The data of tpch sf10 `lineitem` table.
+
+* The create table statement is
+```sql
+CREATE TABLE IF NOT EXISTS lineitem (
+ L_ORDERKEY INTEGER NOT NULL,
+ L_PARTKEY INTEGER NOT NULL,
+ L_SUPPKEY INTEGER NOT NULL,
+ L_LINENUMBER INTEGER NOT NULL,
+ L_QUANTITY DECIMAL(15,2) NOT NULL,
+ L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
+ L_DISCOUNT DECIMAL(15,2) NOT NULL,
+ L_TAX DECIMAL(15,2) NOT NULL,
+ L_RETURNFLAG CHAR(1) NOT NULL,
+ L_LINESTATUS CHAR(1) NOT NULL,
+ L_SHIPDATE DATE NOT NULL,
+ L_COMMITDATE DATE NOT NULL,
+ L_RECEIPTDATE DATE NOT NULL,
+ L_SHIPINSTRUCT CHAR(25) NOT NULL,
+ L_SHIPMODE CHAR(10) NOT NULL,
+ L_COMMENT VARCHAR(44) NOT NULL
+)
+DUPLICATE KEY(L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER)
+DISTRIBUTED BY HASH(L_ORDERKEY) BUCKETS 32
+PROPERTIES (
+ "replication_num" = "3"
+);
+```
+
+**Testing Tool**
+
+* [Jmeter](https://jmeter.apache.org/)
+
+JMeter Parameter Settings as Shown in the Images
+
+
+
+
+1. Set the Init Statement Before Testing:
+set group_commit=async_mode and set enable_nereids_planner=false.
+
+2. Enable JDBC Prepared Statement:
+Complete URL:
+jdbc:mysql://127.0.0.1:9030?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=50&sessionVariables=group_commit=async_mode&sessionVariables=enable_nereids_planner=false.
+
+3. Set the Import Type to Prepared Update Statement.
+
+4. Set the Import Statement.
+
+5. Set the Values to Be Imported:
+Ensure that the imported values match the data types one by one.
+
+**Testing Methodology**
+
+* Use JMeter to write data into Doris. Each thread writes 1 row of data per
execution using the insert into statement.
+
+**Test Results**
+
+* Data unit: rows per second.
+
+* The following tests are divided into 30, 100, and 500 concurrency.
+
+**Performance Test with 30 Concurrent Users in Sync Mode, 5 BEs, and 3
Replicas**
+
+| Group Commit Interval | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+| | 321.5 | 307.3 | 285.8 | 224.3 |
+
+**Performance Test with 100 Concurrent Users in Sync Mode, 5 BEs, and 3
Replicas**
+
+| Group Commit Interval | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+| | 1175.2 | 1108.7 | 1016.3 | 704.5 |
+
+**Performance Test with 500 Concurrent Users in Sync Mode, 5 BEs, and 3
Replicas**
+
+| Group Commit Interval | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+| | 3289.8 | 3686.7 | 3280.7 | 2609.2 |
+
+### Insert into Sync Mode Large Batch Data
+
+**Machine Configuration**
+
+* 1 Front-End (FE): Alibaba Cloud, 16-core CPU, 64GB RAM, 1 x 500GB ESSD PL1
cloud disk
+
+* 5 Back-End (BE) nodes: Alibaba Cloud, 16-core CPU, 64GB RAM, 1 x 1TB ESSD
PL1 cloud disk.
+
+* 1 Testing Client: Alibaba Cloud, 16-core CPU, 64GB RAM, 1 x 100GB ESSD PL1
cloud disk
+
+* Test version: Doris-2.1.5
+
+**Dataset**
+
+* Insert into statement for 1000 rows: `insert into tbl values(1,1)...` (1000
rows omitted)
+
+**Testing Tool**
+
+* [Jmeter](https://jmeter.apache.org/)
+
+**Testing Methodology**
+
+* Use JMeter to write data into Doris. Each thread writes 1000 row of data per
execution using the insert into statement.
+
+**Test Results**
+
+* Data unit: rows per second.
+
+* The following tests are divided into 30, 100, and 500 concurrency.
+
+**Performance Test with 30 Concurrent Users in Sync Mode, 5 BEs, and 3
Replicas**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+| | 92.2K | 85.9K | 84K | 83.2K |
+
+**Performance Test with 100 Concurrent Users in Sync Mode, 5 BEs, and 3
Replicas**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+| | 70.4K |70.5K | 73.2K | 69.4K |
+
+**Performance Test with 500 Concurrent Users in Sync Mode, 5 BEs, and 3
Replicas**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+| | 46.3K | 47.7K | 47.4K | 46.5K |
\ No newline at end of file
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-operate/import/import-way/group-commit-manual.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-operate/import/import-way/group-commit-manual.md
index 86946b56b2f..a98bca0d222 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-operate/import/import-way/group-commit-manual.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/data-operate/import/import-way/group-commit-manual.md
@@ -43,6 +43,8 @@ Group Commit 写入有三种模式，分别是：
Doris 首先将数据写入 WAL (`Write Ahead Log`)，然后导入立即返回。Doris
会根据负载和表的`group_commit_interval`属性异步提交数据，提交之后数据可见。为了防止 WAL
占用较大的磁盘空间，单次导入数据量较大时，会自动切换为`sync_mode`。这适用于写入延迟敏感以及高频写入的场景。
+ WAL的数量可以通过FE
http接口查看，具体可见[这里](../../../admin-manual/fe/get-wal-size-action.md)，也可以在BE的metrics中搜索关键词`wal`查看。
+
## Group Commit 使用方式
假如表的结构为：
@@ -66,7 +68,7 @@ PROPERTIES (
**1. 设置 JDBC URL 并在 Server 端开启 Prepared Statement**
```
-url = jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true
+url =
jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=500
```
**2. 配置 `group_commit` session 变量，有如下两种方式：**
@@ -74,7 +76,7 @@ url = jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true
* 通过 JDBC url 设置，增加`sessionVariables=group_commit=async_mode`
```
- url =
jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true&sessionVariables=group_commit=async_mode
+ url =
jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=500&sessionVariables=group_commit=async_mode&sessionVariables=enable_nereids_planner=false
```
* 通过执行 SQL 设置
@@ -89,44 +91,21 @@ url = jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true
```java
private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
-private static final String URL_PATTERN =
"jdbc:mysql://%s:%d/%s?useServerPrepStmts=true";
+private static final String URL_PATTERN =
"jdbc:mysql://%s:%d/%s?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=500&sessionVariables=group_commit=async_mode&sessionVariables=enable_nereids_planner=false";
private static final String HOST = "127.0.0.1";
private static final int PORT = 9087;
private static final String DB = "db";
private static final String TBL = "dt";
private static final String USER = "root";
private static final String PASSWD = "";
-private static final int INSERT_BATCH_SIZE = 10;
-
-private static void groupCommitInsert() throws Exception {
- Class.forName(JDBC_DRIVER);
- try (Connection conn =
DriverManager.getConnection(String.format(URL_PATTERN, HOST, PORT, DB), USER,
PASSWD)) {
- // set session variable 'group_commit'
- try (Statement statement = conn.createStatement()) {
- statement.execute("SET group_commit = async_mode;");
- }
-
- String query = "insert into " + TBL + " values(?, ?, ?)";
- try (PreparedStatement stmt = conn.prepareStatement(query)) {
- for (int i = 0; i < INSERT_BATCH_SIZE; i++) {
- stmt.setInt(1, i);
- stmt.setString(2, "name" + i);
- stmt.setInt(3, i + 10);
- int result = stmt.executeUpdate();
- System.out.println("rows: " + result);
- }
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
-}
+private static final int INSERT_BATCH_SIZE = 10;
private static void groupCommitInsertBatch() throws Exception {
Class.forName(JDBC_DRIVER);
// add rewriteBatchedStatements=true and cachePrepStmts=true in JDBC url
// set session variables by sessionVariables=group_commit=async_mode in
JDBC url
try (Connection conn = DriverManager.getConnection(
- String.format(URL_PATTERN +
"&rewriteBatchedStatements=true&cachePrepStmts=true&sessionVariables=group_commit=async_mode",
HOST, PORT, DB), USER, PASSWD)) {
+ String.format(URL_PATTERN, HOST, PORT, DB), USER, PASSWD)) {
String query = "insert into " + TBL + " values(?, ?, ?)";
try (PreparedStatement stmt = conn.prepareStatement(query)) {
@@ -147,8 +126,122 @@ private static void groupCommitInsertBatch() throws
Exception {
}
```
+注意：由于高频的insert into语句会打印大量的audit log，对最终性能有一定影响，默认关闭了打印prepared语句的audit
log。可以通过设置session variable的方式控制是否打印prepared语句的audit log。
+
+```sql
+# 配置 session 变量开启打印parpared语句的audit log, 默认为false即关闭打印parpared语句的audit log。
+set enable_prepared_stmt_audit_log=true;
+```
+
关于 **JDBC** 的更多用法，参考[使用 Insert 方式同步数据](./insert-into-manual.md)。
+### 使用Golang进行Group Commit
+
+Golang的prepared语句支持有限，所以我们可以通过手动客户端攒批的方式提高Group Commit的性能，以下为一个示例程序。
+
+```Golang
+package main
+
+import (
+ "database/sql"
+ "fmt"
+ "math/rand"
+ "strings"
+ "sync"
+ "sync/atomic"
+ "time"
+
+ _ "github.com/go-sql-driver/mysql"
+)
+
+const (
+ host = "127.0.0.1"
+ port = 9038
+ db = "test"
+ user = "root"
+ password = ""
+ table = "async_lineitem"
+)
+
+var (
+ threadCount = 20
+ batchSize = 100
+)
+
+var totalInsertedRows int64
+var rowsInsertedLastSecond int64
+
+func main() {
+ dbDSN := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?parseTime=true", user,
password, host, port, db)
+ db, err := sql.Open("mysql", dbDSN)
+ if err != nil {
+ fmt.Printf("Error opening database: %s\n", err)
+ return
+ }
+ defer db.Close()
+
+ var wg sync.WaitGroup
+ for i := 0; i < threadCount; i++ {
+ wg.Add(1)
+ go func() {
+ defer wg.Done()
+ groupCommitInsertBatch(db)
+ }()
+ }
+
+ go logInsertStatistics()
+
+ wg.Wait()
+}
+
+func groupCommitInsertBatch(db *sql.DB) {
+ for {
+ valueStrings := make([]string, 0, batchSize)
+ valueArgs := make([]interface{}, 0, batchSize*16)
+ for i := 0; i < batchSize; i++ {
+ for i = 0; i < batchSize; i++ {
+ valueStrings = append(valueStrings, "(?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs,
sql.NullFloat64{Float64: 1.0, Valid: true})
+ valueArgs = append(valueArgs,
sql.NullFloat64{Float64: 1.0, Valid: true})
+ valueArgs = append(valueArgs,
sql.NullFloat64{Float64: 1.0, Valid: true})
+ valueArgs = append(valueArgs,
sql.NullFloat64{Float64: 1.0, Valid: true})
+ valueArgs = append(valueArgs, "N")
+ valueArgs = append(valueArgs, "O")
+ valueArgs = append(valueArgs, time.Now())
+ valueArgs = append(valueArgs, time.Now())
+ valueArgs = append(valueArgs, time.Now())
+ valueArgs = append(valueArgs, "DELIVER IN
PERSON")
+ valueArgs = append(valueArgs, "SHIP")
+ valueArgs = append(valueArgs, "N/A")
+ }
+ }
+ stmt := fmt.Sprintf("INSERT INTO %s VALUES %s",
+ table, strings.Join(valueStrings, ","))
+ _, err := db.Exec(stmt, valueArgs...)
+ if err != nil {
+ fmt.Printf("Error executing batch: %s\n", err)
+ return
+ }
+ atomic.AddInt64(&rowsInsertedLastSecond, int64(batchSize))
+ atomic.AddInt64(&totalInsertedRows, int64(batchSize))
+ }
+}
+
+func logInsertStatistics() {
+ for {
+ time.Sleep(1 * time.Second)
+ fmt.Printf("Total inserted rows: %d\n", totalInsertedRows)
+ fmt.Printf("Rows inserted in the last second: %d\n",
rowsInsertedLastSecond)
+ rowsInsertedLastSecond = 0
+ }
+}
+
+```
+
### INSERT INTO VALUES
* 异步模式
@@ -443,11 +536,13 @@ ALTER TABLE dt SET ("group_commit_data_bytes" =
"134217728");
**机器配置**
-* 1 台 FE：8 核 CPU、16GB 内存、1 块 200GB 通用性 SSD 云磁盘
+* 1 台 FE：阿里云 8 核 CPU、16GB 内存、1 块 100GB ESSD PL1 云磁盘
-* 3 台 BE：16 核 CPU、64GB 内存、1 块 2TB 通用性 SSD 云磁盘
+* 3 台 BE：阿里云 16 核 CPU、64GB 内存、1 块 1TB ESSD PL1 云磁盘
-* 1 台测试客户端：16 核 CPU、64GB 内存、1 块 100GB 通用型 SSD 云磁盘
+* 1 台测试客户端：阿里云 16 核 CPU、64GB 内存、1 块 100GB ESSD PL1 云磁盘
+
+* 测试版本为Doris-3.0.1
**数据集**
@@ -465,18 +560,18 @@ ALTER TABLE dt SET ("group_commit_data_bytes" =
"134217728");
| 导入方式 | 单并发数据量 | 并发数 | 耗时 (秒) | 导入速率 (行/秒) | 导入吞吐 (MB/秒) |
|----------------|---------|------|-----------|----------|-----------|
-| `group_commit` | 10 KB | 10 | 3707 | 66,697 | 8.56 |
-| `group_commit` | 10 KB | 30 | 3385 | 73,042 | 9.38 |
-| `group_commit` | 100 KB | 10 | 473 | 522,725 | 67.11 |
-| `group_commit` | 100 KB | 30 | 390 | 633,972 | 81.39 |
-| `group_commit` | 500 KB | 10 | 323 | 765,477 | 98.28 |
-| `group_commit` | 500 KB | 30 | 309 | 800,158 | 102.56 |
-| `group_commit` | 1 MB | 10 | 304 | 813,319 | 104.24 |
-| `group_commit` | 1 MB | 30 | 286 | 864,507 | 110.88 |
-| `group_commit` | 10 MB | 10 | 290 | 852,583 | 109.28 |
-| `非group_commit` | 1 MB | 10 | 导入报错 -235 | | |
-| `非group_commit` | 10 MB | 10 | 519 | 476,395 | 61.12 |
-| `非group_commit` | 10 MB | 30 | 导入报错 -235 | | |
+| `group_commit` | 10 KB | 10 | 2204 | 112,181 | 14.8 |
+| `group_commit` | 10 KB | 30 | 2176 | 113,625 | 15.0 |
+| `group_commit` | 100 KB | 10 | 283 | 873,671 | 115.1 |
+| `group_commit` | 100 KB | 30 | 244 | 1,013,315 | 133.5 |
+| `group_commit` | 500 KB | 10 | 125 | 1,977,992 | 260.6 |
+| `group_commit` | 500 KB | 30 | 122 | 2,026,631 | 267.1 |
+| `group_commit` | 1 MB | 10 | 119 | 2,077,723 | 273.8 |
+| `group_commit` | 1 MB | 30 | 119 | 2,077,723 | 273.8 |
+| `group_commit` | 10 MB | 10 | 118 | 2,095,331 | 276.1 |
+| `非group_commit` | 1 MB | 10 | 1883 | 131,305 | 17.3|
+| `非group_commit` | 10 MB | 10 | 294 | 840,983 | 105.4 |
+| `非group_commit` | 10 MB | 30 | 118 | 2,095,331 | 276.1|
在上面的`group_commit`测试中，BE 的 CPU 使用率在 10-40% 之间。
@@ -486,11 +581,15 @@ ALTER TABLE dt SET ("group_commit_data_bytes" =
"134217728");
**机器配置**
-* 1 台 FE：8 核 CPU、16 GB 内存、1 块 200 GB 通用性 SSD 云磁盘
+* 1 台 FE：阿里云 8 核 CPU、16GB 内存、1 块 100GB ESSD PL1 云磁盘
+
+* 1 台 BE：阿里云 16 核 CPU、64GB 内存、1 块 500GB ESSD PL1 云磁盘
+
+* 1 台测试客户端：阿里云 16 核 CPU、64GB 内存、1 块 100GB ESSD PL1 云磁盘
-* 1 台 BE：16 核 CPU、64 GB 内存、1 块 2 TB 通用性 SSD 云磁盘
+* 测试版本为Doris-3.0.1
-* 1 台测试客户端：16 核 CPU、64GB 内存、1 块 100 GB 通用型 SSD 云磁盘
+* 关闭打印parpared语句的audit log以提高性能
**数据集**
@@ -508,6 +607,176 @@ ALTER TABLE dt SET ("group_commit_data_bytes" =
"134217728");
| 单个 insert 的行数 | 并发数 | 导入速率 (行/秒) | 导入吞吐 (MB/秒) |
|-------------|-----|-----------|----------|
-| 100 | 20 | 106931 | 11.46 |
+| 100 | 10 | 160,758 | 17.21 |
+| 100 | 20 | 210,476 | 22.19 |
+| 100 | 30 | 214,323 | 22.92 |
在上面的测试中，FE 的 CPU 使用率在 60-70% 左右，BE 的 CPU 使用率在 10-20% 左右。
+
+### Insert into sync 模式小批量数据
+
+**机器配置**
+
+* 1 台 FE：阿里云 16 核 CPU、64GB 内存、1 块 500GB ESSD PL1 云磁盘
+
+* 5 台 BE：阿里云 16 核 CPU、64GB 内存、1 块 1TB ESSD PL1 云磁盘。
+
+* 1 台测试客户端：阿里云 16 核 CPU、64GB 内存、1 块 100GB ESSD PL1 云磁盘
+
+* 测试版本为Doris-3.0.1
+
+**数据集**
+
+* tpch sf10 `lineitem` 表数据集。
+
+* 建表语句为
+```sql
+CREATE TABLE IF NOT EXISTS lineitem (
+ L_ORDERKEY INTEGER NOT NULL,
+ L_PARTKEY INTEGER NOT NULL,
+ L_SUPPKEY INTEGER NOT NULL,
+ L_LINENUMBER INTEGER NOT NULL,
+ L_QUANTITY DECIMAL(15,2) NOT NULL,
+ L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
+ L_DISCOUNT DECIMAL(15,2) NOT NULL,
+ L_TAX DECIMAL(15,2) NOT NULL,
+ L_RETURNFLAG CHAR(1) NOT NULL,
+ L_LINESTATUS CHAR(1) NOT NULL,
+ L_SHIPDATE DATE NOT NULL,
+ L_COMMITDATE DATE NOT NULL,
+ L_RECEIPTDATE DATE NOT NULL,
+ L_SHIPINSTRUCT CHAR(25) NOT NULL,
+ L_SHIPMODE CHAR(10) NOT NULL,
+ L_COMMENT VARCHAR(44) NOT NULL
+)
+DUPLICATE KEY(L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER)
+DISTRIBUTED BY HASH(L_ORDERKEY) BUCKETS 32
+PROPERTIES (
+ "replication_num" = "3"
+);
+```
+
+**测试工具**
+
+* [Jmeter](https://jmeter.apache.org/)
+
+需要设置的jmeter参数如下图所示
+
+
+
+
+1. 设置测试前的init语句，`set group_commit=async_mode`以及`set
enable_nereids_planner=false`。
+2. 开启jdbc的prepared
statement，完整的url为`jdbc:mysql://127.0.0.1:9030?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=50&sessionVariables=group_commit=async_mode&sessionVariables=enable_nereids_planner=false`。
+3. 设置导入类型为prepared update statement。
+4. 设置导入语句。
+5. 设置每次需要导入的值，注意，导入的值与导入值的类型要一一匹配。
+
+**测试方法**
+
+* 通过 `Jmeter` 向`Doris`写数据。每个并发每次通过insert into写入1行数据。
+
+**测试结果**
+
+* 数据单位为行每秒。
+
+* 以下测试分为30，100，500并发。
+
+**30并发sync模式5个BE3副本性能测试**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+|enable_nereids_planner=true| 891.8 | 701.1 | 400.0 | 237.5 |
+|enable_nereids_planner=false| 885.8 | 688.1 | 398.7 | 232.9
|
+
+
+**100并发sync模式5个BE3副本性能测试**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+|enable_nereids_planner=true| 2427.8 | 2068.9 | 1259.4 | 764.9 |
+|enable_nereids_planner=false| 2320.4 | 1899.3 | 1206.2 |749.7|
+
+**500并发sync模式5个BE3副本性能测试**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+|enable_nereids_planner=true| 5567.5 | 5713.2 | 4681.0 | 3131.2 |
+|enable_nereids_planner=false| 4471.6 | 5042.5 | 4932.2 | 3641.1 |
+
+### Insert into sync 模式大批量数据
+
+**机器配置**
+
+* 1 台 FE：阿里云 16 核 CPU、64GB 内存、1 块 500GB ESSD PL1 云磁盘
+
+* 5 台 BE：阿里云 16 核 CPU、64GB 内存、1 块 1TB ESSD PL1 云磁盘。注：测试中分别用了1台，3台，5台BE进行测试。
+
+* 1 台测试客户端：阿里云 16 核 CPU、64GB 内存、1 块 100GB ESSD PL1 云磁盘
+
+* 测试版本为Doris-3.0.1
+
+**数据集**
+
+* tpch sf10 `lineitem` 表数据集。
+
+* 建表语句为
+```sql
+CREATE TABLE IF NOT EXISTS lineitem (
+ L_ORDERKEY INTEGER NOT NULL,
+ L_PARTKEY INTEGER NOT NULL,
+ L_SUPPKEY INTEGER NOT NULL,
+ L_LINENUMBER INTEGER NOT NULL,
+ L_QUANTITY DECIMAL(15,2) NOT NULL,
+ L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
+ L_DISCOUNT DECIMAL(15,2) NOT NULL,
+ L_TAX DECIMAL(15,2) NOT NULL,
+ L_RETURNFLAG CHAR(1) NOT NULL,
+ L_LINESTATUS CHAR(1) NOT NULL,
+ L_SHIPDATE DATE NOT NULL,
+ L_COMMITDATE DATE NOT NULL,
+ L_RECEIPTDATE DATE NOT NULL,
+ L_SHIPINSTRUCT CHAR(25) NOT NULL,
+ L_SHIPMODE CHAR(10) NOT NULL,
+ L_COMMENT VARCHAR(44) NOT NULL
+)
+DUPLICATE KEY(L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER)
+DISTRIBUTED BY HASH(L_ORDERKEY) BUCKETS 32
+PROPERTIES (
+ "replication_num" = "3"
+);
+```
+
+**测试工具**
+
+* [Jmeter](https://jmeter.apache.org/)
+
+**测试方法**
+
+* 通过 `Jmeter` 向`Doris`写数据。每个并发每次通过insert into写入1000行数据。
+
+**测试结果**
+
+* 数据单位为行每秒。
+
+* 以下测试分为30，100，500并发。
+
+**30并发sync模式5个BE3副本性能测试**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+|enable_nereids_planner=true| 9.1K | 11.1K | 11.4K | 11.1K |
+|enable_nereids_planner=false| 157.8K | 159.9K | 154.1K | 120.4K
|
+
+**100并发sync模式5个BE3副本性能测试**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+|enable_nereids_planner=true| 10.0K |9.2K | 8.9K | 8.9K |
+|enable_nereids_planner=false| 130.4k | 131.0K | 130.4K | 124.1K
|
+
+**500并发sync模式5个BE3副本性能测试**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+|enable_nereids_planner=true| 2.5K | 2.5K | 2.3K | 2.1K |
+|enable_nereids_planner=false| 94.2K | 95.1K | 94.4K | 94.8K |
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/data-operate/import/import-way/group-commit-manual.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/data-operate/import/import-way/group-commit-manual.md
index 86946b56b2f..a45bacfa7bc 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/data-operate/import/import-way/group-commit-manual.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/data-operate/import/import-way/group-commit-manual.md
@@ -43,6 +43,8 @@ Group Commit 写入有三种模式，分别是：
Doris 首先将数据写入 WAL (`Write Ahead Log`)，然后导入立即返回。Doris
会根据负载和表的`group_commit_interval`属性异步提交数据，提交之后数据可见。为了防止 WAL
占用较大的磁盘空间，单次导入数据量较大时，会自动切换为`sync_mode`。这适用于写入延迟敏感以及高频写入的场景。
+ WAL的数量可以通过FE
http接口查看，具体可见[这里](../../admin-manual/fe/get-wal-size-action.md)，也可以在BE的metrics中搜索关键词`wal`查看。
+
## Group Commit 使用方式
假如表的结构为：
@@ -66,7 +68,7 @@ PROPERTIES (
**1. 设置 JDBC URL 并在 Server 端开启 Prepared Statement**
```
-url = jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true
+url =
jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=500
```
**2. 配置 `group_commit` session 变量，有如下两种方式：**
@@ -74,7 +76,7 @@ url = jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true
* 通过 JDBC url 设置，增加`sessionVariables=group_commit=async_mode`
```
- url =
jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true&sessionVariables=group_commit=async_mode
+ url =
jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=500&sessionVariables=group_commit=async_mode
```
* 通过执行 SQL 设置
@@ -89,7 +91,7 @@ url = jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true
```java
private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
-private static final String URL_PATTERN =
"jdbc:mysql://%s:%d/%s?useServerPrepStmts=true";
+private static final String URL_PATTERN =
"jdbc:mysql://%s:%d/%s?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=50$sessionVariables=group_commit=async_mode";
private static final String HOST = "127.0.0.1";
private static final int PORT = 9087;
private static final String DB = "db";
@@ -98,35 +100,12 @@ private static final String USER = "root";
private static final String PASSWD = "";
private static final int INSERT_BATCH_SIZE = 10;
-private static void groupCommitInsert() throws Exception {
- Class.forName(JDBC_DRIVER);
- try (Connection conn =
DriverManager.getConnection(String.format(URL_PATTERN, HOST, PORT, DB), USER,
PASSWD)) {
- // set session variable 'group_commit'
- try (Statement statement = conn.createStatement()) {
- statement.execute("SET group_commit = async_mode;");
- }
-
- String query = "insert into " + TBL + " values(?, ?, ?)";
- try (PreparedStatement stmt = conn.prepareStatement(query)) {
- for (int i = 0; i < INSERT_BATCH_SIZE; i++) {
- stmt.setInt(1, i);
- stmt.setString(2, "name" + i);
- stmt.setInt(3, i + 10);
- int result = stmt.executeUpdate();
- System.out.println("rows: " + result);
- }
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
-}
-
private static void groupCommitInsertBatch() throws Exception {
Class.forName(JDBC_DRIVER);
// add rewriteBatchedStatements=true and cachePrepStmts=true in JDBC url
// set session variables by sessionVariables=group_commit=async_mode in
JDBC url
try (Connection conn = DriverManager.getConnection(
- String.format(URL_PATTERN +
"&rewriteBatchedStatements=true&cachePrepStmts=true&sessionVariables=group_commit=async_mode",
HOST, PORT, DB), USER, PASSWD)) {
+ String.format(URL_PATTERN, HOST, PORT, DB), USER, PASSWD)) {
String query = "insert into " + TBL + " values(?, ?, ?)";
try (PreparedStatement stmt = conn.prepareStatement(query)) {
@@ -147,8 +126,122 @@ private static void groupCommitInsertBatch() throws
Exception {
}
```
+注意：由于高频的insert into语句会打印大量的audit log，对最终性能有一定影响，默认关闭了打印prepared语句的audit
log。可以通过设置session variable的方式控制是否打印prepared语句的audit log。
+
+```sql
+# 配置 session 变量开启打印parpared语句的audit log, 默认为false即关闭打印parpared语句的audit log。
+set enable_prepared_stmt_audit_log=true;
+```
+
关于 **JDBC** 的更多用法，参考[使用 Insert 方式同步数据](./insert-into-manual.md)。
+### 使用Golang进行Group Commit
+
+Golang的prepared语句支持有限，所以我们可以通过手动客户端攒批的方式提高Group Commit的性能，以下为一个示例程序。
+
+```Golang
+package main
+
+import (
+ "database/sql"
+ "fmt"
+ "math/rand"
+ "strings"
+ "sync"
+ "sync/atomic"
+ "time"
+
+ _ "github.com/go-sql-driver/mysql"
+)
+
+const (
+ host = "127.0.0.1"
+ port = 9038
+ db = "test"
+ user = "root"
+ password = ""
+ table = "async_lineitem"
+)
+
+var (
+ threadCount = 20
+ batchSize = 100
+)
+
+var totalInsertedRows int64
+var rowsInsertedLastSecond int64
+
+func main() {
+ dbDSN := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?parseTime=true", user, password,
host, port, db)
+ db, err := sql.Open("mysql", dbDSN)
+ if err != nil {
+ fmt.Printf("Error opening database: %s\n", err)
+ return
+ }
+ defer db.Close()
+
+ var wg sync.WaitGroup
+ for i := 0; i < threadCount; i++ {
+ wg.Add(1)
+ go func() {
+ defer wg.Done()
+ groupCommitInsertBatch(db)
+ }()
+ }
+
+ go logInsertStatistics()
+
+ wg.Wait()
+}
+
+func groupCommitInsertBatch(db *sql.DB) {
+ for {
+ valueStrings := make([]string, 0, batchSize)
+ valueArgs := make([]interface{}, 0, batchSize*16)
+ for i := 0; i < batchSize; i++ {
+ for i = 0; i < batchSize; i++ {
+ valueStrings = append(valueStrings, "(?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?)")
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, sql.NullFloat64{Float64: 1.0,
Valid: true})
+ valueArgs = append(valueArgs, sql.NullFloat64{Float64: 1.0,
Valid: true})
+ valueArgs = append(valueArgs, sql.NullFloat64{Float64: 1.0,
Valid: true})
+ valueArgs = append(valueArgs, sql.NullFloat64{Float64: 1.0,
Valid: true})
+ valueArgs = append(valueArgs, "N")
+ valueArgs = append(valueArgs, "O")
+ valueArgs = append(valueArgs, time.Now())
+ valueArgs = append(valueArgs, time.Now())
+ valueArgs = append(valueArgs, time.Now())
+ valueArgs = append(valueArgs, "DELIVER IN PERSON")
+ valueArgs = append(valueArgs, "SHIP")
+ valueArgs = append(valueArgs, "N/A")
+ }
+ }
+ stmt := fmt.Sprintf("INSERT INTO %s VALUES %s",
+ table, strings.Join(valueStrings, ","))
+ _, err := db.Exec(stmt, valueArgs...)
+ if err != nil {
+ fmt.Printf("Error executing batch: %s\n", err)
+ return
+ }
+ atomic.AddInt64(&rowsInsertedLastSecond, int64(batchSize))
+ atomic.AddInt64(&totalInsertedRows, int64(batchSize))
+ }
+}
+
+func logInsertStatistics() {
+ for {
+ time.Sleep(1 * time.Second)
+ fmt.Printf("Total inserted rows: %d\n", totalInsertedRows)
+ fmt.Printf("Rows inserted in the last second: %d\n",
rowsInsertedLastSecond)
+ rowsInsertedLastSecond = 0
+ }
+}
+
+```
+
### INSERT INTO VALUES
* 异步模式
@@ -443,11 +536,13 @@ ALTER TABLE dt SET ("group_commit_data_bytes" =
"134217728");
**机器配置**
-* 1 台 FE：8 核 CPU、16GB 内存、1 块 200GB 通用性 SSD 云磁盘
+* 1 台 FE：阿里云 8 核 CPU、16GB 内存、1 块 100GB ESSD PL1 云磁盘
-* 3 台 BE：16 核 CPU、64GB 内存、1 块 2TB 通用性 SSD 云磁盘
+* 3 台 BE：阿里云 16 核 CPU、64GB 内存、1 块 1TB ESSD PL1 云磁盘
-* 1 台测试客户端：16 核 CPU、64GB 内存、1 块 100GB 通用型 SSD 云磁盘
+* 1 台测试客户端：阿里云 16 核 CPU、64GB 内存、1 块 100GB ESSD PL1 云磁盘
+
+* 测试版本为Doris-2.1.5
**数据集**
@@ -463,20 +558,20 @@ ALTER TABLE dt SET ("group_commit_data_bytes" =
"134217728");
**测试结果**
-| 导入方式 | 单并发数据量 | 并发数 | 耗时 (秒) | 导入速率 (行/秒) | 导入吞吐 (MB/秒) |
-|----------------|---------|------|-----------|----------|-----------|
-| `group_commit` | 10 KB | 10 | 3707 | 66,697 | 8.56 |
-| `group_commit` | 10 KB | 30 | 3385 | 73,042 | 9.38 |
-| `group_commit` | 100 KB | 10 | 473 | 522,725 | 67.11 |
-| `group_commit` | 100 KB | 30 | 390 | 633,972 | 81.39 |
-| `group_commit` | 500 KB | 10 | 323 | 765,477 | 98.28 |
-| `group_commit` | 500 KB | 30 | 309 | 800,158 | 102.56 |
-| `group_commit` | 1 MB | 10 | 304 | 813,319 | 104.24 |
-| `group_commit` | 1 MB | 30 | 286 | 864,507 | 110.88 |
-| `group_commit` | 10 MB | 10 | 290 | 852,583 | 109.28 |
-| `非group_commit` | 1 MB | 10 | 导入报错 -235 | | |
-| `非group_commit` | 10 MB | 10 | 519 | 476,395 | 61.12 |
-| `非group_commit` | 10 MB | 30 | 导入报错 -235 | | |
+| 导入方式 | 单并发数据量 | 并发数 | 耗时 (秒) | 导入速率 (行/秒) | 导入吞吐 (MB/秒) |
+|------------------|-------------|--------|-------------|--------------------|-------------------|
+| group_commit | 10 KB | 10 | 3306 | 74,787 | 9.8
|
+| group_commit | 10 KB | 30 | 3264 | 75,750 | 10.0
|
+| group_commit | 100 KB | 10 | 424 | 582,447 | 76.7
|
+| group_commit | 100 KB | 30 | 366 | 675,543 | 89.0
|
+| group_commit | 500 KB | 10 | 187 | 1,318,661 |
173.7 |
+| group_commit | 500 KB | 30 | 183 | 1,351,087 |
178.0 |
+| group_commit | 1 MB | 10 | 178 | 1,385,148 |
182.5 |
+| group_commit | 1 MB | 30 | 178 | 1,385,148 |
182.5 |
+| group_commit | 10 MB | 10 | 177 | 1,396,887 |
184.0 |
+| 非group_commit | 1 MB | 10 | 2824 | 87,536 | 11.5
|
+| 非group_commit | 10 MB | 10 | 450 | 549,442 | 68.9
|
+| 非group_commit | 10 MB | 30 | 177 | 1,396,887 | 184.0
|
在上面的`group_commit`测试中，BE 的 CPU 使用率在 10-40% 之间。
@@ -486,11 +581,15 @@ ALTER TABLE dt SET ("group_commit_data_bytes" =
"134217728");
**机器配置**
-* 1 台 FE：8 核 CPU、16 GB 内存、1 块 200 GB 通用性 SSD 云磁盘
+* 1 台 FE：阿里云 8 核 CPU、16GB 内存、1 块 100GB ESSD PL1 云磁盘
+
+* 1 台 BE：阿里云 16 核 CPU、64GB 内存、1 块 500GB ESSD PL1 云磁盘
+
+* 1 台测试客户端：阿里云 16 核 CPU、64GB 内存、1 块 100GB ESSD PL1 云磁盘
-* 1 台 BE：16 核 CPU、64 GB 内存、1 块 2 TB 通用性 SSD 云磁盘
+* 测试版本为Doris-2.1.5
-* 1 台测试客户端：16 核 CPU、64GB 内存、1 块 100 GB 通用型 SSD 云磁盘
+* 关闭打印parpared语句的audit log以提高性能
**数据集**
@@ -507,7 +606,170 @@ ALTER TABLE dt SET ("group_commit_data_bytes" =
"134217728");
**测试结果**
| 单个 insert 的行数 | 并发数 | 导入速率 (行/秒) | 导入吞吐 (MB/秒) |
-|-------------|-----|-----------|----------|
-| 100 | 20 | 106931 | 11.46 |
-
+|-------------------|--------|--------------------|--------------------|
+| 100 | 10 | 107,172 | 11.47 |
+| 100 | 20 | 140,317 | 14.79 |
+| 100 | 30 | 142,882 | 15.28 |
在上面的测试中，FE 的 CPU 使用率在 60-70% 左右，BE 的 CPU 使用率在 10-20% 左右。
+
+### Insert into sync 模式小批量数据
+
+**机器配置**
+
+* 1 台 FE：阿里云 16 核 CPU、64GB 内存、1 块 500GB ESSD PL1 云磁盘
+
+* 5 台 BE：阿里云 16 核 CPU、64GB 内存、1 块 1TB ESSD PL1 云磁盘。
+
+* 1 台测试客户端：阿里云 16 核 CPU、64GB 内存、1 块 100GB ESSD PL1 云磁盘
+
+* 测试版本为Doris-2.1.5
+
+**数据集**
+
+* tpch sf10 `lineitem` 表数据集。
+
+* 建表语句为
+```sql
+CREATE TABLE IF NOT EXISTS lineitem (
+ L_ORDERKEY INTEGER NOT NULL,
+ L_PARTKEY INTEGER NOT NULL,
+ L_SUPPKEY INTEGER NOT NULL,
+ L_LINENUMBER INTEGER NOT NULL,
+ L_QUANTITY DECIMAL(15,2) NOT NULL,
+ L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
+ L_DISCOUNT DECIMAL(15,2) NOT NULL,
+ L_TAX DECIMAL(15,2) NOT NULL,
+ L_RETURNFLAG CHAR(1) NOT NULL,
+ L_LINESTATUS CHAR(1) NOT NULL,
+ L_SHIPDATE DATE NOT NULL,
+ L_COMMITDATE DATE NOT NULL,
+ L_RECEIPTDATE DATE NOT NULL,
+ L_SHIPINSTRUCT CHAR(25) NOT NULL,
+ L_SHIPMODE CHAR(10) NOT NULL,
+ L_COMMENT VARCHAR(44) NOT NULL
+)
+DUPLICATE KEY(L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER)
+DISTRIBUTED BY HASH(L_ORDERKEY) BUCKETS 32
+PROPERTIES (
+ "replication_num" = "3"
+);
+```
+
+**测试工具**
+
+* [Jmeter](https://jmeter.apache.org/)
+
+需要设置的jmeter参数如下图所示
+
+
+
+
+1. 设置测试前的init语句，`set group_commit=async_mode`以及`set
enable_nereids_planner=false`。
+2. 开启jdbc的prepared
statement，完整的url为`jdbc:mysql://127.0.0.1:9030?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=50&sessionVariables=group_commit=async_mode&sessionVariables=enable_nereids_planner=false`。
+3. 设置导入类型为prepared update statement。
+4. 设置导入语句。
+5. 设置每次需要导入的值，注意，导入的值与导入值的类型要一一匹配。
+
+**测试方法**
+
+* 通过 `Jmeter` 向`Doris`写数据。每个并发每次通过insert into写入1行数据。
+
+**测试结果**
+
+* 数据单位为行每秒。
+
+* 以下测试分为30，100，500并发。
+
+**30并发sync模式5个BE3副本性能测试**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+| | 321.5 | 307.3 | 285.8 | 224.3 |
+
+
+**100并发sync模式性能测试**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+| | 1175.2 | 1108.7 | 1016.3 | 704.5 |
+
+**500并发sync模式性能测试**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+| | 3289.8 | 3686.7 | 3280.7 | 2609.2 |
+
+### Insert into sync 模式大批量数据
+
+**机器配置**
+
+* 1 台 FE：阿里云 16 核 CPU、64GB 内存、1 块 500GB ESSD PL1 云磁盘
+
+* 5 台 BE：阿里云 16 核 CPU、64GB 内存、1 块 1TB ESSD PL1 云磁盘。注：测试中分别用了1台，3台，5台BE进行测试。
+
+* 1 台测试客户端：阿里云 16 核 CPU、64GB 内存、1 块 100GB ESSD PL1 云磁盘
+
+* 测试版本为Doris-2.1.5
+
+**数据集**
+
+* tpch sf10 `lineitem` 表数据集。
+
+* 建表语句为
+```sql
+CREATE TABLE IF NOT EXISTS lineitem (
+ L_ORDERKEY INTEGER NOT NULL,
+ L_PARTKEY INTEGER NOT NULL,
+ L_SUPPKEY INTEGER NOT NULL,
+ L_LINENUMBER INTEGER NOT NULL,
+ L_QUANTITY DECIMAL(15,2) NOT NULL,
+ L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
+ L_DISCOUNT DECIMAL(15,2) NOT NULL,
+ L_TAX DECIMAL(15,2) NOT NULL,
+ L_RETURNFLAG CHAR(1) NOT NULL,
+ L_LINESTATUS CHAR(1) NOT NULL,
+ L_SHIPDATE DATE NOT NULL,
+ L_COMMITDATE DATE NOT NULL,
+ L_RECEIPTDATE DATE NOT NULL,
+ L_SHIPINSTRUCT CHAR(25) NOT NULL,
+ L_SHIPMODE CHAR(10) NOT NULL,
+ L_COMMENT VARCHAR(44) NOT NULL
+)
+DUPLICATE KEY(L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER)
+DISTRIBUTED BY HASH(L_ORDERKEY) BUCKETS 32
+PROPERTIES (
+ "replication_num" = "3"
+);
+```
+
+**测试工具**
+
+* [Jmeter](https://jmeter.apache.org/)
+
+**测试方法**
+
+* 通过 `Jmeter` 向`Doris`写数据。每个并发每次通过insert into写入1000行数据。
+
+**测试结果**
+
+* 数据单位为行每秒。
+
+* 以下测试分为30，100，500并发。
+
+**30并发sync模式性能测试**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+| | 92.2K | 85.9K | 84K | 83.2K |
+
+**100并发sync模式性能测试**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+| | 70.4K |70.5K | 73.2K | 69.4K |
+
+**500并发sync模式性能测试**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+| | 46.3K | 47.7K | 47.4K | 46.5K |
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/data-operate/import/import-way/group-commit-manual.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/data-operate/import/import-way/group-commit-manual.md
index 86946b56b2f..a18f845d4fd 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/data-operate/import/import-way/group-commit-manual.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/data-operate/import/import-way/group-commit-manual.md
@@ -43,6 +43,8 @@ Group Commit 写入有三种模式，分别是：
Doris 首先将数据写入 WAL (`Write Ahead Log`)，然后导入立即返回。Doris
会根据负载和表的`group_commit_interval`属性异步提交数据，提交之后数据可见。为了防止 WAL
占用较大的磁盘空间，单次导入数据量较大时，会自动切换为`sync_mode`。这适用于写入延迟敏感以及高频写入的场景。
+ WAL的数量可以通过FE
http接口查看，具体可见[这里](../../../admin-manual/fe/get-wal-size-action.md)，也可以在BE的metrics中搜索关键词`wal`查看。
+
## Group Commit 使用方式
假如表的结构为：
@@ -66,7 +68,7 @@ PROPERTIES (
**1. 设置 JDBC URL 并在 Server 端开启 Prepared Statement**
```
-url = jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true
+url =
jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=500
```
**2. 配置 `group_commit` session 变量，有如下两种方式：**
@@ -74,7 +76,7 @@ url = jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true
* 通过 JDBC url 设置，增加`sessionVariables=group_commit=async_mode`
```
- url =
jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true&sessionVariables=group_commit=async_mode
+ url =
jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=500&sessionVariables=group_commit=async_mode&sessionVariables=enable_nereids_planner=false
```
* 通过执行 SQL 设置
@@ -89,7 +91,7 @@ url = jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true
```java
private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
-private static final String URL_PATTERN =
"jdbc:mysql://%s:%d/%s?useServerPrepStmts=true";
+private static final String URL_PATTERN =
"jdbc:mysql://%s:%d/%s?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=500&sessionVariables=group_commit=async_mode&sessionVariables=enable_nereids_planner=false";
private static final String HOST = "127.0.0.1";
private static final int PORT = 9087;
private static final String DB = "db";
@@ -98,35 +100,12 @@ private static final String USER = "root";
private static final String PASSWD = "";
private static final int INSERT_BATCH_SIZE = 10;
-private static void groupCommitInsert() throws Exception {
- Class.forName(JDBC_DRIVER);
- try (Connection conn =
DriverManager.getConnection(String.format(URL_PATTERN, HOST, PORT, DB), USER,
PASSWD)) {
- // set session variable 'group_commit'
- try (Statement statement = conn.createStatement()) {
- statement.execute("SET group_commit = async_mode;");
- }
-
- String query = "insert into " + TBL + " values(?, ?, ?)";
- try (PreparedStatement stmt = conn.prepareStatement(query)) {
- for (int i = 0; i < INSERT_BATCH_SIZE; i++) {
- stmt.setInt(1, i);
- stmt.setString(2, "name" + i);
- stmt.setInt(3, i + 10);
- int result = stmt.executeUpdate();
- System.out.println("rows: " + result);
- }
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
-}
-
private static void groupCommitInsertBatch() throws Exception {
Class.forName(JDBC_DRIVER);
// add rewriteBatchedStatements=true and cachePrepStmts=true in JDBC url
// set session variables by sessionVariables=group_commit=async_mode in
JDBC url
try (Connection conn = DriverManager.getConnection(
- String.format(URL_PATTERN +
"&rewriteBatchedStatements=true&cachePrepStmts=true&sessionVariables=group_commit=async_mode",
HOST, PORT, DB), USER, PASSWD)) {
+ String.format(URL_PATTERN, HOST, PORT, DB), USER, PASSWD)) {
String query = "insert into " + TBL + " values(?, ?, ?)";
try (PreparedStatement stmt = conn.prepareStatement(query)) {
@@ -147,8 +126,122 @@ private static void groupCommitInsertBatch() throws
Exception {
}
```
+注意：由于高频的insert into语句会打印大量的audit log，对最终性能有一定影响，默认关闭了打印prepared语句的audit
log。可以通过设置session variable的方式控制是否打印prepared语句的audit log。
+
+```sql
+# 配置 session 变量开启打印parpared语句的audit log, 默认为false即关闭打印parpared语句的audit log。
+set enable_prepared_stmt_audit_log=true;
+```
+
关于 **JDBC** 的更多用法，参考[使用 Insert 方式同步数据](./insert-into-manual.md)。
+### 使用Golang进行Group Commit
+
+Golang的prepared语句支持有限，所以我们可以通过手动客户端攒批的方式提高Group Commit的性能，以下为一个示例程序。
+
+```Golang
+package main
+
+import (
+ "database/sql"
+ "fmt"
+ "math/rand"
+ "strings"
+ "sync"
+ "sync/atomic"
+ "time"
+
+ _ "github.com/go-sql-driver/mysql"
+)
+
+const (
+ host = "127.0.0.1"
+ port = 9038
+ db = "test"
+ user = "root"
+ password = ""
+ table = "async_lineitem"
+)
+
+var (
+ threadCount = 20
+ batchSize = 100
+)
+
+var totalInsertedRows int64
+var rowsInsertedLastSecond int64
+
+func main() {
+ dbDSN := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?parseTime=true", user,
password, host, port, db)
+ db, err := sql.Open("mysql", dbDSN)
+ if err != nil {
+ fmt.Printf("Error opening database: %s\n", err)
+ return
+ }
+ defer db.Close()
+
+ var wg sync.WaitGroup
+ for i := 0; i < threadCount; i++ {
+ wg.Add(1)
+ go func() {
+ defer wg.Done()
+ groupCommitInsertBatch(db)
+ }()
+ }
+
+ go logInsertStatistics()
+
+ wg.Wait()
+}
+
+func groupCommitInsertBatch(db *sql.DB) {
+ for {
+ valueStrings := make([]string, 0, batchSize)
+ valueArgs := make([]interface{}, 0, batchSize*16)
+ for i := 0; i < batchSize; i++ {
+ for i = 0; i < batchSize; i++ {
+ valueStrings = append(valueStrings, "(?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs,
sql.NullFloat64{Float64: 1.0, Valid: true})
+ valueArgs = append(valueArgs,
sql.NullFloat64{Float64: 1.0, Valid: true})
+ valueArgs = append(valueArgs,
sql.NullFloat64{Float64: 1.0, Valid: true})
+ valueArgs = append(valueArgs,
sql.NullFloat64{Float64: 1.0, Valid: true})
+ valueArgs = append(valueArgs, "N")
+ valueArgs = append(valueArgs, "O")
+ valueArgs = append(valueArgs, time.Now())
+ valueArgs = append(valueArgs, time.Now())
+ valueArgs = append(valueArgs, time.Now())
+ valueArgs = append(valueArgs, "DELIVER IN
PERSON")
+ valueArgs = append(valueArgs, "SHIP")
+ valueArgs = append(valueArgs, "N/A")
+ }
+ }
+ stmt := fmt.Sprintf("INSERT INTO %s VALUES %s",
+ table, strings.Join(valueStrings, ","))
+ _, err := db.Exec(stmt, valueArgs...)
+ if err != nil {
+ fmt.Printf("Error executing batch: %s\n", err)
+ return
+ }
+ atomic.AddInt64(&rowsInsertedLastSecond, int64(batchSize))
+ atomic.AddInt64(&totalInsertedRows, int64(batchSize))
+ }
+}
+
+func logInsertStatistics() {
+ for {
+ time.Sleep(1 * time.Second)
+ fmt.Printf("Total inserted rows: %d\n", totalInsertedRows)
+ fmt.Printf("Rows inserted in the last second: %d\n",
rowsInsertedLastSecond)
+ rowsInsertedLastSecond = 0
+ }
+}
+
+```
+
### INSERT INTO VALUES
* 异步模式
@@ -443,11 +536,13 @@ ALTER TABLE dt SET ("group_commit_data_bytes" =
"134217728");
**机器配置**
-* 1 台 FE：8 核 CPU、16GB 内存、1 块 200GB 通用性 SSD 云磁盘
+* 1 台 FE：阿里云 8 核 CPU、16GB 内存、1 块 100GB ESSD PL1 云磁盘
-* 3 台 BE：16 核 CPU、64GB 内存、1 块 2TB 通用性 SSD 云磁盘
+* 3 台 BE：阿里云 16 核 CPU、64GB 内存、1 块 1TB ESSD PL1 云磁盘
-* 1 台测试客户端：16 核 CPU、64GB 内存、1 块 100GB 通用型 SSD 云磁盘
+* 1 台测试客户端：阿里云 16 核 CPU、64GB 内存、1 块 100GB ESSD PL1 云磁盘
+
+* 测试版本为Doris-3.0.1
**数据集**
@@ -465,18 +560,18 @@ ALTER TABLE dt SET ("group_commit_data_bytes" =
"134217728");
| 导入方式 | 单并发数据量 | 并发数 | 耗时 (秒) | 导入速率 (行/秒) | 导入吞吐 (MB/秒) |
|----------------|---------|------|-----------|----------|-----------|
-| `group_commit` | 10 KB | 10 | 3707 | 66,697 | 8.56 |
-| `group_commit` | 10 KB | 30 | 3385 | 73,042 | 9.38 |
-| `group_commit` | 100 KB | 10 | 473 | 522,725 | 67.11 |
-| `group_commit` | 100 KB | 30 | 390 | 633,972 | 81.39 |
-| `group_commit` | 500 KB | 10 | 323 | 765,477 | 98.28 |
-| `group_commit` | 500 KB | 30 | 309 | 800,158 | 102.56 |
-| `group_commit` | 1 MB | 10 | 304 | 813,319 | 104.24 |
-| `group_commit` | 1 MB | 30 | 286 | 864,507 | 110.88 |
-| `group_commit` | 10 MB | 10 | 290 | 852,583 | 109.28 |
-| `非group_commit` | 1 MB | 10 | 导入报错 -235 | | |
-| `非group_commit` | 10 MB | 10 | 519 | 476,395 | 61.12 |
-| `非group_commit` | 10 MB | 30 | 导入报错 -235 | | |
+| `group_commit` | 10 KB | 10 | 2204 | 112,181 | 14.8 |
+| `group_commit` | 10 KB | 30 | 2176 | 113,625 | 15.0 |
+| `group_commit` | 100 KB | 10 | 283 | 873,671 | 115.1 |
+| `group_commit` | 100 KB | 30 | 244 | 1,013,315 | 133.5 |
+| `group_commit` | 500 KB | 10 | 125 | 1,977,992 | 260.6 |
+| `group_commit` | 500 KB | 30 | 122 | 2,026,631 | 267.1 |
+| `group_commit` | 1 MB | 10 | 119 | 2,077,723 | 273.8 |
+| `group_commit` | 1 MB | 30 | 119 | 2,077,723 | 273.8 |
+| `group_commit` | 10 MB | 10 | 118 | 2,095,331 | 276.1 |
+| `非group_commit` | 1 MB | 10 | 1883 | 131,305 | 17.3|
+| `非group_commit` | 10 MB | 10 | 294 | 840,983 | 105.4 |
+| `非group_commit` | 10 MB | 30 | 118 | 2,095,331 | 276.1|
在上面的`group_commit`测试中，BE 的 CPU 使用率在 10-40% 之间。
@@ -486,11 +581,15 @@ ALTER TABLE dt SET ("group_commit_data_bytes" =
"134217728");
**机器配置**
-* 1 台 FE：8 核 CPU、16 GB 内存、1 块 200 GB 通用性 SSD 云磁盘
+* 1 台 FE：阿里云 8 核 CPU、16GB 内存、1 块 100GB ESSD PL1 云磁盘
+
+* 1 台 BE：阿里云 16 核 CPU、64GB 内存、1 块 500GB ESSD PL1 云磁盘
+
+* 1 台测试客户端：阿里云 16 核 CPU、64GB 内存、1 块 100GB ESSD PL1 云磁盘
-* 1 台 BE：16 核 CPU、64 GB 内存、1 块 2 TB 通用性 SSD 云磁盘
+* 测试版本为Doris-3.0.1
-* 1 台测试客户端：16 核 CPU、64GB 内存、1 块 100 GB 通用型 SSD 云磁盘
+* 关闭打印parpared语句的audit log以提高性能
**数据集**
@@ -508,6 +607,176 @@ ALTER TABLE dt SET ("group_commit_data_bytes" =
"134217728");
| 单个 insert 的行数 | 并发数 | 导入速率 (行/秒) | 导入吞吐 (MB/秒) |
|-------------|-----|-----------|----------|
-| 100 | 20 | 106931 | 11.46 |
+| 100 | 10 | 160,758 | 17.21 |
+| 100 | 20 | 210,476 | 22.19 |
+| 100 | 30 | 214,323 | 22.92 |
在上面的测试中，FE 的 CPU 使用率在 60-70% 左右，BE 的 CPU 使用率在 10-20% 左右。
+
+### Insert into sync 模式小批量数据
+
+**机器配置**
+
+* 1 台 FE：阿里云 16 核 CPU、64GB 内存、1 块 500GB ESSD PL1 云磁盘
+
+* 5 台 BE：阿里云 16 核 CPU、64GB 内存、1 块 1TB ESSD PL1 云磁盘。
+
+* 1 台测试客户端：阿里云 16 核 CPU、64GB 内存、1 块 100GB ESSD PL1 云磁盘
+
+* 测试版本为Doris-3.0.1
+
+**数据集**
+
+* tpch sf10 `lineitem` 表数据集。
+
+* 建表语句为
+```sql
+CREATE TABLE IF NOT EXISTS lineitem (
+ L_ORDERKEY INTEGER NOT NULL,
+ L_PARTKEY INTEGER NOT NULL,
+ L_SUPPKEY INTEGER NOT NULL,
+ L_LINENUMBER INTEGER NOT NULL,
+ L_QUANTITY DECIMAL(15,2) NOT NULL,
+ L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
+ L_DISCOUNT DECIMAL(15,2) NOT NULL,
+ L_TAX DECIMAL(15,2) NOT NULL,
+ L_RETURNFLAG CHAR(1) NOT NULL,
+ L_LINESTATUS CHAR(1) NOT NULL,
+ L_SHIPDATE DATE NOT NULL,
+ L_COMMITDATE DATE NOT NULL,
+ L_RECEIPTDATE DATE NOT NULL,
+ L_SHIPINSTRUCT CHAR(25) NOT NULL,
+ L_SHIPMODE CHAR(10) NOT NULL,
+ L_COMMENT VARCHAR(44) NOT NULL
+)
+DUPLICATE KEY(L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER)
+DISTRIBUTED BY HASH(L_ORDERKEY) BUCKETS 32
+PROPERTIES (
+ "replication_num" = "3"
+);
+```
+
+**测试工具**
+
+* [Jmeter](https://jmeter.apache.org/)
+
+需要设置的jmeter参数如下图所示
+
+
+
+
+1. 设置测试前的init语句，`set group_commit=async_mode`以及`set
enable_nereids_planner=false`。
+2. 开启jdbc的prepared
statement，完整的url为`jdbc:mysql://127.0.0.1:9030?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=50&sessionVariables=group_commit=async_mode&sessionVariables=enable_nereids_planner=false`。
+3. 设置导入类型为prepared update statement。
+4. 设置导入语句。
+5. 设置每次需要导入的值，注意，导入的值与导入值的类型要一一匹配。
+
+**测试方法**
+
+* 通过 `Jmeter` 向`Doris`写数据。每个并发每次通过insert into写入1行数据。
+
+**测试结果**
+
+* 数据单位为行每秒。
+
+* 以下测试分为30，100，500并发。
+
+**30并发sync模式5个BE3副本性能测试**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+|enable_nereids_planner=true| 891.8 | 701.1 | 400.0 | 237.5 |
+|enable_nereids_planner=false| 885.8 | 688.1 | 398.7 | 232.9
|
+
+
+**100并发sync模式5个BE3副本性能测试**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+|enable_nereids_planner=true| 2427.8 | 2068.9 | 1259.4 | 764.9 |
+|enable_nereids_planner=false| 2320.4 | 1899.3 | 1206.2 |749.7|
+
+**500并发sync模式5个BE3副本性能测试**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+|enable_nereids_planner=true| 5567.5 | 5713.2 | 4681.0 | 3131.2 |
+|enable_nereids_planner=false| 4471.6 | 5042.5 | 4932.2 | 3641.1 |
+
+### Insert into sync 模式大批量数据
+
+**机器配置**
+
+* 1 台 FE：阿里云 16 核 CPU、64GB 内存、1 块 500GB ESSD PL1 云磁盘
+
+* 5 台 BE：阿里云 16 核 CPU、64GB 内存、1 块 1TB ESSD PL1 云磁盘。注：测试中分别用了1台，3台，5台BE进行测试。
+
+* 1 台测试客户端：阿里云 16 核 CPU、64GB 内存、1 块 100GB ESSD PL1 云磁盘
+
+* 测试版本为Doris-3.0.1
+
+**数据集**
+
+* tpch sf10 `lineitem` 表数据集。
+
+* 建表语句为
+```sql
+CREATE TABLE IF NOT EXISTS lineitem (
+ L_ORDERKEY INTEGER NOT NULL,
+ L_PARTKEY INTEGER NOT NULL,
+ L_SUPPKEY INTEGER NOT NULL,
+ L_LINENUMBER INTEGER NOT NULL,
+ L_QUANTITY DECIMAL(15,2) NOT NULL,
+ L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
+ L_DISCOUNT DECIMAL(15,2) NOT NULL,
+ L_TAX DECIMAL(15,2) NOT NULL,
+ L_RETURNFLAG CHAR(1) NOT NULL,
+ L_LINESTATUS CHAR(1) NOT NULL,
+ L_SHIPDATE DATE NOT NULL,
+ L_COMMITDATE DATE NOT NULL,
+ L_RECEIPTDATE DATE NOT NULL,
+ L_SHIPINSTRUCT CHAR(25) NOT NULL,
+ L_SHIPMODE CHAR(10) NOT NULL,
+ L_COMMENT VARCHAR(44) NOT NULL
+)
+DUPLICATE KEY(L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER)
+DISTRIBUTED BY HASH(L_ORDERKEY) BUCKETS 32
+PROPERTIES (
+ "replication_num" = "3"
+);
+```
+
+**测试工具**
+
+* [Jmeter](https://jmeter.apache.org/)
+
+**测试方法**
+
+* 通过 `Jmeter` 向`Doris`写数据。每个并发每次通过insert into写入1000行数据。
+
+**测试结果**
+
+* 数据单位为行每秒。
+
+* 以下测试分为30，100，500并发。
+
+**30并发sync模式5个BE3副本性能测试**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+|enable_nereids_planner=true| 9.1K | 11.1K | 11.4K | 11.1K |
+|enable_nereids_planner=false| 157.8K | 159.9K | 154.1K | 120.4K
|
+
+**100并发sync模式5个BE3副本性能测试**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+|enable_nereids_planner=true| 10.0K |9.2K | 8.9K | 8.9K |
+|enable_nereids_planner=false| 130.4k | 131.0K | 130.4K | 124.1K
|
+
+**500并发sync模式5个BE3副本性能测试**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+|enable_nereids_planner=true| 2.5K | 2.5K | 2.3K | 2.1K |
+|enable_nereids_planner=false| 94.2K | 95.1K | 94.4K | 94.8K |
diff --git a/static/images/group-commit/jmeter1.jpg
b/static/images/group-commit/jmeter1.jpg
new file mode 100644
index 00000000000..a76f8986dd8
Binary files /dev/null and b/static/images/group-commit/jmeter1.jpg differ
diff --git a/static/images/group-commit/jmeter2.jpg
b/static/images/group-commit/jmeter2.jpg
new file mode 100644
index 00000000000..ad797ca317a
Binary files /dev/null and b/static/images/group-commit/jmeter2.jpg differ
diff --git
a/versioned_docs/version-2.1/data-operate/import/import-way/group-commit-manual.md
b/versioned_docs/version-2.1/data-operate/import/import-way/group-commit-manual.md
index af2515e23d2..3ed0b440063 100644
---
a/versioned_docs/version-2.1/data-operate/import/import-way/group-commit-manual.md
+++
b/versioned_docs/version-2.1/data-operate/import/import-way/group-commit-manual.md
@@ -44,6 +44,8 @@ Doris groups multiple loads into one transaction commit based
on the `group_comm
Doris writes data to the Write Ahead Log (WAL) firstly, then the load is
returned. Doris groups multiple loads into one transaction commit based on the
`group_commit_interval` table property, and the data is visible after the
commit. To prevent excessive disk space usage by the WAL, it automatically
switches to `sync_mode`. This is suitable for latency-sensitive and
high-frequency writing.
+The number of WALs can be viewed through the FE HTTP interface, as detailed
[here](../../admin-manual/fe/get-wal-size-action.md). Alternatively, you can
search for the keyword `wal` in the BE metrics.
+
## Basic operations
If the table schema is:
@@ -67,7 +69,7 @@ To reduce the CPU cost of SQL parsing and query planning, we
provide the `Prepar
1. Setup JDBC url and enable server side prepared statement
```
-url = jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true
+ url =
jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=500&sessionVariables=enable_nereids_planner=false
```
2. Set `group_commit` session variable, there are two ways to do it:
@@ -75,7 +77,7 @@ url = jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true
* Add `sessionVariables=group_commit=async_mode` in JDBC url
```
-url =
jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true&sessionVariables=group_commit=async_mode
+ url =
jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=500&sessionVariables=enable_nereids_planner=false&sessionVariables=group_commit=async_mode
```
* Use `SET group_commit = async_mode;` command
@@ -90,44 +92,21 @@ try (Statement statement = conn.createStatement()) {
```java
private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
-private static final String URL_PATTERN =
"jdbc:mysql://%s:%d/%s?useServerPrepStmts=true";
+private static final String URL_PATTERN =
"jdbc:mysql://%s:%d/%s?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=500&sessionVariables=enable_nereids_planner=false&sessionVariables=group_commit=async_mode";
private static final String HOST = "127.0.0.1";
private static final int PORT = 9087;
private static final String DB = "db";
private static final String TBL = "dt";
private static final String USER = "root";
private static final String PASSWD = "";
-private static final int INSERT_BATCH_SIZE = 10;
-
-private static void groupCommitInsert() throws Exception {
- Class.forName(JDBC_DRIVER);
- try (Connection conn =
DriverManager.getConnection(String.format(URL_PATTERN, HOST, PORT, DB), USER,
PASSWD)) {
- // set session variable 'group_commit'
- try (Statement statement = conn.createStatement()) {
- statement.execute("SET group_commit = async_mode;");
- }
-
- String query = "insert into " + TBL + " values(?, ?, ?)";
- try (PreparedStatement stmt = conn.prepareStatement(query)) {
- for (int i = 0; i < INSERT_BATCH_SIZE; i++) {
- stmt.setInt(1, i);
- stmt.setString(2, "name" + i);
- stmt.setInt(3, i + 10);
- int result = stmt.executeUpdate();
- System.out.println("rows: " + result);
- }
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
-}
+private static final int INSERT_BATCH_SIZE = 10;
private static void groupCommitInsertBatch() throws Exception {
Class.forName(JDBC_DRIVER);
// add rewriteBatchedStatements=true and cachePrepStmts=true in JDBC url
// set session variables by sessionVariables=group_commit=async_mode in
JDBC url
try (Connection conn = DriverManager.getConnection(
- String.format(URL_PATTERN +
"&rewriteBatchedStatements=true&cachePrepStmts=true&sessionVariables=group_commit=async_mode",
HOST, PORT, DB), USER, PASSWD)) {
+ String.format(URL_PATTERN, HOST, PORT, DB), USER, PASSWD)) {
String query = "insert into " + TBL + " values(?, ?, ?)";
try (PreparedStatement stmt = conn.prepareStatement(query)) {
@@ -148,7 +127,122 @@ private static void groupCommitInsertBatch() throws
Exception {
}
```
-See [Synchronize Data Using Insert Method](./insert-into-manual) for more
details about **JDBC**.
+**Note:** Due to the high frequency of `INSERT INTO` statements, a large
amount of audit logs might be printed, which could impact overall performance.
By default, the audit log for prepared statements is disabled. You can control
whether to print the audit log for prepared statements by setting a session
variable.
+
+```sql
+# Configure the session variable to enable printing the audit log for prepared
statements. By default, it is set to false, which disables printing the audit
log for prepared statements.
+set enable_prepared_stmt_audit_log=true;
+```
+
+For more usage on **JDBC**, refer to [Using Insert to Synchronize
Data](./insert-into-manual).
+
+### Using Golang for Group Commit
+
+Golang has limited support for prepared statements, so we can manually batch
the statements on the client side to improve the performance of Group Commit.
Below is an example program.
+
+```Golang
+package main
+
+import (
+ "database/sql"
+ "fmt"
+ "math/rand"
+ "strings"
+ "sync"
+ "sync/atomic"
+ "time"
+
+ _ "github.com/go-sql-driver/mysql"
+)
+
+const (
+ host = "127.0.0.1"
+ port = 9038
+ db = "test"
+ user = "root"
+ password = ""
+ table = "async_lineitem"
+)
+
+var (
+ threadCount = 20
+ batchSize = 100
+)
+
+var totalInsertedRows int64
+var rowsInsertedLastSecond int64
+
+func main() {
+ dbDSN := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?parseTime=true", user, password,
host, port, db)
+ db, err := sql.Open("mysql", dbDSN)
+ if err != nil {
+ fmt.Printf("Error opening database: %s\n", err)
+ return
+ }
+ defer db.Close()
+
+ var wg sync.WaitGroup
+ for i := 0; i < threadCount; i++ {
+ wg.Add(1)
+ go func() {
+ defer wg.Done()
+ groupCommitInsertBatch(db)
+ }()
+ }
+
+ go logInsertStatistics()
+
+ wg.Wait()
+}
+
+func groupCommitInsertBatch(db *sql.DB) {
+ for {
+ valueStrings := make([]string, 0, batchSize)
+ valueArgs := make([]interface{}, 0, batchSize*16)
+ for i := 0; i < batchSize; i++ {
+ for i = 0; i < batchSize; i++ {
+ valueStrings = append(valueStrings, "(?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?)")
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, sql.NullFloat64{Float64: 1.0,
Valid: true})
+ valueArgs = append(valueArgs, sql.NullFloat64{Float64: 1.0,
Valid: true})
+ valueArgs = append(valueArgs, sql.NullFloat64{Float64: 1.0,
Valid: true})
+ valueArgs = append(valueArgs, sql.NullFloat64{Float64: 1.0,
Valid: true})
+ valueArgs = append(valueArgs, "N")
+ valueArgs = append(valueArgs, "O")
+ valueArgs = append(valueArgs, time.Now())
+ valueArgs = append(valueArgs, time.Now())
+ valueArgs = append(valueArgs, time.Now())
+ valueArgs = append(valueArgs, "DELIVER IN PERSON")
+ valueArgs = append(valueArgs, "SHIP")
+ valueArgs = append(valueArgs, "N/A")
+ }
+ }
+ stmt := fmt.Sprintf("INSERT INTO %s VALUES %s",
+ table, strings.Join(valueStrings, ","))
+ _, err := db.Exec(stmt, valueArgs...)
+ if err != nil {
+ fmt.Printf("Error executing batch: %s\n", err)
+ return
+ }
+ atomic.AddInt64(&rowsInsertedLastSecond, int64(batchSize))
+ atomic.AddInt64(&totalInsertedRows, int64(batchSize))
+ }
+}
+
+func logInsertStatistics() {
+ for {
+ time.Sleep(1 * time.Second)
+ fmt.Printf("Total inserted rows: %d\n", totalInsertedRows)
+ fmt.Printf("Rows inserted in the last second: %d\n",
rowsInsertedLastSecond)
+ rowsInsertedLastSecond = 0
+ }
+}
+
+```
+
### INSERT INTO VALUES
@@ -434,9 +528,13 @@ We have separately tested the write performance of group
commit in high-concurre
#### Environment
-* 1 FE: 8-core CPU, 16 GB RAM, 1 200 GB SSD disk
-* 3 BE: 16-core CPU, 64 GB RAM, 1 2 TB SSD disk
-* 1 Client: 8-core CPU, 64 GB RAM, 1 100 GB SSD disk
+* 1 Front End (FE) server: Alibaba Cloud with 8-core CPU, 16GB RAM, and one
100GB ESSD PL1 SSD.
+
+* 3 Backend (BE) servers: Alibaba Cloud with 16-core CPU, 64GB RAM, and one
1TB ESSD PL1 SSD.
+
+* 1 Test Client: Alibaba Cloud with 16-core CPU, 64GB RAM, and one 100GB ESSD
PL1 SSD.
+
+* The version for testing is Doris-3.0.1.
#### DataSet
@@ -448,24 +546,24 @@ We have separately tested the write performance of group
commit in high-concurre
#### Test Method
-* Setting different single-concurrency data size and concurrency num between
`non group_commit` and `group_commit` modes.
+* Setting different single-concurrency data size and concurrency num between
`non group_commit` and `group_commit=async mode` modes.
#### Test Result
| Load Way | Single-concurrency Data Size | Concurrency | Cost
Seconds | Rows / Seconds | MB / Seconds |
|--------------------|------------------------------|-------------|--------------------|----------------|--------------|
-| `group_commit` | 10 KB | 10 | 3707
| 66,697 | 8.56 |
-| `group_commit` | 10 KB | 30 | 3385
| 73,042 | 9.38 |
-| `group_commit` | 100 KB | 10 | 473
| 522,725 | 67.11 |
-| `group_commit` | 100 KB | 30 | 390
| 633,972 | 81.39 |
-| `group_commit` | 500 KB | 10 | 323
| 765,477 | 98.28 |
-| `group_commit` | 500 KB | 30 | 309
| 800,158 | 102.56 |
-| `group_commit` | 1 MB | 10 | 304
| 813,319 | 104.24 |
-| `group_commit` | 1 MB | 30 | 286
| 864,507 | 110.88 |
-| `group_commit` | 10 MB | 10 | 290
| 852,583 | 109.28 |
-| `non group_commit` | 1 MB | 10 | `-235
error` | | |
-| `non group_commit` | 10 MB | 10 | 519
| 476,395 | 61.12 |
-| `non group_commit` | 10 MB | 30 | `-235
error` | | |
+| `group_commit` | 10 KB | 10 | 2204 | 112,181 | 14.8 |
+| `group_commit` | 10 KB | 30 | 2176 | 113,625 | 15.0 |
+| `group_commit` | 100 KB | 10 | 283 | 873,671 | 115.1 |
+| `group_commit` | 100 KB | 30 | 244 | 1,013,315 | 133.5 |
+| `group_commit` | 500 KB | 10 | 125 | 1,977,992 | 260.6 |
+| `group_commit` | 500 KB | 30 | 122 | 2,026,631 | 267.1 |
+| `group_commit` | 1 MB | 10 | 119 | 2,077,723 | 273.8 |
+| `group_commit` | 1 MB | 30 | 119 | 2,077,723 | 273.8 |
+| `group_commit` | 10 MB | 10 | 118 | 2,095,331 | 276.1 |
+| `non group_commit` | 1 MB | 10 | 1883 | 131,305 | 17.3|
+| `non group_commit` | 10 MB | 10 | 294 | 840,983 | 105.4 |
+| `non group_commit` | 10 MB | 30 | 118 | 2,095,331 | 276.1|
In the above test, the CPU usage of BE fluctuates between 10-40%.
@@ -475,9 +573,15 @@ The `group_commit` effectively enhances import performance
while reducing the nu
#### Environment
-* 1 FE: 8-core CPU, 16 GB RAM, 1 200 GB SSD disk
-* 1 BE: 16-core CPU, 64 GB RAM, 1 2 TB SSD disk
-* 1 Client: 16-core CPU, 64 GB RAM, 1 100 GB SSD disk
+1 Front End (FE) server: Alibaba Cloud with an 8-core CPU, 16GB RAM, and one
100GB ESSD PL1 SSD.
+
+1 Backend (BE) server: Alibaba Cloud with a 16-core CPU, 64GB RAM, and one
500GB ESSD PL1 SSD.
+
+1 Test Client: Alibaba Cloud with a 16-core CPU, 64GB RAM, and one 100GB ESSD
PL1 SSD.
+
+The testing version is Doris-3.0.1.
+
+Disable the printing of prepared statement audit logs to enhance performance.
#### DataSet
@@ -496,6 +600,154 @@ The `group_commit` effectively enhances import
performance while reducing the nu
| Rows per insert | Concurrency | Rows / Second | MB / Second |
|-----------------|-------------|---------------|-------------|
-| 100 | 20 | 106931 | 11.46 |
+| 100 | 10 | 160,758 | 17.21 |
+| 100 | 20 | 210,476 | 22.19 |
+| 100 | 30 | 214,323 | 22.92 |
In the above test, the CPU usage of BE fluctuates between 10-20%, FE
fluctuates between 60-70%.
+
+
+### Insert into Sync Mode Small Batch Data
+
+**Machine Configuration**
+
+* 1 Front-End (FE): Alibaba Cloud, 16-core CPU, 64GB RAM, 1 x 500GB ESSD PL1
cloud disk
+* 5 Back-End (BE) nodes: Alibaba Cloud, 16-core CPU, 64GB RAM, 1 x 1TB ESSD
PL1 cloud disk.
+* 1 Testing Client: Alibaba Cloud, 16-core CPU, 64GB RAM, 1 x 100GB ESSD PL1
cloud disk
+* Test version: Doris-3.0.1
+
+**Dataset**
+
+* The data of tpch sf10 `lineitem` table.
+
+* The create table statement is
+```sql
+CREATE TABLE IF NOT EXISTS lineitem (
+ L_ORDERKEY INTEGER NOT NULL,
+ L_PARTKEY INTEGER NOT NULL,
+ L_SUPPKEY INTEGER NOT NULL,
+ L_LINENUMBER INTEGER NOT NULL,
+ L_QUANTITY DECIMAL(15,2) NOT NULL,
+ L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
+ L_DISCOUNT DECIMAL(15,2) NOT NULL,
+ L_TAX DECIMAL(15,2) NOT NULL,
+ L_RETURNFLAG CHAR(1) NOT NULL,
+ L_LINESTATUS CHAR(1) NOT NULL,
+ L_SHIPDATE DATE NOT NULL,
+ L_COMMITDATE DATE NOT NULL,
+ L_RECEIPTDATE DATE NOT NULL,
+ L_SHIPINSTRUCT CHAR(25) NOT NULL,
+ L_SHIPMODE CHAR(10) NOT NULL,
+ L_COMMENT VARCHAR(44) NOT NULL
+)
+DUPLICATE KEY(L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER)
+DISTRIBUTED BY HASH(L_ORDERKEY) BUCKETS 32
+PROPERTIES (
+ "replication_num" = "3"
+);
+```
+
+**Testing Tool**
+
+* [Jmeter](https://jmeter.apache.org/)
+
+JMeter Parameter Settings as Shown in the Images
+
+
+
+
+1. Set the Init Statement Before Testing:
+set group_commit=async_mode and set enable_nereids_planner=false.
+
+2. Enable JDBC Prepared Statement:
+Complete URL:
+jdbc:mysql://127.0.0.1:9030?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=50&sessionVariables=group_commit=async_mode&sessionVariables=enable_nereids_planner=false.
+
+3. Set the Import Type to Prepared Update Statement.
+
+4. Set the Import Statement.
+
+5. Set the Values to Be Imported:
+Ensure that the imported values match the data types one by one.
+
+**Testing Methodology**
+
+* Use JMeter to write data into Doris. Each thread writes 1 row of data per
execution using the insert into statement.
+
+**Test Results**
+
+* Data unit: rows per second.
+
+* The following tests are divided into 30, 100, and 500 concurrency.
+
+**Performance Test with 30 Concurrent Users in Sync Mode, 5 BEs, and 3
Replicas**
+
+| Group Commit Interval | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+|enable_nereids_planner=true | 891.8 | 701.1 | 400.0 | 237.5 |
+|enable_nereids_planner=false | 885.8 | 688.1 | 398.7 | 232.9
|
+
+**Performance Test with 100 Concurrent Users in Sync Mode, 5 BEs, and 3
Replicas**
+
+| Group Commit Interval | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+|enable_nereids_planner=true | 2427.8 | 2068.9 | 1259.4 | 764.9 |
+|enable_nereids_planner=false | 2320.4 | 1899.3 | 1206.2 | 749.7 |
+
+**Performance Test with 500 Concurrent Users in Sync Mode, 5 BEs, and 3
Replicas**
+
+| Group Commit Interval | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+|enable_nereids_planner=true | 5567.5 | 5713.2 | 4681.0 | 3131.2
|
+|enable_nereids_planner=false | 4471.6 | 5042.5 | 4932.2 | 3641.1 |
+
+### Insert into Sync Mode Large Batch Data
+
+**Machine Configuration**
+
+* 1 Front-End (FE): Alibaba Cloud, 16-core CPU, 64GB RAM, 1 x 500GB ESSD PL1
cloud disk
+
+* 5 Back-End (BE) nodes: Alibaba Cloud, 16-core CPU, 64GB RAM, 1 x 1TB ESSD
PL1 cloud disk.
+
+* 1 Testing Client: Alibaba Cloud, 16-core CPU, 64GB RAM, 1 x 100GB ESSD PL1
cloud disk
+
+* Test version: Doris-3.0.1
+
+**Dataset**
+
+* Insert into statement for 1000 rows: `insert into tbl values(1,1)...` (1000
rows omitted)
+
+**Testing Tool**
+
+* [Jmeter](https://jmeter.apache.org/)
+
+**Testing Methodology**
+
+* Use JMeter to write data into Doris. Each thread writes 1000 row of data per
execution using the insert into statement.
+
+**Test Results**
+
+* Data unit: rows per second.
+
+* The following tests are divided into 30, 100, and 500 concurrency.
+
+**Performance Test with 30 Concurrent Users in Sync Mode, 5 BEs, and 3
Replicas**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+|enable_nereids_planner=true| 9.1K | 11.1K | 11.4K | 11.1K |
+|enable_nereids_planner=false| 157.8K | 159.9K | 154.1K | 120.4K
|
+
+**Performance Test with 100 Concurrent Users in Sync Mode, 5 BEs, and 3
Replicas**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+|enable_nereids_planner=true| 10.0K |9.2K | 8.9K | 8.9K |
+|enable_nereids_planner=false| 130.4k | 131.0K | 130.4K | 124.1K
|
+
+**Performance Test with 500 Concurrent Users in Sync Mode, 5 BEs, and 3
Replicas**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+|enable_nereids_planner=true| 2.5K | 2.5K | 2.3K | 2.1K |
+|enable_nereids_planner=false| 94.2K | 95.1K | 94.4K | 94.8K |
\ No newline at end of file
diff --git
a/versioned_docs/version-3.0/data-operate/import/import-way/group-commit-manual.md
b/versioned_docs/version-3.0/data-operate/import/import-way/group-commit-manual.md
index af2515e23d2..c0c0fde0a62 100644
---
a/versioned_docs/version-3.0/data-operate/import/import-way/group-commit-manual.md
+++
b/versioned_docs/version-3.0/data-operate/import/import-way/group-commit-manual.md
@@ -44,6 +44,8 @@ Doris groups multiple loads into one transaction commit based
on the `group_comm
Doris writes data to the Write Ahead Log (WAL) firstly, then the load is
returned. Doris groups multiple loads into one transaction commit based on the
`group_commit_interval` table property, and the data is visible after the
commit. To prevent excessive disk space usage by the WAL, it automatically
switches to `sync_mode`. This is suitable for latency-sensitive and
high-frequency writing.
+The number of WALs can be viewed through the FE HTTP interface, as detailed
[here](../../admin-manual/fe/get-wal-size-action.md). Alternatively, you can
search for the keyword `wal` in the BE metrics.
+
## Basic operations
If the table schema is:
@@ -67,7 +69,7 @@ To reduce the CPU cost of SQL parsing and query planning, we
provide the `Prepar
1. Setup JDBC url and enable server side prepared statement
```
-url = jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true
+url =
jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=500
```
2. Set `group_commit` session variable, there are two ways to do it:
@@ -75,7 +77,7 @@ url = jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true
* Add `sessionVariables=group_commit=async_mode` in JDBC url
```
-url =
jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true&sessionVariables=group_commit=async_mode
+url = url =
jdbc:mysql://127.0.0.1:9030/db?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=500&sessionVariables=group_commit=async_mode
```
* Use `SET group_commit = async_mode;` command
@@ -90,7 +92,7 @@ try (Statement statement = conn.createStatement()) {
```java
private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
-private static final String URL_PATTERN =
"jdbc:mysql://%s:%d/%s?useServerPrepStmts=true";
+private static final String URL_PATTERN =
"jdbc:mysql://%s:%d/%s?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=500&sessionVariables=group_commit=async_mode";
private static final String HOST = "127.0.0.1";
private static final int PORT = 9087;
private static final String DB = "db";
@@ -99,35 +101,12 @@ private static final String USER = "root";
private static final String PASSWD = "";
private static final int INSERT_BATCH_SIZE = 10;
-private static void groupCommitInsert() throws Exception {
- Class.forName(JDBC_DRIVER);
- try (Connection conn =
DriverManager.getConnection(String.format(URL_PATTERN, HOST, PORT, DB), USER,
PASSWD)) {
- // set session variable 'group_commit'
- try (Statement statement = conn.createStatement()) {
- statement.execute("SET group_commit = async_mode;");
- }
-
- String query = "insert into " + TBL + " values(?, ?, ?)";
- try (PreparedStatement stmt = conn.prepareStatement(query)) {
- for (int i = 0; i < INSERT_BATCH_SIZE; i++) {
- stmt.setInt(1, i);
- stmt.setString(2, "name" + i);
- stmt.setInt(3, i + 10);
- int result = stmt.executeUpdate();
- System.out.println("rows: " + result);
- }
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
-}
-
private static void groupCommitInsertBatch() throws Exception {
Class.forName(JDBC_DRIVER);
// add rewriteBatchedStatements=true and cachePrepStmts=true in JDBC url
// set session variables by sessionVariables=group_commit=async_mode in
JDBC url
try (Connection conn = DriverManager.getConnection(
- String.format(URL_PATTERN +
"&rewriteBatchedStatements=true&cachePrepStmts=true&sessionVariables=group_commit=async_mode",
HOST, PORT, DB), USER, PASSWD)) {
+ String.format(URL_PATTERN, HOST, PORT, DB), USER, PASSWD)) {
String query = "insert into " + TBL + " values(?, ?, ?)";
try (PreparedStatement stmt = conn.prepareStatement(query)) {
@@ -148,7 +127,122 @@ private static void groupCommitInsertBatch() throws
Exception {
}
```
-See [Synchronize Data Using Insert Method](./insert-into-manual) for more
details about **JDBC**.
+**Note:** Due to the high frequency of `INSERT INTO` statements, a large
amount of audit logs might be printed, which could impact overall performance.
By default, the audit log for prepared statements is disabled. You can control
whether to print the audit log for prepared statements by setting a session
variable.
+
+```sql
+# Configure the session variable to enable printing the audit log for prepared
statements. By default, it is set to false, which disables printing the audit
log for prepared statements.
+set enable_prepared_stmt_audit_log=true;
+```
+
+For more usage on **JDBC**, refer to [Using Insert to Synchronize
Data](./insert-into-manual).
+
+### Using Golang for Group Commit
+
+Golang has limited support for prepared statements, so we can manually batch
the statements on the client side to improve the performance of Group Commit.
Below is an example program.
+
+```Golang
+package main
+
+import (
+ "database/sql"
+ "fmt"
+ "math/rand"
+ "strings"
+ "sync"
+ "sync/atomic"
+ "time"
+
+ _ "github.com/go-sql-driver/mysql"
+)
+
+const (
+ host = "127.0.0.1"
+ port = 9038
+ db = "test"
+ user = "root"
+ password = ""
+ table = "async_lineitem"
+)
+
+var (
+ threadCount = 20
+ batchSize = 100
+)
+
+var totalInsertedRows int64
+var rowsInsertedLastSecond int64
+
+func main() {
+ dbDSN := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?parseTime=true", user, password,
host, port, db)
+ db, err := sql.Open("mysql", dbDSN)
+ if err != nil {
+ fmt.Printf("Error opening database: %s\n", err)
+ return
+ }
+ defer db.Close()
+
+ var wg sync.WaitGroup
+ for i := 0; i < threadCount; i++ {
+ wg.Add(1)
+ go func() {
+ defer wg.Done()
+ groupCommitInsertBatch(db)
+ }()
+ }
+
+ go logInsertStatistics()
+
+ wg.Wait()
+}
+
+func groupCommitInsertBatch(db *sql.DB) {
+ for {
+ valueStrings := make([]string, 0, batchSize)
+ valueArgs := make([]interface{}, 0, batchSize*16)
+ for i := 0; i < batchSize; i++ {
+ for i = 0; i < batchSize; i++ {
+ valueStrings = append(valueStrings, "(?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?)")
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, rand.Intn(1000))
+ valueArgs = append(valueArgs, sql.NullFloat64{Float64: 1.0,
Valid: true})
+ valueArgs = append(valueArgs, sql.NullFloat64{Float64: 1.0,
Valid: true})
+ valueArgs = append(valueArgs, sql.NullFloat64{Float64: 1.0,
Valid: true})
+ valueArgs = append(valueArgs, sql.NullFloat64{Float64: 1.0,
Valid: true})
+ valueArgs = append(valueArgs, "N")
+ valueArgs = append(valueArgs, "O")
+ valueArgs = append(valueArgs, time.Now())
+ valueArgs = append(valueArgs, time.Now())
+ valueArgs = append(valueArgs, time.Now())
+ valueArgs = append(valueArgs, "DELIVER IN PERSON")
+ valueArgs = append(valueArgs, "SHIP")
+ valueArgs = append(valueArgs, "N/A")
+ }
+ }
+ stmt := fmt.Sprintf("INSERT INTO %s VALUES %s",
+ table, strings.Join(valueStrings, ","))
+ _, err := db.Exec(stmt, valueArgs...)
+ if err != nil {
+ fmt.Printf("Error executing batch: %s\n", err)
+ return
+ }
+ atomic.AddInt64(&rowsInsertedLastSecond, int64(batchSize))
+ atomic.AddInt64(&totalInsertedRows, int64(batchSize))
+ }
+}
+
+func logInsertStatistics() {
+ for {
+ time.Sleep(1 * time.Second)
+ fmt.Printf("Total inserted rows: %d\n", totalInsertedRows)
+ fmt.Printf("Rows inserted in the last second: %d\n",
rowsInsertedLastSecond)
+ rowsInsertedLastSecond = 0
+ }
+}
+
+```
+
### INSERT INTO VALUES
@@ -434,9 +528,13 @@ We have separately tested the write performance of group
commit in high-concurre
#### Environment
-* 1 FE: 8-core CPU, 16 GB RAM, 1 200 GB SSD disk
-* 3 BE: 16-core CPU, 64 GB RAM, 1 2 TB SSD disk
-* 1 Client: 8-core CPU, 64 GB RAM, 1 100 GB SSD disk
+* 1 Front End (FE) server: Alibaba Cloud with 8-core CPU, 16GB RAM, and one
100GB ESSD PL1 SSD.
+
+* 3 Backend (BE) servers: Alibaba Cloud with 16-core CPU, 64GB RAM, and one
1TB ESSD PL1 SSD.
+
+* 1 Test Client: Alibaba Cloud with 16-core CPU, 64GB RAM, and one 100GB ESSD
PL1 SSD.
+
+* The version for testing is Doris-2.1.5.
#### DataSet
@@ -448,24 +546,24 @@ We have separately tested the write performance of group
commit in high-concurre
#### Test Method
-* Setting different single-concurrency data size and concurrency num between
`non group_commit` and `group_commit` modes.
+* Setting different single-concurrency data size and concurrency num between
`non group_commit` and `group_commit=async mode` modes.
#### Test Result
| Load Way | Single-concurrency Data Size | Concurrency | Cost
Seconds | Rows / Seconds | MB / Seconds |
-|--------------------|------------------------------|-------------|--------------------|----------------|--------------|
-| `group_commit` | 10 KB | 10 | 3707
| 66,697 | 8.56 |
-| `group_commit` | 10 KB | 30 | 3385
| 73,042 | 9.38 |
-| `group_commit` | 100 KB | 10 | 473
| 522,725 | 67.11 |
-| `group_commit` | 100 KB | 30 | 390
| 633,972 | 81.39 |
-| `group_commit` | 500 KB | 10 | 323
| 765,477 | 98.28 |
-| `group_commit` | 500 KB | 30 | 309
| 800,158 | 102.56 |
-| `group_commit` | 1 MB | 10 | 304
| 813,319 | 104.24 |
-| `group_commit` | 1 MB | 30 | 286
| 864,507 | 110.88 |
-| `group_commit` | 10 MB | 10 | 290
| 852,583 | 109.28 |
-| `non group_commit` | 1 MB | 10 | `-235
error` | | |
-| `non group_commit` | 10 MB | 10 | 519
| 476,395 | 61.12 |
-| `non group_commit` | 10 MB | 30 | `-235
error` | | |
+|------------------|-------------|--------|-------------|--------------------|-------------------|
+| group_commit | 10 KB | 10 | 3306 | 74,787 | 9.8
|
+| group_commit | 10 KB | 30 | 3264 | 75,750 | 10.0
|
+| group_commit | 100 KB | 10 | 424 | 582,447 | 76.7
|
+| group_commit | 100 KB | 30 | 366 | 675,543 | 89.0
|
+| group_commit | 500 KB | 10 | 187 | 1,318,661 |
173.7 |
+| group_commit | 500 KB | 30 | 183 | 1,351,087 |
178.0 |
+| group_commit | 1 MB | 10 | 178 | 1,385,148 |
182.5 |
+| group_commit | 1 MB | 30 | 178 | 1,385,148 |
182.5 |
+| group_commit | 10 MB | 10 | 177 | 1,396,887 |
184.0 |
+| non group_commit | 1 MB | 10 | 2824 | 87,536 |
11.5 |
+| non group_commit | 10 MB | 10 | 450 | 549,442 |
68.9 |
+| non group_commit | 10 MB | 30 | 177 | 1,396,887 |
184.0 |
In the above test, the CPU usage of BE fluctuates between 10-40%.
@@ -475,9 +573,15 @@ The `group_commit` effectively enhances import performance
while reducing the nu
#### Environment
-* 1 FE: 8-core CPU, 16 GB RAM, 1 200 GB SSD disk
-* 1 BE: 16-core CPU, 64 GB RAM, 1 2 TB SSD disk
-* 1 Client: 16-core CPU, 64 GB RAM, 1 100 GB SSD disk
+1 Front End (FE) server: Alibaba Cloud with an 8-core CPU, 16GB RAM, and one
100GB ESSD PL1 SSD.
+
+1 Backend (BE) server: Alibaba Cloud with a 16-core CPU, 64GB RAM, and one
500GB ESSD PL1 SSD.
+
+1 Test Client: Alibaba Cloud with a 16-core CPU, 64GB RAM, and one 100GB ESSD
PL1 SSD.
+
+The testing version is Doris-2.1.5.
+
+Disable the printing of prepared statement audit logs to enhance performance.
#### DataSet
@@ -495,7 +599,149 @@ The `group_commit` effectively enhances import
performance while reducing the nu
| Rows per insert | Concurrency | Rows / Second | MB / Second |
-|-----------------|-------------|---------------|-------------|
-| 100 | 20 | 106931 | 11.46 |
+|-------------------|--------|--------------------|--------------------|
+| 100 | 10 | 107,172 | 11.47 |
+| 100 | 20 | 140,317 | 14.79 |
+| 100 | 30 | 142,882 | 15.28 |
In the above test, the CPU usage of BE fluctuates between 10-20%, FE
fluctuates between 60-70%.
+
+
+### Insert into Sync Mode Small Batch Data
+
+**Machine Configuration**
+
+* 1 Front-End (FE): Alibaba Cloud, 16-core CPU, 64GB RAM, 1 x 500GB ESSD PL1
cloud disk
+* 5 Back-End (BE) nodes: Alibaba Cloud, 16-core CPU, 64GB RAM, 1 x 1TB ESSD
PL1 cloud disk.
+* 1 Testing Client: Alibaba Cloud, 16-core CPU, 64GB RAM, 1 x 100GB ESSD PL1
cloud disk
+* Test version: Doris-2.1.5
+
+**Dataset**
+
+* The data of tpch sf10 `lineitem` table.
+
+* The create table statement is
+```sql
+CREATE TABLE IF NOT EXISTS lineitem (
+ L_ORDERKEY INTEGER NOT NULL,
+ L_PARTKEY INTEGER NOT NULL,
+ L_SUPPKEY INTEGER NOT NULL,
+ L_LINENUMBER INTEGER NOT NULL,
+ L_QUANTITY DECIMAL(15,2) NOT NULL,
+ L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
+ L_DISCOUNT DECIMAL(15,2) NOT NULL,
+ L_TAX DECIMAL(15,2) NOT NULL,
+ L_RETURNFLAG CHAR(1) NOT NULL,
+ L_LINESTATUS CHAR(1) NOT NULL,
+ L_SHIPDATE DATE NOT NULL,
+ L_COMMITDATE DATE NOT NULL,
+ L_RECEIPTDATE DATE NOT NULL,
+ L_SHIPINSTRUCT CHAR(25) NOT NULL,
+ L_SHIPMODE CHAR(10) NOT NULL,
+ L_COMMENT VARCHAR(44) NOT NULL
+)
+DUPLICATE KEY(L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER)
+DISTRIBUTED BY HASH(L_ORDERKEY) BUCKETS 32
+PROPERTIES (
+ "replication_num" = "3"
+);
+```
+
+**Testing Tool**
+
+* [Jmeter](https://jmeter.apache.org/)
+
+JMeter Parameter Settings as Shown in the Images
+
+
+
+
+1. Set the Init Statement Before Testing:
+set group_commit=async_mode and set enable_nereids_planner=false.
+
+2. Enable JDBC Prepared Statement:
+Complete URL:
+jdbc:mysql://127.0.0.1:9030?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=99999&prepStmtCacheSize=50&sessionVariables=group_commit=async_mode&sessionVariables=enable_nereids_planner=false.
+
+3. Set the Import Type to Prepared Update Statement.
+
+4. Set the Import Statement.
+
+5. Set the Values to Be Imported:
+Ensure that the imported values match the data types one by one.
+
+**Testing Methodology**
+
+* Use JMeter to write data into Doris. Each thread writes 1 row of data per
execution using the insert into statement.
+
+**Test Results**
+
+* Data unit: rows per second.
+
+* The following tests are divided into 30, 100, and 500 concurrency.
+
+**Performance Test with 30 Concurrent Users in Sync Mode, 5 BEs, and 3
Replicas**
+
+| Group Commit Interval | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+| | 321.5 | 307.3 | 285.8 | 224.3 |
+
+**Performance Test with 100 Concurrent Users in Sync Mode, 5 BEs, and 3
Replicas**
+
+| Group Commit Interval | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+| | 1175.2 | 1108.7 | 1016.3 | 704.5 |
+
+**Performance Test with 500 Concurrent Users in Sync Mode, 5 BEs, and 3
Replicas**
+
+| Group Commit Interval | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+| | 3289.8 | 3686.7 | 3280.7 | 2609.2 |
+
+### Insert into Sync Mode Large Batch Data
+
+**Machine Configuration**
+
+* 1 Front-End (FE): Alibaba Cloud, 16-core CPU, 64GB RAM, 1 x 500GB ESSD PL1
cloud disk
+
+* 5 Back-End (BE) nodes: Alibaba Cloud, 16-core CPU, 64GB RAM, 1 x 1TB ESSD
PL1 cloud disk.
+
+* 1 Testing Client: Alibaba Cloud, 16-core CPU, 64GB RAM, 1 x 100GB ESSD PL1
cloud disk
+
+* Test version: Doris-2.1.5
+
+**Dataset**
+
+* Insert into statement for 1000 rows: `insert into tbl values(1,1)...` (1000
rows omitted)
+
+**Testing Tool**
+
+* [Jmeter](https://jmeter.apache.org/)
+
+**Testing Methodology**
+
+* Use JMeter to write data into Doris. Each thread writes 1000 row of data per
execution using the insert into statement.
+
+**Test Results**
+
+* Data unit: rows per second.
+
+* The following tests are divided into 30, 100, and 500 concurrency.
+
+**Performance Test with 30 Concurrent Users in Sync Mode, 5 BEs, and 3
Replicas**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+| | 92.2K | 85.9K | 84K | 83.2K |
+
+**Performance Test with 100 Concurrent Users in Sync Mode, 5 BEs, and 3
Replicas**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+| | 70.4K |70.5K | 73.2K | 69.4K |
+
+**Performance Test with 500 Concurrent Users in Sync Mode, 5 BEs, and 3
Replicas**
+
+| Group commit internal | 10ms | 20ms | 50ms | 100ms |
+|-----------------------|---------------|---------------|---------------|---------------|
+| | 46.3K | 47.7K | 47.4K | 46.5K |
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
