OB存储空间使用分析

原创范计杰 2024-09-06

323

文件系统

运行日志

/home/admin/oceanbase/log/

参数	用途	默认值	取值范围	是否重启 OBServer 生效
enable_syslog_recycle	用于设置是否开启回收系统日志的功能。	False	True \ False	否
max_syslog_file_count	用于设置在回收日志文件之前可以容纳的日志文件数量。	0	[0, +∞)	否
syslog_io_bandwidth_limit	用于设置系统日志所能占用的磁盘 IO 带宽上限，超过带宽上限容量的系统日志将被丢弃。	30MB	NULL	否
enable_syslog_wf	用于设置是否把 WARN 以上级别的系统日志打印到一个单独的日志文件中。	True	True \ False	否

REDO LOG

clog
etc2
ilog
oob_clog
slog

DATAFILE

sort_dir

sstable/block_file

数据库存储使用分析

observer存储使用率

SELECT
	svr_ip,
	svr_port,
	CONCAT(ROUND(total_size / 1024 / 1024 / 1024, 2), 'G') AS TOTAL,
	CONCAT(ROUND(free_size / 1024 / 1024 / 1024, 2), 'G') AS FREE,
	CONCAT(ROUND((total_size - free_size)  / 1024 / 1024 / 1024, 2), 'G') AS USED,
	CONCAT(ROUND((total_size - free_size) / total_size * 100, 2),'%') AS USED_PCT
FROM
	oceanbase.__all_virtual_disk_stat order by 1;

+---------------+----------+---------+---------+-------+----------+
| svr_ip        | svr_port | TOTAL   | FREE    | USED  | USED_PCT |
+---------------+----------+---------+---------+-------+----------+
| 192.168.56.36 |     2882 | 620.13G | 616.52G | 3.61G | 0.58%    |
| 192.168.56.86 |     2882 | 503.61G | 495.69G | 7.92G | 1.57%    |
| 192.168.56.37 |     2882 | 620.13G | 616.70G | 3.43G | 0.55%    |
| 192.168.56.38 |     2882 | 620.13G | 612.01G | 8.12G | 1.31%    |
| 192.168.56.87 |     2882 | 503.61G | 495.49G | 8.12G | 1.61%    |
| 192.168.56.88 |     2882 | 503.61G | 500.18G | 3.43G | 0.68%    |
+---------------+----------+---------+---------+-------+----------+
6 rows in set (0.031 sec)


SELECT
svr_ip,
svr_port,
CONCAT(ROUND(total_size / 1024 / 1024 / 1024, 2), 'G') AS TOTAL,
CONCAT(ROUND(free_size / 1024 / 1024 / 1024, 2), 'G') AS FREE,
CONCAT(ROUND((total_size - free_size)  / 1024 / 1024 / 1024, 2), 'G') AS USED,
CONCAT(ROUND((total_size - free_size) / total_size * 100, 2),'%') AS USED_PCT
FROM
oceanbase.__all_virtual_disk_stat order by 1;
+---------------+----------+----------+----------+----------+----------+
| svr_ip        | svr_port | TOTAL    | FREE     | USED     | USED_PCT |
+---------------+----------+----------+----------+----------+----------+
| 192.168.56.18 |     2882 | 2101.19G | 1113.93G | 987.26G  | 46.99%   |
| 192.168.56.21 |     2882 | 2101.19G | 1292.95G | 808.24G  | 38.47%   |
| 192.168.56.32 |     2882 | 2101.19G | 836.47G  | 1264.71G | 60.19%   |
| 192.168.56.51 |     2882 | 2101.19G | 1128.53G | 972.66G  | 46.29%   |
| 192.168.56.57 |     2882 | 2101.19G | 1307.98G | 793.21G  | 37.75%   |
| 192.168.56.67 |     2882 | 2101.19G | 948.67G  | 1152.52G | 54.85%   |
| 192.168.56.8  |     2882 | 2101.19G | 871.51G  | 1229.68G | 58.52%   |<<<
| 192.168.56.80 |     2882 | 2101.19G | 1389.63G | 711.56G  | 33.86%   |
| 192.168.56.81 |     2882 | 2101.19G | 940.54G  | 1160.64G | 55.24%   |
+---------------+----------+----------+----------+----------+----------+
9 rows in set (0.019 sec)


