pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMore Autovacuum Questions
8+ messages / 5 participants
[nested] [flat]
* More Autovacuum Questions
@ 2006-05-16 18:00 Chris Hoover <revoohc@gmail.com>
2006-05-16 18:09 ` Re: More Autovacuum Questions Gourish Singbal <gourish@gmail.com>
2006-05-16 18:40 ` Re: More Autovacuum Questions Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 2 replies; 8+ messages in thread
From: Chris Hoover @ 2006-05-16 18:00 UTC (permalink / raw)
To: pgsql-admin
Thanks to those who answered my first question. I am looking at
implementing autovacuuming on our new production 8.1.3 systems. I am
reading the documentation on the various configuration parameters. The
autovacuum_analyze_threshold and autovacuum_vacuum_threshold talk about the
number of updated and deleted tuples. Is there anyway to calculate these
numbers in a running database?
The reason I'm asking is that we are having some sporadic slowness issues
with our databases, and I'm wanting to see how the database looks during
these times. I know that vacuuming some specific tables tends to fix the
problem. So, if I can see what autovacuum might see, I could set the
parameters correctly to try and fix the issue.
Anyway, are these number available to me?
Thanks,
Chris
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: More Autovacuum Questions
2006-05-16 18:00 More Autovacuum Questions Chris Hoover <revoohc@gmail.com>
@ 2006-05-16 18:09 ` Gourish Singbal <gourish@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Gourish Singbal @ 2006-05-16 18:09 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin
U can install pgstattuple module which is available in the contrib directory
and run it on production as
psql> \x
psql> select * from pgstattuple('public'.'tablename');
it displays the table_len, tuple_count, tuple_len, tuple_percent,
dead_tuple_count, dead_tuple_len,dead_tuple_percent,free_space,free_percent
for the table .
On 5/16/06, Chris Hoover <revoohc@gmail.com> wrote:
>
> Thanks to those who answered my first question. I am looking at
> implementing autovacuuming on our new production 8.1.3 systems. I am
> reading the documentation on the various configuration parameters. The
> autovacuum_analyze_threshold and autovacuum_vacuum_threshold talk about the
> number of updated and deleted tuples. Is there anyway to calculate these
> numbers in a running database?
>
> The reason I'm asking is that we are having some sporadic slowness issues
> with our databases, and I'm wanting to see how the database looks during
> these times. I know that vacuuming some specific tables tends to fix the
> problem. So, if I can see what autovacuum might see, I could set the
> parameters correctly to try and fix the issue.
>
> Anyway, are these number available to me?
>
> Thanks,
>
>
> Chris
>
--
Best,
Gourish Singbal
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: More Autovacuum Questions
2006-05-16 18:00 More Autovacuum Questions Chris Hoover <revoohc@gmail.com>
@ 2006-05-16 18:40 ` Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 0 replies; 8+ messages in thread
From: Jim C. Nasby @ 2006-05-16 18:40 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin
On Tue, May 16, 2006 at 02:00:21PM -0400, Chris Hoover wrote:
> Thanks to those who answered my first question. I am looking at
> implementing autovacuuming on our new production 8.1.3 systems. I am
> reading the documentation on the various configuration parameters. The
> autovacuum_analyze_threshold and autovacuum_vacuum_threshold talk about the
> number of updated and deleted tuples. Is there anyway to calculate these
> numbers in a running database?
>
> The reason I'm asking is that we are having some sporadic slowness issues
> with our databases, and I'm wanting to see how the database looks during
> these times. I know that vacuuming some specific tables tends to fix the
> problem. So, if I can see what autovacuum might see, I could set the
> parameters correctly to try and fix the issue.
Your best bet is to turn up your logging verbosity, so that you actually
get more detailed info from autovacuum. Short of that, you can monitor
those tables in pg_stat_user_tables; in particular you need to watch
n_tup_upd and n_tup_del.
As for the autovacuum settings, I've found that the default threasholds
and scale factors are too big by at least a factor of 2. By default, it
will allow a relation to grow to 40% dead rows before vacuuming, which
is a pretty decent amount of waisted space.
Finally, there are certain tables that aren't well suited to autovacuum,
namely any tables that see a very high rate of updates or deletes. For
these tables I usually schedule vacuums via crontab. For tables that
should be kept small (ie: queue tables), I'll even go as far as
vacuuming once a minute, or more often, since these tables should stay
small enough for vacuum to be very fast.
--
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] 8+ messages in thread
* More Autovacuum questions
@ 2008-07-15 13:19 Chris Hoover <revoohc@gmail.com>
2008-07-15 13:52 ` Re: More Autovacuum questions Alvaro Herrera <alvherre@commandprompt.com>
2008-07-15 15:12 ` Re: More Autovacuum questions Matthew T. O'Connor <matthew@zeut.net>
0 siblings, 2 replies; 8+ messages in thread
From: Chris Hoover @ 2008-07-15 13:19 UTC (permalink / raw)
To: pgsql-admin
Another question.
When autovacuum sleeps, does it release the lock it has on the table?
What we would like to have happen is for vacuum to work for a while, sleep,
and while it is sleeping run an analyze on the table. We need this due to
how quickly our data is changing. Currently we are running analyze every 5
minutes against the table of question and get a very serious performance hit
when vacuum runs due to the index stats getting out of date. As soon as we
rerun analyze after the vacuum happiness returns.
The pg_total_relation_size for the table in question is 64GB, and as stated
above is a very heavily used and modifed table.
Thanks for any comments,
Chris
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: More Autovacuum questions
2008-07-15 13:19 More Autovacuum questions Chris Hoover <revoohc@gmail.com>
@ 2008-07-15 13:52 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 8+ messages in thread
From: Alvaro Herrera @ 2008-07-15 13:52 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin
Chris Hoover escribió:
> Another question.
>
> When autovacuum sleeps, does it release the lock it has on the table?
What do you mean sleep? There are two sleeps: the one caused by
cost-based vacuum delay (lasts for some milliseconds, happens every
handful of pages vacuumed; no locks released here) and the big sleep
that happens after one worker finishes and the next one starts (all
locks are released).
When one autovac worker is processing a table, no other worker touches
it. So if you have a table that's 64 GB, it won't be analyzed until
the currently running vacuum finishes.
> What we would like to have happen is for vacuum to work for a while, sleep,
> and while it is sleeping run an analyze on the table. We need this due to
> how quickly our data is changing. Currently we are running analyze every 5
> minutes against the table of question and get a very serious performance hit
> when vacuum runs due to the index stats getting out of date. As soon as we
> rerun analyze after the vacuum happiness returns.
This is worst case for Postgres vacuuming, I think.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: More Autovacuum questions
2008-07-15 13:19 More Autovacuum questions Chris Hoover <revoohc@gmail.com>
@ 2008-07-15 15:12 ` Matthew T. O'Connor <matthew@zeut.net>
2008-07-15 22:36 ` Re: More Autovacuum questions Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 1 reply; 8+ messages in thread
From: Matthew T. O'Connor @ 2008-07-15 15:12 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin
Chris Hoover wrote:
> Another question.
>
> When autovacuum sleeps, does it release the lock it has on the table?
>
> What we would like to have happen is for vacuum to work for a while,
> sleep, and while it is sleeping run an analyze on the table. We need
> this due to how quickly our data is changing. Currently we are running
> analyze every 5 minutes against the table of question and get a very
> serious performance hit when vacuum runs due to the index stats getting
> out of date. As soon as we rerun analyze after the vacuum happiness
> returns.
>
> The pg_total_relation_size for the table in question is 64GB, and as
> stated above is a very heavily used and modifed table.
As Alvaro already said this is a case where autovacuum still isn't
great. Perhaps what you should do here is disable auto-vacumming of the
table during busy periods and and only have it vacuum at off peak times?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: More Autovacuum questions
2008-07-15 13:19 More Autovacuum questions Chris Hoover <revoohc@gmail.com>
2008-07-15 15:12 ` Re: More Autovacuum questions Matthew T. O'Connor <matthew@zeut.net>
@ 2008-07-15 22:36 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-07-16 05:27 ` Re: More Autovacuum questions Matthew T. O'Connor <matthew@zeut.net>
0 siblings, 1 reply; 8+ messages in thread
From: Alvaro Herrera @ 2008-07-15 22:36 UTC (permalink / raw)
To: Matthew T. O'Connor <matthew@zeut.net>; +Cc: Chris Hoover <revoohc@gmail.com>; pgsql-admin
Matthew T. O'Connor escribió:
> As Alvaro already said this is a case where autovacuum still isn't
> great.
While I have your attention ;-) do you have any ideas on how to improve
this? I don't see anything that looks like a solution for this case.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: More Autovacuum questions
2008-07-15 13:19 More Autovacuum questions Chris Hoover <revoohc@gmail.com>
2008-07-15 15:12 ` Re: More Autovacuum questions Matthew T. O'Connor <matthew@zeut.net>
2008-07-15 22:36 ` Re: More Autovacuum questions Alvaro Herrera <alvherre@commandprompt.com>
@ 2008-07-16 05:27 ` Matthew T. O'Connor <matthew@zeut.net>
0 siblings, 0 replies; 8+ messages in thread
From: Matthew T. O'Connor @ 2008-07-16 05:27 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Chris Hoover <revoohc@gmail.com>; pgsql-admin
Alvaro Herrera wrote:
> Matthew T. O'Connor escribió:
>
>> As Alvaro already said this is a case where autovacuum still isn't
>> great.
>>
>
> While I have your attention ;-) do you have any ideas on how to improve
> this? I don't see anything that looks like a solution for this case.
No good ideas, but a few random thoughts.
1) Can we make vacuum not block analyze? That way we can continue to
analyze a table while it's being vacuumed.
1b) If not, can we make auto-analyze bump auto-vacuum?
2) Maintenance windows. They wouldn't solve this problem, but they
might mitigate it some.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2008-07-16 05:27 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-16 18:00 More Autovacuum Questions Chris Hoover <revoohc@gmail.com>
2006-05-16 18:09 ` Gourish Singbal <gourish@gmail.com>
2006-05-16 18:40 ` Jim C. Nasby <jnasby@pervasive.com>
2008-07-15 13:19 More Autovacuum questions Chris Hoover <revoohc@gmail.com>
2008-07-15 13:52 ` Re: More Autovacuum questions Alvaro Herrera <alvherre@commandprompt.com>
2008-07-15 15:12 ` Re: More Autovacuum questions Matthew T. O'Connor <matthew@zeut.net>
2008-07-15 22:36 ` Re: More Autovacuum questions Alvaro Herrera <alvherre@commandprompt.com>
2008-07-16 05:27 ` Re: More Autovacuum questions Matthew T. O'Connor <matthew@zeut.net>
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