From: Pierre C <lists@peufeu.com>
To: pgsql-performance@postgresql.org, "Clemens Eisserer" <linuxhippy@gmail.com>
Subject: Re: Slow query execution over high latency network
Date: Sat, 19 Feb 2011 13:23:18 +0100
Message-ID: <op.vq5go4zpeorkce@apollo13> (raw)
In-Reply-To: <AANLkTi=7bOACvdrVOwR8rfgpjU=AjH1oq31H2CCJ8J+1@mail.gmail.com>
References: <AANLkTi=7bOACvdrVOwR8rfgpjU=AjH1oq31H2CCJ8J+1@mail.gmail.com>
>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, linuxhippy@gmail.com
Subject: Re: Slow query execution over high latency network
In-Reply-To: <op.vq5go4zpeorkce@apollo13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox