queuing表浅析

AntTech_LUTVQZ · 2023 年9 月 6 日 20:20

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’);

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’;

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;

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’;

查询表 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’);

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;

2.向表 tab_no_queue 添加测试数据

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;

3.执行一次表和租户级别的转储，避免以上插入操作对本实验的影响
obclient -uroot@sys -h127.0.0.1 -P2881
ALTER SYSTEM MINOR FREEZE TENANT=(ob_ora);

4.确认转储结束
SELECT * FROM __all_zone WHERE name=‘merge_status’;
–确认 merge_status 为‘IDLE‘状态，表明转储成功结束

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

6.执行批量 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;

7.登陆 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;

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

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

删除记录，观察是否触发系统转储，理解隐含参数 ob_queuing_fast_freeze_min_count的含义，得出结果此参数对非 queuing 表无效
delete from tab_no_queue where mod(id,5) in (2);
commit;
查看表 tab_no_queue 的转储情况，确认没有转储
select * from gv$merge_info where table_id =1100611139453777 order by start_time desc limit 6;

11.再次执行 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%’;

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’);

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);

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

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

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;

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;

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;

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;

结论1
比较和非 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%’;

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