查看oceanbase执行计划

原创 lqkitten 2022-02-20

1103

利用tpcc-mysql工具对oceanbase压测，并查看sql执行计划

一、测试环境

三台openEuler release 20.03 (LTS-SP3)通过obd部署的1-1-1集群。
[root@node1 rx-0]# cat /etc/openEuler-release
openEuler release 20.03 (LTS-SP3)
[root@node1 rx-0]# df -Th
Filesystem                 Type      Size  Used Avail Use% Mounted on
devtmpfs                   devtmpfs  7.6G     0  7.6G   0% /dev
tmpfs                      tmpfs     7.6G     0  7.6G   0% /dev/shm
tmpfs                      tmpfs     7.6G   25M  7.6G   1% /run
tmpfs                      tmpfs     7.6G     0  7.6G   0% /sys/fs/cgroup
/dev/mapper/openeuler-root ext4       23G   20G  2.2G  90% /
tmpfs                      tmpfs     7.6G     0  7.6G   0% /tmp
/dev/sdb                   xfs        12G  2.2G  9.8G  19% /fcfs
/dev/sda1                  ext4      976M  244M  665M  27% /boot
tmpfs                      tmpfs     1.6G     0  1.6G   0% /run/user/0
tmpfs                      tmpfs     1.6G     0  1.6G   0% /run/user/1001

复制

二、准备数据加载工具tpcc-mysql

git clone https://github.com/Percona-Lab/tpcc-mysql.git
cd tpcc-mysql/src
make 

复制

三、测试环境相关设置

1、修改sys超时参数

obclient -uroot@sys  -pobroot123 -h127.1 -P2883 -c -A oceanbase
alter system set enable_auto_leader_switch=false;
alter system set enable_one_phase_commit=false;
alter system set enable_monotonic_weak_read = true;
alter system set weak_read_version_refresh_interval='5s';
alter system _ob_minor_merge_schedule_interval='5s';
alter system set memory_limit_percentage = 90;  
alter system set memstore_limit_percentage = 55;
alter system set freeze_trigger_percentage = 70;
alter system set minor_freeze_times = 50;      
alter system set minor_warm_up_duration_time = 0;
alter system set merge_thread_count = 32;
alter system set minor_merge_concurrency = 8;
alter system set _mini_merge_concurrency = 4;


复制

２、修改PROXY 参数

alter proxyconfig set proxy_mem_limited='4G'; --防止 oom，可根据实际环境动态调整
alter proxyconfig set enable_compression_protocol=false; --关闭压缩，降低 CPU 百分率
alter proxyconfig set work_thread_num=32; -- 调整工作线程数，寻找最优性能
alter proxyconfig set enable_compression_protocol=false;
alter proxyconfig set enable_metadb_used=false;
alter proxyconfig set enable_standby=false;
alter proxyconfig set enable_strict_stat_time=false;
alter proxyconfig set use_local_dbconfig=true;


复制

３. 查看当前集群资源使用情况：

MySQL [oceanbase]> select
    -> a.zone,
    -> concat(a.svr_ip, ':', a.svr_port) observer,
    -> cpu_total,
    -> (cpu_total-cpu_assigned) cpu_free,
    -> round(mem_total / 1024 / 1024 / 1024) mem_total_gb,
    -> round((mem_total-mem_assigned)/ 1024 / 1024 / 1024) mem_free_gb,
    -> usec_to_time(b.last_offline_time) last_offline_time,
    -> usec_to_time(b.start_service_time) start_service_time,
    -> b.status,
    -> b.build_version ,
    -> usec_to_time(a.stop_time) stop_time
    -> from
    -> __all_virtual_server_stat a
    -> join __all_server b on
    -> (a.svr_ip = b.svr_ip
    -> and a.svr_port = b.svr_port)
    -> order by
    -> a.zone,
    -> a.svr_ip;

