[
https://issues.apache.org/jira/browse/HIVE-29813?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Marta Kuczora updated HIVE-29813:
---------------------------------
Summary: (ACID) Improve the query of duplicate deletion from
COMPLETED_TXN_COMPONENTS by extending an index (was: ACID - Improve the query
of duplicate deletion in COMPLETED_TXN_COMPONENTS by extending an index)
> (ACID) Improve the query of duplicate deletion from COMPLETED_TXN_COMPONENTS
> by extending an index
> --------------------------------------------------------------------------------------------------
>
> Key: HIVE-29813
> URL: https://issues.apache.org/jira/browse/HIVE-29813
> Project: Hive
> Issue Type: Task
> Reporter: Marta Kuczora
> Assignee: Marta Kuczora
> Priority: Major
> Labels: pull-request-available
>
> Investigated the query in RemoveDuplicateCompleteTxnComponentsCommand which
> deletes duplicates from the COMPLETED_TXN_COMPONENTS table. I did some
> testing with this query, mainly focusing on MySQL backend. Here is the result:
> I used a test dataset for testing the delete statement which contained
> 8.000.000 rows in the COMPLETED_TXN_COMPONENTS table, and 7.996.497 rows got
> deleted by the query during the testing.
> The delete query ran ~130 sec.
> Then extended the index on the COMPLETED_TXN_COMPONENTS table to include the
> CTC_WRITEID and CTC_UPDATE_DELETE columns as well. Please note that the
> existing index already contained the CTC_DATABASE, CTC_TABLE and
> CTC_PARTITION columns.
> The delete query in this case ran ~100 sec.
> Extending the index would be an easy way to improve the runtime of this
> query. I checked how much overhead the index extension would mean, but it was
> negligable.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
