Re: 9.1.3 backends getting stuck in 'startup'

On May 24, 2012, at 3:13 PM, Tom Lane wrote:

> Jeff Frost <jeff(at)pgexperts(dot)com> writes:
>> On 05/24/12 12:21, Tom Lane wrote:
>
> Huh. A bit bigger, but not by that much. It doesn't seem like this
> would be enough to make seqscan performance fall off a cliff, as it
> apparently did. Unless maybe the slightly-bloated catalogs were just a
> bit too large to fit in RAM, and so the repeated seqscans went from
> finding all their data in kernel disk buffers to finding none of it?

Seems unlikely.

>
>> So, interestingly, they're both quite large, but the old broken system is
>> quite a bit larger. Any other data points be helpful?
>
> I think it would be interesting to get the pg_relation_size for pg_class
> plus pg_attribute plus pg_index (which I think is everything that gets
> seqscannedd in this way) on both systems, and see how those numbers
> match up to total RAM on the box.

Server has 128GB of RAM.

Currently running system:

select pg_size_pretty(pg_relation_size('pg_class'));
pg_size_pretty
----------------
181 MB
(1 row)

select pg_size_pretty(pg_relation_size('pg_index'));
pg_size_pretty
----------------
23 MB
(1 row)

Old broken system:

select pg_size_pretty(pg_relation_size('pg_class'));
pg_size_pretty
----------------
221 MB
(1 row)

select pg_size_pretty(pg_relation_size('pg_index'));
pg_size_pretty
----------------
44 MB
(1 row)

BTW, when I connected to it this time, I had a really long time before my psql was able to send a query, so it seems to be still broken at least.