Degradation of postgres 7.4.5 on FreeBSD/CygWin - Mailing list pgsql-performance

Hi All

I'm really desparate about this. The problem has occurried in both of my
customers first with cygwin and now with FreeBSD 5.3.

After 2 months, postgres start get down the performance, and simple queries
that should run in 100ms now tooks about 15 secs.

Another behaviour, the data is growing to much, with no reason, just like
the comparision.

So, to solve problem, for the 5th time, a made a backup, dropped the entire
database, recreate e reimported.

One friend of mine tell me about same problem in linux and he go back to
7.3.x, and with me 5 times.

The old data have this sizes:

$ du -ks * | sort -nr
1379872 base
131202  pg_xlog
390     global
336     serverlog
74      pg_clog
8       postgresql.conf
4       pg_hba.conf
2       postmaster.opts
2       pg_ident.conf
2       PG_VERSION

The Reimported database has this sizes:
$ du -ks * | sort -nr
916496  base
131202  pg_xlog
134     global
14      serverlog
10      pg_clog
8       postgresql.conf
4       pg_hba.conf
2       postmaster.pid
2       postmaster.opts
2       pg_ident.conf
2       PG_VERSION


This Procedure took 100 ms, but before re-import it took about 15secs, in a
process that have a 1000 itens its took about 4 hours to finish, and after
re-import 5 minutes.

The bottleneck is this recursion procedure, that is a part os others
procedure, but it have a simple query.

CREATE OR REPLACE FUNCTION Produt_Repos(numeric, double precision, integer,
integer) RETURNS double precision AS '
DECLARE
    xcodpro ALIAS FOR $1;
    xPesfor ALIAS FOR $2;
    xAno    ALIAS FOR $3;
    xMes    ALIAS FOR $4;
    oMatpro RECORD;
    xPreRep DOUBLE PRECISION;
    nPreRep DOUBLE PRECISION;
    xQtdKgs DOUBLE PRECISION;
    xPreCus DOUBLE PRECISION;
BEGIN
    xPreRep := 0;

    IF xPesFor <> 0 THEN
        FOR oMatpro IN SELECT a.qtdpro, a.codmat, b.pesfor
                         FROM matpro a, produt b
                        WHERE a.codpro = xCodpro
                          AND b.codpro = a.codmat LOOP

        xQtdKgs := oMatpro.QtdPro / xPesFor;
        nPreRep := Produt_Repos( oMatpro.codmat, coalesce(oMatpro.pesfor, 0.0),
xAno, xMes);
            xPrerep := xPrerep + (nPreRep * xQtdKgs);

            IF nPreRep = 0 THEN
        SELECT coalesce(PreCus, 0.0) INTO xPreCus FROM produt_fecha WHERE codpro =
oMatPro.codmat and ano = xAno and mes = xMes LIMIT 1;
                xPreRep := xPrerep + ( xPrecus * xQtdKgs );
            END IF;
        END LOOP;
    END IF;

    RETURN xPreRep;
END;
' LANGUAGE 'plpgsql';



This are my configs:

msginfo:
        msgmax:  16384  (max characters in a message)
        msgmni:     40  (# of message queues)
        msgmnb:   2048  (max characters in a message queue)
        msgtql:     40  (max # of messages in system)
        msgssz:      8  (size of a message segment)
        msgseg:   2048  (# of message segments in system)

shminfo:
        shmmax: 163840000       (max shared memory segment size)
        shmmin:       1 (min shared memory segment size)
        shmmni:    4000 (max number of shared memory identifiers)
        shmseg:     128 (max shared memory segments per process)
        shmall:   40000 (max amount of shared memory in pages)

seminfo:
        semmap:     30  (# of entries in semaphore map)
        semmni:  40961  (# of semaphore identifiers)
        semmns:  16380  (# of semaphores in system)
        semmnu:     30  (# of undo structures in system)
        semmsl:  16380  (max # of semaphores per id)
        semopm:    100  (max # of operations per semop call)
        semume:     10  (max # of undo entries per process)
        semusz:     92  (size in bytes of undo structure)
        semvmx:  32767  (semaphore maximum value)
        semaem:  16384  (adjust on exit max value)


max_connections = 30
shared_buffers = 8192           # min 16, at least max_connections*2, 8KB
each
sort_mem = 32768                # min 64, size in KB
vacuum_mem = 32768              # min 1024, size in KB
max_fsm_pages = 40000           # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 2000        # min 100, ~50 bytes each

These are my crontab activities:

$ crontab -l
00 13 * * 1-5 /bin/sh /home/postgres/backup.sh >/dev/null 2>&1
00 19 * * 1-5 /bin/sh /home/postgres/backup.sh >/dev/null 2>&1
00 23 * * 1-5 /usr/local/pgsql/bin/psql supre -c "vacuum analyze;"
>>/dev/null 2>&1
00 23 * * 6 /usr/local/pgsql/bin/psql supre -c "reindex database supre;"
>>/dev/null 2>&1
00 23 * * 7 /usr/local/pgsql/bin/psql supre -c "vacuum full analyze;"
>>/dev/null 2>&1


So guys, i'm really desparate about this issue, and i think i'm doing
everthing right. Please help me.

If i tell to my customer that he is having the same problem that in cygwin
version, after spending money to change from windows to freebsd,upgrading
server, etc, problably he will kill me. :)

Best Regards
Rodrigo Moreno