索引大小统计

[chwei]

OB MySQL租户怎么统计索引的大小啊，类似MySQL的informatian_schema.tables里面的索引大小，OB这个视图中索引大小的值是空的

[辞霜]

收到您的咨询问题，这边先内部查一下

[靖顺]

参考这篇博客中的SQL:OceanBase 社区gzwi3v_gaMTgzMTg4NTU3MC4xNzEzMTY0NzM3_ga_T35KTM57DZ*MTcyMDUyMzY1Mi44MS4xLjE3MjA1MjQ0NzguMzQuMC4w

[AntTech_ZT39FF]

这篇文章中，统计表/索引的大小的sql只能查表大小，查不了索引大小

[辞霜]

table name换成索引名称试试

[AntTech_ZT39FF]

依然不行

[辞霜]

select /*+ READ_CONSISTENCY(WEAK) */
dotl.database_name,
dotl2.table_name,
dotl.index_name,
concat(round(sum(dotr.data_size/1024/1024/1024), 2), ‘G’) datasize_primary_replica ,
concat(round(sum(dotr.required_size)/1024/1024/1024, 2), ‘G’) requiredsize_primary_replica
from
dba_ob_table_locations dotl,
dba_ob_tablet_replicas dotr,
dba_ob_table_locations dotl2
where dotl.ls_id = dotl2.ls_id
and dotl.svr_ip = dotl2.svr_ip
and dotl.role = dotl2.role
and dotl.data_table_id = dotl2.table_id
and dotl.ls_id = dotr.ls_id
and dotl.svr_ip = dotr.svr_ip
and dotl.tablet_id = dotr.tablet_id
and dotl.table_type = ‘INDEX’
and dotl.index_name = ‘idx_xxx’
and dotl.database_name = ‘db_xxx’
and dotl2.table_name = ‘tab_xxx’
and dotl.role = ‘leader’ --是否只查询主副本
group by dotl.index_name;

试一下这个

[AntTech_ZT39FF]

还是不行

[辞霜]

索引名确定是正确的么，先查一下索引名称
这两个sql是没问题的呀

[AntTech_ZT39FF]

找到原因了，下面的sql只能在sys系统租户上查询

而这个sql只能在用户租户上查询

而实际用户租户下通过dba_ob_table_locations t1,dba_ob_tablet_replicas t2也能查询表大小，那用sys系统租户和普通用户租户查询有区别吗？

[AntTech_ZT39FF]

我用刚才查询索引大小的sql，发现ocp上这里显示的索引大小是单副本大小，而上面显示的数据大小却是三副本大小，是什么原因？

[辞霜]

ocp版本是多少

[AntTech_ZT39FF]

版本号: 4.2.2-20240425204033

[辞霜]

ocp显示问题目前还在跟进，后续有消息会在本帖回复

[AntTech_ZT39FF]

今天测试发现，之前讨论的以为是ocp的版本导致显示的索引大小不对，实际上是这个3表关联的sql查询分区表的索引大小不对，这个sql因为关联后结果集中TABLET_ID重复，导致按索引分组后大小翻了一倍，我修改了下sql：
select /*+ READ_CONSISTENCY(WEAK) */
t1.table_name,
concat(round(sum(t2.data_size/1024/1024/1024), 2), ‘G’) datasize_primary_replica ,
concat(round(sum(t2.required_size)/1024/1024/1024, 2), ‘G’) requiredsize_primary_replica
from dba_ob_table_locations t1,dba_ob_tablet_replicas t2
where
t1.svr_ip=t2.svr_ip
and t1.ls_id=t2.ls_id
and t1.tablet_id=t2.tablet_id
– and t1.role=‘leader’
and t1.database_name=‘XXX’
and (t1.table_name=‘XXXXXX’
or t1.data_table_id in (select table_id from dba_ob_table_locations where table_name=‘XXXXXX’ limit 1))
group by t1.table_name
order by 3 desc;

[辞霜]

看着像是把上面的两条sql给结合起来了

[辞霜]

刚刚测试，下面这条使用sys租户也可以查询出可能是还需要优化目前查询速度很慢
select t1.table_name,
round(sum(t2.data_size)/1024/1024/1024,2) as data_size_gb,
round(sum(t2.required_size)/1024/1024/1024,2) as required_size_gb
from dba_ob_tenants t,cdb_ob_table_locations t1,cdb_ob_tablet_replicas t2
where t.tenant_id=t1.tenant_id
and t1.svr_ip=t2.svr_ip
and t1.tenant_id=t2.tenant_id
and t1.ls_id=t2.ls_id
and t1.tablet_id=t2.tablet_id
and t1.role=‘leader’
and t.tenant_id=‘1002’
and t1.database_name=‘lzq’
group by t1.table_name
order by 3 desc;