openGauss Cluster Manager RTO Test_jtpccterminal unexpected sqlexception on rollback

目录

一、环境介绍

1. 软件环境

2. 硬件环境

3. 架构图

二、测试场景

1. go程序多IP连接测试RTO

2. BenchmarkSQL多IP连接测试RTO

3. 数据库端观测时间

总结

---

一、环境介绍

1. 软件环境

2. 硬件环境

3. 架构图

二、测试场景

1. go程序多IP连接测试RTO

原理：CM检测主库发生故障，不可访问时会自动选新主，go驱动通过target_session_attrs=read-write控制只连主库，通过SQL
select sysdate,pg_is_in_recovery(); 查询结果时间戳查看RTO

• go程序代码

[root@cloud001-0003 go]# cat 1.go 
// Copyright © 2021 Bin Liu <bin.liu@enmotech.com>
 
package main
 
import (
        "database/sql"
        "fmt"
        _ "gitee.com/opengauss/openGauss-connector-go-pq"
        "log"
        "os"
        "os/signal"
        "syscall"
        "time"
)
 
/*
需要有访问dbe_perf.global_instance_time的权限
CREATE USER dbuser_monitor with login monadmin PASSWORD 'Mon@1234';
grant usage on schema dbe_perf to dbuser_monitor;
grant select on dbe_perf.global_instance_time to dbuser_monitor;
CGO_ENABLED=0 GOOS=linux GOARCH=arm64
*/
 
var (
        dsnExample = `DSN="postgres://gaussdb:secret@foo,bar,baz/mydb?sslmode=disable"
DSN="postgres://gaussdb:secret@foo:1,bar:2,baz:3/mydb?sslmode=disable"
DSN="user=gaussdb password=secret host=foo,bar,baz port=5432 dbname=mydb sslmode=disable"
DSN="user=gaussdb password=secret host=foo,bar,baz port=5432,5432,5433 dbname=mydb sslmode=disable"`
)
 
func main() {
 os.Setenv("DSN", "postgres://gaussdb:Enmo12345@172.16.0.65:26000,172.16.0.202:26000,172.16.0.193:26000/postgres?"+
"sslmode=disable&loggerLevel=debug&target_session_attrs=read-write")
        connStr := os.Getenv("DSN")
        if connStr == "" {
                fmt.Println("please define the env DSN. example:\n" + dsnExample)
                return
        }
        fmt.Println("DNS:", connStr)
        db, err := sql.Open("opengauss", connStr)
        if err != nil {
                log.Fatal(err)
        }
        var (
                newTimer = time.NewTicker(1 * time.Second)
                doClose  = make(chan struct{}, 1)
        )
 
        go func() {
                for {
                        select {
                        case <-newTimer.C:
                                if err := getNodeName(db); err != nil {
                                        fmt.Println(err)
                                }
                        case <-doClose:
                                newTimer.Stop()
                                return
                        }
                }
        }()
 
        sigChan := make(chan os.Signal, 2)
        signal.Notify(sigChan, syscall.SIGTERM, syscall.SIGINT, syscall.SIGKILL) //nolint:staticcheck
        defer signal.Stop(sigChan)
        <-sigChan
        doClose <- struct{}{}
 
}
 
func getNodeName(db *sql.DB) error {
        var err error
        // tx, err := db.Begin()
        // if err != nil {
        //      return err
        // }
        // defer tx.Commit()
        var sysdate string
        var pgIsInRecovery bool
        var nodeName string
        err = db.QueryRow("select sysdate,pg_is_in_recovery();").
                Scan(&sysdate, &pgIsInRecovery)
        if err != nil {
                return err
        }
        var channel string
 
        // err = db.QueryRow("select channel from pg_stat_get_wal_senders() limit 1 ").
        //      Scan(&channel)
        fmt.Println(sysdate, nodeName, pgIsInRecovery, channel)
        // if err != nil {
        //      return err
        // }
        return nil
}
 
 

• 模拟数据库故障

[omm@cloud001-0002 data]$ mv db1/ db1.bak

• go程序连接数据库及重连时间

• 时间差

2022/04/11 16:02:13.614273 connector.go:222:  info dialing server host 172.16.0.65 port 26000 
2022/04/11 16:02:20.683716 connector.go:145: debug find instance host 172.16.0.202 port 26000 
 
RTO时间7s

2. BenchmarkSQL多IP连接测试RTO

原理：CM检测主库发生故障，不可访问时会自动选新主，jdbc驱动通过target_session_type=master控制只连主库，通过SQL
程序执行时间戳查看RTO

BenchmarkSQL模拟负载及重连时间

• 时间差

16:02:13,561 [Thread-8] ERROR  jTPCCTData : Unexpected SQLException in STOCK_LEVELsage: 153MB / 897MB           
16:02:20,834 [Thread-57] FATAL  jTPCCTerminal : Unexpected SQLException on rollback: This connection has been closed.
 
RTO时间7.273s

3. 数据库端观测时间

• 数据库日志

• 时间差

2022-04-11 16:02:13.253 tid=1795056 StartAndStop ERROR: data path disc writable test failed, /opt/mogdb/data/data/db1.          
2022-04-11 16:02:20.438 tid=1815697  LOG: failover msg from cm_server, data_dir :/opt/mogdb/data/data/db1  nodetype is 2
 
RTO时间7.185s

总结

---

• 在有负载情况（tpcc压测产生负载，数据库服务器CPU占用50%左右）进行主库宕机测试，
• 以主库宕机为起始点，备库成功作为新主库启动成功为终止点RTO为7.185s
• 以主库宕机为起始点，模拟TPCC压测的benchmarkSQL程序成功重新连接到新主库为终止点RTO为7.273s
• 以主库宕机为起始点，模拟其它应用连接数据库的go程序成功重新连接到新主库为终止点7s
• 由于go程序至精确到s，猜测实际时间为7.185s以上

综上所述openGauss Cluster Manager RTO约为7s左右

作者：lihongda