PGPool Linux Apache top performance Delphi11

[RDP1974 40]

hi, I like paste here a little unit helping coders to do scalable I/O http app

 

look, I did an apachebench in a I9 virtualized ubuntu, with 100 concurrent users, asking this text through webbroker and firedac postgres sql query:

«Tuttavia, perché voi intendiate da dove sia nato tutto questo errore, di quelli che incolpano il piacere ed esaltano il dolore, io spiegherò tutta la questione, e presenterò le idee espresse dal famoso esploratore della verità, vorrei quasi dire dal costruttore della felicità umana. Nessuno, infatti, detesta, odia, o rifugge il piacere in quanto tale, solo perché è piacere, ma perché grandi sofferenze colpiscono quelli che non sono capaci di raggiungere il piacere attraverso la ragione; e al contrario, non c'è nessuno che ami, insegua, voglia raggiungere il dolore in se stesso, soltanto perché è dolore, ma perché qualche volta accadono situazioni tali per cui attraverso la sofferenza o il dolore si cerca di raggiungere un qualche grande piacere. Concentrandoci su casi di piccola importanza: chi di noi intraprende un esercizio ginnico, se non per ottenerne un qualche vantaggio? E d'altra parte, chi avrebbe motivo di criticare colui che desidera provare un piacere cui non segua nessun fastidio, o colui che fugge un dolore che non produce nessun piacere? Al contrario, però, noi con indignazione denunciamo e riteniamo meritevoli di odio quelli che, rammolliti e corrotti dai piaceri del momento, accecati dal desiderio, non prevedono a quali dolori e a quali sofferenze andranno incontro, e uguale colpa hanno quelli che abbandonano i propri doveri per pigrizia d'animo, cioè per evitare le fatiche e i dolori. Certamente è facile e rapido distinguere questi casi. Infatti nel tempo libero, quando abbiamo tutta la nostra possibilità di scegliere e niente ci ostacola dal fare ciò che ci piace di più, bisogna accogliere ogni piacere e respingere ogni dolore. Ma in altri momenti, o nei doveri inevitabili o negli obblighi che ci vengono dalle circostanze, spesso accadrà che si debba respingere il piacere e accogliere il fastidio. E così il saggio si regola scegliendo tra questi atteggiamenti, facendo in modo che o – respingendo il piacere – ne ottenga di più grandi, o – sopportando il dolore – ne eviti di peggiori.»

 

calling an Apache2 module webbroker

 

the system raw performances are impressive, producing 18000 (18 thousands) sustained http requests for sec

 

pgsql local limits 1000, ssl off

 

put PhysPG with libpq.so in lib path over the datamodule

here the unit to use, before you can set this on webbroker source

 

begin
{$IFDEF MSWINDOWS}
  CoInitFlags := COINIT_MULTITHREADED;
{$ENDIF}
  Web.ApacheApp.InitApplication(@GModuleData);
  Application.Initialize;
  Application.MaxConnections:=1000;
  Application.WebModuleClass := WebModuleClass;
  Application.Run;
end.


the unit to interface pooled pg

unit PGPool;

interface

uses
{$IFDEF WINDOWS}
  Windows,
  FireDAC.VCLUI.Wait,
{$ENDIF}
  Classes,
  System.SysUtils,
  // FireDAC.Phys.PG,
  FireDAC.Stan.Intf,
  FireDAC.Stan.Option,
  FireDAC.Stan.Error,
  FireDAC.UI.Intf,
  FireDAC.Phys.Intf,
  FireDAC.Stan.Def,
  FireDAC.Stan.Pool,
  FireDAC.Stan.Async,
  FireDAC.Phys,
  FireDAC.Stan.Param,
  FireDAC.DatS,
  FireDAC.DApt.Intf,
  FireDAC.DApt,
  FireDAC.Comp.DataSet,
  FireDAC.Comp.Client;

type
  TDB = class
  public
    DBC: TFDConnection;
    DBQ: TFDQuery;
    DBT: TFDTransaction;
    constructor Create;
    destructor Destroy; override;
  end;

implementation

procedure InitPool;
var
  oParams: TStrings;
begin
  oParams := TStringList.Create;
  oParams.Add('DriverID=PG');
  oParams.Add('User_Name=USERNAME');
  oParams.Add('Server=ADDRESS');
  oParams.Add('Password=PASSWORD');
  oParams.Add('Database=DBNAME');
  // oParams.Add('CharacterSet=none');
  oParams.Add('Pooled=True');
  oParams.Add('POOL_CleanupTimeout=3600000');
  oParams.Add('POOL_ExpireTimeout=600000');
  oParams.Add('POOL_MaximumItems=1000');

  FDManager.Close;
  while FDManager.State <> dmsInactive do
    Sleep(1);

  FDManager.Open;
  FDManager.AddConnectionDef('PG_Pooled', 'PG', oParams);
  oParams.Free;
end;

constructor TDB.Create;
begin
  inherited;
  // init connection
  DBC := TFDConnection.Create(nil);
  DBC.ConnectionDefName := 'PG_Pooled';
  DBC.LoginPrompt := False;
  DBC.FetchOptions.Unidirectional := True;
  DBC.UpdateOptions.RequestLive := False;
  DBC.Transaction := DBT;

  // init transaction
  DBT := TFDTransaction.Create(nil);
  DBT.Connection := DBC;

  // init query
  DBQ := TFDQuery.Create(nil);
  DBQ.Connection := DBC;
  DBQ.Transaction := DBT;
  DBQ.FetchOptions.Unidirectional := True;
  DBQ.UpdateOptions.RequestLive := False;

  DBC.Connected := True;
