Administration

Data Collector Tables

0

There exist tables in Vertica which give very granular historical resource information. While the collection tables themslves reside in the v_internal schema, the list of tables from data_collector can be found under the v_monitor schema:

You will get back a row for each node if you do not use DISTINCT. The documentation also details the columns that are available, and their retention policies.

The retention policy for each data collection can be queried using the GET_DATA_COLLECTOR_POLICY() function. This function will return the space to maintain in memory and disk for the given component (defaults below). The time retention in the table below is an estimate based on the date difference of the minimum and maximum time stamps available for that data collection in my cluster (results will vary).

Here’s what is in the treasure chest (Vertica version 6.1.2-0):

Table Name Description ≈ Time Retention Memory Retention Disk Retention
dc_allocation_pool_statistics Information about global memory pools, which generally cannot be recovered without restart 15 minutes 64KB 256KB
dc_allocation_pool_statistics_by_day Information about global memory pools, which generally cannot be recovered without restart (historical, by day) 6 months 64KB 256KB
dc_allocation_pool_statistics_by_hour Information about global memory pools, which generally cannot be recovered without restart (historical, by hour) 8 days 64KB 256KB
dc_allocation_pool_statistics_by_minute Information about global memory pools, which generally cannot be recovered without restart (historical, by minute) 3 hours 64KB 256KB
dc_allocation_pool_statistics_by_second Information about global memory pools, which generally cannot be recovered without restart (historical, by second) 3 minutes 64KB 256KB
dc_analyze_statistics History of statistics collection 5 days 1000KB 10000KB
dc_backups Monitoring successful backups   10KB 10KB
dc_block_memory_manager_events Events for Block Memory Manager 5 months 1000KB 10000KB
dc_block_memory_manager_statistics Statistics and history of block memory manager 20 minutes 64KB 256KB
dc_block_memory_manager_statistics_by_day Statistics and history of block memory manager (historical, by day) 6 months 64KB 256KB
dc_block_memory_manager_statistics_by_hour Statistics and history of block memory manager (historical, by hour) 8 days 64KB 256KB
dc_block_memory_manager_statistics_by_minute Statistics and history of block memory manager (historical, by minute) 4 hours 64KB 256KB
dc_block_memory_manager_statistics_by_second Statistics and history of block memory manager (historical, by second) 5 minutes 64KB 256KB
dc_cancels Canceled queries 5 days 10KB 100KB
dc_catalog_info Catalog statistics and history 15 minutes 64KB 256KB
dc_catalog_info_by_day Catalog statistics and history (historical, by day) 5 minutes 64KB 256KB
dc_catalog_info_by_hour Catalog statistics and history (historical, by hour) 7 days 64KB 256KB
dc_catalog_info_by_minute Catalog statistics and history (historical, by minute) 3 hours 64KB 256KB
dc_catalog_info_by_second Catalog statistics and history (historical, by second) 6 minutes 64KB 256KB
dc_catalog_operations History of all catalog read operations   Not Kept Not Kept
dc_catalog_persistence_events History of catalog disk interactions 7 days 1000KB 10000KB
dc_client_server_messages Client-Server Messages (Front End to Back End Protocol) sent   Not Kept Not Kept
dc_configuration_changes Changes to configuration parameters (vertica.conf) 3 days 200KB 2000KB
dc_cpu_aggregate Aggregate CPU information 12 hours 64KB 25600KB
dc_cpu_aggregate_by_day Aggregate CPU information (historical, by day) 7 days 64KB 25600KB
dc_cpu_aggregate_by_hour Aggregate CPU information (historical, by hour) 7 days 64KB 25600KB
dc_cpu_aggregate_by_minute Aggregate CPU information (historical, by minute) 6 days 64KB 25600KB
dc_cpu_aggregate_by_second Aggregate CPU information (historical, by second) 5 hours 64KB 25600KB
dc_cpu_info CPU information 30 minutes 64KB 25600KB
dc_cpu_info_by_day CPU information (historical, by day) 6 months 64KB 25600KB
dc_cpu_info_by_hour CPU information (historical, by hour) 20 hours 64KB 25600KB
dc_cpu_info_by_minute CPU information (historical, by minute) 8 hours 64KB 25600KB
dc_cpu_info_by_second CPU information (historical, by second) 15 minutes 64KB 25600KB
dc_data_channels History of data communication between nodes   Not Kept Not Kept
dc_deployment_projections Output projections history per design deployment 36 hours 1000KB 10000KB
dc_deployment_status History of all design deployment progress/status 4 months 1000KB 10000KB
dc_deployments History of all design deployments 1 month 100KB 10000KB
dc_design_projection_columns_encoding Output design projection columns encoding history 4 months 100KB 10000KB
dc_design_projection_statements Output design projection statement history 4 months 1000KB 10000KB
dc_design_projections Output design projections history per design 4 months 100KB 10000KB
dc_design_queries Design queries history per design 4 months 1000KB 10000KB
dc_design_query_ideal_plan_features Design queries ideal features history 4 months 1000KB 10000KB
dc_design_query_plan_costs Design ideal and actual plan costs per query history 4 months 100KB 10000KB
dc_design_query_projections Design queries and actual features history 4 months 1000KB 10000KB
dc_design_status History of designs executed and current design status   1000KB 10000KB
dc_design_tables Design tables history per design 4 months 100KB 10000KB
dc_designs History of all designs 4 months 100KB 10000KB
dc_disk_resource_rejections Disk Resource Rejection Records   100KB 10000KB
dc_dist_calls History of cluster control messages   Not Kept Not Kept
dc_elastic_cluster_states History of all EC state changes 5 minutes Emitted to vertica.log; 1000KB 10000KB
dc_errors History of all errors+warnings encountered 1 month 1000KB 10000KB
dc_execution_engine_events History of important events during local planning and execution 18 hours 100KB 20000KB
dc_execution_engine_profiles History of EE profiles 5 minutes 2000KB 50000KB
dc_explain_plans Explain plans 5 hours 100KB 10000KB
dc_io_info Information about device IOs 1 hour 64KB 25600KB
dc_io_info_by_day Information about device IOs (historical, by day) 6 months 64KB 25600KB
dc_io_info_by_hour Information about device IOs (historical, by hour) 1 month 64KB 25600KB
dc_io_info_by_minute Information about device IOs (historical, by minute) 8 hours 64KB 25600KB
dc_io_info_by_second Information about device IOs (historical, by second) 15 minutes 64KB 25600KB
dc_load_events History of important events during load parsing 2 hours 1000KB 10000KB
dc_lock_attempts History of lock attempts (resolved requests) 1 hour 100KB 10000KB
dc_lock_releases History of lock releases 1 hour 100KB 10000KB
dc_lock_requests History of lock requests   Not Kept Not Kept
dc_login_failures Failed login attempts 6 months 200KB 5000KB
dc_memory_info Information about node memory allocation, at the OS level 1 day 64KB 25600KB
dc_memory_info_by_day Information about node memory allocation, at the OS level (historical, by day) 6 months 64KB 25600KB
dc_memory_info_by_hour Information about node memory allocation, at the OS level (historical, by hour) 6 months 64KB 25600KB
dc_memory_info_by_minute Information about node memory allocation, at the OS level (historical, by minute) 2 weeks 64KB 25600KB
dc_memory_info_by_second Information about node memory allocation, at the OS level (historical, by second) 5 days 64KB 25600KB
dc_monitoring_events_cleared Monitoring events cleared 6 months 100KB 1000KB
dc_monitoring_events_posted Monitoring events posted 6 months 1000KB 10000KB
dc_move_partitions History of all partition movements   100KB 1000KB
dc_netstats Network protocol statistics 2 hours 64KB 25600KB
dc_netstats_by_day Network protocol statistics (historical, by day) 6 months 64KB 25600KB
dc_netstats_by_hour Network protocol statistics (historical, by hour) 2 months 64KB 25600KB
dc_netstats_by_minute Network protocol statistics (historical, by minute) 18 hours 64KB 25600KB
dc_netstats_by_second Network protocol statistics (historical, by second) 45 minutes 64KB 25600KB
dc_network_info Network interface information and statistics 9 hours 64KB 25600KB
dc_network_info_by_day Network interface information and statistics (historical, by day) 6 months 64KB 25600KB
dc_network_info_by_hour Network interface information and statistics (historical, by hour) 6 months 64KB 25600KB
dc_network_info_by_minute Network interface information and statistics (historical, by minute 3 days 64KB 25600KB
dc_network_info_by_second Network interface information and statistics (historical, by second 3 hours 64KB 25600KB
dc_node_state History of all node state changes 4 days 10KB 100KB
dc_optimizer_events History of important events during optimizer planning 4 days 1000KB 10000KB
dc_optimizer_stats History of optimizer runtime statistics 4 days 1000KB 10000KB
dc_plan_activities Execution plan activities 10 minutes 400KB 2000KB
dc_plan_activity_details Execution plan activity details 5 minutes 200KB 1000KB
dc_plan_activity_phases Phases of execution for plan activities 15 minutes 100KB 1000KB
dc_plan_activity_resources Execution plan activity associated resources 5 minutes 100KB 1000KB
dc_plan_out_of_process_resources Execution plan resource out-of-process details   100KB 1000KB
dc_plan_parallel_zones Execution plan zone parallelism 45 minutes 100KB 1000KB
dc_plan_parents Execution plan step parent information 10 minutes 1000KB 10000KB
dc_plan_phases Execution plan phases 1 day 100KB 1000KB
dc_plan_resource_activities Execution plan resource associated activities 5 minutes 100KB 1000KB
dc_plan_resource_phases Phases of execution for plan resources 5 minutes 100KB 1000KB
dc_plan_resources Individual execution plan resources 5 minutes 400KB 2000KB
dc_plan_step_output_schema Execution plan step output schema information   Not Kept Not Kept
dc_plan_step_properties Execution plan step properties 5 minutes 1000KB 10000KB
dc_plan_steps Individual execution plans steps 30 minutes 1000KB 10000KB
dc_plans Execution plans 1 month 100KB 1000KB
dc_process_info Information about vertica process memory, handles and system limits 15 days 64KB 25600KB
dc_process_info_by_day Information about vertica process memory, handles and system limits (historical, by day) 6 months 64KB 25600KB
dc_process_info_by_hour Information about vertica process memory, handles and system limits (historical, by hour) 6 months 64KB 25600KB
dc_process_info_by_minute Information about vertica process memory, handles and system limits (historical, by minute) 7 days 64KB 25600KB
dc_process_info_by_second Information about vertica process memory, handles and system limits (historical, by second) 3 hours 64KB 25600KB
dc_projection_checkpoint_epochs Projection checkpoint epoch history 3 days 100KB 10000KB
dc_projection_recoveries Monitoring completed projection recoveries 5 months 200KB 10000KB
dc_projection_refreshes_completed History of refreshed projections 1 month 100KB 10000KB
dc_projections_used Projections used in each SQL request issued 10 days 2000KB 150000KB
dc_query_executions Various query execution steps 45 minutes 1000KB 20000KB
dc_rebalance_projection_starts History of all projections that started to be rebalanced (EC)   1000KB 20000KB
dc_rebalanced_projections History of all projections rebalanced (EC)   100KB 10000KB
dc_rebalanced_segments History of all segments rebalanced (EC)   100KB 10000KB
dc_requests_completed History of all SQL requests completed 5 days 2000KB 50000KB
dc_requests_issued History of all SQL requests issued 4 days 2000KB 50000KB
dc_requests_retried History of all SQL requests issued that were retried 4 days 2000KB 50000KB
dc_resource_acquisitions History of all resource acquisitions 1 hour 1000KB 10000KB
dc_resource_pool_history History of all resource modifications 5 months 1000KB 10000KB
dc_resource_rejections Resource Rejection Records 5 months 1000KB 10000KB
dc_resource_releases History of all resource acquisition releases 2 hours 1000KB 10000KB
dc_roses_committed History of all ROS committed 3 days 1000KB 10000KB
dc_roses_created History of all ROS and DVROS created 3 days 1000KB 10000KB
dc_roses_destroyed History of all ROS destroyed 3 days 1000KB 10000KB
dc_runtime_priority_changes History of runtime priority changes 3 hours 1000KB 10000KB
dc_session_ends Sessions ended 4 days 100KB 5000KB
dc_session_starts Sessions started 3 days 200KB 5000KB
dc_signals History of process signals received 6 months 10KB 1000KB
dc_spread_monitor History of spread statistics 2 days 1000KB 10000KB
dc_startups History of all node startup events 5 months 200KB 1000KB
dc_storage_info Storage information (Used and Free space) 5 minutes 64KB 256KB
dc_storage_info_by_day Storage information (Used and Free space) (historical, by day) 2 months 64KB 256KB
dc_storage_info_by_hour Storage information (Used and Free space) (historical, by hour) 2 days 64KB 256KB
dc_storage_info_by_minute Storage information (Used and Free space) (historical, by minute) 1 hour 64KB 256KB
dc_storage_info_by_second Storage information (Used and Free space) (historical, by second) 1 minute 64KB 256KB
dc_storage_layer_statistics Statistics and history of storage and caching layer 10 minutes 64KB 256KB
dc_storage_layer_statistics_by_day Statistics and history of storage and caching layer (historical, by day) 4 months 64KB 256KB
dc_storage_layer_statistics_by_hour Statistics and history of storage and caching layer (historical, by hour) 3 days 64KB 256KB
dc_storage_layer_statistics_by_minute Statistics and history of storage and caching layer (historical, by minute) 1 hour 64KB 256KB
dc_storage_layer_statistics_by_second Statistics and history of storage and caching layer (historical, by second) 3 minutes 64KB 256KB
dc_test For data collector infrastructure testing   Not Kept Not Kept
dc_transaction_ends History of end transactions (commit or rollback) 1 hour 1000KB 10000KB
dc_transaction_starts History of begin transactions 2 days 1000KB 10000KB
dc_tuning_analysis Tuning analysis history in Workload Analyzer   100KB 10000KB
dc_tuning_recommendations Tuning Recommendations in Workload Analyzer   1000KB 10000KB
dc_tuple_mover_events History of Tuple Mover activities 3 days 1000KB 10000KB
dc_upgrades Monitoring catalog upgrades   10KB 10KB
dc_user_audits History of user audits 4 months 100KB 10000KB
dc_woses_committed History of all WOS committed 2 hours 1000KB 10000KB
dc_woses_destroyed History of all WOS destroyed 3 days 1000KB 10000KB

About the author / 

Norbert Krupa

Norbert is the founder of vertica.tips and a Solutions Engineer at Talend. He is an HP Accredited Solutions Expert for Vertica Big Data Solutions. He has written the Vertica Diagnostic Queries which aim to cover monitoring, diagnostics and performance tuning. The views, opinions, and thoughts expressed here do not represent those of the user's employer.

Leave a Reply

Upcoming Events

  • No upcoming events
AEC v1.0.4

Subscribe to Blog via Email

Enter your email address to subscribe and receive notifications of new posts by email.

Read more use cases here.

Notice

This site is not affiliated, endorsed or associated with HPE Vertica. This site makes no claims on ownership of trademark rights. The author contributions on this site are licensed under CC BY-SA 3.0 with attribution required.
%d bloggers like this: