Connection pooling setup

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
FredS
Posts: 208
Joined: Mon 10 Nov 2014 17:52

Re: Connection pooling setup

Post by FredS » Wed 14 Aug 2019 22:59

No, when I turn on the debug in my altered Pooling unit I see we never go above 86 connections.

Active Tasks, CPU, method
----------------------
Active: 86 AvgCPU: 75% for No Pool
Active: 52 AvgCPU: 64% for Pooling

with these times:
Pooling: 38.914-2356
No Pool: 32.304-0114

Simply not a good testing method. Pooling should allow reconnecting to the server even when the server is too busy to initiate a new connection.
Testing with such a small load ends up testing Threading instead.

andrea.magni
Posts: 4
Joined: Wed 14 Aug 2019 06:36
Location: Italy
Contact:

Re: Connection pooling setup

Post by andrea.magni » Thu 15 Aug 2019 07:58

Thanks all for the contribution, some considerations:

1) I am pretty sure connection pooling working in my example would boost performances since the example shows 1000 tasks (on my VM run by 3 threads) connecting and performing a query against the DB. Re using connections is obviously a great chance of optimization...

2) To prove it, I've implemented a rudimentary connection pooling over my example: https://pastebin.com/PrR4g01t You simply need a TButton and a TLabel on a form and use my code to see how it runs. In my case, I am going from 40 seconds to 1.5 seconds (!). Beware: the pooling implemented is rude: 100 connections setup at form creation, however only 3 of them are actually used (because System.Threading is using 3 threads to run the 1000 tasks, results may differ on different systems of course).

Let me know your thoughts please. On the facebook group somebody is even arguing connection pooling client side (meaning not on the DBMS side) exists... :-(

ViktorV
Devart Team
Posts: 2788
Joined: Wed 30 Jul 2014 07:16

Re: Connection pooling setup

Post by ViktorV » Thu 15 Aug 2019 08:31

Database connection pooling is a data access method used to keep database connections open to eliminate the need to reopen connections, which is a resource-intensive operation. Since you're connecting to a database on a localhost where it doesn't require considerable time to connect, you don't see much improvement in performance.
You can find more information about how pooling works in our documentation: https://devart.com/unidac/docs/work_pooling.htm
Also you may find useful this guide to improving performance when using our components: https://devart.com/unidac/docs/increasi ... rmance.htm

ertank
Posts: 158
Joined: Wed 13 Jan 2016 16:00

Re: Connection pooling setup

Post by ertank » Thu 15 Aug 2019 10:34

ViktorV wrote:
Thu 15 Aug 2019 08:31
Since you're connecting to a database on a localhost where it doesn't require considerable time to connect, you don't see much improvement in performance.
From 40 seconds to 1.5 seconds is actually a big improvement.

FredS
Posts: 208
Joined: Mon 10 Nov 2014 17:52

Re: Connection pooling setup EDIT

Post by FredS » Thu 15 Aug 2019 14:33

andrea.magni wrote:
Thu 15 Aug 2019 07:58
Let me know your thoughts please.
After turning off pooling, since pooling is now manually implemented, a network run takes just over 2 seconds using 5 connection in Rio.

Testing your latest with MSSQL takes just over 2 seconds.

Here are the results using MSSQL with yesterday's testing:

Code: Select all

No Pool: 55.501-0315
No Pool: 42.492-1737
Pooling: 06.420-9669
Pooling: 01.143-7127
Pooling: 00.986-3058
Pooling: 01.022-3872
This would suggest something is VERY wrong with FB Pooling..
Test was done using Docker MSSQL 2017 (14.0.3048.4) and the Demo db.

..and Maria DB

Code: Select all

No Pool: 45.969-6065
No Pool: 46.329-7231
Pooling: 00.885-9607
Pooling: 00.527-3404
Pooling: 00.615-0686

ViktorV
Devart Team
Posts: 2788
Joined: Wed 30 Jul 2014 07:16

Re: Connection pooling setup

Post by ViktorV » Fri 16 Aug 2019 13:19

We've improved performance of our product when using pooling for InterBase provider. The modification will be included in the next UniDAC build.

oleg0k
Devart Team
Posts: 43
Joined: Wed 11 Mar 2020 08:28

Re: Connection pooling setup

Post by oleg0k » Mon 18 May 2020 20:55

Hello, try this code:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  LPooling : string;
begin
  Button1.Enabled := False;
  Button1.Caption := 'Running..';
  TTask.Run( procedure
  var
    LIndex: Integer;
    LStopWatch: TStopWatch;
    LTasks: TArray<ITask>;
    LConnection : TUniConnection;
  begin
    LConnection := TUniConnection.Create(nil);
    LConnection.ProviderName := 'InterBase';
    LConnection.Server       := 'localhost';
    LConnection.Database     := 'employee';
    LConnection.Username     := 'SYSDBA';
    LConnection.Password     := 'masterkey';
    LConnection.LoginPrompt  := False;
//    LConnection.SpecificOptions.Values['InterBase.ClientLibrary'] := GetEmbeddedFbDLL;
    LConnection.PoolingOptions.MaxPoolSize := 100;
    LConnection.PoolingOptions.MinPoolSize := 2;
    LConnection.PoolingOptions.ConnectionLifetime := 60000; // 60 seconds
    LConnection.PoolingOptions.Validate := True;
    LConnection.Pooling := True;
    LConnection.Open;  LConnection.Close;  // Some caching should happen !!!   
    LConnection.Open; // Open before timing starts
    LStopWatch := TStopWatch.StartNew;
    LTasks := [];
    for LIndex := 1 to 1000 do
    begin
      LTasks := LTasks + [
        TTask.Run(
          procedure
          var
            LLConnection: TUniConnection;
            LQuery: TUniQuery;
            i : integer;
          begin
            LLConnection := TUniConnection.Create(nil, LConnection.ConnectString);
            LLConnection.Open;
            try
              LQuery := TUniQuery.Create(nil);
              try
                LQuery.Connection := LLConnection;
                LQuery.SQL.Text := 'select * from EMPLOYEE';
                for i := 0 to 10 do begin
                  LQuery.Open;
                  LQuery.Last;
                  LQuery.Close;
                end;
              finally
                FreeAndNil(LQuery);
              end;
            finally
              FreeAndNil(LLConnection);
            end;
          end
        )
      ];

    end;

    TTask.WaitForAll(LTasks);
    LConnection.Free;
    LStopWatch.Stop;
    TThread.Synchronize(nil, procedure begin
        Memo1.Lines.Add(LPooling + LStopWatch.Elapsed.ToString());
        Button1.Enabled := True;
        Button1.Caption := 'Button1'
    end);
  end);

end;
wbr, Oleg
Devart Team

abak
Posts: 25
Joined: Sat 18 Oct 2014 18:42

Re: Connection pooling setup

Post by abak » Fri 22 May 2020 12:10

Thank you Oleg.

Post Reply