end;

destructor TDB.Destroy;
begin
  DBQ.Free;
  DBT.Free;
  DBC.Free;
  inherited;
end;

initialization

InitPool;

end.


eg.

with TDB.Create do
    begin
      try
        DBQ.SQL.Text := 'select * from table';
        DBQ.Open;
        while not DBQ.eof do
        begin
          S := S + DBQ.FieldByName('test').asString + '<br>' + FormatDateTime('ddmmyyyy hh:nn:ss', Now) + '<br>';
          DBQ.Next;
        end;
      finally
        Free;
      end;
    end;

 

kind regards

R.

Edited September 29, 2021 by RDP1974
Please use the Code tags

[RDP1974 40]

100 concurrent users querying pgsql (to see scalability of the stack httpd with db)

same setup test done with latest PHP (jit) and pooling enabled is doing 3000 reqs/sec

delphi llvm linux apache with pooling 15000 reqs/sec (5x!!! the throughput of the php)

 

Edited September 28, 2021 by RDP1974

[RDP1974 40]

plz Idera put Delphi Linux LLVM in Professional version too 🙂

[RDP1974 40]

somebody ask me php details:

 

php 8.0.11 latest JIT compiler enabled (faster mode as apache module), pooling enabled in php.ini (persistent)

 

open connection

query

fetch lines

close connection

 

<?php
// Connecting, selecting database
$dbconn = pg_connect("host=localhost dbname=postgres user=postgres password=postgres") or die('Could not connect: ' . pg_last_error());

// Performing SQL query
$query = 'SELECT * FROM enter';
$result = pg_query($query) or die('Query failed: ' . pg_last_error());

// Printing results in HTML
while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
    foreach ($line as $colvalue) {
        echo "$colvalue";
    }
}

// Free resultset
pg_free_result($result);

// Closing connection
pg_close($dbconn);
?>

 

delphi apache:

Server Software:        Apache/2.4.41
Server Hostname:        localhost
Server Port:            80

Document Path:          /test
Document Length:        965 bytes

Concurrency Level:      100
Time taken for tests:   0.642 seconds
Complete requests:      10000
Failed requests:        0
Keep-Alive requests:    9957
Total transferred:      11768106 bytes
HTML transferred:       9650000 bytes
Requests per second:    15578.10 [#/sec] (mean)
Time per request:       6.419 [ms] (mean)
Time per request:       0.064 [ms] (mean, across all concurrent requests)
Transfer rate:          17902.80 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.2      0       2
Processing:     1    6   6.4      6     175
Waiting:        1    6   6.4      6     175
Total:          1    6   6.5      6     177

Percentage of the requests served within a certain time (ms)
  50%      6
  66%      6
  75%      7
  80%      8
  90%     10
  95%     12
  98%     18
  99%     25
 100%    177 (longest request)

 

php 8 jit:

Server Software:        Apache/2.4.41
Server Hostname:        localhost
Server Port:            80

Document Path:          /test.php
Document Length:        902 bytes

Concurrency Level:      100
Time taken for tests:   2.874 seconds
Complete requests:      10000
Failed requests:        0
Keep-Alive requests:    9980
Total transferred:      11288500 bytes
HTML transferred:       9020000 bytes
Requests per second:    3479.01 [#/sec] (mean)
Time per request:       28.744 [ms] (mean)
Time per request:       0.287 [ms] (mean, across all concurrent requests)
Transfer rate:          3835.23 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.2      0       3
Processing:     3   29   2.9     28      53
Waiting:        2   29   2.9     28      53
Total:          3   29   2.9     28      53

Percentage of the requests served within a certain time (ms)
  50%     28
  66%     29
  75%     29
  80%     30
  90%     31
  95%     33
  98%     36
  99%     41
 100%     53 (longest request)
 

 

Edited September 30, 2021 by RDP1974

[Guest]

I do not understand at all. I use RTC and compile my http/s servers to win32/64.

 

I wrote two different pooling units, one that pools a complete DataModule (for session based servers) and one that pools specific queries with parameters (for my REST-kind of severs). 15 years ago (used IW back then, blissfully rooted that out) and i have not had any reason to change any of that code since. My server can serve 15000 random and different mapi icons per second (the only thing i tested back when, no one has ever complained about performance so i  have not needed to put effort into new "beches").

 

My confusion: why do you need a .so/.dll library to do that? What is the "magic"? What is difficult in implementing pooling in Delphi? Most DAC include connection pooling out of the box. But query pooling or DM pooling is not more complex, it's just about at what level you pool and with what parameters/how exactly you section off the items of the pool. No?

 

I am a bit tired atm, and just want to make clear that i am not criticizing your decision, i am honestly curious.

[RDP1974 40]

hello,

 

I consider that target a webbroker http app as apache module would have a lot of benefits: the code runs inside the process httpd, using the reliability and the scalability of the apache.

I was curious to see the performances of the LLVM Linux compiler in the D11, having done integer tests and some benchmarks, I can tell that's on the level of the windows counterpart.

But I can tell you that IIS isapi under Windows produces similar performance results.

 

I have not benchmarked standalone webserver as Indy based webbroker or others, usually used to be managed as reverse proxy.

 

I had liked to share the results with you, seeing how scalable can be a web app inside Linux Apache realm.

 

btw.The unit wants to show how to enable the pooling in Firedac connections.

 

kind regards

Edited October 3, 2021 by RDP1974