X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 112F1D1C4EB for ; Thu, 7 Aug 2003 14:53:00 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 87910-07 for ; Thu, 7 Aug 2003 11:51:39 -0300 (ADT) Received: from anchor-post-39.mail.demon.net (anchor-post-39.mail.demon.net [194.217.242.80]) by svr1.postgresql.org (Postfix) with ESMTP id 0862ED1C4E1 for ; Thu, 7 Aug 2003 11:52:49 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-39.mail.demon.net with esmtp (Exim 3.36 #2) id 19km8J-0006uf-0U; Thu, 07 Aug 2003 15:52:52 +0100 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 93B1916C74; Thu, 7 Aug 2003 15:52:50 +0100 (BST) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id B105B166BC; Thu, 7 Aug 2003 15:52:49 +0100 (BST) From: Richard Huxton To: Yaroslav Mazurak , pgsql-performance@postgresql.org Subject: Re: PostgreSQL performance problem -> tuning Date: Thu, 7 Aug 2003 15:52:48 +0100 User-Agent: KMail/1.5 References: <49274.192.168.1.32.1060184304.squirrel@mainbox.archonet.com> <3F324F18.6307.4A8BB7A@localhost> <3F320CB1.7040000@lviv.bank.gov.ua> In-Reply-To: <3F320CB1.7040000@lviv.bank.gov.ua> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200308071552.48868.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/133 X-Sequence-Number: 2950 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 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()) FROM 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