From: Ron St-Pierre <ron.pgsql@shaw.ca>
To: pgsql-performance@postgresql.org
Subject: Tuning New Server (slow function)
Date: Wed, 21 Jun 2006 14:27:41 -0700
Message-ID: <4499B9CD.4000500@shaw.ca> (raw)
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: 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
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ron.pgsql@shaw.ca
Subject: Re: Tuning New Server (slow function)
In-Reply-To: <4499B9CD.4000500@shaw.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox