search

MyCATApache / Mycat2

MySQL Proxy using Java NIO based on Sharding SQL,Calcite ,simple and fast
http://www.mycat.org.cn/
GNU General Public License v3.0
1.71k stars 478 forks source link

TPC-C for Mycat2 "java.sql.SQLException: No database selected" #652

Open wsm12138 opened 2 years ago

wsm12138 commented 2 years ago

BenchmarkSQL props.mycat

db=postgres
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://127.0.0.1:8066/tpcc_mycat?serverTimezone=UTC&useSSL=false&                                                                            cachePrepStmts=true&prepStmtCacheSize=8000
user=root
password=123456

warehouses=20

loadWorkers=21

terminals=20

//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=2
//Number of total transactions per minute
limitTxnsPerMin=0

//Set to true to run in 4.x compatible mode. Set to false to use the
//entire configured database evenly.
terminalWarehouseFixed=false

//The following five values must add up to 100
//The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4

// Directory name to create for collecting detailed result data.
// Comment this out to suppress.
resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
//osCollectorScript=./misc/os_collector_linux.py
//osCollectorInterval=1
//osCollectorSSHAddr=user@dbhost
//osCollectorDevices=net_eth0 blk_sda

Mycat2 DB & Table

image

Mycat2 Error log

image

如上配置 BenchmarkSQL 是可以跑通 MySQL 的。 所以想知道是什么原因导致报错，如何快速修复。 Best wishes！

junwen12221 commented 2 years ago

可能mycat中的数据源的url没有写物理库

wsm12138 commented 2 years ago

关于 Mycat2 中的数据源配置，是参考官网 https://www.yuque.com/books/share/6606b3b6-3365-4187-94c4-e51116894695/fb2285b811138a442eb850f0127d7ea3 使用注释动态配置 Mycat2 的

如下为配置信息，没有看出有什么问题😂

目录结构

.
├── clusters
│   ├── c0.cluster.json
│   ├── c1.cluster.json
│   └── prototype.cluster.json
├── datasources
│   ├── dr0.datasource.json
│   ├── dr1.datasource.json
│   ├── dw0.datasource.json
│   ├── dw1.datasource.json
│   └── prototypeDs.datasource.json
├── dbseq.sql
├── logback.xml
├── mycat.lock
├── schemas
│   ├── mycat.schema.json
│   ├── mysql.schema.json
│   └── tpcc_mycat.schema.json
├── sequences
├── server.json
├── server.json.bak
├── simplelogger.properties
├── sql
│   ├── db1.sql
│   ├── db2.sql
│   ├── db3.sql
│   ├── describe_testdb_address.sql
│   ├── describe_testdb_travelrecord.sql
│   ├── show_databases.sql
│   ├── show_full_tables_from_testdb2.sql
│   └── show_full_tables_from_testdb.sql
├── sqlcaches
├── state.json
├── users
│   └── root.user.json
├── version.txt
├── wrapper.conf

datasource/dr0.datasource.json

