利用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)
测试期间系统负载情况
最后修改时间：2022-02-20 10:11:14
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
