Re: Backends stalled in 'startup' state: index corruption

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Greg Sabino Mullane <greg(at)endpoint(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org, Jeff Frost <jeff(at)pgexperts(dot)com>
Subject: Re: Backends stalled in 'startup' state: index corruption
Date: 2012-05-26 16:17:04
Message-ID: 24081.1338049024@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Greg Sabino Mullane <greg(at)endpoint(dot)com> writes:
> On Fri, May 25, 2012 at 07:02:42PM -0400, Tom Lane wrote:
>> pg_attribute just enough smaller to avoid the scenario. Not sure about
>> Greg's case, but he should be able to tell us the size of pg_attribute
>> and his shared_buffers setting ...

> pg_attribute around 5 MB (+6MB indexes), shared_buffers 4GB. However,
> there is a *lot* of churn in pg_attribute and pg_class, mostly due
> to lots of temporary tables.

> P.S. Hmmm that's weird, I just double-checked the above and pg_attribute
> is now 52MB/70MB (the original figures were from yesterday). At any rate,
> nowhere near 1/4 shared buffers.

Hmph. And Jeff swears his isn't large enough to trigger syncscans
either. This could all be due to the "thundering herd" effect of a lot
of processes all doing the same only-moderately-expensive-in-itself
thing; except it's hard to see how the problem gets rolling unless the
single-process cache reload time is already a lot more than the
inter-arrival time.

Would you guys please try this in the problem databases:

select a.ctid, c.relname
from pg_attribute a join pg_class c on a.attrelid=c.oid
where c.relnamespace=11 and c.relkind in ('r','i')
order by 1 desc;

If you see any block numbers above about 20 then maybe the triggering
condition is a row relocation after all.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Greg Sabino Mullane 2012-05-26 16:33:53 Re: Backends stalled in 'startup' state: index corruption
Previous Message Bruce Momjian 2012-05-26 15:58:26 Re: VIP: new format for psql - shell - simple using psql in shell