Re: larger shared buffers slows down cluster

On Wed, Aug 22, 2012 at 1:48 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
> This problem has been reported by a client.
>
> Consider the following very small table test case:
>
> create table bar as select a,b,c,d,e from generate_series(1,2) a,
> generate_series(3,4) b, generate_series( 5,6) c,
> generate_series(7,8) d, generate_series(9,10) e;
> create index bar_a on bar(a);
> create index bar_b on bar(b);
> create index bar_c on bar(c);
> create index bar_d on bar(d);
> create index bar_e on bar(e);
> create unique index bar_abcde on bar(a,b,c,d,e);
>
>
> Now running:
>
> cluster bar using bar_abcde;
>
>
> appears to be very sensitive to the shared buffers setting. In an amazon
> very large memory instance (64GB) and PostgreSQL 9.1.4, I observed the
> following timings:
>
>
> Shared Buffers Time
> 48Gb 2058ms
> 8Gb 372ms
> 1gb 67ms
>
>
> Is this expected behaviour?

Yeah. Clustering the table means that all the indexes and the old
version of the table all get dropped, and each time something is
dropped the entire buffer pool is scoured to remove the old buffers.

In my hands, this is about 10 times better in 9.2 than 9.1.4, at 8GB.
Because now the scouring is done once per object, not once per fork.
Also, the check is done without an initial spinlock.

It perhaps could be improved further by only scouring the pool once,
at the end of the transaction, with a hash of all objects to be
dropped.

> If so, is there a good explanation? I'm not sure
> what other operations might be affected this way.

drop, truncate, reindex, vacuum full. What else causes a table to be
re-written?

Cheers,

Jeff