pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAutovaccum
9+ messages / 6 participants
[nested] [flat]
* Autovaccum
@ 2006-11-30 14:02 Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Ezequias Rodrigues da Rocha @ 2006-11-30 14:02 UTC (permalink / raw)
To: pgsql-sql
Hi list,
I would like to know if it is necessary to set my database to
autovaccum if the intent of my DB Manager is do not make any deletion
in any time.
If there is no deletions why autovaccum ok ?
Regards
Ezequias
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Autovaccum
@ 2006-11-30 14:24 Alvaro Herrera <alvherre@commandprompt.com>
parent: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Alvaro Herrera @ 2006-11-30 14:24 UTC (permalink / raw)
To: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>; +Cc: pgsql-sql
Ezequias Rodrigues da Rocha wrote:
> Hi list,
>
> I would like to know if it is necessary to set my database to
> autovaccum if the intent of my DB Manager is do not make any deletion
> in any time.
>
> If there is no deletions why autovaccum ok ?
You need to vacuum from time to time anyway, even if you don't delete
anything. The easiest way to do it is let autovacuum do it for you.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Autovaccum
@ 2006-11-30 14:56 Peter Childs <peterachilds@gmail.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 1 reply; 9+ messages in thread
From: Peter Childs @ 2006-11-30 14:56 UTC (permalink / raw)
To: pgsql-sql
On 30/11/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Ezequias Rodrigues da Rocha wrote:
> > Hi list,
> >
> > I would like to know if it is necessary to set my database to
> > autovaccum if the intent of my DB Manager is do not make any deletion
> > in any time.
> >
> > If there is no deletions why autovaccum ok ?
>
> You need to vacuum from time to time anyway, even if you don't delete
> anything. The easiest way to do it is let autovacuum do it for you.
>
Complete Answer.
1. If you do any updates or deletes you need to vacuum.
2. If you do any update, deletes or inserts you need to analyse.
3. If you never change anything No Updates, No Inserts, No Deletes you
need to vacuum once ever 2 billion transactions due to wrap around if
you don't your data will disappear. But depending on how busy your
database is this could be once a year.
4. Auto Vacuum does all this automatically. Which is kind of useful.
5. You can do this manually via cron or by hand if you wish.
Peter.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Autovaccum
@ 2006-11-30 15:03 Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
parent: Peter Childs <peterachilds@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Ezequias Rodrigues da Rocha @ 2006-11-30 15:03 UTC (permalink / raw)
To: Peter Childs <peterachilds@gmail.com>; +Cc: pgsql-sql
It is possible to set this up on PgAdmin ?
I need to shutdown the postgresql service to change this parameters ?
ps: Your information was very clear for me. Thank you very much.
Ezequias
2006/11/30, Peter Childs <peterachilds@gmail.com>:
> On 30/11/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> > Ezequias Rodrigues da Rocha wrote:
> > > Hi list,
> > >
> > > I would like to know if it is necessary to set my database to
> > > autovaccum if the intent of my DB Manager is do not make any deletion
> > > in any time.
> > >
> > > If there is no deletions why autovaccum ok ?
> >
> > You need to vacuum from time to time anyway, even if you don't delete
> > anything. The easiest way to do it is let autovacuum do it for you.
> >
>
> Complete Answer.
>
> 1. If you do any updates or deletes you need to vacuum.
>
> 2. If you do any update, deletes or inserts you need to analyse.
>
> 3. If you never change anything No Updates, No Inserts, No Deletes you
> need to vacuum once ever 2 billion transactions due to wrap around if
> you don't your data will disappear. But depending on how busy your
> database is this could be once a year.
>
> 4. Auto Vacuum does all this automatically. Which is kind of useful.
>
> 5. You can do this manually via cron or by hand if you wish.
>
> Peter.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Autovaccum
@ 2006-11-30 15:10 Shane Ambler <pgsql@007Marketing.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 1 reply; 9+ messages in thread
From: Shane Ambler @ 2006-11-30 15:10 UTC (permalink / raw)
To: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>; pgsql-sql; alvherre@commandprompt.com
Alvaro Herrera wrote:
> Ezequias Rodrigues da Rocha wrote:
>> Hi list,
>>
>> I would like to know if it is necessary to set my database to
>> autovaccum if the intent of my DB Manager is do not make any deletion
>> in any time.
>>
>> If there is no deletions why autovaccum ok ?
>
> You need to vacuum from time to time anyway, even if you don't delete
> anything. The easiest way to do it is let autovacuum do it for you.
>
One thing that vacuum/autovacuum does is mark space used by deleted rows
to be reused. Without deletes this won't be necessary in table data
files. But when you update a record an index may also be updated and
have the same effect within the index storage space.
There are other things that vacuum does to keep your database running
optimally. One is to update planner statistics about how many rows are
in each table which effects the query planning and optimizing.
Without deletes a plain vacuum won't achieve a great deal, but a regular
VACUUM ANALYZE (as done by autovacuum) will make a difference to the
performance of your database.
If no data in your db changes then you won't have to bother vacuuming.
--
Shane Ambler
pgSQL@007Marketing.com
Get Sheeky @ http://Sheeky.Biz
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Autovaccum
@ 2006-11-30 18:08 Matthew T. O'Connor <matthew@zeut.net>
parent: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Matthew T. O'Connor @ 2006-11-30 18:08 UTC (permalink / raw)
To: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>; +Cc: Peter Childs <peterachilds@gmail.com>; pgsql-sql
Ezequias Rodrigues da Rocha wrote:
> It is possible to set this up on PgAdmin ?
I don't know.
> I need to shutdown the postgresql service to change this parameters ?
No, you can just modify the params in postgresql.conf and HUP the server
so that it re-reads the config file, no restart required.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Autovaccum
@ 2006-11-30 18:11 Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
parent: Matthew T. O'Connor <matthew@zeut.net>
0 siblings, 1 reply; 9+ messages in thread
From: Ezequias Rodrigues da Rocha @ 2006-11-30 18:11 UTC (permalink / raw)
To: Matthew T. O'Connor <matthew@zeut.net>; +Cc: Peter Childs <peterachilds@gmail.com>; pgsql-sql
Just a little question.
What is the interval of time the vacuum will run on my database ?
Ezequias
2006/11/30, Matthew T. O'Connor <matthew@zeut.net>:
> Ezequias Rodrigues da Rocha wrote:
> > It is possible to set this up on PgAdmin ?
>
> I don't know.
>
> > I need to shutdown the postgresql service to change this parameters ?
>
> No, you can just modify the params in postgresql.conf and HUP the server
> so that it re-reads the config file, no restart required.
>
>
>
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Autovaccum
@ 2006-12-01 17:06 Shane Ambler <pgsql@007Marketing.com>
parent: Shane Ambler <pgsql@007Marketing.com>
0 siblings, 0 replies; 9+ messages in thread
From: Shane Ambler @ 2006-12-01 17:06 UTC (permalink / raw)
To: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>; +Cc: pgsql-sql
Ezequias Rodrigues da Rocha wrote:
> Could you tell me if only this both options are ok (attach) ?
>
> If I don't mark the interval of vacuuns what will be the interval of
> each vacuum ?
The default settings may be fine for you, it depends a bit on how many
insert/updates you get in a given time frame.
http://www.postgresql.org/docs/8.1/interactive/runtime-config-autovacuum.html
will give a definition of each parameter that can be set.
http://www.postgresql.org/docs/8.1/interactive/maintenance.html#AUTOVACUUM
will explain in more detail.
Basically every autovacuum_naptime seconds autovacuum looks at estimates
of how much the database has changed since the last run. If the amount
of change is greater than the thresholds determined from the other
settings then a vacuum/analyze will be done.
If you have 100 records added/updated per day then you may only need it
to run 1 or 2 times a day using smaller thresholds.
If you are adding 10,000 records an hour then you will want it to run
more often.
> 2006/11/30, Shane Ambler <pgsql@007marketing.com>:
>> Alvaro Herrera wrote:
>> > Ezequias Rodrigues da Rocha wrote:
>> >> Hi list,
>> >>
>> >> I would like to know if it is necessary to set my database to
>> >> autovaccum if the intent of my DB Manager is do not make any deletion
>> >> in any time.
>> >>
>> >> If there is no deletions why autovaccum ok ?
>> >
>> > You need to vacuum from time to time anyway, even if you don't delete
>> > anything. The easiest way to do it is let autovacuum do it for you.
>> >
>>
>> One thing that vacuum/autovacuum does is mark space used by deleted rows
>> to be reused. Without deletes this won't be necessary in table data
>> files. But when you update a record an index may also be updated and
>> have the same effect within the index storage space.
>>
>> There are other things that vacuum does to keep your database running
>> optimally. One is to update planner statistics about how many rows are
>> in each table which effects the query planning and optimizing.
>>
>> Without deletes a plain vacuum won't achieve a great deal, but a regular
>> VACUUM ANALYZE (as done by autovacuum) will make a difference to the
>> performance of your database.
>>
>> If no data in your db changes then you won't have to bother vacuuming.
>>
>> --
>>
>> Shane Ambler
>> pgSQL@007Marketing.com
>>
>> Get Sheeky @ http://Sheeky.Biz
>>
>
>
--
Shane Ambler
pgSQL@007Marketing.com
Get Sheeky @ http://Sheeky.Biz
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Autovaccum
@ 2006-12-19 14:23 Markus Schaber <schabi@logix-tt.com>
parent: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Markus Schaber @ 2006-12-19 14:23 UTC (permalink / raw)
To: pgsql-sql
Hi, Ezequias,
"Ezequias Rodrigues da Rocha" <ezequias.rocha@gmail.com> wrote:
> What is the interval of time the vacuum will run on my database ?
That depends on your settings and load.
Autovacuum looks at every table, and checks the number of
modifications, as well as the total number of transactions since the
last vacuum, and decides whether to VACUUM and/or ANALYZE.
The thresholds for those settings can be configured per table, with the
defaults in the postgresql.conf.
Regards,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2006-12-19 14:23 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-30 14:02 Autovaccum Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2006-11-30 14:24 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-11-30 14:56 ` Peter Childs <peterachilds@gmail.com>
2006-11-30 15:03 ` Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2006-11-30 18:08 ` Matthew T. O'Connor <matthew@zeut.net>
2006-11-30 18:11 ` Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2006-12-19 14:23 ` Markus Schaber <schabi@logix-tt.com>
2006-11-30 15:10 ` Shane Ambler <pgsql@007Marketing.com>
2006-12-01 17:06 ` Shane Ambler <pgsql@007Marketing.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