[
https://issues.apache.org/jira/browse/HIVE-29813?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Marta Kuczora updated HIVE-29813:
---------------------------------
Description:
Investigated two queries which could be improved by adding some indexes. One is
the one used by the
RemoveDuplicateCompleteTxnComponentsCommand to delete duplicates from the
COMPLETED_TXN_COMPONENTS table. The other is the one used by MetricsInfoHandler
to collect metrics.
I did some testing with these queries, mainly focusing on MySQL backend. Here
are the results:
********* Metrics collection *************
I ran a test with the following amount of data with a local MySQL:
TXN_COMPONENTS - 2.000.000 rows
COMPLETED_TXN_COMPONENTS - 6.970.000 rows
TXNS - 2.000.001 rows
COMPACTION_QUEUE - 20.000 rows
I ran the {{{}MetricsInfoHandler{}}}.{{{}SELECT_METRICS_INFO_QUERY{}}} query
and it took ~2.5 sec.
Then added the following indexes to the HMS backend DB:
{code:java}
CREATE INDEX TXNS_STATE_TYPE_ID_IDX ON TXNS (TXN_STATE, TXN_TYPE, TXN_ID)
ALGORITHM=INPLACE LOCK=NONE;
CREATE INDEX HL_ACQUIRED_AT_IDX ON HIVE_LOCKS (HL_ACQUIRED_AT)
ALGORITHM=INPLACE LOCK=NONE;
CREATE INDEX CQ_STATE_COMMIT_TIME_IDX ON COMPACTION_QUEUE (CQ_STATE,
CQ_COMMIT_TIME) ALGORITHM=INPLACE LOCK=NONE; {code}
With the indexes the query ran ~1.5 sec.
********* Duplicate deletion *************
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.
> ACID - Improve the queries of the metrics collection and duplicate deletion
> by adding indexes
> ---------------------------------------------------------------------------------------------
>
> 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
>
> Investigated two queries which could be improved by adding some indexes. One
> is the one used by the
> RemoveDuplicateCompleteTxnComponentsCommand to delete duplicates from the
> COMPLETED_TXN_COMPONENTS table. The other is the one used by
> MetricsInfoHandler to collect metrics.
> I did some testing with these queries, mainly focusing on MySQL backend. Here
> are the results:
> ********* Metrics collection *************
> I ran a test with the following amount of data with a local MySQL:
> TXN_COMPONENTS - 2.000.000 rows
> COMPLETED_TXN_COMPONENTS - 6.970.000 rows
> TXNS - 2.000.001 rows
> COMPACTION_QUEUE - 20.000 rows
> I ran the {{{}MetricsInfoHandler{}}}.{{{}SELECT_METRICS_INFO_QUERY{}}} query
> and it took ~2.5 sec.
> Then added the following indexes to the HMS backend DB:
>
> {code:java}
> CREATE INDEX TXNS_STATE_TYPE_ID_IDX ON TXNS (TXN_STATE, TXN_TYPE, TXN_ID)
> ALGORITHM=INPLACE LOCK=NONE;
> CREATE INDEX HL_ACQUIRED_AT_IDX ON HIVE_LOCKS (HL_ACQUIRED_AT)
> ALGORITHM=INPLACE LOCK=NONE;
> CREATE INDEX CQ_STATE_COMMIT_TIME_IDX ON COMPACTION_QUEUE (CQ_STATE,
> CQ_COMMIT_TIME) ALGORITHM=INPLACE LOCK=NONE; {code}
>
> With the indexes the query ran ~1.5 sec.
>
> ********* Duplicate deletion *************
> 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)
