Query memory usage information|V4.3.3| docs|Distributed Database

Query memory usage information

Last Updated：2024-12-02 03:48:29 Updated

This topic describes how to query virtual tables and logs for memory usage information.

Query views

Query the total memory of an OBServer node

You can query the oceanbase.gv$sysstat view from the sys tenant for the memory usage of a specified server.

obclient> SELECT * FROM oceanbase.gv$sysstat WHERE svr_ip = 'xx.xx.xx.xx' AND svr_port=2882 AND con_id=1 AND name like '%observer memory%';
+--------+----------------+----------+------------+---------------------------+-------+-------------+------------+---------+
| CON_ID | SVR_IP         | SVR_PORT | STATISTIC# | NAME                      | CLASS | VALUE       | VALUE_TYPE | STAT_ID |
+--------+----------------+----------+------------+---------------------------+-------+-------------+------------+---------+
|      1 |   xx.xx.xx.xx  |     2882 |        568 | observer memory used size |    64 | 12735815680 | SET_VALUE  |  140008 |
|      1 |   xx.xx.xx.xx  |     2882 |        569 | observer memory free size |    64 |   224395264 | SET_VALUE  |  140009 |
|      1 |   xx.xx.xx.xx  |     2882 |        571 | observer memory hold size |    64 | 12960210944 | SET_VALUE  |  140011 |
+--------+----------------+----------+------------+---------------------------+-------+-------------+------------+---------+
3 rows in set

Query the used memory of each tenant

You can query the oceanbase.GV$OB_TENANT_MEMORY view from the sys tenant for the used memory of each tenant.

obclient> SELECT tenant_id, hold FROM oceanbase.GV$OB_TENANT_MEMORY WHERE svr_ip = 'xx.xx.xx.xx' AND svr_port=2882  GROUP BY tenant_id;
+-----------+------------+
| tenant_id | hold       |
+-----------+------------+
|         1 | 1308491776 |
|        21 |          0 |
|       500 | 8969994240 |
|       506 |    2097152 |
|       507 |    2097152 |
|       508 |   18874368 |
|       509 |    2097152 |
|       510 |    2097152 |
|       512 |    2097152 |
|       999 |    2097152 |
|      1001 |  524156928 |
|      1002 |  593408000 |
|      1003 |  522059776 |
|      1004 |  771678208 |
+-----------+------------+
14 rows in set

Query the used memory of a specified tenant

You can query the oceanbase.GV$OB_MEMORY view from the sys tenant for the used memory of a specified tenant.

