[HIVE-27976] Improve logic/query to clean COMPLETED_TXN_COMPONENTS table

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: Hive
• Assignee: Unassigned
• Reporter: Taraka Rama Rao Lethavadla
• Created: 04/Jan/24 08:34
• Updated: 04/Jan/24 08:38

Description

removeDuplicateCompletedTxnComponents seems to take more time in busy clusters where the number of entries grow at a higher rate in COMPLETED_TXN_COMPONENTS

Copying discussion from HIVE-27022

What about considering an other approach? What if instead of running the clean up in one, large transaction, we try to run multiple small ones?

For example, at MSSQL we did measures back then and found that when we want to delete large amount of records, it is way faster in batches under 5000 element (usually we used 4000).

The reason why it was faster was the locking mechanism of the database: for large amount of records, it put exclusive locks and you cannot use the table in other processes and it can cause performance issues.

The clean up in that case is a little bit complicated: it can be slow because of the time to take to delete the records or it can be the time to collect the records that we want to delete.

For those kind of scenarios I would recommend to have two parameters for the clean up:

• Batch size
• Number of iterations
  And I would still keep a 1 minute interval as default.

So that, it can be easily to fine tune the parameters for the customers: if there are too many records to delete, just increase the number of iterations. If it takes too large of time to collect what to delete, increase the time window and/or the batch size.

Another thought, 

How about modifying the query like

DELETE FROM "completed_txn_components" "tc" WHERE  rowid in (SELECT :"SYS_B_0"                FROM   "completed_txn_components"                WHERE  "ctc_database" = "tc"."ctc_database"                       AND "ctc_table" = "tc"."ctc_table"                       AND ( "ctc_partition" = "tc"."ctc_partition"                              OR ( "ctc_partition" IS NULL                                   AND "tc"."ctc_partition" IS NULL ) )                       AND ( "tc"."ctc_update_delete" = :"SYS_B_1"                              OR "tc"."ctc_update_delete" = :"SYS_B_2"                                 AND "ctc_update_delete" = :"SYS_B_3" )                       AND "tc"."ctc_writeid" < "ctc_writeid") 

Or

How about we do this clean up query to clean entries related to a table/partition as part of Cleaner itself? so that the overall load on house keeper get's reduced

Issue Links

• is a child of: HIVE-27022 Split removeDuplicateCompletedTxnComponents away from AcidHouseKeeper and onto a separate timer