select count(*) from __all_virtual_partition_sstable_macro_info where svr_ip='192.168.56.8';
+----------+
| count(*) |
+----------+
|   629280 |
+----------+
1 row in set (1.554 sec)

与__all_virtual_disk_stat 算出为的1229G一致
MySQL [oceanbase]> select 629280*2/1024;
+---------------+
| 629280*2/1024 |
+---------------+
|     1229.0625 |<<<
+---------------+
1 row in set (0.001 sec)

select  svr_ip,macro_idx_in_data_file,count(*)  from __all_virtual_partition_sstable_macro_info 
group by svr_ip,macro_idx_in_data_file having count(*)>1;
Empty set (5.480 sec)

通过__all_space_usage查看filedata，metadata，indexdata，logdata，tmpdata使用

MySQL [oceanbase]> desc __all_space_usage;
+--------------+--------------+------+-----+----------------------+--------------------------------+
| Field        | Type         | Null | Key | Default              | Extra                          |
+--------------+--------------+------+-----+----------------------+--------------------------------+
| gmt_create   | timestamp(6) | YES  |     | CURRENT_TIMESTAMP(6) |                                |
| gmt_modified | timestamp(6) | YES  |     | CURRENT_TIMESTAMP(6) | ON UPDATE CURRENT_TIMESTAMP(6) |
| tenant_id    | bigint(20)   | NO   | PRI | NULL                 |                                |
| svr_ip       | varchar(46)  | NO   | PRI | NULL                 |                                |
| svr_port     | bigint(20)   | NO   | PRI | NULL                 |                                |
| start_seq    | bigint(20)   | NO   | PRI | NULL                 |                                |
| file_type    | varchar(32)  | NO   | PRI | NULL                 |                                |
| data_size    | bigint(20)   | NO   |     | NULL                 |                                |
| used_size    | bigint(20)   | NO   |     | NULL                 |                                |
+--------------+--------------+------+-----+----------------------+--------------------------------+
9 rows in set (0.004 sec)

MySQL [oceanbase]> select distinct file_type from __all_space_usage;
+-----------------------+
| file_type             |
+-----------------------+
| tenant file data      |
| tenant file meta data |
| tenant index data     |
| tenant log data       |
| tenant tmp data       |
+-----------------------+
5 rows in set (0.008 sec)

select svr_ip,
round(sum(case when file_type='tenant file data' then data_size else 0 end)/1024/1024) filedata,
round(sum(case when file_type='tenant file meta data' then data_size else 0 end)/1024/1024) metadata,
round(sum(case when file_type='tenant index data' then data_size else 0 end)/1024/1024) indexdata,
round(sum(case when file_type='tenant log data' then data_size else 0 end)/1024/1024) logdata,
round(sum(case when file_type='tenant tmp data' then data_size else 0 end)/1024/1024) tmpdata
from  oceanbase.__all_space_usage
group by svr_ip;

---------------+----------+----------+-----------+---------+---------+
| svr_ip        | filedata | metadata | indexdata | logdata | tmpdata |
+---------------+----------+----------+-----------+---------+---------+
| 192.168.56.16 |  1041726 |      440 |    103120 |   25771 |       0 |
| 192.168.56.2  |   610436 |      388 |    204606 |   31567 |       0 |
| 192.168.56.20 |  1298792 |      508 |    159972 |  101685 |       0 |
| 192.168.56.21 |  1135890 |      482 |    205744 |   23836 |       0 |
| 192.168.56.26 |  1179408 |      486 |    200492 |   92460 |       0 |
| 192.168.56.29 |  1306674 |      474 |     73786 |   32354 |       0 |
| 192.168.56.33 |   994476 |      448 |    134270 |   47337 |       0 |
| 192.168.56.34 |   781544 |      400 |    131682 |  102306 |       0 |
| 192.168.56.38 |  1299116 |      492 |    100502 |   39373 |       0 |
+---------------+----------+-------

