查看 OceanBase 执行计划_ob数据库 query time out-CSDN博客

本文链接：https://blog.csdn.net/qq_40877676/article/details/122984791

环境说明

单机单节点部署
obd cluster display test

+---------------------------------------------+
|                   observer                  |
+-----------+---------+------+-------+--------+
| ip        | version | port | zone  | status |
+-----------+---------+------+-------+--------+
| 127.0.0.1 | 3.1.1   | 2881 | zone1 | active |
+-----------+---------+------+-------+--------+

/etc/sysctl.conf

fs.aio-max-nr = 1048576 ##文件系统最大异步io
fs.file-max = 6815744 ##文件系统中文件的最大个数

一、使用 BenmarkSQL 运行 TPC-C
1.1 准备:
创建tpcc租户（不建议用sys租户）

单机扩容

oceanbase默认sys租户使用了2.5-5个CPU，1G内存.剩余1.5个cpu 3G内存。
根本不够使用的。因此修改配置进行扩容


obd cluster edit-config test
obd cluster reload  test
obd cluster restart test

添加租户

obclient -uroot@sys -h127.0.0.1 -P2881 oceanbase

这里没有obproxy，因此端口不是2883

#创建资源单元规格

create resource unit tpcc_unit_2c2g max_cpu=2, min_cpu=2, max_memory='2g', min_memory='2g', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='10g';

#创建资源池(

create resource pool tpcc_pool_test unit = 'tpcc_unit_2c2g', unit_num = 1;

#创建租户：

obclient -uroot@tpcc -h127.0.0.1 -P2881 -p123456 -Doceanbase
create tenant tpcc resource_pool_list=('sys_pool');
alter user root identified by '123456';

创建数据库

create database tpcc;
obclient -uroot@tpcc -h127.0.0.1 -P2881 -p123456 -Dtpcc

1.2. 操作过程
集群参数调优：无

设置事务超时时间

Worker 001: ERROR: Transaction is timeout

ob_query_timeout 用于设置查询超时时间，单位是微秒。

set global ob_query_timeout=36000000000;

ob_trx_timeout 用于设置事务超时时间，单位为微秒。

set global ob_trx_timeout=36000000000;

1.2 安装benchmarksql

##java环境配置

yum install java-1.8.0-openjdk  java-1.8.0-openjdk-devel  
java  -version
openjdk version "1.8.0_322"

下载benchmarksql

git clone https://github.com/obpilot/benchmarksql-5.0.git
/app/local/2022/benchmarksql-5.0

修改配置：

cd /app/local/2022/benchmarksql-5.0/run
cat  tpcc.props.ob

conn=jdbc:oceanbase://127.1:2881/tpcc?useUnicode=true&characterEncoding=utf-8
#这里没有obproxy jdbc 2283改为2281
user=root@tpcc
#tpcc租户 root用户
password=123456

建立用户和数据文件

sh runSQL.sh tpcc.props.ob sql.common/tableCreates.sql

./runSQL.sh props.ob ./sql.oceanbase/tableCreates.sql

Worker 001: Loading Warehouse      1 done
Worker 001: Loading Warehouse      2
Worker 001: Loading Warehouse      2 done

查看数据

obclient -uroot@tpcc -h127.0.0.1 -P2881 -p123456 -Dtpcc

MySQL [tpcc]> show tables;
+------------------+
| Tables_in_tpcc   |
+------------------+
| bmsql_config     |
| bmsql_customer   |
| bmsql_district   |
| bmsql_history    |
| bmsql_item       |
| bmsql_new_order  |
| bmsql_oorder     |
| bmsql_order_line |
| bmsql_stock      |
| bmsql_warehouse  |
+------------------+
10 rows in set (0.002 sec)


MySQL [tpcc]> select count(*) from bmsql_oorder;
+----------+
| count(*) |
+----------+
|    60000 |
+----------+
1 row in set (0.066 sec)

1.3 运行TPCC测试
更新统计信息

obclient -uroot@sys -h127.0.0.1 -P2881 oceanbase

alter system major freeze;

pmC 8.81 ， 96秒一共运行了17个事务
cat runBenchmark.sh

source ./funcs.sh $1
sh runBenchmark.sh tpcc.props.ob
Term-00, Running Average tpmTOTAL: 14.43  Current tpmTOTAL: 96  Memory Usage: 111MB / 1928MB
17:22:44,157 [Thread-2] INFO  jTPCC : Term-00, Measured tpmC (NewOrders) = 8.81
17:22:44,157 [Thread-2] INFO  jTPCC : Term-00, Measured tpmTOTAL = 14.43
17:22:44,157 [Thread-2] INFO  jTPCC : Term-00, Session Start   = 2022-02-11 17:21:29
17:22:44,157 [Thread-2] INFO  jTPCC : Term-00, Session End    = 2022-02-11 17:22:44
17:22:44,158 [Thread-2] INFO  jTPCC : Term-00, Transaction Count = 17

二、分析 TPC-C TOP SQL，并查看 3条 SQL 的解析执行计划和实际执行计划。
2.1 查询TOP3 sql 实际执行计划
obclient -uroot@tpcc -h127.0.0.1 -P2881 -p123456 -Doceanbase


SELECT sql_id, count(*),  round(avg(elapsed_time)) avg_elapsed_time,
                      round(avg(execute_time)) avg_exec_time,
                      s.svr_ip,
                      s.svr_port,
                      s.tenant_id,
                      s.plan_id
                     FROM gv$sql_audit s
                     WHERE 1=1
                      and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) )
                     GROUP BY sql_id
                    order by avg_elapsed_time desc limit 3;




*********************** 1. row ***************************
          sql_id: F59A700FA168324279B0DBC25E19760F
        count(*): 1
avg_elapsed_time: 640709
   avg_exec_time: 635456
          svr_ip: 127.0.0.1
        svr_port: 2882
       tenant_id: 1002
         plan_id: 655
*************************** 2. row ***************************
          sql_id: 482BA7822AE7BE644CEBEB55213E7284
        count(*): 1
avg_elapsed_time: 633977
   avg_exec_time: 632120
          svr_ip: 127.0.0.1
        svr_port: 2882
       tenant_id: 1002
         plan_id: 666
*************************** 3. row ***************************
          sql_id: EC66B09D06D688727D0F999BFCFF5348
        count(*): 1
avg_elapsed_time: 456054
   avg_exec_time: 454233
          svr_ip: 127.0.0.1
        svr_port: 2882
       tenant_id: 1002
         plan_id: 667
3 rows in set (0.028 sec)

EC66B09D06D688727D0F999BFCFF5348 为例子分析

查看sql

```select distinct query_sql from gv$sql_audit where sql_id='EC66B09D06D688727D0F999BFCFF5348' \G;

OPERATOR Operator 的名称

SELECT sum(ol_amount) AS sum_ol_amount FROM bmsql_order_line WHERE ol_w_id = 2 AND ol_d_id = 1 AND ol_o_id = 2101

实际执行计划

SELECT ip, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.`gv$plan_cache_plan_explain`  
                        where tenant_id=1002 AND ip = '127.0.0.1' AND port=2882 AND plan_id=667 \G;
*********************** 1. row ***************************
          ip: 127.0.0.1
  plan_depth: 0
plan_line_id: 0
    operator: PHY_SCALAR_AGGREGATE  聚合操作
        name: NULL
        rows: 1
        cost: 499581
    property: NULL
*************************** 2. row ***************************
          ip: 127.0.0.1
  plan_depth: 1
plan_line_id: 1
    operator:  PHY_TABLE_SCAN 扫描
        name: bmsql_order_line
        rows: 11
        cost: 499579


2.2 执行计划（没有执行）

EC66B09D06D688727D0F999BFCFF5348 sql 为例子
explain SELECT sum(ol_amount) AS sum_ol_amount  FROM bmsql_order_line  WHERE ol_w_id = 2 AND ol_d_id = 1 AND ol_o_id = 2101 \G;
*************************** 1. row ***************************
Query Plan: ======================================================
|ID|OPERATOR       |NAME            |EST. ROWS|COST  |
------------------------------------------------------
|0 |SCALAR GROUP BY|                |1        |499691|
|1 | TABLE SCAN    |bmsql_order_line|11       |499689|
======================================================


Outputs & filters:
-------------------------------------
  0 - output([T_FUN_SUM(bmsql_order_line.ol_amount)]), filter(nil),
      group(nil), agg_func([T_FUN_SUM(bmsql_order_line.ol_amount)])
  1 - output([bmsql_order_line.ol_amount]), filter([bmsql_order_line.ol_o_id = 2101], [bmsql_order_line.ol_d_id = 1], [bmsql_order_line.ol_w_id = 2]),
      access([bmsql_order_line.ol_w_id], [bmsql_order_line.ol_d_id], [bmsql_order_line.ol_o_id], [bmsql_order_line.ol_amount]), partitions(p0)