obclient> SELECT tenant_id, CTX_NAME, hold FROM oceanbase.GV$OB_MEMORY WHERE svr_ip = 'xx.xx.xx.xx' AND svr_port=2882 AND tenant_id = 1002 GROUP BY CTX_NAME;
+-----------+-----------------+-----------+
| tenant_id | CTX_NAME        | hold      |
+-----------+-----------------+-----------+
|      1002 | APPLY_STATUS    |      8128 |
|      1002 | CACHE_MAP_NODE  |  16646144 |
|      1002 | ClogGe          |    311296 |
|      1002 | ColUsagHashMap  |    212736 |
|      1002 | DDLKvMgrObj     |    524288 |
|      1002 | DeadLock        |      8128 |
|      1002 | DmlStatMap      |    212688 |
|      1002 | FlushLog        |     65536 |
|      1002 | FlushMeta       |      8128 |
|      1002 | GtsTaskQueue    |    286720 |
|      1002 | HashBuckLCSta   |   1589248 |
|      1002 | HashBuckPlanCac |    794624 |
|      1002 | HashBuckPsCache |   1187840 |
|      1002 | HashBuckPsInfo  |   1187840 |
|      1002 | HashNode        |      7968 |
|      1002 | HashNodeLCSta   |    111104 |
|      1002 | HashNodePlanCac |     55552 |
|      1002 | HashNodePsCache |      7968 |
|      1002 | HashNodePsInfo  |      7936 |
|      1002 | IdxBlkTreePath  |     89280 |
|      1002 | LobAllocator    |     65536 |
|      1002 | LockMemObj      |     65536 |
|      1002 | LockWaitMgr     |      8128 |
|      1002 | LogApplyStatus  |     13568 |
|      1002 | LogGroupBuffer  |  83927040 |
|      1002 | LogReplayStatus |     32768 |
|      1002 | LOG_HASH_MAP    |      1120 |
|      1002 | LSMap           |   4249664 |
|      1002 | LSSvr           |    269248 |
|      1002 | MatchOffsetMap  |     16256 |
|      1002 | Memstore        |  64503808 |
|      1002 | MemtableCallbac |    260096 |
|      1002 | MemTblMgrObj    |    393216 |
|      1002 | MemTblObj       |   1048576 |
|      1002 | MetaAllocator   |  14385792 |
|      1002 | MysqlRequesReco |  86261760 |
|      1002 | OmtTenant       |  10674448 |
|      1002 | PartTranCtxMgr  |    802816 |
|      1002 | PlanBaselineMgr |        96 |
|      1002 | PoolFreeList    |   5250880 |
|      1002 | QueryAllocator  |     16256 |
|      1002 | REPLAY_STATUS   |      8128 |
|      1002 | SqlDtl          |   3907584 |
|      1002 | SqlMemMgr       |     24272 |
|      1002 | SqlPhyPlan      | 143297472 |
|      1002 | SqlPhyPlObj     |   1099840 |
|      1002 | SqlPlanCache    |   7868160 |
|      1002 | SqlPlanMon      |   5021696 |
|      1002 | SqlPsCache      |      4144 |
|      1002 | SSTblObj        |   2097152 |
|      1002 | TabletObj       |   1638400 |
|      1002 | thread_factor   |     32768 |
|      1002 | TxCtxMemObj     |     65536 |
|      1002 | TxDataMemObj    |     65536 |
|      1002 | TX_DATA_TABLE   |   1815104 |
+-----------+-----------------+-----------+
55 rows in set

Query the memory usage details of a memory module

You can query the oceanbase.GV$OB_MEMORY view from the sys tenant for the memory usage details of a memory module. You can also specify a server or tenant in the query statement.

obclient> SELECT * FROM oceanbase.GV$OB_MEMORY WHERE svr_ip = 'xx.xx.xx.xx' AND svr_port=2882 AND tenant_id = 1002 ORDER BY hold desc limit 10;
+-----------+----------------+----------+-------------------+-----------------+-------+------------+-----------+
| TENANT_ID | SVR_IP         | SVR_PORT | CTX_NAME          | MOD_NAME        | COUNT | HOLD       | USED      |
+-----------+----------------+----------+-------------------+-----------------+-------+------------+-----------+
|      1002 | xx.xx.xx.xx    |     2882 | KVSTORE_CACHE_ID  | KvstorCacheMb   |   498 | 1044381696 |         0 |
|      1002 | xx.xx.xx.xx    |     2882 | PLAN_CACHE_CTX_ID | SqlPhyPlan      |  7069 |  135522752 | 120440953 |
|      1002 | xx.xx.xx.xx    |     2882 | DEFAULT_CTX_ID    | MysqlRequesReco |    11 |  100827136 | 100797440 |
|      1002 | xx.xx.xx.xx    |     2882 | DEFAULT_CTX_ID    | IoControl       |    37 |  100143104 | 100086048 |
|      1002 | xx.xx.xx.xx    |     2882 | DEFAULT_CTX_ID    | LogGroupBuffer  |     2 |   83927040 |  83886080 |
|      1002 | xx.xx.xx.xx    |     2882 | CO_STACK          | CoStack         |   156 |   79233024 |  79082016 |
|      1002 | xx.xx.xx.xx    |     2882 | DEFAULT_CTX_ID    | ServerObjecPool |     1 |   63983616 |  63963136 |
|      1002 | xx.xx.xx.xx    |     2882 | MEMSTORE_CTX_ID   | Memstore        |    21 |   43696128 |  43676136 |
|      1002 | xx.xx.xx.xx    |     2882 | DEFAULT_CTX_ID    | SqlDtl          |   191 |   23449216 |  21915426 |
|      1002 | xx.xx.xx.xx    |     2882 | DEFAULT_CTX_ID    | SqlPlanManger   |    54 |   23281664 |  22815744 |
+-----------+----------------+----------+-------------------+-----------------+-------+------------+-----------+
10 rows in set