{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"READ_WRITE",
        "maxCon":1000,
        "maxConnectTimeout":30000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"dr0",
        "password":"test",
        "queryTimeout":0,
        "type":"JDBC",
        "url":"jdbc:mysql://127.0.0.1:3306?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
        "user":"test",
        "weight":0

您这的建议是否是将 jdbc:mysql://127.0.0.1:3306 转换为 jdbc:mysql://127.0.0.1:3306/someDB 呢？ @junwen12221

junwen12221 commented 2 years ago

关于 Mycat2 中的数据源配置，是参考官网 https://www.yuque.com/books/share/6606b3b6-3365-4187-94c4-e51116894695/fb2285b811138a442eb850f0127d7ea3 使用注释动态配置 Mycat2 的

如下为配置信息，没有看出有什么问题😂

目录结构

.
├── clusters
│   ├── c0.cluster.json
│   ├── c1.cluster.json
│   └── prototype.cluster.json
├── datasources
│   ├── dr0.datasource.json
│   ├── dr1.datasource.json
│   ├── dw0.datasource.json
│   ├── dw1.datasource.json
│   └── prototypeDs.datasource.json
├── dbseq.sql
├── logback.xml
├── mycat.lock
├── schemas
│   ├── mycat.schema.json
│   ├── mysql.schema.json
│   └── tpcc_mycat.schema.json
├── sequences
├── server.json
├── server.json.bak
├── simplelogger.properties
├── sql
│   ├── db1.sql
│   ├── db2.sql
│   ├── db3.sql
│   ├── describe_testdb_address.sql
│   ├── describe_testdb_travelrecord.sql
│   ├── show_databases.sql
│   ├── show_full_tables_from_testdb2.sql
│   └── show_full_tables_from_testdb.sql
├── sqlcaches
├── state.json
├── users
│   └── root.user.json
├── version.txt
├── wrapper.conf

datasource/dr0.datasource.json

{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"READ_WRITE",
        "maxCon":1000,
        "maxConnectTimeout":30000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"dr0",
        "password":"test",
        "queryTimeout":0,
        "type":"JDBC",
        "url":"jdbc:mysql://127.0.0.1:3306?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
        "user":"test",
        "weight":0

您这的建议是否是将 jdbc:mysql://127.0.0.1:3306 转换为 jdbc:mysql://127.0.0.1:3306/someDB 呢？ @junwen12221

是的

wsm12138 commented 2 years ago

已解决，感谢~

wsm12138 commented 2 years ago

您好，在调研 Mycat2 的期间做了 1,000 warehouse 的 TPC-C 实验。 遇到以下报错。

Mycat2 版本

GitVersion

4cf6b658c3f5a4e2d0402de5f8f8fb682fb30455

wrapper.log

INFO   | jvm 1    | 2022/04/19 15:37:52 | 2022-04-19 15:37:52,861[INFO]io.mycat.vertx.VertxMycatServer.lambda$addSession$0:148session:io.mycat.mycatmysql.MycatVertxMysqlSession@254d065c is closing
INFO   | jvm 1    | 2022/04/19 15:37:52 | 2022-04-19 15:37:52,862[INFO]io.mycat.vertx.VertxMycatServer.lambda$addSession$0:148session:io.mycat.mycatmysql.MycatVertxMysqlSession@2c02db88 is closing
INFO   | jvm 1    | 2022/04/19 15:37:52 | 2022-04-19 15:37:52,869[INFO]io.mycat.vertx.VertxMycatServer.lambda$addSession$0:148session:io.mycat.mycatmysql.MycatVertxMysqlSession@2adb17c7 is closing
INFO   | jvm 1    | 2022/04/19 15:37:52 | 2022-04-19 15:37:52,870[INFO]io.mycat.vertx.VertxMycatServer.lambda$addSession$0:148session:io.mycat.mycatmysql.MycatVertxMysqlSession@3e80df1a is closing
INFO   | jvm 1    | 2022/04/19 15:37:52 | 2022-04-19 15:37:52,883[INFO]io.mycat.vertx.VertxMycatServer.lambda$addSession$0:148session:io.mycat.mycatmysql.MycatVertxMysqlSession@660040ee is closing
INFO   | jvm 1    | 2022/04/19 15:37:52 | 2022-04-19 15:37:52,893[INFO]io.mycat.vertx.VertxMycatServer.lambda$addSession$0:148session:io.mycat.mycatmysql.MycatVertxMysqlSession@184b1fae is closing
INFO   | jvm 1    | 2022/04/19 15:37:52 | 2022-04-19 15:37:52,899[INFO]io.mycat.vertx.VertxMycatServer.lambda$addSession$0:148session:io.mycat.mycatmysql.MycatVertxMysqlSession@86a3a36 is closing
INFO   | jvm 1    | 2022/04/19 15:37:52 | 2022-04-19 15:37:52,936[INFO]io.mycat.vertx.VertxMycatServer.lambda$addSession$0:148session:io.mycat.mycatmysql.MycatVertxMysqlSession@20a1299f is closing
INFO   | jvm 1    | 2022/04/19 15:37:53 | 2022-04-19 15:37:53,087[INFO]io.mycat.vertx.VertxMycatServer.lambda$addSession$0:148session:io.mycat.mycatmysql.MycatVertxMysqlSession@77c42fac is closing
INFO   | jvm 1    | 2022/04/19 15:37:56 | 2022-04-19 15:37:56,440[ERROR]io.mycat.newquery.NewMycatConnectionImpl.lambda$update$5:394
INFO   | jvm 1    | 2022/04/19 15:37:56 | com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
INFO   | jvm 1    | 2022/04/19 15:37:56 |       at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
INFO   | jvm 1    | 2022/04/19 15:37:56 |       at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
INFO   | jvm 1    | 2022/04/19 15:37:56 |       at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
INFO   | jvm 1    | 2022/04/19 15:37:56 |       at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
INFO   | jvm 1    | 2022/04/19 15:37:56 |       at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
INFO   | jvm 1    | 2022/04/19 15:37:56 |       at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1348)
INFO   | jvm 1    | 2022/04/19 15:37:56 |       at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)
INFO   | jvm 1    | 2022/04/19 15:37:56 |       at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeUpdate(DruidPooledPreparedStatement.java:255)
INFO   | jvm 1    | 2022/04/19 15:37:56 |       at io.mycat.newquery.NewMycatConnectionImpl.lambda$update$5(NewMycatConnectionImpl.java:384)
INFO   | jvm 1    | 2022/04/19 15:37:56 |       at io.vertx.core.impl.future.Transformation.onSuccess(Transformation.java:39)
INFO   | jvm 1    | 2022/04/19 15:37:56 |       at io.vertx.core.impl.future.FutureBase.emitSuccess(FutureBase.java:60)
INFO   | jvm 1    | 2022/04/19 15:37:56 |       at io.vertx.core.impl.future.SucceededFuture.addListener(SucceededFuture.java:88)
INFO   | jvm 1    | 2022/04/19 15:37:56 |       at io.vertx.core.impl.future.FutureBase.transform(FutureBase.java:92)
INFO   | jvm 1    | 2022/04/19 15:37:56 |       at io.vertx.core.impl.future.SucceededFuture.transform(SucceededFuture.java:27)
INFO   | jvm 1    | 2022/04/19 15:37:56 |       at io.mycat.newquery.NewMycatConnectionImpl.update(NewMycatConnectionImpl.java:363)
INFO   | jvm 1    | 2022/04/19 15:37:56 |       at io.mycat.monitor.ThreadMycatConnectionImplWrapper.lambda$update$5(ThreadMycatConnectionImplWrapper.java:119)
INFO   | jvm 1    | 2022/04/19 15:37:56 |       at io.mycat.IOExecutor$1.lambda$executeBlocking$0(IOExecutor.java:75)
INFO   | jvm 1    | 2022/04/19 15:37:56 |       at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
INFO   | jvm 1    | 2022/04/19 15:37:56 |       at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
INFO   | jvm 1    | 2022/04/19 15:37:56 |       at java.lang.Thread.run(Thread.java:748)
INFO   | jvm 1    | 2022/04/19 15:37:56 | 2022-04-19 15:37:56,503[INFO]io.mycat.vertx.VertxMycatServer.lambda$addSession$0:148session:io.mycat.mycatmysql.MycatVertxMysqlSession@3af6b8ee is closing
INFO   | jvm 1    | 2022/04/19 15:37:58 | 2022-04-19 15:37:58,417[ERROR]io.mycat.newquery.NewMycatConnectionImpl.lambda$update$5:394
INFO   | jvm 1    | 2022/04/19 15:37:58 | com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
INFO   | jvm 1    | 2022/04/19 15:37:58 |       at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
INFO   | jvm 1    | 2022/04/19 15:37:58 |       at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
INFO   | jvm 1    | 2022/04/19 15:37:58 |       at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
INFO   | jvm 1    | 2022/04/19 15:37:58 |       at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
INFO   | jvm 1    | 2022/04/19 15:37:58 |       at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
INFO   | jvm 1    | 2022/04/19 15:37:58 |       at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1348)
INFO   | jvm 1    | 2022/04/19 15:37:58 |       at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)
INFO   | jvm 1    | 2022/04/19 15:37:58 |       at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeUpdate(DruidPooledPreparedStatement.java:255)
INFO   | jvm 1    | 2022/04/19 15:37:58 |       at io.mycat.newquery.NewMycatConnectionImpl.lambda$update$5(NewMycatConnectionImpl.java:384)
INFO   | jvm 1    | 2022/04/19 15:37:58 |       at io.vertx.core.impl.future.Transformation.onSuccess(Transformation.java:39)
INFO   | jvm 1    | 2022/04/19 15:37:58 |       at io.vertx.core.impl.future.FutureBase.emitSuccess(FutureBase.java:60)

