obce_3

Queuing（Buffer)表
1 实验目的
通过对 memstore 内存使用的监控，了解表记录频繁更新、产生多版本后导致的 memstore 的内存
增长现象。
了解 buffer 表提高全表扫描性能的原因，理解 OceanBase 在 buffer 表场景特殊的转储策略与优化。
2 1ECS——1OB 集群
3 实验前提

实验环境内置 Oracle 租户 ob_ora，租户资源规格大小为 3C6G
obclient -uroot@sys -h127.0.0.1 -P2881
#新建资源规格
create resource unit u1_ora max_cpu=3,min_cpu=3,max_memory='6G',min_memory='6G',max_iops=128,max_disk_size='50G',max_session_num=100;

#新建资源池
create resource pool pool_ora unit='u1_ora',unit_num=1,zone_list=('zone1');
#新建 oracle 租户
CREATE TENANT IF NOT EXISTS ob_ora charset='utf8mb4', replica_num=1,zone_list=('zone1' ), primary_zone='zone1',resource_pool_list=('pool_ora') SET ob_tcp_invited_nodes='%', ob_compatibility_mode='oracle';

4 实验步骤
步骤 1 登录 Oracle 租户的 sys 用户（注意不是 sys 租户），查看修改前参数值；修改隐含参数
_ob_queuing_fast_freeze_min_count 的默认值，此参数限制 delete 语句到达这个阈值，触发对 queuing 表执行转储的操作；
注意: 本实验只是引导学员理解这个参数对 queuing 表的影响，实际部署环境中，根据需要调整此隐含参数。

obclient -h127.0.0.1 -P2883 -uSYS@ob_ora#obce_test

show parameters like '_ob_queuing_fast_freeze_min_count';
##默认500000

alter system set "_ob_queuing_fast_freeze_min_count"=20000;

create user tpcc identified by obce_test;
grant dba to tpcc;

步骤 2 创建并登录 tpcc 用户，授予 dba 权限，创建 2 张表，一张表为普通表，另外一个为queuing 表
obclient -h127.0.0.1 -P2883 -utpcc@ob_ora#obce_test -p

drop table tab_no_queue purge;
drop table tab_queue purge;
create table tab_no_queue (id int primary key, name varchar(10), contact
varchar(20), addr varchar(100));
create table tab_queue(id int primary key, name varchar(10), contact varchar(20),
addr varchar(100)) table_mode='queuing';

步骤 3 查询表 tab_no_queue 的 table_id 和 leader 副本所在的 Observer IP 地址; 注意
tenant_id 根据学员实际 tenant_id，记录 table_id（1100611139453781）和 IP （127.0.0.1）
obclient -uroot@sys -h127.0.0.1 -P2881
SELECT tenant_id FROM oceanbase.gv$tenant where tenant_name='ob_ora';
#1001

use oceanbase;

select table_id, table_name from __all_virtual_table where tenant_id=1001 and table_name= upper('tab_no_queue');
+------------------+--------------+
| table_id | table_name |
+------------------+--------------+
| 1100611139453777 | TAB_NO_QUEUE |
+------------------+--------------+
1 row in set (0.003 sec)

select tenant.tenant_name,meta.table_id, tab.table_name,
partition_id,zone,svr_ip,svr_port, case when role=1 then 'leader' when role=2
then 'follower' else null end as role, tab.primary_zone from
__all_virtual_meta_table meta inner join __all_tenant tenant on
meta.tenant_id=tenant.tenant_id inner join __all_virtual_table tab on
meta.tenant_id=tab.tenant_id and meta.table_id=tab.table_id where
tenant.tenant_id=1001 and tab.table_name='TAB_NO_QUEUE' order by
tenant.tenant_name,table_name,partition_id,zone;

obclient -h127.0.0.1 -P2883 -utpcc@ob_ora#obce_test -pobce_test

