hivesever2编译sql慢问题处理

问题描述：
使用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 r o c e s s o r Processor ProcessorExecuteStatement.getResult(TCLIService.java:1437) at org.apache.hive.service.rpc.thrift.TCLIService P r o c e s s o r Processor ProcessorExecuteStatement.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 o r k e r P r o c e s s . r u n ( T T h r e a d P o o l S e r v e r . j a v a : 286 ) a t j a v a . u t i l . c o n c u r r e n t . T h r e a d P o o l E x e c u t o r . r u n W o r k e r ( T h r e a d P o o l E x e c u t o r . j a v a : 1149 ) a t j a v a . u t i l . c o n c u r r e n t . T h r e a d P o o l E x e c u t o r WorkerProcess.run(TThreadPoolServer.java:286) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor WorkerProcess.run(TThreadPoolServer.java:286)atjava.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)atjava.util.concurrent.ThreadPoolExecutorWorker.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集群，验证成功！