+-------+-------------------+-----------+----------+--------------+-------------+----------------------------+----------------------------+--------+----------------------------------------------------------------------------------------+----------------------------+
| zone  | observer          | cpu_total | cpu_free | mem_total_gb | mem_free_gb | last_offline_time          | start_service_time         | status | build_version                                                                          | stop_time                  |
+-------+-------------------+-----------+----------+--------------+-------------+----------------------------+----------------------------+--------+----------------------------------------------------------------------------------------+----------------------------+
| zone1 | 192.168.56.7:2882 |        30 |     19.5 |           10 |           3 | 1970-01-01 08:00:00.000000 | 2022-02-20 08:22:51.343304 | active | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) | 1970-01-01 08:00:00.000000 |
| zone2 | 192.168.56.8:2882 |        30 |     19.5 |           10 |           3 | 1970-01-01 08:00:00.000000 | 2022-02-20 08:23:44.476628 | active | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) | 1970-01-01 08:00:00.000000 |
| zone3 | 192.168.56.9:2882 |        30 |     19.5 |           10 |           3 | 1970-01-01 08:00:00.000000 | 2022-02-20 08:23:36.482777 | active | 3.1.2_10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d(Dec 30 2021 02:47:29) | 1970-01-01 08:00:00.000000 |
+-------+-------------------+-----------+----------+--------------+-------------+----------------------------+----------------------------+--------+----------------------------------------------------------------------------------------+----------------------------+
3 rows in set (0.041 sec)

复制

可用资源情况

MySQL [oceanbase]>  select
	a.zone,
	concat(a.svr_ip, ':', a.svr_port) observer,
	cpu_total,
	cpu_assigned,
	(cpu_total-cpu_assigned) cpu_free,
	mem_total / 1024 / 1024 / 1024 mem_total_gb,
	mem_assigned / 1024 / 1024 / 1024 mem_assign_gb,
	(mem_total-mem_assigned)/ 1024 / 1024 / 1024 mem_free_gb
from
	__all_virtual_server_stat a
join __all_server b on
	(a.svr_ip = b.svr_ip
		and a.svr_port = b.svr_port)
order by
	a.zone,
	a.svr_ip ;
+-------+-------------------+-----------+--------------+----------+-----------------+----------------+----------------+
| zone  | observer          | cpu_total | cpu_assigned | cpu_free | mem_total_gb    | mem_assign_gb  | mem_free_gb    |
+-------+-------------------+-----------+--------------+----------+-----------------+----------------+----------------+
| zone1 | 192.168.56.7:2882 |        30 |         10.5 |     19.5 | 10.000000000000 | 7.000000000000 | 3.000000000000 |
| zone2 | 192.168.56.8:2882 |        30 |         10.5 |     19.5 | 10.000000000000 | 7.000000000000 | 3.000000000000 |
| zone3 | 192.168.56.9:2882 |        30 |         10.5 |     19.5 | 10.000000000000 | 7.000000000000 | 3.000000000000 |
+-------+-------------------+-----------+--------------+----------+-----------------+----------------+----------------+
3 rows in set (0.005 sec)


复制

MySQL [oceanbase]> select t1.name resource_pool_name, t2.`name` unit_config_name, t2.max_cpu, t2.min_cpu, t2.max_memory/1024/1024/1024 max_mem_gb, t2.min_memory/1024/1024/1024 min_mem_gb, t3.unit_id, t3.zone, concat(t3.svr_ip,':',t3.`svr_port`) observer,t4.tenant_id, t4.tenant_name
    -> from __all_resource_pool t1 join __all_unit_config t2 on (t1.unit_config_id=t2.unit_config_id)
    ->     join __all_unit t3 on (t1.`resource_pool_id` = t3.`resource_pool_id`)
    ->     left join __all_tenant t4 on (t1.tenant_id=t4.tenant_id)
    -> order by t1.`resource_pool_id`, t2.`unit_config_id`, t3.unit_id
    -> ;
