Re: Vaccuming dead rows on busy databases

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

...VACUUM FULL WAIT
> Sounds like a deadlock waiting to happen :-(

Yeah, this sounds more and more like a job for a client application.

> AFAIK the general practice is to just accept the fact that vacuum can't
> remove recently-dead tuples. You should look into whether you can't
> shorten your transactions --- very-long-running transactions create
> other performance issues besides vacuum not removing stuff.

Sure, but does a long-running transaction on a different database in
the same cluster have any other consequences?

At any rate, I suppose this is something I can probably code around. If it
gets too bad, I'll try to coordinate the timing a bit more between the
databases, increase the frequency of vacuum, or simply kill some of the
long-running transactions before kicking off the vacuum.

Brian Hurt wrote:
> My understanding is that vacuum can not delete any row that was deleted
> after the oldest outstanding transaction. [snip]

Thanks, that was a good explanation.

> Which is why having only a single transaction open, but it's been open
> for 24 hours, is a problem.

Well, 24 hours is a bit much :), but perhaps until a database-specific xmin
is enabled, I'll also consider using an entirely different cluster for
databases which do long-runnning queries.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation
PGP Key: 0x14964AC8 200612181419
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFFhvZvvJuQZxSWSsgRArxYAKCh5YUbJosJiMDhon2vghIq0f0yIACeKdjD
0QK0N8P+C4odb7Vfvi5wy/U=
=Cvwh
-----END PGP SIGNATURE-----