Calculate the Catalog Size in Memory on Each Node: Quick Tip

Posted November 28, 2018 by James Knicely, Vertica Field Chief Technologist

Three 3D arrows, different colors pointing in different directions
The RESOURCE_POOL_STATUS system table provides the current state of built-in and user-defined resource pools on each node, including memory usage. The METADATA built-in pool tracks memory allocated for catalog data and storage data structures.

Knowing those two facts, we can calculate catalog memory usage on all nodes by querying the system table RESOURCE_POOL_STATUS for the METADATA pool!

Example: dbadmin=> SELECT node_name, pool_name, memory_size_kb/1024^2 AS memory_size_kb_gb dbadmin-> FROM resource_pool_status dbadmin-> WHERE pool_name = 'metadata'; node_name | pool_name | memory_size_kb_gb --------------------+-----------+------------------- v_test_db_node0001 | metadata | 0.104231834411621 v_test_db_node0002 | metadata | 0.104231824231921 v_test_db_node0003 | metadata | 0.104231834213117 (1 row) Helpful Links:

https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/RESOURCE_POOL_STATUS.htm

https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/Built-inPools.htm

Have fun!