数据库查询占用空间大小和ocp express上面显示租户大小不一致

【 使用环境 】测试环境
【 OB or 其他组件 】OCP
【 使用版本 】ob 4.1
【问题描述】用客户端连接查询数据库大小为900G左右，ocp租户显示租户大小70.53G，显示不一致

MySQL [gsc_product]> select
→
→ table_schema as ‘数据库’,
→
→ sum(table_rows) as ‘记录数’,
→
→ sum(truncate(data_length/1024/1024, 2)) as ‘数据容量(MB)’,
→
→ sum(truncate(index_length/1024/1024, 2)) as ‘索引容量(MB)’
→
→ from information_schema.tables
→
→ group by table_schema
→
→ order by sum(data_length) desc, sum(index_length) desc;
±-------------------±-----------±-----------------±-----------------+
| 数据库 | 记录数 | 数据容量(MB) | 索引容量(MB) |
±-------------------±-----------±-----------------±-----------------+
| gsc_product | 3891296447 | 906661.92 | NULL |
| oceanbase | 31848180 | 7875.48 | NULL |
| test | 4199803 | 240.31 | NULL |
| information_schema | 193 | 0.00 | NULL |
| mysql | 7 | 0.00 | NULL |
±-------------------±-----------±-----------------±-----------------+
5 rows in set (0.04 sec)

相关同学正在查看

select coalesce(t1.tenant_id, -1) as tenant_id, tenant_name, sum(data_disk_in_use) as data_disk_in_use, sum(log_disk_in_use) as log_disk_in_use from (select t1.unit_id, t1.svr_ip, t1.svr_port, t2.tenant_id, t1.data_disk_in_use, t1.log_disk_in_use from ( select unit_id, svr_ip, svr_port, sum(data_disk_in_use) as data_disk_in_use, sum(log_disk_in_use) as log_disk_in_use from v$ob_units group by unit_id ) t1 join dba_ob_units t2 on t1.unit_id = t2.unit_id) t1 join dba_ob_tenants t2 on t1.tenant_id = t2.tenant_id where tenant_type <>‘meta’ group by tenant_id

obagent中是通过这条sql进行采集的

obclient [gsc_product]> select coalesce(t1.tenant_id, -1) as tenant_id, tenant_name, sum(data_disk_in_use) as data_disk_in_use, sum(log_disk_in_use) as log_disk_in_use from (select t1.unit_id, t1.svr_ip, t1.svr_port, t2.tenant_id, t1.data_disk_in_use, t1.log_disk_in_use from ( select unit_id, svr_ip, svr_port, sum(data_disk_in_use) as data_disk_in_use, sum(log_disk_in_use) as log_disk_in_use from v$ob_units group by unit_id ) t1 join dba_ob_units t2 on t1.unit_id = t2.unit_id) t1 join dba_ob_tenants t2 on t1.tenant_id = t2.tenant_id where tenant_type <>‘meta’ group by tenant_id
→ ;
ERROR 1146 (42S02): Table ‘gsc_product.v$ob_units’ doesn’t exist

需要用sys租户连接oceanbase数据库

可以了，怎么查询数据库每个表的大小呢

那两个租户有表吗，有数据吗
sql中的 v$ob_units 可以改成 gv$ob_units 试试看。
或者到租户所在的机器上执行原本的sql。这两个租户可能不在这个机器上

精确到表级别的占用空间大小的查询语句方便发个吗？

可以参考这个帖子中的sql

4.2没有__all_virtual_meta_table 这个表了。
只能查数据大小，无法查占用磁盘大小。

__all_tablet_meta_table.data_size