梦幻联动-MogDB/openGauss与ShardingSphere在TPC-C上的表现（二）

最新推荐文章于 2024-11-22 12:53:12 发布

转载最新推荐文章于 2024-11-22 12:53:12 发布 · 324 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://developer.aliyun.com/article/1147436

文章标签：

#c语言 #数据库 #postgresql

本文介绍了如何配置ShardingSphere以实现数据库分片，包括主配置文件`server.yaml`和分片配置文件`config-sharding.yaml`的详细内容。接着展示了启动ShardingSphereProxy的步骤，并进行了连接测试。此外，还涉及使用BenchmarkSQL进行性能调试，包括查看配置、生成原始数据和运行TPCC程序的过程。整个过程揭示了ShardingSphere在分布式数据库管理和性能优化上的应用。

3. 修改配置文件

文件过长注释部分已经省略

主配置文件

[root@db1 conf]# cat server.yaml 
rules:
  - !AUTHORITY
    users:
      - root@%:root
      - sharding@:sharding
    provider:
      type: ALL_PRIVILEGES_PERMITTED
props:
  max-connections-size-per-query: 2
  proxy-frontend-flush-threshold: 128  # The default value is 128.
  proxy-backend-query-fetch-size: 1000

users部分为ShardingSphere的账号密码，属于ShardingSphere的对象和数据库对象无关。
分片文件

[root@db1 conf]# cat config-sharding.yaml
schemaName: tpcc
dataSources:
  ds_0:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 3000
    minPoolSize: 1
    password: tpcc@123
    url: jdbc:postgresql://192.168.2.157:26000/tpcc?serverTimezone=UTC&useSSL=false&loggerLevel=OFF
    username: tpcc
  ds_1:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 3000
    minPoolSize: 1
    password: tpcc@123
    url: jdbc:postgresql://192.168.2.158:26000/tpcc?serverTimezone=UTC&useSSL=false&loggerLevel=OFF
    username: tpcc  
rules:
  - !SHARDING
    bindingTables:
      - bmsql_warehouse, bmsql_customer
      - bmsql_stock, bmsql_district, bmsql_order_line
    defaultDatabaseStrategy:
      none:
    defaultTableStrategy:
      none:
    keyGenerators:
      snowflake:
        props:
          worker-id: 123
        type: SNOWFLAKE
    tables:
      bmsql_config:
        actualDataNodes: ds_0.bmsql_config
      bmsql_warehouse:
        actualDataNodes: ds_${0..1}.bmsql_warehouse
        databaseStrategy:
          standard:
            shardingColumn: w_id
            shardingAlgorithmName: bmsql_warehouse_database_inline
      bmsql_district:
        actualDataNodes: ds_${0..1}.bmsql_district
        databaseStrategy:
          standard:
            shardingColumn: d_w_id
            shardingAlgorithmName: bmsql_district_database_inline
      bmsql_customer:
        actualDataNodes: ds_${0..1}.bmsql_customer
        databaseStrategy:
          standard:
            shardingColumn: c_w_id
            shardingAlgorithmName: bmsql_customer_database_inline
      bmsql_item:
        actualDataNodes: ds_${0..1}.bmsql_item
        databaseStrategy:
          standard:
            shardingColumn: i_id
            shardingAlgorithmName: bmsql_item_database_inline
      bmsql_history:
        actualDataNodes: ds_${0..1}.bmsql_history
        databaseStrategy:
          standard:
            shardingColumn: h_w_id
            shardingAlgorithmName: bmsql_history_database_inline
      bmsql_oorder:
        actualDataNodes: ds_${0..1}.bmsql_oorder_${0..1}
        databaseStrategy:
          standard:
            shardingColumn: o_w_id
            shardingAlgorithmName: bmsql_oorder_database_inline
        tableStrategy:
          standard:
            shardingColumn: o_c_id
            shardingAlgorithmName: bmsql_oorder_table_inline
      bmsql_stock:
        actualDataNodes: ds_${0..1}.bmsql_stock
        databaseStrategy:
          standard:
            shardingColumn: s_w_id
            shardingAlgorithmName: bmsql_stock_database_inline
      bmsql_new_order:
        actualDataNodes: ds_${0..1}.bmsql_new_order
        databaseStrategy:
          standard:
            shardingColumn: no_w_id
            shardingAlgorithmName: bmsql_new_order_database_inline
      bmsql_order_line:
        actualDataNodes: ds_${0..1}.bmsql_order_line
        databaseStrategy:
          standard:
            shardingColumn: ol_w_id
            shardingAlgorithmName: bmsql_order_line_database_inline
    shardingAlgorithms:
      bmsql_warehouse_database_inline:
        type: INLINE
        props:
          algorithm-expression: ds_${w_id % 2}
      bmsql_district_database_inline:
        type: INLINE
        props:
          algorithm-expression: ds_${d_w_id % 2}
      bmsql_customer_database_inline:
        type: INLINE
        props:
          algorithm-expression: ds_${c_w_id % 2}
      bmsql_item_database_inline:
        type: INLINE
        props:
          algorithm-expression: ds_${i_id % 2}
      bmsql_history_database_inline:
        type: INLINE
        props:
          algorithm-expression: ds_${h_w_id % 2}
      bmsql_oorder_database_inline:
        type: INLINE
        props:
          algorithm-expression: ds_${o_w_id % 2}
      bmsql_oorder_table_inline:
        type: INLINE
        props:
          algorithm-expression: bmsql_oorder_${o_c_id % 2}
      bmsql_stock_database_inline:
        type: INLINE
        props:
          algorithm-expression: ds_${s_w_id % 2}
      bmsql_new_order_database_inline:
        type: INLINE
        props:
          algorithm-expression: ds_${no_w_id % 2}
      bmsql_order_line_database_inline:
        type: INLINE
        props:
          algorithm-expression: ds_${ol_w_id % 2}

