Collect information about the macroblock utilization of nodes

Description

You can query the macroblock utilization of nodes in a cluster, and determine whether the utilization is too low.

Statement

OceanBase Database of a version earlier than V4.0.0:

SELECT *, round(data_size_gb/required_size_gb*100,3) percentage 
FROM (
  SELECT svr_ip,svr_port, round(sum(occupy_size)/1024/1024/1024,5) data_size_gb , round(sum(used_size)/1024/1024/1024,5) required_size_gb
  FROM __all_virtual_storage_stat 
  WHERE store_type=1 
  GROUP BY svr_ip,svr_port
)

OceanBase Database V4.0.0 and later:

SELECT *, round(data_size_gb/required_size_gb*100,3) percentage
FROM (
  SELECT svr_ip,svr_port, round(sum(data_size)/1024/1024/1024,5) data_size_gb , round(sum(required_size)/1024/1024/1024,5) required_size_gb
  FROM __all_virtual_tablet_meta_table 
  GROUP BY svr_ip,svr_port
)

Troubleshooting method

Based on the collected information, you can determine whether many macroblocks are not fully occupied or whether the utilization of macroblocks is low. If yes, you can reorganize the data to release disk space. The procedure is as follows:

1. Execute the following statement: alter table tablename set progressive_merge_num = 1.
2. Run the alter system major freeze command.
3. Set the progressive_merge_num parameter to 0.