Weird three minutes delay for TSQLConnection.Connect with InterBase 7.5

Question

Environment:

• Delphi 2009 client applications (and one Java), running on Windows 2003 server
• connecting to InterBase 7.5.1 (another Windows 2003 Server) over dbExpress

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

In random intervals, the connect need a three-minutes "extra time". I first suspected it could be a problem with the SQL query, but more detailed logging today showed that it is the SQLConnection.Connect which hangs.

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

Has anybody experienced a similar three-minutes "hang"?

p.s. the Java application does not log connect time so I can not say wheter it is affected by this problem.

---

This phenomenon appeared in the log files since we started with logging in 2012, but the rate has sharply increased last month. The only environment change has been the addition of new Windows servers (for RDP services, Mail, and Fax) so it could be a network-related problem.

Answer 1

Aside of a possible network problem, the cause of the delay can be that, from time to time, your query triggers a garbage collection in one of the table(s) that it is querying.

I don't know in detail Interbase 7.5 internals, but in my experience (with Firebird), this usually happens when a select is made on a table from which many records have been deleted/updated recently.

This doc at IBExpert.net explains it:

A garbage collection is only performed during a database sweep, database backup or when a SELECT query is made on a table (and not by INSERT, ALTER or DELETE). Whenever Firebird/InterBase® touches a row, such as during a SELECT operation, the versioning engine sweeps out any versions of the row where the transaction number is older than the Oldest Interesting Transaction (OIT). This helps to keep the version history small and manageable and also keeps performance reasonable.

A periodic sweep or backup made at low usage hours, can increase performance and minimize the risk of being hitted by an inconvenient garbage collection. See Sweep interval and automated housekeeping (page 6-20) and Facilitating garbage collection (page 11-19) at the Interbase 7.5 Operations Guide for more info on this.

Answer 2

Since the rate has increased with the additions of new servers on the network you could have a packet loss and a long timeout to retry. For test that hypothesis you can change the connection timeout to a small value. You also can monitor the network traffic between the servers using wireshark or tcpdump.

Monitoring

To monitor the TCP handshake only you can use:

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

Answer 3

Please check if hard disk power saving is activated on any disk on mentioned servers. That would explain if you have a delay in first connect and then no delay in following connections. Then, after a while power saving gets activated and the same problem raises again.