Message-ID: <4C3080CC.1010300@postnewspapers.com.au>
Date: Sun, 04 Jul 2010 20:38:36 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.9) Gecko/20100423 Lightning/1.0b1 Thunderbird/3.0.4
MIME-Version: 1.0
To: Sachin Kumar <Sachin.Ku@globallogic.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Performance issues with postgresql-8.4.0
References: <21026437C2BC0D45BE196CB0A35EFEFD052700D7@ex2-del1.synapse.com>
In-Reply-To: <21026437C2BC0D45BE196CB0A35EFEFD052700D7@ex2-del1.synapse.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On 29/06/10 15:01, Sachin Kumar wrote:

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

Quick guess: checkpoints. Enable checkpoint logging, follow the logs,
see if there's any correspondance.

In general, looking at the logs might help you identify the issue.

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

Fetching the number of entries in a table - using count(...) - is
actually a rather expensive operation, and a poor choice if you just
want to see if the server is responsive.

  SELECT id FROM tablename LIMIT 1;

where "id" is the primary key of the table would be a better option.

--
Craig Ringer