作者简介：罗呈祥，数据库管理员。
1.环境需求
• 单副本集群一套（1个zone，三台机器）
• BenchmarkSQL
2. 准备 BenchmarkSQL
注意：需要 java 环境，不低于1.8.0 .
下载地址： https://github.com/obpilot/benchmarksql-5.0
jdbc下载地址： https://help.aliyun.com/document_detail/212815.html
创建测试用户和数据库：
MySQL [oceanbase]> create database tpcc; Query OK, 1 row affected (0.102 sec) MySQL [oceanbase]> create user tpcc identified by 'ABcd12@#'; Query OK, 0 rows affected (0.095 sec) MySQL [oceanbase]> grant all on *.* to tpcc; Query OK, 0 rows affected (0.095 sec)
修改配置文件 props.ob (在run目录下)
db=oracle driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver conn=jdbc:oceanbase://192.168.1.115:2883/tpcc?useUnicode=true&characterEncoding=utf-8 user=tpcc@mytest#obcluster password=ABcd12@# warehouses=10 # 仓库数 loadWorkers=10 # 并发数 terminals=100 //To run specified transactions per terminal- runMins must equal zero runTxnsPerTerminal=0 //To run for specified minutes- runTxnsPerTerminal must equal zero runMins=15 # 测试持续15分钟 //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
3. 准备数据
sql 文件在 benchmarksql-5.0-master/run/sql.oceanbase 中，看了一些建表语句，使用了 varchar2 类型，ob为 mysql 模式，需要修改：
cp tableCreates.sql tableCreates_1zone.sql sed -i 's/varchar2/varchar/g' tableCreates_1zone.sql # 建表 ./runSQL.sh props.ob sql.oceanbase/tableCreates_1zone.sql #加载数据 ./runLoader.sh props.ob
如果有报事务超时的，需要调整事务超时参数：
MySQL [oceanbase]> show variables like '%timeout%'; +---------------------+------------------+ | Variable_name | Value | +---------------------+------------------+ | connect_timeout | 10 | | interactive_timeout | 28800 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | ob_pl_block_timeout | 3216672000000000 | | ob_query_timeout | 10000000 | | ob_trx_idle_timeout | 120000000 | | ob_trx_lock_timeout | -1 | | ob_trx_timeout | 100000000 | | wait_timeout | 28800 | +---------------------+------------------+ 11 rows in set (0.021 sec) MySQL [oceanbase]> set global ob_trx_timeout=10000000000; Query OK, 0 rows affected (0.056 sec) MySQL [oceanbase]> set global ob_query_timeout=100000000; Query OK, 0 rows affected (0.039 sec)
从 OCP 上下性能监控：
可以看到此时的 QPS，响应时间等有明显升高。
数据加载完成之后，创建索引。
./runSQL.sh props.ob sql.oceanbase/indexCreates.sql # ------------------------------------------------------------ # Loading SQL file sql.oceanbase/indexCreates.sql # ------------------------------------------------------------ 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. 测试
./runBenchmark.sh props.ob
等待结果输出：
测试的详细信息在 my_result_2022-02-07_121917/data/result.csv 文件中，按需查看。
5. 查看执行计划
找执行过的 sql：
执行过的SQL在/home/admin/logs/obproxy/log/obproxy_digest.log中有记录
grep obcluster:eeotest:tpcc obproxy_digest.log | grep SELECT
查看基础执行计划：
explain SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance FROM bmsql_customer WHERE c_w_id = 7 AND c_d_id = 2 AND c_id = 856;
查看更为详细的执行计划：
MySQL [tpcc]> explain extended SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance FROM bmsql_customer WHERE c_w_id = 7 AND c_d_id = 2 AND c_id = 856; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ============================================ |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------- |0 |TABLE GET|bmsql_customer|1 |54 | ============================================ Outputs & filters: ------------------------------------- 0 - output([bmsql_customer.c_first(0x7f84190ded70)], [bmsql_customer.c_middle(0x7f84190df430)], [bmsql_customer.c_last(0x7f84190df950)], [bmsql_customer.c_street_1(0x7f84190dfe70)], [bmsql_customer.c_street_2(0x7f84190e0390)], [bmsql_customer.c_city(0x7f84190e08b0)], [bmsql_customer.c_state(0x7f84190e0dd0)], [bmsql_customer.c_zip(0x7f84190e1470)], [bmsql_customer.c_phone(0x7f84190e1990)], [bmsql_customer.c_since(0x7f84190e1eb0)], [bmsql_customer.c_credit(0x7f84190e23d0)], [bmsql_customer.c_credit_lim(0x7f84190e28f0)], [bmsql_customer.c_discount(0x7f84190e2e10)], [bmsql_customer.c_balance(0x7f84190e34b0)]), filter(nil), access([bmsql_customer.c_first(0x7f84190ded70)], [bmsql_customer.c_middle(0x7f84190df430)], [bmsql_customer.c_last(0x7f84190df950)], [bmsql_customer.c_street_1(0x7f84190dfe70)], [bmsql_customer.c_street_2(0x7f84190e0390)], [bmsql_customer.c_city(0x7f84190e08b0)], [bmsql_customer.c_state(0x7f84190e0dd0)], [bmsql_customer.c_zip(0x7f84190e1470)], [bmsql_customer.c_phone(0x7f84190e1990)], [bmsql_customer.c_since(0x7f84190e1eb0)], [bmsql_customer.c_credit(0x7f84190e23d0)], [bmsql_customer.c_credit_lim(0x7f84190e28f0)], [bmsql_customer.c_discount(0x7f84190e2e10)], [bmsql_customer.c_balance(0x7f84190e34b0)]), partitions(p0), is_index_back=false, range_key([bmsql_customer.c_w_id(0x7f8418ee98e0)], [bmsql_customer.c_d_id(0x7f84190dda70)], [bmsql_customer.c_id(0x7f84190de850)]), range[7,2,856 ; 7,2,856], range_cond([bmsql_customer.c_w_id(0x7f8418ee98e0) = 7(0x7f8418ee9260)], [bmsql_customer.c_d_id(0x7f84190dda70) = 2(0x7f84190dd3f0)], [bmsql_customer.c_id(0x7f84190de850) = 856(0x7f84190de1d0)]) Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "tpcc.bmsql_customer"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- bmsql_customer:table_rows:300000, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:local_storage, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback Parameters ------------------------------------- | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.005 sec)
从TABLE GET ， is_index_back=false 可以看出走了索引，没有回表查，索引覆盖了，unique_index_without_indexback。
再找一条 SQL 查看执行计划：
可以看到上面两条 SQL 的 OPERATOR 都是 TABLE GET ，表示都是走主键定位，可以说就是最优了。
6. 创建 outline
outline 有两种语法：
注意：连接 ob 时命令行要指定 -c，否则会忽略 hint
/* 使用 SQL_TEXT 创建 Outline */ CREATE [OR REPLACE] OUTLINE outline_name ON stmt [ TO target_stmt ] /* 使用 SQL_ID 创建 Outline */ CREATE OUTLINE outline_name ON sql_id USING HINT hint;
如果使用 SQL_ID 来创建，需要查询 SQL_ID.
# 以 SELECT d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = 9 AND d_id = 3; 为例子。 use oceanbase MySQL [oceanbase]> select * from gv$sql where SQL_TEXT like 'SELECT d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id =%'\G *************************** 1. row *************************** CON_ID: 1001 SVR_IP: 10.1.38.112 SVR_PORT: 2882 PLAN_ID: 7334 SQL_ID: 8D9BE1431EDD25A3120C78D89F3CDADC TYPE: 1 SQL_TEXT: SELECT d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = ? AND d_id = ? PLAN_HASH_VALUE: 10891013227342761743 FIRST_LOAD_TIME: 2022-02-07 17:49:03.682029 LAST_ACTIVE_TIME: 2022-02-07 17:49:03.682528 AVG_EXE_USEC: 3387 SLOWEST_EXE_TIME: 2022-02-07 17:49:03.682528 SLOWEST_EXE_USEC: 3387 SLOW_COUNT: 0 HIT_COUNT: 0 PLAN_SIZE: 48848 EXECUTIONS: 1 DISK_READS: 0 DIRECT_WRITES: 0 BUFFER_GETS: 0 APPLICATION_WAIT_TIME: 0 CONCURRENCY_WAIT_TIME: 0 USER_IO_WAIT_TIME: 0 ROWS_PROCESSED: 1 ELAPSED_TIME: 3387 CPU_TIME: 3295 1 row in set (0.088 sec) # 创建outline CREATE OUTLINE bmsql_district_ol1 on '8D9BE1431EDD25A3120C78D89F3CDADC' using hint /*+ parallel(4)*/ ; # 查看outline是否生效 MySQL [tpcc]> SELECT * FROM oceanbase.gv$outline WHERE OUTLINE_NAME = 'bmsql_district_ol1'; +-----------+------------------+------------------+---------------+--------------------+-------------------+----------+----------------+-------------+ | tenant_id | database_id | outline_id | database_name | outline_name | visible_signature | sql_text | outline_target | outline_sql | +-----------+------------------+------------------+---------------+--------------------+-------------------+----------+----------------+-------------+ | 1001 | 1100611139404829 | 1100611139404779 | tpcc | bmsql_district_ol1 | | | | | +-----------+------------------+------------------+---------------+--------------------+-------------------+----------+----------------+-------------+ 1 row in set (0.005 sec) # 发现并没有生效。 # 使用SQL语句创建outline MySQL [tpcc]> create outline bmsql_district_ol2 on SELECT/*+ parallel(4) */ d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = 9 AND d_id = 3; Query OK, 0 rows affected (0.046 sec) MySQL [tpcc]> SELECT * FROM oceanbase.gv$outline WHERE OUTLINE_NAME = 'bmsql_district_ol1'; +-----------+------------------+------------------+---------------+--------------------+-------------------+----------+----------------+-------------+ | tenant_id | database_id | outline_id | database_name | outline_name | visible_signature | sql_text | outline_target | outline_sql | +-----------+------------------+------------------+---------------+--------------------+-------------------+----------+----------------+-------------+ | 1001 | 1100611139404829 | 1100611139404779 | tpcc | bmsql_district_ol1 | | | | | +-----------+------------------+------------------+---------------+--------------------+-------------------+----------+----------------+-------------+ 1 row in set (0.014 sec) MySQL [tpcc]> SELECT * FROM oceanbase.gv$outline WHERE OUTLINE_NAME = 'bmsql_district_ol2'; +-----------+------------------+------------------+---------------+--------------------+-----------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tenant_id | database_id | outline_id | database_name | outline_name | visible_signature | sql_text | outline_target | outline_sql | +-----------+------------------+------------------+---------------+--------------------+-----------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1001 | 1100611139404829 | 1100611139404780 | tpcc | bmsql_district_ol2 | SELECT d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = ? AND d_id = ? | SELECT/*+ parallel(4) */ d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = 9 AND d_id = 3 | | SELECT /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "tpcc.bmsql_district"@"SEL$1") PARALLEL(4) END_OUTLINE_DATA*/d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = 9 AND d_id = 3 | +-----------+------------------+------------------+---------------+--------------------+-----------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.045 sec) # 手动指定hint，查看执行计划 MySQL [tpcc]> explain extended SELECT/*+ parallel(4) */ d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = 9 AND d_id = 3; +-----------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-----------------------------------------------------------------------------------------------------------------------+ | ====================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------------ |0 |PX COORDINATOR | |1 |53 | |1 | EXCHANGE OUT DISTR|:EX10000 |1 |53 | |2 | PX BLOCK ITERATOR| |1 |53 | |3 | TABLE GET |bmsql_district|1 |53 | ====================================================== Outputs & filters: ------------------------------------- 0 - output([bmsql_district.d_tax(0x7faecea8aab0)], [bmsql_district.d_next_o_id(0x7faecea8afd0)]), filter(nil) 1 - output([bmsql_district.d_tax(0x7faecea8aab0)], [bmsql_district.d_next_o_id(0x7faecea8afd0)]), filter(nil), dop=4 2 - output([bmsql_district.d_tax(0x7faecea8aab0)], [bmsql_district.d_next_o_id(0x7faecea8afd0)]), filter(nil) 3 - output([bmsql_district.d_tax(0x7faecea8aab0)], [bmsql_district.d_next_o_id(0x7faecea8afd0)]), filter(nil), access([bmsql_district.d_tax(0x7faecea8aab0)], [bmsql_district.d_next_o_id(0x7faecea8afd0)]), partitions(p0), is_index_back=false, range_key([bmsql_district.d_w_id(0x7faecea6e9e0)], [bmsql_district.d_id(0x7faecea8a590)]), range[9,3 ; 9,3], range_cond([bmsql_district.d_w_id(0x7faecea6e9e0) = 9(0x7faecea6e360)], [bmsql_district.d_id(0x7faecea8a590) = 3(0x7faecea89f10)]) Used Hint: ------------------------------------- /*+ PARALLEL(4) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "tpcc.bmsql_district"@"SEL$1") PARALLEL(4) END_OUTLINE_DATA */ Plan Type: ------------------------------------- DISTRIBUTED Optimization Info: ------------------------------------- bmsql_district:table_rows:100, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:local_storage, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback Parameters ------------------------------------- | +-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.004 sec)
可能是outline并没有在执行计划中展示：
通过查询gv$plan_cache_plan_stat可以看到outline_id不为-1，表示执行计划已经绑定。
MySQL [tpcc]> select * from oceanbase.gv$plan_cache_plan_stat where query_sql like 'SELECT * FROM bmsql_district2 WHERE d_w_id = 9 AND d_id = 3%'\G
*************************** 1. row ***************************
tenant_id: 1001
svr_ip: 10.1.38.113
svr_port: 2882
plan_id: 6060
sql_id: 969DD3AAE35A71E46B81C29AE226E692
type: 3
is_bind_sensitive: 0
is_bind_aware: 0
db_id: 18446744073709551615
statement: SELECT * FROM bmsql_district2 WHERE d_w_id = ? AND d_id = ?
query_sql: SELECT * FROM bmsql_district2 WHERE d_w_id = 9 AND d_id = 3
special_params:
param_infos: {1,0,0,0,5},{1,0,0,0,5}
sys_vars: 45,4194304,2,4,1,0,0,32,3,1,0,1,1,0,10485760,1,1,0,1,BINARY,BINARY,AL32UTF8,AL32UTF8,BYTE,FALSE,1,100,64,200,0,13,NULL,1,1,1
plan_hash: 4355160801114405601
first_load_time: 2022-02-07 20:29:27.145922
schema_version: 1644236952315912
merged_version: 25
last_active_time: 2022-02-07 20:29:27.157579
avg_exe_usec: 23869
slowest_exe_time: 2022-02-07 20:29:27.157579
slowest_exe_usec: 23869
slow_count: 0
hit_count: 0
plan_size: 81616
executions: 1
disk_reads: 0
direct_writes: 0
buffer_gets: 5
application_wait_time: 0
concurrency_wait_time: 2008
user_io_wait_time: 0
rows_processed: 1
elapsed_time: 23869
cpu_time: 20313
large_querys: 0
delayed_large_querys: 0
delayed_px_querys: 0
outline_version: 1644236952309632
outline_id: 1100611139404785
outline_data: /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "tpcc.bmsql_district2"@"SEL$1") PARALLEL(4) END_OUTLINE_DATA*/
acs_sel_info:
table_scan: 0
evolution: 0
evo_executions: 0
evo_cpu_time: 0
timeout_count: 0
ps_stmt_id: -1
sessid: 0
temp_tables:
is_use_jit: 0
object_type: SQL_PLAN
hints_info: PARALLEL(4)
hints_all_worked: 1
pl_schema_id: NULL
is_batched_multi_stmt: 0
1 row in set (0.029 sec)
select * from oceanbase.gv$outline ;
+-----------+------------------+------------------+---------------+--------------------+------------------------------------------------------------+------------------------------------------------------------------------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tenant_id | database_id | outline_id | database_name | outline_name | visible_signature | sql_text | outline_target | outline_sql |
+-----------+------------------+------------------+---------------+--------------------+------------------------------------------------------------+------------------------------------------------------------------------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1001 | 1100611139404829 | 1100611139404783 | tpcc | bmsql_district_ol1 | SELECT * FROM bmsql_district WHERE d_w_id = ? AND d_id = ? | SELECT/*+ parallel(4) */ * FROM bmsql_district WHERE d_w_id = 9 AND d_id = 3 | | SELECT /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "tpcc.bmsql_district"@"SEL$1") PARALLEL(4) END_OUTLINE_DATA*/* FROM bmsql_district WHERE d_w_id = 9 AND d_id = 3 |
| 1001 | 1100611139404829 | 1100611139404784 | tpcc | outline2 | | | | |
| 1001 | 1100611139404829 | 1100611139404785 | tpcc | outline3 | | | | |
+-----------+------------------+------------------+---------------+--------------------+------------------------------------------------------------+------------------------------------------------------------------------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.026 sec)
OceanBase 社区版入门到实战教程直播正在进行中~
快和小伙伴一起进群交流学习吧~
加入直播群方式一：
钉钉群号 3582 5151
加入直播群方式二：
扫码下方二维码加入
