From: Yaroslav Mazurak <yamazurak@Lviv.Bank.Gov.UA>
To: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL performance problem -> tuning
Date: Thu, 07 Aug 2003 20:04:03 +0300
Message-ID: <3F328683.7050008@lviv.bank.gov.ua> (raw)
In-Reply-To: <Pine.LNX.4.33.0308071017240.18251-100000@css120.ihs.com>
References: <Pine.LNX.4.33.0308071017240.18251-100000@css120.ihs.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: yamazurak@Lviv.Bank.Gov.UA
Subject: Re: PostgreSQL performance problem -> tuning
In-Reply-To: <3F328683.7050008@lviv.bank.gov.ua>
References: <Pine.LNX.4.33.0308071017240.18251-100000@css120.ihs.com> <3F328683.7050008@lviv.bank.gov.ua>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox