SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for AWS Amazon Web Services, Redshift, RDS, AWS Lambda Functions, S3 Buckets, VPC, EC2, IAM

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

Redshift database connection limit 498 exceeded for non-superusers
Amazon Redshift database connection error

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;
SQL Query for Redshift database connections

For example, I had such figures on one of Amazon Redshift cluster.

Redshift database connection count

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;
Amazon Redshift database connections

The outcome of the above SQL query shows that especially one of the database users has an excessive number of database connections open.

Redshift database connection for each user

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.

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.



AWS


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.