Amazon Redshift Database connection limit "498" exceeded for non-superusers
While accessing to Amazon Redshift database from a Microsoft Power BI report, connection limit "498" exceeded for non-superusers error occurred preventing a successful Redshift database access failing the dashboard reports. Of course when the number of database connection limits are exceeded, not only Power BI but all other applications trying to connect Amazon Redshift cluster will fail preventing you solutions to work as expected.
Here is the error message displayed on Power BI IDE
ODBC: ERROR [53300] [Amazon][Amazon Redshift] (10) Error occurred while trying to connect: [SQLState 53300] FATAL: connection limit "498" exceeded for non-superusers
Each Amazon Redshift database has a concurrent connection limit defined while the database is created. By default this connection limit is "UNLIMITED". For Amazon Redshift DWH administrators it is possible to change the connection limit by using ALTER DATABASE SQL statement after the database is created.
In addition to the database concurrent connection limit, for each database user it is also possible to define a concurrent database connection limit.
When such a problem occurs, SQL developers can execute following SQL SELECT command on STV_SESSIONS to display the number of open database connections on on each Redshift database.
SELECT db_name, COUNT(*)
FROM stv_sessions
GROUP BY db_name;
For example, I had such figures on one of Amazon Redshift cluster.
As seen above, on one of the Redshift databases the database connections count is nearly close to 500 connections.
Furthermore database developers can use below SQL query for open database connections count on each database for each user.
SELECT
user_name, db_name, COUNT(*)
FROM stv_sessions
GROUP BY
user_name, db_name
ORDER BY
user_name, db_name;
The outcome of the above SQL query shows that especially one of the database users has an excessive number of database connections open.
In fact, if database users are programmatically accessing to databases programmatically and if the opened database connections are not closed properly these connections are left idle and in open state. This is a known issue and valid for all database platforms. Garbage collectors are used to remove unused objects from memory. But when it comes to database connections there is not a solution which is so effective to drop unused database connections.
When I checked for the Amazon Redshift error connection limit "498" exceeded for non-superusers, I see that a few AWS Lambda functions are establishing database connections to Redshift databases but not closing the database connection after they finish their tasks.
So contact application developers to close database connections opened to Amazon Redshift database as soon as possible.
An appropriate method call for your programming language and library used similar to below should be called.
conn.close()
Of course, a TRY-CATCH-FINALLY block can be used for closing the database connections even in case of an exception occurs.
To be sure that the number of database connections for a specific Redshift database is set to maximum value, following ALTER DATABASE SQL command can be used.
ALTER DATABASE dbname CONNECTION LIMIT UNLIMITED;
Even there is not a limitation in above SQL command for the number of connections, executing SQL statement will start dropping unused database connections.
To immediately start reducing the number of open connections and dropping excessive database connections to a specific Redshift database user, below ALTER USER SQL command can be used by data warehouse adminsitrators.
ALTER USER "dbusername" CONNECTION LIMIT 20;
When Redshift data warehouse administrators execute above ALTER USER command with a number of connections limit, existing open connections will reduce step by step within minutes.
After status of the Redshift database connection is normal again, the modified user settings can be reset for that specific user as follows:
ALTER USER "dbusername" CONNECTION LIMIT UNLIMITED;
As the error message indicates, the open connections limit is applied only to non-superusers on Amazon Redshift database. Superusers will not be affected from the limits.