insert into tab_no_queue select level,
case mod(level,5)
when 0 then '张一'
when 1 then '李一'
when 2 then '王一'
when 3 then '赵一'
when 4 then '钱一'
else null
end
, '1234567890','Asia-China-Sichuan-Chengdu'
from dual where mod(level,5)=3 connect by level <=500000;
commit;
步骤 5 执行一次表和租户级别的转储，避免以上插入操作对本实验的影响
obclient -uroot@sys -h127.0.0.1 -P2881
ALTER SYSTEM MINOR FREEZE TENANT=(ob_ora);

步骤 6 确认转储结束
SELECT * FROM __all_zone WHERE name='merge_status';
--确认 merge_status 为‘IDLE‘状态，表明转储成功结束

-- 查看 gv$merge_info，确认转储发生时间
use oceanbase;
select * from gv$merge_info where table_id =1100611139453779 order by start_time desc limit 6;

+-----------+----------+-----------+------------------+--------------+-------+-----------------+------------------+----------------------------+----------------------------+-------------------+-----------+-----------------+
| SVR_IP | SVR_PORT | TENANT_ID | TABLE_ID | PARTITION_ID | TYPE | ACTION | VERSION | START_TIME | END_TIME | MACRO_BLOCK_COUNT | REUSE_PCT | PARALLEL_DEGREE |
+-----------+----------+-----------+------------------+--------------+-------+-----------------+------------------+----------------------------+----------------------------+-------------------+-----------+-----------------+
| 127.0.0.1 | 2882 | 1001 | 1100611139453777 | 0 | minor | mini complement | 1681176056935704 | 2023-04-11 09:21:02.714899 | 2023-04-11 09:21:02.729823 | 0 | 0.00 | 1 |
| 127.0.0.1 | 2882 | 1001 | 1100611139453777 | 0 | minor | mini merge | 1681176056935704 | 2023-04-11 09:21:02.520045 | 2023-04-11 09:21:02.714300 | 1 | 0.00 | 1 |
+-----------+----------+-----------+------------------+--------------+-------+-----------------+------------------+----------------------------+----------------------------+-------------------+-----------+-----------------+
2 rows in set (0.003 sec)

步骤 7 执行批量 insert 语句，模拟此表被应用插入新数据的场景
insert into tab_no_queue select level,
case mod(level,5)
when 0 then '张一'
when 1 then '李一'
when 2 then '王一'
when 3 then '赵一'
when 4 then '钱一'
else null
end, '1234567890','Asia-China-Sichuan-Chengdu' from dual where mod(level,5) in (2)
connect by level <=150000;
commit;

步骤 8 登陆 sys 租户，查看 tab_no_queue 表在 memstore 的内存消耗情况，关注 used_mb 字段值
select ip,table_id,partition_id,round(used/1024/1024,1) as
used_mb,hash_items,btree_items,is_active from gv$memstore_info where table_id = 1100611139453779;

+-----------+------------------+--------------+---------+------------+-------------+-----------+
| ip | table_id | partition_id | used_mb | hash_items | btree_items | is_active |
+-----------+------------------+--------------+---------+------------+-------------+-----------+
| 127.0.0.1 | 1100611139453777 | 0 | 12.0 | 29824 | 30000 | 1 |
+-----------+------------------+--------------+---------+------------+-------------+-----------+
1 row in set (0.016 sec)

步骤 9 模拟修改表的操作，了解多版本数据对 memstore 内存的使用

update tab_no_queue set name = '王二' where mod(id,5) in (2);
commit;

步骤 10 删除记录，观察是否触发系统转储，理解隐含参数 ob_queuing_fast_freeze_min_count的含义，得出结果此参数对非 queuing 表无效
delete from tab_no_queue where mod(id,5) in (2);
commit;

步骤 11 查看表 tab_no_queue 的转储情况，确认没有转储
select * from gv$merge_info where table_id =1100611139453777 order by start_time desc limit 6;

再次执行 insert 和 delete 操作，观察 tab_no_queue 表的转储情况, 确认没有转储发生
insert into tab_no_queue
select level,
case mod(level,5)
when 0 then '张一'
when 1 then '李一'
when 2 then '王一'
when 3 then '赵一'
when 4 then '钱一'
else null
end
, '1234567890','Asia-China-Sichuan-Chengdu'
from dual
where mod(level,5) in (4)
connect by level <=150000;
delete from tab_no_queue where mod(id,5) in (4);
commit;