+--------------------+------------------+---------+---------+----------------+----------------+---------+-------+-------------------+-----------+-------------+
| resource_pool_name | unit_config_name | max_cpu | min_cpu | max_mem_gb     | min_mem_gb     | unit_id | zone  | observer          | tenant_id | tenant_name |
+--------------------+------------------+---------+---------+----------------+----------------+---------+-------+-------------------+-----------+-------------+
| sys_pool           | sys_unit_config  |       5 |     2.5 | 2.399999999440 | 2.000000000000 |       1 | zone1 | 192.168.56.7:2882 |         1 | sys         |
| sys_pool           | sys_unit_config  |       5 |     2.5 | 2.399999999440 | 2.000000000000 |       2 | zone2 | 192.168.56.8:2882 |         1 | sys         |
| sys_pool           | sys_unit_config  |       5 |     2.5 | 2.399999999440 | 2.000000000000 |       3 | zone3 | 192.168.56.9:2882 |         1 | sys         |
| pool1              | unit1            |       8 |       4 | 5.000000000000 | 3.000000000000 |    1001 | zone1 | 192.168.56.7:2882 |      1001 | t_obdemo    |
| pool1              | unit1            |       8 |       4 | 5.000000000000 | 3.000000000000 |    1002 | zone2 | 192.168.56.8:2882 |      1001 | t_obdemo    |
| pool1              | unit1            |       8 |       4 | 5.000000000000 | 3.000000000000 |    1003 | zone3 | 192.168.56.9:2882 |      1001 | t_obdemo    |
+--------------------+------------------+---------+---------+----------------+----------------+---------+-------+-------------------+-----------+-------------+
6 rows in set (0.004 sec)



复制

修改资源单元规格，cpu,memory大小一致

MySQL [oceanbase]> alter resource unit unit1 min_cpu 8,min_memory '5g';
Query OK, 0 rows affected (0.009 sec)

复制

修改后

MySQL [oceanbase]> select t1.name resource_pool_name, t2.`name` unit_config_name, t2.max_cpu, t2.min_cpu, t2.max_memory/1024/1024/1024 max_mem_gb, t2.min_memory/1024/1024/1024 min_mem_gb, t3.unit_id, t3.zone, concat(t3.svr_ip,':',t3.`svr_port`) observer,t4.tenant_id, t4.tenant_name
    -> from __all_resource_pool t1 join __all_unit_config t2 on (t1.unit_config_id=t2.unit_config_id)
    ->     join __all_unit t3 on (t1.`resource_pool_id` = t3.`resource_pool_id`)
    ->     left join __all_tenant t4 on (t1.tenant_id=t4.tenant_id)
    -> order by t1.`resource_pool_id`, t2.`unit_config_id`, t3.unit_id
    -> ;
+--------------------+------------------+---------+---------+----------------+----------------+---------+-------+-------------------+-----------+-------------+
| resource_pool_name | unit_config_name | max_cpu | min_cpu | max_mem_gb     | min_mem_gb     | unit_id | zone  | observer          | tenant_id | tenant_name |
+--------------------+------------------+---------+---------+----------------+----------------+---------+-------+-------------------+-----------+-------------+
| sys_pool           | sys_unit_config  |       5 |     2.5 | 2.399999999440 | 2.000000000000 |       1 | zone1 | 192.168.56.7:2882 |         1 | sys         |
| sys_pool           | sys_unit_config  |       5 |     2.5 | 2.399999999440 | 2.000000000000 |       2 | zone2 | 192.168.56.8:2882 |         1 | sys         |
| sys_pool           | sys_unit_config  |       5 |     2.5 | 2.399999999440 | 2.000000000000 |       3 | zone3 | 192.168.56.9:2882 |         1 | sys         |
| pool1              | unit1            |       8 |       8 | 5.000000000000 | 5.000000000000 |    1001 | zone1 | 192.168.56.7:2882 |      1001 | t_obdemo    |
| pool1              | unit1            |       8 |       8 | 5.000000000000 | 5.000000000000 |    1002 | zone2 | 192.168.56.8:2882 |      1001 | t_obdemo    |
| pool1              | unit1            |       8 |       8 | 5.000000000000 | 5.000000000000 |    1003 | zone3 | 192.168.56.9:2882 |      1001 | t_obdemo    |
+--------------------+------------------+---------+---------+----------------+----------------+---------+-------+-------------------+-----------+-------------+
6 rows in set (0.002 sec)

