oceanbase分区表性能没有达到预期

OceanBase 技术问题
#1

【 使用环境 】测试环境
【 OB or 其他组件 】observer+proxy
【 使用版本 】4.5.0.0-100000012025112711
【问题描述】使用副本表测试的性能较高，使用双倍资源的partition by hash(id + 1) partitions 4分区表性能看起来没有提升很多
【复现路径】

  1. 三台物理机交叉部署3个zone，每个zone两个节点
kubectl get pods -n ob -o=json | jq -r '.items[] | "\(.metadata.name): \(.spec.containers[] | "\(.name): CPU Limit=\(.resources.limits.cpu // "none"), Memory Limit=\(.resources.limits.memory // "none")")"'
obcluster-1-zone1-2p49cr: observer: CPU Limit=8, Memory Limit=32Gi
obcluster-1-zone1-2p49cr: obagent: CPU Limit=1, Memory Limit=2Gi
obcluster-1-zone1-qdfklj: observer: CPU Limit=8, Memory Limit=32Gi
obcluster-1-zone1-qdfklj: obagent: CPU Limit=1, Memory Limit=2Gi
obcluster-1-zone2-mwbcmj: observer: CPU Limit=8, Memory Limit=32Gi
obcluster-1-zone2-mwbcmj: obagent: CPU Limit=1, Memory Limit=2Gi
obcluster-1-zone2-vnxmnn: observer: CPU Limit=8, Memory Limit=32Gi
obcluster-1-zone2-vnxmnn: obagent: CPU Limit=1, Memory Limit=2Gi
obcluster-1-zone3-c2sct9: observer: CPU Limit=8, Memory Limit=32Gi
obcluster-1-zone3-c2sct9: obagent: CPU Limit=1, Memory Limit=2Gi
obcluster-1-zone3-wgb55p: observer: CPU Limit=8, Memory Limit=32Gi
obcluster-1-zone3-wgb55p: obagent: CPU Limit=1, Memory Limit=2Gi
obproxy-obvs-55468f874b-pscvn: obproxy: CPU Limit=30, Memory Limit=16Gi
obproxy-obvs-55468f874b-qsfsc: obproxy: CPU Limit=30, Memory Limit=16Gi
obproxy-obvs-55468f874b-x57zj: obproxy: CPU Limit=30, Memory Limit=16Gi

kubectl get pod -n ob -owide
NAME                            READY   STATUS    RESTARTS   AGE   IP               NODE             NOMINATED NODE   READINESS GATES
obcluster-1-zone1-2p49cr        2/2     Running   0          22h   10.244.136.17    xos-r8ixobgt     <none>           <none>
obcluster-1-zone1-qdfklj        2/2     Running   0          22h   10.244.214.185   xos-00003-f318   <none>           <none>
obcluster-1-zone2-mwbcmj        2/2     Running   0          22h   10.244.163.156   xos-00002-efe1   <none>           <none>
obcluster-1-zone2-vnxmnn        2/2     Running   0          22h   10.244.136.20    xos-r8ixobgt     <none>           <none>
obcluster-1-zone3-c2sct9        2/2     Running   0          22h   10.244.214.163   xos-00003-f318   <none>           <none>
obcluster-1-zone3-wgb55p        2/2     Running   0          22h   10.244.163.173   xos-00002-efe1   <none>           <none>
obproxy-obvs-55468f874b-pscvn   1/1     Running   0          22h   10.244.214.143   xos-00003-f318   <none>           <none>
obproxy-obvs-55468f874b-qsfsc   1/1     Running   0          22h   10.244.163.133   xos-00002-efe1   <none>           <none>
obproxy-obvs-55468f874b-x57zj   1/1     Running   0          22h   10.244.136.50    xos-r8ixobgt     <none>           <none>

kubectl get svc -n ob
NAME               TYPE       CLUSTER-IP     EXTERNAL-IP   PORT(S)                         AGE
svc-obproxy-obvs   NodePort   10.192.1.130   <none>        2883:31281/TCP,2884:31506/TCP   6d14h
  1. 创建大租户
