On Fri, Jul 2, 2010 at 1:40 AM, Sachin Kumar <Sachin.Ku@xxxxxxxxxxxxxxx> 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
