Re: Tuning New Server (slow function) - Mailing list pgsql-performance

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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461