pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Yaroslav Mazurak <yamazurak@Lviv.Bank.Gov.UA>
To: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL performance problem -> tuning
Date: Thu, 7 Aug 2003 15:52:48 +0100
Message-ID: <200308071552.48868.dev@archonet.com> (raw)
In-Reply-To: <3F320CB1.7040000@lviv.bank.gov.ua>
References: <49274.192.168.1.32.1060184304.squirrel@mainbox.archonet.com>
	<3F324F18.6307.4A8BB7A@localhost>
	<3F320CB1.7040000@lviv.bank.gov.ua>

On Thursday 07 August 2003 09:24, Yaroslav Mazurak wrote:
> > IIRC there is a limit on filesystem cache on freeBSD. 300MB by default.
> > If that is the case, you might have to raise it to make
> > effective_cache_size really effective..
>
> 	"Try various sysctls" says nothing for me. I want use *all available
> RAM* (of course, without needed for OS use) for PostgreSQL.

PG will be using the OS' disk caching.

> 	While idle time top says:
>
> Mem: 14M Active, 1944K Inact, 28M Wired, 436K Cache, 48M Buf, 331M Free
> Swap: 368M Total, 17M Used, 352M Free, 4% Inuse
>
> 	After 1 minute of "EXPLAIN ANALYZE SELECT SUM(showcalc('B00204', dd,
> r020, t071)) FROM v_file02wide WHERE a011 = 3 AND inrepdate(data) AND
> b030 IN (SELECT b030 FROM dov_bank WHERE dov_bank_box_22(box) IN ('NL',
> 'NM')) AND r030 = 980;" executing:
>
> Mem: 64M Active, 17M Inact, 72M Wired, 436K Cache, 48M Buf, 221M Free
> Swap: 368M Total, 3192K Used, 365M Free
>
>    PID USERNAME    PRI NICE  SIZE    RES STATE    TIME   WCPU    CPU
> COMMAND 59063 postgres     49   0 65560K 55492K RUN      1:06 94.93% 94.63%
> postgres
>
> 	After 12 minutes of query executing:
>
> Mem: 71M Active, 17M Inact, 72M Wired, 436K Cache, 48M Buf, 214M Free
> Swap: 368M Total, 3192K Used, 365M Free
>
>    PID USERNAME    PRI NICE  SIZE    RES STATE    TIME   WCPU    CPU
> COMMAND 59063 postgres     56   0 73752K 62996K RUN     12:01 99.02% 99.02%
> postgres
>
> 	I suspect that swap-file size is too small for my query... but query
> isn't too large, about 8K rows only. :-|

Looks fine - PG isn't growing too large and your swap usage seems steady. We 
can try upping the sort memory later, but given the amount of data you're 
dealing with I'd guess 64MB should be fine.

I think we're going to have to break the query down a little and see where the 
issue is.

What's the situation with:
EXPLAIN ANALYZE SELECT <some_field> FROM v_file02wide WHERE a011 = 3 AND 
inrepdate(data) AND b030 IN (SELECT b030 FROM dov_bank WHERE 
dov_bank_box_22(box) IN ('NL', 'NM')) AND r030 = 980;

and:
EXPLAIN ANALYZE SELECT SUM(showcalc(<parameters>)) FROM <something simple>

Hopefully one of these will run in a reasonable time, and the other will not. 
Then we can examine the slow query in more detail. Nothing from your previous 
EXPLAIN (email of yesterday 13:42) looks unreasonable but something must be 
going wild in the heart of the query, otherwise you wouldn't be here.

-- 
  Richard Huxton
  Archonet Ltd



view thread (24+ messages)  latest in thread

Message-ID: <200308071552.48868.dev@archonet.com>
Permalink:  ../200308071552.48868.dev@archonet.com/
Also on:    postgresql.org/message-id/200308071552.48868.dev@archonet.com

 · 

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: dev@archonet.com, yamazurak@Lviv.Bank.Gov.UA
  Subject: Re: PostgreSQL performance problem -> tuning
  In-Reply-To: <200308071552.48868.dev@archonet.com>
  References: <49274.192.168.1.32.1060184304.squirrel@mainbox.archonet.com> <3F324F18.6307.4A8BB7A@localhost> <3F320CB1.7040000@lviv.bank.gov.ua> <200308071552.48868.dev@archonet.com>

* 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