oceanbase-tpcc测试

一、准备压测工具

详情请看官网文档

1.下载资源

# 下载
wget https://sourceforge.net/projects/benchmarksql/files/benchmarksql-5.0.zip
# 解压
unzip benchmarksql-5.0.zip

2.修改 java 代码

// vim benchmarksql-5.0/src/client/jTPCC.java
if (iDB.equals("firebird"))
        dbType = DB_FIREBIRD;
    else if (iDB.equals("oracle"))
        dbType = DB_ORACLE;
    else if (iDB.equals("postgres"))
        dbType = DB_POSTGRES;
    // 增加ob类型
    else if (iDB.equals("oceanbase"))
        dbType = DB_OCEANBASE;
    else
    {
        log.error("unknown database type '" + iDB + "'");
        return;
    }



// vim benchmarksql-5.0/src/client/jTPCCConfig.java
public final static int         
DB_UNKNOWN = 0,
DB_FIREBIRD = 1,
DB_ORACLE = 2,
DB_POSTGRES = 3,
// 增加 ob 类型
DB_OCEANBASE = 4;

// vim benchmarksql-5.0/src/client/jTPCCConnection.java
// 增加 AS L 别名
default:
            stmtStockLevelSelectLow = dbConn.prepareStatement(
                "SELECT count(*) AS low_stock FROM (" +
                "    SELECT s_w_id, s_i_id, s_quantity " +
                "        FROM bmsql_stock " +
                "        WHERE s_w_id = ? AND s_quantity < ? AND s_i_id IN (" +
                "            SELECT ol_i_id " +
                "                FROM bmsql_district " +
                "                JOIN bmsql_order_line ON ol_w_id = d_w_id " +
                "                 AND ol_d_id = d_id " +
                "                 AND ol_o_id >= d_next_o_id - 20 " +
                "                 AND ol_o_id < d_next_o_id " +
                "                WHERE d_w_id = ? AND d_id = ? " +
                "        ) " +
                "    )AS L");
            break;

3.修改 shell 文件

# vim benchmarksql-5.0/run/funcs.sh
function setCP()
{
   case "$(getProp db)" in
firebird)
    cp="../lib/firebird/*:../lib/*"
    ;;
oracle)
    cp="../lib/oracle/*"
    if [ ! -z "${ORACLE_HOME}" -a -d ${ORACLE_HOME}/lib ] ; then
 cp="${cp}:${ORACLE_HOME}/lib/*"
    fi
    cp="${cp}:../lib/*"
    ;;
postgres)
    cp="../lib/postgres/*:../lib/*"
    ;;
# 增加 ob 配置
oceanbase)
    cp="../lib/oceanbase/*:../lib/*"
    ;;
   esac
   myCP=".:${cp}:../dist/*"
   export myCP
}

# ...省略
# 增加 ob 配置
case "$(getProp db)" in
   firebird|oracle|postgres|oceanbase)
   ;;
   "") echo "ERROR: missing db= config option in ${PROPS}" >&2
   exit 1
   ;;
   *)  echo "ERROR: unsupported database type 'db=$(getProp db)' in ${PROPS}" >&2
   exit 1
   ;;
esac


# vim benchmarksql-5.0/run/runDatabaseBuild.sh
AFTER_LOAD="indexCreates foreignKeys extraHistID buildFinish"
修改为：
AFTER_LOAD="indexCreates buildFinish"

4.修改配置文件

# vim vim benchmarksql-5.0/run/props.ob

db=oceanbase
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://127.0.0.1:2881/tpccdb?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true&allowMultiQueries=true
user=root@test
password=****

warehouses=10
loadWorkers=2
//fileLocation=/data/temp/

terminals=10
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=10
//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=true

//The following five values must add up to 100
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

5.编译

apt install ant
cd benchmarksql-5.0
ant

6.安装jdbc包

# 下载压缩包
wget https://downloads.mysql.com/archives/get/p/3/file/mysql-connector-java-5.1.47.tar.gz
# 解压缩
tar zxf mysql-connector-java-5.1.47.tar.gz
cd mysql-connector-java-5.1.47
cp mysql-connector-java-5.1.47.jar mysql-connector-java-5.1.47-bin.jar benchmarksql-5.0/lib/

二、准备数据和压测

1.建表sql

vim  benchmarksql-5.0/run/sql.common/tableCreates_parts.sql
CREATE TABLE bmsql_config (
cfg_name    varchar(30) primary key,
cfg_value   varchar(50)
);

# drop tablegroup tpcc_group;
CREATE TABLEGROUP tpcc_group sharding='PARTITION';


CREATE TABLE bmsql_warehouse (
   w_id        integer   not null,
   w_ytd       decimal(12,2),
   w_tax       decimal(4,4),
   w_name      varchar(10),
   w_street_1  varchar(20),
   w_street_2  varchar(20),
   w_city      varchar(20),
   w_state     char(2),
   w_zip       char(9),
   primary key(w_id)
)tablegroup='tpcc_group' partition by hash(w_id) partitions 9;

