Greg Sabino Mullane <greg@endpoint.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