Query logs

Query the total memory of an OBServer node

Log in to the server where the OBServer node resides.
Run the following command to enter the directory where log files are located:
```
cd ~/oceanbase/log
```

Run the following command to query the total memory usage of the OBServer node:

grep CHUNK_MGR observer.log

The query result is as follows:

[2022-07-14 09:29:26.243252] INFO  [STORAGE] print_tenant_usage (ob_tenant_memory_printer.cpp:121) [104667][ServerGTimer][T0][Y0-0000000000000000-0-0] [lt=6] [CHUNK_MGR] free=42 pushes=2504440 pops=2504398 limit= 85,899,345,920 hold= 10,254,864,384 total_hold= 10,655,629,312 used= 10,166,784,000 freelist_hold=     88,080,384 maps=        159,816 unmaps=        156,719 large_maps=        156,935 large_unmaps=        156,719 memalign=0 virtual_memory_used= 11,851,849,728
[2022-07-14 09:29:36.309803] INFO  [STORAGE] print_tenant_usage (ob_tenant_memory_printer.cpp:121) [104667][ServerGTimer][T0][Y0-0000000000000000-0-0] [lt=11] [CHUNK_MGR] free=39 pushes=2504609 pops=2504570 limit= 85,899,345,920 hold= 10,254,864,384 total_hold= 10,655,629,312 used= 10,173,075,456 freelist_hold=     81,788,928 maps=        159,826 unmaps=        156,729 large_maps=        156,945 large_unmaps=        156,729 memalign=0 virtual_memory_used= 11,851,849,728
[2022-07-14 09:29:46.376095] INFO  [STORAGE] print_tenant_usage (ob_tenant_memory_printer.cpp:121) [104667][ServerGTimer][T0][Y0-0000000000000000-0-0] [lt=6] [CHUNK_MGR] free=36 pushes=2504774 pops=2504738 limit= 85,899,345,920 hold= 10,254,864,384 total_hold= 10,655,629,312 used= 10,179,366,912 freelist_hold=     75,497,472 maps=        159,836 unmaps=        156,739 large_maps=        156,955 large_unmaps=        156,739 memalign=0 virtual_memory_used= 11,851,849,728
[2022-07-14 09:29:56.455361] INFO  [STORAGE] print_tenant_usage (ob_tenant_memory_printer.cpp:121) [104667][ServerGTimer][T0][Y0-0000000000000000-0-0] [lt=9] [CHUNK_MGR] free=33 pushes=2504938 pops=2504905 limit= 85,899,345,920 hold= 10,254,864,384 total_hold= 10,655,629,312 used= 10,185,658,368 freelist_hold=     69,206,016 maps=        159,846 unmaps=        156,749 large_maps=        156,965 large_unmaps=        156,749 memalign=0 virtual_memory_used= 11,851,849,728
[2022-07-14 09:30:06.520032] INFO  [STORAGE] print_tenant_usage (ob_tenant_memory_printer.cpp:121) [104667][ServerGTimer][T0][Y0-0000000000000000-0-0] [lt=7] [CHUNK_MGR] free=32 pushes=2505099 pops=2505067 limit= 85,899,345,920 hold= 10,254,864,384 total_hold= 10,655,629,312 used= 10,187,755,520 freelist_hold=     67,108,864 maps=        159,856 unmaps=        156,759 large_maps=        156,975 large_unmaps=        156,759 memalign=0 virtual_memory_used= 11,851,849,728
[2022-07-14 09:30:16.586924] INFO  [STORAGE] print_tenant_usage (ob_tenant_memory_printer.cpp:121) [104667][ServerGTimer][T0][Y0-0000000000000000-0-0] [lt=8] [CHUNK_MGR] free=32 pushes=2505261 pops=2505229 limit= 85,899,345,920 hold= 10,254,864,384 total_hold= 10,655,629,312 used= 10,187,755,520 freelist_hold=     67,108,864 maps=        159,866 unmaps=        156,769 large_maps=        156,985 large_unmaps=        156,769 memalign=0 virtual_memory_used= 11,851,849,728

