pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAutovacuum / full vacuum
39+ messages / 16 participants
[nested] [flat]
* Autovacuum / full vacuum
@ 2006-01-17 10:18 Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 13:19 ` Re: Autovacuum / full vacuum Alvaro Herrera <alvherre@alvh.no-ip.org>
2006-01-17 13:59 ` Re: Autovacuum / full vacuum Christopher Browne <cbbrowne@acm.org>
2006-01-17 14:56 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 15:13 ` Re: Autovacuum / full vacuum Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 5 replies; 39+ messages in thread
From: Michael Riess @ 2006-01-17 10:18 UTC (permalink / raw)
To: pgsql-performance
hi,
I'm curious as to why autovacuum is not designed to do full vacuum. I
know that the necessity of doing full vacuums can be reduced by
increasing the FSM, but in my opinion that is the wrong decision for
many applications. My application does not continuously
insert/update/delete tuples at a constant rate. Basically there are long
periods of relatively few modifications and short burst of high
activity. Increasing the FSM so that even during these bursts most space
would be reused would mean to reduce the available memory for all
other database tasks.
So my question is: What's the use of an autovacuum daemon if I still
have to use a cron job to do full vacuums? wouldn't it just be a minor
job to enhance autovacuum to be able to perform full vacuums, if one
really wants it to do that - even if some developers think that it's the
wrong approach?
Mike
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
@ 2006-01-17 10:29 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 10:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 11:35 ` Re: Autovacuum / full vacuum Pandurangan R S <pandurangan.r.s@gmail.com>
4 siblings, 2 replies; 39+ messages in thread
From: Christopher Kings-Lynne @ 2006-01-17 10:29 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; +Cc: pgsql-performance
> So my question is: What's the use of an autovacuum daemon if I still
> have to use a cron job to do full vacuums? wouldn't it just be a minor
> job to enhance autovacuum to be able to perform full vacuums, if one
> really wants it to do that - even if some developers think that it's the
> wrong approach?
You should never have to do full vacuums...
Chris
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2006-01-17 10:33 ` Michael Riess <mlriess@gmx.de>
2006-01-17 12:08 ` Re: Autovacuum / full vacuum Michael Stone <mstone+postgres@mathom.us>
2006-01-17 14:04 ` Re: Autovacuum / full vacuum Christopher Browne <cbbrowne@acm.org>
2006-01-17 14:09 ` Re: Autovacuum / full vacuum Matthew T. O'Connor <matthew@zeut.net>
1 sibling, 3 replies; 39+ messages in thread
From: Michael Riess @ 2006-01-17 10:33 UTC (permalink / raw)
To: pgsql-performance
Hi,
did you read my post? In the first part I explained why I don't want to
increase the FSM that much.
Mike
>> So my question is: What's the use of an autovacuum daemon if I still
>> have to use a cron job to do full vacuums? wouldn't it just be a minor
>> job to enhance autovacuum to be able to perform full vacuums, if one
>> really wants it to do that - even if some developers think that it's
>> the wrong approach?
>
> You should never have to do full vacuums...
>
> Chris
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 10:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
@ 2006-01-17 12:08 ` Michael Stone <mstone+postgres@mathom.us>
2 siblings, 0 replies; 39+ messages in thread
From: Michael Stone @ 2006-01-17 12:08 UTC (permalink / raw)
To: pgsql-performance
On Tue, Jan 17, 2006 at 11:33:02AM +0100, Michael Riess wrote:
>did you read my post? In the first part I explained why I don't want to
>increase the FSM that much.
Since you didn't quantify it, that wasn't much of a data point. (IOW,
you'd generally have to be seriously resource constrained before the FSM
would be a significant source of memory consumption--in which case more
RAM would probably be a much better solution than screwing with
autovacuum.)
Mike Stone
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 10:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
@ 2006-01-17 14:04 ` Christopher Browne <cbbrowne@acm.org>
2006-01-17 14:50 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2 siblings, 1 reply; 39+ messages in thread
From: Christopher Browne @ 2006-01-17 14:04 UTC (permalink / raw)
To: pgsql-performance
> did you read my post? In the first part I explained why I don't want
> to increase the FSM that much.
No, you didn't. You explained *that* you thought you didn't want to
increase the FSM. You didn't explain why.
FSM expansion comes fairly cheap, and tends to be an effective way of
eliminating the need for VACUUM FULL. That is generally considered to
be a good tradeoff. In future versions, there is likely to be more of
this sort of thing; for instance, on the ToDo list is a "Vacuum Space
Map" that would collect page IDs that need vacuuming so that
PostgreSQL could do "quicker" vacuums...
--
output = reverse("moc.liamg" "@" "enworbbc")
http://cbbrowne.com/info/internet.html
Given recent events in Florida, the tourism board in Texas has
developed a new advertising campaign based on the slogan "Ya'll come
to Texas, where we ain't shot a tourist in a car since November 1963."
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 10:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:04 ` Re: Autovacuum / full vacuum Christopher Browne <cbbrowne@acm.org>
@ 2006-01-17 14:50 ` Michael Riess <mlriess@gmx.de>
2006-01-17 15:07 ` Re: Autovacuum / full vacuum Michael Stone <mstone+postgres@mathom.us>
2006-01-17 15:08 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 15:10 ` Re: Autovacuum / full vacuum hubert depesz lubaczewski <depesz@gmail.com>
2006-01-17 22:28 ` Re: Autovacuum / full vacuum Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 4 replies; 39+ messages in thread
From: Michael Riess @ 2006-01-17 14:50 UTC (permalink / raw)
To: pgsql-performance
Well,
I think that the documentation is not exactly easy to understand. I
always wondered why there are no examples for common postgresql
configurations. All I know is that the default configuration seems to be
too low for production use. And while running postgres I get no hints as
to which setting needs to be increased to improve performance. I have no
chance to see if my FSM settings are too low other than to run vacuum
full verbose in psql, pipe the result to a text file and grep for some
words to get a somewhat comprehensive idea of how much unused space
there is in my system.
Don't get me wrong - I really like PostgreSQL and it works well in my
application. But somehow I feel that it might run much better ...
about the FSM: You say that increasing the FSM is fairly cheap - how
should I know that?
>> did you read my post? In the first part I explained why I don't want
>> to increase the FSM that much.
>
> No, you didn't. You explained *that* you thought you didn't want to
> increase the FSM. You didn't explain why.
>
> FSM expansion comes fairly cheap ...
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 10:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:04 ` Re: Autovacuum / full vacuum Christopher Browne <cbbrowne@acm.org>
2006-01-17 14:50 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
@ 2006-01-17 15:07 ` Michael Stone <mstone+postgres@mathom.us>
3 siblings, 0 replies; 39+ messages in thread
From: Michael Stone @ 2006-01-17 15:07 UTC (permalink / raw)
To: pgsql-performance
On Tue, Jan 17, 2006 at 03:50:38PM +0100, Michael Riess wrote:
>about the FSM: You say that increasing the FSM is fairly cheap - how
>should I know that?
Why would you assume otherwise, to the point of not considering changing
the setting?
The documentation explains how much memory is used for FSM entries. If
you look at vacuum verbose output it will tell you how much memory
you're currently using for the FSM.
Mike Stone
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 10:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:04 ` Re: Autovacuum / full vacuum Christopher Browne <cbbrowne@acm.org>
2006-01-17 14:50 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
@ 2006-01-17 15:08 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 15:59 ` Re: Autovacuum / full vacuum Scott Marlowe <smarlowe@g2switchworks.com>
3 siblings, 1 reply; 39+ messages in thread
From: Andrew Sullivan @ 2006-01-17 15:08 UTC (permalink / raw)
To: pgsql-performance
On Tue, Jan 17, 2006 at 03:50:38PM +0100, Michael Riess wrote:
> always wondered why there are no examples for common postgresql
> configurations.
You mean like this one? (for 8.0):
<http://www.powerpostgresql.com/Downloads/annotated_conf_80.html;
> All I know is that the default configuration seems to be
> too low for production use.
Define "production use". It may be too low for you.
> chance to see if my FSM settings are too low other than to run vacuum
> full verbose in psql, pipe the result to a text file and grep for some
Not true. You don't need a FULL on there to figure this out.
> about the FSM: You say that increasing the FSM is fairly cheap - how
> should I know that?
Do the math. The docs say this:
--snip---
max_fsm_pages (integer)
Sets the maximum number of disk pages for which free space will
be tracked in the shared free-space map. Six bytes of shared memory
are consumed for each page slot. This setting must be more than 16 *
max_fsm_relations. The default is 20000. This option can only be set
at server start.
max_fsm_relations (integer)
Sets the maximum number of relations (tables and indexes) for
which free space will be tracked in the shared free-space map.
Roughly seventy bytes of shared memory are consumed for each slot.
The default is 1000. This option can only be set at server start.
---snip---
So by default, you have 6 B * 20,000 = 120,000 bytes for the FSM pages.
By default, you have 70 B * 1,000 = 70,000 bytes for the FSM
relations.
Now, there are two knobs. One of them tracks the number of
relations. How many relations do you have? Count the number of
indexes and tables you have, and give yourself some headroom in case
you add some more, and poof, you have your number for the relations.
Now all you need to do is figure out what your churn rate is on
tables, and count up how many disk pages that's likely to be. Give
yourself a little headroom, and the number of FSM pages is done, too.
This churn rate is often tough to estimate, though, so you may have
to fiddle with it from time to time.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 10:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:04 ` Re: Autovacuum / full vacuum Christopher Browne <cbbrowne@acm.org>
2006-01-17 14:50 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 15:08 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
@ 2006-01-17 15:59 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-01-17 17:16 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 39+ messages in thread
From: Scott Marlowe @ 2006-01-17 15:59 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-performance
On Tue, 2006-01-17 at 09:08, Andrew Sullivan wrote:
> On Tue, Jan 17, 2006 at 03:50:38PM +0100, Michael Riess wrote:
> > always wondered why there are no examples for common postgresql
> > configurations.
>
> You mean like this one? (for 8.0):
>
> <http://www.powerpostgresql.com/Downloads/annotated_conf_80.html;
I have to admit, looking at the documentation, that we really don't
explain this all that well in the administration section, and I can see
how easily led astray beginners are.
I think it's time I joined the pgsql-docs mailing list...
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 10:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:04 ` Re: Autovacuum / full vacuum Christopher Browne <cbbrowne@acm.org>
2006-01-17 14:50 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 15:08 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 15:59 ` Re: Autovacuum / full vacuum Scott Marlowe <smarlowe@g2switchworks.com>
@ 2006-01-17 17:16 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 17:31 ` Re: Autovacuum / full vacuum Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 39+ messages in thread
From: Andrew Sullivan @ 2006-01-17 17:16 UTC (permalink / raw)
To: pgsql-performance
On Tue, Jan 17, 2006 at 09:59:25AM -0600, Scott Marlowe wrote:
> I have to admit, looking at the documentation, that we really don't
> explain this all that well in the administration section, and I can see
> how easily led astray beginners are.
I understand what you mean, but I suppose my reaction would be that
what we really need is a place to keep these things, with a note in
the docs that the "best practice" settings for these are documented
at <some url>, and evolve over time as people gain expertise with the
new features.
I suspect, for instance, that nobody knows exactly the right settings
for any generic workload yet under 8.1 (although probably people know
them well enough for particular workloads).
A
--
Andrew Sullivan | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
--Dennis Ritchie
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 10:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:04 ` Re: Autovacuum / full vacuum Christopher Browne <cbbrowne@acm.org>
2006-01-17 14:50 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 15:08 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 15:59 ` Re: Autovacuum / full vacuum Scott Marlowe <smarlowe@g2switchworks.com>
2006-01-17 17:16 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
@ 2006-01-17 17:31 ` Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 39+ messages in thread
From: Scott Marlowe @ 2006-01-17 17:31 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-performance
On Tue, 2006-01-17 at 11:16, Andrew Sullivan wrote:
> On Tue, Jan 17, 2006 at 09:59:25AM -0600, Scott Marlowe wrote:
> > I have to admit, looking at the documentation, that we really don't
> > explain this all that well in the administration section, and I can see
> > how easily led astray beginners are.
>
> I understand what you mean, but I suppose my reaction would be that
> what we really need is a place to keep these things, with a note in
> the docs that the "best practice" settings for these are documented
> at <some url>, and evolve over time as people gain expertise with the
> new features.
>
> I suspect, for instance, that nobody knows exactly the right settings
> for any generic workload yet under 8.1 (although probably people know
> them well enough for particular workloads).
But the problem is bigger than that. The administrative docs were
obviously evolved over time, and now they kind of jump around and around
covering the same subject from different angles and at different
depths. Even I find it hard to find what I need, and I know PostgreSQL
administration well enough to be pretty darned good at it.
For the beginner, it must seem much more confusing. The more I look at
the administration section of the docs, the more I want to reorganize
the whole thing, and rewrite large sections of it as well.
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 10:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:04 ` Re: Autovacuum / full vacuum Christopher Browne <cbbrowne@acm.org>
2006-01-17 14:50 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
@ 2006-01-17 15:10 ` hubert depesz lubaczewski <depesz@gmail.com>
3 siblings, 0 replies; 39+ messages in thread
From: hubert depesz lubaczewski @ 2006-01-17 15:10 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; +Cc: pgsql-performance
On 1/17/06, Michael Riess <mlriess@gmx.de> wrote:
>
> about the FSM: You say that increasing the FSM is fairly cheap - how
> should I know that?
>
comment from original postgresql.conf file seems pretty obvious:
#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000 # min 100, ~70 bytes each
basically setting max_fsm_pages to 1000000 consumes 6 megabytes. and i
definitelly doubt you will ever hit that high.
depesz
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 10:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:04 ` Re: Autovacuum / full vacuum Christopher Browne <cbbrowne@acm.org>
2006-01-17 14:50 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
@ 2006-01-17 22:28 ` Jim C. Nasby <jnasby@pervasive.com>
2006-01-18 14:09 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
3 siblings, 1 reply; 39+ messages in thread
From: Jim C. Nasby @ 2006-01-17 22:28 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; +Cc: pgsql-performance
On Tue, Jan 17, 2006 at 03:50:38PM +0100, Michael Riess wrote:
> Well,
>
> I think that the documentation is not exactly easy to understand. I
> always wondered why there are no examples for common postgresql
> configurations. All I know is that the default configuration seems to be
> too low for production use. And while running postgres I get no hints as
> to which setting needs to be increased to improve performance. I have no
There's a number of sites that have lots of info on postgresql.conf
tuning. Google for 'postgresql.conf tuning' or 'annotated
postgresql.conf'.
> chance to see if my FSM settings are too low other than to run vacuum
> full verbose in psql, pipe the result to a text file and grep for some
> words to get a somewhat comprehensive idea of how much unused space
> there is in my system.
>
> Don't get me wrong - I really like PostgreSQL and it works well in my
> application. But somehow I feel that it might run much better ...
>
> about the FSM: You say that increasing the FSM is fairly cheap - how
> should I know that?
decibel@phonebook.1[16:26]/opt/local/share/postgresql8:3%grep fsm \
postgresql.conf.sample
#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000 # min 100, ~70 bytes each
decibel@phonebook.1[16:26]/opt/local/share/postgresql8:4%
--
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] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 10:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:04 ` Re: Autovacuum / full vacuum Christopher Browne <cbbrowne@acm.org>
2006-01-17 14:50 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 22:28 ` Re: Autovacuum / full vacuum Jim C. Nasby <jnasby@pervasive.com>
@ 2006-01-18 14:09 ` Michael Riess <mlriess@gmx.de>
2006-01-18 22:48 ` Re: Autovacuum / full vacuum Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 39+ messages in thread
From: Michael Riess @ 2006-01-18 14:09 UTC (permalink / raw)
To: pgsql-performance
> There's a number of sites that have lots of info on postgresql.conf
> tuning. Google for 'postgresql.conf tuning' or 'annotated
> postgresql.conf'.
I know some of these sites, but who should I know if the information on
those pages is correct? The information on those pages should be
published as part of the postgres documentation. Doesn't have to be too
much, maybe like this page:
http://www.powerpostgresql.com/Downloads/annotated_conf_80.html
But it should be part of the documentation to show newbies that not only
the information is correct, but also approved of and recommended by the
postgres team.
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 10:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:04 ` Re: Autovacuum / full vacuum Christopher Browne <cbbrowne@acm.org>
2006-01-17 14:50 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 22:28 ` Re: Autovacuum / full vacuum Jim C. Nasby <jnasby@pervasive.com>
2006-01-18 14:09 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
@ 2006-01-18 22:48 ` Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 39+ messages in thread
From: Jim C. Nasby @ 2006-01-18 22:48 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; +Cc: pgsql-performance
On Wed, Jan 18, 2006 at 03:09:42PM +0100, Michael Riess wrote:
> >There's a number of sites that have lots of info on postgresql.conf
> >tuning. Google for 'postgresql.conf tuning' or 'annotated
> >postgresql.conf'.
>
> I know some of these sites, but who should I know if the information on
> those pages is correct? The information on those pages should be
> published as part of the postgres documentation. Doesn't have to be too
> much, maybe like this page:
>
> http://www.powerpostgresql.com/Downloads/annotated_conf_80.html
>
> But it should be part of the documentation to show newbies that not only
> the information is correct, but also approved of and recommended by the
> postgres team.
Actually, most of what you find there is probably also found in
techdocs. But of course it would be better if the docs did a better job
of explaining things...
--
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] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 10:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
@ 2006-01-17 14:09 ` Matthew T. O'Connor <matthew@zeut.net>
2006-01-17 14:30 ` Re: Autovacuum / full vacuum Markus Schaber <schabi@logix-tt.com>
2006-01-17 14:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 15:09 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
2 siblings, 3 replies; 39+ messages in thread
From: Matthew T. O'Connor @ 2006-01-17 14:09 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; +Cc: pgsql-performance
Michael Riess wrote:
> did you read my post? In the first part I explained why I don't want to
> increase the FSM that much.
I'm sure he did, but just because you don't have enough FSM space to
capture all everything from your "burst", that doesn't mean that space
can't be reclaimed. The next time a regular vacuum is run, it will once
again try to fill the FSM with any remaining free space it finds in the
table. What normally happens is that your table will never bee 100%
free of dead space, normally it will settle at some steady state size
that is small percentage bigger than the table will be after a full
vacuum. As long as that percentage is small enough, the effect on
performance is negligible. Have you measured to see if things are truly
faster after a VACUUM FULL?
Matt
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 10:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:09 ` Re: Autovacuum / full vacuum Matthew T. O'Connor <matthew@zeut.net>
@ 2006-01-17 14:30 ` Markus Schaber <schabi@logix-tt.com>
2 siblings, 0 replies; 39+ messages in thread
From: Markus Schaber @ 2006-01-17 14:30 UTC (permalink / raw)
To: Matthew T. O'Connor <matthew@zeut.net>; +Cc: Michael Riess <mlriess@gmx.de>; pgsql-performance
Hi, Matthew,
Matthew T. O'Connor wrote:
> I'm sure he did, but just because you don't have enough FSM space to
> capture all everything from your "burst", that doesn't mean that space
> can't be reclaimed. The next time a regular vacuum is run, it will once
> again try to fill the FSM with any remaining free space it finds in the
> table. What normally happens is that your table will never bee 100%
> free of dead space, normally it will settle at some steady state size
> that is small percentage bigger than the table will be after a full
> vacuum. As long as that percentage is small enough, the effect on
> performance is negligible.
This will work if you've a steady stream of inserts / updates, but not
if you happen to have update bulks that exhaust the FSM capacity. The
update first fills up all the FSM, and then allocates new pages for the
rest. Then VACUUM comes and refills the FSM, however, the FSM does not
contain enough free space for the next large bulk update. The same is
for deletes and large bulk inserts, btw.
So your table keeps growing steadily, until VACUUM FULL or CLUSTER comes
along to clean up the mess.
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 10:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:09 ` Re: Autovacuum / full vacuum Matthew T. O'Connor <matthew@zeut.net>
@ 2006-01-17 14:33 ` Michael Riess <mlriess@gmx.de>
2006-01-17 15:36 ` Re: Autovacuum / full vacuum Tom Lane <tgl@sss.pgh.pa.us>
2006-01-17 17:25 ` Re: Autovacuum / full vacuum Markus Schaber <schabi@logix-tt.com>
2 siblings, 2 replies; 39+ messages in thread
From: Michael Riess @ 2006-01-17 14:33 UTC (permalink / raw)
To: pgsql-performance
Hi,
yes, some heavily used tables contain approx. 90% free space after a
week. I'll try to increase FSM even more, but I think that I will still
have to run a full vacuum every week. Prior to 8.1 I was using 7.4 and
ran a full vacuum every day, so the autovacuum has helped a lot.
But actually I never understood why the database system slows down at
all when there is much unused space in the files. Are the unused pages
cached by the system, or is there another reason for the impact on the
performance?
Mike
> Have you measured to see if things are truly
> faster after a VACUUM FULL?
>
> Matt
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 10:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:09 ` Re: Autovacuum / full vacuum Matthew T. O'Connor <matthew@zeut.net>
2006-01-17 14:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
@ 2006-01-17 15:36 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 39+ messages in thread
From: Tom Lane @ 2006-01-17 15:36 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; +Cc: pgsql-performance
Michael Riess <mlriess@gmx.de> writes:
> But actually I never understood why the database system slows down at
> all when there is much unused space in the files.
Perhaps some of your common queries are doing sequential scans? Those
would visit the empty pages as well as the full ones.
regards, tom lane
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 10:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:09 ` Re: Autovacuum / full vacuum Matthew T. O'Connor <matthew@zeut.net>
2006-01-17 14:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
@ 2006-01-17 17:25 ` Markus Schaber <schabi@logix-tt.com>
1 sibling, 0 replies; 39+ messages in thread
From: Markus Schaber @ 2006-01-17 17:25 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; +Cc: pgsql-performance
Hi, Michael,
Michael Riess wrote:
> But actually I never understood why the database system slows down at
> all when there is much unused space in the files. Are the unused pages
> cached by the system, or is there another reason for the impact on the
> performance?
No, they are not cached as such, but PostgreSQL caches whole pages, and
you don't have only empty pages, but also lots of partially empty pages,
so the signal/noise ratio is worse (means PostgreSQL has to fetch more
pages to get the same data).
Sequential scans etc. are also slower.
And some file systems get slower when files get bigger or there are more
files, but this effect should not really be noticeable here.
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 10:33 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:09 ` Re: Autovacuum / full vacuum Matthew T. O'Connor <matthew@zeut.net>
@ 2006-01-17 15:09 ` Andrew Sullivan <ajs@crankycanuck.ca>
2 siblings, 0 replies; 39+ messages in thread
From: Andrew Sullivan @ 2006-01-17 15:09 UTC (permalink / raw)
To: pgsql-performance
On Tue, Jan 17, 2006 at 09:09:02AM -0500, Matthew T. O'Connor wrote:
> vacuum. As long as that percentage is small enough, the effect on
> performance is negligible. Have you measured to see if things are truly
Actually, as long as the percentage is small enough and the pages are
really empty, the performance effect is positive. If you have VACUUM
FULLed table, inserts have to extend the table before inserting,
whereas in a table with some space reclaimed, the I/O effect of
having to allocate another disk page is already done.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
When my information changes, I alter my conclusions. What do you do sir?
--attr. John Maynard Keynes
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Re: Autovacuum / full vacuum Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2006-01-17 11:35 ` Pandurangan R S <pandurangan.r.s@gmail.com>
1 sibling, 0 replies; 39+ messages in thread
From: Pandurangan R S @ 2006-01-17 11:35 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Michael Riess <mlriess@gmx.de>; pgsql-performance
>> You should never have to do full vacuums...
I would rather say, You should never have to do full vacuums by any
periodic means. It may be done on a adhoc basis, when you have figured
out that your table is never going to grow that big again.
On 1/17/06, Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> > So my question is: What's the use of an autovacuum daemon if I still
> > have to use a cron job to do full vacuums? wouldn't it just be a minor
> > job to enhance autovacuum to be able to perform full vacuums, if one
> > really wants it to do that - even if some developers think that it's the
> > wrong approach?
>
> You should never have to do full vacuums...
>
> Chris
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
@ 2006-01-17 13:19 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
4 siblings, 0 replies; 39+ messages in thread
From: Alvaro Herrera @ 2006-01-17 13:19 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; +Cc: pgsql-performance
Michael Riess wrote:
> hi,
>
> I'm curious as to why autovacuum is not designed to do full vacuum.
Because a VACUUM FULL is too invasive. Lazy vacuum is so light on the
system w.r.t. locks that it's generally not a problem to start one at
any time. On the contrary, vacuum full could be a disaster on some
situations.
What's more, in general a lazy vacuum is enough to keep the dead space
within manageability, given a good autovacuum configuration and good FSM
configuration, so there's mostly no need for full vacuum. (This is the
theory at least.) For the situations where there is a need, we tell you
to issue it manually.
> So my question is: What's the use of an autovacuum daemon if I still
> have to use a cron job to do full vacuums? wouldn't it just be a minor
> job to enhance autovacuum to be able to perform full vacuums, if one
> really wants it to do that - even if some developers think that it's the
> wrong approach?
Yes, it is a minor job to "enhance" it to perform vacuum full. The
problem is having a good approach to determining _when_ to issue a full
vacuum, and having a way to completely disallow it. If you want to do
the development work, be my guest (but let us know your design first).
If you don't, I guess you would have to wait until it comes high enough
on someone's to-do list, maybe because you convinced him (or her, but we
don't have Postgres-ladies at the moment AFAIK) monetarily or something.
You can, of course, produce a patch and use it internally. This is free
software, remember.
--
Alvaro Herrera Developer, http://www.PostgreSQL.org
"God is real, unless declared as int"
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
@ 2006-01-17 13:59 ` Christopher Browne <cbbrowne@acm.org>
2006-01-17 14:05 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
4 siblings, 1 reply; 39+ messages in thread
From: Christopher Browne @ 2006-01-17 13:59 UTC (permalink / raw)
To: pgsql-performance
> I'm curious as to why autovacuum is not designed to do full vacuum.
Because that's terribly invasive due to the locks it takes out.
Lazy vacuum may chew some I/O, but it does *not* block your
application for the duration.
VACUUM FULL blocks the application. That is NOT something that anyone
wants to throw into the "activity mix" randomly.
--
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://linuxdatabases.info/info/slony.html
Signs of a Klingon Programmer #11: "This machine is a piece of GAGH! I
need dual Pentium processors if I am to do battle with this code!"
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 13:59 ` Re: Autovacuum / full vacuum Christopher Browne <cbbrowne@acm.org>
@ 2006-01-17 14:05 ` Michael Riess <mlriess@gmx.de>
2006-01-17 15:13 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 39+ messages in thread
From: Michael Riess @ 2006-01-17 14:05 UTC (permalink / raw)
To: pgsql-performance
> VACUUM FULL blocks the application. That is NOT something that anyone
> wants to throw into the "activity mix" randomly.
There must be a way to implement a daemon which frees up space of a
relation without blocking it too long. It could abort after a certain
number of blocks have been freed and then move to the next relation.
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 13:59 ` Re: Autovacuum / full vacuum Christopher Browne <cbbrowne@acm.org>
2006-01-17 14:05 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
@ 2006-01-17 15:13 ` Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 39+ messages in thread
From: Andrew Sullivan @ 2006-01-17 15:13 UTC (permalink / raw)
To: pgsql-performance
On Tue, Jan 17, 2006 at 03:05:29PM +0100, Michael Riess wrote:
> There must be a way to implement a daemon which frees up space of a
> relation without blocking it too long.
Define "too long". If I have a table that needs to respond to a
SELECT in 50ms, I don't have time for you to lock my table. If this
were such an easy thing to do, don't you think the folks who came up
wit the ingenious lazy vacuum system would have done it?
Remember, a vacuum full must completely lock the table, because it is
physically moving bits around on the disk. So a SELECT can't happen
at the same time, because the bits might move out from under the
SELECT while it's running. Concurrency is hard, and race conditions
are easy, to implement.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
--Alexander Hamilton
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
@ 2006-01-17 14:56 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 15:04 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 16:43 ` Re: Autovacuum / full vacuum Chris Browne <cbbrowne@acm.org>
4 siblings, 2 replies; 39+ messages in thread
From: Andrew Sullivan @ 2006-01-17 14:56 UTC (permalink / raw)
To: pgsql-performance
On Tue, Jan 17, 2006 at 11:18:59AM +0100, Michael Riess wrote:
> hi,
>
> I'm curious as to why autovacuum is not designed to do full vacuum. I
Because nothing that runs automatically should ever take an exclusive
lock on the entire database, which is what VACUUM FULL does.
> activity. Increasing the FSM so that even during these bursts most space
> would be reused would mean to reduce the available memory for all
> other database tasks.
I don't believe the hit is enough that you should even notice it.
You'd have to post some pretty incredible use cases to show that the
tiny loss of memory to FSM is worth (a) an exclusive lock and (b) the
loss of efficiency you get from having some preallocated pages in
tables.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:56 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
@ 2006-01-17 15:04 ` Michael Riess <mlriess@gmx.de>
2006-01-17 15:19 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 1 reply; 39+ messages in thread
From: Michael Riess @ 2006-01-17 15:04 UTC (permalink / raw)
To: pgsql-performance
Hi,
>> hi,
>>
>> I'm curious as to why autovacuum is not designed to do full vacuum. I
>
> Because nothing that runs automatically should ever take an exclusive
> lock on the entire database, which is what VACUUM FULL does.
I thought that vacuum full only locks the table which it currently
operates on? I'm pretty sure that once a table has been vacuumed, it can
be accessed without any restrictions while the vacuum process works on
the next table.
>
>> activity. Increasing the FSM so that even during these bursts most space
>> would be reused would mean to reduce the available memory for all
>> other database tasks.
>
> I don't believe the hit is enough that you should even notice it.
> You'd have to post some pretty incredible use cases to show that the
> tiny loss of memory to FSM is worth (a) an exclusive lock and (b) the
> loss of efficiency you get from having some preallocated pages in
> tables.
I have 5000 tables and a workstation with 1 GB RAM which hosts an Apache
Web Server, Tomcat Servlet Container and PostgreSQL. RAM is not
something that I have plenty of ... and the hardware is fixed and cannot
be changed.
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:56 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 15:04 ` Re: Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
@ 2006-01-17 15:19 ` Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 39+ messages in thread
From: Andrew Sullivan @ 2006-01-17 15:19 UTC (permalink / raw)
To: pgsql-performance
On Tue, Jan 17, 2006 at 04:04:41PM +0100, Michael Riess wrote:
>
> I thought that vacuum full only locks the table which it currently
> operates on? I'm pretty sure that once a table has been vacuumed, it can
> be accessed without any restrictions while the vacuum process works on
> the next table.
Yes, I think the way I phrased it was unfortunate. But if you issue
VACUUM FULL you'll get an exclusive lock on everything, although not
all at the same time. But of course, if your query load is like
this
BEGIN;
SELECT from t1, t2 where t1.col1 = t2.col2;
[application logic]
UPDATE t3 . . .
COMMIT;
you'll find yourself blocked in the first statement on both t1 and
t2; and then on t3 as well. You sure don't want that to happen
automagically, in the middle of your business day.
> I have 5000 tables and a workstation with 1 GB RAM which hosts an Apache
> Web Server, Tomcat Servlet Container and PostgreSQL. RAM is not
> something that I have plenty of ... and the hardware is fixed and cannot
> be changed.
I see. Well, I humbly submit that your problem is not the design of
the PostgreSQL server, then. "The hardware is fixed and cannot be
changed," is the first optimisation I'd make. Heck, I gave away a
box to charity only two weeks ago that would solve your problem
better than automatically issuing VACUUM FULL.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Information security isn't a technological problem. It's an economics
problem.
--Bruce Schneier
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:56 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
@ 2006-01-17 16:43 ` Chris Browne <cbbrowne@acm.org>
2006-01-17 17:30 ` Re: Autovacuum / full vacuum Alvaro Herrera <alvherre@alvh.no-ip.org>
2006-01-17 18:13 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 2 replies; 39+ messages in thread
From: Chris Browne @ 2006-01-17 16:43 UTC (permalink / raw)
To: pgsql-performance
ajs@crankycanuck.ca (Andrew Sullivan) writes:
> On Tue, Jan 17, 2006 at 11:18:59AM +0100, Michael Riess wrote:
>> hi,
>>
>> I'm curious as to why autovacuum is not designed to do full vacuum. I
>
> Because nothing that runs automatically should ever take an exclusive
> lock on the entire database, which is what VACUUM FULL does.
That's a bit more than what autovacuum would probably do...
autovacuum does things table by table, so that what would be locked
should just be one table.
Even so, I'd not be keen on having anything that runs automatically
take an exclusive lock on even as much as a table.
>> activity. Increasing the FSM so that even during these bursts most
>> space would be reused would mean to reduce the available memory for
>> all other database tasks.
>
> I don't believe the hit is enough that you should even notice
> it. You'd have to post some pretty incredible use cases to show that
> the tiny loss of memory to FSM is worth (a) an exclusive lock and
> (b) the loss of efficiency you get from having some preallocated
> pages in tables.
There is *a* case for setting up full vacuums of *some* objects. If
you have a table whose tuples all get modified in the course of some
common query, that will lead to a pretty conspicuous bloating of *that
table.*
Even with a big FSM, the pattern of how updates take place will lead
to that table having ~50% of its space being "dead/free," which is way
higher than the desirable "stable proportion" of 10-15%.
For that sort of table, it may be attractive to run VACUUM FULL on a
regular basis. Of course, it may also be attractive to try to come up
with an update process that won't kill the whole table's contents at
once ;-).
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/x.html
"As long as each individual is facing the TV tube alone, formal
freedom poses no threat to privilege." --Noam Chomsky
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:56 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 16:43 ` Re: Autovacuum / full vacuum Chris Browne <cbbrowne@acm.org>
@ 2006-01-17 17:30 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2006-01-17 18:40 ` Re: Autovacuum / full vacuum Chris Browne <cbbrowne@acm.org>
1 sibling, 1 reply; 39+ messages in thread
From: Alvaro Herrera @ 2006-01-17 17:30 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
Chris Browne wrote:
> ajs@crankycanuck.ca (Andrew Sullivan) writes:
> > On Tue, Jan 17, 2006 at 11:18:59AM +0100, Michael Riess wrote:
> >> hi,
> >>
> >> I'm curious as to why autovacuum is not designed to do full vacuum. I
> >
> > Because nothing that runs automatically should ever take an exclusive
> > lock on the entire database, which is what VACUUM FULL does.
>
> That's a bit more than what autovacuum would probably do...
> autovacuum does things table by table, so that what would be locked
> should just be one table.
Even a database-wide vacuum does not take locks on more than one table.
The table locks are acquired and released one by one, as the operation
proceeds. And as you know, autovacuum (both 8.1's and contrib) does
issue database-wide vacuums, if it finds a database close to an xid
wraparound.
--
Alvaro Herrera http://www.advogato.org/person/alvherre
"Las mujeres son como hondas: mientras más resistencia tienen,
más lejos puedes llegar con ellas" (Jonas Nightingale, Leap of Faith)
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:56 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 16:43 ` Re: Autovacuum / full vacuum Chris Browne <cbbrowne@acm.org>
2006-01-17 17:30 ` Re: Autovacuum / full vacuum Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2006-01-17 18:40 ` Chris Browne <cbbrowne@acm.org>
2006-01-17 19:32 ` Re: Autovacuum / full vacuum Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 39+ messages in thread
From: Chris Browne @ 2006-01-17 18:40 UTC (permalink / raw)
To: pgsql-performance
alvherre@alvh.no-ip.org (Alvaro Herrera) writes:
> Chris Browne wrote:
>> ajs@crankycanuck.ca (Andrew Sullivan) writes:
>> > On Tue, Jan 17, 2006 at 11:18:59AM +0100, Michael Riess wrote:
>> >> hi,
>> >>
>> >> I'm curious as to why autovacuum is not designed to do full vacuum. I
>> >
>> > Because nothing that runs automatically should ever take an exclusive
>> > lock on the entire database, which is what VACUUM FULL does.
>>
>> That's a bit more than what autovacuum would probably do...
>> autovacuum does things table by table, so that what would be locked
>> should just be one table.
>
> Even a database-wide vacuum does not take locks on more than one table.
> The table locks are acquired and released one by one, as the operation
> proceeds. And as you know, autovacuum (both 8.1's and contrib) does
> issue database-wide vacuums, if it finds a database close to an xid
> wraparound.
Has that changed recently? I have always seen "vacuumdb" or SQL
"VACUUM" (without table specifications) running as one long
transaction which doesn't release the locks that it is granted until
the end of the transaction.
--
"cbbrowne","@","acm.org"
http://cbbrowne.com/info/spiritual.html
"My nostalgia for Icon makes me forget about any of the bad things. I
don't have much nostalgia for Perl, so its faults I remember."
-- Scott Gilbert comp.lang.python
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:56 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 16:43 ` Re: Autovacuum / full vacuum Chris Browne <cbbrowne@acm.org>
2006-01-17 17:30 ` Re: Autovacuum / full vacuum Alvaro Herrera <alvherre@alvh.no-ip.org>
2006-01-17 18:40 ` Re: Autovacuum / full vacuum Chris Browne <cbbrowne@acm.org>
@ 2006-01-17 19:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-18 15:50 ` Re: Autovacuum / full vacuum Mindaugas <mind@bi.lt>
0 siblings, 1 reply; 39+ messages in thread
From: Tom Lane @ 2006-01-17 19:32 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
Chris Browne <cbbrowne@acm.org> writes:
> alvherre@alvh.no-ip.org (Alvaro Herrera) writes:
>> Even a database-wide vacuum does not take locks on more than one table.
>> The table locks are acquired and released one by one, as the operation
>> proceeds.
> Has that changed recently? I have always seen "vacuumdb" or SQL
> "VACUUM" (without table specifications) running as one long
> transaction which doesn't release the locks that it is granted until
> the end of the transaction.
You sure? It's not supposed to, and watching a database-wide vacuum
with "select * from pg_locks" doesn't look to me like it ever has locks
on more than one table (plus the table's indexes and toast table).
regards, tom lane
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:56 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 16:43 ` Re: Autovacuum / full vacuum Chris Browne <cbbrowne@acm.org>
2006-01-17 17:30 ` Re: Autovacuum / full vacuum Alvaro Herrera <alvherre@alvh.no-ip.org>
2006-01-17 18:40 ` Re: Autovacuum / full vacuum Chris Browne <cbbrowne@acm.org>
2006-01-17 19:32 ` Re: Autovacuum / full vacuum Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-01-18 15:50 ` Mindaugas <mind@bi.lt>
2006-01-18 16:54 ` Re: Autovacuum / full vacuum Chris Browne <cbbrowne@acm.org>
2006-01-18 16:55 ` Re: Autovacuum / full vacuum Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 2 replies; 39+ messages in thread
From: Mindaugas @ 2006-01-18 15:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
> >> Even a database-wide vacuum does not take locks on more than one table.
> >> The table locks are acquired and released one by one, as the operation
> >> proceeds.
>
> > Has that changed recently? I have always seen "vacuumdb" or SQL
> > "VACUUM" (without table specifications) running as one long
> > transaction which doesn't release the locks that it is granted until
> > the end of the transaction.
>
> You sure? It's not supposed to, and watching a database-wide vacuum
> with "select * from pg_locks" doesn't look to me like it ever has locks
> on more than one table (plus the table's indexes and toast table).
Are there some plans to remove vacuum altogether?
Mindaugas
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:56 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 16:43 ` Re: Autovacuum / full vacuum Chris Browne <cbbrowne@acm.org>
2006-01-17 17:30 ` Re: Autovacuum / full vacuum Alvaro Herrera <alvherre@alvh.no-ip.org>
2006-01-17 18:40 ` Re: Autovacuum / full vacuum Chris Browne <cbbrowne@acm.org>
2006-01-17 19:32 ` Re: Autovacuum / full vacuum Tom Lane <tgl@sss.pgh.pa.us>
2006-01-18 15:50 ` Re: Autovacuum / full vacuum Mindaugas <mind@bi.lt>
@ 2006-01-18 16:54 ` Chris Browne <cbbrowne@acm.org>
1 sibling, 0 replies; 39+ messages in thread
From: Chris Browne @ 2006-01-18 16:54 UTC (permalink / raw)
To: pgsql-performance
mind@bi.lt ("Mindaugas") writes:
>> >> Even a database-wide vacuum does not take locks on more than one
>> >> table. The table locks are acquired and released one by one, as
>> >> the operation proceeds.
>>
>> > Has that changed recently? I have always seen "vacuumdb" or SQL
>> > "VACUUM" (without table specifications) running as one long
>> > transaction which doesn't release the locks that it is granted
>> > until the end of the transaction.
>>
>> You sure? It's not supposed to, and watching a database-wide
>> vacuum with "select * from pg_locks" doesn't look to me like it
>> ever has locks on more than one table (plus the table's indexes and
>> toast table).
>
> Are there some plans to remove vacuum altogether?
I don't see that on the TODO list...
http://www.postgresql.org/docs/faqs.TODO.html
To the contrary, there is a whole section on what functionality to
*ADD* to VACUUM.
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/finances.html
"There are two types of hackers working on Linux: those who can spell,
and those who can't. There is a constant, pitched battle between the
two camps."
--Russ Nelson (Linux Kernel Summary, Ver. 1.1.75 -> 1.1.76)
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:56 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 16:43 ` Re: Autovacuum / full vacuum Chris Browne <cbbrowne@acm.org>
2006-01-17 17:30 ` Re: Autovacuum / full vacuum Alvaro Herrera <alvherre@alvh.no-ip.org>
2006-01-17 18:40 ` Re: Autovacuum / full vacuum Chris Browne <cbbrowne@acm.org>
2006-01-17 19:32 ` Re: Autovacuum / full vacuum Tom Lane <tgl@sss.pgh.pa.us>
2006-01-18 15:50 ` Re: Autovacuum / full vacuum Mindaugas <mind@bi.lt>
@ 2006-01-18 16:55 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
1 sibling, 0 replies; 39+ messages in thread
From: Alvaro Herrera @ 2006-01-18 16:55 UTC (permalink / raw)
To: Mindaugas <mind@bi.lt>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Mindaugas wrote:
> > >> Even a database-wide vacuum does not take locks on more than one table.
> > >> The table locks are acquired and released one by one, as the operation
> > >> proceeds.
> >
> > > Has that changed recently? I have always seen "vacuumdb" or SQL
> > > "VACUUM" (without table specifications) running as one long
> > > transaction which doesn't release the locks that it is granted until
> > > the end of the transaction.
> >
> > You sure? It's not supposed to, and watching a database-wide vacuum
> > with "select * from pg_locks" doesn't look to me like it ever has locks
> > on more than one table (plus the table's indexes and toast table).
>
> Are there some plans to remove vacuum altogether?
No, but there are plans to make it as automatic and unintrusive as
possible. (User configuration will probably always be needed.)
--
Alvaro Herrera Developer, http://www.PostgreSQL.org
FOO MANE PADME HUM
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 14:56 ` Re: Autovacuum / full vacuum Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 16:43 ` Re: Autovacuum / full vacuum Chris Browne <cbbrowne@acm.org>
@ 2006-01-17 18:13 ` Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 0 replies; 39+ messages in thread
From: Andrew Sullivan @ 2006-01-17 18:13 UTC (permalink / raw)
To: pgsql-performance
On Tue, Jan 17, 2006 at 11:43:14AM -0500, Chris Browne wrote:
> ajs@crankycanuck.ca (Andrew Sullivan) writes:
> > Because nothing that runs automatically should ever take an exclusive
> > lock on the entire database,
> That's a bit more than what autovacuum would probably do...
Or even VACUUM FULL, as I tried to make clearer in another message:
the way I phrased it suggests that it's a simultaneous lock on the
entire database (when it is most certainly not). I didn't intend to
mislead; my apologies.
Note, though, that the actual effect for a user might look worse
than a lock on the entire database, though, if you conider
statement_timeout and certain use patterns.
Suppose you want to issue occasional VACCUM FULLs, but your
application is prepared for this, and depends on statement_timeout to
tell it "sorry, too long, try again". Now, if the exclusive lock on
any given table takes less than statement_timeout, so that each
statement is able to continue in its time, the application looks like
it's having an outage _even though_ it is actually blocked on
vacuums. (Yes, it's poor application design. There's plenty of that
in the world, and you can't always fix it.)
> There is *a* case for setting up full vacuums of *some* objects. If
> you have a table whose tuples all get modified in the course of some
> common query, that will lead to a pretty conspicuous bloating of *that
> table.*
Sure. And depending on your use model, that might be good. In many
cases, though, a "rotor table + view + truncate" approach would be
better, and would allow improved uptime. If you don't care about
uptime, and can take long outages every day, then the discussion is
sort of moot anyway. And _all_ of this is moot, as near as I can
tell, given the OP's claim that the hardware is adequate and
immutable, even though the former claim is demonstrably false.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
@ 2006-01-17 15:13 ` Tom Lane <tgl@sss.pgh.pa.us>
4 siblings, 0 replies; 39+ messages in thread
From: Tom Lane @ 2006-01-17 15:13 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; +Cc: pgsql-performance
Michael Riess <mlriess@gmx.de> writes:
> I'm curious as to why autovacuum is not designed to do full vacuum.
Locking considerations. VACUUM FULL takes an exclusive lock, which
blocks any foreground transactions that want to touch the table ---
so it's really not the sort of thing you want being launched at
unpredictable times.
regards, tom lane
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: Autovacuum / full vacuum
@ 2006-01-17 14:36 Larry Rosenman <lrosenman@pervasive.com>
0 siblings, 0 replies; 39+ messages in thread
From: Larry Rosenman @ 2006-01-17 14:36 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; pgsql-performance
Michael Riess wrote:
> Hi,
>
> yes, some heavily used tables contain approx. 90% free space after a
> week. I'll try to increase FSM even more, but I think that I will
> still have to run a full vacuum every week. Prior to 8.1 I was using
> 7.4 and ran a full vacuum every day, so the autovacuum has helped a
> lot.
>
> But actually I never understood why the database system slows down at
> all when there is much unused space in the files. Are the unused pages
> cached by the system, or is there another reason for the impact on the
> performance?
The reason is that the system needs to LOOK at the pages/tuples to see
if the tuples
are dead or not.
So, the number of dead tuples impacts the scans.
LER
>
> Mike
--
Larry Rosenman
Database Support Engineer
PERVASIVE SOFTWARE. INC.
12365B RIATA TRACE PKWY
3015
AUSTIN TX 78727-6531
Tel: 512.231.6173
Fax: 512.231.6597
Email: Larry.Rosenman@pervasive.com
Web: www.pervasive.com
^ permalink raw reply [nested|flat] 39+ messages in thread
end of thread, other threads:[~2006-01-18 22:48 UTC | newest]
Thread overview: 39+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-17 10:18 Autovacuum / full vacuum Michael Riess <mlriess@gmx.de>
2006-01-17 10:29 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2006-01-17 10:33 ` Michael Riess <mlriess@gmx.de>
2006-01-17 12:08 ` Michael Stone <mstone+postgres@mathom.us>
2006-01-17 14:04 ` Christopher Browne <cbbrowne@acm.org>
2006-01-17 14:50 ` Michael Riess <mlriess@gmx.de>
2006-01-17 15:07 ` Michael Stone <mstone+postgres@mathom.us>
2006-01-17 15:08 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 15:59 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-01-17 17:16 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 17:31 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-01-17 15:10 ` hubert depesz lubaczewski <depesz@gmail.com>
2006-01-17 22:28 ` Jim C. Nasby <jnasby@pervasive.com>
2006-01-18 14:09 ` Michael Riess <mlriess@gmx.de>
2006-01-18 22:48 ` Jim C. Nasby <jnasby@pervasive.com>
2006-01-17 14:09 ` Matthew T. O'Connor <matthew@zeut.net>
2006-01-17 14:30 ` Markus Schaber <schabi@logix-tt.com>
2006-01-17 14:33 ` Michael Riess <mlriess@gmx.de>
2006-01-17 15:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-17 17:25 ` Markus Schaber <schabi@logix-tt.com>
2006-01-17 15:09 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 11:35 ` Pandurangan R S <pandurangan.r.s@gmail.com>
2006-01-17 13:19 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2006-01-17 13:59 ` Christopher Browne <cbbrowne@acm.org>
2006-01-17 14:05 ` Michael Riess <mlriess@gmx.de>
2006-01-17 15:13 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 14:56 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 15:04 ` Michael Riess <mlriess@gmx.de>
2006-01-17 15:19 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 16:43 ` Chris Browne <cbbrowne@acm.org>
2006-01-17 17:30 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2006-01-17 18:40 ` Chris Browne <cbbrowne@acm.org>
2006-01-17 19:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-18 15:50 ` Mindaugas <mind@bi.lt>
2006-01-18 16:54 ` Chris Browne <cbbrowne@acm.org>
2006-01-18 16:55 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2006-01-17 18:13 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-01-17 15:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-17 14:36 Re: Autovacuum / full vacuum Larry Rosenman <lrosenman@pervasive.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