Performance Issue on Concurrent Connection and get Data

Data Abstract
1

Hi,

I hit a performance issue on using with intraweb when concurrent say 10 connection to get the data.

Delphi： 10.3.3
DA： 10.0.1463
intraweb： 15.1.2
db: firbird 2.5

I write testing web client , simulate a flow :slight_smile:

  1. Login ( make connect and get db password and compare)
  2. go to data list page ( data row is 1x)
  3. go to another data page ( data row 3x)

if one client it is 4 second, if 10 client it go to 14 second case, if 20 client it go 40 second case,

as contacted to the intraweb, I try to narrow down the case by only write DA client.

The PC client, dynamic sql get data for 2 table（ ensure each client mark request at the same time , it is concurrent) , I compare httpchannel and IndySuperTcpchannel , no much difference

  1. client 3 second
    5 client avg 5 second
    10 client avg 10 second

if I need check what is the perfomance bottleneck.

Any advise to find the reason ?

Joe

2

Hello

How many rows are requested in each case?
Also how exactly is Connection Manager is configured in your DA server app?

3

My ConnectionString:
Server=AnyDAC?AuxDriver=FB;Server=localhost;Database=MYDB.FDB;UserID=SYSDBA;Password=masterkey;Protocol=TCPIP;Characterset=utf8;

    ROIndySuperTCPServer.ThreadPool.MaxQueue := 300;
    ROIndySuperTCPServer.ThreadPool.PoolThreads := 100;
    ROIndySuperTCPServer.ThreadPool.MaxThreads := 50;

object DAConnectionManager: TDAConnectionManager
  MaxPoolSize = 2000
  DriverManager = DriverManager
  PoolingEnabled = True
End

And My Client Code:

  TTask.Run(
    procedure
    begin
      Try
        nTime:=Now;
        tmpcds:=TClientDataSet.Create(Nil);
        tmpcds.FieldDefs.Assign(cdsLog.FieldDefs);
        tmpcds.CreateDataSet;

          RoIndyTcp:=TROIndySuperTCPChannel.Create(Nil);
          RoIndyTcp.Port := ROIndySuperTCPChannel2.Port;
          RoIndyTcp.Host := ROIndySuperTCPChannel2.Host;

        ROBinMessage:=TROBinMessage.Create;

        damTmp:=TDAMemDataTable.Create(Nil);
        damTmp.LogicalName := 'tmp';
        damTmp.RemoteFetchEnabled := False;
        DmSql.InitRDA(TDAMemDataTable(damTmp),'select * from IOP_TBL',True,-1);
         DmSql.InitRDA(TDAMemDataTable(damTmp),'select * from GAME_TBL',True,-1);
        TThread.Synchronize(nil,
          procedure
          begin
            Memo1.Lines.Insert(0,'Done:'+IntToStr(i)+'----'+IntToStr(MilliSecondsBetween(nTime,now)));
          end);

    end);
end;

for IOP_TBL around 7xxx Row
Game_Tbl around 5xx Row

Please comment

4

Just as a comment. I do an approach of setting up the thread pools based on the number of cores available on the machine. Too many threads on the pool and queue kills off performance. So does the connection pool size. Consider that by default an ADO connection to SQL server holds 100 connections on its pool. So my recommendation will be to set the connection pool to align with a small buffer with the amount of your service threads if your service processing is more IOBound (long term execution).

5

Thanks, I will test the maxpool size on the performance hit. As why I set it , as my practical case it wont over 100, so I give it a try but your comment inpsire me to try the pool size.

6

any comment to test out what is the issue?

7

Hi,

looks like, it is a bottleneck in firebird.
if you move your db (.fdb) to RAM drive or SSD, it should produce better results.

you should reproduce it w/o DA if you open your tables from client(s) at the same time.

of course, DA will show a bit worse results because ~8000 records should be serialized and deserialized, but overall tendency should be the same, like

1 client x second
5 client avg ~1.5x seconds
10 client avg ~3x seconds