Re: Tuning New Server (slow function)

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*?

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.

Of course, even better would be 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.

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

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