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 11:24:17 +0300
Message-ID: <3F320CB1.7040000@lviv.bank.gov.ua> (raw)
In-Reply-To: <3F324F18.6307.4A8BB7A@localhost>
References: <49274.192.168.1.32.1060184304.squirrel@mainbox.archonet.com>
	<3F324F18.6307.4A8BB7A@localhost>

		Hi All!

Shridhar Daithankar wrote:

> On 7 Aug 2003 at 10:05, Yaroslav Mazurak wrote:

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

> <rant>
> Grr.. I don't like freeBSD for it's top output.Active/inactive/Wired.. Grr.. 
> why can't it be shared buffered and cached? Same goes for HP-UX top. Looking at 
> it one gets hardly any real information.. Anyway that's just me..
> </rant>

	Grr... I don't like PostgreSQL for it's memory usage parameters. In 
Sybase ASA, I say for example: "use 64Mb RAM for cache". I don't worry 
about data in this cache - this may be queries, sort areas, results etc. 
I think that server know better about it's memory requirements. I know 
that Sybase *use*, and use *only this* memory and don't trap with 
"Memory exhausted" error.
	I'm not remember 700 minutes queries (more complex that my query), 
following with "memory exhausted" error, on Sybase.
	Advertising, he? :(

>  Top on freeBSD seems pretty unintuituive em but if you find any documentation 
> on that, that would help you. (Haven't booted in freeBSD in ages so no data 
> out of my head..)

> You can try various sysctls on freeBSD. Basicalyl idea is to find out how much 
> of memory is used and how much is cached. FreeBSD must be providing that one in 
> some form..

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

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

>  Shridhar

With best regards
	Yaroslav Mazurak.




view thread (24+ messages)  latest in thread

Message-ID: <3F320CB1.7040000@lviv.bank.gov.ua>
Permalink:  ../3F320CB1.7040000@lviv.bank.gov.ua/
Also on:    postgresql.org/message-id/3F320CB1.7040000@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: <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>

* 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