首页 > 其他分享 >OceanBase初体验之查看OceanBase的执行计划

OceanBase初体验之查看OceanBase的执行计划

时间:2024-03-17 17:44:57浏览次数:34  
标签:INFO 00 初体验 14 查看 OceanBase Term jTPCC id

前置条件

  • 包含obd和obclient的中控机
  • OceanBase 测试集群
  • 独立的测试租户
  • BenchmarkSQL 工具(可选)

为了能够方面的查看复杂SQL的执行计划,我们先用TPCC模拟一些数据库负载。

模拟数据库负载

obd里面已经集成了tpcc测试工具,需要联网更新一下插件即可。如果机器不具备外网环境,需要提前下载BenchmarkSQL上传到测试机中。

BenchmarkSQL下载地址:https://github.com/meiq4096/benchmarksql-5.0

这里直接用obd里的tpcc,操作比较简单:

[ob@localhost ~]$ sudo yum install -y yum-utils
[ob@localhost ~]$ sudo yum-config-manager --add-repo https://mirrors.aliyun.com/oceanbase/OceanBase.repo
[ob@localhost ~]$ sudo yum install obtpcc java

在前面新建的tt租户下跑一个10仓的负载,时间是5分钟:

[ob@localhost ~]$ obd test tpcc obtest --tenant=tt --warehouses 10  --run-mins 5
Get local repositories and plugins ok
Open ssh connection ok
Cluster status check ok
Connect obproxy(x.x.x.222:2883) ok
Connect obproxy(x.x.x.222:2883) ok
Optimize for stage build ok
Connect to tenant tt ok
Server check ok
Merge ok
Starting BenchmarkSQL LoadData

driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://x.x.x.222:2883/test?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=30000000&useSSL=false
user=root@tt
password=***********
warehouses=10
loadWorkers=1
fileLocation (not defined)
csvNullValue (not defined - using default 'NULL')