mysql> CREATE RESOURCE UNIT min_uc_000 MAX_CPU 1, MEMORY_SIZE '2G', LOG_DISK_SIZE '2G' ;
mysql> ALTER RESOURCE UNIT  min_uc_000 MAX_CPU=2, MIN_CPU=2, MEMORY_SIZE='15G';
mysql> CREATE RESOURCE POOL min_rp_001 UNIT 'min_uc_000', UNIT_NUM 1, ZONE_LIST ('zone1','zone2','zone3');
mysql> CREATE TENANT IF NOT EXISTS min_tenant_000 LOCALITY = "F@zone1, F@zone2, F@zone3", PRIMARY_ZONE = "zone1", RESOURCE_POOL_LIST=('min_rp_001') set OB_TCP_INVITED_NODES='%';
mysql> ALTER RESOURCE TENANT  min_tenant_000 UNIT_NUM = 2;
mysql> ALTER RESOURCE UNIT  min_uc_000 MAX_CPU=13, MIN_CPU=13, MEMORY_SIZE='17G';
  1. 测试副本表性能
mysql> SELECT * FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE DATABASE_NAME = 'test';
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+----------+--------------+-----------------+----------------------------+-----------+-----------------+---------------+----------+------------+
| TENANT_ID | DATABASE_NAME | TABLE_NAME | TABLE_ID | TABLE_TYPE | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | DATA_TABLE_ID | TABLET_ID | LS_ID | ZONE  | SVR_IP         | SVR_PORT | ROLE     | REPLICA_TYPE | DUPLICATE_SCOPE | DUPLICATE_READ_CONSISTENCY | OBJECT_ID | TABLEGROUP_NAME | TABLEGROUP_ID | SHARDING | INDEX_TYPE |
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+----------+--------------+-----------------+----------------------------+-----------+-----------------+---------------+----------+------------+
|      1002 | test          | vsmongo    |   500075 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200060 |  1002 | zone1 | 10.244.136.17  |     2882 | LEADER   | FULL         | NONE            | NONE                       |    500075 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500075 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200060 |  1002 | zone2 | 10.244.163.156 |     2882 | FOLLOWER | FULL         | NONE            | NONE                       |    500075 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500075 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200060 |  1002 | zone3 | 10.244.214.163 |     2882 | FOLLOWER | FULL         | NONE            | NONE                       |    500075 | NULL            |          NULL | NULL     | NULL       |
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+----------+--------------+-----------------+----------------------------+-----------+-----------------+---------------+----------+------------+
3 rows in set (0.50 sec)

测试命令示例：

./test-ob-client --hosts="10.192.1.130:2883" --user root --password 123 --tenantName="min_tenant_000" --loadcount  4000000 --dropTable && sleep 60
./test-ob-client --hosts="10.192.1.130:2883" --user root --password 123 --tenantName="min_tenant_000" --loadcount  4000000 --threads 200 --connPoolSize 200 --load && sleep 60
./test-ob-client --hosts="10.192.1.130:2883" --user root --password 123 --tenantName="min_tenant_000" --loadcount  4000000 --threads 200 --connPoolSize 200 --run --updateratio=0 && sleep 60
./test-ob-client --hosts="10.192.1.130:2883" --user root --password 123 --tenantName="min_tenant_000" --loadcount  4000000 --threads 200 --connPoolSize 200 --run --updateratio=0.05 && sleep 60
  1. 测试分区表
