开表、内存及元数据参数分析

发表于2025-03-05 19:01:4227次浏览2个评论

#### 参数：
1 最大可缓冲的打开中的TABLE数量
参数名：table_open_cache
说明：参数设置建议根据并发数，每个session涉及的表数量计算。比如100个并发，每个访问N个表(无论是否为同一个表),那么这个参数建议设置成100*N。当然实际设置时，是要考虑系统使用的量，比如中间临时表。如果表OPEN数量超过了参数，且没有可回收的(LRU)，有可能临时打开表，使用后立即释放。
SHOW GLOBAL VARIABLES LIKE '%table_open_cache%';
查看已打开的table数量：
SHOW GLOBAL STATUS LIKE 'opened_tables';
查看当前打开的table数量：
SHOW GLOBAL STATUS LIKE 'open_tables';
查看当前打开的table：
show open tables;
show open tables from testdb;

2 定义表结构frm缓冲数量
参数名：table_definition_cache
说明：可以缓冲表的frm结构。参数设置建议根据实际表的数量，特别是近期常用的表数量进行缓冲，避免过大而浪费内存（但一半frm表结构都很小，K几倍，1万个也就10M)，过小则每次从磁盘读取影响性能。如果当前要打开的超过定义的最大值，且在prepare 的SQL里，会报 Prepared statement needs to be re-prepared 错误。
SHOW GLOBAL VARIABLES LIKE '%table_definition_cache%';
SHOW GLOBAL STATUS LIKE '%table_definition%';

3 限制表实例数量
参数名：_gbase_express_table_limit
数值范围：最小值 16，最大值 10241024，默认值 16*1024
说明：当引擎层打开表数量达到参数设定的值时，不会立即触发清理动作。而是由后台线程每 5 秒检测一次，如果达到上限才会触发清理动作。

4 限制表实例元数据总大小
参数名：_gbase_express_table_metadata_limit
数值范围：gnode 下默认值为 temp 堆大小一半，gcluster 下默认值为 1GB，最小值最大值不限，配置文件中可使用 K/M/G
方式设置说明：当引擎层打开表实例的元数据大小之和达到参数设定的值时，不会立即触发清理动作。而是由后台线程每 5 秒检测一次，如果达到上限才会触发清理动作。

5 超出限制后对 m_tables 清理比例
参数名：_gbase_express_table_clear_rate
数值范围：最大值 100（表示 100%），最小值 1（表示 1%），默认值 10（表示 10%）
说明： 1) 表数量淘汰机制：当触发清理动作时每次清理多少比例的表对象实例。此比例是当前内存中表实例总数的比例。 2) 元数据大小淘汰机制：每次清理多少比例的元数据大小。此比例是当前内存中所有表实例的元数据大小之和的比例。

6 查看元数据当前情况（gn层）
1）查看元数据参数值

gbase> show variables like '%express_table%';
+-------------------------------------+-----------+
| _gbase_express_table_clear_rate     | 10        |
| _gbase_express_table_limit          | 16384     |
| _gbase_express_table_metadata_limit | 134217728 |
+-------------------------------------+-----------+

2）查看缓存元数据数量
express_cached_tables 表示当前内存中有多少个表。
express_cached_metadata 表示当前内存中所有表的元数据大小。

gbase> show status like '%express%';
+-------------------------+-------+
| express_cached_metadata | 49637 |
| express_cached_tables   | 2     |
+-------------------------+-------+

7 内存使用信息 gn层）
1）performance_schema.heap_usage_info 当前节点的系统堆内存信息