Worker 000: Loading ITEM
Worker 000: Loading ITEM done
Worker 000: Loading Warehouse      1
Worker 000: Loading Warehouse      1 done
Worker 000: Loading Warehouse      2
Worker 000: Loading Warehouse      2 done
Worker 000: Loading Warehouse      3
Worker 000: Loading Warehouse      3 done
Worker 000: Loading Warehouse      4
Worker 000: Loading Warehouse      4 done
Worker 000: Loading Warehouse      5
Worker 000: Loading Warehouse      5 done
Worker 000: Loading Warehouse      6
Worker 000: Loading Warehouse      6 done
Worker 000: Loading Warehouse      7
Worker 000: Loading Warehouse      7 done
Worker 000: Loading Warehouse      8
Worker 000: Loading Warehouse      8 done
Worker 000: Loading Warehouse      9
Worker 000: Loading Warehouse      9 done
Worker 000: Loading Warehouse     10
Worker 000: Loading Warehouse     10 done
create index ok
finish build ok
check data ok
Optimize for stage test ok
Connect to tenant tt ok
Merge ok
14:20:27,866 [main] INFO   jTPCC : Term-00,
14:20:27,869 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
14:20:27,869 [main] INFO   jTPCC : Term-00,      BenchmarkSQL v5.0
14:20:27,869 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
14:20:27,869 [main] INFO   jTPCC : Term-00,  (c) 2003, Raul Barbosa
14:20:27,869 [main] INFO   jTPCC : Term-00,  (c) 2004-2016, Denis Lussier
14:20:27,872 [main] INFO   jTPCC : Term-00,  (c) 2016, Jan Wieck
14:20:27,872 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
14:20:27,872 [main] INFO   jTPCC : Term-00,
14:20:27,872 [main] INFO   jTPCC : Term-00, db=oceanbase
14:20:27,872 [main] INFO   jTPCC : Term-00, driver=com.mysql.jdbc.Driver
14:20:27,872 [main] INFO   jTPCC : Term-00, conn=jdbc:mysql://x.x.x.222:2883/test?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=30000000&useSSL=false
14:20:27,872 [main] INFO   jTPCC : Term-00, user=root@tt
14:20:27,872 [main] INFO   jTPCC : Term-00,
14:20:27,872 [main] INFO   jTPCC : Term-00, warehouses=10
14:20:27,873 [main] INFO   jTPCC : Term-00, terminals=100
14:20:27,874 [main] INFO   jTPCC : Term-00, runMins=5
14:20:27,874 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=0
14:20:27,874 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=true
14:20:27,874 [main] INFO   jTPCC : Term-00,
14:20:27,874 [main] INFO   jTPCC : Term-00, newOrderWeight=45
14:20:27,875 [main] INFO   jTPCC : Term-00, paymentWeight=43
14:20:27,875 [main] INFO   jTPCC : Term-00, orderStatusWeight=4
14:20:27,875 [main] INFO   jTPCC : Term-00, deliveryWeight=4
14:20:27,875 [main] INFO   jTPCC : Term-00, stockLevelWeight=4
14:20:27,875 [main] INFO   jTPCC : Term-00,
14:20:27,875 [main] INFO   jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
14:20:27,875 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
14:20:27,875 [main] INFO   jTPCC : Term-00,
14:20:27,891 [main] INFO   jTPCC : Term-00, copied /home/ob/tmp/props.oceanbase to my_result_2024-03-17_142027/run.properties
14:20:27,891 [main] INFO   jTPCC : Term-00, created my_result_2024-03-17_142027/data/runInfo.csv for runID 1
14:20:27,891 [main] INFO   jTPCC : Term-00, writing per transaction results to my_result_2024-03-17_142027/data/result.csv
14:20:27,892 [main] INFO   jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
14:20:27,892 [main] INFO   jTPCC : Term-00, osCollectorInterval=1
14:20:27,892 [main] INFO   jTPCC : Term-00, osCollectorSSHAddr=null
14:20:27,892 [main] INFO   jTPCC : Term-00, osCollectorDevices=null
14:20:27,953 [main] INFO   jTPCC : Term-00,
14:20:28,239 [main] INFO   jTPCC : Term-00, C value for C_LAST during load: 192                                                          Term-00, 14:25:29,121 [Thread-5] INFO   jTPCC : Term-00, mTOTAL: 1116984    Memory Usage: 95MB / 730MB                                                     14:25:29,121 [Thread-5] INFO   jTPCC : Term-00,                                                                                                   14:25:29,121 [Thread-5] INFO   jTPCC : Term-00, Measured tpmC (NewOrders) = 15185.13
14:25:29,121 [Thread-5] INFO   jTPCC : Term-00, Measured tpmTOTAL = 33746.42
14:25:29,121 [Thread-5] INFO   jTPCC : Term-00, Session Start     = 2024-03-17 14:20:28
14:25:29,121 [Thread-5] INFO   jTPCC : Term-00, Session End       = 2024-03-17 14:25:29
14:25:29,122 [Thread-5] INFO   jTPCC : Term-00, Transaction Count = 168883
TPC-C Result
Measured tpmC (NewOrders)   : 15185.13
Measured tpmTOTAL           : 33746.42
Session Start               : 2024-03-17 14:20:28
Session End                 : 2024-03-17 14:25:29
Transaction Count           : 168883

Recover ok
Trace ID: eeb13702-e424-11ee-aaa8-1c697a639d50
If you want to view detailed obd logs, please run: obd display-trace eeb13702-e424-11ee-aaa8-1c697a639d50

手动部署 BenchmarkSQL 主要步骤:

git clone https://github.com/obpilot/benchmarksql-5.0.git
cd benchmarksql-5.0/run
vi props.ob  # 修改成实际的连接信息,设置主要运行参数
sh runSQL.sh props.ob sql.oceanbase/tableCreates.sql  # 创建表结构
sh runSQL.sh props.ob sql.oceanbase/indexCreates.sql  # 创建索引
sh runLoader.sh props.ob # 装载数据
sh runBenchmark.sh props.ob # 执行测试

跑tpcc时数据库的负载情况如下:

查找 TOP SQL

查询某段时间内请求次数排在 TOP N 的 SQL:

SELECT/*+ PARALLEL(15)*/ SQL_ID,PLAN_ID, COUNT(*) AS QPS, AVG(t1.elapsed_time) RT 
                FROM oceanbase.GV$OB_SQL_AUDIT t1 WHERE tenant_id = 1002 AND 
                IS_EXECUTOR_RPC = 0 AND time_to_usec(DATE_SUB(current_timestamp, INTERVAL 10 MINUTE) ) AND
                request_time < time_to_usec (now()) 
                GROUP BY t1.sql_id ORDER BY QPS DESC LIMIT 10;