步骤 12 对 tab_no_queue 执行全表扫描，查看 gv$sql_audit 的执行信息 , 注意 execute_time时间(78752)，记录与后面的 queuing 表对比。
select 2, count(*) from tab_no_queue;
#登陆 oceanbase 的 sys 租户，执行以下语句
select svr_ip,query_sql,trace_id,sql_id,plan_id,is_hit_plan,plan_type,elapsed_time,
execute_time,get_plan_time,table_scan,memstore_read_row_count,
ssstore_read_row_count from gv$sql_audit where tenant_id=1001 and query_sql like
'select%, count(*) from tab_no_queue%';

+-----------+--------------------------------------+-----------------------------------+----------------------------------+---------+-------------+-----------+--------------+--------------+---------------+------------+-------------------------+------------------------+
| svr_ip | query_sql | trace_id | sql_id | plan_id | is_hit_plan | plan_type | elapsed_time | execute_time | get_plan_time | table_scan | memstore_read_row_count | ssstore_read_row_count |
+-----------+--------------------------------------+-----------------------------------+----------------------------------+---------+-------------+-----------+--------------+--------------+---------------+------------+-------------------------+------------------------+
| 127.0.0.1 | select 2, count(*) from tab_no_queue | YB427F000001-0005F9051599BFFF-0-0 | D4556082FA7C5F0ED9FE743D3728D821 | 19 | 0 | 1 | 80513 | 78752 | 1721 | 1 | 60000 | 100000 |
+-----------+--------------------------------------+-----------------------------------+----------------------------------+---------+-------------+-----------+--------------+--------------+---------------+------------+-------------------------+------------------------+
1 row in set (0.014 sec)

步骤 13 对 tab_queue 表（queuing 表）执行以上相同的步骤，查询表 tab_queue 的 table_id 和leader 副本所在的 Observer IP 地址; 注意 tenant_id 根据学员实际 tenant_id，记录
table_id（1100611139453780）和 IP（127.0.0.1）

SELECT * FROM oceanbase.gv$tenant where tenant_name='ob_ora';
select table_id, table_name from __all_virtual_table where tenant_id=1001 and table_name= upper('tab_queue');
+------------------+------------+
| table_id | table_name |
+------------------+------------+
| 1100611139453778 | TAB_QUEUE |
+------------------+------------+
1 row in set (0.001 sec)

select tenant.tenant_name,meta.table_id, tab.table_name,
partition_id,zone,svr_ip,svr_port, case when role=1 then 'leader' when role=2
then 'follower' else null end as role, tab.primary_zone from
__all_virtual_meta_table meta inner join __all_tenant tenant on
meta.tenant_id=tenant.tenant_id inner join __all_virtual_table tab on
meta.tenant_id=tab.tenant_id and meta.table_id=tab.table_id where
tenant.tenant_id=1001 and tab.table_name='TAB_QUEUE' order by
tenant.tenant_name,table_name,partition_id,zone;

步骤 14 向 queuing 表（tab_queue）添加测试数据
insert into tab_queue select level,
case mod(level,5)
when 0 then '张一'
when 1 then '李一'
when 2 then '王一'
when 3 then '赵一'
when 4 then '钱一'
else null
end
, '1234567890','Asia-China-Sichuan-Chengdu'
from dual where mod(level,5)=3 connect by level <=500000;
commit;

步骤 15 执行一次表和租户级别的转储，避免以上插入操作对本实验的影响
ALTER SYSTEM MINOR FREEZE TENANT=(ob_ora);

