Max Pool Size is being exceded

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
jamir.araujo
Posts: 22
Joined: Wed 13 Mar 2019 17:25

Max Pool Size is being exceded

Post by jamir.araujo » Wed 25 Aug 2021 12:19

Hello!

A team on my company is facing an issue where the connection parameter Max Pool Size is not being respected when there a too many locks.

The context where this behavior happens is too big, so i made a simple console application to reproduce it.

Code: Select all

class Program
    {
        static async Task Main(string[] args)
        {
            var connectionString = "Data Source=127.0.0.1;Port=5432;Database=MaxPoolSize;User ID=postgres;password=admin;Max Pool Size=5";

            PostgreSqlLicense.Validate(connectionString);

            var recordId = Guid.NewGuid();

            var connection = new PgSqlConnection(connectionString);
            await connection.OpenAsync();

            var command = connection.CreateCommand();
            command.CommandText = $"INSERT INTO public.\"Records\" (\"Id\", \"Text\") VALUES ('{recordId}'::uuid, 'Teste')";
            await command.ExecuteNonQueryAsync();

            await connection.CloseAsync();

            var tasks = new List<Task>();
            foreach (var operation in Enumerable.Range(1, 30))
            {
                tasks.Add(Task.Run(async () =>
                {
                    try
                    {
                        var connection = new PgSqlConnection(connectionString);
                        await connection.OpenAsync();

                        var transaction = connection.BeginTransaction();

                        var sqlText = $"UPDATE public.\"Records\" SET \"Text\" = 'text-{operation}' WHERE \"Id\" = '{recordId}'";

                        var command = new PgSqlCommand(sqlText, connection, transaction);

                        await command.ExecuteNonQueryAsync();

                        await command.DisposeAsync();
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine("Operation {0} failed", operation);
                        throw;
                    }
                }));
            }

            await Task.WhenAll(tasks);

            Console.WriteLine("Done!");
        }
    }
In this application I set Max Pool Size to 5 and really forced the lock to occur by opening connections and transactions in parallel, without committing the transactions or closing the connections.

The table I'n using is very simple. It's table named "Records" with Primary Key "Id" type uuid and a column "Text" type text.

The application starts with 6 connections (one of them being the connection that was opened to validate the license), which already exceeds the configured limit. After one minute it goes to 10, after another minute 14, then 18, then 19, then all connections time out and the line Task.WhenAll (tasks) throws.

The connections timeout is expected, but exceeding Max Pool Size was not.

It is possible to see when the Max Pool Size has been reached, the code stops on the OpenAsync method, but than after a minute and some connections timeouts, it allows more connections.

DmitryGm
Devart Team
Posts: 121
Joined: Fri 11 Dec 2020 10:27

Re: Max Pool Size is being exceded

Post by DmitryGm » Thu 26 Aug 2021 14:56

Max Pool Size limit doesn't restrict creation of connection objects. But when the limit is reached, the Open() method waits until an existing connection is closed or until timeout.

This behavior is demonstrated by your code example - this is the designed behavior.

jamir.araujo
Posts: 22
Joined: Wed 13 Mar 2019 17:25

Re: Max Pool Size is being exceded

Post by jamir.araujo » Fri 27 Aug 2021 17:02

Yes, the code is stopping at the Open() method, but the connections are opened and exceeding the max pool size.

You can see this by running the code and executing the following query on pgadmin:

Code: Select all

SELECT * FROM pg_stat_activity where datname = 'MaxPoolSize'
The code will stop at the Open() Method and eventually will throw a timeout exception. But code connection are being open and are not released when the timeout occur.

The connection are not being released when I manually call the Close or Dispose. They are not released even when I call the ClearAllPools method. The only thing that releases the connections is closing the application.

DmitryGm
Devart Team
Posts: 121
Joined: Fri 11 Dec 2020 10:27

Re: Max Pool Size is being exceded

Post by DmitryGm » Mon 30 Aug 2021 14:47

Thank you for your report. We are investigating this case with PostgreSQL connections and shall inform you as soon as we have any results.

DmitryGm
Devart Team
Posts: 121
Joined: Fri 11 Dec 2020 10:27

Re: Max Pool Size is being exceded

Post by DmitryGm » Wed 22 Sep 2021 08:20

This is due to a "forgot" transaction.

Code: Select all

var transaction = connection.BeginTransaction();
In that case, the Application cannot keep track of the release connections and allocation of new connections.

Note that the transaction class implements IDisposable. You should wrap the transaction into a "using" like that:

Code: Select all

                        
using (var transaction = connection.BeginTransaction())
{
    var sqlText = $"UPDATE public.\"Records\" SET \"Text\" = 'text-{operation}' WHERE \"Id\" = '{recordId}'";
    var command = new PgSqlCommand(sqlText, connection, transaction);
    await command.ExecuteNonQueryAsync();
    await command.DisposeAsync();
}

In this case, everything works correctly.

jamir.araujo
Posts: 22
Joined: Wed 13 Mar 2019 17:25

Re: Max Pool Size is being exceded

Post by jamir.araujo » Mon 11 Oct 2021 20:33

Yes, my code lets the transaction and connection open intentionaly to generate locks.

I did this becousa we were seeing a similar issue on some application of my company. More connections open than we should have, and many of them locked by transactions (possibly due to some dead lock)

I executed the same code using the Npgsql driver and it did not exceded the max pool size.

DmitryGm
Devart Team
Posts: 121
Joined: Fri 11 Dec 2020 10:27

Re: Max Pool Size is being exceded

Post by DmitryGm » Tue 12 Oct 2021 10:56

Yes, dotConnect for PostgreSQL requires more correct code here. This results in better performance. Therefore, this behavior of dotConnect for PostgreSQL will not be changed.

jamir.araujo
Posts: 22
Joined: Wed 13 Mar 2019 17:25

Re: Max Pool Size is being exceded

Post by jamir.araujo » Wed 13 Oct 2021 12:17

Fair enough. Could you at least inform of this behavior in the Max Pool Size documentation?

DmitryGm
Devart Team
Posts: 121
Joined: Fri 11 Dec 2020 10:27

Re: Max Pool Size is being exceded

Post by DmitryGm » Mon 18 Oct 2021 10:38

Thank you for your report. Yes indeed, in the case of forgot unclosed transactions, compliance with Max Pool Size is not guaranteed. It may be reflected in the documentation soon.

lilycollins
Posts: 2
Joined: Thu 28 Apr 2022 07:32

Re: Max Pool Size is being exceded

Post by lilycollins » Tue 19 Jul 2022 01:20

Thanks for your post! In the event of forgetting undisclosed transactions, compliance with Maximum Group Size is not guaranteed. venge io taming io

JessicaReynolds
Posts: 3
Joined: Mon 04 Jul 2022 02:47

Re: Max Pool Size is being exceded

Post by JessicaReynolds » Thu 21 Jul 2022 09:31

Despite our best efforts, many of our connections are more open than they should bebubble shooter(probably due to some deadlock).

Post Reply