Vertica In-active partitions

edited July 17 in General Discussion

Dear's,
i have partitioned table contineouly feeding data how would i know the active partition or those which are in-active any more, for some reason i can see the very old partition key in my table projection i can tell these can be created by the wrong dattimestamp filed (originaleventtime),
v_my_node0001 201804 0 1
v_my_node0001 206004 0 8
v_my_node0002 201808 0 1
v_my_node0002 201804 0 1
v_my_node0003 201904 0 2
v_my_node0003 201808 0 1
if these are in-active i have perform the below step to make the partiotion active and correct but i am not sure if this can activiate or create new active partition.
ALTER DATABASE mydb SET ACTIVEPARTITIONCOUNT=2;
Br,

Answers

  • Bryan_HBryan_H Employee

    It is not always necessary to increase active partition count. Older partitions are not explicitly read-only, but Tuple Mover will work on inactive partitions with lower priority. The following query will find which partitions are "active" in the sense that they are being processed as "strata" in Tuple Mover:

    SELECT p.node_name, p.partition_key, p.ros_id, p.ros_size_bytes, p.ros_row_count, ROS_container_count
    FROM partitions p JOIN strata s ON p.partition_key = s.stratum_key AND p.node_name=s.node_name
    WHERE p.projection_name = '<your_projection_name>' ORDER BY p.node_name, p.partition_key;

    It will help to know what version of Vertica you are using, because Tuple Mover behavior changed a bit between major versions (7.x, 8.x, 9.x, 10.x)

  • Vertica version is 8.x

  • Bryan_HBryan_H Employee

    I'm not sure I understand the issue. Even data loaded into inactive partitions is available for query and will be processed through strata eventually. It is just not fully optimized at first. In Vertica 8.x, you can assign more resources to TM resource pool, then run SELECT DO_TM_TASK('mergeout'); and TM will start to merge out inactive partition data though it will take longer to process and the manual mergeout may take a noticeable amount of resources causing other jobs and queries to slow.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.