BenchmarkSQL 报错

15:36:07,898 [main] INFO   jTPCC : Term-00,
15:36:07,900 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
15:36:07,900 [main] INFO   jTPCC : Term-00,      BenchmarkSQL v5.0
15:36:07,900 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
15:36:07,900 [main] INFO   jTPCC : Term-00,  (c) 2003, Raul Barbosa
15:36:07,900 [main] INFO   jTPCC : Term-00,  (c) 2004-2016, Denis Lussier
15:36:07,902 [main] INFO   jTPCC : Term-00,  (c) 2016, Jan Wieck
15:36:07,902 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
15:36:07,902 [main] INFO   jTPCC : Term-00,
15:36:07,903 [main] INFO   jTPCC : Term-00, db=postgres
15:36:07,903 [main] INFO   jTPCC : Term-00, driver=com.mysql.jdbc.Driver
15:36:07,903 [main] INFO   jTPCC : Term-00, conn=jdbc:mysql://IP:8066/tpcc1?serverTimezone=UTC&useSSL=false&cachePrepStmts=true&prepStmtCacheSize=8000
15:36:07,903 [main] INFO   jTPCC : Term-00, user=root
15:36:07,903 [main] INFO   jTPCC : Term-00,
15:36:07,903 [main] INFO   jTPCC : Term-00, warehouses=1000
15:36:07,903 [main] INFO   jTPCC : Term-00, terminals=500
15:36:07,904 [main] INFO   jTPCC : Term-00, runMins=1
15:36:07,905 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=0
15:36:07,905 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=false
15:36:07,905 [main] INFO   jTPCC : Term-00,
15:36:07,905 [main] INFO   jTPCC : Term-00, newOrderWeight=45
15:36:07,905 [main] INFO   jTPCC : Term-00, paymentWeight=43
15:36:07,905 [main] INFO   jTPCC : Term-00, orderStatusWeight=4
15:36:07,905 [main] INFO   jTPCC : Term-00, deliveryWeight=4
15:36:07,905 [main] INFO   jTPCC : Term-00, stockLevelWeight=4
15:36:07,905 [main] INFO   jTPCC : Term-00,
15:36:07,905 [main] INFO   jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
15:36:07,905 [main] INFO   jTPCC : Term-00, osCollectorScript=null
15:36:07,906 [main] INFO   jTPCC : Term-00,
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
15:36:07,970 [main] INFO   jTPCC : Term-00, copied props.mycat to my_result_2022-04-19_153607/run.properties
15:36:07,970 [main] INFO   jTPCC : Term-00, created my_result_2022-04-19_153607/data/runInfo.csv for runID 41
15:36:07,970 [main] INFO   jTPCC : Term-00, writing per transaction results to my_result_2022-04-19_153607/data/result.csv
15:36:07,970 [main] INFO   jTPCC : Term-00,
15:36:09,663 [main] INFO   jTPCC : Term-00, C value for C_LAST during load: 154
15:36:09,663 [main] INFO   jTPCC : Term-00, C value for C_LAST this run:    239
15:36:09,663 [main] INFO   jTPCC : Term-00,                                                           15:37:15,862 [Thread-469] ERROR  jTPCCTData : Unexpected SQLException in DELIVERY_BGsage: 228MB / 1766MB
15:37:15,863 [Thread-469] ERROR  jTPCCTData : Lock wait timeout exceeded; try restarting transaction
com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1348)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)
        at jTPCCTData.executeDeliveryBG(jTPCCTData.java:1623)
        at jTPCCTData.execute(jTPCCTData.java:115)
        at jTPCCTerminal.executeTransactions(jTPCCTerminal.java:237)
        at jTPCCTerminal.run(jTPCCTerminal.java:88)
        at java.lang.Thread.run(Thread.java:748)                                                                                                                    15:37:17,834 [Thread-294] ERROR  jTPCCTData : Unexpected SQLException in DELIVERY_BGsage: 406MB / 1706MB