MySQL [oceanbase]> \q
Bye

复制

４、租户超时参数设置

[root@node1 rx-0]# obclient -h192.168.56.7 -uroot@t_obdemo -P2883 -c -A  -p
Enter password:
Welcome to the OceanBase.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

set global ob_timestamp_service='GTS' ;
set global autocommit=ON;
set global ob_query_timeout=36000000000;
set global ob_trx_timeout=36000000000;
set global max_allowed_packet=67108864;
set global ob_sql_work_area_percentage=100;
set global parallel_max_servers=800;
set global parallel_servers_target=800;

复制

加载数据

./tpcc_start -h127.0.0.1 -P 2883 -d tpcc -utpcc@t_obdemo -w3 -c32 -r10 -l10800 -pobroot123
 650, trx: 90, 95%: 1910.877, 99%: 2234.053, max_rt: 2922.950, 91|6224.477, 9|69.716, 9|1662.910, 9|4545.140
 660, trx: 96, 95%: 2406.932, 99%: 2910.815, max_rt: 3755.202, 91|4636.709, 10|78.115, 9|1836.132, 10|5019.699
 670, trx: 87, 95%: 1876.864, 99%: 2341.551, max_rt: 2442.926, 91|6018.072, 9|61.740, 10|1913.560, 9|4638.209
 680, trx: 89, 95%: 2388.272, 99%: 3246.872, max_rt: 3330.305, 84|5604.641, 9|63.627, 9|1751.144, 9|4664.569
 690, trx: 90, 95%: 2118.133, 99%: 2218.725, max_rt: 2638.818, 96|5018.650, 9|67.924, 9|1708.576, 8|4653.324
 700, trx: 91, 95%: 2322.703, 99%: 2547.030, max_rt: 2618.710, 88|5347.387, 9|81.798, 8|1995.720, 9|4895.266
 710, trx: 87, 95%: 1943.761, 99%: 2376.861, max_rt: 2440.083, 90|5216.277, 9|87.353, 9|1576.101, 9|4399.511
 720, trx: 85, 95%: 1841.801, 99%: 2258.258, max_rt: 2284.648, 82|6504.917, 8|53.452, 8|1692.168, 9|4796.165
 730, trx: 79, 95%: 2040.343, 99%: 3060.024, max_rt: 3392.892, 82|9933.464, 8|69.372, 9|1766.271, 9|3795.509
 740, trx: 92, 95%: 2275.221, 99%: 2556.196, max_rt: 2728.911, 94|9653.868, 10|86.866, 9|1670.054, 8|4516.399
 750, trx: 88, 95%: 2358.434, 99%: 2833.443, max_rt: 3817.807, 90|5598.837, 8|67.313, 9|1681.954, 8|4489.902
 760, trx: 89, 95%: 2376.149, 99%: 2930.048, max_rt: 3534.046, 84|5621.660, 8|53.893, 9|1684.145, 10|4392.274

复制

查询前１０条语句

MySQL [oceanbase]>select
	sql_id,
	count(*),
	round(avg(elapsed_time)) avg_elapsed_time,
	round(avg(execute_time)) avg_exec_time
from
	gv$sql_audit s
where
	1 = 1
	and user_name = 'tpcc'
	and request_time >= time_to_usec(DATE_SUB(current_timestamp, interval 30 minute) )
group by
	sql_id
order by
	avg_elapsed_time desc