mysql> SELECT * FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE DATABASE_NAME = 'test';
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+----------+--------------+-----------------+----------------------------+-----------+-----------------+---------------+----------+------------+
| TENANT_ID | DATABASE_NAME | TABLE_NAME | TABLE_ID | TABLE_TYPE | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | DATA_TABLE_ID | TABLET_ID | LS_ID | ZONE  | SVR_IP         | SVR_PORT | ROLE     | REPLICA_TYPE | DUPLICATE_SCOPE | DUPLICATE_READ_CONSISTENCY | OBJECT_ID | TABLEGROUP_NAME | TABLEGROUP_ID | SHARDING | INDEX_TYPE |
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+----------+--------------+-----------------+----------------------------+-----------+-----------------+---------------+----------+------------+
|      1002 | test          | vsmongo    |   500070 | USER TABLE | p0             | NULL              | NULL       |          NULL |    200056 |  1002 | zone1 | 10.244.136.17  |     2882 | LEADER   | FULL         | NONE            | NONE                       |    500071 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500070 | USER TABLE | p0             | NULL              | NULL       |          NULL |    200056 |  1002 | zone2 | 10.244.163.156 |     2882 | FOLLOWER | FULL         | NONE            | NONE                       |    500071 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500070 | USER TABLE | p0             | NULL              | NULL       |          NULL |    200056 |  1002 | zone3 | 10.244.214.163 |     2882 | FOLLOWER | FULL         | NONE            | NONE                       |    500071 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500070 | USER TABLE | p1             | NULL              | NULL       |          NULL |    200057 |  1002 | zone1 | 10.244.136.17  |     2882 | LEADER   | FULL         | NONE            | NONE                       |    500072 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500070 | USER TABLE | p1             | NULL              | NULL       |          NULL |    200057 |  1002 | zone2 | 10.244.163.156 |     2882 | FOLLOWER | FULL         | NONE            | NONE                       |    500072 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500070 | USER TABLE | p1             | NULL              | NULL       |          NULL |    200057 |  1002 | zone3 | 10.244.214.163 |     2882 | FOLLOWER | FULL         | NONE            | NONE                       |    500072 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500070 | USER TABLE | p2             | NULL              | NULL       |          NULL |    200058 |  1002 | zone1 | 10.244.136.17  |     2882 | LEADER   | FULL         | NONE            | NONE                       |    500073 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500070 | USER TABLE | p2             | NULL              | NULL       |          NULL |    200058 |  1002 | zone2 | 10.244.163.156 |     2882 | FOLLOWER | FULL         | NONE            | NONE                       |    500073 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500070 | USER TABLE | p2             | NULL              | NULL       |          NULL |    200058 |  1002 | zone3 | 10.244.214.163 |     2882 | FOLLOWER | FULL         | NONE            | NONE                       |    500073 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500070 | USER TABLE | p3             | NULL              | NULL       |          NULL |    200059 |  1002 | zone1 | 10.244.136.17  |     2882 | LEADER   | FULL         | NONE            | NONE                       |    500074 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500070 | USER TABLE | p3             | NULL              | NULL       |          NULL |    200059 |  1002 | zone2 | 10.244.163.156 |     2882 | FOLLOWER | FULL         | NONE            | NONE                       |    500074 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500070 | USER TABLE | p3             | NULL              | NULL       |          NULL |    200059 |  1002 | zone3 | 10.244.214.163 |     2882 | FOLLOWER | FULL         | NONE            | NONE                       |    500074 | NULL            |          NULL | NULL     | NULL       |
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+----------+--------------+-----------------+----------------------------+-----------+-----------------+---------------+----------+------------+
12 rows in set (0.67 sec)

测试命令示例

./test-ob-client --hosts="10.192.1.130:2883" --user root --password 123 --tenantName="min_tenant_000" --loadcount  4000000 --dropTable && sleep 60
./test-ob-client --hosts="10.192.1.130:2883" --user root --password 123 --tenantName="min_tenant_000" --loadcount  4000000 --threads 200 --connPoolSize 200 --load --usePartition && sleep 60
./test-ob-client --hosts="10.192.1.130:2883" --user root --password 123 --tenantName="min_tenant_000" --loadcount  4000000 --threads 200 --connPoolSize 200 --run --updateratio=0 && sleep 60
./test-ob-client --hosts="10.192.1.130:2883" --user root --password 123 --tenantName="min_tenant_000" --loadcount  4000000 --threads 200 --connPoolSize 200 --run --updateratio=0.05 && sleep 60

5 测试结果对比
| 场景| load| r:w=100:0| r:w=95:5| r:w=50:50| r:w=5:95| r:w=0:100|

|------|------|------|

| 副本表 | 26494 | 63314 | 56833 | 30272 | 20588 | 17663 |

| 分区表 | 28841 | 64768 | 58074 | 31076 | 20163 | 18371 |

期望效果

副本表测试过程中，logstream所在的pod的CPU打满了。分区表测试过程中，两个logstream的pod的CPU也打满了。理论上分区的测试性能应该高于副本表，或者差不多得有0.5-1倍的性能提升吧

1 个赞
#3

我的测试端代码如下：

package main

import (
	"database/sql"
	"fmt"
	"math/rand"
	"os"
	"runtime"
	"strings"
	"sync"
	"sync/atomic"
	"time"

	"github.com/alecthomas/kong"
	_ "github.com/go-sql-driver/mysql"
)

// 全局查询耗时统计
var (
	globalMinLatency   int64 = 1<<63 - 1 // 初始化为最大int64值
	globalMaxLatency   int64 = 0         // 初始化为0
	globalTotalLatency int64 = 0         // 总耗时，用于计算平均值
	globalTotalQueries int64 = 0         // 总查询次数
)

// 测试参数
type CurrentOpFlags struct {
	Hosts        string `name:"hosts" help:"OceanBase服务器 host 地址" default:"10.0.0.11:32759"`
	User         string `name:"user" help:"OceanBase用户名" default:"root"`
	Password     string `name:"password" help:"OceanBase密码" default:"root_password"`
	Threads      int    `name:"threads" help:"number of threads of goroutine." default:"12"`
	ConnPoolSize int    `name:"connPoolSize" help:"number of threads in sdk connection thread pool." default:"100"`

	DBName     string `name:"dbname" help:"数据库名称" default:"test"`
	TableName  string `name:"tablename" help:"表名称" default:"test.vsmongo"`
	TenantName string `name:"tenantName" help:"租户名称" default:"tenant_test_0"`

	FieldCount   int  `name:"fieldcount" help:"字段数量" default:"5"`
	UsePartition bool `name:"usePartition" help:"是否创建分区表" default:"false"`

	Load      bool `name:"load" help:"insert records to collections" default:"false"`
	LoadCount int  `name:"loadcount" help:"加载记录数量" default:"10000"`

	Run         bool    `name:"run" help:"query or update records to collections" default:"false"`
	ReadWeak    bool    `name:"readweak" help:"read weak" default:"false"`
	UpdateRatio float64 `name:"updateratio" help:"更新操作比例" default:"0.5"`

	DropTable bool `name:"dropTable" help:"drop table or collection" default:"false"`
}

// 解析命令行参数
func ParseOptions() CurrentOpFlags {
	var opts CurrentOpFlags
	_ = kong.Parse(&opts,
		kong.Name("oceanbase performance test tool"),
		kong.Description("OceanBase Performance Test Tool"),
		kong.UsageOnError(),
		kong.ConfigureHelp(kong.HelpOptions{
			Compact: true,
		}),
	)

	// 验证参数
	if opts.UpdateRatio < 0 || opts.UpdateRatio > 1 {
		fmt.Printf("错误: UpdateRatio 必须在 0-1 之间，当前值为: %.2f\n", opts.UpdateRatio)
		os.Exit(1)
	}

	return opts
}

// 创建 OceanBase 连接的通用函数
func createOBConnection(password, tenantName, dbName string, flg CurrentOpFlags) (*sql.DB, error) {
	var dsn string

	// 根据 tenantName 是否为空来构建不同的 DSN
	if tenantName == "" {
		// 连接到全局 OceanBase（不指定 tenant）
		dsn = fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8mb4", flg.User, password, flg.Hosts, dbName)
	} else if password == "" {
		// 连接到指定的 tenant（不带密码）
		dsn = fmt.Sprintf("%s@%s:@tcp(%s)/%s?charset=utf8mb4", flg.User, tenantName, flg.Hosts, dbName)
	} else {
		// 连接到指定的 tenant（带密码）
		dsn = fmt.Sprintf("%s@%s:%s@tcp(%s)/%s?charset=utf8mb4", flg.User, tenantName, password, flg.Hosts, dbName)
	}

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		fmt.Printf("连接数据库失败 dsn: %s, error: %v\n", dsn, err)
		return nil, err
	}

	// 设置连接池参数
	db.SetMaxOpenConns(flg.ConnPoolSize)     // 最大同时连接数
	db.SetMaxIdleConns(flg.ConnPoolSize)     // 最大空闲连接数
	db.SetConnMaxLifetime(600 * time.Minute) // 连接最大存活时间（秒）
	db.SetConnMaxIdleTime(600 * time.Minute) // 空闲连接最大存活时间（秒）

	// 使用Ping验证连接是否有效
	if err := db.Ping(); err != nil {
		fmt.Printf("数据库连接Ping失败 dsn: %s, error: %v\n", dsn, err)
		db.Close()
		return nil, err
	}

	// 使用简单的SELECT查询进一步验证连接
	var version string
	if err := db.QueryRow("SELECT VERSION()").Scan(&version); err != nil {
		fmt.Printf("数据库查询验证失败 dsn: %s, error: %v\n", dsn, err)
		db.Close()
		return nil, err
	}

	// 如果是连接到 tenant，打印 tenant 信息
	if tenantName != "" {
		fmt.Printf("租户 %s 连接成功，版本: %s\n", tenantName, version)
	} else {
		fmt.Printf("OceanBase 连接成功，版本: %s\n", version)
	}

	return db, nil
}

