pgDAC and multithreading

[apriem]

Hello,

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.

[azyk]

We have received your sample by email and executed it. After all the 25 threads finished working, 1 000 000 records were successfully inserted to the PG_TEST table. We got no errors on execution.

Please provide the full error message you got on execution of your sample.

[apriem]

I get EPgError exceptions inside (some of the) threads.

The message is (I removed some #13#10 characters from it) : Error on data reading from the connection: De externe host heeft een verbinding verbroken. Socket Error Code: 10054($2746).

The stacktrace property of the exception is empty.

[azyk]

Please modify your sample so that there would be used only standard components instead of PgDAC: dbGO (TADOConnection, TADOQuery). Check whether the problem is reproduced in this case and let us know the results.

Note: the reason for this problem can be incorrect network or PostgreSQL configuration. Please look at the 10054 error code description at MSDN: http://msdn.microsoft.com/en-us/library ... s.85).aspx

[apriem]

OK, I will try this and let you know the results.