+-------------+------------+-----------+--------------+----------------+----------------+----------------+-------------+
| HOST        | HEAP_TYPE  | HEAP_SIZE | USED_IN_HEAP | USED_IN_SYSTEM | MAX_USED_BLOCK | MAX_FREE_BLOCK | USED_BLOCKS |
+-------------+------------+-----------+--------------+----------------+----------------+----------------+-------------+
| GBASEDATA01 | dc_heap    | 536870912 |          128 |              0 |       11984832 |      536862720 |           5 |
| GBASEDATA01 | temp_heap  | 268435456 |         7344 |              0 |        1898528 |      268414976 |         121 |
| GBASEDATA01 | large_heap | 268435456 |            0 |              0 |          81936 |      268435456 |           0 |
+-------------+------------+-----------+--------------+----------------+----------------+----------------+-------------+
列名            说明
HOST            主机名(本机)
HEAP_TYPE        堆类型(dc_heap，temp_heap，large_heap）
HEAP_SIZE        堆总大小，单位字节
USED_IN_HEAP    堆使用中的大小，单位字节
USED_IN_SYSTEM    从操作系统额外申请的大小，单位字节
MAX_USED_BLOCK    当前堆中，最大块的大小，单位字节
MAX_FREE_BLOCK    当前堆中，最大可用块的大小，单位字节
USED_BLOCKS        堆中使用的块个数（申请成功的次数)

1.1）performance_schema.mem_detail_cell CELL堆内存使用详情

gbase> select class,count(class),sum(size) from performance_schema.mem_detail_cell group by class;
+-------+--------------+-----------+
| class | count(class) | sum(size) |
+-------+--------------+-----------+
|     7 |            1 |         5 |
|    20 |            1 |         8 |
|    21 |            1 |         4 |
|    22 |            1 |         2 |
|    23 |            1 |         8 |
+-------+--------------+-----------+
DC CLASS:
27 - str类型存储的压缩数据  grep -B 3 "LIRS_ReleaseCell" stack文件  #69477 LIRS淘汰机制BUG， patach.9修复，临时解决方案 kill gbased

1.2）performance_schema.mem_detail_large LARGE堆内存使用详情

gbase> select class,count(class),sum(size) from performance_schema.mem_detail_large group by class;
Empty set (Elapsed: 00:00:00.00)
LARGE CLASS:
14 - 内存泄漏

1.3）performance_schema.mem_detail_temp TEMP堆内存使用详情

gbase> select class,count(class),sum(size) from performance_schema.mem_detail_temp group by class;
+-------+--------------+-----------+
| class | count(class) | sum(size) |
+-------+--------------+-----------+
|     2 |           30 |      2400 |
|     4 |           30 |       240 |
|    59 |           61 |      2393 |
+-------+--------------+-----------+
TEMP CLASS:
115 - temp堆发生内存泄漏
107&164 - DTS组件接收端队列缓存

2）performance_schema.cache_usage_info 当前服务的内存缓冲信息

gbase> select * from performance_schema.cache_usage_info;
+-------------+----------+-------------+---------------+------------------+--------------+-----------------+-------------------+----------------------+---------------+------------------+--------------------+-----------------------+----------+
| HOST        | TOTAL_DC | UNLOCKED_DC | TOTAL_DC_SIZE | UNLOCKED_DC_SIZE | HOT_TOTAL_DC | HOT_UNLOCKED_DC | HOT_TOTAL_DC_SIZE | HOT_UNLOCKED_DC_SIZE | COLD_TOTAL_DC | COLD_UNLOCKED_DC | COLD_TOTAL_DC_SIZE | COLD_UNLOCKED_DC_SIZE | HIT_RATE |
+-------------+----------+-------------+---------------+------------------+--------------+-----------------+-------------------+----------------------+---------------+------------------+--------------------+-----------------------+----------+
| GBASEDATA01 |        8 |           8 |         16920 |            16920 |            8 |               8 |             16920 |                16920 |             0 |                0 |                  0 |                     0 | 90.78%   |
+-------------+----------+-------------+---------------+------------------+--------------+-----------------+-------------------+----------------------+---------------+------------------+--------------------+-----------------------+----------+
字段                        说明
HOST                    主机名字，就是本机
TOTAL_DC                DC总数量
UNLOCKED_DC                没有被锁住的DC总数量(表示可以被redlease释放)
TOTAL_DC_SIZE            DC占用总内存大小(单位:字节)
UNLOCKED_DC_SIZE        没有被锁住的DC占用内存大小(单位:字节)
HOT_TOTAL_DC            热数据DC数量
HOT_UNLOCKED_DC            没有被锁住的热数据DC数量
HOT_TOTAL_DC_SIZE        热数据DC占用总内存大小(单位:字节)
HOT_UNLOCKED_DC_SIZE    没有被锁住的热数据DC占用内存大小(单位:字节)
COLD_TOTAL_DC            冷数据DC数量
COLD_UNLOCKED_DC        没有被锁住的冷数据DC数量
COLD_TOTAL_DC_SIZE        冷数据DC占用总内存大小(单位:字节)
COLD_UNLOCKED_DC_SIZE    没有被锁住的冷数据DC占用内存大小(单位:字节)
HIT_RATE                DC命中率

