[jira] [Commented] (HIVE-25672) Hive isn't purging older compaction entries from show compaction command
[ https://issues.apache.org/jira/browse/HIVE-25672?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17598020#comment-17598020 ] Rohan Nimmagadda commented on HIVE-25672: - We have more than 2M completed transactions in HIVE, with default HIVE properties the backend DB (Postgres) is not able to handle the query to delete in bigger chunks it failed out with the below expectation {code:java} An I/O error occurred while sending to the backend. (SQLState=08006, ErrorCode=0) 2022-08-25T15:06:00,256 ERROR [pool-6-thread-6]: txn.AcidCompactionHistoryService (AcidCompactionHistoryService.java:run(64)) - Serious error in pool-6-thread-6 org.apache.hadoop.hive.metastore.api.MetaException: Unable to connect to transaction database org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend. purgeCompactionHistory() : An I/O error occurred while sending to the backend {code} So we applied the HIVE-25659 to HIVE 3.1 Version and added the below configs to delete older completed txn's The below configurations should be documented # hive.direct.sql.max.parameters=1 (Any one instance of HMS) # hive.metastore.housekeeping.threads.on=true # hive.metastore.task.threads.remote=true (Any one instance of HMS) # hive.compactor.history.retention.succeeded=1 # hive.compactor.history.retention.failed=3 > Hive isn't purging older compaction entries from show compaction command > > > Key: HIVE-25672 > URL: https://issues.apache.org/jira/browse/HIVE-25672 > Project: Hive > Issue Type: Bug > Components: Hive, Metastore, Transactions >Affects Versions: 3.1.1 >Reporter: Rohan Nimmagadda >Priority: Minor > > Added below properties in hive-site, but it's not enforced to auto purging. > When we run show compaction command it takes forever and returns billions of > rows. > Result of show compactions command : > {code:java} > 752,450 rows selected (198.066 seconds) > {code} > {code:java} > hive.compactor.history.retention.succeeded": "10", > "hive.compactor.history.retention.failed": "10", > "hive.compactor.history.retention.attempted": "10", > "hive.compactor.history.reaper.interval": "10m" {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (HIVE-25672) Hive isn't purging older compaction entries from show compaction command
[ https://issues.apache.org/jira/browse/HIVE-25672?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17481313#comment-17481313 ] Zoltan Haindrich commented on HIVE-25672: - HIVE-25633 could cause the AcidHouseKeeperService to not run > Hive isn't purging older compaction entries from show compaction command > > > Key: HIVE-25672 > URL: https://issues.apache.org/jira/browse/HIVE-25672 > Project: Hive > Issue Type: Bug > Components: Hive, Metastore, Transactions >Affects Versions: 3.1.1 >Reporter: Rohan Nimmagadda >Priority: Minor > > Added below properties in hive-site, but it's not enforced to auto purging. > When we run show compaction command it takes forever and returns billions of > rows. > Result of show compactions command : > {code:java} > 752,450 rows selected (198.066 seconds) > {code} > {code:java} > hive.compactor.history.retention.succeeded": "10", > "hive.compactor.history.retention.failed": "10", > "hive.compactor.history.retention.attempted": "10", > "hive.compactor.history.reaper.interval": "10m" {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (HIVE-25672) Hive isn't purging older compaction entries from show compaction command
[ https://issues.apache.org/jira/browse/HIVE-25672?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17481265#comment-17481265 ] Zoltan Haindrich commented on HIVE-25672: - I tried to reproduce this issue; at first metastore.compactor.initiator.on was disabled on my cluster for some reason; but after turning that on things started working correctly: * a metastore with 52M of heap was able to cleanup 10K of records in no time ** and was OOM-ed for 100K * a metastore with 966K rows in the COMPLETED_COMPACTIONS table ** removed 50773 rows multiple times - and was able to reduce the volume to below 100 in around a minute I don't know if we have an issue here - as it seems like that most likely for some reason either the `AcidHouseKeeperService` is not running - or stopped running for some reason > Hive isn't purging older compaction entries from show compaction command > > > Key: HIVE-25672 > URL: https://issues.apache.org/jira/browse/HIVE-25672 > Project: Hive > Issue Type: Bug > Components: Hive, Metastore, Transactions >Affects Versions: 3.1.1 >Reporter: Rohan Nimmagadda >Priority: Minor > > Added below properties in hive-site, but it's not enforced to auto purging. > When we run show compaction command it takes forever and returns billions of > rows. > Result of show compactions command : > {code:java} > 752,450 rows selected (198.066 seconds) > {code} > {code:java} > hive.compactor.history.retention.succeeded": "10", > "hive.compactor.history.retention.failed": "10", > "hive.compactor.history.retention.attempted": "10", > "hive.compactor.history.reaper.interval": "10m" {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (HIVE-25672) Hive isn't purging older compaction entries from show compaction command
[ https://issues.apache.org/jira/browse/HIVE-25672?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17440396#comment-17440396 ] Karen Coppage commented on HIVE-25672: -- Make sure the AcidHouseKeeperService is running: hive.metastore.housekeeping.threads.on=true. Does this fix the issue? > Hive isn't purging older compaction entries from show compaction command > > > Key: HIVE-25672 > URL: https://issues.apache.org/jira/browse/HIVE-25672 > Project: Hive > Issue Type: Bug > Components: Hive, Metastore, Transactions >Affects Versions: 3.1.1 >Reporter: Rohan Nimmagadda >Priority: Minor > > Added below properties in hive-site, but it's not enforced to auto purging. > When we run show compaction command it takes forever and returns billions of > rows. > Result of show compactions command : > {code:java} > 752,450 rows selected (198.066 seconds) > {code} > {code:java} > hive.compactor.history.retention.succeeded": "10", > "hive.compactor.history.retention.failed": "10", > "hive.compactor.history.retention.attempted": "10", > "hive.compactor.history.reaper.interval": "10m" {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)