The following table describes the parameters in the log information.

Parameter	Description
limit	The maximum total memory of the OBServer node.
hold	The total memory obtained by the OBServer node from the operating system.
used	The memory (including caches) used by the OBServer node.
freelist_hold	The memory not used by the OBServer node.

Query the memory usage of each tenant

Log in to the server where the OBServer node resides.
Run the following command to enter the directory where log files are located:
```
cd ~/oceanbase/log
```

Run the following command to view the memory usage of each tenant:

grep ob_malloc_allocator.cpp observer.log

A sample query result is as follows:

[2022-07-14 09:29:26.241149] INFO  [LIB] operator() (ob_malloc_allocator.cpp:387) [104667][ServerGTimer][T0][Y0-0000000000000000-0-0] [lt=8] [MEMORY] tenant: 500, limit: 9,223,372,036,854,775,807 hold: 6,765,867,008 rpc_hold: 0 cache_hold: 0 cache_used: 0 cache_item_count: 0
[2022-07-14 09:29:26.241729] INFO  [LIB] operator() (ob_malloc_allocator.cpp:387) [104667][ServerGTimer][T1][Y0-0000000000000000-0-0] [lt=10] [MEMORY] tenant: 1, limit: 17,179,869,184 hold: 1,170,079,744 rpc_hold: 0 cache_hold: 144,703,488 cache_used: 144,703,488 cache_item_count: 69
[2022-07-14 09:29:26.242025] INFO  [LIB] operator() (ob_malloc_allocator.cpp:387) [104667][ServerGTimer][T1001][Y0-0000000000000000-0-0] [lt=5] [MEMORY] tenant: 1001, limit: 1,073,741,824 hold: 511,574,016 rpc_hold: 0 cache_hold: 218,103,808 cache_used: 218,103,808 cache_item_count: 104
[2022-07-14 09:29:26.242321] INFO  [LIB] operator() (ob_malloc_allocator.cpp:387) [104667][ServerGTimer][T1002][Y0-0000000000000000-0-0] [lt=8] [MEMORY] tenant: 1002, limit: 6,442,450,944 hold: 534,687,744 rpc_hold: 0 cache_hold: 44,040,192 cache_used: 44,040,192 cache_item_count: 21
[2022-07-14 09:29:26.242625] INFO  [LIB] operator() (ob_malloc_allocator.cpp:387) [104667][ServerGTimer][T1003][Y0-0000000000000000-0-0] [lt=4] [MEMORY] tenant: 1003, limit: 1,073,741,824 hold: 511,574,016 rpc_hold: 0 cache_hold: 224,395,264 cache_used: 224,395,264 cache_item_count: 107
[2022-07-14 09:29:26.242901] INFO  [LIB] operator() (ob_malloc_allocator.cpp:387) [104667][ServerGTimer][T1004][Y0-0000000000000000-0-0] [lt=8] [MEMORY] tenant: 1004, limit: 6,442,450,944 hold: 633,266,176 rpc_hold: 0 cache_hold: 65,011,712 cache_used: 65,011,712 cache_item_count: 31
[2022-07-14 09:29:36.307729] INFO  [LIB] operator() (ob_malloc_allocator.cpp:387) [104667][ServerGTimer][T0][Y0-0000000000000000-0-0] [lt=10] [MEMORY] tenant: 500, limit: 9,223,372,036,854,775,807 hold: 6,765,867,008 rpc_hold: 0 cache_hold: 0 cache_used: 0 cache_item_count: 0

