Thread: VACUUM and open transactions
I'm running postgres 8.0.8. I have a table that is updated very rapidly, so I vacuum it every 10 minutes. The problem is that I sometimes have transactions that hang out for a long time without doing anything. These transactions are preventing VACUUM from cleaning up tuples that were created and then deleted in transactions that started way after the hanging one. Is there any way to fix this?
On Thu, Oct 19, 2006 at 04:18:50PM -0400, Joseph Shraibman wrote: > I'm running postgres 8.0.8. I have a table that is updated very > rapidly, so I vacuum it every 10 minutes. The problem is that I > sometimes have transactions that hang out for a long time without doing > anything. These transactions are preventing VACUUM from cleaning up > tuples that were created and then deleted in transactions that started > way after the hanging one. Is there any way to fix this? Sure, don't keep transactions open for so long. Is there a particular reason you do that? The problem is that the "old" transaction can see effects of later started transactions, so VACUUM can't delete the later stuff either... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
Martijn van Oosterhout wrote: > Sure, don't keep transactions open for so long. Is there a particular > reason you do that? Because I have a leak somewhere? Even if the transaction isn't that old it will still cause me some table bloat in the meantime. > > The problem is that the "old" transaction can see effects of later > started transactions, so VACUUM can't delete the later stuff either... How can it see effects of transactions that started after it?
On Thu, Oct 19, 2006 at 04:25:09PM -0400, Joseph S wrote: > >The problem is that the "old" transaction can see effects of later > >started transactions, so VACUUM can't delete the later stuff either... > > How can it see effects of transactions that started after it? Check the documentation for the difference the READ COMMITTED and SERIALIZABLE transaction. The former (the default) will see the results of any committed transactions, even if they started later. http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html#XACT-READ-COMMITTED Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
But if the tuple in question was created and then deleted after the transaction, the transaction should still not need to see it. Martijn van Oosterhout wrote: > On Thu, Oct 19, 2006 at 04:25:09PM -0400, Joseph S wrote: >>> The problem is that the "old" transaction can see effects of later >>> started transactions, so VACUUM can't delete the later stuff either... >> How can it see effects of transactions that started after it? > > Check the documentation for the difference the READ COMMITTED and > SERIALIZABLE transaction. The former (the default) will see the results > of any committed transactions, even if they started later. > > http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html#XACT-READ-COMMITTED > > Have a nice day,
Joseph S <jks@selectacast.net> writes:
> But if the tuple in question was created and then deleted after the
> transaction, the transaction should still not need to see it.
No, because it might have taken a snapshot during the interval where the
tuple was good.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 10/19/06 15:22, Martijn van Oosterhout wrote: > On Thu, Oct 19, 2006 at 04:18:50PM -0400, Joseph Shraibman wrote: >> I'm running postgres 8.0.8. I have a table that is updated very >> rapidly, so I vacuum it every 10 minutes. The problem is that I >> sometimes have transactions that hang out for a long time without doing >> anything. These transactions are preventing VACUUM from cleaning up >> tuples that were created and then deleted in transactions that started >> way after the hanging one. Is there any way to fix this? > > Sure, don't keep transactions open for so long. Is there a particular > reason you do that? Not every system is OLTP. If these are long-open online transactions, then as MvO implies, you definitely have a problem that needs fixing. OTOH, if these are validly long-running updaters, you (the OP) will have to think outside the box for techniques to break that million- row UPDATE statement into short-time committable chunks. "Canditate key" tables and PL/pgSQL or a scripting language are one possibility. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFOEc4S9HxQb37XmcRAke6AJ4hh6YOxEoWPNyczc5/ajkWH7fqxwCfXJy3 izu264kElNxESaC0qjdPc68= =E8tr -----END PGP SIGNATURE-----
I realize this thread is old, but I just conducted an experiment with pg 8.0.10 and a transaction with a SERIALIZABLE isolation level does prevent VACUUM from reclaiming rows that were created and then obsoleted in a subsequent transaction. Martijn van Oosterhout wrote: > On Thu, Oct 19, 2006 at 04:25:09PM -0400, Joseph S wrote: >>> The problem is that the "old" transaction can see effects of later >>> started transactions, so VACUUM can't delete the later stuff either... >> How can it see effects of transactions that started after it? > > Check the documentation for the difference the READ COMMITTED and > SERIALIZABLE transaction. The former (the default) will see the results > of any committed transactions, even if they started later. > > http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html#XACT-READ-COMMITTED > > Have a nice day,
Joseph S wrote: > I realize this thread is old, but I just conducted an experiment with pg > 8.0.10 and a transaction with a SERIALIZABLE isolation level does > prevent VACUUM from reclaiming rows that were created and then obsoleted > in a subsequent transaction. Right. This is expected. VACUUM cannot remove them because the serializable transaction might still want to see those rows. (I am assuming the serializable transaction is still running when the vacuum starts. If that's not the case, please explain better). -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
The serializable transaction *can't* see those rows, they were created and obsoleted after the start of the transaction. The point of make the transaction serializable in the first place was to allow VACUUM to reclaim those rows. Alvaro Herrera wrote: > Joseph S wrote: >> I realize this thread is old, but I just conducted an experiment with pg >> 8.0.10 and a transaction with a SERIALIZABLE isolation level does >> prevent VACUUM from reclaiming rows that were created and then obsoleted >> in a subsequent transaction. > > Right. This is expected. VACUUM cannot remove them because the > serializable transaction might still want to see those rows. (I am > assuming the serializable transaction is still running when the vacuum > starts. If that's not the case, please explain better). >
> Alvaro Herrera wrote: > >Joseph S wrote: > >>I realize this thread is old, but I just conducted an experiment with pg > >>8.0.10 and a transaction with a SERIALIZABLE isolation level does > >>prevent VACUUM from reclaiming rows that were created and then obsoleted > >> in a subsequent transaction. > > > >Right. This is expected. VACUUM cannot remove them because the > >serializable transaction might still want to see those rows. (I am > >assuming the serializable transaction is still running when the vacuum > >starts. If that's not the case, please explain better). Joseph S wrote: > The serializable transaction *can't* see those rows, they were created > and obsoleted after the start of the transaction. The point of make the > transaction serializable in the first place was to allow VACUUM to > reclaim those rows. Well, if you're thinking that vacuum will reclaim those rows just because the transaction is serializable and thus the rows are invisible, you're mistaken. If that's the only reason to set the transaction serializable, you'll be better off changing it to read committed because you're not getting that benefit. It's possible that there's some optimization to be made here, but right now it doesn't exist. (And to be frank, I haven't thought about the issue to be certain that the optimization is possible at all -- maybe there's some reason why it's not, for example ctid chains or whatever). -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
>>> Right. This is expected. VACUUM cannot remove them because the
>>> serializable transaction might still want to see those rows.
> Joseph S wrote:
>> The serializable transaction *can't* see those rows, they were created
>> and obsoleted after the start of the transaction. The point of make the
>> transaction serializable in the first place was to allow VACUUM to
>> reclaim those rows.
> Well, if you're thinking that vacuum will reclaim those rows just
> because the transaction is serializable and thus the rows are invisible,
> you're mistaken.
VACUUM doesn't even know that the transaction is serializable, much less
specific details of which other transactions it can or can't see the
effects of. The rule is that anything newer than the "xmin" advertised
by the transaction might be visible.
This is, of course, an engineering tradeoff: we could cause transactions
to advertise more-complete details of the snapshots they're using, and
then try to teach VACUUM to take advantage of that knowledge. But the
distributed overhead of that is daunting, and the benefits uncertain.
Personally I think that xmin-only advertisement is a pretty good
tradeoff.
regards, tom lane