benchmarksql 配置jdbc:mysql:loadbalance测试出现大量异常

[salahgao]

测试版本 tidb v4.0.1 benchmarksql v5.0-mysql-support-opt-2.1

测试配置

db=mysql
driver=com.mysql.cj.jdbc.Driver
conn=jdbc:mysql:loadbalance://10.5.74.51:4000,10.5.74.52:4000,10.5.74.53:4000/tpcc?useSSL=false&useServerPrepStmts=true&useConfigs=maxPerformance&rewriteBatchedStatements=true
warehouses=200
terminals=500

测试异常

10:32:33,872 [Thread-353] ERROR  jTPCCTData : Unexpected SQLException in NEW_ORDER
10:32:33,873 [Thread-353] ERROR  jTPCCTData : Duplicate entry '7-4-3295-1' for key 'PRIMARY'
10:32:33,872 [Thread-93] ERROR  jTPCCTData : Unexpected SQLException in NEW_ORDER
10:32:33,873 [Thread-93] ERROR  jTPCCTData : Duplicate entry '94-10-3286-1' for key 'PRIMARY'
10:32:33,873 [Thread-137] ERROR  jTPCCTData : Unexpected SQLException in PAYMENT
10:32:33,874 [Thread-137] ERROR  jTPCCTData : Deadlock found when trying to get lock; try restarting transaction
10:32:33,873 [Thread-160] ERROR  jTPCCTData : Unexpected SQLException in PAYMENT
10:32:33,875 [Thread-160] ERROR  jTPCCTData : Deadlock found when trying to get lock; try restarting transaction

java.sql.BatchUpdateException: Duplicate entry '94-10-3286-1' for key 'PRIMARY'
        at sun.reflect.GeneratedConstructorAccessor28.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.cj.util.Util.handleNewInstance(Util.java:191)
        at com.mysql.cj.util.Util.getInstance(Util.java:166)
        at com.mysql.cj.util.Util.getInstance(Util.java:173)
        at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchedInserts(ClientPreparedStatement.java:776)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:447)
        at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:814)
        at sun.reflect.GeneratedMethodAccessor76.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at com.mysql.cj.jdbc.ha.MultiHostConnectionProxy$JdbcInterfaceProxy.invoke(MultiHostConnectionProxy.java:105)
        at com.sun.proxy.$Proxy1.executeBatch(Unknown Source)
        at jTPCCTData.executeNewOrder(jTPCCTData.java:560)
        at jTPCCTData.execute(jTPCCTData.java:95)
        at jTPCCTerminal.executeTransactions(jTPCCTerminal.java:277)
        at jTPCCTerminal.run(jTPCCTerminal.java:88)
        at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '94-10-3286-1' for key 'PRIMARY'
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:634)
        at com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:414)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1113)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1381)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchedInserts(ClientPreparedStatement.java:737)
        ... 12 more
com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:634)
        at com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:414)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1113)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1381)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1046)
        at sun.reflect.GeneratedMethodAccessor34.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at com.mysql.cj.jdbc.ha.MultiHostConnectionProxy$JdbcInterfaceProxy.invoke(MultiHostConnectionProxy.java:105)
        at com.sun.proxy.$Proxy1.executeUpdate(Unknown Source)
        at jTPCCTData.executePayment(jTPCCTData.java:793)
        at jTPCCTData.execute(jTPCCTData.java:99)
        at jTPCCTerminal.executeTransactions(jTPCCTerminal.java:160)
        at jTPCCTerminal.run(jTPCCTerminal.java:88)
        at java.lang.Thread.run(Thread.java:748)

异常统计

[root@client run]# cat t4|grep "Duplicate entry"|wc -l
37748
[root@client run]# cat t4|grep "Deadlock found "|wc -l
10916
[root@client run]# cat t4|grep "Unexpected SQLException "|wc -l
48664

执行结果

10:37:31,600 [Thread-166] INFO   jTPCC : Term-00,
10:37:31,600 [Thread-166] INFO   jTPCC : Term-00,
10:37:31,600 [Thread-166] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 19554.56
10:37:31,600 [Thread-166] INFO   jTPCC : Term-00, Measured tpmTOTAL = 43411.32
10:37:31,601 [Thread-166] INFO   jTPCC : Term-00, Session Start     = 2020-06-24 10:32:30
10:37:31,601 [Thread-166] INFO   jTPCC : Term-00, Session End       = 2020-06-24 10:37:31
10:37:31,601 [Thread-166] INFO   jTPCC : Term-00, Transaction Count = 217722
10:37:31,601 [Thread-166] INFO   jTPCC : executeTime[Payment]=72336244
10:37:31,601 [Thread-166] INFO   jTPCC : executeTime[Order-Status]=606410
10:37:31,601 [Thread-166] INFO   jTPCC : executeTime[Delivery]=14090381
10:37:31,601 [Thread-166] INFO   jTPCC : executeTime[Stock-Level]=907878
10:37:31,601 [Thread-166] INFO   jTPCC : executeTime[New-Order]=62055808

benchmarksql 配置jdbc:mysql:loadbalance测试出现大量异常，异常是测试程序问题还是测试配置问题，如何来解决？谢谢

[cyckax]

同样的问题出现，请问有解释么？