15:37:17,835 [Thread-294] ERROR  jTPCCTData : Lock wait timeout exceeded; try restarting transaction
com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1348)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)
        at jTPCCTData.executeDeliveryBG(jTPCCTData.java:1623)
        at jTPCCTData.execute(jTPCCTData.java:115)
        at jTPCCTerminal.executeTransactions(jTPCCTerminal.java:237)
        at jTPCCTerminal.run(jTPCCTerminal.java:88)
        at java.lang.Thread.run(Thread.java:748)
wsm12138 commented 2 years ago

我做了以下尝试：

  1. 确认增加链接数，确保 配置的数据源最大链接 > 压测并发数
  2. 增大分配给 Mycat2 的资源
  3. 降低日志记录等级以提升性能
  4. 事务模型由默认的 XA 该我 Proxy

结果依然报上面同样的错误。 以下为 Mycat2 部分配置

conf/wrapper.conf

wrapper.java.additional.5=-Dcom.sun.management.jmxremote.port=1984
wrapper.java.additional.6=-Dcom.sun.management.jmxremote.authenticate=false
wrapper.java.additional.7=-Dcom.sun.management.jmxremote.ssl=false
wrapper.java.additional.8=-Xmx16G
wrapper.java.additional.9=-Xms16G
wrapper.java.initmemory=256M
wrapper.java.maxmemory=8192M
wrapper.app.parameter.1=io.mycat.MycatCore
wrapper.app.parameter.2=start
wrapper.console.format=PM
wrapper.console.loglevel=INFO
wrapper.logfile=logs/wrapper.log
wrapper.logfile.format=LPTM
wrapper.logfile.loglevel=INFO
wrapper.logfile.maxsize=512m
wrapper.logfile.maxfiles=30
wrapper.syslog.loglevel=NONE
wrapper.console.title=mycat2
wrapper.ntservice.name=mycat
wrapper.ntservice.displayname=mycat2
wrapper.ntservice.description=The project of Mycat2
wrapper.ntservice.dependency.1=
wrapper.ntservice.starttype=AUTO_START
wrapper.ntservice.interactive=false
wrapper.ping.timeout=120

