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 19:11

Must be some messaging going on because when you write it properly, so the Form doesn't freeze, you get this in Rio after two prior executions to allow Tasks to queue and near 100% CPU usage:

No Pool: 43.528-7977
Pooling: 42.712-4339

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 := ckPooling.checked;
    if LConnection.Pooling then LPooling := 'Pooling: ' Else LPooling := 'No Pool: ';

    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;

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

Re: Connection pooling setup

Post by ertank » Wed 14 Aug 2019 22:46

I didn't understand why pooling is slower or same speed. It supposed to be faster for such a code.

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: 2874
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: 160
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: 2874
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: 91
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.

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

Re: Connection pooling setup

Post by oleg0k » Thu 28 May 2020 14:50

Hello,
It is good to see that the problem has been solved.
Thank you for the interest to our product.

wbr, Oleg
Devart Team

andrefm
Posts: 31
Joined: Wed 23 Oct 2013 10:02

Re: Connection pooling setup

Post by andrefm » Wed 24 Jun 2020 12:27

oleg0k wrote:
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
Hi Oleg,
Could you please let me know why it is not possible to have 2 connections pools to the same database?
For example if I use your example, set the MaxPoolSize = 4 and the MinPoolSize = 4 and duplicate the TTask.Run, I still see only 4 connections to my database and not 8 as I would expect (2 pools). I run my tests using MySQL 5.7.
I am building one application which I require multiple connection pools (could be same or other databases) and the idea is that one pool is to run some slow queries and another for more faster critical queries.
Could you please share one example how it should be done?
Thank you

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

Re: Connection pooling setup

Post by oleg0k » Wed 24 Jun 2020 18:58

Hello,

MinPoolSize and MaxPoolSize are properties for specifying the minimum and maximum number of CONNECTIONS in a connection pool. There can only be one pool, which may contain many connections.
See this page for more information on the TPoolingOptions members: https://www.devart.com/unidac/docs/deva ... embers.htm

wbr, Oleg
Devart Team

andrefm
Posts: 31
Joined: Wed 23 Oct 2013 10:02

Re: Connection pooling setup

Post by andrefm » Wed 24 Jun 2020 22:54

I understand the properties MinPoolSize and MaxPoolSize. I set those to confirm what you said that there can only be on pool. But could you please inform why there can be only ONE pool? I checked the whole documentation and I couldn't find anything information that you can only have ONE pool.
https://www.devart.com/unidac/docs/work_pooling.htm

If I create 2 separate threads with one pool in each, why this cannot happen Thx?
Is there any suggestion how I could have 2 or 3 connection pools to different databases in one application?
Last edited by andrefm on Sat 27 Jun 2020 13:25, edited 1 time in total.

andrefm
Posts: 31
Joined: Wed 23 Oct 2013 10:02

Re: Connection pooling setup

Post by andrefm » Thu 25 Jun 2020 01:05

Oleg, for me this is a bug. It should allow me to have multiple connection pools. Is there any reason why this cannot be accomplished?

Looking at your example. IMO it is not a good example to explain how a connection pool works as something does not make sense.
The first connection LConnection you simply connect to database, but there is no query running in this connection, it just stay idle. It is a wasted connection. Or can you explain better why you done the connection?
From what I could see, LLConnection is just grabbing the connection settings and the queries are using this connection. We can comment out the Lconnection (open and close) to avoid unnecessary connection.

Code: Select all

//      LConnection.Open;
//      LConnection.Close; // Some caching should happen !!!
//      LConnection.Open; // Open before timing starts

I tried the your example creating 2 pools to two different databases (SQL Server and MySQL) using MinPoolSize and MaxPoolSize = 5 for each connection.
It works but wrongly.

First, SQL Server opened ONLY 2 connections and MySQL only 1. This is very weird, but could be because I was expecting the amount of connections according to MinPoolSize, but later I noticed that MinPoolSize is to avoid freeing connection and not exactly that it would create the amount of connections in advance.
I added a "sleep(500)"" before closing the query, then I saw 5 connections at the first database and one connection at the other database.
The connections at the second database increased to a higher amount only when the first task completed. Therefore, it seems the amount of connections does not work properly with two connection pools. As you said, it seems that internally UNIDAC only accepts one pool at the time which is not good for me as I need 2 and more connection pools to different databases (could also be the same database).

Code: Select all

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 := 'SQL Server';
      LConnection.Server := '192.168.0.171';
      LConnection.port := 1433;
      LConnection.Database := 'sakila';
      LConnection.Username := 'tutorial';
      LConnection.Password := 'Tutorial.123!';
      LConnection.LoginPrompt := False;
      LConnection.PoolingOptions.MaxPoolSize := 5;
      LConnection.PoolingOptions.MinPoolSize := 5;
      LConnection.PoolingOptions.ConnectionLifetime := 60000; // 60 seconds
      LConnection.PoolingOptions.Validate := True;
      LConnection.Pooling := True;
      LConnection.SpecificOptions.Values['Provider'] := 'prDirect';
      LConnection.SpecificOptions.Values['ApplicationName'] := 'My application';
      //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 dbo.actor';
                for i := 0 to 10 do
                begin
                  LQuery.Open;
                  LQuery.Last;
                  LQuery.Close;
                end;
              finally
                FreeAndNil(LQuery);
              end;
            finally
              LLConnection.Close;
              FreeAndNil(LLConnection);
            end;
          end)];

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

  TTask.Run(
    procedure
    var
      LIndex: Integer;
      LStopWatch: TStopWatch;
      LTasks: TArray<ITask>;
      LConnection: TUniConnection;
    begin
      LConnection := TUniConnection.Create(nil);
      LConnection.ProviderName := 'MySQL';
      LConnection.Server := '192.168.0.171';
      LConnection.Database := 'sakila';
      LConnection.Username := 'tutorial';
      LConnection.Password := 'Tutorial.123!';
      LConnection.LoginPrompt := False;
      LConnection.PoolingOptions.MaxPoolSize := 10;
      LConnection.PoolingOptions.MinPoolSize := 10;
      LConnection.PoolingOptions.ConnectionLifetime := 5000; // 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 actor';
                for i := 0 to 10 do
                begin
                  LQuery.Open;
                  LQuery.Last;
                  LQuery.Close;
                end;
              finally
                FreeAndNil(LQuery);
              end;
            finally
              LLConnection.Close;
              FreeAndNil(LLConnection);
            end;
          end)];

      end;
      TTask.WaitForAll(LTasks);
      LConnection.Close;
      FreeAndNil(LConnection);
      LStopWatch.Stop;
      TThread.Synchronize(nil,
        procedure
        begin
          Memo1.Lines.Add('MYSQL - '+LPooling + LStopWatch.Elapsed.ToString());
          //Button1.Enabled := True;
          //Button1.Caption := 'Button1'
        end);
    end);
Last edited by andrefm on Sat 27 Jun 2020 13:32, edited 1 time in total.

Post Reply