查看 gv$merge_info，确认转储发生时间
select * from gv$merge_info where table_id =1100611139453779 order by start_time
desc limit 6;
+-----------+----------+-----------+------------------+--------------+-------+-----------------+------------------+----------------------------+----------------------------+-------------------+-----------+-----------------+
| SVR_IP | SVR_PORT | TENANT_ID | TABLE_ID | PARTITION_ID | TYPE | ACTION | VERSION | START_TIME | END_TIME | MACRO_BLOCK_COUNT | REUSE_PCT | PARALLEL_DEGREE |
+-----------+----------+-----------+------------------+--------------+-------+-----------------+------------------+----------------------------+----------------------------+-------------------+-----------+-----------------+
| 127.0.0.1 | 2882 | 1001 | 1100611139453779 | 0 | minor | buf minor merge | 1681177676789313 | 2023-04-11 09:48:25.084332 | 2023-04-11 09:48:25.310552 | 1 | 0.00 | 1 |
| 127.0.0.1 | 2882 | 1001 | 1100611139453779 | 0 | minor | mini complement | 1681177676789313 | 2023-04-11 09:48:05.228777 | 2023-04-11 09:48:05.244622 | 0 | 0.00 | 1 |
| 127.0.0.1 | 2882 | 1001 | 1100611139453779 | 0 | minor | mini merge | 1681177676789313 | 2023-04-11 09:48:05.061723 | 2023-04-11 09:48:05.228032 | 1 | 0.00 | 1 |
| 127.0.0.1 | 2882 | 1001 | 1100611139453779 | 0 | minor | buf minor merge | 1681177590761500 | 2023-04-11 09:47:04.798232 | 2023-04-11 09:47:04.798386 | 0 | 0.00 | 1 |
| 127.0.0.1 | 2882 | 1001 | 1100611139453779 | 0 | minor | mini complement | 1681177590761500 | 2023-04-11 09:46:45.018481 | 2023-04-11 09:46:45.018538 | 0 | 0.00 | 1 |
| 127.0.0.1 | 2882 | 1001 | 1100611139453779 | 0 | minor | mini merge | 1681177590761500 | 2023-04-11 09:46:45.017855 | 2023-04-11 09:46:45.017914 | 0 | 0.00 | 1 |
+-----------+----------+-----------+------------------+--------------+-------+-----------------+------------------+----------------------------+----------------------------+-------------------+-----------+-----------------+

步骤 17 执行批量 insert 语句，模拟此表被应用插入新数据的场景
insert into tab_queue select level,
case mod(level,5)
when 0 then '张一'
when 1 then '李一'
when 2 then '王一'
when 3 then '赵一'
when 4 then '钱一'
else null
end, '1234567890','Asia-China-Sichuan-Chengdu' from dual where mod(level,5) in (2)
connect by level <=150000;
commit;

步骤 18 登陆 sys 租户，查看 tab_queue 表在 memstore 的内存消耗情况，关注 used_mb 字段
值
select ip,table_id,partition_id,round(used/1024/1024,1) as used_mb,hash_items,btree_items,is_active from gv$memstore_info
where table_id =1100611139453779;
+-----------+------------------+--------------+---------+------------+-------------+-----------+
| ip | table_id | partition_id | used_mb | hash_items | btree_items | is_active |
+-----------+------------------+--------------+---------+------------+-------------+-----------+
| 127.0.0.1 | 1100611139453779 | 0 | 14.0 | 29824 | 30000 | 1 |
+-----------+------------------+--------------+---------+------------+-------------+-----------+
1 row in set (0.014 sec)

步骤 19 模拟修改表的操作，了解多版本数据对 memstore 内存的使用
update tab_queue set name = '王二' where mod(id,5) in (2);
commit;

步骤 20 再次查看此步骤的内存消耗，可以看到 update 语句消耗更多内存
select ip,table_id,partition_id,round(used/1024/1024,1) as used_mb,hash_items,btree_items,is_active from gv$memstore_info
where table_id =1100611139453779;
+-----------+------------------+--------------+---------+------------+-------------+-----------+
| ip | table_id | partition_id | used_mb | hash_items | btree_items | is_active |
+-----------+------------------+--------------+---------+------------+-------------+-----------+
| 127.0.0.1 | 1100611139453779 | 0 | 22.0 | 29824 | 30000 | 1 |
+-----------+------------------+--------------+---------+------------+-------------+-----------+
1 row in set (0.014 sec)

步骤 21 删除记录，观察是否触发系统转储，理解隐含参数 ob_queuing_fast_freeze_min_count的含义
delete from tab_queue where mod(id,5) in (2);
commit;

