Re: Alternative for vacuuming queue-like tables

2006-04-28 (금), 14:40 +0200, Csaba Nagy 쓰시길:
> I placed a cron job to
> cluster the queue table on it's PK index.
what does that mean?
--
my site <a href="http://www.myowndictionary.com">myowndictionary</a> was
made to help students of many languages learn them faster.

On Fri, 2006-04-28 at 15:20, kmh496 wrote:
> 2006-04-28 (금), 14:40 +0200, Csaba Nagy 쓰시길:
> > I placed a cron job to
> > cluster the queue table on it's PK index.
> what does that mean?

Means execute:

CLUSTER pk_queue_table ON queue_table;

See http://www.postgresql.org/docs/8.1/static/sql-cluster.html for more
details on CLUSTER.

This will actually rebuild the table I guess after it can lock it
exclusively. Due to the fact that the queue transactions are very short
lived, the exclusive lock is fast acquired and the clustering operation
is also fast as the table has only a few entries.

I'm not sure how this operation can work in the presence of other long
running transactions which did not touch the queue table yet, but it
actually does work, I can confirm that. Is it violating MVCC maybe ?

Cheers,
Csaba.

On Apr 28, 2006, at 9:32 AM, Csaba Nagy wrote:

> I'm not sure how this operation can work in the presence of other long
> running transactions which did not touch the queue table yet, but it
> actually does work, I can confirm that. Is it violating MVCC maybe ?

It sounds like it does potentially violate mvcc, but I haven't
thought hard about it. It also has the advantage that it rebuilds
your indexes during the clustering.

And a convenient feature is that you just define those CLUSTER
statements once, then whenever you need it, just run "CLUSTER;" all
by itself and all tables previously defined as clustered will be re-
clustered on the specified index.

Csaba Nagy <nagy(at)ecircle-ag(dot)com> writes:
> I'm not sure how this operation can work in the presence of other long
> running transactions which did not touch the queue table yet, but it
> actually does work, I can confirm that. Is it violating MVCC maybe ?

