Skip site navigation (1) Skip section navigation (2)

Tuning New Server (slow function)

From: Ron St-Pierre <ronDOTpgsql(at)shaw(dot)ca>
To: pgsql-performance(at)postgresql(dot)org
Subject: Tuning New Server (slow function)
Date: 2006-06-21 15:37:51
Message-ID: 449967CF.6070105@shaw.ca (view raw or flat)
Thread:
Lists: pgsql-performance
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:  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(at)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

pgsql-performance by date

Next:From: Csaba NagyDate: 2006-06-21 15:42:21
Subject: Re: Help tuning autovacuum - seeing lots of relationbloat
Previous:From: Sven GeislerDate: 2006-06-21 15:37:07
Subject: Re: Speeding up query, Joining 55mil and 43mil records.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group