mysql租户类型回收站的总大小怎么查

OceanBase 技术问题
#1

【 使用版本 】
4.2.5.1
【问题描述】清晰明确描述问题
怎么能查到回收站中所有对象的总大小

1 个赞
#3

用户问题: “如何查询 OceanBase 回收站中所有对象的总大小？”

对于这个问题，实际上并不需要使用 obdiag 来解决，因为这属于日常的数据库操作。您可以直接通过 SQL 查询来获取回收站中所有对象的总大小。OceanBase 提供了类似于 Oracle 的回收站功能，您可以通过以下 SQL 语句查询：

SELECT SUM(size) AS total_size FROM oceanbase.__all_recyclebin;

这里 oceanbase.__all_recyclebin 表存储了所有被删除但尚未被物理清除的对象信息，包括每个对象的大小。执行上述查询将返回回收站中所有对象的总大小。

如果您在执行该查询时遇到任何问题或需要进一步的帮助，请随时告知。此外，如果您有其他关于 obdiag 使用或数据库运维方面的问题，我也非常乐意为您提供帮助。

附上敏捷诊断工具 obdiag 使用帮助链接

#4

mysql> SELECT SUM(size) AS total_size FROM oceanbase.__all_recyclebin;
ERROR 1054 (42S22): Unknown column ‘size’ in ‘field list’
mysql> show create table oceanbase.__all_recyclebin\G
*************************** 1. row ***************************
Table: __all_recyclebin
Create Table: CREATE TABLE __all_recyclebin (
gmt_create timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
tenant_id bigint(20) NOT NULL,
object_name varchar(128) NOT NULL,
type bigint(20) NOT NULL,
database_id bigint(20) NOT NULL,
table_id bigint(20) NOT NULL,
tablegroup_id bigint(20) NOT NULL,
original_name varchar(256) NOT NULL,
PRIMARY KEY (tenant_id, object_name, type),
KEY idx_recyclebin_db_type (tenant_id, database_id, type) BLOCK_SIZE 16384 LOCAL,
KEY idx_recyclebin_ori_name (original_name) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘none’ REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10 TABLEGROUP = ‘oceanbase’
1 row in set (0.00 sec)

#5

小助手回复的有问题，试试使用oceanbase.dba_recyclebin

#6

mysql> select * from oceanbase.dba_recyclebin;
Empty set (0.19 sec)

mysql> SELECT SUM(size) AS total_size FROM oceanbase.dba_recyclebin;
ERROR 1054 (42S22): Unknown column ‘size’ in ‘field list’

mysql> show create table oceanbase.dba_recyclebin\G
ERROR 1142 (42000): SHOW VIEW command denied to user ‘xm_dba_monitor’@’%’ for table ‘dba_recyclebin’

还是报没有size字段，但是我用的不是root用户，连表结构也查不了，难道这个查询用户不同会缺少字段吗，是必须要用root用户吗

#7

学习

#8

mysql> show create table oceanbase.dba_recyclebin\G
*************************** 1. row ***************************
View: DBA_RECYCLEBIN
Create View: CREATE VIEW DBA_RECYCLEBIN AS SELECT CAST(B.DATABASE_NAME AS CHAR(128)) AS OWNER, CAST(A.OBJECT_NAME AS CHAR(128)) AS OBJECT_NAME, CAST(A.ORIGINAL_NAME AS CHAR(128)) AS ORIGINAL_NAME, CAST(NULL AS CHAR(9)) AS OPERATION, CAST(CASE A.TYPE WHEN 1 THEN ‘TABLE’ WHEN 2 THEN ‘NORMAL INDEX’ WHEN 3 THEN ‘VIEW’ ELSE NULL END AS CHAR(25)) AS TYPE, CAST(CASE WHEN TP.TABLESPACE_ID IS NULL THEN NULL ELSE TP.TABLESPACE_NAME END AS CHAR(30)) AS TS_NAME, CAST(C.GMT_CREATE AS DATE) AS CREATETIME, CAST(C.GMT_MODIFIED AS DATE) AS DROPTIME, CAST(NULL AS SIGNED) AS DROPSCN, CAST(NULL AS CHAR(128)) AS PARTITION_NAME, CAST(‘YES’ AS CHAR(3)) AS CAN_UNDROP, CAST(‘YES’ AS CHAR(3)) AS CAN_PURGE, CAST(NULL AS SIGNED) AS RELATED, CAST(NULL AS SIGNED) AS BASE_OBJECT, CAST(NULL AS SIGNED) AS PURGE_OBJECT, CAST(NULL AS SIGNED) AS SPACE FROM OCEANBASE.__ALL_RECYCLEBIN A JOIN OCEANBASE.__ALL_DATABASE B ON A.TENANT_ID = B.TENANT_ID AND A.DATABASE_ID = B.DATABASE_ID JOIN OCEANBASE.__ALL_TABLE C ON A.TENANT_ID = C.TENANT_ID AND A.TABLE_ID = C.TABLE_ID LEFT JOIN OCEANBASE.__ALL_TENANT_TABLESPACE TP ON C.TENANT_ID = TP.TENANT_ID AND C.TABLESPACE_ID = TP.TABLESPACE_ID WHERE A.TENANT_ID = 0 AND A.TYPE IN (1, 2, 3) AND C.TABLE_MODE >> 12 & 15 in (0,1) AND C.INDEX_ATTRIBUTES_SET & 16 = 0 UNION ALL SELECT CAST(A.ORIGINAL_NAME AS CHAR(128)) AS OWNER, CAST(A.OBJECT_NAME AS CHAR(128)) AS OBJECT_NAME, CAST(A.ORIGINAL_NAME AS CHAR(128)) AS ORIGINAL_NAME, CAST(NULL AS CHAR(9)) AS OPERATION, CAST(‘DATABASE’ AS CHAR(25)) AS TYPE, CAST(NULL AS CHAR(30)) AS TS_NAME, CAST(B.GMT_CREATE AS DATE) AS CREATETIME, CAST(B.GMT_MODIFIED AS DATE) AS DROPTIME, CAST(NULL AS SIGNED) AS DROPSCN, CAST(NULL AS CHAR(128)) AS PARTITION_NAME, CAST(‘YES’ AS CHAR(3)) AS CAN_UNDROP, CAST(‘YES’ AS CHAR(3)) AS CAN_PURGE, CAST(NULL AS SIGNED) AS RELATED, CAST(NULL AS SIGNED) AS BASE_OBJECT, CAST(NULL AS SIGNED) AS PURGE_OBJECT, CAST(NULL AS SIGNED) AS SPACE FROM OCEANBASE.__ALL_RECYCLEBIN A JOIN OCEANBASE.__ALL_DATABASE B ON A.TENANT_ID = B.TENANT_ID AND A.DATABASE_ID = B.DATABASE_ID WHERE A.TENANT_ID = 0 AND A.TYPE = 4 UNION ALL SELECT CAST(B.DATABASE_NAME AS CHAR(128)) AS OWNER, CAST(A.OBJECT_NAME AS CHAR(128)) AS OBJECT_NAME, CAST(A.ORIGINAL_NAME AS CHAR(128)) AS ORIGINAL_NAME, CAST(NULL AS CHAR(9)) AS OPERATION, CAST(‘TRIGGER’ AS CHAR(25)) AS TYPE, CAST(NULL AS CHAR(30)) AS TS_NAME, CAST(C.GMT_CREATE AS DATE) AS CREATETIME, CAST(C.GMT_MODIFIED AS DATE) AS DROPTIME, CAST(NULL AS SIGNED) AS DROPSCN, CAST(NULL AS CHAR(128)) AS PARTITION_NAME, CAST(‘YES’ AS CHAR(3)) AS CAN_UNDROP, CAST(‘YES’ AS CHAR(3)) AS CAN_PURGE, CAST(NULL AS SIGNED) AS RELATED, CAST(NULL AS SIGNED) AS BASE_OBJECT, CAST(NULL AS SIGNED) AS PURGE_OBJECT, CAST(NULL AS SIGNED) AS SPACE FROM OCEANBASE.__ALL_RECYCLEBIN A JOIN OCEANBASE.__ALL_DATABASE B ON A.TENANT_ID = B.TENANT_ID AND A.DATABASE_ID = B.DATABASE_ID JOIN OCEANBASE.__ALL_TENANT_TRIGGER C ON A.TENANT_ID = C.TENANT_ID AND A.TABLE_ID = C.TRIGGER_ID WHERE A.TENANT_ID = 0 AND A.TYPE = 6 UNION ALL SELECT CAST(NULL AS CHAR(128)) AS OWNER, CAST(A.OBJECT_NAME AS CHAR(128)) AS OBJECT_NAME, CAST(A.ORIGINAL_NAME AS CHAR(128)) AS ORIGINAL_NAME, CAST(NULL AS CHAR(9)) AS OPERATION, CAST(‘TENANT’ AS CHAR(25)) AS TYPE, CAST(NULL AS CHAR(30)) AS TS_NAME, CAST(USEC_TO_TIME(B.SCHEMA_VERSION) AS DATE) AS CREATETIME, CAST(A.GMT_CREATE AS DATE) AS DROPTIME, CAST(NULL AS SIGNED) AS DROPSCN, CAST(NULL AS CHAR(128)) AS PARTITION_NAME, CAST(‘YES’ AS CHAR(3)) AS CAN_UNDROP, CAST(‘YES’ AS CHAR(3)) AS CAN_PURGE, CAST(NULL AS SIGNED) AS RELATED, CAST(NULL AS SIGNED) AS BASE_OBJECT, CAST(NULL AS SIGNED) AS PURGE_OBJECT, CAST(NULL AS SIGNED) AS SPACE FROM OCEANBASE.__ALL_RECYCLEBIN A JOIN OCEANBASE.__ALL_VIRTUAL_CORE_ALL_TABLE B ON A.TABLE_ID = B.TENANT_ID AND B.TABLE_NAME = ‘__all_core_table’ WHERE A.TYPE = 7
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
1 row in set (0.02 sec)

mysql>

#9

需要用root查询，这张表没有size字段 应该查询space字段

#10

mysql> SELECT space FROM oceanbase.dba_recyclebin;
Empty set (0.16 sec)

这样可以吗

#11

上面的视图中看到，space字段是视图硬编码的 NULL，不反映任何存储信息

#12

ob的回收站是个逻辑概念，目前 OceanBase 4.x 版本没有直接查询“回收站大小”的内置功能

#13

SELECT SUM(size) AS total_size FROM oceanbase.__all_recyclebin;

#14

不行的

#15

是个好问题啊，一直没关注这个细节，Mark下看有没有解决方案。

#16

已经解决了官方文档中有一篇记录了 ob_cluster_recyclebin_disk_used_over_threshold OceanBase 回收站磁盘占用超限-V4.3.6-文档-分布式数据库使用文档

1 个赞
#17

刚刚也顺着记着OCP上有回收站的告警找到了这篇文章，原来有个回收站的库

#18

简单写了个SQL，不一定对，可以作为思路参考

SELECT DISTINCT T1.DATABASE_NAME,
                T1.TABLE_ID,
                T1.TABLE_NAME,
                T3.ORIGINAL_NAME,
                T1.TABLE_TYPE,
                T2.DATA_SIZE,
                T2.REQUIRED_SIZE
  FROM DBA_OB_TABLE_LOCATIONS T1,
       DBA_OB_TABLET_REPLICAS T2,
       __ALL_RECYCLEBIN       T3
 WHERE 1 = 1
   AND T1.TABLET_ID = T2.TABLET_ID
   AND T1.TABLE_ID = T3.TABLE_ID
   AND DATABASE_NAME = '__recyclebin'

+---------------+----------+-----------------------------------------+---------------+------------+-----------+---------------+
| DATABASE_NAME | TABLE_ID | TABLE_NAME                              | original_name | TABLE_TYPE | DATA_SIZE | REQUIRED_SIZE |
+---------------+----------+-----------------------------------------+---------------+------------+-----------+---------------+
| __recyclebin  |   500004 | __recycle_$_1757569992_1760080762131712 | t1            | USER TABLE |       374 |       2097152 |
+---------------+----------+-----------------------------------------+---------------+------------+-----------+---------------+
1 row in set (0.078 sec)