Yes :-(. I think you can get away with it if all your transactions that
use the queue table run in READ COMMITTED (not serializable) mode, and
if they work like

BEGIN;
LOCK queue_table IN some-suitable-mode;
process queue table;
COMMIT;

Grabbing the lock will ensure that CLUSTER finishes before any snapshot
is taken.

regards, tom lane

> > actually does work, I can confirm that. Is it violating MVCC maybe ?
>
> Yes :-(. I think you can get away with it if all your transactions that
[snip]

Well, I actually don't want to get away this time :-)

This table is only processed by the queue manager and that uses very
short transactions, basically only atomic insert/update/delete. No long
running transaction will ever touch that table.

So this fits perfectly my queue cleanup needs.

Cheers,
Csaba.

nagy(at)ecircle-ag(dot)com (Csaba Nagy) writes:
> On Fri, 2006-04-28 at 15:20, kmh496 wrote:
>> 2006-04-28 (금), 14:40 +0200, Csaba Nagy 쓰시길:
>> > I placed a cron job to
>> > cluster the queue table on it's PK index.
>> what does that mean?
>
> Means execute:
>
> CLUSTER pk_queue_table ON queue_table;
>
> See http://www.postgresql.org/docs/8.1/static/sql-cluster.html for more
> details on CLUSTER.
>
> This will actually rebuild the table I guess after it can lock it
> exclusively. Due to the fact that the queue transactions are very short
> lived, the exclusive lock is fast acquired and the clustering operation
> is also fast as the table has only a few entries.
>
> I'm not sure how this operation can work in the presence of other long
> running transactions which did not touch the queue table yet, but it
> actually does work, I can confirm that. Is it violating MVCC maybe ?

There is, I believe, a problem there; there is a scenario where data
can get "dropped out from under" those old connections.

This has been added to the TODO...

http://www.postgresql.org/docs/faqs.TODO.html

* Make CLUSTER preserve recently-dead tuples per MVCC requirements
--
(format nil "~S(at)~S" "cbbrowne" "ntlug.org")
http://cbbrowne.com/info/sgml.html
Should vegetarians eat animal crackers?

> There is, I believe, a problem there; there is a scenario where data
> can get "dropped out from under" those old connections.
>
> This has been added to the TODO...
>
> http://www.postgresql.org/docs/faqs.TODO.html
>
> * Make CLUSTER preserve recently-dead tuples per MVCC requirements

OK, I can see this being a problem in the general case.

However, for my queue table the current behavior is a life-saver. Would
it be possible to still provide a variant of rebuild which is documented
to not be MVCC compliant ? Something like: I don't care the old
transactions which did not touch yet this table to see the old data for
this table, I want to rebuild it.

I actually don't care about clustering in this case, only about
rebuilding the table once I can get an exclusive lock on it. I guess
this would be possible with the "switch to a copy" method except the new
table is a different entity (as mentioned in another post) and it will
not preserve the dependencies of the original table.

I guess what I'm asking for is a kind of "REBUILD TABLE" which is not
MVCC by definition but it would be useful in the mentioned queue table
case.

Cheers,
Csaba.

On Friday 28 April 2006 12:20, Csaba Nagy wrote:
> > There is, I believe, a problem there; there is a scenario where data
> > can get "dropped out from under" those old connections.
> >
> > This has been added to the TODO...
> >
> > http://www.postgresql.org/docs/faqs.TODO.html
> >
> > * Make CLUSTER preserve recently-dead tuples per MVCC requirements
>
> OK, I can see this being a problem in the general case.
>
> However, for my queue table the current behavior is a life-saver. Would
> it be possible to still provide a variant of rebuild which is documented
> to not be MVCC compliant ? Something like: I don't care the old
> transactions which did not touch yet this table to see the old data for
> this table, I want to rebuild it.
>
> I actually don't care about clustering in this case, only about
> rebuilding the table once I can get an exclusive lock on it. I guess
> this would be possible with the "switch to a copy" method except the new
> table is a different entity (as mentioned in another post) and it will
> not preserve the dependencies of the original table.
>
> I guess what I'm asking for is a kind of "REBUILD TABLE" which is not
> MVCC by definition but it would be useful in the mentioned queue table
> case.
>

vaccum full ?

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Csaba Nagy wrote:
>> There is, I believe, a problem there; there is a scenario where data
>> can get "dropped out from under" those old connections.
>>
>> This has been added to the TODO...
>>
>> http://www.postgresql.org/docs/faqs.TODO.html
>>
>> * Make CLUSTER preserve recently-dead tuples per MVCC requirements
>
> OK, I can see this being a problem in the general case.
>
> However, for my queue table the current behavior is a life-saver. Would
> it be possible to still provide a variant of rebuild which is documented
> to not be MVCC compliant ? Something like: I don't care the old
> transactions which did not touch yet this table to see the old data for
> this table, I want to rebuild it.
>
> I actually don't care about clustering in this case, only about
> rebuilding the table once I can get an exclusive lock on it. I guess
> this would be possible with the "switch to a copy" method except the new
> table is a different entity (as mentioned in another post) and it will
> not preserve the dependencies of the original table.
>
> I guess what I'm asking for is a kind of "REBUILD TABLE" which is not
> MVCC by definition but it would be useful in the mentioned queue table
> case.
The general problem seems to be that a transaction has no way to promise
never to touch a specific table. Maybe some kind of "negative lock"
would help here - you'd do "exclude table foo from transaction" at the
start of your transaction, which would cause postgres to raise an error
if you indeed tried to access that table. Vacuum could then ignore your
transaction when deciding which tuples it can safely remove from the
table foo.

This would be a nice feature IMHO - would it be possible to do something
like that, or am I overlooking something.

greetings, Florian Pflug

"Florian G. Pflug" <fgp(at)phlo(dot)org> writes:
> The general problem seems to be that a transaction has no way to promise
> never to touch a specific table. Maybe some kind of "negative lock"
> would help here - you'd do "exclude table foo from transaction" at the
> start of your transaction, which would cause postgres to raise an error
> if you indeed tried to access that table. Vacuum could then ignore your
> transaction when deciding which tuples it can safely remove from the
> table foo.

Unfortunately that really wouldn't help VACUUM at all. The nasty
problem for VACUUM is that what it has to figure out is not the oldest
transaction that it thinks is running, but the oldest transaction that
anyone else thinks is running. So what it looks through PGPROC for is
not the oldest XID, but the oldest XMIN. And even if it excluded procs
that had promised not to touch the target table, it would find that
their XIDs had been factored into other processes' XMINs, resulting
in no improvement.

As a comparison point, VACUUM already includes code to ignore backends
in other databases (if it's vacuuming a non-shared table), but it turns
out that that code is almost entirely useless :-(, because those other
backends still get factored into the XMINs computed by backends that are
in the same database as VACUUM. We've speculated about fixing this by
having each backend compute and advertise both "global" and "database
local" XMINs, but the extra cycles that'd need to be spent in *every*
snapshot computation seem like a pretty nasty penalty. And the approach
certainly does not scale to anything like per-table exclusions.

regards, tom lane

> > I guess what I'm asking for is a kind of "REBUILD TABLE" which is not
> > MVCC by definition but it would be useful in the mentioned queue table
> > case.
> >
>
> vaccum full ?

Nope, it won't work, it will still leave in all the dead tuples
potentially visible by old transactions, even if those transactions
didn't touch yet the vacuumed table. In my case that means for some
tables sometimes 1000s times of bloat, given that the table always has
few active rows and high insert/update/delete rates...

CLUSTER is locking the table exclusively, which means it will wait until
all transactions which actually touched the table are finished, and then
rebuild the table, ignoring dead rows, without caring about old
transactions who could still see the dead rows (at least this is how I
think it works based on my observations). Perfect for my purpose, but
not MVCC...

Cheers,
Csaba.

On Sat, Apr 29, 2006 at 06:39:21PM -0400, Tom Lane wrote:
> "Florian G. Pflug" <fgp(at)phlo(dot)org> writes:
> > The general problem seems to be that a transaction has no way to promise
> > never to touch a specific table. Maybe some kind of "negative lock"
> > would help here - you'd do "exclude table foo from transaction" at the
> > start of your transaction, which would cause postgres to raise an error
> > if you indeed tried to access that table. Vacuum could then ignore your
> > transaction when deciding which tuples it can safely remove from the
> > table foo.
>
> Unfortunately that really wouldn't help VACUUM at all. The nasty
> problem for VACUUM is that what it has to figure out is not the oldest
> transaction that it thinks is running, but the oldest transaction that
> anyone else thinks is running. So what it looks through PGPROC for is
> not the oldest XID, but the oldest XMIN. And even if it excluded procs
> that had promised not to touch the target table, it would find that
> their XIDs had been factored into other processes' XMINs, resulting
> in no improvement.
>
> As a comparison point, VACUUM already includes code to ignore backends
> in other databases (if it's vacuuming a non-shared table), but it turns
> out that that code is almost entirely useless :-(, because those other
> backends still get factored into the XMINs computed by backends that are
> in the same database as VACUUM. We've speculated about fixing this by
> having each backend compute and advertise both "global" and "database
> local" XMINs, but the extra cycles that'd need to be spent in *every*
> snapshot computation seem like a pretty nasty penalty. And the approach
> certainly does not scale to anything like per-table exclusions.

I'd actually been thinking about this recently, and had come up with the
following half-baked ideas:

Allow a transaction to specify exactly what tables it will be touching,
perhaps as an extension to BEGIN. Should any action that transaction
takes attempt to access a table not specified, throw an error.

A possible variant on that would be to automatically determine at
transaction start all the tables that would be accessed by that
transaction.

Once that list is available, vacuum should be able to use it to ignore
any transactions that have promised not to touch whatever table it's
vacuuming. While this doesn't help with transactions that don't make any
promises, for short-running transactions we don't really care. As long
as all long-running transactions state their intentions it should allow
for useful vacuuming of queue tables and the like.

Given that we don't care about what tables a short-running transaction
will access, we could delay the (probably expensive) determination of
what tables a backend will access until the transaction is over a
specific age. At that point the list of tables could be built and
(theoretically) the transactions XMIN adjusted accordingly.

Unfortunately this won't help with pg_dump right now. But an extension
would be to allow passing a table order into pg_dump so that it dumps
queue tables first. Once a table is dumped pg_dump shouldn't need to
access it again, so it's theoretically possible to make the "I promise
not to access these tables" list dynamic during the life of a
transaction; as pg_dump finishes with tables it could then promise not
to touch them again.

Or maybe a better idea would just be to come up with some other means
for people to do things like queue tables and session tables...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

"Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> I'd actually been thinking about this recently, and had come up with the
> following half-baked ideas:

> Allow a transaction to specify exactly what tables it will be touching,
> perhaps as an extension to BEGIN. Should any action that transaction
> takes attempt to access a table not specified, throw an error.

> A possible variant on that would be to automatically determine at
> transaction start all the tables that would be accessed by that
> transaction.

> Once that list is available, vacuum should be able to use it to ignore
> any transactions that have promised not to touch whatever table it's
> vacuuming.

No, you missed my point entirely. The above would help not at all,
unless the restrictions were somehow propagated through XMIN
calculations, which seems impracticable. (Every backend calculate a
separate XMIN with respect to every table that's being mentioned by any
other backend? I don't think so...)

regards, tom lane

Tom Lane wrote:
> "Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
>
>>I'd actually been thinking about this recently, and had come up with the
>>following half-baked ideas:
>
>>Allow a transaction to specify exactly what tables it will be touching,
>>perhaps as an extension to BEGIN. Should any action that transaction
>>takes attempt to access a table not specified, throw an error.
>
>>A possible variant on that would be to automatically determine at
>>transaction start all the tables that would be accessed by that
>>transaction.
>
>>Once that list is available, vacuum should be able to use it to ignore
>>any transactions that have promised not to touch whatever table it's
>>vacuuming.
>
> No, you missed my point entirely. The above would help not at all,
> unless the restrictions were somehow propagated through XMIN
> calculations, which seems impracticable. (Every backend calculate a
> separate XMIN with respect to every table that's being mentioned by any
> other backend? I don't think so...)

So basically the problem is that calculating a cluster-wide XMIN is fine from
a performance point of view, but to coarse from a vacuum point of view. OTOH
calculating a table-specific XMIN is fine enough for vacuum to vacuum queue-like
tables efficiently, but bad from a performance point of view.

What about doing the xmin-calculations and the xmin exporting per tablespace instead?
This would be more generic than the local/global split, because all global objects
are in the pg_global tablespace I believe. OTOH most people probably don't have more
than a handfull of tablespaces, which seems a manageable amount...

greetings, Florian Pflug

On Thu, May 04, 2006 at 03:30:34PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> > I'd actually been thinking about this recently, and had come up with the
> > following half-baked ideas:
>
> > Allow a transaction to specify exactly what tables it will be touching,
> > perhaps as an extension to BEGIN. Should any action that transaction
> > takes attempt to access a table not specified, throw an error.
>
> > A possible variant on that would be to automatically determine at
> > transaction start all the tables that would be accessed by that
> > transaction.
>
> > Once that list is available, vacuum should be able to use it to ignore
> > any transactions that have promised not to touch whatever table it's
> > vacuuming.
>
> No, you missed my point entirely. The above would help not at all,
> unless the restrictions were somehow propagated through XMIN
> calculations, which seems impracticable. (Every backend calculate a
> separate XMIN with respect to every table that's being mentioned by any
> other backend? I don't think so...)

I mentioned it further down the post, as well as the idea that we really
don't care about short-lived transactions, so theoretically we could
just compute this information for transactions that have been running
for more than some period of time. Presumably the overhead of
calculating a seperate XMIN for each table wouldn't be that great for a
transaction that's already been running 15 seconds...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461