2.1）performance_schema.cache_cell_status_info 表包含了每个dc的具体详情

gbase> select * from performance_schema.cache_cell_status_info;
+----------+-----------+----+------------+--------+------+
| TABLE_ID | COLUMN_ID | DP | LOCK_COUNT | LOCKED | SIZE |
+----------+-----------+----+------------+--------+------+
|      276 |         1 |  0 |          0 |      0 |   70 |
|      229 |         5 |  0 |          0 |      0 | 8204 |
|      229 |         3 |  0 |          0 |      0 |  115 |
|      229 |         2 |  0 |          0 |      0 |  156 |
|      229 |         6 |  0 |          0 |      0 | 8221 |
|      229 |         4 |  0 |          0 |      0 |  120 |
|      229 |         1 |  0 |          0 |      0 |   17 |
|      229 |         0 |  0 |          0 |      0 |   17 |
+----------+-----------+----+------------+--------+------+
字段        说明
TABLE_ID    表编号（GBase 8a查看表的tableId)
COLUMN_ID    列编号,从0开始
DP            DC的位置，从0开始
LOCK_COUNT    被锁的次数
LOCKED        锁住状态，1=是，0=否
SIZE        占用内存大小

8 分区表元数据
当查询指定分区的数据时，元数据只打开指定的分区，不会将其全部打开。当alter add一列分区时，会将全部元数据释放掉。