步骤 22 查看表 tab_queue 的转储情况, 确认发生转储的信息（若没有立即发现转储发生，可以等
待 30 秒左右，多次执行此命令）
select * from gv$merge_info where table_id =1100611139453779 order by start_time
desc limit 6;

+-----------+----------+-----------+------------------+--------------+-------+------------------+------------------+----------------------------+----------------------------+-------------------+-----------+-----------------+
| 127.0.0.1 | 2882 | 1001 | 1100611139453779 | 0 | minor | buf minor merge | 1681177865642223 | 2023-04-11 09:51:45.817840 | 2023-04-11 09:51:45.918254 | 1 | 100.00 | 1 |
| 127.0.0.1 | 2882 | 1001 | 1100611139453779 | 0 | minor | mini minor merge | 1681177865642223 | 2023-04-11 09:51:45.817448 | 2023-04-11 09:51:46.114550 | 1 | 0.00 | 1 |
| 127.0.0.1 | 2882 | 1001 | 1100611139453779 | 0 | minor | mini complement | 1681177865642223 | 2023-04-11 09:51:25.933503 | 2023-04-11 09:51:25.937578 | 0 | 0.00 | 1 |
| 127.0.0.1 | 2882 | 1001 | 1100611139453779 | 0 | minor | mini merge | 1681177865642223 | 2023-04-11 09:51:25.801412 | 2023-04-11 09:51:25.932413 | 1 | 0.00 | 1 |
| 127.0.0.1 | 2882 | 1001 | 1100611139453779 | 0 | minor | buf minor merge | 1681177676789313 | 2023-04-11 09:48:25.084332 | 2023-04-11 09:48:25.310552 | 1 | 0.00 | 1 |
| 127.0.0.1 | 2882 | 1001 | 1100611139453779 | 0 | minor | mini complement | 1681177676789313 | 2023-04-11 09:48:05.228777 | 2023-04-11 09:48:05.244622 | 0 | 0.00 | 1 |
+-----------+----------+-----------+------------------+--------------+-------+------------------+------------------+----------------------------+----------------------------+-------------------+-----------+-----------------+
6 rows in set (0.001 sec)

步骤 23 再次执行 insert 和 delete 操作，观察 tab_queue 表的转储情况, 确认发生一次新的转储
insert into tab_queue
select level,
case mod(level,5)
when 0 then '张一'
when 1 then '李一'
when 2 then '王一'
when 3 then '赵一'
when 4 then '钱一'
else null
end
, '1234567890','Asia-China-Sichuan-Chengdu'
from dual
where mod(level,5) in (4)
connect by level <=150000;
delete from tab_queue where mod(id,5) in (4);
commit;

查看表 tab_queue 的转储情况, 确认发生转储的信息（若没有立即发现转储发生，可以等
待 30 秒左右，多次执行此命令）
select * from gv$merge_info where table_id =1100611139453779 order by start_time
desc limit 6;

