myVertica  

Tuning Performance

This topic walks you through the different options for analyzing performance and show you how to identify where problem areas lie.

Getting Recommendations

If your queries are performing sub-optimally, you can get tuning recommendations for them in the following ways:

  • Running the Workload Analyzer (WLA) using the ANALYZE_WORKLOAD() function
  • Running the WLA through the Management Console interface
  • Querying the V_MONITOR.TUNING_RECOMMENDATIONS system table

Workload Analyzer

The Workload Analyzer(WLA) is a Vertica utility that provides tuning recommendations as well as hints about optimizing database objects. The utility works by analyzing system tables to monitor the performance of your queries, workload history, resources, and configurations. Through this analysis, the WLA can identify the root causes of poor query performance.

Knowing What to Tune

When you run ANALYZE_WORKLOAD, the WLA provides you with a list of recommendations for tuning. The output includes a description of what you need to do and, if possible, the code you need to carry out the task.

Common Triggering Conditions

Sometimes, the WLA will make recommendations that are difficult to understand. Here is a list of some of the most common triggering conditions, along with the recommendations to resolve the issue:

Triggering Condition What to Do
Internal configuration parameter is not the same across nodes.

Reset configuration parameter.

ALTER DATABASE mydb SET parameter = value;

An unused projection meets the following conditions:
  • No queries on projection for more than 30 days but, projection’s anchor table has been queried more than 10 times
  • Projection’s anchor table is not a temp or system table
  • Projection’s table is not small, where the number of bytes of disk storage in use by the projection (used_bytes) is more than 10M

Drop the projection (projection-name).

DROP PROJECTION public.T1_fact_super_P1_B1;

User with dbadmin/pseudosuperuser role has empty password.

Set the password for user.

ALTER USER (user) IDENTIFIED BY (‘new_password’);

Table with too many partition count.

Alter the table’s partition expression.

ALTER TABLE (schema.table) PARTITION BY (new_partition_expression) REORGANIZE;

LGE threshold setting is lower than the default setting. Workload Analyzer does not trigger a tuning recommendation for this scenario unless you altered settings and/or services under the guidance of technical support.
Tuple Mover’s MoveOutInterval parameter is set greater than the default setting.

Decrease the MoveOutInterval configuration parameter setting:

ALTER DATABASE mydb SET MoveOutInterval = default_value;

Tuple Mover has been disabled. Workload Analyzer does not trigger a tuning recommendation for this scenario unless you altered settings and/or services under the guidance of technical support.
Too Many ROS containers since the last mergeout operation; configuration parameters are set lower than the default. Workload Analyzer does not trigger a tuning recommendation for this scenario unless you altered settings and/or services under the guidance of technical support.
Too Many ROS containers since the last mergeout operation; the TM Mergeout service is disabled. Workload Analyzer does not trigger a tuning recommendation for this scenario unless you altered settings and/or services under the guidance of technical support.
Average CPU usage exceeds 95% for 20 minutes. Check system processes or change the settings for PLANNEDCONCURRENCY and/or MAXCONCURRENCY for the RESOURCE POOL.
Partitioned table data is not fully reorganized after repartitioning.

Reorganize data in partitioned table public.T1.

ALTER TABLE public.T1 REORGANIZE;

Table has multiple partition keys within the same ROS container.

Reorganize data in partitioned table public.T1.

ALTER TABLE public.T1 REORGANIZE;

Excessive swap activity; average memory usage exceeds 99% for 10 minutes. Check system processes.
A table does not have any Database Designer-designed projections. Run database designer on table public.T1.
Statistics are stale (no histogram or predicate falls outside histogram).

Run ANALYZE_STATISTICS():

SELECT analyze_statistics (‘public.t.a’);

Data distribution in segmented projection is skewed. Re-segment projection public.t_super on high-cardinality column(s).
GROUP BY spill event. Consider running an incremental design on query.

Learn More

For more information about tuning your database for optimal performance, see Analyzing Workloads in our core documentation.