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

* RES: Degradation of postgres 7.4.5 on FreeBSD/CygWin
@ 2005-02-18 15:43 Rodrigo Moreno <rodrigo.miguel@terra.com.br>
  2005-02-18 14:51 ` Re: RES: Degradation of postgres 7.4.5 on FreeBSD/CygWin Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2005-02-18 14:52 ` Re: RES: Degradation of postgres 7.4.5 on FreeBSD/CygWin Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2005-02-18 14:59 ` Re: RES: Degradation of postgres 7.4.5 on FreeBSD/CygWin Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 3 replies; 4+ messages in thread

From: Rodrigo Moreno @ 2005-02-18 15:43 UTC (permalink / raw)
  To: pgsql-performance

Hi,

this is only max 15 concurrent conections. And is not a heavy performance
database, so i think this is not necessary vacumm more than once a day.

In another customer, has only 5 users and the database have 300mb, small
database, and has the same behaviour (haven't modified postgresql).
My first instalation was not changed anything in postgresql.conf, but in
this new server (FreeBSD) i have changed some parameters.

as showed in my crontab list, i think this is enough:
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

These my changed configs in postgresql.conf:
max_connections = 30
shared_buffers = 8192
sort_mem = 32768
vacuum_mem = 32768
max_fsm_pages = 40000
max_fsm_relations = 2000

But why after 2 months the database has 1.3gb and after reimport on 900mb ?

Both customer are smaller databases, but one of them, has 8 years os data,
it's the reason of size 900mb, these are too smaller database.

Regards
Rodrigo Moreno




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

* Re: RES: Degradation of postgres 7.4.5 on FreeBSD/CygWin
  2005-02-18 15:43 RES: Degradation of postgres 7.4.5 on FreeBSD/CygWin Rodrigo Moreno <rodrigo.miguel@terra.com.br>
@ 2005-02-18 14:51 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2 siblings, 0 replies; 4+ messages in thread

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

> this is only max 15 concurrent conections. And is not a heavy performance
> database, so i think this is not necessary vacumm more than once a day.
> 
> In another customer, has only 5 users and the database have 300mb, small
> database, and has the same behaviour (haven't modified postgresql).
> My first instalation was not changed anything in postgresql.conf, but in
> this new server (FreeBSD) i have changed some parameters.
> 
> as showed in my crontab list, i think this is enough:
> 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;"

We just told you - it's nowhere near enough.  Vacuum once an hour.  Size 
of the database is not that relevant, its size of changes that is.

Chris



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

* Re: RES: Degradation of postgres 7.4.5 on FreeBSD/CygWin
  2005-02-18 15:43 RES: Degradation of postgres 7.4.5 on FreeBSD/CygWin Rodrigo Moreno <rodrigo.miguel@terra.com.br>
@ 2005-02-18 14:52 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2 siblings, 0 replies; 4+ messages in thread

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

> 00 23 * * 1-5 /usr/local/pgsql/bin/psql supre -c "vacuum analyze;"

Also, this is bad - you are not vacuuming all your databases, which will 
cause you data loss one day with transaction wraparound.  Use the 
vacuumdb utility that comes with PostgreSQL instead.

Chris



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

* Re: RES: Degradation of postgres 7.4.5 on FreeBSD/CygWin
  2005-02-18 15:43 RES: Degradation of postgres 7.4.5 on FreeBSD/CygWin Rodrigo Moreno <rodrigo.miguel@terra.com.br>
@ 2005-02-18 14:59 ` Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 0 replies; 4+ messages in thread

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

"Rodrigo Moreno" <rodrigo.miguel@terra.com.br> writes:
> max_fsm_pages = 40000
> max_fsm_relations = 2000

> But why after 2 months the database has 1.3gb and after reimport on 900mb ?

40k pages = 320M bytes = 1/3rd of your database.  Perhaps you need a
larger setting for max_fsm_pages.

However, 30% bloat of the database doesn't particularly bother me,
especially when you are using infrequent vacuums.  Bear in mind that,
for example, the steady-state fill factor of a b-tree index is usually
estimated at less than 70%.  A certain amount of wasted space is not
only intended, but essential for reasonable performance.

What you need is to take a more detailed look at the behavior of that
function that's getting so slow.  Are the query plans changing?  Is
the loop iterating over many more rows than before?  You haven't told
us anything that would account for 100x slowdown.

			regards, tom lane



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


end of thread, other threads:[~2005-02-18 15:43 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 15:43 RES: Degradation of postgres 7.4.5 on FreeBSD/CygWin Rodrigo Moreno <rodrigo.miguel@terra.com.br>
2005-02-18 14:51 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-02-18 14:52 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-02-18 14:59 ` Tom Lane <tgl@sss.pgh.pa.us>

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