// 更新全局耗时统计
func updateGlobalLatencyStats(elapsedUs int64) {
	// 使用原子操作更新全局统计
	for {
		currentMin := atomic.LoadInt64(&globalMinLatency)
		if elapsedUs >= currentMin {
			break
		}
		if atomic.CompareAndSwapInt64(&globalMinLatency, currentMin, elapsedUs) {
			break
		}
	}

	for {
		currentMax := atomic.LoadInt64(&globalMaxLatency)
		if elapsedUs <= currentMax {
			break
		}
		if atomic.CompareAndSwapInt64(&globalMaxLatency, currentMax, elapsedUs) {
			break
		}
	}

	atomic.AddInt64(&globalTotalLatency, elapsedUs)
	atomic.AddInt64(&globalTotalQueries, 1)
}

// 创建表
func createTable(db *sql.DB, flg *CurrentOpFlags) error {
	createDatabaseSQL := fmt.Sprintf("CREATE DATABASE IF NOT EXISTS %s", flg.DBName)
	if _, err := db.Exec(createDatabaseSQL); err != nil {
		fmt.Printf("Failed to create db: %v, sql: %s\n", err, createDatabaseSQL)
		return err
	}

	// 生成字段定义
	fields := []string{"id INT PRIMARY KEY"}
	for i := 0; i < flg.FieldCount; i++ {
		fields = append(fields, fmt.Sprintf("f%d VARCHAR(%d)", i+1, 255))
	}
	columns := strings.Join(fields, ", ")

	// 创建表SQL - 根据选项决定是否使用分区
	var createSQL string
	if flg.UsePartition {
		createSQL = fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s (%s) partition by hash(id + 1) partitions 4;", flg.TableName, columns)
	} else {
		createSQL = fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s (%s);", flg.TableName, columns)
	}
	_, err := db.Exec(createSQL)
	return err
}

// 插入数据
func loadData(db *sql.DB, flg *CurrentOpFlags) error {
	var wg sync.WaitGroup
	chunkSize := flg.LoadCount / flg.Threads
	// 启动多个goroutine并行插入
	for i := 0; i < flg.Threads; i++ {
		wg.Add(1)

		start := i*chunkSize + 1
		end := (i + 1) * chunkSize
		if i == flg.Threads-1 {
			end = flg.LoadCount
		}

		go func(start, end int) {
			defer wg.Done()
			for j := start; j <= end; j++ {
				// 生成字段定义
				fields := []string{}
				values := []string{}
				for i := 0; i < flg.FieldCount; i++ {
					fields = append(fields, fmt.Sprintf("f%d", i+1))
					values = append(values, fmt.Sprintf("'%s'", generateRandomString(flg.LoadCount)))
				}
				fieldStr := strings.Join(fields, ", ")
				valueStr := strings.Join(values, ", ")

				// 构建插入语句
				insertSQL := fmt.Sprintf("INSERT INTO %s (id, %s) VALUES (%d, %s)", flg.TableName, fieldStr, j, valueStr)

				// 记录插入开始时间
				startTime := time.Now()

				_, err := db.Exec(insertSQL)
				if err != nil {
					fmt.Fprintf(os.Stderr, "插入失败 (id=%d): %v\n", j, err)
				}

				// 计算插入耗时（微秒）
				elapsedTime := time.Since(startTime)
				elapsedUs := elapsedTime.Microseconds()

				// 更新全局统计
				updateGlobalLatencyStats(elapsedUs)
			}
		}(start, end)
	}

	// 等待所有goroutine完成
	wg.Wait()
	return nil
}

// 读操作
func performRead(db *sql.DB, flg *CurrentOpFlags) error {
	id := rand.Intn(flg.LoadCount) + 1 // 生成1~1e5的id
	startTime := time.Now()

	filter := fmt.Sprintf(" WHERE id = %d", id)
	querySQL := fmt.Sprintf("SELECT * FROM %s %s;", flg.TableName, filter)
	if flg.ReadWeak {
		querySQL = fmt.Sprintf("SELECT /*+READ_CONSISTENCY(WEAK)*/ * FROM %s %s;", flg.TableName, filter)
	}
	rows, err := db.Query(querySQL)
	if err != nil {
		fmt.Printf("Failed to query ob table %s: %v\n", flg.TableName, err)
		return err
	}

	var count int
	for rows.Next() {
		count++
	}
	rows.Close()

	// 计算查询耗时（微秒）
	elapsedTime := time.Since(startTime)
	elapsedUs := elapsedTime.Microseconds()

	// 更新全局统计
	updateGlobalLatencyStats(elapsedUs)

	return nil
}

