pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Vacuum is needed or not?
7+ messages / 5 participants
[nested] [flat]

* Vacuum is needed or not?
@ 2006-01-05 12:23 Szabolcs BALLA <szabolcs.balla@confinsystems.com>
  2006-01-13 04:21 ` Re: Vacuum is needed or not? Jaime Casanova <systemguards@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Szabolcs BALLA @ 2006-01-05 12:23 UTC (permalink / raw)
  To: pgsql-admin

Hi, 

How do I know vacuum is need or not? I mean, are there any query to know
status of statistics, tables, indexes, etc? 
(7.4.7)
Our database size bigger than just go and do it. :(

Thanks
Szabek

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

* Re: Vacuum is needed or not?
  2006-01-05 12:23 Vacuum is needed or not? Szabolcs BALLA <szabolcs.balla@confinsystems.com>
@ 2006-01-13 04:21 ` Jaime Casanova <systemguards@gmail.com>
  2006-01-13 19:18   ` Re: Vacuum is needed or not? Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Jaime Casanova @ 2006-01-13 04:21 UTC (permalink / raw)
  To: Szabolcs BALLA <szabolcs.balla@confinsystems.com>; +Cc: pgsql-admin

On 1/5/06, Szabolcs BALLA <szabolcs.balla@confinsystems.com> wrote:
> Hi,
>
> How do I know vacuum is need or not? I mean, are there any query to know
> status of statistics, tables, indexes, etc?

at least, once every billion transactions to be safe...

> (7.4.7)
> Our database size bigger than just go and do it. :(
>

ahh... then execute it once a week or once a day, for avoid excessive growing...

> Thanks
> Szabek
>
>


--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)



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

* Re: Vacuum is needed or not?
  2006-01-05 12:23 Vacuum is needed or not? Szabolcs BALLA <szabolcs.balla@confinsystems.com>
  2006-01-13 04:21 ` Re: Vacuum is needed or not? Jaime Casanova <systemguards@gmail.com>
@ 2006-01-13 19:18   ` Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Jim C. Nasby @ 2006-01-13 19:18 UTC (permalink / raw)
  To: Jaime Casanova <systemguards@gmail.com>; +Cc: Szabolcs BALLA <szabolcs.balla@confinsystems.com>; pgsql-admin

On Thu, Jan 12, 2006 at 11:21:19PM -0500, Jaime Casanova wrote:
> On 1/5/06, Szabolcs BALLA <szabolcs.balla@confinsystems.com> wrote:
> > Hi,
> >
> > How do I know vacuum is need or not? I mean, are there any query to know
> > status of statistics, tables, indexes, etc?
> 
> at least, once every billion transactions to be safe...
> 
> > (7.4.7)
> > Our database size bigger than just go and do it. :(
> >
> 
> ahh... then execute it once a week or once a day, for avoid excessive growing...

Even once a day can be awefully conservative for many databases. Keep in
mind that every single row that is updated or deleted will hang around
in the database until you vacuum; on a busy system that can result in a
lot of wasted space. And once the bloat is there, it can be hard to get
rid of.

7.4.7 has contrib/pg_autovacuum, and I highly recommend it. Also,
http://lnk.nu/pervasive-postgres.com/7m4.asp and
http://lnk.nu/pervasive-postgres.com/7jd.asp might be interesting reads
for you.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



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

* Vacuum is needed or not?
@ 2006-01-05 13:12 Szabolcs BALLA <szballa@confinsystems.com>
  2006-01-05 14:37 ` Re: Vacuum is needed or not? Jaime Casanova <systemguards@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Szabolcs BALLA @ 2006-01-05 13:12 UTC (permalink / raw)
  To: pgsql-admin

Hi, 

How do I know vacuum is need or not? I mean, are there any query to know
status of statistics, tables, indexes, etc? 
(7.4.7)
Our database size bigger than just go and do it. :(

Thanks,
Szabek

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

* Re: Vacuum is needed or not?
  2006-01-05 13:12 Vacuum is needed or not? Szabolcs BALLA <szballa@confinsystems.com>
@ 2006-01-05 14:37 ` Jaime Casanova <systemguards@gmail.com>
  2006-01-05 15:48   ` Re: Vacuum is needed or not? Jaime Casanova <systemguards@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Jaime Casanova @ 2006-01-05 14:37 UTC (permalink / raw)
  To: Szabolcs BALLA <szballa@confinsystems.com>; +Cc: pgsql-admin

On 1/5/06, Szabolcs BALLA <szballa@confinsystems.com> wrote:
> Hi,
>
> How do I know vacuum is need or not? I mean, are there any query to know
> status of statistics, tables, indexes, etc?
> (7.4.7)
> Our database size bigger than just go and do it. :(
>
> Thanks,
> Szabek

VACUUM is needed at least every billion transactions...

execute it in a script periodically... if you are in doubt if you need
VACUUM then you need it urgently, execute it right now!!!

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)



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

* Re: Vacuum is needed or not?
  2006-01-05 13:12 Vacuum is needed or not? Szabolcs BALLA <szballa@confinsystems.com>
  2006-01-05 14:37 ` Re: Vacuum is needed or not? Jaime Casanova <systemguards@gmail.com>
@ 2006-01-05 15:48   ` Jaime Casanova <systemguards@gmail.com>
  2006-01-05 17:30     ` Re: Vacuum is needed or not? Guido Barosio <gbarosio@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Jaime Casanova @ 2006-01-05 15:48 UTC (permalink / raw)
  To: Szabolcs BALLA <szballa@confinsystems.com>; +Cc: pgsql-admin

On 1/5/06, Szabolcs BALLA <szballa@confinsystems.com> wrote:
>
>
VACUUM is needed at least every billion transactions...

execute it in a
> script periodically... if you are in doubt if you need
VACUUM then you need
> it urgently, execute it right now!!!

>
> yes, and i running every weekend a full analyze, but actually i think it's
> need again, but just for feel i couldn't stop the server.
> I need evidence. (oldspice rulez ;))
>
> Szabek

a plain VACUUM (without full) is enough in most cases... and you don't
need to stop the server for a plain VACUUM...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)



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

* Re: Vacuum is needed or not?
  2006-01-05 13:12 Vacuum is needed or not? Szabolcs BALLA <szballa@confinsystems.com>
  2006-01-05 14:37 ` Re: Vacuum is needed or not? Jaime Casanova <systemguards@gmail.com>
  2006-01-05 15:48   ` Re: Vacuum is needed or not? Jaime Casanova <systemguards@gmail.com>
@ 2006-01-05 17:30     ` Guido Barosio <gbarosio@gmail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Guido Barosio @ 2006-01-05 17:30 UTC (permalink / raw)
  To: Jaime Casanova <systemguards@gmail.com>; +Cc: Szabolcs BALLA <szballa@confinsystems.com>; pgsql-admin

http://www.postgresql.org/docs/8.0/interactive/maintenance.html

Read all, in order to understand the following quote:

"

Since periodic VACUUM runs are needed anyway for the reasons described
earlier, it's unlikely that any table would not be vacuumed for as long as a
billion transactions. But to help administrators ensure this constraint is
met, VACUUM stores transaction ID statistics in the system table pg_database.
In particular, the datfrozenxid column of a database's pg_database row is
updated at the completion of any database-wide VACUUM operation (i.e.,
VACUUM that does not name a specific table). The value stored in this field
is the freeze cutoff XID that was used by that VACUUM command. All normal
XIDs older than this cutoff XID are guaranteed to have been replaced by
FrozenXID within that database. A convenient way to examine this information
is to execute the query

SELECT datname, age(datfrozenxid) FROM pg_database;

"

Best wishes,
Guido


On 1/5/06, Jaime Casanova <systemguards@gmail.com> wrote:
>
> On 1/5/06, Szabolcs BALLA <szballa@confinsystems.com> wrote:
> >
> >
> VACUUM is needed at least every billion transactions...
>
> execute it in a
> > script periodically... if you are in doubt if you need
> VACUUM then you need
> > it urgently, execute it right now!!!
>
> >
> > yes, and i running every weekend a full analyze, but actually i think
> it's
> > need again, but just for feel i couldn't stop the server.
> > I need evidence. (oldspice rulez ;))
> >
> > Szabek
>
> a plain VACUUM (without full) is enough in most cases... and you don't
> need to stop the server for a plain VACUUM...
>
> --
> regards,
> Jaime Casanova
> (DBA: DataBase Aniquilator ;)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>



--
"Adopting the position that you are smarter than an automaticoptimization
algorithm is generally a good way to achieve lessperformance, not more" -
Tom Lane.

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


end of thread, other threads:[~2006-01-13 19:18 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-05 12:23 Vacuum is needed or not? Szabolcs BALLA <szabolcs.balla@confinsystems.com>
2006-01-13 04:21 ` Jaime Casanova <systemguards@gmail.com>
2006-01-13 19:18   ` Jim C. Nasby <jnasby@pervasive.com>
2006-01-05 13:12 Vacuum is needed or not? Szabolcs BALLA <szballa@confinsystems.com>
2006-01-05 14:37 ` Jaime Casanova <systemguards@gmail.com>
2006-01-05 15:48   ` Jaime Casanova <systemguards@gmail.com>
2006-01-05 17:30     ` Guido Barosio <gbarosio@gmail.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