pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedautovacuum on a -mostly- r/o table
17+ messages / 9 participants
[nested] [flat]
* autovacuum on a -mostly- r/o table
@ 2006-09-27 16:08 Edoardo Ceccarelli <eddy@axa.it>
2006-09-27 16:13 ` Re: autovacuum on a -mostly- r/o table Alan Hodgson <ahodgson@simkin.ca>
2006-09-27 16:13 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Tobias Brox <tobias@nordicbet.com>
2006-09-27 16:13 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Csaba Nagy <nagy@ecircle-ag.com>
2006-09-27 16:14 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Bill Moran <wmoran@collaborativefusion.com>
2006-09-27 16:31 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Rod Taylor <pg@rbt.ca>
0 siblings, 5 replies; 17+ messages in thread
From: Edoardo Ceccarelli @ 2006-09-27 16:08 UTC (permalink / raw)
To: pgsql-performance; +Cc: pgsql-admin@postgresql.org
Hello,
we are running a 7.3 postgres db with only a big table (avg
500.000records) and 7 indexes for a search engine.
we have 2 of this databases and we can switch from one to another.
Last week we decided to give a try to 8.1 on one of them and everything
went fine, db is faster (about 2 or 3 times in our case) and the server
load is higher - which should mean that faster response time is achieved
by taking a better use of the server.
We also activated the autovacuum feature to give it a try and that's
were our problems started.
I left the standard autovacuum configuration just to wait and see, pg
decided to start a vacuum on the table just midday when users were
launching search queries on the table and server load reached a very
high value so that in a couple of minutes the db was unusable
With pg7.3 we use to vacuum the db night time, mostly because the insert
and updates in this table is made in a batch way: a single task that
puts 100.000 records in the db in 10/20minutes, so the best time to
actually vacuum the db would be after this batch.
I have read that autovacuum cannot check to see pg load before launching
vacuum but is there any patch about it? that would sort out the problem
in a good and simple way.
Otherwise, which kind of set of parameters I should put in autovacuum
configuration? I am stuck because in our case the table gets mostly read
and if I set up things as to vacuum the table after a specific amount of
insert/updates, I cannot foresee whether this could happen during
daytime when server is under high load.
How can I configure the vacuum to run after the daily batch insert/update?
Any help appreciated
Thank you very much
Edoardo
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum on a -mostly- r/o table
2006-09-27 16:08 autovacuum on a -mostly- r/o table Edoardo Ceccarelli <eddy@axa.it>
@ 2006-09-27 16:13 ` Alan Hodgson <ahodgson@simkin.ca>
4 siblings, 0 replies; 17+ messages in thread
From: Alan Hodgson @ 2006-09-27 16:13 UTC (permalink / raw)
To: pgsql-performance
On Wednesday 27 September 2006 09:08, Edoardo Ceccarelli <eddy@axa.it>
wrote:
>
> How can I configure the vacuum to run after the daily batch
> insert/update?
>
If you really only want it to run then, you should disable autovacuum and
continue to run the vacuum manually.
You might also investigate the vacuum cost delay options, which will make
vacuum take longer but will have less of an impact on your database while
running.
--
"If a nation values anything more than freedom, it will lose its freedom;
and the irony of it is that if it is comfort or money that it values more,
it will lose that too." -- Somerset Maugham, Author
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [PERFORM] autovacuum on a -mostly- r/o table
2006-09-27 16:08 autovacuum on a -mostly- r/o table Edoardo Ceccarelli <eddy@axa.it>
@ 2006-09-27 16:13 ` Tobias Brox <tobias@nordicbet.com>
4 siblings, 0 replies; 17+ messages in thread
From: Tobias Brox @ 2006-09-27 16:13 UTC (permalink / raw)
To: Edoardo Ceccarelli <eddy@axa.it>; +Cc: pgsql-performance; pgsql-admin@postgresql.org
[Edoardo Ceccarelli - Wed at 06:08:30PM +0200]
> We also activated the autovacuum feature to give it a try and that's
> were our problems started.
(...)
> How can I configure the vacuum to run after the daily batch insert/update?
I think you shouldn't use autovacuum in your case.
We haven't dared testing out autovacuum yet even though we probably
should, so we're running vacuum at fixed times of the day. We have a
very simple script to do this, the most important part of it reads:
echo "vacuum verbose analyze;" | psql $DB_NAME > $logdir/$filename 2>&1
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [PERFORM] autovacuum on a -mostly- r/o table
2006-09-27 16:08 autovacuum on a -mostly- r/o table Edoardo Ceccarelli <eddy@axa.it>
@ 2006-09-27 16:13 ` Csaba Nagy <nagy@ecircle-ag.com>
2006-09-27 18:33 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Matthew T. O'Connor <matthew@zeut.net>
4 siblings, 1 reply; 17+ messages in thread
From: Csaba Nagy @ 2006-09-27 16:13 UTC (permalink / raw)
To: Edoardo Ceccarelli <eddy@axa.it>; +Cc: pgsql-performance; pgsql-admin@postgresql.org
On Wed, 2006-09-27 at 18:08, Edoardo Ceccarelli wrote:
> How can I configure the vacuum to run after the daily batch insert/update?
Check out this:
http://www.postgresql.org/docs/8.1/static/catalog-pg-autovacuum.html
By inserting the right row you can disable autovacuum to vacuum your big
tables, and then you can schedule vacuum nightly for those just as
before. There's still a benefit in that you don't need to care about
vacuuming the rest of the tables, which will be done just in time.
Cheers,
Csaba.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [PERFORM] autovacuum on a -mostly- r/o table
2006-09-27 16:08 autovacuum on a -mostly- r/o table Edoardo Ceccarelli <eddy@axa.it>
2006-09-27 16:13 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Csaba Nagy <nagy@ecircle-ag.com>
@ 2006-09-27 18:33 ` Matthew T. O'Connor <matthew@zeut.net>
0 siblings, 0 replies; 17+ messages in thread
From: Matthew T. O'Connor @ 2006-09-27 18:33 UTC (permalink / raw)
To: Csaba Nagy <nagy@ecircle-ag.com>; +Cc: Edoardo Ceccarelli <eddy@axa.it>; pgsql-performance; pgsql-admin@postgresql.org
Csaba Nagy wrote:
> On Wed, 2006-09-27 at 18:08, Edoardo Ceccarelli wrote:
>
>> How can I configure the vacuum to run after the daily batch insert/update?
>>
>
> Check out this:
> http://www.postgresql.org/docs/8.1/static/catalog-pg-autovacuum.html
>
> By inserting the right row you can disable autovacuum to vacuum your big
> tables, and then you can schedule vacuum nightly for those just as
> before. There's still a benefit in that you don't need to care about
> vacuuming the rest of the tables, which will be done just in time.
In addition autovacuum respects the work of manual or cron based
vacuums, so if you issue a vacuum right after a daily batch insert /
update, autovacuum won't repeat the work of that manual vacuum.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [PERFORM] autovacuum on a -mostly- r/o table
2006-09-27 16:08 autovacuum on a -mostly- r/o table Edoardo Ceccarelli <eddy@axa.it>
@ 2006-09-27 16:14 ` Bill Moran <wmoran@collaborativefusion.com>
2006-09-27 16:40 ` Re: autovacuum on a -mostly- r/o table Edoardo Ceccarelli <eddy@axa.it>
4 siblings, 1 reply; 17+ messages in thread
From: Bill Moran @ 2006-09-27 16:14 UTC (permalink / raw)
To: Edoardo Ceccarelli <eddy@axa.it>; +Cc: pgsql-performance; pgsql-admin@postgresql.org
In response to Edoardo Ceccarelli <eddy@axa.it>:
> Hello,
>
> we are running a 7.3 postgres db with only a big table (avg
> 500.000records) and 7 indexes for a search engine.
> we have 2 of this databases and we can switch from one to another.
> Last week we decided to give a try to 8.1 on one of them and everything
> went fine, db is faster (about 2 or 3 times in our case) and the server
> load is higher - which should mean that faster response time is achieved
> by taking a better use of the server.
>
> We also activated the autovacuum feature to give it a try and that's
> were our problems started.
> I left the standard autovacuum configuration just to wait and see, pg
> decided to start a vacuum on the table just midday when users were
> launching search queries on the table and server load reached a very
> high value so that in a couple of minutes the db was unusable
>
> With pg7.3 we use to vacuum the db night time, mostly because the insert
> and updates in this table is made in a batch way: a single task that
> puts 100.000 records in the db in 10/20minutes, so the best time to
> actually vacuum the db would be after this batch.
>
> I have read that autovacuum cannot check to see pg load before launching
> vacuum but is there any patch about it? that would sort out the problem
> in a good and simple way.
> Otherwise, which kind of set of parameters I should put in autovacuum
> configuration? I am stuck because in our case the table gets mostly read
> and if I set up things as to vacuum the table after a specific amount of
> insert/updates, I cannot foresee whether this could happen during
> daytime when server is under high load.
> How can I configure the vacuum to run after the daily batch insert/update?
It doesn't sound as if your setup is a good match for autovacuum. You
might be better off going back to the cron vacuums. That's the
beauty of Postgres -- it gives you the choice.
If you want to continue with autovac, you may want to experiment with
vacuum_cost_delay and associated parameters, which can lessen the
impact of vacuuming.
--
Bill Moran
Collaborative Fusion Inc.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum on a -mostly- r/o table
2006-09-27 16:08 autovacuum on a -mostly- r/o table Edoardo Ceccarelli <eddy@axa.it>
2006-09-27 16:14 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Bill Moran <wmoran@collaborativefusion.com>
@ 2006-09-27 16:40 ` Edoardo Ceccarelli <eddy@axa.it>
0 siblings, 0 replies; 17+ messages in thread
From: Edoardo Ceccarelli @ 2006-09-27 16:40 UTC (permalink / raw)
To: Bill Moran <wmoran@collaborativefusion.com>; +Cc: pgsql-performance
Bill Moran wrote:
> In response to Edoardo Ceccarelli <eddy@axa.it>:
>>
>> I have read that autovacuum cannot check to see pg load before launching
>> vacuum but is there any patch about it? that would sort out the problem
>> in a good and simple way.
>> Otherwise, which kind of set of parameters I should put in autovacuum
>> configuration? I am stuck because in our case the table gets mostly read
>> and if I set up things as to vacuum the table after a specific amount of
>> insert/updates, I cannot foresee whether this could happen during
>> daytime when server is under high load.
>> How can I configure the vacuum to run after the daily batch insert/update?
>>
>
> It doesn't sound as if your setup is a good match for autovacuum. You
> might be better off going back to the cron vacuums. That's the
> beauty of Postgres -- it gives you the choice.
>
> If you want to continue with autovac, you may want to experiment with
> vacuum_cost_delay and associated parameters, which can lessen the
> impact of vacuuming.
>
>
The db is constantly monitored during high peak so that we can switch to
a backup pg7.3 database that is being vacuumed every night.
This is giving me the opportunity to try it so I tried this:
vacuum_cost_delay = 200
vacuum_cost_page_hit = 5
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20
vacuum_cost_limit = 100
I know these values affect the normal vacuum process but apparently this
means setting
#autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
# autovac, -1 means use
# vacuum_cost_delay
and
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovac, -1 means use
# vacuum_cost_limit
for the rest of them I am currently trying the deafults:
#autovacuum_naptime = 60 # time between autovacuum runs,
in secs
#autovacuum_vacuum_threshold = 1000 # min # of tuple updates before
vacuum
#autovacuum_analyze_threshold = 500 # min # of tuple updates before
analyze
#autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before vacuum
#autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before
analyze
Does anybody know which process is actually AUTO-vacuum-ing the db?
So that I can check when is running...
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [PERFORM] autovacuum on a -mostly- r/o table
2006-09-27 16:08 autovacuum on a -mostly- r/o table Edoardo Ceccarelli <eddy@axa.it>
@ 2006-09-27 16:31 ` Rod Taylor <pg@rbt.ca>
2006-09-27 16:49 ` Re: autovacuum on a -mostly- r/o table Edoardo Ceccarelli <eddy@axa.it>
2006-09-27 20:27 ` Re: autovacuum on a -mostly- r/o table Bill Moran <wmoran@collaborativefusion.com>
4 siblings, 2 replies; 17+ messages in thread
From: Rod Taylor @ 2006-09-27 16:31 UTC (permalink / raw)
To: Edoardo Ceccarelli <eddy@axa.it>; +Cc: pgsql-performance; pgsql-admin@postgresql.org
On Wed, 2006-09-27 at 18:08 +0200, Edoardo Ceccarelli wrote:
>
> I have read that autovacuum cannot check to see pg load before
> launching
> vacuum but is there any patch about it? that would sort out the
> problem
> in a good and simple way.
In some cases the solution to high load is to vacuum the tables being
hit the heaviest -- meaning that simply checking machine load isn't
enough to make that decision.
In fact, that high load problem is exactly why autovacuum was created in
the first place.
--
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum on a -mostly- r/o table
2006-09-27 16:08 autovacuum on a -mostly- r/o table Edoardo Ceccarelli <eddy@axa.it>
2006-09-27 16:31 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Rod Taylor <pg@rbt.ca>
@ 2006-09-27 16:49 ` Edoardo Ceccarelli <eddy@axa.it>
2006-09-27 16:53 ` Re: autovacuum on a -mostly- r/o table Tobias Brox <tobias@nordicbet.com>
1 sibling, 1 reply; 17+ messages in thread
From: Edoardo Ceccarelli @ 2006-09-27 16:49 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
Rod Taylor wrote:
> On Wed, 2006-09-27 at 18:08 +0200, Edoardo Ceccarelli wrote:
>
>> I have read that autovacuum cannot check to see pg load before
>> launching
>> vacuum but is there any patch about it? that would sort out the
>> problem
>> in a good and simple way.
>>
>
> In some cases the solution to high load is to vacuum the tables being
> hit the heaviest -- meaning that simply checking machine load isn't
> enough to make that decision.
>
> In fact, that high load problem is exactly why autovacuum was created in
> the first place.
>
True,
but autovacuum could check load -before- and -during- it's execution and
it could adjust himself automatically to perform more or less
aggressively depending on the difference between those two values.
Maybe with a parameter like: maximum-autovacuum-load=0.2
that would mean: "never load the machine more than 20% for the autovacuum"
...another thing is, how could autovacuum check for machine load, this
is something I cannot imagine right now...
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum on a -mostly- r/o table
2006-09-27 16:08 autovacuum on a -mostly- r/o table Edoardo Ceccarelli <eddy@axa.it>
2006-09-27 16:31 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Rod Taylor <pg@rbt.ca>
2006-09-27 16:49 ` Re: autovacuum on a -mostly- r/o table Edoardo Ceccarelli <eddy@axa.it>
@ 2006-09-27 16:53 ` Tobias Brox <tobias@nordicbet.com>
0 siblings, 0 replies; 17+ messages in thread
From: Tobias Brox @ 2006-09-27 16:53 UTC (permalink / raw)
To: Edoardo Ceccarelli <eddy@axa.it>; +Cc: pgsql-performance
[Edoardo Ceccarelli - Wed at 06:49:23PM +0200]
> ...another thing is, how could autovacuum check for machine load, this
> is something I cannot imagine right now...
One solution I made for our application, is to check the
pg_stats_activity view. It requires some config to get the stats
available in that view, though. When the application is to start a
low-priority transaction, it will first do:
select count(*) from pg_stat_activity where current_query not like
'<IDL%' and query_start+?<now();
if the returned value is high, the application will sleep a bit and try
again later.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum on a -mostly- r/o table
2006-09-27 16:08 autovacuum on a -mostly- r/o table Edoardo Ceccarelli <eddy@axa.it>
2006-09-27 16:31 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Rod Taylor <pg@rbt.ca>
@ 2006-09-27 20:27 ` Bill Moran <wmoran@collaborativefusion.com>
2006-09-28 08:44 ` Re: autovacuum on a -mostly- r/o table Edoardo Ceccarelli <eddy@axa.it>
1 sibling, 1 reply; 17+ messages in thread
From: Bill Moran @ 2006-09-27 20:27 UTC (permalink / raw)
To: Edoardo Ceccarelli <eddy@axa.it>; +Cc: pgsql-performance
In response to Edoardo Ceccarelli <eddy@axa.it>:
> Rod Taylor wrote:
> > On Wed, 2006-09-27 at 18:08 +0200, Edoardo Ceccarelli wrote:
> >
> >> I have read that autovacuum cannot check to see pg load before
> >> launching
> >> vacuum but is there any patch about it? that would sort out the
> >> problem
> >> in a good and simple way.
> >>
> >
> > In some cases the solution to high load is to vacuum the tables being
> > hit the heaviest -- meaning that simply checking machine load isn't
> > enough to make that decision.
> >
> > In fact, that high load problem is exactly why autovacuum was created in
> > the first place.
> >
> True,
> but autovacuum could check load -before- and -during- it's execution and
> it could adjust himself automatically to perform more or less
> aggressively depending on the difference between those two values.
> Maybe with a parameter like: maximum-autovacuum-load=0.2
> that would mean: "never load the machine more than 20% for the autovacuum"
This is pretty non-trivial. How do you define 20% load? 20% of the
CPU? Does that mean that it's OK for autovac to use 3% cpu and 100% of
your IO? Ok, so we need to calculate an average of IO and CPU -- which
disks? If your WAL logs are on one disk, and you've used tablespaces
to spread the rest of your DB across different partitions, it can
be pretty difficult to determine which IO parameters you want to take
into consideration.
--
Bill Moran
Collaborative Fusion Inc.
****************************************************************
IMPORTANT: This message contains confidential information and is
intended only for the individual named. If the reader of this
message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.
****************************************************************
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum on a -mostly- r/o table
2006-09-27 16:08 autovacuum on a -mostly- r/o table Edoardo Ceccarelli <eddy@axa.it>
2006-09-27 16:31 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Rod Taylor <pg@rbt.ca>
2006-09-27 20:27 ` Re: autovacuum on a -mostly- r/o table Bill Moran <wmoran@collaborativefusion.com>
@ 2006-09-28 08:44 ` Edoardo Ceccarelli <eddy@axa.it>
0 siblings, 0 replies; 17+ messages in thread
From: Edoardo Ceccarelli @ 2006-09-28 08:44 UTC (permalink / raw)
To: pgsql-performance
>> True,
>> but autovacuum could check load -before- and -during- it's execution and
>> it could adjust himself automatically to perform more or less
>> aggressively depending on the difference between those two values.
>> Maybe with a parameter like: maximum-autovacuum-load=0.2
>> that would mean: "never load the machine more than 20% for the autovacuum"
>>
>
> This is pretty non-trivial. How do you define 20% load? 20% of the
> CPU? Does that mean that it's OK for autovac to use 3% cpu and 100% of
> your IO? Ok, so we need to calculate an average of IO and CPU -- which
> disks? If your WAL logs are on one disk, and you've used tablespaces
> to spread the rest of your DB across different partitions, it can
> be pretty difficult to determine which IO parameters you want to take
> into consideration.
>
>
As I said before, it could be done, the main requirement is to find a
way for pg to check for a value of the system load; of course it has to
be an average value between disk and cpu, of course the daemon would
have to collect sample of this values continuously, and of course
everything would be better if the server it's only running Postgres
Still I think you are right, it wouldn't suit exactly every situations
but it could be an "emergency" feature:
What happened to me was very clear:
server running pg8 under heavy load, cpu's were 90% idle as usual.
At some point the vacuum started, the server reached 50 of overall load
and cpu's were 1% idle
I think any test can detect such a situation, regardles if load it's
more I/O based or CPU based
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [PERFORM] autovacuum on a -mostly- r/o table
@ 2006-10-15 09:52 Tobias Brox <tobias@nordicbet.com>
2006-10-15 14:42 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Matthew T. O'Connor <matthew@zeut.net>
0 siblings, 1 reply; 17+ messages in thread
From: Tobias Brox @ 2006-10-15 09:52 UTC (permalink / raw)
To: Matthew T. O'Connor <matthew@zeut.net>; +Cc: Csaba Nagy <nagy@ecircle-ag.com>; Edoardo Ceccarelli <eddy@axa.it>; pgsql-performance; pgsql-admin@postgresql.org
[Matthew T. O'Connor - Wed at 02:33:10PM -0400]
> In addition autovacuum respects the work of manual or cron based
> vacuums, so if you issue a vacuum right after a daily batch insert /
> update, autovacuum won't repeat the work of that manual vacuum.
I was experimenting a bit with autovacuum now. To make the least effect
possible, I started with a too high cost_delay/cost_limit-ratio. The
effect of this was that autovacuum "never" finished the transactions it
started with, and this was actually causing the nightly vacuum to not do
it's job good enough.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [PERFORM] autovacuum on a -mostly- r/o table
2006-10-15 09:52 Re: [PERFORM] autovacuum on a -mostly- r/o table Tobias Brox <tobias@nordicbet.com>
@ 2006-10-15 14:42 ` Matthew T. O'Connor <matthew@zeut.net>
2006-10-15 14:52 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Tobias Brox <tobias@nordicbet.com>
0 siblings, 1 reply; 17+ messages in thread
From: Matthew T. O'Connor @ 2006-10-15 14:42 UTC (permalink / raw)
To: Tobias Brox <tobias@nordicbet.com>; +Cc: Csaba Nagy <nagy@ecircle-ag.com>; Edoardo Ceccarelli <eddy@axa.it>; pgsql-performance; pgsql-admin@postgresql.org
Tobias Brox wrote:
> [Matthew T. O'Connor - Wed at 02:33:10PM -0400]
>
>> In addition autovacuum respects the work of manual or cron based
>> vacuums, so if you issue a vacuum right after a daily batch insert /
>> update, autovacuum won't repeat the work of that manual vacuum.
>>
>
> I was experimenting a bit with autovacuum now. To make the least effect
> possible, I started with a too high cost_delay/cost_limit-ratio. The
> effect of this was that autovacuum "never" finished the transactions it
> started with, and this was actually causing the nightly vacuum to not do
> it's job good enough.
Yeah, I think if the delay settings are too high it can cause problems,
that's part of the reason we have yet to turn these on be default since
we won't have enough data to suggest good values. Can you tell us what
settings you finally settled on?
BTW hopefully for 8.3 we are going to add the concept of maintenance
windows to autovacuum, during these periods you can lower the thresholds
and perhaps even change the delay settings to make autovacuum more
aggressive during the maintenance window. This hopefully will just
about eliminate the need for nightly cron based vacuum runs.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [PERFORM] autovacuum on a -mostly- r/o table
2006-10-15 09:52 Re: [PERFORM] autovacuum on a -mostly- r/o table Tobias Brox <tobias@nordicbet.com>
2006-10-15 14:42 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Matthew T. O'Connor <matthew@zeut.net>
@ 2006-10-15 14:52 ` Tobias Brox <tobias@nordicbet.com>
2006-10-18 19:10 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Jim C. Nasby <jim@nasby.net>
0 siblings, 1 reply; 17+ messages in thread
From: Tobias Brox @ 2006-10-15 14:52 UTC (permalink / raw)
To: Matthew T. O'Connor <matthew@zeut.net>; +Cc: Tobias Brox <tobias@nordicbet.com>; Csaba Nagy <nagy@ecircle-ag.com>; Edoardo Ceccarelli <eddy@axa.it>; pgsql-performance; pgsql-admin@postgresql.org
[Matthew T. O'Connor - Sun at 10:42:34AM -0400]
> Yeah, I think if the delay settings are too high it can cause problems,
> that's part of the reason we have yet to turn these on be default since
> we won't have enough data to suggest good values. Can you tell us what
> settings you finally settled on?
I'm still not yet settled, and the project manager is critical to
autovacuum (adds complexity, no obvious benefits from it, we see from
the CPU graphs that it's causing iowait, iowait is bad). We're going to
run autovacuum biweekly now to see what effect it has on the server
load.
I've been using the cost/delay-setting of 200/200 for a week now, and
I'm going to continue with 100/150 for a while.
Are there any known disadvantages of lowering both values to the extreme
- say, 20/20 instead of 200/200? That would efficiently mean "sleep as
often as possible, and sleep for 1 ms for each cost unit spent" if
I've understood the system right.
Are there any logs that can help me, and eventually, are there any
ready-made scripts for checking when autovacuum is running, and
eventually for how long it keeps its transactions? I'll probably write
up something myself if not.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [PERFORM] autovacuum on a -mostly- r/o table
2006-10-15 09:52 Re: [PERFORM] autovacuum on a -mostly- r/o table Tobias Brox <tobias@nordicbet.com>
2006-10-15 14:42 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Matthew T. O'Connor <matthew@zeut.net>
2006-10-15 14:52 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Tobias Brox <tobias@nordicbet.com>
@ 2006-10-18 19:10 ` Jim C. Nasby <jim@nasby.net>
2006-10-18 20:59 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Larry Rosenman <ler@lerctr.org>
0 siblings, 1 reply; 17+ messages in thread
From: Jim C. Nasby @ 2006-10-18 19:10 UTC (permalink / raw)
To: Tobias Brox <tobias@nordicbet.com>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; Csaba Nagy <nagy@ecircle-ag.com>; Edoardo Ceccarelli <eddy@axa.it>; pgsql-performance; pgsql-admin@postgresql.org
On Sun, Oct 15, 2006 at 04:52:12PM +0200, Tobias Brox wrote:
> Are there any logs that can help me, and eventually, are there any
> ready-made scripts for checking when autovacuum is running, and
> eventually for how long it keeps its transactions? I'll probably write
> up something myself if not.
8.2 adds some stats on when autovac last ran, per-table. I don't
remember if it reports how long it took to vacuum the table, but that
would probably be useful info.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [PERFORM] autovacuum on a -mostly- r/o table
2006-10-15 09:52 Re: [PERFORM] autovacuum on a -mostly- r/o table Tobias Brox <tobias@nordicbet.com>
2006-10-15 14:42 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Matthew T. O'Connor <matthew@zeut.net>
2006-10-15 14:52 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Tobias Brox <tobias@nordicbet.com>
2006-10-18 19:10 ` Re: [PERFORM] autovacuum on a -mostly- r/o table Jim C. Nasby <jim@nasby.net>
@ 2006-10-18 20:59 ` Larry Rosenman <ler@lerctr.org>
0 siblings, 0 replies; 17+ messages in thread
From: Larry Rosenman @ 2006-10-18 20:59 UTC (permalink / raw)
To: 'Jim C. Nasby' <jim@nasby.net>; 'Tobias Brox' <tobias@nordicbet.com>; +Cc: 'Matthew T. O'Connor' <matthew@zeut.net>; 'Csaba Nagy' <nagy@ecircle-ag.com>; 'Edoardo Ceccarelli' <eddy@axa.it>; pgsql-performance; pgsql-admin@postgresql.org
Jim C. Nasby wrote:
> On Sun, Oct 15, 2006 at 04:52:12PM +0200, Tobias Brox wrote:
>> Are there any logs that can help me, and eventually, are there any
>> ready-made scripts for checking when autovacuum is running, and
>> eventually for how long it keeps its transactions? I'll probably
>> write up something myself if not.
>
> 8.2 adds some stats on when autovac last ran, per-table. I don't
> remember if it reports how long it took to vacuum the table, but that
> would probably be useful info.
It does NOT. It's just the timestamp of the END of the vacuum / analyze.
(I'm the author of the patch).
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 512-248-2683 E-Mail: ler@lerctr.org
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2006-10-18 20:59 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-27 16:08 autovacuum on a -mostly- r/o table Edoardo Ceccarelli <eddy@axa.it>
2006-09-27 16:13 ` Alan Hodgson <ahodgson@simkin.ca>
2006-09-27 16:13 ` Tobias Brox <tobias@nordicbet.com>
2006-09-27 16:13 ` Csaba Nagy <nagy@ecircle-ag.com>
2006-09-27 18:33 ` Matthew T. O'Connor <matthew@zeut.net>
2006-09-27 16:14 ` Bill Moran <wmoran@collaborativefusion.com>
2006-09-27 16:40 ` Edoardo Ceccarelli <eddy@axa.it>
2006-09-27 16:31 ` Rod Taylor <pg@rbt.ca>
2006-09-27 16:49 ` Edoardo Ceccarelli <eddy@axa.it>
2006-09-27 16:53 ` Tobias Brox <tobias@nordicbet.com>
2006-09-27 20:27 ` Bill Moran <wmoran@collaborativefusion.com>
2006-09-28 08:44 ` Edoardo Ceccarelli <eddy@axa.it>
2006-10-15 09:52 Re: [PERFORM] autovacuum on a -mostly- r/o table Tobias Brox <tobias@nordicbet.com>
2006-10-15 14:42 ` Matthew T. O'Connor <matthew@zeut.net>
2006-10-15 14:52 ` Tobias Brox <tobias@nordicbet.com>
2006-10-18 19:10 ` Jim C. Nasby <jim@nasby.net>
2006-10-18 20:59 ` Larry Rosenman <ler@lerctr.org>
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