pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
To: pgsql-performance@postgresql.org, Yaroslav Mazurak <yamazurak@Lviv.Bank.Gov.UA>
Subject: Re: PostgreSQL performance problem -> tuning
Date: Wed, 06 Aug 2003 18:23:40 +0530
Message-ID: <3F3147AC.10040.A3BF40@localhost> (raw)
In-Reply-To: <3F30F7CF.6090900@lviv.bank.gov.ua>
References: <200308061030.36610.dev@archonet.com>

On 6 Aug 2003 at 15:42, Yaroslav Mazurak wrote:
> >>sort_mem = 131072
> > This sort_mem value is *very* large - that's 131MB for *each sort* that gets 
> > done. I'd suggest trying something in the range 1,000-10,000. What's probably 
> > happening with the error above is that PG is allocating ridiculous amounts of 
> > memory, the machines going into swap and everything eventually grinds to a 
> > halt.
> 
> 	What mean "each sort"? Each query with SORT clause or some internal
> (invisible to user) sorts too (I can't imagine: indexed search or
> whatever else)?
> 	I'm reduced sort_mem to 16M.

Good call. I would say start with 4M if you time to experiment. 

> >>enable_seqscan = false
> 
> > Don't tinker with these in a live system, they're only really for 
> > testing/debugging.
> 
> 	This is another strange behavior of PostgreSQL - he don't use some
> created indexes (seq_scan only) after ANALYZE too. OK, I'm turned on
> this option back.

At times it thinks correct as well. An index scan might be costly. It does not 
hurt leaving this option on. If your performance improves by turning off this 
option, usually the problem is somewhere else..

> 
> >>effective_cache_size = 65536
> 
> > So you typically get about 256MB cache usage in top/free?
> 
> 	No, top shows 12-20Mb.
> 	I'm reduced effective_cache_size to 4K blocks (16M?).

Are you on linux?( I lost OP). Don't trust top. Use free to find out how much 
true free memory you have.. Look at second line of free..

HTH

Bye
 Shridhar

--
millihelen, n.:	The amount of beauty required to launch one ship.




view thread (24+ messages)  latest in thread

Message-ID: <3F3147AC.10040.A3BF40@localhost>
Permalink:  ../3F3147AC.10040.A3BF40@localhost/
Also on:    postgresql.org/message-id/3F3147AC.10040.A3BF40@localhost

 · 

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: shridhar_daithankar@persistent.co.in, yamazurak@Lviv.Bank.Gov.UA
  Subject: Re: PostgreSQL performance problem -> tuning
  In-Reply-To: <3F3147AC.10040.A3BF40@localhost>
  References: <200308061030.36610.dev@archonet.com> <3F3147AC.10040.A3BF40@localhost>

* 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