Vaccuming dead rows on busy databases
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Is there a general strategy out there for removing dead rows on
busy databases? I've learned that both a vacuum and a vacuum full
will not remove dead rows if there is an open transaction anywhere
on the entire cluster. Closing open transactions is not always a
viable solution, especially for periodic cron-driven vacuums. What
do people generally do to get around this problem? I can think of
a few possible solutions, but wanted to see if there was a best
practice before I went too much further. All my solutions are
external as well: I'd like to see something like VACUUM FULL WAIT. :)
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200612181331
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFFht6qvJuQZxSWSsgRArZDAKD9zfrsuysep38o+UaW/2tDL/AdvACguZIO
jd0JERBi0AwBBZj+xu80Qtg=
=vSuy
-----END PGP SIGNATURE-----
"Greg Sabino Mullane" <greg@turnstep.com> writes:
I'd like to see something like VACUUM FULL WAIT. :)
Sounds like a deadlock waiting to happen :-(
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.
regards, tom lane
Tom Lane wrote:
"Greg Sabino Mullane" <greg@turnstep.com> writes:
I'd like to see something like VACUUM FULL WAIT. :)
Sounds like a deadlock waiting to happen :-(
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.
It seems to me that the most common support problem I keep seeing on the
mailing lists is VACUUM not working well because of long running
transactions. If I understand it correctly, people have talked about
reducing the problem by tracking xmin (or something, sorry if I'm
getting this wrong) on a per table basis rather and per cluster. Now
I'm sure this is not simple and I know I don't have the skills to do it,
but I think it would resolve (or at least significantly mitigate) what I
perceive as one of the biggest usage problems with PostgreSQL.
Comments?
-----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@turnstep.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-----