From: Ron St-Pierre <ron.pgsql@shaw.ca>
To: Jim C. Nasby <jnasby@pervasive.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Tuning New Server (slow function)
Date: Wed, 21 Jun 2006 15:53:06 -0700
Message-ID: <4499CDD2.2070900@shaw.ca> (raw)
In-Reply-To: <20060621222142.GO93655@pervasive.com>
References: <4499B9CD.4000500@shaw.ca>
<20060621222142.GO93655@pervasive.com>
Jim C. Nasby wrote:
> On Wed, Jun 21, 2006 at 02:27:41PM -0700, Ron St-Pierre wrote:
>
>> We just purchased a new Dell PowerEdge 2800 (dual xeon, 8GB RAM, raid 4,
>> RHEL, postgres 8.1) and ported our old database over to it (single cpu,
>>
>
> RAID *4*?
>
oops, raid 5 (but we are getting good io throughput...)
> If you do any kind of updating at all, you're likely to be real unhappy
> with that...
>
>
>> 2GB RAM, no raid, postgres 7.4). Our apps perform great on it, however
>> some queries are super slow. One function in particular, which used to
>> take 15-30 minutes on the old server, has been running now for over 12
>> hours:
>> BEGIN
>> TRUNCATE stock.datacount;
>> FOR rec IN SELECT itemID, item, hexValue FROM stock.activeitem LOOP
>> histdate := (SELECT updatedate FROM stock.historical s WHERE
>> s.itemID=rec.itemID ORDER BY updatedate DESC LIMIT 1);
>> IF histdate IS NOT NULL THEN
>> funddate := (SELECT updatedate FROM stock.funddata s WHERE
>> s.itemID=rec.itemID);
>> techdate := (SELECT updatedate FROM stock.techsignals s
>> WHERE s.itemID=rec.itemID);
>> IF (histdate <> funddate) OR (histdate <> techdate) OR
>> (funddate IS NULL) OR (techdate IS NULL) THEN
>> counter := counter + 1;
>> outrec.itemID := rec.itemID;
>> outrec.item := rec.item;
>> outrec.hexvalue := rec.hexvalue;
>> RETURN NEXT outrec;
>> END IF;
>> END IF;
>> END LOOP;
>> INSERT INTO stock.datacount (itemcount) VALUES (counter);
>> COPY stock.datacount TO ''/tmp/datacount'';
>> RETURN;
>> END;
>>
>> note: stock.activeitem contains about 75000 rows
>>
>
> Getting EXPLAIN ANALYZE from the queries would be good. Adding debug
> output via NOTICE to see how long each step is taking would be a good
> idea, too.
>
>
I set client_min_messages = debug2, log_min_messages = debug2 and
log_statement = 'all' and am running the query with EXPLAIN ANALYZE. I
don't know how long it will take until something useful returns, but I
will let it run for a while.
> Of course, even better would be to do away with the cursor...
>
>
How would I rewrite it to do away with the cursor?
>> "top" shows:
>> CPU states: cpu user nice system irq softirq iowait idle
>> total 5.8% 0.6% 31.2% 0.0% 0.0% 0.5% 61.6%
>> Mem: 8152592k av, 8143012k used, 9580k free, 0k shrd, 179888k
>> buff
>>
>
> The high system % (if I'm reading this correctly) makes me wonder if
> this is some kind of locking issue.
>
>
But it's the only postgres process running.
>> 6342296k actv, 1206340k in_d, 137916k in_c
>> Swap: 8385760k av, 259780k used, 8125980k free 7668624k
>> cached
>>
>> PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
>> 17027 postgres 25 0 566M 561M 560M R 24.9 7.0 924:34 1
>> postmaster
>>
>> I've likely set some parameter(s) to the wrong values, but I don't know
>> which one(s). Here are my relevant postgresql.conf settings:
>> shared_buffers = 70000
>> work_mem = 9192
>> maintenance_work_mem = 131072
>> max_fsm_pages = 70000
>> fsync = off (temporarily, will be turned back on)
>> checkpoint_segments = 64
>> checkpoint_timeout = 1800
>> effective_cache_size = 70000
>>
>> [root@new-server root]# cat /proc/sys/kernel/shmmax
>> 660000000
>>
>> We want to put this into production soon, but this is a showstopper. Can
>> anyone help me out with this?
>>
>>
>> Thanks
>>
>> Ron St.Pierre
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faq
>>
>>
>
>
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ron.pgsql@shaw.ca, jnasby@pervasive.com
Subject: Re: Tuning New Server (slow function)
In-Reply-To: <4499CDD2.2070900@shaw.ca>
* 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