Created 08-22-2024 02:11 AM
Hi all,
in my test cluster I am noticing a slowdown in the execution of acid queries.
Analyzing in detail I noticed that the compactions remain stuck at "ready for cleaning" and there are many delta files.
I also tried to manually launch the compaction without any result. hive.metastore.housekeeping.threads.on and hive.metastore.housekeeping.threads.on is true only in 1 hive metastore host.
This is a table properties:
bucketing_version 2
transactional true
transactional_properties default
transient_lastDdlTime 1720453037
In the development cluster with the identical configuration I do not have this problem. Do you have any suggestions?
I'm running in CDP 7.1.9
Thanks, Lorenzo
Created 09-05-2024 04:53 AM
Created 08-22-2024 04:24 AM
Please verify if there are any long-running transactions on the cluster and, if found, consider aborting them using the "abort transactions" command, if it is safe to do so.
You can use the "show transactions" command in Beeline to validate the long-running transactions.
Another alternative is to use the following backend DB query .
SELECT * FROM "TXNS" WHERE "TXN_ID" = ( SELECT min(res.id) FROM ( SELECT "NTXN_NEXT" AS id FROM "NEXT_TXN_ID" UNION ALL SELECT "MHL_TXNID" FROM "MIN_HISTORY_LEVEL" WHERE "MHL_TXNID" = ( SELECT min("MHL_MIN_OPEN_TXNID") FROM "MIN_HISTORY_LEVEL" ) ) res)
Note: This query is for postgres DB, modify it depending upon the backend DB in which you're using.
Created on 08-22-2024 06:29 AM - edited 08-22-2024 06:30 AM
Hi,
Created 08-22-2024 11:53 PM
To determine the cause of the failure, it is recommended to review the HMS logs within the specified time frame as the exception stack-trace does not provide sufficient information.
Created 08-27-2024 08:44 AM
I deleted the open transactions from the Oracle db. After restarting hive unfortunately I still have the same problems. There are no error messages from the logs and the tables are not locked.
INFO org.apache.hadoop.hive.ql.txn.compactor.Cleaner: [Cleaner-executor-thread-0]: Starting cleaning for id:5365402,dbname:XXXX,tableName:XXXX,partName:schema_sorgente=XXXX,state:,type:MAJOR,enqueueTime:0,start:0,properties:null,runAs:hive,tooManyAborts:false,hasOldAbort:false,highestWriteId:826,errorMessage:null,workerId: null,initiatorId: null
2024-08-27 14:26:53,877 WARN org.apache.hadoop.hive.ql.txn.compactor.Cleaner: [Cleaner-executor-thread-0]: id=5365402 Remained 21 obsolete directories from hdfs://XXXX. [base_0000201_v1772045,base_0000014_v1403023,delta_0000002_0000002_0000,delete_delta_0000003_0000003_0000,delta_0000003_0000003_0000,delta_0000004_0000004_0000,delete_delta_0000007_0000007_0000,delta_0000007_0000007_0000,delta_0000008_0000008_0000,delete_delta_0000011_0000011_0000,delta_0000011_0000011_0000,delta_0000012_0000012_0000,delete_delta_0000013_0000013_0000,delta_0000013_0000013_0000,delta_0000014_0000014_0000,delete_delta_0000200_0000200_0000,delta_0000200_0000200_0000,delta_0000201_0000201_0000,delete_delta_0000498_0000498_0000,delta_0000498_0000498_0000,delta_0000499_0000499_0000]
2024-08-27 14:26:53,877 WARN org.apache.hadoop.hive.ql.txn.compactor.Cleaner: [Cleaner-executor-thread-0]: No files were removed. Leaving queue entry id:5365402,dbname:XXXX,tableName:XXXX,partName:schema_sorgente=XXXX,state:,type:MAJOR,enqueueTime:0,start:0,properties:null,runAs:hive,tooManyAborts:false,hasOldAbort:false,highestWriteId:826,errorMessage:null,workerId: null,initiatorId: null in ready for cleaning state.
Created 08-28-2024 12:07 AM
Based on the INFO logs, it appears that there is an open transaction blocking the compaction cleaner process. This requires a separate investigation, so I advise raising a support case to resolve the problem. Additionally, we need to examine the HMS logs, backend DB dump, and the output of "hdfs dfs -ls -R" command.
Created 09-05-2024 04:53 AM