larger shared buffers slows down cluster

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: larger shared buffers slows down cluster
Date: 2012-08-22 20:48:21
Message-ID: 50354595.5070004@dunslane.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


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? If so, is there a good explanation? I'm not
sure what other operations might be affected this way.

cheers

andrew

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2012-08-22 21:00:56 Re: larger shared buffers slows down cluster
Previous Message Josh Berkus 2012-08-22 18:45:19 Re: Audit Logs WAS: temporal support patch