多进程数据库不适合作为hive的元数据库

简介

“今天发现一个比较奇怪的现象，因为博主不熟悉mysql，所以在安装hive的使用了postgresql作为hive的元数据库，在测试几个连接工具对hive进行链接，后面再测试的时候发现链接不上了，并且报错日志如下：”


SLF4J: Failed toString() invocation on an object of type [org.apache.hadoop.hive.ql.session.OperationLog]
java.lang.OutOfMemoryError: GC overhead limit exceeded
FAILED: command has been interrupted: during query execution: 
null
2024-03-16 15:20:55,302 Log4j2-TF-2-AsyncLogger[AsyncContext@71be98f5]-1 ERROR RandomAccessFileManager (/tmp/hadoop/operation_logs/513aee2e-444c-407e-8e5d-2f8541a9d2f7/hadoop_20240316152052_4af3ebb2-0011-48e8-acc3-46f60e08f82f) java.io.FileNotFoundException: /tmp/hadoop/operation_logs/513aee2e-444c-407e-8e5d-2f8541a9d2f7/hadoop_20240316152052_4af3ebb2-0011-48e8-acc3-46f60e08f82f (No such file or directory) java.io.FileNotFoundException: /tmp/hadoop/operation_logs/513aee2e-444c-407e-8e5d-2f8541a9d2f7/hadoop_20240316152052_4af3ebb2-0011-48e8-acc3-46f60e08f82f (No such file or directory)at java.io.RandomAccessFile.open0(Native Method)at java.io.RandomAccessFile.open(RandomAccessFile.java:316)at java.io.RandomAccessFile.<init>(RandomAccessFile.java:243)at java.io.RandomAccessFile.<init>(RandomAccessFile.java:124)at org.apache.logging.log4j.core.appender.RandomAccessFileManager$RandomAccessFileManagerFactory.createManager(RandomAccessFileManager.java:208)at org.apache.logging.log4j.core.appender.RandomAccessFileManager$RandomAccessFileManagerFactory.createManager(RandomAccessFileManager.java:186)at org.apache.logging.log4j.core.appender.AbstractManager.getManager(AbstractManager.java:114)at org.apache.logging.log4j.core.appender.OutputStreamManager.getManager(OutputStreamManager.java:100)at org.apache.logging.log4j.core.appender.RandomAccessFileManager.getFileManager(RandomAccessFileManager.java:72)at org.apache.hadoop.hive.ql.log.HushableRandomAccessFileAppender.createAppender(HushableRandomAccessFileAppender.java:178)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:498)at org.apache.logging.log4j.core.config.plugins.util.PluginBuilder.build(PluginBuilder.java:136)at org.apache.logging.log4j.core.config.AbstractConfiguration.createPluginObject(AbstractConfiguration.java:1120)at org.apache.logging.log4j.core.config.AbstractConfiguration.createConfiguration(AbstractConfiguration.java:1045)at org.apache.logging.log4j.core.appender.routing.RoutingAppender.createAppender(RoutingAppender.java:299)at org.apache.logging.log4j.core.appender.routing.RoutingAppender.getControl(RoutingAppender.java:271)at org.apache.logging.log4j.core.appender.routing.RoutingAppender.append(RoutingAppender.java:229)at org.apache.logging.log4j.core.config.AppenderControl.tryCallAppender(AppenderControl.java:161)at org.apache.logging.log4j.core.config.AppenderControl.callAppender0(AppenderControl.java:134)at org.apache.logging.log4j.core.config.AppenderControl.callAppenderPreventRecursion(AppenderControl.java:125)at org.apache.logging.log4j.core.config.AppenderControl.callAppender(AppenderControl.java:89)at org.apache.logging.log4j.core.config.LoggerConfig.callAppenders(LoggerConfig.java:542)at org.apache.logging.log4j.core.config.LoggerConfig.processLogEvent(LoggerConfig.java:500)at org.apache.logging.log4j.core.config.LoggerConfig.log(LoggerConfig.java:483)at org.apache.logging.log4j.core.config.LoggerConfig.log(LoggerConfig.java:471)at org.apache.logging.log4j.core.config.AwaitCompletionReliabilityStrategy.log(AwaitCompletionReliabilityStrategy.java:98)at org.apache.logging.log4j.core.async.AsyncLogger.actualAsyncLog(AsyncLogger.java:488)at org.apache.logging.log4j.core.async.RingBufferLogEvent.execute(RingBufferLogEvent.java:156)at org.apache.logging.log4j.core.async.RingBufferLogEventHandler.onEvent(RingBufferLogEventHandler.java:51)at org.apache.logging.log4j.core.async.RingBufferLogEventHandler.onEvent(RingBufferLogEventHandler.java:29)at com.lmax.disruptor.BatchEventProcessor.run(BatchEventProcessor.java:129)at java.lang.Thread.run(Thread.java:750)2024-03-16 15:20:55,433 Log4j2-TF-2-AsyncLogger[AsyncContext@71be98f5]-1 ERROR Unable to invoke factory method in class org.apache.hadoop.hive.ql.log.HushableRandomAccessFileAppender for element HushableMutableRandomAccess: java.lang.IllegalStateException: ManagerFactory [org.apache.logging.log4j.core.appender.RandomAccessFileManager$RandomAccessFileManagerFactory@307957f1] unable to create manager for [/tmp/hadoop/operation_logs/513aee2e-444c-407e-8e5d-2f8541a9d2f7/hadoop_20240316152052_4af3ebb2-0011-48e8-acc3-46f60e08f82f] with data [org.apache.logging.log4j.core.appender.RandomAccessFileManager$FactoryData@57fffa4] java.lang.reflect.InvocationTargetExceptionat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:498)at org.apache.logging.log4j.core.config.plugins.util.PluginBuilder.build(PluginBuilder.java:136)at org.apache.logging.log4j.core.config.AbstractConfiguration.createPluginObject(AbstractConfiguration.java:1120)at org.apache.logging.log4j.core.config.AbstractConfiguration.createConfiguration(AbstractConfiguration.java:1045)at org.apache.logging.log4j.core.appender.routing.RoutingAppender.createAppender(RoutingAppender.java:299)at org.apache.logging.log4j.core.appender.routing.RoutingAppender.getControl(RoutingAppender.java:271)at org.apache.logging.log4j.core.appender.routing.RoutingAppender.append(RoutingAppender.java:229)at org.apache.logging.log4j.core.config.AppenderControl.tryCallAppender(AppenderControl.java:161)at org.apache.logging.log4j.core.config.AppenderControl.callAppender0(AppenderControl.java:134)at org.apache.logging.log4j.core.config.AppenderControl.callAppenderPreventRecursion(AppenderControl.java:125)at org.apache.logging.log4j.core.config.AppenderControl.callAppender(AppenderControl.java:89)at org.apache.logging.log4j.core.config.LoggerConfig.callAppenders(LoggerConfig.java:542)at org.apache.logging.log4j.core.config.LoggerConfig.processLogEvent(LoggerConfig.java:500)at org.apache.logging.log4j.core.config.LoggerConfig.log(LoggerConfig.java:483)at org.apache.logging.log4j.core.config.LoggerConfig.log(LoggerConfig.java:471)at org.apache.logging.log4j.core.config.AwaitCompletionReliabilityStrategy.log(AwaitCompletionReliabilityStrategy.java:98)at org.apache.logging.log4j.core.async.AsyncLogger.actualAsyncLog(AsyncLogger.java:488)at org.apache.logging.log4j.core.async.RingBufferLogEvent.execute(RingBufferLogEvent.java:156)at org.apache.logging.log4j.core.async.RingBufferLogEventHandler.onEvent(RingBufferLogEventHandler.java:51)at org.apache.logging.log4j.core.async.RingBufferLogEventHandler.onEvent(RingBufferLogEventHandler.java:29)at com.lmax.disruptor.BatchEventProcessor.run(BatchEventProcessor.java:129)at java.lang.Thread.run(Thread.java:750)
Caused by: java.lang.IllegalStateException: ManagerFactory [org.apache.logging.log4j.core.appender.RandomAccessFileManager$RandomAccessFileManagerFactory@307957f1] unable to create manager for [/tmp/hadoop/operation_logs/513aee2e-444c-407e-8e5d-2f8541a9d2f7/hadoop_20240316152052_4af3ebb2-0011-48e8-acc3-46f60e08f82f] with data [org.apache.logging.log4j.core.appender.RandomAccessFileManager$FactoryData@57fffa4]at org.apache.logging.log4j.core.appender.AbstractManager.getManager(AbstractManager.java:116)at org.apache.logging.log4j.core.appender.OutputStreamManager.getManager(OutputStreamManager.java:100)at org.apache.logging.log4j.core.appender.RandomAccessFileManager.getFileManager(RandomAccessFileManager.java:72)at org.apache.hadoop.hive.ql.log.HushableRandomAccessFileAppender.createAppender(HushableRandomAccessFileAppender.java:178)... 25 more2024-03-16 15:20:55,695 Log4j2-TF-2-AsyncLogger[AsyncContext@71be98f5]-1 ERROR Unable to create Appender of type HushableMutableRandomAccess
Exception in thread "HiveServer2-Handler-Pool: Thread-470" java.lang.OutOfMemoryError: GC overhead limit exceeded
WARN: The method class org.apache.commons.logging.impl.SLF4JLogFactory#release() was invoked.
WARN: Please see http://www.slf4j.org/codes.html#release for an explanation.

大概意思就是我JVM的资源不足了，内存溢出了
这个就很奇怪，我就测试了几个数据库链接工具，还没上强度，也没上job，怎么就内存溢出了呢？
我查看了一下进程（看看是什么事务占用了资源），吓我一跳

每一次在测试的时候，都会产生一个链接会话，且一直没有被回收，且即便是我关闭了会话，这个会话进程依然存在，长此以往会话占用的资源就爆满了。再有新的链接想要链接进来，就会被refused。
这里和postgresql的底层设计有点关联，postgresql是一个多进程多线程类型的一个数据库，对于hive在安装的时候，其实内置的是derby数据库。这里建议采用其默认数据库，其兼容性会更好。目前行业主流仍然使用mysql作为其元数据库管理最多