clusters/prototype.cluster.json

{
        "clusterType":"MASTER_SLAVE",
        "heartbeat":{
                "heartbeatTimeout":1000,
                "maxRetry":3,
                "minSwitchTimeInterval":300,
                "slaveThreshold":0
        },
        "masters":[
                "prototypeDs"
        ],
        "maxCon":5000,
        "name":"prototype",
        "readBalanceType":"BALANCE_ALL",
        "switchType":"SWITCH"
}

clusters/c0.cluster.json

{
        "clusterType":"MASTER_SLAVE",
        "heartbeat":{
                "heartbeatTimeout":1000,
                "maxRetryCount":3,
                "minSwitchTimeInterval":300,
                "showLog":false,
                "slaveThreshold":0.0
        },
        "masters":[
                "dw0"
        ],
        "maxCon":2000,
        "name":"c0",
        "readBalanceType":"BALANCE_ALL",
        "replicas":[],
        "switchType":"SWITCH"
}

datasources/dw0.datasource.json

{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"READ_WRITE",
        "maxCon":1000,
        "maxConnectTimeout":30000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"dw0",
        "password":"passwd",
        "queryTimeout":0,
        "type":"JDBC",
        "url":"jdbc:mysql://IP:3306/tpcc1?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
        "user":"root",
        "weight":0
}

datasources/prototypeDs.datasource.json

{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"READ_WRITE",
        "maxCon":20000,
        "maxConnectTimeout":3000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"prototypeDs",
        "password":"passwd",
        "type":"JDBC",
        "url":"jdbc:mysql://IP:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
        "user":"root",
        "weight":0
}