+-----------+----------+-----------+------------------+--------------+-------+------------------+------------------+----------------------------+----------------------------+-------------------+-----------+-----------------+
| SVR_IP | SVR_PORT | TENANT_ID | TABLE_ID | PARTITION_ID | TYPE | ACTION | VERSION | START_TIME | END_TIME | MACRO_BLOCK_COUNT | REUSE_PCT | PARALLEL_DEGREE |
+-----------+----------+-----------+------------------+--------------+-------+------------------+------------------+----------------------------+----------------------------+-------------------+-----------+-----------------+
| 127.0.0.1 | 2882 | 1001 | 1100611139453779 | 0 | minor | mini complement | 1681177985878566 | 2023-04-11 09:53:26.006367 | 2023-04-11 09:53:26.010483 | 0 | 0.00 | 1 |
| 127.0.0.1 | 2882 | 1001 | 1100611139453779 | 0 | minor | mini merge | 1681177985878566 | 2023-04-11 09:53:25.960056 | 2023-04-11 09:53:26.005700 | 1 | 0.00 | 1 |
| 127.0.0.1 | 2882 | 1001 | 1100611139453779 | 0 | minor | buf minor merge | 1681177865642223 | 2023-04-11 09:51:45.817840 | 2023-04-11 09:51:45.918254 | 1 | 100.00 | 1 |
| 127.0.0.1 | 2882 | 1001 | 1100611139453779 | 0 | minor | mini minor merge | 1681177865642223 | 2023-04-11 09:51:45.817448 | 2023-04-11 09:51:46.114550 | 1 | 0.00 | 1 |
| 127.0.0.1 | 2882 | 1001 | 1100611139453779 | 0 | minor | mini complement | 1681177865642223 | 2023-04-11 09:51:25.933503 | 2023-04-11 09:51:25.937578 | 0 | 0.00 | 1 |
| 127.0.0.1 | 2882 | 1001 | 1100611139453779 | 0 | minor | mini merge | 1681177865642223 | 2023-04-11 09:51:25.801412 | 2023-04-11 09:51:25.932413 | 1 | 0.00 | 1 |
+-----------+----------+-----------+------------------+--------------+-------+------------------+------------------+----------------------------+----------------------------+-------------------+-----------+-----------------+
6 rows in set (0.001 sec)

思考：比较和非 queuing 表 tab_no_queue 的转储情况，可以看到 tab_queue 在 delete语句满足隐含参数（_ob_queuing_fast_freeze_min_count）设定的阈值时，立刻发生自
动转储，随即执行了 queuing 表独有的 buf minor merge，把刚刚转储生成的 mini sstable 与 major sstable 合并成一个 minor sstable，这个操作有利于对 queuing 表的全表扫描效率

步骤 24 对 tab_queue 执行全表扫描，查看 gv$sql_audit 的执行信息 , 注意 execution_time 时间，对比与非 queuing 表的执行时间， queuing 表明显缩短。
select 2, count(*) from tab_queue;
#登陆 oceanbase 的 sys 租户，执行以下语句
select svr_ip,query_sql,trace_id,sql_id,plan_id,is_hit_plan,plan_type,elapsed_time,
execute_time,get_plan_time,table_scan,memstore_read_row_count,
ssstore_read_row_count from gv$sql_audit where tenant_id=1001 and query_sql like
'select%, count(*) from tab_queue%';

+-----------+-----------------------------------+-----------------------------------+----------------------------------+---------+-------------+-----------+--------------+--------------+---------------+------------+-------------------------+------------------------+
| svr_ip | query_sql | trace_id | sql_id | plan_id | is_hit_plan | plan_type | elapsed_time | execute_time | get_plan_time | table_scan | memstore_read_row_count | ssstore_read_row_count |
+-----------+-----------------------------------+-----------------------------------+----------------------------------+---------+-------------+-----------+--------------+--------------+---------------+------------+-------------------------+------------------------+
| 127.0.0.1 | select 2, count(*) from tab_queue | YB427F000001-0005F90514C9BFEB-0-0 | 192BE89323FFC9A0C689EEDCEFF906D6 | 29 | 0 | 1 | 44038 | 42833 | 1160 | 1 | 0 | 100000 |
+-----------+-----------------------------------+-----------------------------------+----------------------------------+---------+-------------+-----------+--------------+--------------+---------------+------------+-------------------------+------------------------+
1 row in set (0.023 sec)

结论：从 queuing 表和 no queuing 表的执行时间对比结果得出，queue 表在用户多次执行 DML 语句后造成内存数据增加，但是对批量 delete 语句自动转储对查询链路增加的问题进行了优化， queuing 表的全表扫描总耗时大大减少。

内存用满问题
1 实验目的
通过在交易场景下对内存模式的内存使用分析，初步了解各个内存模块的作用
通过对内存用满的问题分析诊断，掌握 OceanBase 数据库内存问题的排查与解决方法

2 实验环境
1ECS-1OB 集群

3 实验前提
内置 MySQL 租户 ob_mysql，租户资源规格大小为 1C2G

#新建资源规格
#drop resource unit u1_mysql;
create resource unit u1_mysql max_cpu=1,min_cpu=1,max_memory='2G',min_memory='2G',max_iops=128,max_disk_size='20G',max_session_num=100;

