Re: Slow query execution over WAN network

Hi,

I tried to analyze it further, this time the query executed over UMTS
- where it took 2000ms. (ping tp server 130ms, about 2mbit/s down):
http://93.190.88.182/wireshark_umts.tcpdump

0-490ms: Some TCP magic going on (tcp segment of a reassembled PDU??)
491 ms - Postgres query is sent
680ms - 1472ms - TCP ACKs
1642ms - Postgres - parse/bind completition
1873ms - Postgres - Row description
1893ms - First data Row
Row / ACK
2053ms - Last data Row

What makes me wonder is:
- Why does it take 500ms until the query is sent, what is that tcp
stuff happening before
- It seems until 1472ms the server is sending ACKs for the query
request, so does it take ~1000ms for the server to receive the query?
So is the query-upload the problem?
- Actual Row-Data transmission only takes 150ms.

If its indeed the query-data upload, are there any options to compress
the stream between the jdbc-driver and postgres-server?

Thanks, Clemens


> You truncated times in profiler window. May I ask what time was measured for
> socket.read(), and what kind of connection did you used for this (dsl, umts),
> connection speed and min/max/avg ping time to database server?

Sorry, I've deleted the profile - but about 70% were spent in socket.read().
Ping to the db-server was about ~30ms, it was a 8mbit ADSL line with
1M upstream.
Looking at the profile it seems reading back the data (~25kb/request)
is the problem.

> What other operations are performed inside benchamrkQuery(), commit/rollback,
> connection open?

Nothing, its just a:
> ResultSet rs = st.executeQuery(query);
> while (rs.next());

> Did fetch size wa,s when you was making this test, enaught to get all records
> at once?
Yes, I tried setting it higher a bit but nothing changed.
However the query only returned 12 rows - so I guess it should be
covered by default fetch size.

Thanks, Clemens