Administration

Under the Hood: System Tables and Views

5

Most users are familiar with the system_tables table which provides a list of v_catalog and v_monitor tables. These tables are helpful in managing your instance. What isn’t very visible is the v_internal schema, it’s tables, and underlying definitions. In this short post, we’ll see the tables available in the v_internal schema and how to query the definition for some of the views.

An information table such as users looks like this:

What you may not know is that this is actually a view, which we’ll see in a moment. But first, let’s find the v_internal tables.

You can browse through the entire list, categorized by schema, at the end of this post. Of the 403 system tables, 118 are views (WHERE is_view = 't'). After you’ve found a view, you can lookup the definition in v_internal.vs_system_views. For example, let’s get the definition of the users view:

This returns:

Not only is it neat that we can see the underlying view for the system table, but we can now build more powerful, customized views based on these definitions. For example, the vs_elastic_cluster table contains information on your cluster’s elasticity, and even provides URL’s to Google’s Chart API for segment layouts:

segment_layout_chart_url

System Tables

v_catalog
table_schema table_name table_description
v_catalog all_tables A complete listing of all tables and views
v_catalog cluster_layout Current cluster layout with relative ordering of nodes
v_catalog columns Table column information
v_catalog comments User comments on catalog objects
v_catalog constraint_columns Table column constraint information
v_catalog databases Database information
v_catalog dual Oracle(TM) compatibility DUAL table
v_catalog elastic_cluster Information about cluster elasticity
v_catalog epochs Mapping from time to epoch (potentially sparse)
v_catalog fault_groups Fault Group information
v_catalog foreign_keys Foreign key information
v_catalog grants Grant information
v_catalog hcatalog_columns HCatalog table column information
v_catalog hcatalog_schemata HCatalog schema information
v_catalog hcatalog_table_list HCatalog table list
v_catalog hcatalog_tables HCatalog table information
v_catalog large_cluster_configuration_status Status of nodes for large cluster management
v_catalog license_audits Database license compliance computation details
v_catalog licenses Licenses installed in the database
v_catalog materialize_flextable_columns_results Most recent results per flex table for each key name considered for materialization
v_catalog nodes Node information
v_catalog odbc_columns An ODBC compliant listing of column metadata
v_catalog passwords User password history and password reuse policy
v_catalog primary_keys Primary key information
v_catalog profile_parameters Profile Parameters information
v_catalog profiles Profile information
v_catalog projection_checkpoint_epochs Projection checkpoint epochs
v_catalog projection_columns Projection columns information
v_catalog projection_delete_concerns Projections that may have delete performance concerns
v_catalog projections Projection information
v_catalog resource_pool_defaults Information about default values for resource pools properties, both internal and dba-created
v_catalog resource_pools Information about defined resource pools, both internal and dba-created
v_catalog roles Role information
v_catalog schemata Schema information
v_catalog sequences Sequence information
v_catalog storage_locations Storage Location Information
v_catalog system_columns System column information
v_catalog system_tables Displays a list of all system tables except internal ones
v_catalog table_constraints Constraint information
v_catalog tables Table information
v_catalog types Information about supported data types
v_catalog user_audits User-requested database object size computation details
v_catalog user_functions User Defined Function information
v_catalog user_procedures User procedure information
v_catalog user_transforms User Defined Transform Function information
v_catalog users User information
v_catalog view_columns View column information
v_catalog views View information
v_internal
table_schema table_name table_description
v_internal catalog_events Catalog Event Information
v_internal dc_allocation_pool_statistics Information about global memory pools, which generally cannot be recovered without restart
v_internal dc_allocation_pool_statistics_by_day Information about global memory pools, which generally cannot be recovered without restart (historical,
by day)
v_internal dc_allocation_pool_statistics_by_hour Information about global memory pools, which generally cannot be recovered without restart (historical,
by hour)
v_internal dc_allocation_pool_statistics_by_minute Information about global memory pools, which generally cannot be recovered without restart (historical,
by minute)
v_internal dc_allocation_pool_statistics_by_second Information about global memory pools, which generally cannot be recovered without restart (historical,
by second)
v_internal dc_analyze_statistics History of statistics collection
v_internal dc_backups Monitoring successful backups
v_internal dc_block_memory_manager_events Events for Block Memory Manager
v_internal dc_block_memory_manager_statistics Statistics and history of block memory manager
v_internal dc_block_memory_manager_statistics_by_day Statistics and history of block memory manager (historical, by day)
v_internal dc_block_memory_manager_statistics_by_hour Statistics and history of block memory manager (historical, by hour)
v_internal dc_block_memory_manager_statistics_by_minute Statistics and history of block memory manager (historical, by minute)
v_internal dc_block_memory_manager_statistics_by_second Statistics and history of block memory manager (historical, by second)
v_internal dc_cancels Canceled queries
v_internal dc_catalog_info Catalog statistics and history
v_internal dc_catalog_info_by_day Catalog statistics and history (historical, by day)
v_internal dc_catalog_info_by_hour Catalog statistics and history (historical, by hour)
v_internal dc_catalog_info_by_minute Catalog statistics and history (historical, by minute)
v_internal dc_catalog_info_by_second Catalog statistics and history (historical, by second)
v_internal dc_catalog_operations History of all catalog read operations
v_internal dc_catalog_persistence_events History of catalog disk interactions
v_internal dc_client_server_messages Client-Server Messages (Front End to Back End Protocol) sent
v_internal dc_configuration_changes Changes to configuration parameters (vertica.conf)
v_internal dc_cpu_aggregate Aggregate CPU information
v_internal dc_cpu_aggregate_by_day Aggregate CPU information (historical, by day)
v_internal dc_cpu_aggregate_by_hour Aggregate CPU information (historical, by hour)
v_internal dc_cpu_aggregate_by_minute Aggregate CPU information (historical, by minute)
v_internal dc_cpu_aggregate_by_second Aggregate CPU information (historical, by second)
v_internal dc_data_channels History of data communication between nodes
v_internal dc_deployment_projections Output projections history per design deployment
v_internal dc_deployment_status History of all design deployment progress/status
v_internal dc_deployments History of all design deployments
v_internal dc_design_projection_columns_encoding Output design projection columns encoding history
v_internal dc_design_projection_statements Output design projection statement history
v_internal dc_design_projections Output design projections history per design
v_internal dc_design_queries Design queries history per design
v_internal dc_design_query_ideal_plan_features Design queries ideal features history
v_internal dc_design_query_plan_costs Design ideal and actual plan costs per query history
v_internal dc_design_query_projections Design queries and actual features history
v_internal dc_design_status History of designs executed and current design status
v_internal dc_design_tables Design tables history per design
v_internal dc_designs History of all designs
v_internal dc_disk_resource_rejections Disk Resource Rejection Records
v_internal dc_dist_calls History of cluster control messages
v_internal dc_elastic_cluster_states History of all EC state changes
v_internal dc_errors History of all errors+warnings encountered
v_internal dc_execution_engine_events History of important events during local planning and execution
v_internal dc_execution_engine_profiles History of EE profiles
v_internal dc_explain_plans Explain plans
v_internal dc_features_used Vertica features used recently
v_internal dc_io_info Information about device IOs
v_internal dc_io_info_by_day Information about device IOs (historical, by day)
v_internal dc_io_info_by_hour Information about device IOs (historical, by hour)
v_internal dc_io_info_by_minute Information about device IOs (historical, by minute)
v_internal dc_io_info_by_second Information about device IOs (historical, by second)
v_internal dc_load_events History of important events during load parsing
v_internal dc_lock_attempts History of lock attempts (resolved requests)
v_internal dc_lock_releases History of lock releases
v_internal dc_lock_requests History of lock requests
v_internal dc_login_failures Failed login attempts
v_internal dc_memory_info Information about node memory allocation, at the OS level
v_internal dc_memory_info_by_day Information about node memory allocation, at the OS level (historical, by day)
v_internal dc_memory_info_by_hour Information about node memory allocation, at the OS level (historical, by hour)
v_internal dc_memory_info_by_minute Information about node memory allocation, at the OS level (historical, by minute)
v_internal dc_memory_info_by_second Information about node memory allocation, at the OS level (historical, by second)
v_internal dc_monitoring_events_cleared Monitoring events cleared
v_internal dc_monitoring_events_posted Monitoring events posted
v_internal dc_move_partitions History of all partition movements
v_internal dc_netstats Network protocol statistics
v_internal dc_netstats_by_day Network protocol statistics (historical, by day)
v_internal dc_netstats_by_hour Network protocol statistics (historical, by hour)
v_internal dc_netstats_by_minute Network protocol statistics (historical, by minute)
v_internal dc_netstats_by_second Network protocol statistics (historical, by second)
v_internal dc_network_info Network interface information and statistics
v_internal dc_network_info_by_day Network interface information and statistics (historical, by day)
v_internal dc_network_info_by_hour Network interface information and statistics (historical, by hour)
v_internal dc_network_info_by_minute Network interface information and statistics (historical, by minute)
v_internal dc_network_info_by_second Network interface information and statistics (historical, by second)
v_internal dc_node_state History of all node state changes
v_internal dc_optimizer_events History of important events during optimizer planning
v_internal dc_optimizer_stats History of optimizer runtime statistics
v_internal dc_plan_activities Execution plan activities
v_internal dc_plan_activity_details Execution plan activity details
v_internal dc_plan_activity_phases Phases of execution for plan activities
v_internal dc_plan_activity_resources Execution plan activity associated resources
v_internal dc_plan_out_of_process_resources Execution plan resource out-of-process details
v_internal dc_plan_parallel_zones Execution plan zone parallelism
v_internal dc_plan_parents Execution plan step parent information
v_internal dc_plan_phases Execution plan phases
v_internal dc_plan_resource_activities Execution plan resource associated activities
v_internal dc_plan_resource_phases Phases of execution for plan resources
v_internal dc_plan_resources Individual execution plan resources
v_internal dc_plan_step_output_schema Execution plan step output schema information
v_internal dc_plan_step_properties Execution plan step properties
v_internal dc_plan_steps Individual execution plans steps
v_internal dc_plans Execution plans
v_internal dc_process_info Information about vertica process memory, handles and system limits
v_internal dc_process_info_by_day Information about vertica process memory, handles and system limits (historical, by day)
v_internal dc_process_info_by_hour Information about vertica process memory, handles and system limits (historical, by hour)
v_internal dc_process_info_by_minute Information about vertica process memory, handles and system limits (historical, by minute)
v_internal dc_process_info_by_second Information about vertica process memory, handles and system limits (historical, by second)
v_internal dc_projection_checkpoint_epochs Projection checkpoint epoch history
v_internal dc_projection_recoveries Monitoring completed projection recoveries
v_internal dc_projection_refreshes_completed History of refreshed projections
v_internal dc_projections_used Projections used in each SQL request issued
v_internal dc_query_executions Various query execution steps
v_internal dc_rebalance_projection_sizes Calculated sizes of partitions at start of rebalance operation
v_internal dc_rebalance_projection_starts History of all projections that started to be rebalanced (EC)
v_internal dc_rebalanced_projections History of all projections rebalanced (EC)
v_internal dc_rebalanced_segments History of all segments rebalanced (EC)
v_internal dc_requests_completed History of all SQL requests completed
v_internal dc_requests_issued History of all SQL requests issued
v_internal dc_requests_retried History of all SQL requests issued that were retried
v_internal dc_resource_acquisitions History of all resource acquisitions
v_internal dc_resource_pool_history History of all resource modifications
v_internal dc_resource_rejections Resource Rejection Records
v_internal dc_resource_releases History of all resource acquisition releases
v_internal dc_roses_committed History of all ROS committed
v_internal dc_roses_created History of all ROS and DVROS created
v_internal dc_roses_destroyed History of all ROS destroyed
v_internal dc_runtime_priority_changes History of runtime priority changes
v_internal dc_session_ends Sessions ended
v_internal dc_session_starts Sessions started
v_internal dc_signals History of process signals received
v_internal dc_spread_monitor History of spread statistics
v_internal dc_startups History of all node startup events
v_internal dc_storage_info Storage information (Used and Free space)
v_internal dc_storage_info_by_day Storage information (Used and Free space) (historical, by day)
v_internal dc_storage_info_by_hour Storage information (Used and Free space) (historical, by hour)
v_internal dc_storage_info_by_minute Storage information (Used and Free space) (historical, by minute)
v_internal dc_storage_info_by_second Storage information (Used and Free space) (historical, by second)
v_internal dc_storage_layer_statistics Statistics and history of storage and caching layer
v_internal dc_storage_layer_statistics_by_day Statistics and history of storage and caching layer (historical, by day)
v_internal dc_storage_layer_statistics_by_hour Statistics and history of storage and caching layer (historical, by hour)
v_internal dc_storage_layer_statistics_by_minute Statistics and history of storage and caching layer (historical, by minute)
v_internal dc_storage_layer_statistics_by_second Statistics and history of storage and caching layer (historical, by second)
v_internal dc_test For data collector infrastructure testing
v_internal dc_transaction_ends History of end transactions (commit or rollback)
v_internal dc_transaction_starts History of begin transactions
v_internal dc_tuning_analysis Tuning analysis history in Workload Analyzer
v_internal dc_tuning_recommendations Tuning Recommendations in Workload Analyzer
v_internal dc_tuple_mover_events History of Tuple Mover activities
v_internal dc_upgrades Monitoring catalog upgrades
v_internal dc_user_audits History of user audits
v_internal dc_woses_committed History of all WOS committed
v_internal dc_woses_destroyed History of all WOS destroyed
v_internal execution_step_timings Lists time taken by each significant phase of query execution, including as a percentage of the total.
v_internal odbc_procedure_columns An ODBC compliant listing of procedure column metadata
v_internal recv_protocol List of Messages and PG characters received by the server
v_internal send_protocol List of Messages and PG characters sent by the server
v_internal session_sequences Sequences cached in the session
v_internal system System level information
v_internal tuning_recommendation_details Tuning recommendations from workload analyzer
v_internal vs_catalog_event_add_derived_column Add Derived Column events
v_internal vs_catalog_event_alter_partition Alter partition events
v_internal vs_catalog_event_drop_partition Drop partition events
v_internal vs_catalog_event_rebalance Rebalance table events
v_internal vs_catalog_event_restore Restore table events
v_internal vs_catalog_event_rollback Cluster epoch rollback (ASR) events
v_internal vs_catalog_event_truncate Truncate table events
v_internal vs_cluster_layout Information about the preferred cluster layout and how fault groups are distributed
v_internal vs_column_storage Information on the amount of disk storage used by each column of each projection on each node.
v_internal vs_columns Table column information
v_internal vs_columns_helper Attribute information Helper
v_internal vs_comments User and internal comments on catalog objects
v_internal vs_configuration_parameters System configuration parameters
v_internal vs_constraint_columns Column constraint information
v_internal vs_constraints Constraint information
v_internal vs_data_channels Information about current data channels
v_internal vs_data_channels_closed Information about data channels which are closed but not yet cleared
v_internal vs_data_collector Statistics on all Usage Data Collector logs
v_internal vs_databases Database information
v_internal vs_deployments Current (or last) design deployment status
v_internal vs_designs Event history of currently running DBDesigner design
v_internal vs_dfs_distribution_helper DFS file distribution information helper
v_internal vs_dfs_file DFS file element information
v_internal vs_dfs_file_block DFS physical file blocks information
v_internal vs_dfs_file_distributions DFS file distribution information
v_internal vs_dv_ros DV ROS information
v_internal vs_dv_wos_containers DV WOS information
v_internal vs_elastic_cluster Information about cluster elasticity
v_internal vs_epoch_map Epoch map information
v_internal vs_epochs Epoch Map Entries
v_internal vs_execution_engine_profiles Per EE operator profiling information for the running queries
v_internal vs_fault_groups Fault Group information
v_internal vs_global_settings Global Setting information
v_internal vs_grants Grant information
v_internal vs_hcatalog_configurations HCatalog configuration information
v_internal vs_hcatalog_table_details HCatalog table details
v_internal vs_hcatalog_table_list HCatalog table list
v_internal vs_index_tool_progress Index tool progress
v_internal vs_license_audits History of license audit results and details.
v_internal vs_licenses License information
v_internal vs_local_nodes Local node information
v_internal vs_network_interfaces Network Interfaces on a Node
v_internal vs_node_dependencies Node dependency for fault tolerance
v_internal vs_node_states Cluster node state information
v_internal vs_nodes Node information
v_internal vs_partitions Partition metadata
v_internal vs_passwords User password information
v_internal vs_passwords_helper Password history Helper
v_internal vs_plan_activities Execution plan activities
v_internal vs_plan_activity_details Execution plan activity details
v_internal vs_plan_activity_phases Phases of execution for plan activities
v_internal vs_plan_activity_resources Execution plan activity associated resources
v_internal vs_plan_out_of_process_resources Execution plan resource out-of-process details
v_internal vs_plan_parallel_zones Execution plan parallel zones
v_internal vs_plan_parents Execution plan step parent information
v_internal vs_plan_phases Execution plan phases
v_internal vs_plan_resource_activities Execution plan resource associated activities
v_internal vs_plan_resource_phases Phases of execution for plan resources
v_internal vs_plan_resources Individual execution plan resources
v_internal vs_plan_step_output_schema Execution plan step output schema information
v_internal vs_plan_step_properties Execution plan step properties
v_internal vs_plan_steps Individual execution plans steps
v_internal vs_plans Execution plans
v_internal vs_procedure_columns List all of the SQL function parameters
v_internal vs_procedures List all of the SQL functions
v_internal vs_profiles Profile information
v_internal vs_projection_attributes Projection attribute information
v_internal vs_projection_column_histogram Column Histogram information
v_internal vs_projection_column_order Projection column order information
v_internal vs_projection_columns Projection column information
v_internal vs_projection_queries Projection query information
v_internal vs_projection_recoveries Projection recovery information
v_internal vs_projection_recoveries_by_container Recovery by container progress per projection
v_internal vs_projection_recoveries_incremental Incremental recovery progress per projection
v_internal vs_projection_recoveries_replay_delete Incremental recovery replay delete progress per projection
v_internal vs_projection_refreshes Information on ongoing projection refresh operations
v_internal vs_projection_segment_information Projection segmentation information
v_internal vs_projections Projection information
v_internal vs_rebalance_projection_status Rebalance progress for relevant projections.
v_internal vs_rebalance_projections Information about which method will be used to rebalance each projection
v_internal vs_rebalance_segments Information about how segments are to be rebalanced
v_internal vs_rebalance_separated_storage_containers Helper view with information about storage containers that rebalance separated.
v_internal vs_rebalance_storage_containers Information about storage containers to be rebalanced.
v_internal vs_rebalance_transferred_storage_containers Helper view with information about storage containers that were transferred.
v_internal vs_resource_acquisitions Resources in use by queries
v_internal vs_resource_pool_defaults List of default resource pool properties
v_internal vs_resource_pools Resource pool catalog information
v_internal vs_roles Role information
v_internal vs_ros ROS information
v_internal vs_ros_containers ROS information
v_internal vs_ros_min_max_values Min/Max values and null counts stored in a ros
v_internal vs_ros_segment_bounds Segment bounds spanned by a ros
v_internal vs_schemata Schema information
v_internal vs_segments Segmentation information
v_internal vs_segments_helper Segment Information Helper
v_internal vs_sequences Sequence information
v_internal vs_sessions Information on each Session (internal or external session)
v_internal vs_storage_columns Storage column information
v_internal vs_storage_locations Storage location information
v_internal vs_storage_policies Storage tier assignment
v_internal vs_strata Information of Tuple mover stata structures per ROS container
v_internal vs_subnets Subnets of the Database
v_internal vs_system_tables Displays a list of all system tables
v_internal vs_system_views Displays a list of all virtual view definitions
v_internal vs_tables Table information
v_internal vs_tables_view Table information
v_internal vs_tuning_rule_parameters Configurable parameters for tuning rules
v_internal vs_tuning_rule_parameters_helper Tuning rule parameter helper
v_internal vs_tuning_rules Tuning rules for Workload Analyzer
v_internal vs_tuple_mover_operations Current tuple mover operations
v_internal vs_unique_key_statistics Soft unique key statistics
v_internal vs_unwrap_dc_rebalanced_segments Helper to separate rebalanced segments that overlap the segment range (i.e. segment_lower_bound > seg
ment_upper_bound becomes two rows {0, segment_upper_bound} and {segment_lower_bound, 2^32 – 1})
v_internal vs_unwrap_dc_roses_created Helper to separate roses created that overlap the segment range (i.e. segment_lower_bound > segment_u
pper_bound becomes two rows {0, segment_upper_bound} and {segment_lower_bound, 2^32 – 1})
v_internal vs_unwrap_rebalance_segments Helper to separate rebalance segments that overlap the segment range (i.e. segment_lower_bound > segm
ent_upper_bound becomes two rows {0, segment_upper_bound} and {segment_lower_bound, 2^32 – 1})
v_internal vs_unwrap_storage_containers Helper to translate NULL segment bounds and separate storage containers that overlap the segment range (
i.e. segment_lower_bound > segment_upper_bound becomes two rows {0, segment_upper_bound} and {segment_lower_bound, 2^32 –
1})
v_internal vs_users User information
v_internal vs_view_columns View column information
v_internal vs_views View information
v_internal vs_wos_containers WOS information
v_monitor
table_schema table_name table_description
v_monitor active_events Displays all of the active events in the cluster
v_monitor column_storage Information on the amount of disk storage used by each column of each projection on each node.
v_monitor configuration_changes Configuration parameter (vertica.conf) change history
v_monitor configuration_parameters Configuration Parameters information
v_monitor cpu_usage CPU usage history
v_monitor critical_hosts Hosts whose failure will result in cluster shutdown
v_monitor critical_nodes Nodes whose failure will result in cluster shutdown
v_monitor current_session Information on current Session
v_monitor data_collector Statistics on all Usage Data Collector logs
v_monitor database_backups Database backup history
v_monitor database_connections List of connections to other Vertica databases
v_monitor database_snapshots Information on stored database snapshots
v_monitor delete_vectors Information on delete vectors
v_monitor deploy_status Monitor the current (or last) DBD deployment progress
v_monitor deployment_projection_statements Deployment projection statements from current DBD designs
v_monitor deployment_projections Projections to be deployed from current DBD designs
v_monitor design_queries Current DBD design queries
v_monitor design_status Monitor the current (or last) DBD design progress
v_monitor design_tables Current DBD design tables
v_monitor designs Current DBD designs
v_monitor dfs_storage Metadata on files stored in the Vertica Distributed File System
v_monitor disk_resource_rejections Disk Resource Rejection Summarizations
v_monitor disk_storage Disk usage information
v_monitor error_messages Error and warning messages encountered while processing queries
v_monitor event_configurations Current Event configuration
v_monitor execution_engine_profiles Per EE operator profiling information
v_monitor host_resources Per host profiling information
v_monitor io_usage Disk I/O bandwidth usage history
v_monitor load_streams Load metrics for each load stream on each node.
v_monitor lock_usage Lock usage information wait time/count and hold time/count
v_monitor locks Lock grants and requests for all nodes
v_monitor login_failures User login failures
v_monitor memory_usage Memory usage history
v_monitor monitoring_events All system event history
v_monitor network_interfaces Information about network interfaces on all Vertica nodes
v_monitor network_usage Network bandwidth usage history
v_monitor node_resources Per node profiling information
v_monitor node_states Node recovery state change history
v_monitor output_deployment_status Projection deployment status of current DBD designs
v_monitor output_event_history Monitor progress of current DBD designs
v_monitor partition_reorganize_errors Background Partition Reorganize Errors
v_monitor partition_status Partition Status
v_monitor partitions Partition Metadata
v_monitor process_signals History of signals handled
v_monitor projection_recoveries Recovery status per projection
v_monitor projection_refreshes Historical and current projection refreshes
v_monitor projection_storage Storage information on each Projection
v_monitor projection_usage Projection usage history
v_monitor query_events Query optimization, planning and execution events
v_monitor query_metrics Summarized query information
v_monitor query_plan_profiles Numbers for plan queries
v_monitor query_profiles Query profiles
v_monitor query_requests User-issued query requests
v_monitor rebalance_projection_status Rebalance progress for relevant projections.
v_monitor rebalance_table_status Rebalance progress for relevant tables.
v_monitor recovery_status Overall recovery status
v_monitor resource_acquisitions Resource acquisition history for user queries
v_monitor resource_pool_status Resource pool usage Information
v_monitor resource_queues Queries waiting to acquire resources
v_monitor resource_rejection_details Resource rejection history
v_monitor resource_rejections Resource Rejection Summarizations
v_monitor resource_usage Resource usage Information
v_monitor session_profiles Per session profiling information
v_monitor sessions Information on each Session
v_monitor storage_containers Information on each storage container
v_monitor storage_policies Storage tier policies
v_monitor storage_tiers Tiered Storage Information
v_monitor storage_usage File system storage usage
v_monitor strata Information of strata used in Tuple Mover, one row per stratum
v_monitor strata_structures Information of strata structures used in Tuple Mover, one row per strata structure
v_monitor system System level information
v_monitor system_resource_usage System resource (memory, cpu, network, disk I/O) usage history
v_monitor system_services Background system services
v_monitor system_sessions System internal session history per system task
v_monitor transactions Transaction history
v_monitor tuning_recommendations Aggregated tuning recommendations from workload analyzer
v_monitor tuple_mover_operations Current and historical tuple mover activities
v_monitor udx_fenced_processes Information about processes Vertica uses to run UDx in fenced mode
v_monitor user_libraries List of user defined shared libraries
v_monitor user_library_manifest List of exported (by library writers) objects from user defined shared libraries
v_monitor user_sessions User session history
v_monitor wos_container_storage Storage information on WOS allocator

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.

5 Comments

  1. Tariq October 20, 2015 at 4:34 PM -  Reply

    Hi Norbert,

    Thank you for such an informative post. This is really useful, specially for someone like me who’s completely new to Vertica. I was wondering if it is possible to gather resource utilisation for a specific query through system tables, or otherwise. I am in the process of testing a custom UDS which I have written and am using it with COPY command. I am particularly interested in capturing IO, CPU, MEMORY, DISK, N\W bandwidth consumed during each COPY command. Right now I am querying several system tables in order to gather this information. I would really appreciate if you could provide me some pointers to do this in a more intuitive fashion. Many thanks!

    • Norbert Krupa October 20, 2015 at 9:24 PM -  Reply

      Have you attempted to PROFILE the query?

      • Tariq October 21, 2015 at 2:05 AM -  Reply

        Hi Norbert, thank you so much for the prompt response. Yes, I did profile the query. However, I could not get what exactly I was looking for. Profiling my COPY command provided me the following info :
        node_name
        user_id
        user_name
        session_id
        transaction_id
        statement_id
        plan_id
        operator_name
        operator_id
        baseplan_id
        path_id
        localplan_id
        activity_id
        resource_id
        counter_name
        counter_tag
        counter_value
        is_executing

        I am actually interested in system resource utilisation info for a particular query.

        • Norbert Krupa October 21, 2015 at 9:16 AM -  Reply

          You’re on the right path. You just need to look at the counter_names for the profile. You can find a list in the documentation. For CPU utilization, you can use the execution time (us) counter; for memory you can use memory allocated (bytes) counter.

          You should know that it’ll be very difficult to get the system utilization metrics you’re looking for (CPU, IO) because of the architecture.

          If you just want a high level estimate, you can always use EXPLAIN VERBOSE statement; for estimated Disk, CPU, Memory, and Network costs.

  2. Raj January 10, 2017 at 3:25 AM -  Reply

    Hey Norbert,This really Helpful.Do you have the details documentation of the system tables

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: