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.About the Author
Soniya Shah
Information Developer
Currently, a first year law student with a background in science and technology. Experienced technical writer, with specializations in software documentation, big data, blog development, and website development. I build user-centered content to communicate complex and technical information more easily.
I used to work for Vertica full time for about 3 years. I still work at Vertica part time while going to law school.
Update: Soniya is now doing her law internship, and no longer working at Vertica. Good luck, Soniya!