select svr_ip,
round(sum(case when file_type='tenant file data' then used_size else 0 end)/1024/1024) filedata,
round(sum(case when file_type='tenant file meta data' then used_size else 0 end)/1024/1024) metadata,
round(sum(case when file_type='tenant index data' then used_size else 0 end)/1024/1024) indexdata,
round(sum(case when file_type='tenant log data' then used_size else 0 end)/1024/1024) logdata,
round(sum(case when file_type='tenant tmp data' then used_size else 0 end)/1024/1024) tmpdata
from  oceanbase.__all_space_usage
group by svr_ip;

+---------------+----------+----------+-----------+---------+---------+
| svr_ip        | filedata | metadata | indexdata | logdata | tmpdata |
+---------------+----------+----------+-----------+---------+---------+
| 192.168.56.16 |  1041726 |      440 |    103120 |   26249 |       0 |
| 192.168.56.2  |   610436 |      388 |    204606 |   32081 |       0 |
| 192.168.56.20 |  1298792 |      508 |    159972 |  102966 |       0 |
| 192.168.56.21 |  1135890 |      482 |    205744 |   24249 |       0 |
| 192.168.56.26 |  1179408 |      486 |    200492 |   93649 |       0 |
| 192.168.56.29 |  1306674 |      474 |     73786 |   32802 |       0 |
| 192.168.56.33 |   994476 |      448 |    134270 |   47878 |       0 |
| 192.168.56.34 |   781544 |      400 |    131682 |  103587 |       0 |
| 192.168.56.38 |  1299116 |      492 |    100502 |   39950 |       0 |
+---------------+----------+----------+-----------+---------+---------+
9 rows in set (0.010 s

分析sstable占用

top table

select table_id,table_name,sum(size)/1024/1024 
from  oceanbase.gv$sstable  where table_type in (1,2,3)
group by table_id,table_name order by  sum(size)/1024/1024  desc limit 10;

+------------------+---------------------------------+---------------------+
| table_id         | table_name                      | sum(size)/1024/1024 |
+------------------+---------------------------------+---------------------+
| 1107208209220438 | TTA                             |         57.65620422 |
|    1099511628058 | __all_table_v2_history          |          4.64503098 |
| 1103909674337112 | sales                           |          3.11807442 |
|    1099511627930 | __all_server_event_history      |          2.42345238 |
|    1099511627898 | __all_column_statistic          |          1.64523125 |
|    1099511627916 | __all_rootservice_event_history |          1.51569557 |
|    1099511627891 | __all_column_history            |          1.34175682 |
|    1099511627973 | __all_package_history           |          1.28235626 |
|    1099511627972 | __all_package                   |          1.28005600 |
|    1099511627966 | __all_routine_history           |          0.49564362

分析memtab_mb，major_sst_mb，minor_sst_mb，trans_sst_mb占用

=============V3
  enum TableType {
    MEMTABLE = 0,
    MAJOR_SSTABLE = 1,
    MINOR_SSTABLE = 2,  // obsoleted type after 2.2
    TRANS_SSTABLE = 3,  // new table type from 3.1
    MULTI_VERSION_MINOR_SSTABLE = 4,
    COMPLEMENT_MINOR_SSTABLE = 5,            // new table type from 3.1
    MULTI_VERSION_SPARSE_MINOR_SSTABLE = 6,  // reserved table type
    MINI_MINOR_SSTABLE = 7,
    RESERVED_MINOR_SSTABLE = 8,
    MAX_TABLE_TYPE
  };
select svr_ip,
round(sum(case when table_type=0 then size else 0 end)/1024/1024) memtab_mb,
round(sum(case when table_type=1 then size else 0 end)/1024/1024) major_sst_mb,
round(sum(case when table_type=2 then size else 0 end)/1024/1024) minor_sst_mb,
round(sum(case when table_type=3 then size else 0 end)/1024/1024) trans_sst_mb,
round(sum(case when table_type in (4,5,6,7,8) then size else 0 end)/1024/1024) other_sst_mb
from  oceanbase.gv$sstable 
--where TABLE_NAME='OBJ1'
group by svr_ip;

+---------------+-----------+--------------+--------------+--------------+--------------+
| svr_ip        | memtab_mb | major_sst_mb | minor_sst_mb | trans_sst_mb | other_sst_mb |
+---------------+-----------+--------------+--------------+--------------+--------------+
| 192.168.56.33 |     13172 |       956833 |            0 |            0 |           59 |
| 192.168.56.21 |     13214 |      1155759 |            0 |            0 |           11 |
| 192.168.56.26 |      8740 |      1206002 |            0 |            0 |            5 |
| 192.168.56.38 |      7418 |      1218801 |            0 |            0 |          135 |
| 192.168.56.20 |     15118 |      1273883 |            0 |            0 |           63 |
| 192.168.56.16 |     17544 |      1020119 |            0 |            0 |         7463 |
| 192.168.56.34 |     18626 |       808394 |            0 |            0 |         7463 |
| 192.168.56.29 |      3414 |      1201491 |            0 |            0 |            6 |
| 192.168.56.2  |     22018 |       707580 |            0 |            0 |         7563 |
+---------------+-----------+--------------+--------------+--------------+--------------+
9 rows in set (4.528 sec)

MySQL [oceanbase]>

GC未完成(未释放)的TOP 对象

select  table_id,role,round(sum(required_size)/1024/1024/1024,2) gb  from __all_virtual_meta_table where (table_id,partition_id) in (select TABLE_ID,partition_id from __all_virtual_tenant_gc_partition_info
where (TABLE_ID,partition_id ) not in (select TABLE_ID,partition_id  from __all_virtual_table)) 
group by table_id,role order by round(sum(required_size)/1024/1024/1024,2) desc limit 10;

+------------------+------+------+
| table_id         | role | gb   |
+------------------+------+------+
| 1107208209220438 |    2 | 0.04 |
| 1107208209220438 |    1 | 0.02 |
+------------------+------+------+
2 rows in set (0.131 sec)

通过 macro_block_marker分析tmp macro block

data_count 为数据文件占用的宏块数量。
hold_count 为临时文件占用的宏块数量。

MySQL [oceanbase]> select svr_ip,total_count,data_count,hold_count from __all_virtual_macro_block_marker_status;
+---------------+-------------+------------+------------+
| svr_ip        | total_count | data_count | hold_count |
+---------------+-------------+------------+------------+
| 192.168.56.137|     1777676 |     719178 |       4417 |
| 192.168.56.153|     1777676 |     709122 |          0 |
| 192.168.56.163|     1777676 |     764377 |       6748 |
| 192.168.56.235|     1777676 |     928251 |       1951 |
| 192.168.56.208|     1777676 |     698419 |       2201 |
| 192.168.56.69 |     1777676 |     957971 |      13044 |
| 192.168.56.42 |     1777676 |     746152 |       3483 |
| 192.168.56.68 |     1777676 |     686968 |       6097 |
| 192.168.56.96 |     1777676 |     962730 |       3956 |
+---------------+-------------+------------+------------+
9 rows in set (0.035 sec)

分析临时文件占用

参考OB官方知识库

https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000685638?back=kb

https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000209899?back=kb

https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000209899

墨力计划 oceanbase

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者