Taraka Rama Rao Lethavadla created HIVE-27976:
-------------------------------------------------
Summary: Improve logic/query to clean COMPLETED_TXN_COMPONENTS
table
Key: HIVE-27976
URL: https://issues.apache.org/jira/browse/HIVE-27976
Project: Hive
Issue Type: Improvement
Components: Hive
Reporter: Taraka Rama Rao Lethavadla
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|https://github.com/apache/hive/pull/4970#issuecomment-1875219288]
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
{noformat}
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") {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
