Re: PostgreSQL performance problem -> tuning

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.