What Should I do if the Database Performance is Slow?
Troubleshootusing the following checklist if your database performance is slow.
Check if any of the following problems exist:
|1||Is the query performance slow?||If the query performance is slow, review the Query Performance checklist.
If the query performance is not slow, go to Step 2.
|2||Is the entire database slow?||If the whole database is slow, go to Step 3.
If the whole database is not slow, your checklist is complete.
|3||Check if all the nodes are UP.
||If there is any node DOWN,
If nodes restarted and the performance improved, your checklist is complete.
If the node restarted and performance is still slow, go to Step 4.
If the node did not restart, review the Node Down checklist.
|4||Check if there are too many delete vectors.
||If there are more than 1000 delete vectors, review the Manage Delete Vectors checklist.
If there are not too many delete vectors, go to Step 5.
|5||Check if epochs are advancing.
||If epochs are not advancing, review the AHM not Advancing checklist.
If epochs are advancing, go to Step 6.
|6||Check if one node is slower than the others.
Run a select statement for each node in the cluster and identify the there is a slower node.
||If one node is slower than the others,
|7||Check if the workload is balanced across all the nodes.
||If one node has a heavier workload, distribute the workload to all the nodes.
Review the documentation on Connection Load balancing.
If the workload is balanced, go to Step 8.
|8||Check if there are resource rejections.
||If there are resource rejections, review the Query Performance checklist.
If there are no significant resource rejections that justify slowness go to Step 9.
|9||Check if there are sessions in queue.
||If there are queries waiting for resources, go to Step 10.|
|10||Check if there are long-running sessions that are using too many resources.
||If there is a statement running for too long and using a high proportion of the box resources, consider stopping the statement, |
Upon statement cancellation, resources should be freed and performance should improve.
If it does not improve, go to Step 11..
If the statement does not terminate properly, contact Vertica Technical Support.
|11||Check if any transactions are waiting for locks.
||If transactions are waiting for locks, identify lock-holding sessions and consider wait for transaction to complete or cancel the session to free locks.
If it does not improve, go to Step 12.
If the session does not terminate properly, contact Vertica Technical Support reporting a hang session.
|12||Check the catalog size in memory.
||If catalog is larger than the 5% of memory in the host, resource pools should be adjust to free memory needed by the catalog as Vertica process is a risk of being terminated by the kernel with OOM.
Contact Vertica Technical Support to debug catalog size growth and discuss alternatives to free memory to be used to allocated catalog. Alternatives are:
In Many cases restarting the node could free memory used by catalog, debugging with support will help to determine the best course of action.
|13||Check usage of resident and virtual memory and maps of memory created.
||If Virtual Memory or resident memory is high, monitor to see if the numbers lower.
If the numbers do not lower, contact Vertica Technical support to debug the issue.
Restarting the nodes should resolve the issue but a proper debugging should be done, follow Catalog Size Debugging checklist.