Page 1 of 3
Connection pooling setup
Posted: Wed 14 Aug 2019 06:43
by andrea.magni
Hi, this is my first post on this forum.
I am approaching UniDAC so maybe I am missing something basic but I cannot make connection pooling to work.
Here is my code:
https://pastebin.com/7ZiLVkYJ
Anyone could please point me out why it is not working?
My environment:
- Win10 64bit (VM)
- Delphi 10.3.2
- UniDAC8.0.1
- Firebird 3 (32bit)
Thanks in advance
Re: Connection pooling setup
Posted: Wed 14 Aug 2019 08:44
by mika
Hi,
I have been using with ODBC without problems
Code: Select all
function CreateConnection(const ADataBaseName: string);
begin
Result := TUniConnection.Create( nil );
Result.LoginPrompt := false;
Result.ProviderName := 'ODBC';
Result.Server := ADataBaseName;
Result.Pooling := true;
Result.Connect;
end;
I didn't see connect in your code..
Re: Connection pooling setup
Posted: Wed 14 Aug 2019 08:52
by andrea.magni
I've just tried, thanks. Nothing changed unfortunately...
Other ideas?
Sincerely,
Andrea
Re: Connection pooling setup
Posted: Wed 14 Aug 2019 10:18
by ViktorV
Please describe in detail what is not working as expected in the pooling.
Re: Connection pooling setup
Posted: Wed 14 Aug 2019 12:19
by andrea.magni
Hi, thanks for chiming in.
I would expect to see a performance boost but I am seeing same performance whether Pooling is set to True or False.
My timing is around 40 seconds. In a similar scenario I would expect a 10x boost at least... Or at least some difference changing Pooling value... What am I missing?
Thanks a lot
Andrea
Re: Connection pooling setup
Posted: Wed 14 Aug 2019 15:27
by FredS
Interesting test, made some modifications (see code below) but here are my results:
No Pool: 42.044-4221
Pooling: 43.957-2788
Code: Select all
procedure TForm1.Button1Click(Sender: TObject);
var
LIndex: Integer;
LStopWatch: TStopWatch;
LTasks: TArray<ITask>;
LConnection : TUniConnection;
LPooling : string;
begin
Button1.Enabled := False;
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);
LStopWatch.Stop;
Memo1.Lines.Add(LPooling + LStopWatch.Elapsed.ToString(tssDebug));
LConnection.Free;
Button1.Enabled := True;
end;
Re: Connection pooling setup
Posted: Wed 14 Aug 2019 15:36
by ertank
FredS wrote: ↑Wed 14 Aug 2019 15:27
Interesting test, made some modifications (see code below) but here are my results:
Any chance you share complete test project? Thanks.
Re: Connection pooling setup
Posted: Wed 14 Aug 2019 15:38
by FredS
Code: Select all
object Form1: TForm1
Left = 0
Top = 0
Caption = 'Form1'
ClientHeight = 299
ClientWidth = 635
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = []
OldCreateOrder = False
PixelsPerInch = 96
TextHeight = 13
object Button1: TButton
Left = 80
Top = 72
Width = 201
Height = 25
Caption = 'Button1'
TabOrder = 0
OnClick = Button1Click
end
object ckPooling: TCheckBox
Left = 320
Top = 76
Width = 97
Height = 17
Caption = 'ckPooling'
TabOrder = 1
end
object Memo1: TMemo
Left = 80
Top = 120
Width = 201
Height = 89
TabOrder = 2
end
end
Against a networked Docker Server:
No Pool: 01:12.378-1402
Pooling: 01:21.227-0238
Re: Connection pooling setup
Posted: Wed 14 Aug 2019 16:09
by ertank
I use Delphi 10.3.2, UniDAC 8.0.1, target is Win32 executable.
My test is some what different. Here is code:
Code: Select all
unit Unit2;
interface
uses
Winapi.Windows,
Winapi.Messages,
System.SysUtils,
System.Variants,
System.Classes,
Vcl.Graphics,
Vcl.Controls,
Vcl.Forms,
Vcl.Dialogs,
Vcl.StdCtrls,
DASQLMonitor,
UniSQLMonitor;
type
TForm2 = class(TForm)
Button1: TButton;
UniSQLMonitor1: TUniSQLMonitor;
Button2: TButton;
Button3: TButton;
Memo1: TMemo;
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure Button3Click(Sender: TObject);
procedure FormCreate(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form2: TForm2;
implementation
{$R *.dfm}
uses
Uni,
Diagnostics,
Threading,
InterbaseUniProvider,
System.TimeSpan;
procedure SetupPooledConnection(const AUniConn: TUniConnection);
begin
AUniConn.ProviderName := 'InterBase';
AUniConn.Server :='localhost';
AUniConn.Database := 'C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB';
AUniConn.Username := 'SYSDBA';
AUniConn.Password := 'masterkey';
AUniConn.SpecificOptions.Values['ClientLibrary'] := 'C:\Program Files\Firebird\Firebird_2_5\WOW64\fbclient.dll';
AUniConn.LoginPrompt := False;
AUniConn.PoolingOptions.MaxPoolSize := 100;
AUniConn.PoolingOptions.MinPoolSize := 2;
AUniConn.PoolingOptions.ConnectionLifetime := 60000; // 60 seconds
AUniConn.PoolingOptions.Validate := True;
AUniConn.Pooling := True;
end;
procedure SetupRegularConnection(const AUniConn: TUniConnection);
begin
AUniConn.ProviderName := 'InterBase';
AUniConn.Server :='localhost';
AUniConn.Database := 'C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB';
AUniConn.Username := 'SYSDBA';
AUniConn.Password := 'masterkey';
AUniConn.SpecificOptions.Values['ClientLibrary'] := 'C:\Program Files\Firebird\Firebird_2_5\WOW64\fbclient.dll';
AUniConn.LoginPrompt := False;
AUniConn.Pooling := False;
end;
procedure TForm2.Button1Click(Sender: TObject);
var
LIndex: Integer;
LStopWatch: TStopWatch;
LTasks: TArray<ITask>;
begin
LStopWatch := TStopWatch.StartNew;
LTasks := [];
for LIndex := 1 to 1000 do
begin
LTasks := LTasks + [
TTask.Run(
procedure
var
LConnection: TUniConnection;
LQuery: TUniQuery;
begin
LConnection := TUniConnection.Create(nil);
try
SetupPooledConnection(LConnection);
LQuery := TUniQuery.Create(nil);
try
LQuery.Connection := LConnection;
LQuery.SQL.Text := 'select * from EMPLOYEE';
LQuery.Open;
LQuery.Last;
finally
FreeAndNil(LQuery);
end;
finally
FreeAndNil(LConnection);
end;
end
)
];
end;
TTask.WaitForAll(LTasks);
LStopWatch.Stop;
Button1.Caption := LStopWatch.ElapsedMilliseconds.ToString;
end;
procedure TForm2.Button2Click(Sender: TObject);
var
LIndex: Integer;
LConnection: TUniConnection;
LQuery: TUniQuery;
LStopWatch: TStopWatch;
begin
LStopWatch := TStopWatch.StartNew;
for LIndex := 1 to 1000 do
begin
LConnection := TUniConnection.Create(nil);
try
SetupRegularConnection(LConnection);
LQuery := TUniQuery.Create(nil);
try
LQuery.Connection := LConnection;
LQuery.SQL.Text := 'select * from EMPLOYEE';
LQuery.Open;
LQuery.Last;
finally
FreeAndNil(LQuery);
end;
finally
FreeAndNil(LConnection);
end;
end;
Button2.Caption := LStopWatch.ElapsedMilliseconds.ToString;
end;
procedure TForm2.Button3Click(Sender: TObject);
var
LIndex: Integer;
LStopWatch: TStopWatch;
LTasks: TArray<ITask>;
LConnection : TUniConnection;
LPooling : string;
begin
Button1.Enabled := False;
LConnection := TUniConnection.Create(nil);
LConnection.ProviderName := 'InterBase';
LConnection.Server :='localhost';
LConnection.Database := 'C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB';
LConnection.Username := 'SYSDBA';
LConnection.Password := 'masterkey';
LConnection.LoginPrompt := False;
LConnection.SpecificOptions.Values['ClientLibrary'] := 'C:\Program Files\Firebird\Firebird_2_5\WOW64\fbclient.dll';
LConnection.PoolingOptions.MaxPoolSize := 100;
LConnection.PoolingOptions.MinPoolSize := 2;
LConnection.PoolingOptions.ConnectionLifetime := 60000; // 60 seconds
LConnection.PoolingOptions.Validate := True;
LConnection.Pooling := True;
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);
LStopWatch.Stop;
Memo1.Lines.Add(LPooling + LStopWatch.Elapsed.ToString());
LConnection.Free;
Button1.Enabled := True;
end;
procedure TForm2.FormCreate(Sender: TObject);
begin
Memo1.Clear();
end;
end.
Here is DFM:
Code: Select all
object Form2: TForm2
Left = 0
Top = 0
Caption = 'Form2'
ClientHeight = 289
ClientWidth = 554
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = []
OldCreateOrder = False
OnCreate = FormCreate
DesignSize = (
554
289)
PixelsPerInch = 96
TextHeight = 13
object Button1: TButton
Left = 8
Top = 8
Width = 75
Height = 25
Caption = 'Pooled'
TabOrder = 0
OnClick = Button1Click
end
object Button2: TButton
Left = 89
Top = 8
Width = 100
Height = 25
Caption = 'Direct Connection'
TabOrder = 1
OnClick = Button2Click
end
object Button3: TButton
Left = 195
Top = 8
Width = 150
Height = 25
Caption = 'Alternative with pooling'
TabOrder = 2
OnClick = Button3Click
end
object Memo1: TMemo
Left = 8
Top = 39
Width = 538
Height = 242
Anchors = [akLeft, akTop, akRight, akBottom]
Lines.Strings = (
'Memo1')
ScrollBars = ssBoth
TabOrder = 3
WordWrap = False
end
object UniSQLMonitor1: TUniSQLMonitor
Left = 336
Top = 80
end
end
I run it on a local FirebirdSQL 2.5 server. Click order: "Direct Connection", "Pooled", Alternative with pooling" results as
Direct Connection: 18728ms
Pooled: 5043ms
Alternative with pooling: 4.8179159 seconds
If I start testing clicking one of the pooled buttons then Direct Connection is way faster like 9017ms which makes me think that even we specify no pool, UniDAC uses it internally for a reason. Though, Direct Connection is still slow like 19-21 seconds even if I click it more than 60 seconds after pooled ones. As connect timeout for pooled is over, UniDAC is seemingly terminating pool connections.
Re: Connection pooling setup
Posted: Wed 14 Aug 2019 16:23
by FredS
My first test was against FB 3.05, the Docker one is 3.04, both in Rio.
This one is using Berlin against the same Docker server.
In Berlin I tweaked System.Threading to allocate new Tasks faster:
No Pool: 28.232-0294
Pooling: 01:20.369-7462
EDIT:
I should add that I added the extra loop because I know the standard System.Threading unit is too slow for short tests like this.
Re: Connection pooling setup
Posted: Wed 14 Aug 2019 16:38
by ertank
FredS wrote: ↑Wed 14 Aug 2019 16:23
No Pool: 28.232-0294
Pooling: 01:20.369-7462
How should we read above figures? Like for no pool: 28 seconds, 232 ms?
Re: Connection pooling setup
Posted: Wed 14 Aug 2019 16:45
by FredS
Sorry, one of my internal helpers:
tssDebug = [d.][hh:][nn:]ss.zzz-tttt
or 1 minute 20 seconds with Pooling
Re: Connection pooling setup
Posted: Wed 14 Aug 2019 16:56
by ertank
FredS wrote: ↑Wed 14 Aug 2019 16:45
Sorry, one of my internal helpers:
tssDebug = [d.][hh:][nn:]ss.zzz-tttt
or 1 minute 20 seconds with Pooling
Your code runs faster if not pooled. Strange...
Re: Connection pooling setup
Posted: Wed 14 Aug 2019 17:32
by FredS
Same test using Berlin against a fresh Docker FB 2.5.9 Super Server:
No Pool: 29.147-8424
Pooling: 01:12.745-0770
NOTE: Used FB Client 3.05
Rio against FB 2.5.9 ss:
No Pool: 52.480-7969
Pooling: 01:10.386-6314
No Pool: 22.634-2510
Note the second 'No Pool' run uses existing cached Tasks, also goes to show how much more responsive my tweaks make System.Threading in Berlin.
Re: Connection pooling setup
Posted: Wed 14 Aug 2019 18:22
by FredS
One more bit of info, ran the first 'No Pool' to allow the Tasks to spin up in Rio.
Ran the second and it ended with 92% CPU usage which is normal.
Immediately ran 'Pooling' it never made it above 42% CPU usage, best guess is that its being throttled by a mutex or whatnot.
Code: Select all
No Pool: 50.901-0948
No Pool: 38.275-8876
Pooling: 01:15.416-9918