pingcap / benchmarksql Goto Github PK

View Code? Open in Web Editor NEW

73.0 94.0 76.0 4.01 MB

Unofficial mirror of benchmarksql on github

Home Page: https://sourceforge.net/projects/benchmarksql/

Shell 6.89% R 10.85% Python 4.80% PLSQL 1.34% Java 76.12%

benchmarksql's Introduction

BENCHMARKSQL README

CHANGE LOG:

Version 5.0 lussman & jannicash:

Upgrade to PostgreSQL 9.3 JDBC 4.1 version 1102 driver
Improve support for Oracle
Re-implement the non-uniform random generator in TPC-C style.
Conform to clause 4.3.3.1 and enable lookup by last name
Add a switch to disable terminal-warehouse association, spreading the data access over all configured warehouses.
Re-worked the run shell scripts and the location of SQL files to make support of more database types easier.
Add support for Firebird (http://www.firebirdsql.org).
Add FOREIGN KEYS as defined by TPC-C 1.3.
Major code overhaul. The per transaction type terminal data generation, execution and terminal trace code is moved into a module jTPCCTData. The database connection with all prepared statements has moved into a module jTPCCConnection.
Add collecting per transaction result data and OS Level resource usage collection. The R statistics package is used to graph detailed information and a complete report in HTML can be generated from the data.

Version 4.1.2 TBD jannicash:

Fixed one more preparedStatement() leak. Hopefully with the help of Oracle's V$OPEN_CURSOR view we got them all now.
Fixed a possible deadlock problem in the NEW_ORDER transaction. Multiple parallel transaction could attempt to lock the same STOCK rows in reverse order. Sorting the order lines by item ID avoids this problem.

Version 4.1.1 2016-01-31 jannicash:

Changed the status line to update only once per second. The previous implementation was getting rather noisy at high throughput.
Fixed two preparedStatement() leaks that could cause ORA-01000 errors on longer runs with high throughput.
Fixed a problem in the calculation of sleep time between transactions when using limitTxnsPerMin that could cause the test to hang at the end.
Added support for escaping ; as ; in SQL files to be able to load functions and execute anonymous PL blocks (needed for next item).
Changed the definition of history.hist_id into a plain integer with no special functionality. Two new database vendor specific SQL scripts allow to enable the column after data load as an auto incrementing primary key. See HOW-TO-RUN.txt for details.

Version 4.1.0 2014-03-13 lussman:

Upgrade to using JDK 7
Upgrade to PostgreSQL JDBC 4.1 version 1101 driver
Stop claiming to support DB2 (only Postgres & Oracle are well tested)

Version 4.0.9 2013-11-04 cadym:

Incorporate new PostgreSQL JDBC 4 version 1100 driver
Changed default user from postgres to benchmarksql
Added id column as primary key to history table
Renamed schema to benchmarksql
Changed log4j format to be more readable
Created the "benchmark" schema to contain all tables
Incorporate new PostgreSQL JDBC4 version 1003 driver
Transaction rate pacing mechanism
Correct error with loading customer table from csv file
Status line report dynamically shown on terminal
Fix lookup by name in PaymentStatus and Delivery Transactions (in order to be more compatible with the TPC-C spec)
Rationalized the variable naming in the input parameter files (now that the GUI is gone, variable names still make sense)
Default log4j settings only writes to file (not terminal)

Version 4.0.2 2013-06-06 lussman & cadym:

Removed Swing & AWT GUI so that this program is runnable from the command line
Remove log4j usage from runSQL & runLoader (only used now for the actual running of the Benchmark)
Fix truncation problem with customer.csv file
Comment out "BadCredit" business logic that was not working and throwing stack traces
Fix log4j messages to always show the terminal name
Remove bogus log4j messages

Version 3.0.9 2013-03-21 lussman:

Config log4j for rotating log files once per minute
Default flat file location to '/tmp/csv/' in table copies script
Drop incomplete & untested Windoze '.bat' scripts
Standardize logging with log4j
Improve Logging with meaningful DEBUG and INFO levels
Simplify "build.xml" to eliminate nbproject dependency
Defaults read in from propeerties
Groudwork laid to eliminate the GUI
Default GUI console to PostgreSQL and 10 Warehouses

Version 2.3.5 2013-01-29 lussman:

Default build is now with JDK 1.6 and JDBC 4 Postgres 9.2 driver
Remove outdated JDBC 3 drivers (for JDK 1.5). You can run as before by a JDBC4 driver from any supported vendor.
Remove ExecJDBC warning about trying to rollback when in autocommit mode
Remove the extraneous COMMIT statements from the DDL scripts since ExecJDBC runs in autocommit mode
Fix the version number displayed in the console

Versions 1.0 thru 2.2 2004 - 2012 lussman:

Dare to Compare
Forked from the jTPCC project

benchmarksql's People

Contributors

Stargazers

Watchers

Forkers

jackysp youjiali1995 suntingme alivxxx mahjonp juaby cfzjywxk imtbkcat little-wallace gogoyao xuhuaiyu lyfnnnnnn strawhat925 ye-haiyang terry1504 zhangx007zz eidear lhy1024 pingfan20190524 capdata meegar darthmanwe pandalive he-xiaolong zoujia10261021 justbk2015 teslacn istudies guilhermealvesfernandes tfeng mengjiahao jimolonely jingpengkun weiday huimchenwell sandynz qinluyan89 jeremylii hlj-jlh isabella232 firegunlns tristanjamesball lmy2011 by1995 knull-cn lastincisor haiying-gao lilinjun1884 danielhjz vipaiou-outlook lujiashun enchanterblue yui-song wangtiga win-man jiekertime apecloud-inc strongduanmu davidshiz xunknown cmcc-ict 7yyo shawn0915 yangkeao alexrogalskiy liyuefu andyli029

benchmarksql's Issues

wrong Block device prefix in `run/misc/os_collector_linux.py`

the device status is not in the directory given by the source.

>  ls /sys/class/block/
dm-0  loop0      nvme0n1p2  nvme1n1    nvme2n1p2  nvme2n1p5  nvme3n1p2  sdb   sdc1  sde   sde3
dm-1  nvme0n1    nvme0n1p3  nvme2n1    nvme2n1p3  nvme3n1    sda        sdb1  sdd   sde1
dm-2  nvme0n1p1  nvme0n1p4  nvme2n1p1  nvme2n1p4  nvme3n1p1  sda1       sdc   sdd1  sde2

So we should change

def initBlockDevice(dev):
    ...
    devPath = os.path.join("/sys/block", dev[4:], "stat")

to:

def initBlockDevice(dev):
    ...
    devPath = os.path.join("/sys/class/block", dev[4:], "stat")

oracle should change default network dev if using `OL8`

the default network dev name is something like virtio0 in oracle linux-8.

dmesg | grep -i eth

and follows this guide to change it to eth0

Or runBenchmark.sh can not run as expected.

oracle does not support `LIMIT`

LIMIT 1 should be replaced by FETCH FIRST 1 ROWS ONLY

15:43:21,203 [Thread-3] ERROR  jTPCCTData : ORA-00933: SQL 命令未正确结束

java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:807)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
	at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3713)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1167)
	at jTPCCTData.executeOrderStatus(jTPCCTData.java:1184)
	at jTPCCTData.execute(jTPCCTData.java:103)
	at jTPCCTerminal.executeTransactions(jTPCCTerminal.java:212)
	at jTPCCTerminal.run(jTPCCTerminal.java:88)
	at java.lang.Thread.run(Thread.java:750)
