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

Symptom

The number of DBService connections exceeds the default maximum value (which can be obtained from the dbservice.database.max.connections parameter of DBService) due to heavy service traffic. As a result, MetaStore is abnormal and the error message "slots are reserved for non-replication superuser connections" is displayed.

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 the number of DBService connections to exceed the maximum limit, necessitating an adjustment to the maximum number of connections for DBService.

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. Increase the value of dbservice.database.max.connections parameter based on the site requirements.

   This parameter is a core configuration in the database service that sets the maximum number of concurrent connections, limiting the upper threshold of clients simultaneously connected to the DBService.

   Set this parameter properly based on the actual service requirements, in terms of the following:

   • Node resource configuration: The larger the memory and the more the CPU cores, the higher the maximum number of connections supported.
   • Service concurrency: Ensure that the allowed concurrent client requests in peak hours are greater than the actual concurrent requests to prevent services from being rejected.
   • Resource consumption of a single connection: If the service operations (such as complex queries and large transactions) of each database connection consume resources heavily, reduce the maximum number of connections. Conversely, increase the maximum number of connections if operations consume fewer resources.

3. Save the configuration and restart the affected services or instances.
4. If the fault persists, check the service code for any connection leaks.