Weird 3 minutes delay for TSQLConnection.Connect with InterBase 7.5

Question

Wednesday:

• Delphi 2009 client applications (and one Java) running on a Windows 2003 server
• connection to InterBase 7.5.1 (another Windows 2003 Server) via dbExpress

Delphi applications register the time to open TSQLConnection using the AfterConnect event handler of the TSQLConnection object.

At random intervals, a three-minute “extra time” is required to connect. I initially suspected that this might be a problem with the SQL query, but more detailed registration today showed that it was hanging SQLConnection.Connect .

I am not sure if this could be a problem for the network, InterBase server or Delphi / dbExpress level.

Has anyone experienced a similar “hang” three minutes?

ps the Java application does not register connection time, so I can’t say that this problem affects it.

---

This phenomenon has appeared in the log files since we started with registration in 2012, but the speed increased sharply last month. The only change in the environment is the addition of new Windows servers (for RDP, Mail, and Fax services), so this may be a network problem.

Answer 1

In addition to a possible network problem, the cause of the delay may be that from time to time your request starts garbage collection in one of the tables (tables) that it requests.

I don’t know Interbase 7.5 internals in detail, but, in my experience (with Firebird ), this usually happens when select is created on the table from which many entries were recently deleted / updated.

This document on IBExpert.net explains this:

Garbage collection is only performed during database expansion, database backup, or when a SELECT query is executed on a table (not INSERT, ALTER, or DELETE). Whenever Firebird / InterBase® touches a row, for example, during a SELECT operation, the version control mechanism displays all versions of the row where the transaction number is older than the oldest interesting transaction (OIT). This helps keep version history small and manageable, while also providing reasonable performance.

Periodic scans or backups performed during short hours can increase productivity and minimize the risk of inconvenient garbage collection. See Scan Interval and Auto Housekeeping (page 6-20) and Garbage Collection Facilitation (page 11-19) in the Interbase 7.5 Operations Guide for more information on this.

Answer 2

As speed increased with the addition of new servers on the network, you might lose packet and time out for a retry. To test this hypothesis, you can change the connection timeout to a small value. You can also control network traffic between servers using wirehark or tcpdump.

Monitoring

To monitor the TCP handshake, you can use:

tcpdump -i eth0 'tcp [13] and 2 = 2

Answer 3

Please check if the power saving of the hard drive on any drive on the specified servers is enabled. This explains if you have a delay in the first connection, and then there is no delay in the following connections. Then after some time, the power saving is activated, and the same problem arises again.