Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes
To: pgsql-performance@postgresql.org,
 "Clemens Eisserer" <linuxhippy@gmail.com>
Subject: Re: Slow query execution over high latency network
References: <AANLkTi=7bOACvdrVOwR8rfgpjU=AjH1oq31H2CCJ8J+1@mail.gmail.com>
Date: Sat, 19 Feb 2011 13:23:18 +0100
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
From: "Pierre C" <lists@peufeu.com>
Message-ID: <op.vq5go4zpeorkce@apollo13>
In-Reply-To: <AANLkTi=7bOACvdrVOwR8rfgpjU=AjH1oq31H2CCJ8J+1@mail.gmail.com>
User-Agent: Opera Mail/10.63 (Linux)

>
> When executing huge (10kb), hibernate-generated queries I noticed that
> when executed remotly over  high-latency network (ping to server
> 200-400ms), the query takes a lot longer to complete.
>
> When the query is executed remotly (psql or jdbc) it takes 1800ms to
> execute, when I issue the query in an ssh terminal, I see the results
> almost immediatly.
> So although I should see the same latency over ssh , its way faster over  
> ssh.
> The transmitted data is small (the wireshard-file has 22kb, attached),
> and even though the umts-network is high-latency its relativly high
> bandwith (~512kbit/s up, ~2mbit/s down).


Well, if your upload bandwidth is really 512 kbits, uncompressed  
transmission of your query text should take about 0.2s, not too bad. SSH  
normally uses compression, so it should be a lor faster.

Your attached file didn't come through.

Anyway, there are several options :

- different execution plan between your app and ssh+psql, which can happen  
if the planning uses/doesn't use your specific parameters, or if some  
wrong type bindings in your app force postgres not to use an index  
(there's been a few messages on that lately, check the archives).

- dumb client versus smart client :

smart client : use the protocol which sends the query text + parameters +  
prepare + execute in 1 TCP message, 1 ping, postgres works, 1 ping, get  
reply
dumb client :
- send prepare
- wait for reply
- send execute
- wait for reply
- send "gimme result"
- wait for reply
- etc

> Any idea whats causing this? Maybe too small buffers somewhere?
> For me it poses problem, because I am working on a 2-Tier java
> application which should connect to postgres remotly - however with
> every more complex query taking 2s its almost unuseable over wireless
> networks (umts).

If you want to ensure the fastest response time you need to ensure than  
one user action (click) needs one and only one roundtrip to the server  
before all the results are displayed. If said action needs 2 SQL queries,  
it ain't possible, unless (maybe) you use the asynchronous query protocol.  
You can also stuff multiple queries in stored procedures (but Hibernate  
won't be able to generate them obviously).

One solution could be to put the database handling stuff inside an  
appserver, make your app communicate to it with a low-overhead RPC  
protocol (ie, not raw uncompressed XML) that minimizes the number of  
roudtrips, and compresses data thoroughly.