ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > 国内数据库 > 实践练习六（必选）：查看 OceanBase 执行计划

实践练习六（必选）：查看 OceanBase 执行计划

原创 国内数据库 作者：zy_rain 时间：2024-03-16 18:46:09 0 删除 编辑

1 服务器规划：

服务器一台

名称

说明

操作系统

CentOS Linux   release 7.8.2003

处理器

4 v

内存

12 g

硬盘

80 g

 

2. 下载 benchmarksql-5.0 ， jdk

https://sourceforge.net/projects/benchmarksql/files/latest/download

上传 benchmarksql-5.0 和 jdk 到主机

解压jdk并配置环境变量

在主机上使用 ant 编译 benchmarksql-5.0

修改 benchmarksql-5.0/src/client/jTPCC.java

 

增加 mysql 相关部分，如下所示

 

if (iDB.equals("firebird"))

        dbType = DB_FIREBIRD;

    else if (iDB.equals("oracle"))

        dbType = DB_ORACLE;

    else if (iDB.equals("postgres"))

        dbType = DB_POSTGRES;

    else if (iDB.equals("mysql"))

        dbType = DB_UNKNOWN;

    else

    {

        log.error("unknown database type '" + iDB + "'");

        return;

    }

 

修改 benchmarksql-5.0/src/client/jTPCCConnection.java

 

SQL 子查询增加 ”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;

执行编译： ant

 

修改以下配置使 b enchmarksql-5.0 支持 mysql ：

