Date: Wed, 21 Jun 2006 17:21:42 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Ron St-Pierre <ron.pgsql@shaw.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: Tuning New Server (slow function)
Message-ID: <20060621222142.GO93655@pervasive.com>
References: <4499B9CD.4000500@shaw.ca>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4499B9CD.4000500@shaw.ca>
User-Agent: Mutt/1.5.11

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