[
https://issues.apache.org/jira/browse/HIVE-26145?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17523138#comment-17523138
]
Janos Kovacs commented on HIVE-26145:
-------------------------------------
The critical one was only on TXN_WRITE_NOTIFICATION_LOG, seems NOTIFICATION_LOG
has proper index.
{noformat}
MariaDB [(none)]> select distinct blocking_trx_id from (SELECT r.trx_id
waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query
waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN
information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id) as a where
a.blocking_query like '%TXN_WRITE_NOTIFICATION_LOG%';
+-----------------+
| blocking_trx_id |
+-----------------+
| 3086363831 |
| 3086389504 |
| 3086363966 |
| 3086366531 |
+-----------------+
{noformat}
Increasing startup delay - like to days or weeks - is rather a workaround than
a proper solution for disabling the task in certain instances.
For the workaround we also increased the TTL on the other 4 instances - just
for the case if the startup delay would not be long enough, ensure the other
instances find no record to clean up, that way those nodes would not execute
DELETE statements.
The no-run-on-zero-freq flag would be a more proper way to turn off the
cleaners.
> Disable notification cleaner if interval is zero
> ------------------------------------------------
>
> Key: HIVE-26145
> URL: https://issues.apache.org/jira/browse/HIVE-26145
> Project: Hive
> Issue Type: Sub-task
> Components: Metastore
> Reporter: Janos Kovacs
> Assignee: Janos Kovacs
> Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Many of the housekeeping/background tasks can be turned off in case of having
> multiple instances running parallel.
> Some are controlled via the housekeeping node configuration, others are not
> started if their frequency is set to zero.
> The DB-Notification cleaner unfortunately doesn't have this functionality
> which makes all instances to race for the lock on the backend HMS database.
> Goal is to add change to be able to turn cleaner off in case if there are
> multiple instances running (be able to bound it to the housekeeping
> instance).
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
