pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: Degradation of postgres 7.4.5 on FreeBSD/CygWin
4+ messages / 3 participants
[nested] [flat]

* Re: Degradation of postgres 7.4.5 on FreeBSD/CygWin
@ 2005-02-18 14:08  Steinar H. Gunderson <sgunderson@bigfoot.com>
  parent: Rodrigo Moreno <rodrigo.miguel@terra.com.br>
  1 sibling, 0 replies; 4+ messages in thread

From: Steinar H. Gunderson @ 2005-02-18 14:08 UTC (permalink / raw)
  To: pgsql-performance

On Fri, Feb 18, 2005 at 11:54:34AM -0300, Rodrigo Moreno wrote:
> 00 23 * * 1-5 /usr/local/pgsql/bin/psql supre -c "vacuum analyze;"
>>>/dev/null 2>&1

Isn't vacuum once a day a bit too little with heavy activity? You should
probably consider autovacuum.

> 00 23 * * 6 /usr/local/pgsql/bin/psql supre -c "reindex database supre;"
>>>/dev/null 2>&1

REINDEX DATABASE does (AFAIK) only index the indexes on the system tables in
the database.

/* Steinar */
-- 
Homepage: http://www.sesse.net/



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Degradation of postgres 7.4.5 on FreeBSD/CygWin
@ 2005-02-18 14:32  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Rodrigo Moreno <rodrigo.miguel@terra.com.br>
  1 sibling, 1 reply; 4+ messages in thread

From: Tom Lane @ 2005-02-18 14:32 UTC (permalink / raw)
  To: Rodrigo Moreno <rodrigo.miguel@terra.com.br>; +Cc: pgsql-performance

"Rodrigo Moreno" <rodrigo.miguel@terra.com.br> writes:
> 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.

Are you vacuuming on a regular basis?  Do you have the FSM settings high
enough to cover the database?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Degradation of postgres 7.4.5 on FreeBSD/CygWin
@ 2005-02-18 14:40  Steinar H. Gunderson <sgunderson@bigfoot.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Steinar H. Gunderson @ 2005-02-18 14:40 UTC (permalink / raw)
  To: pgsql-performance

On Fri, Feb 18, 2005 at 09:32:25AM -0500, Tom Lane wrote:
> Are you vacuuming on a regular basis?  Do you have the FSM settings high
> enough to cover the database?

He posted his cron settings ;-)

/* Steinar */
-- 
Homepage: http://www.sesse.net/



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Degradation of postgres 7.4.5 on FreeBSD/CygWin
@ 2005-02-18 14:54  Rodrigo Moreno <rodrigo.miguel@terra.com.br>
  0 siblings, 2 replies; 4+ messages in thread

From: Rodrigo Moreno @ 2005-02-18 14:54 UTC (permalink / raw)
  To: 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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2005-02-18 14:54 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-18 14:54 Degradation of postgres 7.4.5 on FreeBSD/CygWin Rodrigo Moreno <rodrigo.miguel@terra.com.br>
2005-02-18 14:08 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2005-02-18 14:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-18 14:40   ` Steinar H. Gunderson <sgunderson@bigfoot.com>

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