修改 funcs.sh

 cd benchmarksql-5.0/run

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/*"

            ;;

        mysql)

            cp="../lib/mysql/*:../lib/*"

            ;;

    esac

    myCP=".:${cp}:../dist/*"

    export myCP

}

 

# ----

# Make sure that the properties file does have db= and the value

# is a database, we support.

# ----

case "$(getProp db)" in

    firebird|oracle|postgres| mysql)

        ;;

    "") 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

修改： runDatabaseBuild.sh

BEFORE_LOAD="tableCreates"

AFTER_LOAD="indexCreates foreignKeys buildFinish"

 

修改配置文件 props.ob ( 在 run 目录下 )

db=mysql

driver=com.alipay.oceanbase.jdbc.Driver

conn=jdbc:oceanbase://127.0.0.1:2883/tpccdb

user=tpcc@obmysql

password=tpcc

warehouses=1

loadWorkers=4

terminals=1

//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

//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_ens33 blk_sda

 

配置环境jdk环境变量上传oceanbase jar包lib/mysql 下

 

  3. 构建表，并加载数据

[admin@bb4a923622b8 run]$ ./runDatabaseBuild.sh props.ob

[admin@zytob02 run]$ ./runDatabaseBuild.sh  props.ob

# ------------------------------------------------------------

# Loading SQL file ./sql.common/tableCreates.sql

# ------------------------------------------------------------

create table bmsql_config (

cfg_name    varchar(30) primary key,

cfg_value   varchar(50)

);

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)

);

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)

);

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)

);

create sequence bmsql_hist_id_seq;

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)

);

create table bmsql_new_order (

no_w_id  integer   not null,

no_d_id  integer   not null,

no_o_id  integer   not null

);

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

);

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)

);

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

);

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)

);

Starting BenchmarkSQL LoadData

 

driver=com.alipay.oceanbase.jdbc.Driver

conn=jdbc:oceanbase://192.168.163.113:2883/test

user=root@obmysql

password=***********

warehouses=1

loadWorkers=4

fileLocation (not defined)

csvNullValue (not defined - using default 'NULL')

 

Worker 000: Loading ITEM

Worker 001: Loading Warehouse      1

Worker 000: Loading ITEM done

Worker 001: Loading Warehouse      1 done

# ------------------------------------------------------------

# Loading SQL file ./sql.common/indexCreates.sql

# ------------------------------------------------------------

alter table bmsql_warehouse add constraint bmsql_warehouse_pkey

primary key (w_id);

alter table bmsql_district add constraint bmsql_district_pkey

primary key (d_w_id, d_id);

alter table bmsql_customer add constraint bmsql_customer_pkey

primary key (c_w_id, c_d_id, c_id);

create index bmsql_customer_idx1

on  bmsql_customer (c_w_id, c_d_id, c_last, c_first);

alter table bmsql_oorder add constraint bmsql_oorder_pkey

primary key (o_w_id, o_d_id, o_id);

create unique index bmsql_oorder_idx1

on  bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id);

alter table bmsql_new_order add constraint bmsql_new_order_pkey

primary key (no_w_id, no_d_id, no_o_id);

alter table bmsql_order_line add constraint bmsql_order_line_pkey

primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);

alter table bmsql_stock add constraint bmsql_stock_pkey

primary key (s_w_id, s_i_id);

alter table bmsql_item add constraint bmsql_item_pkey

primary key (i_id);

# ------------------------------------------------------------

# Loading SQL file ./sql.common/foreignKeys.sql

# ------------------------------------------------------------

alter table bmsql_district add constraint d_warehouse_fkey

foreign key (d_w_id)

references bmsql_warehouse (w_id);

alter table bmsql_customer add constraint c_district_fkey

foreign key (c_w_id, c_d_id)

references bmsql_district (d_w_id, d_id);

alter table bmsql_history add constraint h_customer_fkey

foreign key (h_c_w_id, h_c_d_id, h_c_id)

references bmsql_customer (c_w_id, c_d_id, c_id);

alter table bmsql_history add constraint h_district_fkey

foreign key (h_w_id, h_d_id)

references bmsql_district (d_w_id, d_id);

alter table bmsql_new_order add constraint no_order_fkey

foreign key (no_w_id, no_d_id, no_o_id)

references bmsql_oorder (o_w_id, o_d_id, o_id);

alter table bmsql_oorder add constraint o_customer_fkey

foreign key (o_w_id, o_d_id, o_c_id)

references bmsql_customer (c_w_id, c_d_id, c_id);

alter table bmsql_order_line add constraint ol_order_fkey

foreign key (ol_w_id, ol_d_id, ol_o_id)

references bmsql_oorder (o_w_id, o_d_id, o_id);

alter table bmsql_order_line add constraint ol_stock_fkey

foreign key (ol_supply_w_id, ol_i_id)

references bmsql_stock (s_w_id, s_i_id);

alter table bmsql_stock add constraint s_warehouse_fkey

foreign key (s_w_id)

references bmsql_warehouse (w_id);

alter table bmsql_stock add constraint s_item_fkey

foreign key (s_i_id)

references bmsql_item (i_id);

# ------------------------------------------------------------

# Loading SQL file ./sql.common/buildFinish.sql

# ------------------------------------------------------------

-- ----

-- Extra commands to run after the tables are created, loaded,

-- indexes built and extra's created.

-- ----

 

4. 执行性能测试

 

[admin@zytob02 run]$ ./runBenchmark.sh  props.ob

06:23:28,063 [main] INFO   jTPCC : Term-00,

06:23:28,065 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+

06:23:28,066 [main] INFO   jTPCC : Term-00,      BenchmarkSQL v5.0

06:23:28,066 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+

06:23:28,066 [main] INFO   jTPCC : Term-00,  (c) 2003, Raul Barbosa

06:23:28,066 [main] INFO   jTPCC : Term-00,  (c) 2004-2016, Denis Lussier

06:23:28,070 [main] INFO   jTPCC : Term-00,  (c) 2016, Jan Wieck

06:23:28,070 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+

06:23:28,070 [main] INFO   jTPCC : Term-00,

06:23:28,070 [main] INFO   jTPCC : Term-00, db=mysql

06:23:28,070 [main] INFO   jTPCC : Term-00, driver=com.alipay.oceanbase.jdbc.Driver

06:23:28,071 [main] INFO   jTPCC : Term-00, conn=jdbc:oceanbase://192.168.163.113:2883/test

06:23:28,071 [main] INFO   jTPCC : Term-00, user=root@obmysql

06:23:28,071 [main] INFO   jTPCC : Term-00,

06:23:28,071 [main] INFO   jTPCC : Term-00, warehouses=1

06:23:28,071 [main] INFO   jTPCC : Term-00, terminals=5

06:23:28,073 [main] INFO   jTPCC : Term-00, runMins=5

06:23:28,073 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=0

06:23:28,073 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=true

06:23:28,073 [main] INFO   jTPCC : Term-00,

06:23:28,073 [main] INFO   jTPCC : Term-00, newOrderWeight=45

06:23:28,074 [main] INFO   jTPCC : Term-00, paymentWeight=43

06:23:28,074 [main] INFO   jTPCC : Term-00, orderStatusWeight=4

06:23:28,074 [main] INFO   jTPCC : Term-00, deliveryWeight=4

06:23:28,074 [main] INFO   jTPCC : Term-00, stockLevelWeight=4

06:23:28,074 [main] INFO   jTPCC : Term-00,

06:23:28,074 [main] INFO   jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS

06:23:28,075 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py

06:23:28,075 [main] INFO   jTPCC : Term-00,

06:23:28,092 [main] INFO   jTPCC : Term-00, copied props.ob to my_result_2024-03-16_062328/run.properties

06:23:28,093 [main] INFO   jTPCC : Term-00, created my_result_2024-03-16_062328/data/runInfo.csv for runID 10

06:23:28,093 [main] INFO   jTPCC : Term-00, writing per transaction results to my_result_2024-03-16_062328/data/result.csv

06:23:28,095 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py

06:23:28,095 [main] INFO   jTPCC : Term-00, osCollectorInterval=1

06:23:28,095 [main] INFO   jTPCC : Term-00, osCollectorSSHAddr=null

06:23:28,095 [main] INFO   jTPCC : Term-00, osCollectorDevices=net_ens33 blk_sda

06:23:28,230 [main] INFO   jTPCC : Term-00,

06:23:28,555 [main] INFO   jTPCC : Term-00, C value for C_LAST during load: 216

06:23:28,555 [main] INFO   jTPCC : Term-00, C value for C_LAST this run:    139

06:23:28,556 [main] INFO   jTPCC : Term-00,

Term-00, Running Average tpmTOTAL: 2313.61    Current tpmTOTAL: 77184    Memory Usage: 6MB / 151MB          

06:28:28,968 [Thread-5] INFO   jTPCC : Term-00,

06:28:28,969 [Thread-5] INFO   jTPCC : Term-00,

06:28:28,969 [Thread-5] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 1029.09

06:28:28,970 [Thread-5] INFO   jTPCC : Term-00, Measured tpmTOTAL = 2312.56

06:28:28,970 [Thread-5] INFO   jTPCC : Term-00, Session Start     = 2024-03-16 06:23:28

06:28:28,970 [Thread-5] INFO   jTPCC : Term-00, Session End       = 2024-03-16 06:28:28

06:28:28,970 [Thread-5] INFO   jTPCC : Term-00, Transaction Count = 11572

 

5 获取 执行计划

 

登录 mysql -h 192.168.163.113 -uroot@obmysql#obdemo -P2883 -p -c -A test

获取top sql

mysql> SELECT user_name,sql_id, count(*), round(avg(elapsed_time)) avg_elapsed_time, round(avg(execute_time)) avg_exec_time

    ->             FROM gv$OB_SQL_AUDIT s

    ->             WHERE 1=1

    ->              and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 1 day))

    ->             GROUP BY sql_id

    ->            order by user_name,avg_elapsed_time desc limit 10;

+-----------+----------------------------------+----------+------------------+---------------+

| user_name | sql_id                           | count(*) | avg_elapsed_time | avg_exec_time |

+-----------+----------------------------------+----------+------------------+---------------+

| root      | FC3FED8CCB2946DE54F1C5BA3656023C |       68 |            45419 |          5942 |

| root      | 227161BB8903E42465279C2C90395D05 |        4 |            41991 |         41155 |

| root      | F4585305C4CB9B091C750826A7DEDD13 |       76 |            30541 |          3767 |

| root      | B447DE16B3F42D2409B2A2BE50328E63 |       76 |            17806 |          5436 |

| root      | F95CD7A05C7064A028D76E0088B147F7 |       71 |             8840 |          1197 |

| root      | 8D6E84735C0B8FB1823D199E2CA141C8 |       71 |             7002 |          6653 |

| root      | C9FA41444B84AF63AD4FB24B9252F1A6 |        7 |             5516 |          5228 |

| root      | 5984364296F35BE1B71CD5622426385A |        7 |             4082 |          3615 |

| root      | B3FC9C1BD6CAB2D8A9180BEF92F112D2 |        7 |             3574 |          3014 |

| root      | 482BA7822AE7BE644CEBEB55213E7284 |       68 |             3110 |          2828 |

+-----------+----------------------------------+----------+------------------+---------------+

10 rows in set (0.04 sec)

 

mysql> select query_sql from gv$OB_PLAN_CACHE_PLAN_STAT where sql_id='FC3FED8CCB2946DE54F1C5BA3656023C';

+----------------------------------------------------------------------------------------------------+

| query_sql                                                                                          |

+----------------------------------------------------------------------------------------------------+

| SELECT d_tax, d_next_o_id     FROM bmsql_district     WHERE d_w_id = 1 AND d_id = 4     FOR UPDATE |

| SELECT d_tax, d_next_o_id     FROM bmsql_district     WHERE d_w_id = 1 AND d_id = 9     FOR UPDATE |

+----------------------------------------------------------------------------------------------------+

2 rows in set (0.01 sec)

 

mysql> select query_sql from gv$OB_PLAN_CACHE_PLAN_STAT where sql_id='227161BB8903E42465279C2C90395D05';

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| query_sql                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| SELECT count(*) AS low_stock FROM (    SELECT s_w_id, s_i_id, s_quantity         FROM bmsql_stock         WHERE s_w_id = 1 AND s_quantity < 20 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 = 1 AND d_id = 9         )     ) AS L  |

| SELECT count(*) AS low_stock FROM (    SELECT s_w_id, s_i_id, s_quantity         FROM bmsql_stock         WHERE s_w_id = 1 AND s_quantity < 19 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 = 1 AND d_id = 10         )     ) AS L |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.01 sec)

 

mysql> select query_sql from gv$OB_PLAN_CACHE_PLAN_STAT where sql_id='F4585305C4CB9B091C750826A7DEDD13';

+-----------------------------------------------------------------------------------------+

| query_sql                                                                               |

+-----------------------------------------------------------------------------------------+

| UPDATE bmsql_district     SET d_ytd = d_ytd + 1917.27     WHERE d_w_id = 1 AND d_id = 1 |

| UPDATE bmsql_district     SET d_ytd = d_ytd + 4803.7     WHERE d_w_id = 1 AND d_id = 5  |

| UPDATE bmsql_district     SET d_ytd = d_ytd + 1177.88     WHERE d_w_id = 1 AND d_id = 1 |

| UPDATE bmsql_district     SET d_ytd = d_ytd + 147.4     WHERE d_w_id = 1 AND d_id = 2   |

+-----------------------------------------------------------------------------------------+

4 rows in set (0.00 sec)

 

获取执行前三个 top sql 执行计划

mysql>  explain  SELECT d_tax, d_next_o_id     FROM bmsql_district     WHERE d_w_id = 1 AND d_id = 4     FOR UPDATE;

 

 

explain SELECT count(*) AS low_stock FROM (    SELECT s_w_id, s_i_id, s_quantity         FROM bmsql_stock         WHERE s_w_id = 1 AND s_quantity < 20 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 = 1 AND d_id = 9         )     ) AS L ;

 

 explain UPDATE bmsql_district     SET d_ytd = d_ytd + 1917.27     WHERE d_w_id = 1 AND d_id = 1 ;

 


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30349701/viewspace-3009187/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 实践练习二（必选）：手动部署 OceanBase 集群
下一篇： 实践练习四（必选）：迁移 MySQL 数据到 OceanBase 集群
请登录后发表评论 登录
全部评论
zy_rain
没有刻骨铭心的记忆，怎么会有潇洒自如的帅气！

注册时间：2015-07-15

  • 博文量
    16
  • 访问量
    18031

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593