Re: PostgreSQL performance problem -> tuning

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