hivesever2编译sql慢问题处理_hive执行sql很慢-CSDN博客

本文链接：https://blog.csdn.net/sunxunyong/article/details/142923824

问题描述：
使用beeline连接hive，执行show databases 很慢200s-600s
使用hive客户端连hive，执行show databases <1s

现象分析：
hive客户端是直接连hivemetastore查询，说明hivemetastore正常。
问题在hiveserver2！

hs2开启DEBUG日志
发现执行卡在：
在这里插入图片描述

beeline开启执行日志：
hive.server2.logging.operation.enabled=true
set hive.server2.logging.operation.level=VERBOSE;

分析hs2的线程锁：
jstack -l hs2pid >> jslog
使用在线工具分析： https://spotify.github.io/threaddump-analyzer/
在这里插入图片描述

分析hs2-handler在wait ***f80
搜索jstack日志原始文件：Locked，找到 f80 的锁
Locked ownable synchronizers:

<0x000000046c061f80> (a java.util.concurrent.locks.ReentrantLock$FairSync)
<0x0000000480b2dc28> (a java.util.concurrent.ThreadPoolExecutor$Worker)
分析这个线程：
“a1b4656f-8bd6-4695-8741-6185dac91a7e HiveServer2-Handler-Pool: Thread-3070” #3070 prio=5 os_prio=0 tid=0x00007f7aec96f800 nid=0x5c9e runnable [0x00007f7aab66b000]
java.lang.Thread.State: RUNNABLE
at java.io.RandomAccessFile.writeBytes(Native Method)
at java.io.RandomAccessFile.write(RandomAccessFile.java:525)
at org.apache.logging.log4j.core.appender.RandomAccessFileManager.writeToDestination(RandomAccessFileManager.java:89)
at org.apache.logging.log4j.core.appender.OutputStreamManager.flushBuffer(OutputStreamManager.java:288)
eliminated <0x0000000470d4da00> (a org.apache.logging.log4j.core.appender.RandomAccessFileManager)
at org.apache.logging.log4j.core.appender.RandomAccessFileManager.flush(RandomAccessFileManager.java:98)
locked <0x0000000470d4da00> (a org.apache.logging.log4j.core.appender.RandomAccessFileManager)
at org.apache.logging.log4j.core.appender.AbstractOutputStreamAppender.directEncodeEvent(AbstractOutputStreamAppender.java:179)
at org.apache.logging.log4j.core.appender.AbstractOutputStreamAppender.tryAppend(AbstractOutputStreamAppender.java:170)
at org.apache.logging.log4j.core.appender.AbstractOutputStreamAppender.append(AbstractOutputStreamAppender.java:161)
at org.apache.hadoop.hive.ql.log.HushableRandomAccessFileAppender.append(HushableRandomAccessFileAppender.java:101)
at org.apache.logging.log4j.core.config.AppenderControl.tryCallAppender(AppenderControl.java:156)
at org.apache.logging.log4j.core.config.AppenderControl.callAppender0(AppenderControl.java:129)
at org.apache.logging.log4j.core.config.AppenderControl.callAppenderPreventRecursion(AppenderControl.java:120)
at org.apache.logging.log4j.core.config.AppenderControl.callAppender(AppenderControl.java:84)
at org.apache.logging.log4j.core.appender.routing.RoutingAppender.append(RoutingAppender.java:227)
at org.apache.logging.log4j.core.config.AppenderControl.tryCallAppender(AppenderControl.java:156)
at org.apache.logging.log4j.core.config.AppenderControl.callAppender0(AppenderControl.java:129)
at org.apache.logging.log4j.core.config.AppenderControl.callAppenderPreventRecursion(AppenderControl.java:120)
at org.apache.logging.log4j.core.config.AppenderControl.callAppender(AppenderControl.java:84)
at org.apache.logging.log4j.core.config.LoggerConfig.callAppenders(LoggerConfig.java:448)
at org.apache.logging.log4j.core.config.LoggerConfig.processLogEvent(LoggerConfig.java:433)
at org.apache.logging.log4j.core.config.LoggerConfig.log(LoggerConfig.java:417)
at org.apache.logging.log4j.core.config.LoggerConfig.log(LoggerConfig.java:403)
at org.apache.logging.log4j.core.config.AwaitCompletionReliabilityStrategy.log(AwaitCompletionReliabilityStrategy.java:63)
at org.apache.logging.log4j.core.Logger.logMessage(Logger.java:146)
at org.apache.logging.log4j.spi.AbstractLogger.logMessageSafely(AbstractLogger.java:2091)
at org.apache.logging.log4j.spi.AbstractLogger.logMessage(AbstractLogger.java:1999)
at org.apache.logging.log4j.spi.AbstractLogger.logIfEnabled(AbstractLogger.java:1868)
at org.apache.logging.slf4j.Log4jLogger.debug(Log4jLogger.java:134)
at org.apache.sentry.provider.common.ResourceAuthorizationProvider.doHasAccess(ResourceAuthorizationProvider.java:144)
at org.apache.sentry.provider.common.ResourceAuthorizationProvider.hasAccess(ResourceAuthorizationProvider.java:112)
at org.apache.sentry.binding.hive.authz.HiveAuthzBinding.authorize(HiveAuthzBinding.java:336)
at org.apache.sentry.binding.hive.authz.HiveAuthzBindingHookBase.authorizeWithHiveBindings(HiveAuthzBindingHookBase.java:514)
at org.apache.sentry.binding.hive.HiveAuthzBindingHook.postAnalyze(HiveAuthzBindingHook.java:354)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:600)
at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1425)
at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1398)
at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:205)
at org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:290)
at org.apache.hive.service.cli.operation.Operation.run(Operation.java:260)
at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:505)
at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:491)
at org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:295)
at org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:507)
at org.apache.hive.service.rpc.thrift.TCLIService $P rocessor$ ExecuteStatement.getResult(TCLIService.java:1437)
at org.apache.hive.service.rpc.thrift.TCLIService $P rocessor$ ExecuteStatement.getResult(TCLIService.java:1422)
at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
at org.apache.hive.service.auth.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:56)
at org.apache.thrift.server.TThreadPoolServer $W or k er P rocess . r u n (TT h re a d P oo lS er v er . ja v a : 286) a t ja v a . u t i l . co n c u rre n t . T h re a d P oo lE x ec u t or . r u nW or k er (T h re a d P oo lE x ec u t or . ja v a : 1149) a t ja v a . u t i l . co n c u rre n t . T h re a d P oo lE x ec u t or$ Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Locked ownable synchronizers:
<0x000000046c061f80> (a java.util.concurrent.locks.ReentrantLock$FairSync)
<0x0000000480b2dc28> (a java.util.concurrent.ThreadPoolExecutor$Worker)
问题出现在这段：sentry导致！
at org.apache.sentry.binding.hive.authz.HiveAuthzBinding.authorize(HiveAuthzBinding.java:336)
at org.apache.sentry.binding.hive.authz.HiveAuthzBindingHookBase.authorizeWithHiveBindings(HiveAuthzBindingHookBase.java:514)
at org.apache.sentry.binding.hive.HiveAuthzBindingHook.postAnalyze(HiveAuthzBindingHook.java:354)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:600)
尝试关闭hive的sentry验证：
1、搜索：doas hiveserver2启动模拟 --打开
2、搜索：sentry sentry服务 --选择none
3、搜索：sentry.hive.testing.mode --改为false

重启hive集群，验证。
报错：create/drop 表报错 read-only parameter: DB_NOTIFICATION_EVENT_ID_KEY_NAME
修改配置：
hive.metastore.event.db.notification.api.auth=false
hive.metastore.transactional.event.listeners 空值 --删掉
hive.metastore.event.listeners org.apache.hive.hcatalog.listener.DbNotificationListener --删掉

重启hive集群，验证成功！