At 11:14 AM 14/07/2008, you wrote:

>Hi, we have a firebird (1.5.4) database on a remote suse linux server,
>and we are getting really slow performance. Our ping time to the
>server is 250-300ms.
>As an example, I connected via IBExpert, and after a really slow
>startup if I dbl-click a table to bring up its details, this takes 20
>seconds.

"A table" isn't a table. It's the set returned from SELECT * FROM ATABLE. How "fast" it is depends on how the client app is delivering the set to you, i.e., does it fetch the entire set before showing you anything? or does it fetch a "screenful" of records at a time?

>We ran Wireshark (aka Ethereal) on the connection and it seems the
>dialog between IBExpert and the remote DB Server is a mixture of TCP
>and "GDS DB" protocols. The TCP packets all seem to be processed quite
>quickly, but the GDS DB ones are really slow... typically 300-400ms
>for each packet. So it would appear all the GDS DB packets are sent
>synchronously or have extra handshaking. Because there is so much
>to-and-fro'ing, this adds up to quite a long time.

Hmm, there is no such thing as "GDS DB protocol". All packets are TCP packets. gds_db is the symbolic name for the port on which the Firebird server is listening. If you haven't specifically configured it otherwise, the port is 3050.

The dialog between YourServer and the client goes thus:

Client: SELECT * FROM ATABLE, requests the server to prepare this query
Server returns the metadata structures for the output (or a error status array if there's an exception)
Client tells server to execute the query
Server performs the query, feeding output into a buffer at the server side and tells the client there is something there
Client does a FETCH on the first record

Now, that's bare bones. IBExpert is written with Delphi, using the FIBPlus components. Under the covers, it might be doing some more things, including going through the request-prepare-execute sequence to query the system tables for detailed metadata information. And, once it gets the signal that the buffer is receiving the output, it probably goes into a FOR loop to continue fetching records until the buffer is empty (or it might be configured to pull across "n" records at a time, up to the capacity of its own record buffer).

In short, you're barking up the wrong tree if you think that somehow a "table" should magically appear on your screen in a single round trip. If we are sensible, we don't design client applications that pull entire "tables" across the wire. We think in "smallest possible sets" and never do SELECT * queries - unless our app is a tool like IBExpert, natch!

>If there is extra handshaking or synchronous behaviour, is there some
>settings we can switch off (in firebird) to speed things up?

There's nothing "switched on" in the default configuration that is designed to hobble anything. Have you examined your TCP/IP settings to see whether your server defaults have been hobbled?

It would be useful to mention whether you are running Classic or Superserver.

>I gather Firebird 2 may have improved this protocol, but for now we must remain
>on 1.5.4.

Fb 2's rejigged wire protocol does reduce the round trips under some conditions, it's true, but it's no magic wand. It's pretty obvious that your 20-minute requests are due to something other than the comparatively noisy v.1.5 protocol.

>[When we run our main application, normal startup (includes a lot of
>database I/O) with a local database is about 10 seconds,

That's still far too long...."database I/O" can mean lots of things....what does your app actually do at startup?

>but with the remote database, 20 minutes!!]

And that's simply ridiculous. Something in your routing is causing this...assuming of course that your remote client is a LAN node and not an Internet connection...

Can I suggest that you use isql to do your metrics, to eliminate the "noise" invoked by these GUI applications?

./heLen