server.json

{
  "loadBalance":{
    "defaultLoadBalance":"BalanceRandom",
    "loadBalances":[]
  },
  "mode":"local",
  "properties":{},
  "server":{
    "bufferPool":{

    },
    "idleTimer":{
      "initialDelay":3,
      "period":60000,
      "timeUnit":"SECONDS"
    },
    "ip":"0.0.0.0",
    "mycatId":1,
    "port":8066,
    "reactorNumber":8,
    "tempDirectory":null,
    "timeWorkerPool":{
      "corePoolSize":0,
      "keepAliveTime":1,
      "maxPendingLimit":65535,
      "maxPoolSize":2000,
      "taskTimeout":5,
      "timeUnit":"MINUTES"
    },
    "workerPool":{
      "corePoolSize":1,
      "keepAliveTime":1,
      "maxPendingLimit":65535,
      "maxPoolSize":2000,
      "taskTimeout":5,
      "timeUnit":"MINUTES"
    }
  }
}

users/root.user.json

{
        "dialect":"mysql",
        "ip":null,
        "password":"123456",
        "transactionType":"proxy",
        "username":"root"
}
zwyqz commented 2 years ago

这个报错是属于数据库锁超时； 运行测试程序时候，查查看数据库的锁；

wsm12138 commented 2 years ago

@zwyqz 您好， 又试了几次，Mycat 后面直接接了一个 数据库。表未进行分表。 同样的测试，测试 MySQL 不会出现问题。 压测程序及 DB 中间接入 Mycat2 后，每次必现报错且无法生成最终结果，怀疑是否是 多线程并发资源争用导致产生了锁，单线程没有问题。

压测 MySQL 结果 500 并发

16:04:00,095 [main] INFO   jTPCC : Term-00,      BenchmarkSQL v5.0
16:04:00,095 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
16:04:00,095 [main] INFO   jTPCC : Term-00,  (c) 2003, Raul Barbosa
16:04:00,095 [main] INFO   jTPCC : Term-00,  (c) 2004-2016, Denis Lussier
16:04:00,097 [main] INFO   jTPCC : Term-00,  (c) 2016, Jan Wieck
16:04:00,097 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
16:04:00,097 [main] INFO   jTPCC : Term-00,
16:04:00,097 [main] INFO   jTPCC : Term-00, db=postgres
16:04:00,097 [main] INFO   jTPCC : Term-00, driver=com.mysql.jdbc.Driver
16:04:00,097 [main] INFO   jTPCC : Term-00, conn=jdbc:mysql://IP:3306/tpcc1?serverTimezone=UTC&useSSL=false&cachePrepStmts=true&prepStmtCacheSiPublicKeyRetrieval=true
16:04:00,097 [main] INFO   jTPCC : Term-00, user=root
16:04:00,097 [main] INFO   jTPCC : Term-00,
16:04:00,098 [main] INFO   jTPCC : Term-00, warehouses=1000
16:04:00,098 [main] INFO   jTPCC : Term-00, terminals=500
16:04:00,099 [main] INFO   jTPCC : Term-00, runMins=1
16:04:00,099 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=0
16:04:00,099 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=false
16:04:00,099 [main] INFO   jTPCC : Term-00,
16:04:00,099 [main] INFO   jTPCC : Term-00, newOrderWeight=45
16:04:00,099 [main] INFO   jTPCC : Term-00, paymentWeight=43
16:04:00,099 [main] INFO   jTPCC : Term-00, orderStatusWeight=4
16:04:00,100 [main] INFO   jTPCC : Term-00, deliveryWeight=4
16:04:00,100 [main] INFO   jTPCC : Term-00, stockLevelWeight=4
16:04:00,100 [main] INFO   jTPCC : Term-00,
16:04:00,100 [main] INFO   jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
16:04:00,100 [main] INFO   jTPCC : Term-00, osCollectorScript=null
16:04:00,100 [main] INFO   jTPCC : Term-00,
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered vi manual loading of the driver class is generally unnecessary.
16:04:00,164 [main] INFO   jTPCC : Term-00, copied props.mysql to my_result_2022-04-19_160400/run.properties
16:04:00,164 [main] INFO   jTPCC : Term-00, created my_result_2022-04-19_160400/data/runInfo.csv for runID 47
16:04:00,164 [main] INFO   jTPCC : Term-00, writing per transaction results to my_result_2022-04-19_160400/data/result.csv
16:04:00,164 [main] INFO   jTPCC : Term-00,
16:04:00,426 [main] INFO   jTPCC : Term-00, C value for C_LAST during load: 154
16:04:00,427 [main] INFO   jTPCC : Term-00, C value for C_LAST this run:    40
16:04:00,427 [main] INFO   jTPCC : Term-00,                                                             Term-00, Running Average tpmTOTAL: 219620.67    16:05:01,552 [Thread-177] INFO   jTPCC : Term-00,
16:05:01,552 [Thread-177] INFO   jTPCC : Term-00,
16:05:01,552 [Thread-177] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 99120.14
16:05:01,552 [Thread-177] INFO   jTPCC : Term-00, Measured tpmTOTAL = 219835.21
16:05:01,552 [Thread-177] INFO   jTPCC : Term-00, Session Start     = 2022-04-19 16:04:01
16:05:01,552 [Thread-177] INFO   jTPCC : Term-00, Session End       = 2022-04-19 16:05:01
16:05:01,552 [Thread-177] INFO   jTPCC : Term-00, Transaction Count = 220120