// 更新操作
func performUpdate(db *sql.DB, config *CurrentOpFlags) error {
	id := rand.Intn(config.LoadCount) + 1        // 生成1~1e5的id
	fieldIdx := rand.Intn(config.FieldCount) + 1 // 选择要更新的字段（f1~fN）
	newValue := generateRandomString(config.LoadCount)

	query := fmt.Sprintf("UPDATE %s SET f%d = '%s' WHERE id = %d", config.TableName, fieldIdx, newValue, id)

	// 记录更新开始时间
	startTime := time.Now()

	_, err := db.Exec(query)
	if err != nil {
		return err
	}

	// 计算更新耗时（微秒）
	elapsedTime := time.Since(startTime)
	elapsedUs := elapsedTime.Microseconds()

	// 更新全局统计
	updateGlobalLatencyStats(elapsedUs)

	return nil
}

// 生成随机字符串
func generateRandomString(size int) string {
	suffix := "weihtr092384209gsihr92834y098249siofwoerjowioisfh4r09fsfhisfiowerhkjnfaisfweirwr9829r92349wr912h3491"
	return fmt.Sprintf("%s_%d", suffix, rand.Intn(size)+1)
}

func main() {
	// 解析命令行参数
	configVal := ParseOptions()
	flg := &configVal

	var now time.Time

	// 1. 连接OceanBase数据库
	db, err := createOBConnection(flg.Password, flg.TenantName, flg.DBName, *flg)
	if err != nil {
		fmt.Println("连接数据库失败:", err)
		os.Exit(1)
	}
	defer db.Close()

	// 2. 创建表（如果不存在）
	if flg.DropTable {
		now = time.Now()
		dropTableSQL := fmt.Sprintf("DROP TABLE IF EXISTS %s", flg.TableName)
		if _, err := db.Exec(dropTableSQL); err != nil {
			fmt.Println("删除表失败:", err)
			os.Exit(1)
		}
		fmt.Println("删除表成功, spent time: ", time.Since(now))
	} else if err := createTable(db, flg); err != nil {
		fmt.Println("创建表失败:", err)
		os.Exit(1)
	}

	// 3. 加载数据
	if flg.Load {
		now = time.Now()
		if err := loadData(db, flg); err != nil {
			fmt.Println("数据加载失败:", err)
			os.Exit(1)
		}
		fmt.Printf("数据加载完成, 共 %d 条记录, cost: %s\n", flg.LoadCount, time.Since(now))
	}

	// 4. 并发读写测试
	if flg.Run {
		var wg sync.WaitGroup
		runtime.GOMAXPROCS(flg.Threads) // 设置合适的并发数

		var sum int32 = 0
		now = time.Now()

		for i := 0; i < flg.Threads; i++ {
			wg.Add(1)
			go func() {
				defer wg.Done()
				for int(atomic.LoadInt32(&sum)) < flg.LoadCount {
					atomic.AddInt32(&sum, 1)
					// 根据比例选择操作
					if rand.Float64() < 1-flg.UpdateRatio {
						// 读操作：随机查询某条记录
						if err := performRead(db, flg); err != nil {
							fmt.Println("读操作失败:", err)
						}
					} else {
						// 更新操作：随机更新某条记录的某个字段
						if err := performUpdate(db, flg); err != nil {
							fmt.Println("更新操作失败:", err)
						}
					}
				}
			}()
		}
		wg.Wait()
	}

	// 打印全局查询耗时统计
	fmt.Printf("\n=== Global Query Latency Statistics, total cost: %s == qps: %v =\n", time.Since(now), float64(atomic.LoadInt64(&globalTotalQueries))/float64(time.Since(now).Seconds()))
	totalQueries := atomic.LoadInt64(&globalTotalQueries)
	minLatency := atomic.LoadInt64(&globalMinLatency)
	maxLatency := atomic.LoadInt64(&globalMaxLatency)
	totalLatency := atomic.LoadInt64(&globalTotalLatency)

	fmt.Printf("Total queries executed: %d\n", totalQueries)

	if totalQueries > 0 {
		// 计算平均耗时
		avgLatency := float64(totalLatency) / float64(totalQueries)
		fmt.Printf("Min latency: %d us\n", minLatency)
		fmt.Printf("Max latency: %d us\n", maxLatency)
		fmt.Printf("Average latency: %.2f us\n", avgLatency)
	} else {
		fmt.Println("No queries executed.")
	}

	fmt.Println("All operations completed.")
}
#4