#新建资源池
create resource pool pool_mysql unit='u1_mysql',unit_num=1,zone_list=('zone1','zone2','zone3') ;
#新建 mysql 租户
#CREATE TENANT IF NOT EXISTS ob_mysql charset='utf8mb4',resource_pool_list=('pool_mysql') ,zone_list=('zone1','zone2','zone3'), PRIMARY_ZONE='zone1,zone2;zone3'SET ob_tcp_invited_nodes='%', ob_compatibility_mode='mysql';

CREATE TENANT IF NOT EXISTS ob_mysql charset='utf8mb4',resource_pool_list=('pool_mysql') ,zone_list=('zone1'), PRIMARY_ZONE='zone1' SET ob_tcp_invited_nodes='%', ob_compatibility_mode='mysql';

SELECT * FROM oceanbase.gv$tenant where tenant_name='ob_mysql';

+-----------+-------------+-----------+--------------+----------------+------+-----------+---------------+
| tenant_id | tenant_name | zone_list | primary_zone | collation_type | info | read_only | locality |
+-----------+-------------+-----------+--------------+----------------+------+-----------+---------------+
| 1002 | ob_mysql | zone1 | RANDOM | 0 | | 0 | FULL{1}@zone1 |
+-----------+-------------+-----------+--------------+----------------+------+-----------+---------------+
1 row in set (0.002 sec)

sql_audit_percentage参数和ob_query_timeout参数再换着试试

4 实验步骤
步骤 1 如果之前的练习没有安装 sysbench 工具，现在安装 sysbench 工具
yum install -y sysbench
sysbench --version
sysbench 1.0.17

步骤 2 创建 sbtest 数据库，准备对 ob_mysql 租户进行压力测试, 注意 sysbench 工具目前仅支持 MySQL 租户
obclient -h127.0.0.1 -P2883 -uroot@ob_mysql#obce_test
mysql> create database sbtest;

步骤 3 初始化测试表
sysbench /usr/share/sysbench/oltp_read_write.lua --tables=20 --table-size=200000 --mysql-host=127.0.0.1 --mysql-port=2883 --mysql-user=root@ob_mysql --threads=100 --report-interval=10 --time=60 --db-driver=mysql --db-ps-mode=disable cleanup

步骤 4 准备测试数据
sysbench /usr/share/sysbench/oltp_read_write.lua --tables=20 --table-size=200000 --mysql-host=127.0.0.1 --mysql-port=2883 --mysql-user=root@ob_mysql --threads=100 --report-interval=10 --time=60 --db-driver=mysql --db-ps-mode=disable prepare
注意：如果此步骤报错，记录错误代码,如下截屏， insert 语句向表 sbtest7 插入时报错，下一步执行可以忽略，目的是能看到应用由于内存不够报错（注意：本实验展示错误代码为 4030，实际环境中会有其他 40xx 的报错，后续步骤根据实际报错代码进行查询）

#FATAL: mysql_drv_query() returned error 4030 (Over tenant memory limits) for query 'INSERT INTO sbtest8(k, c, pad) VALUES(100782, '60544635103-31366196493-95558437650-32244069089-319

步骤 5 运行测试，如果上个步骤已经看到错误，可以忽略此步骤
sysbench /usr/share/sysbench/oltp_read_write.lua --tables=20 --table-size=200000 --mysql-host=127.0.0.1 --mysql-port=2883 --mysql-user=root@ob_mysql --threads=100 --report-interval=10 --time=60 --db-driver=mysql --db-ps-mode=disable prepare run

步骤 6 看到由于内存不够，引起测试命令报错，查看内存模块使用情况，按照使用内存容量继续排序
select context,count,used,alloc_count,free_count from v$memory where
TENANT_ID=1002 order by used desc limit 10;

+-------------------+-------+-----------+-------------+------------+
| context | count | used | alloc_count | free_count |
+-------------------+-------+-----------+-------------+------------+
| OB_MEMSTORE | 123 | 257832600 | 0 | 0 |
| TransAudit | 1 | 104855040 | 0 | 0 |
| MysqlRequesReco | 6 | 90444800 | 0 | 0 |
| OB_SQL_PHY_PLAN | 278 | 53045528 | 0 | 0 |
| OB_SQL_PLAN_CACHE | 416 | 27457667 | 0 | 0 |
| OB_KVSTORE_CACHE | 3 | 6288384 | 0 | 0 |
| LogAggreBuffer | 40 | 2621440 | 0 | 0 |
| MemtableCallbac | 273 | 2236416 | 0 | 0 |
| Election | 1 | 2096128 | 0 | 0 |
| ElectionGroup | 1 | 2096128 | 0 | 0 |
+-------------------+-------+-----------+-------------+------------+
10 rows in set (0.012 sec)

步骤 7 查看 gv$sql_audit 中此 SQL 语句的执行情况，可以看到数据字典中已经存放了这个 SQL的 trace_id，记录此 trace_id 准备下个步骤
注意：错误代码及表名称根据实际反馈进行修改
select svr_ip,query_sql,sql_id,plan_id, trace_id,rpc_count,plan_type,elapsed_time,
get_plan_time from gv$sql_audit where ret_code= '-4030' and query_sql like '%sbtest9%' order by elapsed_time limit 5 \G

sql_id: 613E5368B572A7E2C8E1613C2DB3DEEB
plan_id: 8
trace_id: YB427F000001-0005F90516C9C18C-0-0
rpc_count: 0
plan_type: 1
elapsed_time: 72272
get_plan_time: 3534
1 row in set (0.028 sec)

步骤 8 在 observer.log 或者前一个日志中查询此 trace_id , 可以关联 sysbench 的执行时间
grep YB427F000001-0005F90516C9C18C-0-0 /home/admin/oceanbase/log/observer.log --如果此文件中没有结果，搜索前一个 observer 日志文件
grep YB427F000001-0005F90516C9C18C-0-0 /root/observer/log/observer.log

grep YB427F000001-0006047F0FE6F23A-0-0 /root/observer/log/observer.log

打开此文件查询相关详细信息，发现日志文件报告 memstore 内存到达上限的错误。确认是memstore 的内容总容量不够引起应用报错。

[2023-04-11 10:09:36.755572] WARN [STORAGE] check_query_allowed (ob_partition_service.cpp:3800) [2046][0][YB427F000001-0005F90516C9C18C-0-0] [lt=4] [dc=0] this tenant is already out of memstore limit(tenant_id=1002, ret=-4030)

步骤 9 再次执行 sysbench 压测，将 tables 个数减为 5，看到执行成功，查看各内存模块使用情况
sysbench /usr/share/sysbench/oltp_read_write.lua --tables=5 --table-size=200000 --mysql-host=127.0.0.1 --mysql-port=2883 --mysql-user=root@ob_mysql --threads=100 --report-interval=10 --time=60 --db-driver=mysql --db-ps-mode=disable cleanup
sysbench /usr/share/sysbench/oltp_read_write.lua --tables=5 --table-size=200000 --mysql-host=127.0.0.1 --mysql-port=2883 --mysql-user=root@ob_mysql --threads=100 --report-interval=10 --time=60 --db-driver=mysql --db-ps-mode=disable prepare
sysbench /usr/share/sysbench/oltp_read_write.lua --tables=5 --table-size=200000 --mysql-host=127.0.0.1 --mysql-port=2883 --mysql-user=root@ob_mysql --threads=100 --report-interval=10 --time=60 --db-driver=mysql --db-ps-mode=disable run

select context,count,used,alloc_count,free_count from v$memory where
TENANT_ID=1002 order by used desc limit 10;
结论：在租户总内存为 2GB 的场景中，模拟多用户应用并发，由于内存不够引起应用报错，查询数据字典获取各个内存的消耗情况。判断某个内存模块不够时的处理方法，本例中由于 memstore 内存不够时，可能需要扩容租户总量。

posted @ 2023-09-05 20:46 chinesern 阅读(72) 评论(0) 编辑收藏举报

刷新页面返回顶部

chinesern

obce_3

公告