+----------------------------------+---------+------+----------+
| SQL_ID                           | PLAN_ID | QPS  | RT       |
+----------------------------------+---------+------+----------+
| A460265EC2F0763A15DD27CE9E4E2200 |    4518 | 4771 |  59.6087 |
| 7229213613983BC5FDA15AD11EC70D01 |    3206 | 1440 | 340.2958 |
| E1F2BDA1D7391B757859ED3704E5AFB7 |    3213 | 1440 | 125.9278 |
| 2B5697196EDFE9E97FA3384F581178F5 |    3214 | 1440 |  87.7139 |
| FFFCA4D67EA0A788813031B8BBC3B329 |       0 |  301 |  34.6412 |
| 54B5A5861DAF78F52D9ADFBEE83D35B5 |    3202 |  152 |  94.4276 |
| 7F4A525CC8F849F6527F4911CC4BC348 |    3189 |  152 | 121.2303 |
| BB01ECB7605AE31CBC65E6949D84B235 |    3195 |  152 | 220.9079 |
| E86A0CA8BE3F21A2FBC9F1F9855075A1 |    3184 |  152 | 824.8158 |
| FC3FED8CCB2946DE54F1C5BA3656023C |    3181 |  152 | 501.9671 |
+----------------------------------+---------+------+----------+
10 rows in set (0.012 sec)

查询某段时间内平均 执行时间 排在 TOP N 的 SQL(可根据执行时间定位慢SQL):

SELECT/*+ PARALLEL(15)*/ SQL_ID,PLAN_ID, COUNT(*) AS QPS, AVG(t1.elapsed_time) RT   
                FROM oceanbase.GV$OB_SQL_AUDIT t1   
                WHERE tenant_id = 1002 AND IS_EXECUTOR_RPC = 0    
                AND request_time > time_to_usec(DATE_SUB(current_timestamp, INTERVAL 10 MINUTE) )
                GROUP BY t1.sql_id ORDER BY RT DESC LIMIT 10;

根据sql_id找到对应的SQL文本:

obclient [(none)]> select query_sql from oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT where sql_id='A460265EC2F0763A15DD27CE9E4E2200';
+---------------------------------------------------------------------------+
| query_sql                                                                 |
+---------------------------------------------------------------------------+
| SELECT i_price, i_name, i_data     FROM bmsql_item     WHERE i_id = 14972 |
+---------------------------------------------------------------------------+
1 row in set (0.005 sec)

分析执行计划

我们获取平均执行时间最长的三条SQL,分析他们的实际执行计划和解析执行计划。

先找出TOP SQL:

obclient [(none)]> SELECT/*+ PARALLEL(15)*/ SQL_ID,PLAN_ID, COUNT(*) AS QPS, AVG(t1.elapsed_time) RT
    ->                 FROM oceanbase.GV$OB_SQL_AUDIT t1
    ->                 WHERE tenant_id = 1002 AND IS_EXECUTOR_RPC = 0
    ->                 AND request_time > time_to_usec(DATE_SUB(current_timestamp, INTERVAL 10 MINUTE) )
    ->                 GROUP BY t1.sql_id ORDER BY RT DESC LIMIT 3;
+----------------------------------+---------+------+------------+
| SQL_ID                           | PLAN_ID | QPS  | RT         |
+----------------------------------+---------+------+------------+
| 89B757D18634B472A21A0EB8EB83ECE3 |    4035 |    1 | 11747.0000 |
| F59A700FA168324279B0DBC25E19760F |    3217 |    8 |  5686.6250 |
| C9FA41444B84AF63AD4FB24B9252F1A6 |    4025 |    7 |  2694.7143 |
+----------------------------------+---------+------+------------+
3 rows in set (0.012 sec)

第一条sql_id找出对应的SQL文本,再手动获取执行计划:

