Re: Recommended optimisations slows down PostgreSQL 8.4 - Mailing list pgsql-performance

From Tom Lane
Subject Re: Recommended optimisations slows down PostgreSQL 8.4
Date
Msg-id 1940.1313174461@sss.pgh.pa.us
Whole thread Raw
In response to Recommended optimisations slows down PostgreSQL 8.4  (Waldo Nell <pwnell@telkomsa.net>)
List pgsql-performance
Waldo Nell <pwnell@telkomsa.net> writes:
> I have PostgreSQL 8.4.8 on Ubuntu Linux x64.  Server is a Core i7 950
> with 6GB of RAM.  2GB of RAM us used by Java, some small amount by the
> kernel / services and the rest is available to PostgreSQL.

[ and the DB is 74GB, and things get slower when raising shared_buffers
  from 24MB to 1500MB ]

One other point here is that with the DB so much larger than available
RAM, you are almost certainly doing lots of I/O (unless your test case
has lots of locality of reference).  With small shared_buffers, the
space for kernel disk cache amounts to 3 or so GB, and that's your
primary buffer against duplicate I/Os.  When you crank shared_buffers
up to half that, you now have two buffer pools of about the same size
independently trying to cache the most-used parts of the DB.  This is
likely to not work too well and result in much more I/O.  You save some
shared-buffers-to-kernel-buffers transfers with more shared_buffers, but
if the amount of disk I/O goes up a lot in consequence, you'll come out
way behind.

            regards, tom lane

pgsql-performance by date:

Previous
From: Waldo Nell
Date:
Subject: Re: Recommended optimisations slows down PostgreSQL 8.4
Next
From: Stephen Frost
Date:
Subject: Re: Recommended optimisations slows down PostgreSQL 8.4