Devart Forums

Discussion forums for open issues and questions concerning database tools, data access components and developer tools from Devart
https://forums.devart.com/

Connection pooling setup

https://forums.devart.com/viewtopic.php?f=28&t=39169

Page 2 of 3

Re: Connection pooling setup

Posted: Wed 14 Aug 2019 19:11
by FredS
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;

Re: Connection pooling setup

Posted: Wed 14 Aug 2019 22:46
by ertank
I didn't understand why pooling is slower or same speed. It supposed to be faster for such a code.

Re: Connection pooling setup

Posted: Wed 14 Aug 2019 22:59
by FredS
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.

Re: Connection pooling setup

Posted: Thu 15 Aug 2019 07:58
by andrea.magni
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... :-(

Re: Connection pooling setup

Posted: Thu 15 Aug 2019 08:31
by ViktorV
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

Re: Connection pooling setup

Posted: Thu 15 Aug 2019 10:34
by ertank
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.

Re: Connection pooling setup EDIT

Posted: Thu 15 Aug 2019 14:33
by FredS
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

Re: Connection pooling setup

Posted: Fri 16 Aug 2019 13:19
by ViktorV
We've improved performance of our product when using pooling for InterBase provider. The modification will be included in the next UniDAC build.

Re: Connection pooling setup

Posted: Mon 18 May 2020 20:55
by oleg0k
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

Re: Connection pooling setup

Posted: Fri 22 May 2020 12:10
by abak
Thank you Oleg.

Re: Connection pooling setup

Posted: Thu 28 May 2020 14:50
by oleg0k
Hello,
It is good to see that the problem has been solved.
Thank you for the interest to our product.

wbr, Oleg
Devart Team

Re: Connection pooling setup

Posted: Wed 24 Jun 2020 12:27
by andrefm
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

Re: Connection pooling setup

Posted: Wed 24 Jun 2020 18:58
by oleg0k
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

Re: Connection pooling setup

Posted: Wed 24 Jun 2020 22:54
by andrefm
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?

Re: Connection pooling setup

Posted: Thu 25 Jun 2020 01:05
by andrefm
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);
All times are UTC
Page 2 of 3
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/