Hi,
we use 7.4 and suffer from table and index bloat. I tracked down the
issue to vacuum using GetOldestXmin() which returns the oldest xmin of
any database in cluster, not xmin of the current database. Yes, I've
read the thread about this issue called "Problems Vacuumi'ng" - April 2004
http://groups.google.com/groups?hl=cs&lr=&threadm=20040401215058.3FBB14307%40jimsun.LinxNet.com&rnum=3&prev=/groups%3Fhl%3Dcs%26lr%3D%26q%3DProblems%2BVacuum%26btnG%3DHledat
But our situation is not caused by misbehaved clients. Let me explain.
We're running several big OLTP databases on the server. Typical
transaction is insert/update/delete and takes less than second. Up to
this point everything is fine. But there are also tasks like backup or
initial data loading that take much more time than a second - sometimes an hour or more. And during this time _all_ our
databaseson the
server are not properly vacuumed because there is a long running
transaction in some other database. This is really big problem for
us. We're trying to avoid long running transactions but backup and
initial data loading are essential tasks. The only solution I can see
now, is to have several database clusters on the server in order to have
completly separated databases...
Is there something I'm missing? What can I do for correct vacuuming when
there is a long running transaction in some other database?
My suggestion is to add some more logic to vacuum to get correct oldest
xmin - local to current database. I don't want to add extra overhead to
all transactions but only to vacuum. I have no knowledge of Pg internals
but something like adding txn number to pg_stat_activity and then
checking this view (if it is enabled)... Would this be possible?
Bottom line: GetOldestXmin() is also called from Slony and also leads to
unexpected behaviour (at least for me) - replication is waiting for
transactions in completly unrelated databases.
Thanks, Kuba
