Investigate Sudden Performance Degradation of a Query

Posted June 14, 2018 by Soniya Shah, Information Developer

Business Team Meeting Discussion Working Concept
Have you ever had a formerly fast-running query begin to run slowly? Use the following checklist to investigate the sudden slowdown of a formerly fast-running query.
Step Task Results
1 Check for host error messages using the following commands: $ cat /var/log/messages $ dmesg If the cluster is in good health, go to Step 2. If the cluster is not healthy and if you see error messages, inform your system administrator. After you have resolved the issue, continue with this checklist.
2 Check the cluster setup on all nodes using the following command: $ /opt/vertica/oss/python/bin/python -m vertica.local_verify If the cluster is not set up correctly, Vertica displays a list of errors with links to the Vertica documentation. Review the links and resolve the errors. If you receive no errors, go to Step 3.
3 Stop the database before checking cluster health: $ /opt/vertica/bin/admintools -t stop_db -d db-name If the database stops successfully, go to Step 4. Otherwise, run the validation scripts, and go to Step 4.
4 Check the network performance of your cluster: $ /opt/vertica/bin/vnetperf Check the input and output performance: $ /opt/vertica/bin/vioperf Check the CPU performance: $ /opt/vertica/bin/vcpuperf Examine the results for the network, input and output, and CPU performance, and go to Step 5.
5 Run an EXPLAIN statement, or EXPLAIN LOCAL VERBOSE, and review output to identify possible abnormalities:=> EXPLAIN <query> => EXPLAIN LOCAL VERBOSE <query> If you see a missing statistics message, go to Step 6. If you do not see an expected join order, go to Step 7. You can verify a change in your query plan by comparing the current plan to an older plan. If you see a regression in the new plan, contact Vertica Technical Support. If you do not have access to an older plan, or if you see no abnormality, go to Step 8.
6 Analyze the missing statistics message in the EXPLAIN plan, using the following command: => SELECT ANALYZE_STATISTICS(‘<table_schema>.<table_name>’); When statistics complete, repeat Step 5 to see if the plan changes or produces different abnormalities. For more information about ANALYZE STATISTICS, see the Vertica documentation.
7 Vertica analyzes the query and changes the join order of the query if the new join order is more efficient. If the join order of the query has changed, you can force the join order by using a SYNTACTIC_JOIN hint. The hint enforces the join order and enables other join hints. => SELECT /*+SYNTACTIC_JOIN*/ If using the Vertica query optimizer hint improves the query performance, contact Vertica Technical Support to report a regression.
8 With real-time profiling, you can monitor long-running queries while they execute. Profile a query to identify query slowness.For more information about profiling query execution, see System Tables for Analyzing Query Performance in the Vertica Knowledge Base. If your query performance has improved, you have completed this checklist. If you query performance has not improved, contact Vertica Technical Support.

Learn More

To improve the performance of a particular query, see Troubleshooting Vertica Query Performance with-System Tables in the Vertica Knowledge Base.