CREATE TABLE bmsql_district (
   d_w_id       integer       not null,
   d_id         integer       not null,
   d_ytd        decimal(12,2),
   d_tax        decimal(4,4),
   d_next_o_id  integer,
   d_name       varchar(10),
   d_street_1   varchar(20),
   d_street_2   varchar(20),
   d_city       varchar(20),
   d_state      char(2),
   d_zip        char(9),
   PRIMARY KEY (d_w_id, d_id)
)tablegroup='tpcc_group' partition by hash(d_w_id) partitions 9;

CREATE TABLE bmsql_customer (
   c_w_id         integer        not null,
   c_d_id         integer        not null,
   c_id           integer        not null,
   c_discount     decimal(4,4),
   c_credit       char(2),
   c_last         varchar(16),
   c_first        varchar(16),
   c_credit_lim   decimal(12,2),
   c_balance      decimal(12,2),
   c_ytd_payment  decimal(12,2),
   c_payment_cnt  integer,
   c_delivery_cnt integer,
   c_street_1     varchar(20),
   c_street_2     varchar(20),
   c_city         varchar(20),
   c_state        char(2),
   c_zip          char(9),
   c_phone        char(16),
   c_since        timestamp,
   c_middle       char(2),
   c_data         varchar(500),
   PRIMARY KEY (c_w_id, c_d_id, c_id)
)tablegroup='tpcc_group' partition by hash(c_w_id) partitions 9;


CREATE TABLE bmsql_history (
   hist_id  integer,
   h_c_id   integer,
   h_c_d_id integer,
   h_c_w_id integer,
   h_d_id   integer,
   h_w_id   integer,
   h_date   timestamp,
   h_amount decimal(6,2),
   h_data   varchar(24)
)tablegroup='tpcc_group' partition by hash(h_w_id) partitions 9;

CREATE TABLE bmsql_new_order (
   no_w_id  integer   not null ,
   no_d_id  integer   not null,
   no_o_id  integer   not null,
   PRIMARY KEY (no_w_id, no_d_id, no_o_id)
)tablegroup='tpcc_group' partition by hash(no_w_id) partitions 9;

CREATE TABLE bmsql_oorder (
   o_w_id       integer      not null,
   o_d_id       integer      not null,
   o_id         integer      not null,
   o_c_id       integer,
   o_carrier_id integer,
   o_ol_cnt     integer,
   o_all_local  integer,
   o_entry_d    timestamp,
   PRIMARY KEY (o_w_id, o_d_id, o_id)
)tablegroup='tpcc_group' partition by hash(o_w_id) partitions 9;

CREATE TABLE bmsql_order_line (
   ol_w_id         integer   not null,
   ol_d_id         integer   not null,
   ol_o_id         integer   not null,
   ol_number       integer   not null,
   ol_i_id         integer   not null,
   ol_delivery_d   timestamp,
   ol_amount       decimal(6,2),
   ol_supply_w_id  integer,
   ol_quantity     integer,
   ol_dist_info    char(24),
   PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number)
)tablegroup='tpcc_group' partition by hash(ol_w_id) partitions 9;

CREATE TABLE bmsql_item (
   i_id     integer      not null,
   i_name   varchar(24),
   i_price  decimal(5,2),
   i_data   varchar(50),
   i_im_id  integer,
   PRIMARY KEY (i_id)
);

CREATE TABLE bmsql_stock (
   s_w_id       integer       not null,
   s_i_id       integer       not null,
   s_quantity   integer,
   s_ytd        integer,
   s_order_cnt  integer,
   s_remote_cnt integer,
   s_data       varchar(50),
   s_dist_01    char(24),
   s_dist_02    char(24),
   s_dist_03    char(24),
   s_dist_04    char(24),
   s_dist_05    char(24),
   s_dist_06    char(24),
   s_dist_07    char(24),
   s_dist_08    char(24),
   s_dist_09    char(24),
   s_dist_10    char(24),
   PRIMARY KEY (s_w_id, s_i_id)
)tablegroup='tpcc_group' use_bloom_filter=true partition by hash(s_w_id) partitions 9;

2.建表

./runSQL.sh props.ob sql.common/tableCreates_parts.sql

3.添加索引

登录到对应的集群中的数据库添加索引

CREATE INDEX bmsql_customer_idx1
  on  bmsql_customer (c_w_id, c_d_id, c_last, c_first) local;

CREATE INDEX bmsql_oorder_idx1
  on  bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id) local;

4.集群合并

• 这一步可有可无，合并测试的效果更好些

ALTER SYSTEM MAJOR FREEZE;
# 查看status,为idle表示合并完成，注意只能在sys租户中查看
SELECT * FROM oceanbase.CDB_OB_ZONE_MAJOR_COMPACTION;

5.开始压测

./runBenchmark.sh props.ob