obclient [tpcc]> explain SELECT count(*) AS low_stock FROM (    SELECT s_w_id, s_i_id, s_quantity         FROM bmsql_stock         WHERE s_w_id = 4 AND s_quantity < 17 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 = 4 AND d_id = 9         )     ) ;
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| ================================================================================                                                                         |
| |ID|OPERATOR                            |NAME            |EST.ROWS|EST.TIME(us)|                                                                         |
| --------------------------------------------------------------------------------                                                                         |
| |0 |SCALAR GROUP BY                     |                |1       |269         |                                                                         |
| |1 |└─HASH RIGHT SEMI JOIN              |                |15      |269         |                                                                         |
| |2 |  ├─SUBPLAN SCAN                    |VIEW1           |15      |24          |                                                                         |
| |3 |  │ └─NESTED-LOOP JOIN              |                |15      |24          |                                                                         |
| |4 |  │   ├─TABLE GET                   |bmsql_district  |1       |3           |                                                                         |
| |5 |  │   └─DISTRIBUTED TABLE RANGE SCAN|bmsql_order_line|1       |21          |                                                                         |
| |6 |  └─TABLE RANGE SCAN                |bmsql_stock     |1035    |156         |                                                                         |
| ================================================================================                                                                         |
| Outputs & filters:                                                                                                                                       |
| -------------------------------------                                                                                                                    |
|   0 - output([T_FUN_COUNT(*)]), filter(nil), rowset=256                                                                                                  |
|       group(nil), agg_func([T_FUN_COUNT(*)])                                                                                                             |
|   1 - output(nil), filter(nil), rowset=256                                                                                                               |
|       equal_conds([bmsql_stock.s_i_id = VIEW1.ol_i_id]), other_conds(nil)                                                                                |
|   2 - output([VIEW1.ol_i_id]), filter(nil), rowset=256                                                                                                   |
|       access([VIEW1.ol_i_id])                                                                                                                            |
|   3 - output([bmsql_order_line.ol_i_id]), filter(nil), rowset=256                                                                                        |
|       conds(nil), nl_params_([bmsql_district.d_next_o_id(:0)]), use_batch=false                                                                          |
|   4 - output([bmsql_district.d_next_o_id]), filter([bmsql_district.d_next_o_id > bmsql_district.d_next_o_id - 20]), rowset=256                           |
|       access([bmsql_district.d_next_o_id]), partitions(p0)                                                                                               |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                        |
|       range_key([bmsql_district.d_w_id], [bmsql_district.d_id]), range[4,9 ; 4,9],                                                                       |
|       range_cond([bmsql_district.d_w_id = 4], [bmsql_district.d_id = 9])                                                                                 |
|   5 - output([bmsql_order_line.ol_i_id]), filter(nil), rowset=256                                                                                        |
|       access([bmsql_order_line.ol_i_id]), partitions(p0)                                                                                                 |
|       is_index_back=false, is_global_index=false,                                                                                                        |
|       range_key([bmsql_order_line.ol_w_id], [bmsql_order_line.ol_d_id], [bmsql_order_line.ol_o_id], [bmsql_order_line.ol_number]), range(MIN ; MAX),     |
|       range_cond([bmsql_order_line.ol_w_id = 4], [bmsql_order_line.ol_d_id = 9], [bmsql_order_line.ol_o_id >= :0 - 20], [bmsql_order_line.ol_o_id < :0]) |
|   6 - output([bmsql_stock.s_i_id]), filter([bmsql_stock.s_quantity < 17]), rowset=256                                                                    |
|       access([bmsql_stock.s_i_id], [bmsql_stock.s_quantity]), partitions(p0)                                                                             |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                        |
|       range_key([bmsql_stock.s_w_id], [bmsql_stock.s_i_id]), range(4,MIN ; 4,MAX),                                                                       |
|       range_cond([bmsql_stock.s_w_id = 4])                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
36 rows in set (0.088 sec)

它的真实执行计划:

select * from oceanbase.V$OB_PLAN_CACHE_PLAN_EXPLAIN where tenant_id=1002 and  plan_id=3217 ;

执行计划重要信息:

  • OPERATOR,算子的名称,比如全表扫描TABLE SCAN,真实执行计划会加上PHY_前缀
  • NAME,算子操作的对象名称,比如表名、索引名等
  • ROWS,真实执行计划里面代表实际数据行数,explain里面代表估算的行数(EST.ROWS)
  • COST,算子执行时间,单位是微秒,explain里面的EST.TIME为估算时间
  • PROPERTY,算子执行的详细过程

这里在查询执行计划时有一个坑要注意,oceanbase.V$OB_PLAN_CACHE_PLAN_EXPLAIN视图只能在SQL执行的observer节点上查询,因为PLAN CACHE是按节点缓存的,通过obproxy登录的话往往查不到想要的数据,要直连observer。