PRIMARY_ZONE 可以设置为random

#5
  1. 修改PRIMARY_ZONE
mysql> ALTER TENANT min_tenant_000 SET PRIMARY_ZONE = 'RANDOM';
Query OK, 0 rows affected (0.12 sec)
  1. 重新测试
    leader现在已经分配到了两个物理节点
mysql> SELECT * FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE DATABASE_NAME = 'test';
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+----------+--------------+-----------------+----------------------------+-----------+-----------------+---------------+----------+------------+
| TENANT_ID | DATABASE_NAME | TABLE_NAME | TABLE_ID | TABLE_TYPE | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | DATA_TABLE_ID | TABLET_ID | LS_ID | ZONE  | SVR_IP         | SVR_PORT | ROLE     | REPLICA_TYPE | DUPLICATE_SCOPE | DUPLICATE_READ_CONSISTENCY | OBJECT_ID | TABLEGROUP_NAME | TABLEGROUP_ID | SHARDING | INDEX_TYPE |
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+----------+--------------+-----------------+----------------------------+-----------+-----------------+---------------+----------+------------+
|      1002 | test          | vsmongo    |   500076 | USER TABLE | p0             | NULL              | NULL       |          NULL |    200061 |  1002 | zone1 | 10.244.136.17  |     2882 | LEADER   | FULL         | NONE            | NONE                       |    500077 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500076 | USER TABLE | p0             | NULL              | NULL       |          NULL |    200061 |  1002 | zone2 | 10.244.163.156 |     2882 | FOLLOWER | FULL         | NONE            | NONE                       |    500077 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500076 | USER TABLE | p0             | NULL              | NULL       |          NULL |    200061 |  1002 | zone3 | 10.244.214.163 |     2882 | FOLLOWER | FULL         | NONE            | NONE                       |    500077 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500076 | USER TABLE | p1             | NULL              | NULL       |          NULL |    200062 |  1003 | zone1 | 10.244.136.17  |     2882 | FOLLOWER | FULL         | NONE            | NONE                       |    500078 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500076 | USER TABLE | p1             | NULL              | NULL       |          NULL |    200062 |  1003 | zone2 | 10.244.163.156 |     2882 | LEADER   | FULL         | NONE            | NONE                       |    500078 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500076 | USER TABLE | p1             | NULL              | NULL       |          NULL |    200062 |  1003 | zone3 | 10.244.214.163 |     2882 | FOLLOWER | FULL         | NONE            | NONE                       |    500078 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500076 | USER TABLE | p2             | NULL              | NULL       |          NULL |    200063 |  1002 | zone1 | 10.244.136.17  |     2882 | LEADER   | FULL         | NONE            | NONE                       |    500079 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500076 | USER TABLE | p2             | NULL              | NULL       |          NULL |    200063 |  1002 | zone2 | 10.244.163.156 |     2882 | FOLLOWER | FULL         | NONE            | NONE                       |    500079 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500076 | USER TABLE | p2             | NULL              | NULL       |          NULL |      1002 | test          | vsmongo    |   500076 | USER TABLE | p2             | NULL              | NULL       |          NULL |    200063 |  1002 | zone3 | 10.244.214.163 |     2882 | FOLLOWER | FULL         | NONE            | NONE                       |    500079 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500076 | USER TABLE | p3             | NULL              | NULL       |          NULL |    200064 |  1003 | zone1 | 10.244.136.17  |     2882 | FOLLOWER | FULL         | NONE            | NONE                       |    500080 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500076 | USER TABLE | p3             | NULL              | NULL       |          NULL |    200064 |  1003 | zone2 | 10.244.163.156 |     2882 | LEADER   | FULL         | NONE            | NONE                       |    500080 | NULL            |          NULL | NULL     | NULL       |
|      1002 | test          | vsmongo    |   500076 | USER TABLE | p3             | NULL              | NULL       |          NULL |    200064 |  1003 | zone3 | 10.244.214.163 |     2882 | FOLLOWER | FULL         | NONE            | NONE                       |    500080 | NULL            |          NULL | NULL     | NULL       |
+-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+----------------+----------+----------+--------------+-----------------+----------------------------+-----------+-----------------+---------------+----------+------------+
12 rows in set (0.65 sec)

3 结果对比

结果看起来更差了

| 场景| load| r:w=100:0| r:w=95:5| r:w=50:50| r:w=5:95| r:w=0:100|

|------|------|------|

| 副本表 | 26494 | 63314 | 56833 | 30272 | 20588 | 17663 |

| 分区表-(leader在一个物理节点) | 28841 | 64768 | 58074 | 31076 | 20163 | 18371 |

| 分区表-(leader分配在两个物理节点) | 16650 | 26479 | 24498 | 16976 | | |

比较奇怪的是，另外三个svr没有分配到任何ls和tablet

mysql> SELECT * FROM oceanbase.DBA_OB_SERVERS;
+----------------+----------+----+-------+----------+-----------------+--------+----------------------------+-----------+-----------------------+----------------------------+----------------------------+-------------------------------------------------------------------------------------------+-------------------+
| SVR_IP         | SVR_PORT | ID | ZONE  | SQL_PORT | WITH_ROOTSERVER | STATUS | START_SERVICE_TIME         | STOP_TIME | BLOCK_MIGRATE_IN_TIME | CREATE_TIME                | MODIFY_TIME                | BUILD_VERSION                                                                             | LAST_OFFLINE_TIME |
+----------------+----------+----+-------+----------+-----------------+--------+----------------------------+-----------+-----------------------+----------------------------+----------------------------+-------------------------------------------------------------------------------------------+-------------------+
| 10.244.136.17  |     2882 |  1 | zone1 |     2881 | YES             | ACTIVE | 2026-02-02 11:08:59.033333 | NULL      | NULL                  | 2026-02-02 11:08:57.375211 | 2026-02-02 11:08:59.451973 | 4.5.0.0_100000012025112711-0e8d5ad012baf0953b2032a35a88bdf8886e9a7a(Nov 27 2025 12:05:51) | NULL              |
| 10.244.136.20  |     2882 |  5 | zone2 |     2881 | NO              | ACTIVE | 2026-02-02 11:14:34.573905 | NULL      | NULL                  | 2026-02-02 11:14:21.277460 | 2026-02-02 11:14:36.352947 | 4.5.0.0_100000012025112711-0e8d5ad012baf0953b2032a35a88bdf8886e9a7a(Nov 27 2025 12:05:51) | NULL              |
| 10.244.163.156 |     2882 |  2 | zone2 |     2881 | NO              | ACTIVE | 2026-02-02 11:09:00.359983 | NULL      | NULL                  | 2026-02-02 11:08:57.386734 | 2026-02-02 11:09:01.479965 | 4.5.0.0_100000012025112711-0e8d5ad012baf0953b2032a35a88bdf8886e9a7a(Nov 27 2025 12:05:51) | NULL              |
| 10.244.163.173 |     2882 |  6 | zone3 |     2881 | NO              | ACTIVE | 2026-02-02 11:14:41.671248 | NULL      | NULL                  | 2026-02-02 11:14:28.228735 | 2026-02-02 11:14:44.382554 | 4.5.0.0_100000012025112711-0e8d5ad012baf0953b2032a35a88bdf8886e9a7a(Nov 27 2025 12:05:51) | NULL              |
| 10.244.214.163 |     2882 |  3 | zone3 |     2881 | NO              | ACTIVE | 2026-02-02 11:09:00.191206 | NULL      | NULL                  | 2026-02-02 11:08:57.399387 | 2026-02-02 11:09:01.496507 | 4.5.0.0_100000012025112711-0e8d5ad012baf0953b2032a35a88bdf8886e9a7a(Nov 27 2025 12:05:51) | NULL              |
| 10.244.214.185 |     2882 |  4 | zone1 |     2881 | NO              | ACTIVE | 2026-02-02 11:14:23.593598 | NULL      | NULL                  | 2026-02-02 11:14:12.025023 | 2026-02-02 11:14:26.317897 | 4.5.0.0_100000012025112711-0e8d5ad012baf0953b2032a35a88bdf8886e9a7a(Nov 27 2025 12:05:51) | NULL              |
+----------------+----------+----+-------+----------+-----------------+--------+----------------------------+-----------+-----------------------+----------------------------+----------------------------+-------------------------------------------------------------------------------------------+-------------------+
6 rows in set (0.05 sec)
#6

你这样一共才4个分区体现不出来
表数据了多少

#7

好吧。那么咨询一下：

  1. 分区表需要写多少数据量、建多少个分区才能体现它的优势？
  2. zone1其实有两个svr，我之前配置的primary_zone 应该也没有问题吧