pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.




view thread (24+ messages)  latest in thread

Message-ID: <3F328683.7050008@lviv.bank.gov.ua>
Permalink:  ../3F328683.7050008@lviv.bank.gov.ua/
Also on:    postgresql.org/message-id/3F328683.7050008@lviv.bank.gov.ua

 · 

reply

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