9 元数据的清理
将元数据释放可以通过重启集群，或者执行refresh tables;
gbase> refresh tables;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> show status like '%express%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| express_cached_metadata | 0     |
| express_cached_tables   | 0     |
+-------------------------+-------+
gbase> select * from performance_schema.cache_usage_info;
+-------------+----------+-------------+---------------+------------------+--------------+-----------------+-------------------+----------------------+---------------+------------------+--------------------+-----------------------+----------+
| HOST        | TOTAL_DC | UNLOCKED_DC | TOTAL_DC_SIZE | UNLOCKED_DC_SIZE | HOT_TOTAL_DC | HOT_UNLOCKED_DC | HOT_TOTAL_DC_SIZE | HOT_UNLOCKED_DC_SIZE | COLD_TOTAL_DC | COLD_UNLOCKED_DC | COLD_TOTAL_DC_SIZE | COLD_UNLOCKED_DC_SIZE | HIT_RATE |
+-------------+----------+-------------+---------------+------------------+--------------+-----------------+-------------------+----------------------+---------------+------------------+--------------------+-----------------------+----------+
| GBASEDATA01 |        0 |           0 |             0 |                0 |            0 |               0 |                 0 |                    0 |             0 |                0 |                  0 |                     0 | 90.78%   |
+-------------+----------+-------------+---------------+------------------+--------------+-----------------+-------------------+----------------------+---------------+------------------+--------------------+-----------------------+----------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select * from performance_schema.cache_cell_status_info;
Empty set (Elapsed: 00:00:00.00)
gbase> select * from tdb.t1_n2;
Empty set (Elapsed: 00:00:00.01)
gbase> select * from tdb.t1_n1;
+------+------+------------+------+------+---------------------+------+
| a    | b    | c          | d    | e    | f                   | g    |
+------+------+------------+------+------+---------------------+------+
|    0 |    1 | DMD        | kds  | dmd  | 2013-04-01 10:23:01 | 1.10 |
|    0 |    3 | DMD        | cj   | dmd  | 2013-04-01 10:23:01 | 2.10 |
|    1 |    3 | DMD        | lm   | dmd1 | 2013-04-01 10:23:01 | 2.20 |
|    1 |    4 | DMD        | zx   | dmd2 | 2013-04-01 10:23:01 | 2.30 |
+------+------+------------+------+------+---------------------+------+
4 rows in set (Elapsed: 00:00:00.00)
gbase> show status like '%express%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| express_cached_metadata | 55804 |
| express_cached_tables   | 2     |
+-------------------------+-------+
2 rows in set (Elapsed: 00:00:00.00)
gbase> select * from performance_schema.cache_usage_info;
+-------------+----------+-------------+---------------+------------------+--------------+-----------------+-------------------+----------------------+---------------+------------------+--------------------+-----------------------+----------+
| HOST        | TOTAL_DC | UNLOCKED_DC | TOTAL_DC_SIZE | UNLOCKED_DC_SIZE | HOT_TOTAL_DC | HOT_UNLOCKED_DC | HOT_TOTAL_DC_SIZE | HOT_UNLOCKED_DC_SIZE | COLD_TOTAL_DC | COLD_UNLOCKED_DC | COLD_TOTAL_DC_SIZE | COLD_UNLOCKED_DC_SIZE | HIT_RATE |
+-------------+----------+-------------+---------------+------------------+--------------+-----------------+-------------------+----------------------+---------------+------------------+--------------------+-----------------------+----------+
| GBASEDATA01 |        7 |           7 |         16850 |            16850 |            7 |               7 |             16850 |                16850 |             0 |                0 |                  0 |                     0 | 89.89%   |
+-------------+----------+-------------+---------------+------------------+--------------+-----------------+-------------------+----------------------+---------------+------------------+--------------------+-----------------------+----------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select * from performance_schema.cache_cell_status_info;
+----------+-----------+----+------------+--------+------+
| TABLE_ID | COLUMN_ID | DP | LOCK_COUNT | LOCKED | SIZE |
+----------+-----------+----+------------+--------+------+
|      229 |         5 |  0 |          0 |      0 | 8204 |
|      229 |         3 |  0 |          0 |      0 |  115 |
|      229 |         2 |  0 |          0 |      0 |  156 |
|      229 |         6 |  0 |          0 |      0 | 8221 |
|      229 |         4 |  0 |          0 |      0 |  120 |
|      229 |         1 |  0 |          0 |      0 |   17 |
|      229 |         0 |  0 |          0 |      0 |   17 |
+----------+-----------+----+------------+--------+------+
7 rows in set (Elapsed: 00:00:00.00)
gbase> release cache;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> show status like '%express%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| express_cached_metadata | 55804 |
| express_cached_tables   | 2     |
+-------------------------+-------+
2 rows in set (Elapsed: 00:00:00.01)
gbase> select * from performance_schema.cache_usage_info;
+-------------+----------+-------------+---------------+------------------+--------------+-----------------+-------------------+----------------------+---------------+------------------+--------------------+-----------------------+----------+
| HOST        | TOTAL_DC | UNLOCKED_DC | TOTAL_DC_SIZE | UNLOCKED_DC_SIZE | HOT_TOTAL_DC | HOT_UNLOCKED_DC | HOT_TOTAL_DC_SIZE | HOT_UNLOCKED_DC_SIZE | COLD_TOTAL_DC | COLD_UNLOCKED_DC | COLD_TOTAL_DC_SIZE | COLD_UNLOCKED_DC_SIZE | HIT_RATE |
+-------------+----------+-------------+---------------+------------------+--------------+-----------------+-------------------+----------------------+---------------+------------------+--------------------+-----------------------+----------+
| GBASEDATA01 |        0 |           0 |             0 |                0 |            0 |               0 |                 0 |                    0 |             0 |                0 |                  0 |                     0 | 89.89%   |
+-------------+----------+-------------+---------------+------------------+--------------+-----------------+-------------------+----------------------+---------------+------------------+--------------------+-----------------------+----------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select * from performance_schema.cache_cell_status_info;
Empty set (Elapsed: 00:00:00.01)

https://www.gbase8.cn/366
https://www.gbase8.cn/5887

登录后才可以发表评论

levvel发表于 6个月前

人生贵相知，何用金与钱

levvel发表于 2个月前

太有道理了！

开表、内存及元数据参数分析

评论

热门帖子