Jaybird over Lan

[Hugo Larson]

Hello,

Have used Firebird and Jaybird for many years and have noticed that connections over Lan are slow.

Hope I can explain what I mean.
When connection to an IP host, or hostname from Windows to windows it's very notable that connections over Wlan is slow.
When using wired connection it's not obvious. But connection over 2.4 GHz lan. Very slow. 5 GHz faster but not as fast it should be in my opinion.
For example when using wifi 2.5 or 5 GHz, unless downloading large files one cannot feel difference. But with Jaybird executing normal SQL its highly notable.
Don't know if this is related to Jaybird or underlaying socket connection but I thought to tell you about it for an opinion.

BR,
Hugo 

[Mark Rotteveel]

The Firebird protocol is very chatty (lots of small messages), which can
increase latency. Communication over WLAN goes over more layers than a
wired connection, which I guess might increase latency further.

Other than that, Jaybird doesn't really do anything special which would
explain slowness, to be honest.

You may want to check if you're actually fetching multiple rows at a
time, IIRC, you used that old Borland library, and maybe it forces a
small fetch size? Check for setFetchSize and for setCursorName calls
(setting a cursor name will also force fetching rows one-by-one, because
the only real reason to use a named cursor in DSQL, is to use positioned
updates/deletes).

Mark
--
Mark Rotteveel

[Hugo Larson]

Hi Mark,

Thank you for your response.

Made some further test with isql and speed is about the same at Jaybird.

Select * from table with about 5000 rows to file. Size of result file became about 5Mb
When localhost, took a couple of seconds. Wlan up to one minute!

Copy result file over Wlan takes a second. 

I wrote a small program with JDK httpserver that receives SQL and executes locally and send back result. Very fast.

I understand it's not same but I still don't get it why firebird over lan is significant slower even if Firbird is very "chatty" when sql is so simple.

Merry Christmas!
Hugo,

--
You received this message because you are subscribed to the Google Groups "firebird-java" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-jav...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/firebird-java/1176929d-6fc0-47fe-ad1e-5506fee22aa4%40lawinegevaar.nl.

[Mark Rotteveel]

On 24/12/2024 13:37, 'Hugo Larson' via firebird-java wrote:
> Made some further test with isql and speed is about the same at Jaybird.
>
> Select * from table with about 5000 rows to file. Size of result file
> became about 5Mb
> When localhost, took a couple of seconds. Wlan up to one minute!
>
> Copy result file over Wlan takes a second.
>
> I wrote a small program with JDK httpserver that receives SQL and
> executes locally and send back result. Very fast.
>
> I understand it's not same but I still don't get it why firebird over
> lan is significant slower even if Firbird is very "chatty" when sql is
> so simple.

The only other thing I can think of is that your WLAN accesspoint does
additional buffering (packet aggregation), or maybe applies Nagle's
algorithm (which as an intermediate, it shouldn't do), which can delay
small packets, but to be honest, that is just Wild Ass Guessing, and not
something I think WiFi usually does.

If your network setup is more complex, and for example involves proxies
or something, there might be additional choke points.

Alternatively, your WiFi connection is very bad (low signal or low
signal-to-noise ratio), but then I would expect other things like
downloads to also be slow.

In any case, trying something similar, selecting from a table with
BIGINT and a non-null CHAR(1000) column with 5000 rows, which produces
5MiB+ of data, I get nearly instantaneous (2-3 seconds) results over my
WiFi connection (with ISQL).

Mark
--
Mark Rotteveel

[Hugo Larson]

Hi,
First noticed seen this slowness at many customer sites where they also use Wired lan so it's not something specific to my home Wlan, where signal is strong. access point Server and client meters away in my same room.

I use FB 2.5. Don't think it has encryption connection which I guess should be fastest.
What version of FB did you test with?

Hugo.

Mark
--
Mark Rotteveel

--
You received this message because you are subscribed to the Google Groups "firebird-java" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-jav...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/firebird-java/508c3989-8430-4683-8dab-827f0eb064ed%40lawinegevaar.nl.

[Mark Rotteveel]

On 24/12/2024 15:24, 'Hugo Larson' via firebird-java wrote:
> First noticed seen this slowness at many customer sites where they also
> use Wired lan so it's not something specific to my home Wlan, where
> signal is strong. access point Server and client meters away in my same
> room.

Then I'd look at things like firewalls (both local on the client, and
within the network), or virus scanners, that also attempt to scan
network traffic.

You could also try if setting the socketBufferSize connection property
to 16384, 32768, 65536, or even bigger has an effect. Jaybird normally
uses the OS default (the Windows default is 65536, at least on Windows 11).

You may also want to check what the actual size is on your OS:

```
import java.net.Socket;

public class SocketSize {

public static void main(String[] args) throws Exception {
try (var socket = new Socket()) {
System.out.println(socket.getReceiveBufferSize());
System.out.println(socket.getSendBufferSize());
}
}
}
```

On my machine it prints:

```
65536
65536
```

However, I'd be surprised if that would be different for you.

Alternatively, write a simple ping/pong client and server (i.e. the
client sends and flushes a message to the server, and the server
responds with a small message, and so forth), and set them on different
machines in your network, and see how they interact, that is what the
delay is between sending and receiving (make sure to set
TCP_NODELAY/setTcpNoDelay(true) on both client and server socket).

> I use FB 2.5. Don't think it has encryption connection which I guess
> should be fastest.
> What version of FB did you test with?

I tested it with Firebird 5.0.1, but I don't think there will be any
difference between versions in this regard.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 27/12/2024 14:04, 'Mark Rotteveel' via firebird-java wrote:
> On 24/12/2024 15:24, 'Hugo Larson' via firebird-java wrote:
>> First noticed seen this slowness at many customer sites where they
>> also use Wired lan so it's not something specific to my home Wlan,
>> where signal is strong. access point Server and client meters away in
>> my same room.
>
> Then I'd look at things like firewalls (both local on the client, and
> within the network), or virus scanners, that also attempt to scan
> network traffic.

One thing you could try, is to capture the network traffic with
Wireshark both at the client and server (do add appropriate filters to
avoid capturing too much traffic), and compare them to see if packets
are sent and received at roughly the same time, or if there is a
(larger) delay between the packet being sent on one side and being
received on the other end than can be accounted for by network latency.

That would at least suggest it is something in the network between
server and client.

Mark
--
Mark Rotteveel

[Hugo Larson]

Hi Mark,

Sorry for late response.
Made some more tests with isql. Thought this is cleanest way testing.
Now I have two ubuntu virtual servers. FB 4.0. 100mbit/s line over Internet.
It takes even longer than wlan/lan.

I just don't get it why it's so slow. In my world it should just be as fast as localhost plus time to transfer the data ~ 5 Mb.
Transferring 500 MB data between the servers is much faster than the "select * from" 

BR, 
Hugo

Mark
--
Mark Rotteveel

--
You received this message because you are subscribed to the Google Groups "firebird-java" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-jav...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/firebird-java/4bb81b6e-8c21-4fd7-b567-3f1655a9750c%40lawinegevaar.nl.