Query the memory usage details of a specified tenant

Log in to the server where the OBServer node resides.
Run the following command to enter the directory where log files are located:
```
cd ~/oceanbase/log
```

Perform the following steps to view the total memory usage of the tenant with the ID 1002.

Use Vim to open the observer.log file.

Search for ob_malloc_allocator.cpp.*1002.

[MEMORY] ctx_id=           DEFAULT_CTX_ID hold_bytes=    258,838,528 limit= 9,223,372,036,854,775,807
[MEMORY] ctx_id=          MEMSTORE_CTX_ID hold_bytes=     67,108,864 limit= 9,223,372,036,854,775,807
[MEMORY] ctx_id=         TRANS_CTX_MGR_ID hold_bytes=      2,097,152 limit= 9,223,372,036,854,775,807
[MEMORY] ctx_id=        PLAN_CACHE_CTX_ID hold_bytes=    132,120,576 limit= 9,223,372,036,854,775,807
[MEMORY] ctx_id=                WORK_AREA hold_bytes=      4,194,304 limit=               322,122,545
[MEMORY] ctx_id=          META_OBJ_CTX_ID hold_bytes=     22,093,824 limit=               644,245,090
[MEMORY] ctx_id=       TX_CALLBACK_CTX_ID hold_bytes=      2,097,152 limit= 9,223,372,036,854,775,807
[MEMORY] ctx_id=               LOB_CTX_ID hold_bytes=      2,097,152 limit= 9,223,372,036,854,775,807

Query the memory usage details of a memory module

Log in to the server where the OBServer node resides.
Run the following command to enter the directory where log files are located:
```
cd ~/oceanbase/log
```

Perform the following steps to view the memory usage details of the DEFAULT_CTX_ID module in the tenant with the ID 1002.

Use Vim to open the observer.log file.

Search for MEMORY.*1002.*DEFAULT_CTX_ID.

[MEMORY] tenant_id= 1002 ctx_id=           DEFAULT_CTX_ID hold=    258,838,528 used=    216,411,808 limit= 9,223,372,036,854,775,807
[MEMORY] idle_size=         0 free_size=         0
[MEMORY] wash_related_chunks=         0 washed_blocks=         0 washed_size=         0
[MEMORY] hold=     86,261,760 used=     86,239,232 count=       4 avg_used=     21,559,808 mod=MysqlRequesReco
[MEMORY] hold=     83,927,040 used=     83,886,080 count=       2 avg_used=     41,943,040 mod=LogGroupBuffer
[MEMORY] hold=     16,646,144 used=     16,637,952 count=       8 avg_used=      2,079,744 mod=CACHE_MAP_NODE
[MEMORY] hold=     10,674,448 used=     10,631,028 count=      42 avg_used=        253,119 mod=OmtTenant
[MEMORY] hold=      5,021,696 used=      4,977,920 count=       3 avg_used=      1,659,306 mod=SqlPlanMon
[MEMORY] hold=      4,249,664 used=      4,229,120 count=       2 avg_used=      2,114,560 mod=LSMap
[MEMORY] hold=      1,589,248 used=      1,573,024 count=       2 avg_used=        786,512 mod=HashBuckLCSta
[MEMORY] hold=      1,571,264 used=      1,558,656 count=     197 avg_used=          7,911 mod=TX_DATA_TABLE
[MEMORY] hold=      1,548,288 used=      1,383,795 count=      21 avg_used=         65,895 mod=SqlDtl
[MEMORY] hold=      1,187,840 used=      1,179,768 count=       1 avg_used=      1,179,768 mod=HashBuckPsInfo
[MEMORY] hold=      1,187,840 used=      1,179,768 count=       1 avg_used=      1,179,768 mod=HashBuckPsCache
[MEMORY] hold=        794,624 used=        786,512 count=       1 avg_used=        786,512 mod=HashBuckPlanCac