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 10:05:23 +0300
Message-ID: <3F31FA33.1050009@lviv.bank.gov.ua> (raw)
In-Reply-To: <49274.192.168.1.32.1060184304.squirrel@mainbox.archonet.com>
References: <3F30AF9E.2080607@lviv.bank.gov.ua>
	<200308061030.36610.dev@archonet.com>
	<3F30F7CF.6090900@lviv.bank.gov.ua>
	<49274.192.168.1.32.1060184304.squirrel@mainbox.archonet.com>

		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 :))!

>>	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.

> It means each sort - if you look at your query plan and see three "sort"
> clauses that means that query might allocate 48MB to sorting. Now, that's
> good because sorting items on disk is much slower. It's bad because that's
> 48MB less for everything else that's happening.

	OK, I'm preparing to fix this value. :)
	IMHO this is PostgreSQL's lack of memory management. I think that 
PostgreSQL can finally allocate enough memory by himself! :-E

>>	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.

> Fair enough, we can work on those. With 7.3.x you can tell PG to examine
> some tables more thouroughly to get better plans.

	You might EXPLAIN ANALYZE?

>>>>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. :)

>>	I think this is a important remark. Can "JOIN" significantly reduce
>>performance of SELECT statement relative to ", WHERE"?
>>	OK, I'm changed VIEW to this text:

> It can sometimes. What it means is that PG will follow whatever order you
> write the joins in. If you know joining a to b to c is the best order,
> that can be a good thing. Unfortunately, it means the planner can't make a
> better guess based on its statistics.

	At this moment this don't helps. :(

> Well the cost estimates look much more plausible. You couldn't post
> EXPLAIN ANALYSE could you? That actually runs the query.

>>	Now (2K shared_buffers blocks, 16K effective_cache_size blocks, 16Mb
>>sort_mem) PostgreSQL uses much less memory, about 64M... it's not good,
>>I want using all available RAM if possible - PostgreSQL is the main task
>>on this PC.

> Don't forget that any memory PG is using the operating-system can't. The
> OS will cache frequently accessed disk blocks for you, so it's a question
> of finding the right balance.

	PostgreSQL is the primary task for me on this PC - I don't worry about 
other tasks except OS. ;)

>>	May set effective_cache_size to 192M (48K blocks) be better? I don't
>>understand exactly: effective_cache_size tells PostgreSQL about OS cache
>>size or about available free RAM?

> It needs to reflect how much cache the system is using - try the "free"
> command to see figures.

	I'm not found "free" utility on FreeBSD 4.7. :(

> If you could post the output of EXPLAIN ANALYSE rather than EXPLAIN, I'll
> take a look at it this evening (London time). There's also plenty of other
> people on this list who can help too.

	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. :-(

> - Richard Huxton

With best regards
	Yaroslav Mazurak.




view thread (24+ messages)  latest in thread

Message-ID: <3F31FA33.1050009@lviv.bank.gov.ua>
Permalink:  ../3F31FA33.1050009@lviv.bank.gov.ua/
Also on:    postgresql.org/message-id/3F31FA33.1050009@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: <3F31FA33.1050009@lviv.bank.gov.ua>
  References: <3F30AF9E.2080607@lviv.bank.gov.ua> <200308061030.36610.dev@archonet.com> <3F30F7CF.6090900@lviv.bank.gov.ua> <49274.192.168.1.32.1060184304.squirrel@mainbox.archonet.com> <3F31FA33.1050009@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