I'm getting the Too many connections issue in multi-tenant DB

[purna chand]

Im getting the Too many connections issue in multi-tenant DB, when im hitting to one request it is wotking fine with any number of times,
but when im connecting from client side and sending multiple requests at a time am getting too many connections issue.

Thanks in advance

Caused by: java.sql.SQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:207)
at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:136)
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:369)
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:198)
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:467)
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:541)
at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112)
at com.enterprise.portal.configurations.SchemaPerTenantConnectionProvider.getConnection(SchemaPerTenantConnectionProvider.java:74)
at org.hibernate.internal.ContextualJdbcConnectionAccess.obtainConnection(ContextualJdbcConnectionAccess.java:43)
at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:106)
... 68 common frames omitted

Here is my sample code.

@Component
public class SchemaPerTenantConnectionProvider implements MultiTenantConnectionProvider {
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	private AtomicReference<Map<String, HikariDataSource>> dataSources = new AtomicReference<>(new HashMap<>());

	private DataSource dataSource;

	@Autowired
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Value("${multitenant.defaultTenant}")	
	private String defaultTenant;

	@Override
	public Connection getAnyConnection() throws SQLException {
		return dataSource.getConnection();
	}

	@Override
	public void releaseAnyConnection(Connection ignore) throws SQLException {
		Connection connection = dataSource.getConnection();
		if (connection != null) {
			connection.close();
		}
	}

	@Override
	public Connection getConnection(String tenantIdentifier) throws SQLException {
		if (tenantIdentifier.equalsIgnoreCase(defaultTenant)) {
			return dataSource.getConnection();
		} else {
		
		
				HikariDataSource dataSource = dataSources.get().get("passing id here from common DB");
				if (dataSource == null) {
					HikariDataSource newDataSource = new HikariDataSource();
					newDataSource.setPoolName("passing id here from common DB");
					newDataSource.setUsername("passing DB user name here from common DB");
					newDataSource.setPassword("passing DB password here from common DB");
					newDataSource.setJdbcUrl("passing DB URL here from common DB");
					newDataSource.setDriverClassName(Driver.class.getCanonicalName());
					// Minimum Idle time for the connection pool to help release resources to avoid
					// leaks
					newDataSource.setMinimumIdle(100);
					// Cache the data source
					dataSources.get().put("passing id here from common DB", newDataSource);
					return newDataSource.getConnection();
				}
				return dataSource.getConnection();
			

			throw new IllegalArgumentException("Invalid Tenant Key " + tenantIdentifier);
		}
	}

	private void releaseConnectionPool(String poolName) {
		HikariDataSource dataSource = dataSources.get().get(poolName);
		if (dataSource != null) {
			// Close the Hikari-Pool
			dataSource.close();
			// Remove datasource from cache
			dataSources.get().remove(poolName);
		}
	}

	@Override
	public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {
		connection.close();
		releaseConnectionPool("passing id here from common DB");
	}

[Tim Holloway]

I normally just configure all that stuff in the applicationContext.xml and skip the java code.

There are 2 likely causes.

1. You've got so many concurrent users that the pool is being exhausted. You need to enlarge the pool and possibly the number of connection channels available on both client and database server machines.

2. OR you're not releasing connections fast enough, and that includes possibly leaking them. If you're using Spring Data properly, you shouldn't be leaking connections - that's one of the reasons why I use Spring Data, in fact. But if your transactions are taking a long time to run, then even without leakage, you can run out. In which case, first consider whether you can optimize the code running while in a transaction and if at all possible move the long-running code outside of the transaction environment. If that doesn't work, see item #1, above.

[Dave Tolls]

Could be wrong here, but that error looks like it is coming from the database, so it would be the database running out of available connections and not the connection pool.
So there's a mismatch between the pool setup and the database, such that the pool is being allowed to create more connections than the database is configured to handle.

[Tim Holloway]

Dave Tolls wrote:Could be wrong here, but that error looks like it is coming from the database, so it would be the database running out of available connections and not the connection pool.
So there's a mismatch between the pool setup and the database, such that the pool is being allowed to create more connections than the database is configured to handle.

That's a definite possibility. Normally, the process of installing a DBMS server involved bumping up certain performance parameters on the DBMS server host machine, including max allowable connections, but it's a good idea to check.

You can run out of server connections if you have a very busy database server, but if all the connections are coming from one client machine, that can indicate that the client application(s) are not efficiently releasing connections. And therefore not only would you be abusing the database server, you'd also be having an overly-large client connection pool.