pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed'Real' auto vacuum?
6+ messages / 5 participants
[nested] [flat]
* 'Real' auto vacuum?
@ 2005-08-30 22:21 Ralph Mason <ralph.mason@telogis.com>
0 siblings, 2 replies; 6+ messages in thread
From: Ralph Mason @ 2005-08-30 22:21 UTC (permalink / raw)
To: pgsql-performance
This is a wild and crazy thought which I am sure is invalid for some
good reason.
But why can't postgres just vacuum itself as it goes along?
When a row is orphaned it's added to a list of possibly available rows.
When a new row is needed the list of possible rows is examined and the
first one with a transaction id less then the lowest running transaction
id is chosen to be the new row? These rows can be in a heap so it's
really fast to find one.
Like magic - no more vacuuming. No more holes for people to fall into.
Is this an oversimplification of the problem?
Ralph
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 'Real' auto vacuum?
@ 2005-08-30 22:35 mark@mark.mielke.cc
parent: Ralph Mason <ralph.mason@telogis.com>
1 sibling, 1 reply; 6+ messages in thread
From: mark@mark.mielke.cc @ 2005-08-30 22:35 UTC (permalink / raw)
To: Ralph Mason <ralph.mason@telogis.com>; +Cc: pgsql-performance
On Wed, Aug 31, 2005 at 10:21:20AM +1200, Ralph Mason wrote:
> This is a wild and crazy thought which I am sure is invalid for some
> good reason.
>
> But why can't postgres just vacuum itself as it goes along?
>
> When a row is orphaned it's added to a list of possibly available rows.
> When a new row is needed the list of possible rows is examined and the
> first one with a transaction id less then the lowest running transaction
> id is chosen to be the new row? These rows can be in a heap so it's
> really fast to find one.
>
> Like magic - no more vacuuming. No more holes for people to fall into.
Yes please. :-)
> Is this an oversimplification of the problem?
But, yeah. It's probably not that easy, especially with really big
databases. Where is this free list stored? How efficient is it to keep
track of the lowest running transaction at all times? How does one
synchronize access to this free list, to ensure that processes don't
block up waiting for access to the free list? Is the fre list
journalled to prevent corruption, and the accidental re-use of a still
in use row? And, there would be a cost to scanning this list on every
insert or update.
As an outsider (like you?) I see the current model as a design flaw as
well. A neat and tidy model on paper. Not so nice in real life. The
need to vacuum in batch mode, to keep the database from dying, seems
intuitively bad.
I think there must be answers to this problem. Even simple
optimizations, such as defining a table such that any delete or update
within a table, upon commit, will attempt to vacuum just the rows that
should not be considered free for any new transactions. If it's in
use by an active transaction, oh well. It can be picked up by a batch
run of vacuum. If it's free though - let's do it now.
I think any optimizations we come up with, will be more happily accepted
with a working patch that causes no breakage... :-)
Cheers,
mark
--
mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada
One ring to rule them all, one ring to find them, one ring to bring them all
and in the darkness bind them...
http://mark.mielke.cc/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 'Real' auto vacuum?
@ 2005-08-30 22:58 Josh Berkus <josh@agliodbs.com>
parent: Ralph Mason <ralph.mason@telogis.com>
1 sibling, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2005-08-30 22:58 UTC (permalink / raw)
To: pgsql-performance; +Cc: Ralph Mason <ralph.mason@telogis.com>
Ralph,
> When a row is orphaned it's added to a list of possibly available rows.
> When a new row is needed the list of possible rows is examined and the
> first one with a transaction id less then the lowest running transaction
> id is chosen to be the new row? These rows can be in a heap so it's
> really fast to find one.
This is the long-term plan. However, it's actually a lot harder than it
sounds. Patches welcome.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 'Real' auto vacuum?
@ 2005-08-30 23:39 Ralph Mason <ralph.mason@telogis.com>
parent: mark@mark.mielke.cc
0 siblings, 0 replies; 6+ messages in thread
From: Ralph Mason @ 2005-08-30 23:39 UTC (permalink / raw)
To: mark@mark.mielke.cc; +Cc: pgsql-performance
mark@mark.mielke.cc wrote:
>But, yeah. It's probably not that easy, especially with really big
>databases. Where is this free list stored? How efficient is it to keep
>track of the lowest running transaction at all times? How does one
>synchronize access to this free list, to ensure that processes don't
>block up waiting for access to the free list? Is the fre list
>journalled to prevent corruption, and the accidental re-use of a still
>in use row? And, there would be a cost to scanning this list on every
>insert or update.
>
>
I suspect the freelist could be stored as an index, and just handily
postgres supports those out of the box. There would be a cost yes,
but then what is the cost of adding pages to the file all the time? I
guess as with all things there is no one size fits all, so perhaps you
could turn it off - although I expect for 99.9% of the cases 'on' would
be the better choice. If it gets broken there is already the reindex
code that can fix it. A coherency / fixing / recover of a table command
would probably be a useful tool anyway.
>As an outsider (like you?) I see the current model as a design flaw as
>well. A neat and tidy model on paper. Not so nice in real life. The
>need to vacuum in batch mode, to keep the database from dying, seems
>intuitively bad.
>
>
We have a script that vacuums the database every 5 minutes, excessive -
yes, but turns out that any less is no good really. I think that this
is sub optimal, the DB work keeps running, but the vacuum can slow down
other tasks. It also probably flushes data that we would need out of
the page cache so it can look at data that isn't used often as the
vacuum runs. Not the most optimal data access pattern I could imagine.
>I think there must be answers to this problem. Even simple
>optimizations, such as defining a table such that any delete or update
>within a table, upon commit, will attempt to vacuum just the rows that
>should not be considered free for any new transactions. If it's in
>use by an active transaction, oh well. It can be picked up by a batch
>run of vacuum. If it's free though - let's do it now.
>
>
Anything would be good - I think it's the achilles heel of postgres.
Perhaps there is something simple like that could fix 95% of the problem.
>I think any optimizations we come up with, will be more happily accepted
>with a working patch that causes no breakage... :-)
>
>
>
I am sure they would.
Cheers
Ralph
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 'Real' auto vacuum?
@ 2005-08-31 05:17 Mindaugas Riauba <mind@bi.lt>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 6+ messages in thread
From: Mindaugas Riauba @ 2005-08-31 05:17 UTC (permalink / raw)
To: pgsql-performance
> > When a row is orphaned it's added to a list of possibly available rows.
> > When a new row is needed the list of possible rows is examined and the
> > first one with a transaction id less then the lowest running transaction
> > id is chosen to be the new row? These rows can be in a heap so it's
> > really fast to find one.
>
> This is the long-term plan. However, it's actually a lot harder than it
> sounds. Patches welcome.
Some ETA? Since that would be the most welcome addition for us. We
have few very heavily updated databases where table bloat and constant
vacuuming is killing performance.
Mindaugas
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 'Real' auto vacuum?
@ 2005-08-31 13:51 Brad Nicholson <bnichols@ca.afilias.info>
parent: Mindaugas Riauba <mind@bi.lt>
0 siblings, 0 replies; 6+ messages in thread
From: Brad Nicholson @ 2005-08-31 13:51 UTC (permalink / raw)
To: pgsql-performance
Mindaugas Riauba wrote:
>>>When a row is orphaned it's added to a list of possibly available rows.
>>>When a new row is needed the list of possible rows is examined and the
>>>first one with a transaction id less then the lowest running transaction
>>>id is chosen to be the new row? These rows can be in a heap so it's
>>>really fast to find one.
>>>
>>>
>>This is the long-term plan. However, it's actually a lot harder than it
>>sounds. Patches welcome.
>>
>>
>
> Some ETA? Since that would be the most welcome addition for us. We
>have few very heavily updated databases where table bloat and constant
>vacuuming is killing performance.
>
>
>
How often are you vacuuming (the definition of 'constantly' tends to
vary)? Are you vacuuming the whole database each time? If so, identify
which tables are being updated frequently, and vacuum those often.
Vacuum other tables less frequently.
Also, are you you using VACUUM FULL (if so, you certainly don't want to be).
--
Brad Nicholson 416-673-4106 bnichols@ca.afilias.info
Database Administrator, Afilias Canada Corp.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-08-31 13:51 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-30 22:21 'Real' auto vacuum? Ralph Mason <ralph.mason@telogis.com>
2005-08-30 22:35 ` mark@mark.mielke.cc
2005-08-30 23:39 ` Ralph Mason <ralph.mason@telogis.com>
2005-08-30 22:58 ` Josh Berkus <josh@agliodbs.com>
2005-08-31 05:17 ` Mindaugas Riauba <mind@bi.lt>
2005-08-31 13:51 ` Brad Nicholson <bnichols@ca.afilias.info>
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