Catalog Size Debugging
You have a large catalog if your catalog size is more than 10 GB or your catalog is changing more than 5% per day. This checklist gives you some suggestions and recommendations to monitor and reduce your database catalog size.
The database catalog contains tractable metadata such as tables, projections, users, nodes, ROSes, and so on. There are two types of objects in the catalog:
- Global objects are not node-specific. They include tables, users, and nodes. Because any node serves as a query initiator, global objects are fully replicated.
- Local objects are node-specific. They include ROS, WOS, and dependencies. Since storage layout may vary by node, local objects are not replicated. Each node tracks its storage layout independently.
When a node starts, the catalog is cached in memory.
|1||Check the catalog size in all the nodes:
||If you have a large catalog that is increasing at a gradual speed and might affect the performance of your database, consider upgrading to Vertica 7.2.3-15 and above. Alternatively, work with this checklist to find the root of the catalog size.
If the catalog size in memory is below 5% of your total memory and your Vertica performance is acceptable then this is the end of your checklist.
If your catalog is within 5% but is more than 10 GB, use this checklist to find the root cause of your large catalog size. Consider reducing your catalog size using techniques in this checklist.
|2||To see if your catalog is growing and to verify if your database has too many objects, use the following queries:
||If you have a large number of objects for your catalog size, consider reducing unnecessary objects. You can reduce the objects by:
If you do not have a large number of objects for your catalog size, go to Step 3.
|3||Check if your catalog is growing due to an increase in delete vectors.
||If you see a large number of delete vectors relative to the size of your database, consider cleaning the delete vectors. For more information see Delete Vectors checklist.
Cleaning delete vectors lowers catalog size in memory.
After you clean delete vectors, you must restart the database or the node for the catalog size to reduce in size.
If there are not too many delete vectors, go to Step 4.
|4||Check if there are there many ROS containers?
||If ROS containers COUNT is high, go to Step 5 to see if there are projections with too many ROS containers.
You can also contact Vertica Technical Support to perform a deeper analysis of why the catalog is so large.
|5||Identify projections with higher ROS containers.
||If there are too many projections with too many ROS containers, but the average ROS count is lower than 300, go to Step 11.
If there are not too many projections but the AVG_ROS_COUNT is above 500, go to Step 6.
|6||Check if you have too many partitions based on how you designed your tables.
If projections are not partitioned or the number of partitions is less than 600, Step 7.
If there are projections with many unique partitions ID, change the table partition key to have fewer partitions per projection, and go to Step 7.
|7||Monitor if mergeout is working for that particular projection.
||If the last status is not COMPLETE wait until mergeout completes and COUNT ROS containers with query.
|8||Run a manual mergeout.
||If mergeout does not reduce the number of ROS containers, contact Vertica Technical support.|
|9||Restart Node to recover memory after adjusting catalog objects and verify catalog size in memory.
||If restarting the node is not possible, go to Step 10.|
|10||If your Vertica version is earlier than 8.0.1, adjust the resource pool settings to avoid memory swapping.
Create a resource pool size of 100 – (General Pool Memory Size ) – % catalog in memory.
For example, if the catalog in memory is 8% and the general pool memory size is 95% (the default) the calculation is 100 – 95 – 8 = –3.
We allocate 2% in case that catalog grows.
This pool can be adjusted without restarting to avoid swapping or out of memory.
In Vertica 8.0.1, Vertica automatically adjusts METADATA pool.
|Adjust pool to reflect catalog size so resource manager do not plan queries with this memory and avoid OOM or swapping.
This pool adjustment does not resolve performance issues, to resolve performance issue consider the steps to fix catalog size.
|11||If there are too many partitioned tables, but the number of partitions is still as recommended, do one of the following:
||If it is not possible to no partition small tables go to Step 10 to adjust resource pools.|
|12||Identify if there are partitions with fewer rows because those tables should not be partitioned. Partition tables with rows more than 100 per partition.
||Consider repartition tables that have several partitions with few rows in each table.
Alternatively, do not partition those tables at all.
If modifying tables is not an option go to Step 10 to adjust resource pools.