schemaName 为数据库用户名
dataSources为数据源可配置1至多个
rules为分片规则，%2为取id列分成2份到两个数据库。

4. 启动proxy

[root@db1 bin]# pwd
/lee/ss/proxy/bin
[root@db1 bin]# ./start.sh 3307
Starting the ShardingSphere-Proxy ...
The classpath is /lee/ss/proxy/conf:.:/lee/ss/proxy/lib/*:/lee/ss/proxy/ext-lib/*
Please check the STDOUT file: /lee/ss/proxy/logs/stdout.log
[root@db1 bin]# cat /lee/ss/proxy/logs/stdout.log
Thanks for using Atomikos! Evaluate http://www.atomikos.com/Main/ExtremeTransactions for advanced features and professional support
or register at http://www.atomikos.com/Main/RegisterYourDownload to disable this message and receive FREE tips & advice
[INFO ] 2021-11-01 15:53:05.643 [main] o.a.s.p.i.BootstrapInitializer - Database name is `PostgreSQL`, version is `9.2.4`
[INFO ] 2021-11-01 15:53:05.837 [main] o.a.s.p.frontend.ShardingSphereProxy - ShardingSphere-Proxy start success

可以在脚本后指定proxy的启动端口

5. 测试连接

[lee@node157 ~]$ gsql -d tpcc -Usharding -h 192.168.2.136 -p3307 -Wsharding
gsql ((MogDB 2.1.0 build ) compiled at 2021-10-26 19:07:06 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
tpcc=>

六、调试BenchmarkSQL

1. 查看配置文件

[root@db1 run]# cat props.mogdb.ss 
db=postgres
driver=org.postgresql.Driver
conn=jdbc:postgresql://192.168.2.136:3307/tpcc?prepareThreshold=1&batchMode=on&fetchsize=10&loggerLevel=off
user=sharding
password=sharding
warehouses=100
loadWorkers=50
terminals=500
runTxnsPerTerminal=0
runMins=10
limitTxnsPerMin=0
terminalWarehouseFixed=true
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
resultDirectory=ss_result_%tY-%tm-%td_%tH%tM%tS

2. 生成原始数据

[root@db1 run]# ./runDatabaseBuild.sh props.mogdb.ss 
部分日志
# ------------------------------------------------------------
# Loading SQL file ./sql.postgres/buildFinish.sql
------------------------------------------------------------
-- ----
-- Extra commands to run after the tables are created, loaded,
-- indexes built and extra's created.
-- PostgreSQL version.
-- ----

3. 运行TPCC程序

[root@db1 data]# numactl -C 0-25,30-55 ./runBenchmark.sh props.mogdb.ss 
13:55:30,137 [main] INFO   jTPCC : Term-00, 
13:55:30,140 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
13:55:30,140 [main] INFO   jTPCC : Term-00,      BenchmarkSQL v5.0
13:55:30,140 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
13:55:30,140 [main] INFO   jTPCC : Term-00,  (c) 2003, Raul Barbosa
13:55:30,140 [main] INFO   jTPCC : Term-00,  (c) 2004-2016, Denis Lussier
13:55:30,142 [main] INFO   jTPCC : Term-00,  (c) 2016, Jan Wieck
13:55:30,142 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
13:55:30,142 [main] INFO   jTPCC : Term-00, 
13:55:30,142 [main] INFO   jTPCC : Term-00, db=postgres
13:55:30,142 [main] INFO   jTPCC : Term-00, driver=org.postgresql.Driver
13:55:30,143 [main] INFO   jTPCC : Term-00, conn=jdbc:postgresql://192.168.2.136:3307/tpcc?prepareThreshold=1&batchMode=on&fetchsize=10&loggerLevel=off
13:55:30,143 [main] INFO   jTPCC : Term-00, user=sharding
13:55:30,143 [main] INFO   jTPCC : Term-00, 
13:55:30,143 [main] INFO   jTPCC : Term-00, warehouses=100
13:55:30,143 [main] INFO   jTPCC : Term-00, terminals=500
13:55:30,144 [main] INFO   jTPCC : Term-00, runMins=10
13:55:30,144 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=0
13:55:30,145 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=true
13:55:30,145 [main] INFO   jTPCC : Term-00, 
13:55:30,145 [main] INFO   jTPCC : Term-00, newOrderWeight=45
13:55:30,145 [main] INFO   jTPCC : Term-00, paymentWeight=43
13:55:30,145 [main] INFO   jTPCC : Term-00, orderStatusWeight=4
13:55:30,145 [main] INFO   jTPCC : Term-00, deliveryWeight=4
13:55:30,145 [main] INFO   jTPCC : Term-00, stockLevelWeight=4
13:55:30,145 [main] INFO   jTPCC : Term-00, 
13:55:30,145 [main] INFO   jTPCC : Term-00, resultDirectory=ss_result_%tY-%tm-%td_%tH%tM%tS
13:55:30,145 [main] INFO   jTPCC : Term-00, osCollectorScript=null
13:55:30,145 [main] INFO   jTPCC : Term-00, 
13:55:30,157 [main] INFO   jTPCC : Term-00, copied props.mogdb.ss to ss_result_2021-11-01_135530/run.properties
13:55:30,157 [main] INFO   jTPCC : Term-00, created ss_result_2021-11-01_135530/data/runInfo.csv for runID 835
13:55:30,157 [main] INFO   jTPCC : Term-00, writing per transaction results to ss_result_2021-11-01_135530/data/result.csv
13:55:30,158 [main] INFO   jTPCC : Term-00,
13:55:30,237 [main] INFO   jTPCC : Term-00, C value for C_LAST during load: 165
13:55:30,237 [main] INFO   jTPCC : Term-00, C value for C_LAST this run:    92
13:55:30,237 [main] INFO   jTPCC : Term-00, Running Average tpmTOTAL: 626491.25    Current tpmTOTAL: 41415216    Memory Usage: 858MB / 1001MB          
14:05:31,197 [Thread-158] INFO   jTPCC : Term-00, 
14:05:31,197 [Thread-158] INFO   jTPCC : Term-00, 
14:05:31,197 [Thread-158] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 281818.72
14:05:31,198 [Thread-158] INFO   jTPCC : Term-00, Measured tpmTOTAL = 626481.78
14:05:31,198 [Thread-158] INFO   jTPCC : Term-00, Session Start     = 2021-11-01 13:55:31
14:05:31,198 [Thread-158] INFO   jTPCC : Term-00, Session End       = 2021-11-01 14:05:31
14:05:31,198 [Thread-158] INFO   jTPCC : Term-00, Transaction Count = 6265412
14:05:31,198 [Thread-158] INFO   jTPCC : executeTime[Payment]=87346178
14:05:31,198 [Thread-158] INFO   jTPCC : executeTime[Order-Status]=3979084
14:05:31,198 [Thread-158] INFO   jTPCC : executeTime[Delivery]=24407579
14:05:31,198 [Thread-158] INFO   jTPCC : executeTime[Stock-Level]=3583178
14:05:31,198 [Thread-158] INFO   jTPCC : executeTime[New-Order]=180651188

数据未经过充分调优仅供参考

七、何谓梦幻

单一数据库整合成一个大型分布式，即兼顾了单机数据库的稳定，又产生了分布式数据库的能力。
兼容多种数据库，目前支持 MySQL, PostgreSQL, SQLServer, Oracle, openGauss 以及符合 SQL92 规范的 SQL 方言。
面对超负荷的流量或其他不正常状态下，针对某一节点进行熔断和限流，实现从数据库到计算节点打通的一体化管理能力，在故障中为组件提供细粒度的控制能力，并尽可能的提供自愈的可能。
透明化分库分表，尽量对业务无感知，像使用一个数据库一样使用水平分片之后的数据库集群。
支持多种事物粒度选择

本地事物:在不开启任何分布式事务管理器的前提下，让每个数据节点各自管理自己的事务。

两阶段提交:基于XA协议实现的分布式事务对业务侵入很小。它最大的优势就是对使用方透明，用户可以像使用本地事务一样使用基于XA协议的分布式事务。 XA协议能够严格保障事务 ACID 特性。

柔性事务: 如果将实现了 ACID 的事务要素的事务称为刚性事务的话，那么基于 BASE 事务要素的事务则称为柔性事务。 BASE 是基本可用、柔性状态和最终一致性这三个要素的缩写。

	本地事物	两（三）阶段事务	柔性事务
业务改造	无	无	实现相关接口
一致性	不支持	支持	最终一致
隔离性	不支持	支持	业务方保证
并发性能	无影响	严重衰退	略微衰退
适合场景	业务方处理不一致	短事务 & 低并发	长事务 & 高并发