Thread: Vacuum and oldest xmin (again)
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
On Thu, Nov 04, 2004 at 09:31:05AM +0100, Kuba Ouhrabka wrote: > 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... We actually do that, for the reasons you say, plus because it gives us a certain degree of separability (and because it allows us to tune the caches more effectively for each type of system). > My suggestion is to add some more logic to vacuum to get correct oldest > xmin - local to current database. I think the problem is that the xids are in fact global values. This is, importantly, why you get messages about not having vacuumed in a long time in case you have a database which is not in your regular vacuum regimen. I have my doubts that the idea of the xids "local to current database" is even a coherent idea in Postgres, but I may be wrong (in which case someone is bound to correct me). A -- Andrew Sullivan | ajs@crankycanuck.ca When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes
Kuba Ouhrabka <kuba@comgate.cz> writes:
> My suggestion is to add some more logic to vacuum to get correct oldest
> xmin - local to current database.
If you read the code a little more closely, you'd see that it already does.
regards, tom lane
On Thu, Nov 04, 2004 at 10:00:23AM -0500, Tom Lane wrote: > > If you read the code a little more closely, you'd see that it already does. Hmm, so obviously I was confused in my other message. But I've seen the same sort of effect as the OP: transactions in another database on the same back end seem to prevent some recovery by vacuum in the local back end. Is this just an illusion? (I can probably chalk it up to a later-completed transaction in the same back end, of course. I don't know if the same is true for the OP.) A -- Andrew Sullivan | ajs@crankycanuck.ca This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie
Andrew Sullivan <ajs@crankycanuck.ca> writes:
> On Thu, Nov 04, 2004 at 10:00:23AM -0500, Tom Lane wrote:
>> If you read the code a little more closely, you'd see that it already does.
> Hmm, so obviously I was confused in my other message. But I've seen
> the same sort of effect as the OP: transactions in another database
> on the same back end seem to prevent some recovery by vacuum in the
> local back end. Is this just an illusion?
I think it's most likely that there were also old transactions in the
current database. Only the shared tables (pg_shadow, pg_database,
pg_group) are vacuumed using a cutoff that depends on non-local
transactions.
Looking at the back versions, it appears this logic was put in in 7.2;
is it possible you are remembering the behavior of older versions?
regards, tom lane
>>My suggestion is to add some more logic to vacuum to get correct oldest >>xmin - local to current database. > > > If you read the code a little more closely, you'd see that it already does. > > regards, tom lane Could you pls tell what has changed since 7.4? I'm not able to find it... Thanks Kuba
Hi, > I think it's most likely that there were also old transactions in the > current database. Only the shared tables (pg_shadow, pg_database, > pg_group) are vacuumed using a cutoff that depends on non-local > transactions. in my case, there are really no old transactions in current database. > Looking at the back versions, it appears this logic was put in in 7.2; > is it possible you are remembering the behavior of older versions? And it's on 7.4... The problem is fully described in thread I mentioned earlier, Tom's excellent explanation can be found here: http://groups.google.com/groups?hl=cs&lr=&frame=right&th=5227028cb3449572&seekm=11390.1080964720%40sss.pgh.pa.us#link14 Kuba
Kuba Ouhrabka <kuba@comgate.cz> writes: > The problem is fully described in thread I mentioned earlier, Tom's > excellent explanation can be found here: > http://groups.google.com/groups?hl=cs&lr=&frame=right&th=5227028cb3449572&seekm=11390.1080964720%40sss.pgh.pa.us#link14 Oh, that thing. Well, my opinion has not changed since April --- I don't think the problem justifies slowing down every transaction start. regards, tom lane
On Thu, Nov 04, 2004 at 10:48:09AM -0500, Tom Lane wrote: > Looking at the back versions, it appears this logic was put in in 7.2; > is it possible you are remembering the behavior of older versions? Quite likely, in fact. Thanks for clearing that up. A -- Andrew Sullivan | ajs@crankycanuck.ca The plural of anecdote is not data. --Roger Brinner
>>The problem is fully described in thread I mentioned earlier, Tom's >>excellent explanation can be found here: >>http://groups.google.com/groups?hl=cs&lr=&frame=right&th=5227028cb3449572&seekm=11390.1080964720%40sss.pgh.pa.us#link14 > > > Oh, that thing. Well, my opinion has not changed since April --- I > don't think the problem justifies slowing down every transaction start. My question was if the slowdown of every transaction start is really needed - If it is possible to find some other solution, for example add more logic to vacuum to pick up correct txn id (or little bit more "correct"). But unfortunately I have no idea how to do this because of my limited knowledge of Pg internals, the diffucult part I guess are the shared relations... Kuba