Re: PostgreSQL performance problem -> tuning

On Thu, 7 Aug 2003, Yaroslav Mazurak wrote:

> 		Hi All!
> 
> 
> Richard Huxton wrote:
> 
> >>>On Wednesday 06 August 2003 08:34, Yaroslav Mazurak wrote:
> 
> >>>>sort_mem = 131072
> 
> >>>This sort_mem value is *very* large - that's 131MB for *each sort* that
> 
> 	It's not TOO large *for PostgreSQL*. When I'm inserting a large amount 
> of data into tables, sort_mem helps. Value of 192M speeds up inserting 
> significantly (verified :))!

If I remember right, this is on a PII-400 with 384 Megs of RAM.  On a 
machine that small, 128Meg is probably too big for ensuring there are no 
swap storms.  Once you force the box to swap you loose.

> >>>>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?).
> 
> > Cache size is in blocks of 8KB (usually) - it's a way of telling PG what
> > the chances are of disk blocks being already cached by Linux.
> 
> 	PostgreSQL is running on FreeBSD, memory block actually is 4Kb, but in 
> most cases documentation says about 8Kb... I don't know exactly about 
> real disk block size, but suspect that it's 4Kb. :)

FYI effective cache size and shared_buffers are both measured in 
Postgresql sized blocks, which default to 8k but can be changed upon 
compile.  So, effective_cache size for a machine that shows 128 Meg kernel 
cache and 20 meg buffers would be (138*2^20)/(8*2^10) -> (138*2^10)/8 -> 
17664.

> 	I'm afraid that this may be too long. :-(((
> 	Yesterday I'm re-execute my query with all changes... after 700 (!) 
> minutes query failed with: "ERROR:  Memory exhausted in AllocSetAlloc(104)".
> 	I don't understand: result is actually 8K rows long only, but 
> PostgreSQL failed! Why?!! Function showcalc is recursive, but in my 
> query used with level 1 depth only (I know exactly).
> 	Again: I think that this is PostgreSQL's lack of quality memory 
> management. :-(

Can you run top while this is happening and see postgresql's memory usage 
climb or df the disks to see if they're filling up?  could be swap is 
filling even.  How much swap space do you have allocated, by the way?

Also, you have to restart postgresql to get the changes to postgresql.conf 
to take effect.  Just in case you haven't.  Do a show all; in psql to see 
if the settings are what they should be.