标签:INFO,00,初体验,14,查看,OceanBase,Term,jTPCC,id
From: https://www.cnblogs.com/hohoa/p/18078875

相关文章

  • 使用 gdb 查看 coredump 文件的 backtrace
    问题背景程序退出过程中卡死,但是一旦开启log就无法复现问题。于是考虑获取程序卡死时的coredump文件,分析其调用栈。一种方法是在程序卡死时,用kill命令向其发送一个信号,使程序产生coredump文件。前提Target上可以产生core文件(如/tmp/<executable-name>-<pid>.core),......
  • 五 超级数据查看器 讲解稿 列表功能2
    五 超级数据查看器 讲解稿 列表功能2点击此处以新页面打开B站播放教学视频点此下载 百度手机助手 下载地址4讲解稿全文:大家好,今天我们讲解一下,超级数据查看器列表界面,分为1-2两集。这是第二集继续讲解弹出式菜单这里的条目样式,是用来设置界面的样式......
  • OceanBase初体验之从MySQL迁移数据到OceanBase集群
    前置条件MySQL环境OceanBase环境测试用的表结构和一些数据先在源端MySQL用如下脚本创建测试表,以及写入10000条数据用于迁移测试。usetest;CREATETABLEstudents(`id`intNOTNULLPRIMARYKEY,`name`varchar(255),`code`varchar(20),`class`varch......
  • 查看宝塔mysql二进制文件 mysqlbinlog
    mysqlbinlog执行文件位置/www/server/mysql/binmysql-bin二进制日志位置/www/server/data/#/www/server/data/mysql-bin.000060把二进制导出为.sql文件#建议/www/server/data/mysql-bin.000060文件cp到mysqlbinlog文件执行目录并设置权限为www755./mysqlbinlogmysql-......
  • 一个命令查看自己的WIFI密码
    一个命令查看自己的WIFI密码忘记wifi密码怎么办?一个命令查看自己的wifi密码。一、打开命令行使用快捷键“Win+R”,打开运行窗口,输入“cmd”后回车即可。二、输入命令networkshell命令输入命令networkshell,简称netsh;再输入wlan,也就是wifi;最后输入showprofile。......
  • k8s 查看容器资源使用率
    dockerstats1b4043389e79CONTAINERIDNAMECPU%MEMUSAGE/LIMITMEM%NETI/OBLOCKI/OPIDS1b4043389e79k8s_xxx-node_xxxx-node-1_default_6cef3b8e-e1f8-11ee-b58d-060eb4000e9d_01.02%3.021GiB/16GiB18.88%0B/......
  • 通过 Metrics Server 查看 Kubernetes 资源指标
    1.简介MetricsServer是一个用于Kubernetes集群的监控工具,它用于收集、存储和提供关于集群中各种资源的度量数据。MetricsServer是Kubernetes中一个核心的指标收集器,可以提供关于CPU和内存使用情况、节点资源利用率以及其他重要指标的信息。它主要用于水平自动扩展(Horiz......
  • html中如何让网页禁用右键禁止查看源代码
    在网页中,辛辛苦苦写的文章,被别人复制粘贴给盗用去另很多站长感到非常无奈,通常大家复制都会使用选取右键复制,或CTRL+C等方式,下面介绍几种禁止鼠标右键代码,可减少网页上文章被抄袭的几率,当然对高手来说,破解也很简单,不管怎么样,我们还是一起试试吧。使用方法:以下代码加入到<scrip......
  • Linux命令:查看系统信息
    命令汇总unamefilehostnamectllshwlscpucat/proc/目录下的文件topdfcat/etc/目录下的文件pslastidwhowhoamiifconfignetstatiptablesfirewall-cmd 内核信息#uname-a#uname-srm#uname-m#uname-r#file/boot/vmlinuz-$(uname-r) #hostn......
  • 多线程面试-基础篇(多线程系列连更,后续可查看合集)
    问:线程和进程的区别?答:根本区别:进程是系统划分资源的最小单位,线程是cpu执行和调度的最基本单位。进程包含线程,进程内可包含多个线程。问:为什么要使用多线程?答:提高cpu利用率,并且线程切换和调度成本远小于进程。问:什么是线程死锁?如何避免线程死锁?线程死锁是指,在多线程情况下,A......