Interbase 2020 update 3 - high firedac network traffic

[Anto90 0]

We have a website developed with intraweb technology in delphi. (In the period of maximum work we get to have about 2000 active sessions at the same time) (Database weight 15gb) As support we use interbase 2020 update 3 (on a separate server from the one where the webserver runs) and firedac connection technology with delphi 11.2. (The webserver is IIS) What we notice is that as the number of connected users increases, the ibserver network traffic increases a lot up to 5gb/s, congesting the entire network and significantly reducing system performance. (Making navigation between pages very slow) Database connections are made stateless, each request does its job and the db connection is closed when finished. Downgrading to delphi 11.0 the problem seems to decrease traffic, however slow down system performance. However the traffic is not justified by the amount of data exchanged. Fdconnection of firedac is set as protocol tcp and ip the destination server of the database. The two servers are connected in a 10 gigabit LAN. Each session as defined in the intraweb has its own dedicated thread where the datamodule is created with the components for connecting to the database and executing queries) As suggested by embarcadero we increased database pages to 8192 and buffers 200000. How to handle requests to the db in a stateless way (connection and disconnection can significantly degrade system performance?) In my datamodule there are fdconnection with tcp/ip protocol and port 3050 and then with fdquery fd command to execute the queries. What optimal settings for fdconnection can be used.? Does anyone have any experience with this yet? Thank you

 

Andrea Antonini

 

 

Edited December 7, 2022 by Anto90

[Zoran Bonuš 12]

Not exactly addressing the issue, but do you use firedac connection pooling?

https://docwiki.embarcadero.com/RADStudio/Sydney/en/Multithreading_(FireDAC)

May help or circumvalent your problem

 

Also try to use read-only transactions and "read committed" isolation mode whenever possible.

 

Also check the ibclient dll version.

[Fr0sT.Brutal 897]

Try to test with single isolated client

App's traffic could be estimated by counters in the task manager or with sniffer

Then try to enable monitoring at the server to check if queries are the same (probably newer FD sends some excess queries)

Examine FD changelog between 11 and 11.2, probably some defaults have changed

[Sriram 4]

Andrea,
I got some additional feedback from Dmitry. hope this helps.

"

About "high firedac network traffic". There were no changes between 11.0 and 11.2 which may negatively affect FD / IB performance. Then as written in responses: use FD connection pooling, and try to analyze single connection traffic. I can add to that:

• is fiMeta included / excluded from FetchOptions.Items ? IOW, is FD querying IB dictionary on each FDQuery.Open

• how they are controlling transactions ? explicitly or implicitly ? And what is value of FetchOptions.AutoFetchAll ?

To analyze traffic, they can use FireDAC monitor.

"

[Sriram 4]

Andrea,
I see that you are using InterBase 2020 Update 3. We recently released InterBase 2020 Update 4; you can see the details and download from https://blogs.embarcadero.com/embarcadero-interbase-2020-update-4-released/

Just FYI.

[Anto90 0]

On 12/9/2022 at 4:26 AM, Sriram said:

Andrea,
I got some additional feedback from Dmitry. hope this helps.

"

About "high firedac network traffic". There were no changes between 11.0 and 11.2 which may negatively affect FD / IB performance. Then as written in responses: use FD connection pooling, and try to analyze single connection traffic. I can add to that:

• is fiMeta included / excluded from FetchOptions.Items ? IOW, is FD querying IB dictionary on each FDQuery.Open

• how they are controlling transactions ? explicitly or implicitly ? And what is value of FetchOptions.AutoFetchAll ?

To analyze traffic, they can use FireDAC monitor.

"

HI thank you for your response

fiMeta in included in cache items.

We do not use autocommit. we manage transaction. At end of each request me call commit.

FetchOptions.Autofetchall is afAll.

Thank you.