Re: Remote access to Postgresql slow

Is your network link between server and client across the public internet?

You need to check bandwidth and latency characteristics of your network.

A simple test run following on server host and run it again on the client
host.

time psql [connect details] -c 'select now()'

I access postgresql database across the public internet (by tunnelling port
5432 across compressed ssh sessions). In my case latency is a significant
penalty. Locally time response is for above is <10ms but remotely it is 30
times slower (350ms)

You may need to install wireshark or similar and monitor client traffic in
order to figure out the network penalty. Maybe your app goes back and
forward to postgres multiple time; does lots of chatter. If so then
latency cost becomes very significant. You want to try and minimise the
number of postgresql calls; retrieve more data will less SQL operations.

On Fri, Sep 14, 2012 at 7:02 PM, Manoj Agarwal <ma(at)ockham(dot)be> wrote:

> **
>
> Hi,
>
> I have a Centos 6.2 Virtual machine that contains Postgresql version 8.4
> database. The application installed in this Virtual machine uses this
> database that is local to this Virtual machine. I have tried to offload
> the database, by installing it on a remote Virtual machine, on another
> server, and tried to connect to it from my local Virtual machine. The
> application configuration remains the same, only database is offloaded to a
> remote Virtual machine on another server and the connection parameters have
> changed. The connection is all fine and the application can access the
> remote database.
>
> I have observed that the Postgresql is responding extremely slow. What
> should I do to improve its performance?
>
> Please suggest.
>
>
> Kind Regards,
> Manoj Agarwal
>
>