压测 Mycat2 结果 1 线程

16:21:01,132 [main] INFO   jTPCC : Term-00,
16:21:01,134 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
16:21:01,134 [main] INFO   jTPCC : Term-00,      BenchmarkSQL v5.0
16:21:01,134 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
16:21:01,134 [main] INFO   jTPCC : Term-00,  (c) 2003, Raul Barbosa
16:21:01,134 [main] INFO   jTPCC : Term-00,  (c) 2004-2016, Denis Lussier
16:21:01,136 [main] INFO   jTPCC : Term-00,  (c) 2016, Jan Wieck
16:21:01,136 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
16:21:01,136 [main] INFO   jTPCC : Term-00,
16:21:01,136 [main] INFO   jTPCC : Term-00, db=postgres
16:21:01,137 [main] INFO   jTPCC : Term-00, driver=com.mysql.jdbc.Driver
16:21:01,137 [main] INFO   jTPCC : Term-00, conn=jdbc:mysql://IP:8066/tpcc1?serverTimezone=UTC&useSSL=false&cachePrepStmts=true&prepStmtCacheSize=8000
16:21:01,137 [main] INFO   jTPCC : Term-00, user=root
16:21:01,137 [main] INFO   jTPCC : Term-00,
16:21:01,137 [main] INFO   jTPCC : Term-00, warehouses=1000
16:21:01,137 [main] INFO   jTPCC : Term-00, terminals=1
16:21:01,138 [main] INFO   jTPCC : Term-00, runMins=1
16:21:01,139 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=0
16:21:01,139 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=false
16:21:01,139 [main] INFO   jTPCC : Term-00,
16:21:01,139 [main] INFO   jTPCC : Term-00, newOrderWeight=45
16:21:01,139 [main] INFO   jTPCC : Term-00, paymentWeight=43
16:21:01,139 [main] INFO   jTPCC : Term-00, orderStatusWeight=4
16:21:01,139 [main] INFO   jTPCC : Term-00, deliveryWeight=4
16:21:01,139 [main] INFO   jTPCC : Term-00,  @stockLevelWeight=4
16:21:01,139 [main] INFO   jTPCC : Term-00,
16:21:01,139 [main] INFO   jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
16:21:01,139 [main] INFO   jTPCC : Term-00, osCollectorScript=null
16:21:01,139 [main] INFO   jTPCC : Term-00,
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
16:21:01,204 [main] INFO   jTPCC : Term-00, copied props.mycat to my_result_2022-04-19_162101/run.properties
16:21:01,204 [main] INFO   jTPCC : Term-00, created my_result_2022-04-19_162101/data/runInfo.csv for runID 51
16:21:01,204 [main] INFO   jTPCC : Term-00, writing per transaction results to my_result_2022-04-19_162101/data/result.csv
16:21:01,204 [main] INFO   jTPCC : Term-00,
16:21:02,894 [main] INFO   jTPCC : Term-00, C value for C_LAST during load: 154
16:21:02,894 [main] INFO   jTPCC : Term-00, C value for C_LAST this run:    87
16:21:02,894 [main] INFO   jTPCC : Term-00,                                                Term-00, Running Average tpmTOTAL: 4148.93    Current tpmTOTA16:22:02,936 [Thread-0] INFO   jTPCC : Term-00,
16:22:02,937 [Thread-0] INFO   jTPCC : Term-00,
16:22:02,937 [Thread-0] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 1867.75
16:22:02,937 [Thread-0] INFO   jTPCC : Term-00, Measured tpmTOTAL = 4148.44
16:22:02,937 [Thread-0] INFO   jTPCC : Term-00, Session Start     = 2022-04-19 16:21:02
16:22:02,937 [Thread-0] INFO   jTPCC : Term-00, Session End       = 2022-04-19 16:22:02
16:22:02,937 [Thread-0] INFO   jTPCC : Term-00, Transaction Count = 4148
wsm12138 commented 2 years ago

看起来 Mycat2 更改了会话的事务隔离级别导致的。 image

如何更改 Mycat2 自动设置隔离级别呢。没搜到相关的 issue -_-||

zwyqz commented 2 years ago

set session transaction isolation level read uncommitted

wsm12138 commented 2 years ago

set session transaction isolation level read uncommitted

麻烦问下如何改，我试图在链接数据源的时候定义每个压测线程的事务隔离级别。但是没有效果。

 conf/datasources/dw0.datasource.json
{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":["set session transaction isolation level read committed;"],
        "initSqlsGetConnection":true,
        "instanceType":"READ_WRITE",
        "maxCon":1000,
        "maxConnectTimeout":30000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"dw0",
        "password":"passwd",
        "queryTimeout":0,
        "type":"JDBC",
        "url":"jdbc:mysql://IP:3306/tpcc1?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
        "user":"root",
        "weight":0
}

另外麻烦问下，如何配置 Mycat2 才能发挥 TPC-C 场景极致性能呢。仅考虑 Mycat2 这一层就好。感谢~

zwyqz commented 2 years ago

设置的是链接mycat2的链接；

wsm12138 commented 2 years ago

使用在压测程序处定义 事务隔离级别的方式已解决。 但还是没找到 Mycat2 设置的地方~

大佬这边如何配置 Mycat2 以提升性能，是否有文档可以参考下呢。 image

junwen12221 commented 2 years ago

https://www.yuque.com/ccazhw/ml3nkf/5cacdea3cc9ff44e67ed2375e4cd4404

isolation 设置初始化的事务隔离级别 READ_UNCOMMITTED:1 READ_COMMITTED:2 REPEATED_READ:3,默认 SERIALIZABLE:4

最新版支持的,4月发布的版本都支持

wsm12138 commented 2 years ago

https://www.yuque.com/ccazhw/ml3nkf/5cacdea3cc9ff44e67ed2375e4cd4404

isolation 设置初始化的事务隔离级别 READ_UNCOMMITTED:1 READ_COMMITTED:2 REPEATED_READ:3,默认 SERIALIZABLE:4

最新版支持的,4月发布的版本都支持

感谢回复，我验证了下提到的从 Mycat2 端设置事务隔离级别。 根据文档在 user 里设置了事务隔离级别后，发现未生效。

Mycat2 配置 版本 mycat2-install-template-1.20 & mycat2-1.21-release-jar-with-dependencies-2022-5-7.jar image

BenchmarkSQL 报错 image

MySQL 内部事务情况 image

看到文档内有提到 TPC-C 测试，不分库部分表我这里验证也是可以跑通，但是在 4 分片，8 分片的时候。BenchmarkSQL 一直报上面的错。是否有群可以沟通一下呢，卡了有挺久了 ORZ~ https://www.yuque.com/ccazhw/ml3nkf/gxdvoq