Thread: Remote access to Postgresql slow

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
   

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 3:02 AM, Manoj Agarwal <ma@ockham.be> wrote:
> 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?

Are you connecting via host name?  If so have you tried connecting via IP?