limit 10;
+----------------------------------+----------+------------------+---------------+
| sql_id                           | count(*) | avg_elapsed_time | avg_exec_time |
+----------------------------------+----------+------------------+---------------+
| CB9A79DD757E45A3BE7702F0DDBCBF0D |        1 |           294648 |        111390 |
| FA4D3F470B119C8D1532D0F5191F6BDC |       13 |           205235 |         68770 |
| A9056669703DDD218DF758A97E5E9A33 |        4 |           141845 |         33050 |
| B8576DF65DB3D93972D427F6407899C5 |       16 |           126625 |         26919 |
| D12FDB84894224F16092581EBCB2E131 |        1 |           108310 |         73763 |
| 511ADC3F4679AC7B5C0EF9446AB5308C |        1 |            97767 |         68079 |
| DCCCB4046CF77DC97CFDCFD5369521A9 |        1 |            93194 |          2890 |
| C6838B13E9801C95ADF36A70B85EC85D |        5 |            91687 |         68177 |
| 35D668394AE81ED0095DE5C25225A8D1 |        1 |            90081 |         68660 |
| 0147BA89B7F422CDBE96E5326C8CDBA6 |        2 |            89629 |         80144 |
+----------------------------------+----------+------------------+---------------+
10 rows in set (0.268 sec)

复制

选择前两条进行分析

select distinct query_sql from gv$plan_cache_plan_stat where sql_id='FA4D3F470B119C8D1532D0F5191F6BDC';
MySQL [oceanbase]> select query_sql from gv$plan_cache_plan_stat where sql_id='FA4D3F470B119C8D1532D0F5191F6BDC';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| query_sql                                                                                                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 FROM stock WHERE s_i_id = ? AND s_w_id = ? FOR UPDATE |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.266 sec)
MySQL [tpcc]> explain SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 FROM tpcc.stock WHERE s_i_id = 1 AND s_w_id = 1 FOR UPDATE\G
*************************** 1. row ***************************
Query Plan: ===================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
-----------------------------------
|0 |TABLE GET|stock|1        |54  |
===================================

Outputs & filters:
-------------------------------------
  0 - output([stock.s_quantity], [stock.s_data], [stock.s_dist_01], [stock.s_dist_02], [stock.s_dist_03], [stock.s_dist_04], [stock.s_dist_05], [stock.s_dist_06], [stock.s_dist_07], [stock.s_dist_08], [stock.s_dist_09], [stock.s_dist_10]), filter(nil),
      access([stock.s_w_id], [stock.s_i_id], [stock.s_quantity], [stock.s_data], [stock.s_dist_01], [stock.s_dist_02], [stock.s_dist_03], [stock.s_dist_04], [stock.s_dist_05], [stock.s_dist_06], [stock.s_dist_07], [stock.s_dist_08], [stock.s_dist_09], [stock.s_dist_10]), partitions(p0)

1 row in set (0.017 sec)


select distinct query_sql from gv$plan_cache_plan_stat where sql_id='CB9A79DD757E45A3BE7702F0DDBCBF0D';

MySQL [oceanbase]> select distinct query_sql from gv$plan_cache_plan_stat where sql_id='CB9A79DD757E45A3BE7702F0DDBCBF0D';
+-------------------------------------------------------------------------------+
| query_sql                                                                     |
+-------------------------------------------------------------------------------+
| SELECT count(*) FROM stock WHERE s_w_id = ? AND s_i_id = ? AND s_quantity < ? |
+-------------------------------------------------------------------------------+
1 row in set (0.082 sec)
MySQL [tpcc]> explain SELECT count(*) FROM tpcc.stock WHERE s_w_id = 1 AND s_i_id = 1 AND s_quantity < 20\G
*************************** 1. row ***************************
Query Plan: =========================================
|ID|OPERATOR       |NAME |EST. ROWS|COST|
-----------------------------------------
|0 |SCALAR GROUP BY|     |1        |53  |
|1 | TABLE GET     |stock|1        |53  |
=========================================

Outputs & filters:
-------------------------------------
  0 - output([T_FUN_COUNT(*)]), filter(nil),
      group(nil), agg_func([T_FUN_COUNT(*)])
  1 - output([1]), filter([stock.s_quantity < 20]),
      access([stock.s_quantity]), partitions(p0)

1 row in set (0.041 sec)

复制

测试期间系统负载情况