PostgreSQL: Load testing throws error - Npgsql.PostgresException: '53300: sorry, too many clients already'

user_6285638 0
Viewed 1205

i have a simple code that tries to simulate a load test for my Postgres database

            List<Values> values = new List<Values>();

            for (int i = 0; i < 200; i++)
            {
                var mythread = new Thread(() =>
                {
                    DatabaseConnection db = new DatabaseConnection();
                    using (var conn = db.GetDatabaseConnection())
                    {
                        var cmd = new NpgsqlCommand();
                        cmd.Connection = conn;
                        cmd.CommandText = "SELECT val.id, val.name FROM val.myvalue val";
                        cmd.Prepare();
                        var reader = cmd.ExecuteReader();
                        try
                        {
                            while (reader.Read())
                            {
                                Values value = new Values();
                                value.Id = reader.GetInt64(0);
                                value.Name = reader.GetString(1);

                                values.Add(value);
                            }
                            reader.Close();
                        }
                        catch (Exception ex)
                        {
                            reader.Close();
                            var exception = ex;
                            throw exception;
                        }
                    }
                });

                mythread.Start();
            }

but when I run this I get the error Npgsql.PostgresException: '53300: sorry, too many clients already'

Database connection class

public class DatabaseConnection
{
    public NpgsqlConnection GetDatabaseConnection()
    {
        var connectionstring = "User ID=myuser;Password=mypassword;Server=localhost;Port=5432;Database=myvaluesdb;Pooling=True;Minimum Pool Size=10;Maximum Pool Size=100;Trust Server Certificate=true;Connection Idle Lifetime=300;keepalive=10;Timeout=60";
        NpgsqlConnection connection = null;

        try
        {
            connection = new NpgsqlConnection(connectionstring);
            connection.Open();
        }
        catch (Exception)
        {
            // try to reconnect
            connection = new NpgsqlConnection(connectionstring);
            connection.Open();
        }

        if (connection.State == ConnectionState.Open)
            Console.WriteLine("connection status = OPEN");
        else
        {
            var exception = new Exception("could not get DB connection");
            throw exception;
        }

        return connection;
    }
}

checking the max_connections shows 100
so my question is is this normal or is there something am doing wrong that needs to be corrected and if so how can i correct this
Thanks

postgresql c#
1 Answers

If the query you execute are short (i.e. take less than 60 seconds to complete), you shouldn't see this behavior.

When the maximum number of connections (max pool size) is reached and you ask for more, npgsql will wait up to timeout (60 seconds in your case, 15 by default) to return one. It won't establish more, but it will wait that another connection stops being used and is returned to the pool.

In you example, you launch simultaneously 200 threads. The first 100 should have no issue, then following 100 should wait that the first 100 ones return the connections to the pool and then can establish/use their connection.

So, either the query you are running are extremely long (more than 60 seconds), or you failed to close the connection when you are done. I don't see any close statements, so you are relying on the connection dispose, but this is custom code... and your custom dispose() must explicitly close (or dispose) the underlying connection.

user_76355690
Related