Re: PostgreSQL performance problem -> tuning

scott.marlowe wrote:

> On Thu, 7 Aug 2003, Yaroslav Mazurak wrote:

>>Shridhar Daithankar wrote:

> That's a nice theory, but it doesn't work out that way.  About every two
> months someone shows up wanting postgresql to use all the memory in their
> box for caching and we wind up explaining that the kernel is better at
> caching than postgresql is, and how it's better not to push the usage of
> the memory right up to the limit.

    I'm reading this mailing list just few days. :)))

> The reason you don't want to use every bit for postgresql is that, if you
> use add load after that you may make the machine start to swap out and
> slow down considerably.

    What kind of load? PostgreSQL or another? I say that for this PC
primary task and critical goal is DBMS and it's performance.

> My guess is that this is exactly what's happening to you, you're using so
> much memory that the machine is running out and slowing down.

> Drop shared_buffers to 1000 to 4000, sort_mem to 8192 and start over from
> there.  Then, increase them each one at a time until there's no increase
> in speed, or stop if it starts getting slower and back off.

> bigger is NOT always better.

    Let I want to use all available RAM with PostgreSQL.
    Without executing query (PostgreSQL is running) top say now:

Mem: 71M Active, 23M Inact, 72M Wired, 436K Cache, 48M Buf, 208M Free
Swap: 368M Total, 2852K Used, 366M Free

    It's right that I can figure that I can use 384M (total RAM) - 72M
(wired) - 48M (buf) = 264M for PostgreSQL.
    Hence, if I set effective_cache_size to 24M (3072 8K blocks),
reasonable value (less than 240M, say 48M) for sort_mem, some value for
shared_buffers (i.e. 24M, or 6144 4K blocks (FreeBSD), or 3072 8K blocks
(PostgreSQL)), and rest of RAM 264M (total free with OS cache) - 24M
(reserved for OS cache) - 48M (sort) - 24M (shared) = 168M PostgreSQL
allocate dynamically by himself?


With best regards
    Yaroslav Mazurak.