Question on data collector table dc_allocation_pool_statistics

[Fred]

Hello,

I was exploring the options get the resource usage statistics at the resource pool level and thats when I ended up with a set of these data collector tables, which I thought would collect the statistics by time slice as per its naming suffix. However, to my suprise, I could not find any of the resource pools in the system and I see two new entries called 'SAL' and 'Global'. Does anyone have any idea as to what thse pool names are?

Also, is there any system table which could give the historical memory usage statistics at each resource pool level throughout a 24 hour window?

Code: Select all

dbadmin=> select table_name,description from v_monitor.data_collector where component like 'AllocationPoolStatistics%';
               table_name                |                                                    description
-----------------------------------------+--------------------------------------------------------------------------------------------------------------------
 dc_allocation_pool_statistics           | Information about global memory pools, which generally cannot be recovered without restart
 dc_allocation_pool_statistics_by_second | Information about global memory pools, which generally cannot be recovered without restart (historical, by second)
 dc_allocation_pool_statistics_by_minute | Information about global memory pools, which generally cannot be recovered without restart (historical, by minute)
 dc_allocation_pool_statistics_by_hour   | Information about global memory pools, which generally cannot be recovered without restart (historical, by hour)
 dc_allocation_pool_statistics_by_day    | Information about global memory pools, which generally cannot be recovered without restart (historical, by day)
(5 rows)

dbadmin=>

dbadmin=> select pool_name from dc_allocation_pool_statistics_by_hour group by pool_name;
 pool_name
-----------
 Global
 SAL
(2 rows)

dbadmin=>

Thanks, Fred

[NorbertKrupa]

What version are you on?

[Fred]

My db version is v7.0.1-0

Fred

[nnani]

Hi Fred,

These resource pools generally deal with global catalog size.
Since the global catalog is held in memory, this system table shows two types of memory, they both comprise to give you the catalog size which is held in memory by Vertica.

So SAL and Global are types of memory and not resource pools.

[Fred]

Thank you nnani. However, I am looking for historical resource pool usage information by minute, hour etc (not sure if there is such a table or not) so that we can tune the memory allocation to each resource pool. Do you know any such system tables?

[JimKnicely]

Maybe these tables are more relevant to for you need?

Code: Select all

dbadmin=> select table_name,description from v_monitor.data_collector where component ilike '%poolstatus%';
            table_name             |                       description
-----------------------------------+----------------------------------------------------------
 dc_resource_pool_status           | Resource Pool status information
 dc_resource_pool_status_by_second | Resource Pool status information (historical, by second)
 dc_resource_pool_status_by_minute | Resource Pool status information (historical, by minute)
 dc_resource_pool_status_by_hour   | Resource Pool status information (historical, by hour)
 dc_resource_pool_status_by_day    | Resource Pool status information (historical, by day)
(5 rows)

[Fred]

Interesting Jim. I do not see these tables in my version of the database ( v7.0.1-0). What is your db version? Or is it that we need to explicitly enable some of these tables, as I understand these virtual table details are not mentioned anywhere in the documentation and apparently vertica keeps changing their implementation for whatever reason?