[PERFORM] Tuning New Server (slow function)
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, 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; top shows: CPU states: cpuusernice systemirq softirq iowaitidle total5.8%0.6% 31.2% 0.0% 0.0%0.5% 61.6% Mem: 8152592k av, 8143012k used,9580k free, 0k shrd, 179888k buff 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 R24.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 = 7 work_mem = 9192 maintenance_work_mem = 131072 max_fsm_pages = 7 fsync = off (temporarily, will be turned back on) checkpoint_segments = 64 checkpoint_timeout = 1800 effective_cache_size = 7 [EMAIL PROTECTED] root]# cat /proc/sys/kernel/shmmax 66000 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 6: explain analyze is your friend
[PERFORM] Tuning New Server (slow function)
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, 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; top shows: CPU states: cpuusernice systemirq softirq iowaitidle total5.8%0.6% 31.2% 0.0% 0.0%0.5% 61.6% Mem: 8152592k av, 8143012k used,9580k free, 0k shrd, 179888k buff 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 R24.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 = 7 work_mem = 9192 maintenance_work_mem = 131072 max_fsm_pages = 7 fsync = off (temporarily, will be turned back on) checkpoint_segments = 64 checkpoint_timeout = 1800 effective_cache_size = 7 [EMAIL PROTECTED] root]# cat /proc/sys/kernel/shmmax 66000 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 5: don't forget to increase your free space map settings
Re: [PERFORM] Tuning New Server (slow function)
Ron St-Pierre [EMAIL PROTECTED] writes: 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, 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: A fairly common gotcha in updating is to forget to ANALYZE all your tables after loading the data into the new server. My bet is that some of the queries in the function are using bad plans for lack of up-to-date statistics. If ANALYZEing and then starting a fresh session (to get rid of the function's cached plans) doesn't help, you'll need to do some comparison of EXPLAIN plans between old and new server to try to figure out where the problem is. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] 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: cpuusernice systemirq softirq iowaitidle total5.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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] Tuning New Server (slow function)
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, 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 top shows: CPU states: cpuusernice systemirq softirq iowaitidle total5.8%0.6% 31.2% 0.0% 0.0%0.5% 61.6% Mem: 8152592k av, 8143012k used,9580k free, 0k shrd, 179888k buff 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 R24.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 = 7 work_mem = 9192 maintenance_work_mem = 131072 max_fsm_pages = 7 fsync = off (temporarily, will be turned back on) checkpoint_segments = 64 checkpoint_timeout = 1800 effective_cache_size = 7 [EMAIL PROTECTED] root]# cat /proc/sys/kernel/shmmax 66000 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
Re: [PERFORM] 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: cpuusernice systemirq softirq iowaitidle total5.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 R24.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 = 7 work_mem = 9192 maintenance_work_mem = 131072 max_fsm_pages = 7 fsync = off (temporarily, will be turned back on) checkpoint_segments = 64 checkpoint_timeout = 1800 effective_cache_size = 7 [EMAIL PROTECTED] root]# cat /proc/sys/kernel/shmmax 66000 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Tuning New Server (slow function)
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: cpuusernice systemirq softirq iowaitidle total5.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 R24.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 = 7 work_mem = 9192 maintenance_work_mem = 131072 max_fsm_pages = 7 fsync = off (temporarily, will be turned back on) checkpoint_segments = 64 checkpoint_timeout = 1800 effective_cache_size = 7 [EMAIL PROTECTED] root]# cat /proc/sys/kernel/shmmax 66000 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