Caused by: Error : 933, Position : 141, Sql = SELECT o_id, o_entry_d, o_carrier_id     FROM bmsql_oorder     WHERE o_w_id = :1  AND o_d_id = :2  AND o_c_id = :3        ORDER BY o_id DESC LIMIT 1, OriginalSql = SELECT o_id, o_entry_d, o_carrier_id     FROM bmsql_oorder     WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ?       ORDER BY o_id DESC LIMIT 1, Error Msg = ORA-00933: SQL 命令未正确结束

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
	... 19 more

Creating indexes can be parallelized

This part of loading the database takes the longest. Any reason why these indexes can be built in parallel, so more cores will actually work?

Why JDK7?

Use of JDK7 is required.
Why must compile with JDK7?

JDK7 is not compatible with numactl.
I want to bind the client on a numa node with numactl. Java JDK11 is the least verison that support this feature.
How to compile with JDK11?

I tried to use the os collection. Configured ssh, made sure it works. Still nothing gets collected.
How to debug the os collection? It is started from osCollection class. I have no knowledge about java.

Please advise

The script does not support python3

misc/os_collector_linux.py does not support python3,

oracle should change `FOR UPDATE` clause like following

    stmtDeliveryBGSelectOldestNewOrder = dbConn.prepareStatement(
        "SELECT no_o_id FROM bmsql_new_order WHERE no_o_id in " +
        "( " +
        "       SELECT no_o_id    FROM bmsql_new_order " +
        "       WHERE no_w_id = ? AND no_d_id = ? " +
        "       ORDER BY no_o_id ASC " +
        "       FETCH FIRST 1 ROWS ONLY" +
        ") ");
        //"SELECT no_o_id " +
        //"    FROM bmsql_new_order " +
        //"    WHERE no_w_id = ? AND no_d_id = ? " +
        //"    ORDER BY no_o_id ASC" +
        //"    FETCH FIRST 1 ROWS ONLY" +
        //"    FOR UPDATE");

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

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

mysql

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.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 jTPCCTData.executeDeliveryBG(jTPCCTData.java:1623)
at jTPCCTData.execute(jTPCCTData.java:115)
at jTPCCTerminal.executeTransactions(jTPCCTerminal.java:249)
at jTPCCTerminal.run(jTPCCTerminal.java:88)

oracle may cause `ORA-01653` using default `BLOCK_SIZE=8192KB`

if specify many warehouses (e.g., larger than about 400), oracle will have ORA-01653 error because the max table space size for 8192K-block is 32GB. So I have to use (I set warehouses=1000 and user=system):

`Alter tablespace SYSTEM add datafile '/opt/oracle/oradata/ORCLCDB/system02.dbf' size 10240M autoextend on next 1024M Maxsize UNLIMITED`
`Alter tablespace SYSTEM add datafile '/opt/oracle/oradata/ORCLCDB/system03.dbf' size 10240M autoextend on next 1024M Maxsize UNLIMITED`