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 是基本可用、柔性状态和最终一致性这三个要素的缩写。
| 本地事物 | 两（三）阶段事务 | 柔性事务 | |
| 业务改造 | 无 | 无 | 实现相关接口 |
| 一致性 | 不支持 | 支持 | 最终一致 |
| 隔离性 | 不支持 | 支持 | 业务方保证 |
| 并发性能 | 无影响 | 严重衰退 | 略微衰退 |
| 适合场景 | 业务方处理不一致 | 短事务 & 低并发 | 长事务 & 高并发 |
- 读写分离，通过负载均衡策略将查询请求疏导至不同从库。
- 架构，平台，系统，数据库版本，灵活多变，通过ShardingSphere可以实现MogDB和openGauss甚至和PostgreSQL在同一个集群中，参与灵活。
