Re: Performance issues with postgresql-8.4.0: Query gets stuck sometimes

On Fri, Jul 2, 2010 at 1:40 AM, Sachin Kumar <Sachin(dot)Ku(at)globallogic(dot)com> wrote:
> Hi,
>
> We are using postgresql-8.4.0 on 64-bit Linux machine (open-SUSE 11.x). It’s
> a master/slave deployment & slony-2.0.4.rc2 is used for DB replication (from
> master to slave).

You should really be running 8.4.4, not 8.4.0, as there are quite a
few bug fixes since 8.4.0 was released.

slony 2.0.4 is latest, and I'm not sure I trust it completely just
yet, and am still running 1.2.latest myself. At least move forward
from 2.0.4.rc2 to 2.0.4 release.

> At times we have observed that postgres stops responding for several
> minutes, even couldn’t fetch the number of entries in a particular table.

Note that retrieving the number of entries in a table is not a cheap
operation in pgsql. Try something cheaper like "select * from
sometable limit 1;" and see if that responds. If that seems to hang,
open another session and see what select * from pg_statistic has to
say about waiting queries.

> One such instance happens when we execute the following steps:
>
> -         Add few lakh entries (~20) to table X on the master DB.

Note that most westerner's don't know what a lakh is. (100k I believe?)

> -         After addition, slony starts replication on the slave DB. It takes
> several minutes (~25 mins) for replication to finish.
>
> -         During this time (while replication is in progress), sometimes
> postgres stops responding, i.e. we couldn’t even fetch the number of entries
> in any table (X, Y, etc).

I have seen some issues pop up during subscription of large sets like
this. Most of the time you're just outrunning your IO subsystem.
Occasionally a nasty interaction between slony, autovacuum, and user
queries causes a problem.

> Can you please let us know what could the reason for such a behavior and how
> it can be fixed/improved.

You'll need to see what's happening on your end. If pg_statistic says
your simple select * from X limit 1 is waiting, we'll go from there.
If it returns but bigger queries take a long time you've got a
different issue and probably need to monitor your IO subsystem with
things like iostat, vmstat, iotop, etc.

> Please let us know if any information is required wrt hardware
> details/configurations etc.

Always useful to have.