benchmarksql 配置jdbc:mysql:loadbalance测试出现大量异常 #20
Description
测试版本
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测试出现大量异常，异常是测试程序问题还是测试配置问题，如何来解决？谢谢