Hive MetaStore Exception Occurs When the Number of DBService Connections Exceeds the Upper Limit

Symptom

By default, the maximum number of connections to DBService is 300. If the number of connections is greater than 300 due to heavy traffic, an exception occurs in MetaStore and error "slots are reserved for non-replication superuser connections" is reported.

2018-04-26 14:58:55,657 | ERROR | BoneCP-pool-watch-thread | Failed to acquire connection to jdbc:postgresql://10.*.*.*:20051/hivemeta?socketTimeout=60. Sleeping for 1000 ms. Attempts left: 9 | com.jolbox.bonecp.BoneCP.obtainInternalConnection(BoneCP.java:292)
org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
	at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:643)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:184)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
	at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:124)
	at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:28)
	at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:20)
	at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:30)
	at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:22)
	at org.postgresql.Driver.makeConnection(Driver.java:392)
	at org.postgresql.Driver.connect(Driver.java:266)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:208)
	at com.jolbox.bonecp.BoneCP.obtainRawInternalConnection(BoneCP.java:361)
	at com.jolbox.bonecp.BoneCP.obtainInternalConnection(BoneCP.java:269)
	at com.jolbox.bonecp.ConnectionHandle.<init>(ConnectionHandle.java:242)
	at com.jolbox.bonecp.PoolWatchThread.fillConnections(PoolWatchThread.java:115)
	at com.jolbox.bonecp.PoolWatchThread.run(PoolWatchThread.java:82)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:745)

Cause Analysis

Heavy service traffic causes more than 300 connections to DBService, and the maximum number of connections to DBService needs to be increased.

Solution

1. Go to the DBService configuration page.
   • For versions earlier than MRS 3.x: Click the cluster name on the MRS console, choose Components > DBService > Service Configuration, and select All from the Basic drop-down list.
     

     If the Components tab is unavailable, complete IAM user synchronization first. (On the Dashboard page, click Synchronize on the right side of IAM User Sync to synchronize IAM users.)

   • For MRS 3.x or later: Log in to FusionInsight Manager and choose Cluster > Services > DBService > Configurations > All Configurations.

2. Search for dbservice.database.max.connections and set it to a proper value not greater than 1000.
3. Save the configuration and restart the affected services or instances.
4. If the fault persists, check the service code for any connection leaks.