I am trying your product, to see if we can use it in our product. For this purpose, I created a small Delphi (XE2) program to test performance on a PostgreSQL 9.3 database, running on a Windows Server (32-bit PostgreSQL).
My test program creates 25 threads, each of them inserting 40,000 records into a table. This should lead to a total of one million records.
However, this is not the case in my test case. Threads are being aborted, giving various error messages. It seems to me that connections are being reset by the client (my program).
If I lower the number of threads, the problem is not present anymore. Seems like I am running into some bottleneck somewhere.
I have attached the source code of my test program (see below). Can you look at this and tell me if I am doing womething wrong, or if there is indeed some problem in the component or PostgreSQL itself ?
Thanks in advance.
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, PgAccess, Vcl.StdCtrls;
type
TPgInsertThread = class(TThread)
protected
procedure Execute; override;
end;
TForm2 = class(TForm)
Button1: TButton;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form2: TForm2;
implementation
{$R *.dfm}
procedure TForm2.Button1Click(Sender: TObject);
var
PgDataCon: TPgConnection;
intThreadNr: integer;
begin
// Create a test table in database 'PG_TEST'
PgDataCon := TPgConnection.Create(Nil);
PgDataCon.Server := 'localhost';
PgDataCon.Database := 'PG_TEST';
PgDataCon.Username := 'postgres';
PgDataCon.Password := '12345';
PgDataCon.Connect();
try
PgDataCon.ExecSQL('Drop Table PG_TEST;');
except
end;
PgDataCon.ExecSQL('Create Table PG_TEST(PG_A text, PG_B char( 50 ), PG_C char( 50 ), PG_D char( 50 ), PG_E char( 50 ), PG_F char( 50 ), PG_G char( 50 ), PG_H char( 50 ), PG_I char( 50 ), PG_J char( 50 ), ' +
'PG_K char( 50 ), PG_L char( 50 ), PG_M char( 50 ), PG_N char( 50 ), PG_O char( 50 ), PG_P char( 50 ), PG_Q char( 50 ), PG_R char( 50 ), PG_S char( 50 ), PG_T char( 50 ), PG_U char( 50 ), ' +
'PG_V char( 50 ), PG_W char( 50 ), PG_X char( 50 ), PG_Y char( 50 ), PG_Z char( 50 ));');
PgDataCon.ExecSQL('CREATE INDEX ON PG_TEST (PG_A);');
PgDataCon.Disconnect();
// Create 25 threads each inserting 40,000 records
for intThreadNr := 1 to 10 do
TPgInsertThread.Create();
end;
procedure TPgInsertThread.Execute();
var
PgDataCon: TPgConnection;
objQuery: TPgQuery;
intCount1, intCount2: integer;
begin
try
PgDataCon := TPgConnection.Create(Nil);
PgDataCon.Server := 'localhost';
PgDataCon.Database := 'PG_TEST';
PgDataCon.Username := 'postgres';
PgDataCon.Password := '12345';
PgDataCon.Connect();
objQuery := TPgQuery.Create(Nil);
try
objQuery.Connection := PgDataCon;
objQuery.SQL.Text := 'insert into PG_TEST(PG_A,PG_B,PG_C,PG_D,PG_E,PG_F,PG_G,PG_H,PG_I,PG_J,PG_K,PG_L,PG_M,PG_N,PG_O,PG_P,PG_Q,PG_R,PG_S,PG_T,PG_U,PG_V,PG_W,PG_X,PG_Y,PG_Z) ' +
'VALUES (:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14,:p15,:p16,:p17,:p18,:p19,:p20,:p21,:p22,:p23,:p24,:p25,:p26)';
objQuery.Prepare();
for intCount1 := 0 to 39 do
begin
PgDataCon.StartTransaction();
for intCount2 := 1 to 1000 do
begin
objQuery.ParamByName('p1').Value := IntToStr((intCount1 * 1000) + intCount2);
objQuery.ParamByName('p2').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p3').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p4').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p5').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p6').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p7').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p8').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p9').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p10').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p11').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p12').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p13').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p14').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p15').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p16').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p17').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p18').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p19').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p20').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p21').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p22').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p23').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p24').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p25').Value := '12345678901234567890123456789012345678901234567890';
objQuery.ParamByName('p26').Value := '12345678901234567890123456789012345678901234567890';
objQuery.Execute();
end;
PgDataCon.Commit();
end;
finally
objQuery.Free();
end;
PgDataCon.Disconnect();
PgDataCon.Free();
except
on E: Exception do
begin
ShowMessage(E.Message);
end;
end;
end;
end.