Re: Tuning New Server (slow function)

On Jun 21, 2006, at 5:53 PM, Ron St-Pierre wrote:
> 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...)

Just remember that unless you have a really good battery-backed
controller, writes to RAID5 pretty much suck.

>>> 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;
> How would I rewrite it to do away with the cursor?

Something like...

SELECT ...
FROM (SELECT a...., f.updatedate AS funddate, t.updatedate AS
techdate, max(updatedate) hist_date
FROM activeitem a
JOIN historical h USING itemid
GROUP BY a...., f.updatedate, t.updatedate) AS a
LEFT JOIN funddate f USING itemid
LEFT JOIN techsignals USING itemid
WHERE f.updatedate <> hist_date OR t.updatedate <> hist_date OR
f.updatedate IS NULL OR t.updatedate IS NULL
;

BTW, there's some trick that would let you include the NULL tests
with the other tests in the WHERE, but I can't remember it off the
top of my head...

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

Sure, but PostgreSQL still acquires internal locks.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461