Thread: XID-assigned idle transactions affect vacuum's job.
Hi, Long transactions often annoy users because if a long transaction exists on a database vacuum cannot reclaim efficiently. There are several reason why they exist on a database but it's a common case where users or applications forget to commit/rollback transactions. That is, transaction is not executing SQL and its state is 'idle in transaction' on pg_stat_activity. In this case, such transactions don't affect vacuum's job either if they aren't assigned transaction id or if they don't have a snapshot. However if they have xid it will affect vacuum's job even if they don't have a snapshot. I think that to decide which deleted tuples must be preserved we don't need to care about backend PGXACT.xid but must care about PGXACT.xmin. But current GetOldestXmin considers both of them. I guess one reason why GetOldestXmin does so is that it's also used to determine where to truncate pg_subtrans. Is there anything else reason? If nothing, I'd like to change GetOldestXmin so that it sees only PGXACT.xmin for vacuum purposes. Once we addressed this issue it'll helpful especially for user who uses read committed transaction isolation level. Regards, -- Masahiko Sawada NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
On Tue, Mar 20, 2018 at 12:22 PM, Masahiko Sawada <sawada.mshk@gmail.com> wrote: > Hi, > > Long transactions often annoy users because if a long transaction > exists on a database vacuum cannot reclaim efficiently. There are > several reason why they exist on a database but it's a common case > where users or applications forget to commit/rollback transactions. > That is, transaction is not executing SQL and its state is 'idle in > transaction' on pg_stat_activity. In this case, such transactions > don't affect vacuum's job either if they aren't assigned transaction > id or if they don't have a snapshot. However if they have xid it will > affect vacuum's job even if they don't have a snapshot. > > I think that to decide which deleted tuples must be preserved we don't > need to care about backend PGXACT.xid but must care about PGXACT.xmin. > But current GetOldestXmin considers both of them. I guess one reason > why GetOldestXmin does so is that it's also used to determine where to > truncate pg_subtrans. Is there anything else reason? > I think the main reason is that while computing snapshots, we also rely on PGXACT.xid. Basically, it can be present in some other snapshots xmin. Now, if you ignore it in vacuum (GetOldestXmin), then it is quite possible that the xid we have ignored will be part of some other snapshot's xmin which I think in turn can lead to wrong results. -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Masahiko Sawada <sawada.mshk@gmail.com> writes:
> I think that to decide which deleted tuples must be preserved we don't
> need to care about backend PGXACT.xid but must care about PGXACT.xmin.
Surely this is wrong? Assume that the XID in question is the oldest one
visible in the ProcArray (if it isn't, the question is moot anyway).
If we ignore it while setting VACUUM's cutoff, then if we come to a
tuple bearing that XID, we will think the transaction involved is
aborted (since it's not marked as committed) and proceed to remove the
tuple.
As Amit remarks, the fact that that XID will constrain the XMINs of other
open transactions means you'd usually not get any win anyway. But AFAICS,
this proposal is incorrect even without that.
regards, tom lane
Hi, Amit On Tue, Mar 20, 2018 at 8:02 PM, Amit Kapila <amit.kapila16@gmail.com> wrote: > On Tue, Mar 20, 2018 at 12:22 PM, Masahiko Sawada <sawada.mshk@gmail.com> wrote: >> Hi, >> >> Long transactions often annoy users because if a long transaction >> exists on a database vacuum cannot reclaim efficiently. There are >> several reason why they exist on a database but it's a common case >> where users or applications forget to commit/rollback transactions. >> That is, transaction is not executing SQL and its state is 'idle in >> transaction' on pg_stat_activity. In this case, such transactions >> don't affect vacuum's job either if they aren't assigned transaction >> id or if they don't have a snapshot. However if they have xid it will >> affect vacuum's job even if they don't have a snapshot. >> >> I think that to decide which deleted tuples must be preserved we don't >> need to care about backend PGXACT.xid but must care about PGXACT.xmin. >> But current GetOldestXmin considers both of them. I guess one reason >> why GetOldestXmin does so is that it's also used to determine where to >> truncate pg_subtrans. Is there anything else reason? >> > > I think the main reason is that while computing snapshots, we also > rely on PGXACT.xid. Basically, it can be present in some other > snapshots xmin. Now, if you ignore it in vacuum (GetOldestXmin), then > it is quite possible that the xid we have ignored will be part of some > other snapshot's xmin which I think in turn can lead to wrong results. > Sorry I'm still confusing. You meant that it's possible that an xmin of a snapshot can be older than the oldest PGXACT.xmin? If it's possible I'm sure the problem happens but I'm not sure it can happen because PGXACT.xmin is the oldest xid when taking a snapshot. I think that the oldest PGXACT.xmin can be either the same as or younger than the oldest PGXACT.xid. Regards, -- Masahiko Sawada NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
On Tue, Mar 20, 2018 at 11:41 PM, Masahiko Sawada <sawada.mshk@gmail.com> wrote: > Hi, Amit > > On Tue, Mar 20, 2018 at 8:02 PM, Amit Kapila <amit.kapila16@gmail.com> wrote: >> On Tue, Mar 20, 2018 at 12:22 PM, Masahiko Sawada <sawada.mshk@gmail.com> wrote: >>> Hi, >>> >>> Long transactions often annoy users because if a long transaction >>> exists on a database vacuum cannot reclaim efficiently. There are >>> several reason why they exist on a database but it's a common case >>> where users or applications forget to commit/rollback transactions. >>> That is, transaction is not executing SQL and its state is 'idle in >>> transaction' on pg_stat_activity. In this case, such transactions >>> don't affect vacuum's job either if they aren't assigned transaction >>> id or if they don't have a snapshot. However if they have xid it will >>> affect vacuum's job even if they don't have a snapshot. >>> >>> I think that to decide which deleted tuples must be preserved we don't >>> need to care about backend PGXACT.xid but must care about PGXACT.xmin. >>> But current GetOldestXmin considers both of them. I guess one reason >>> why GetOldestXmin does so is that it's also used to determine where to >>> truncate pg_subtrans. Is there anything else reason? >>> >> >> I think the main reason is that while computing snapshots, we also >> rely on PGXACT.xid. Basically, it can be present in some other >> snapshots xmin. Now, if you ignore it in vacuum (GetOldestXmin), then >> it is quite possible that the xid we have ignored will be part of some >> other snapshot's xmin which I think in turn can lead to wrong results. >> > > Sorry I'm still confusing. You meant that it's possible that an xmin > of a snapshot can be older than the oldest PGXACT.xmin? If it's > possible I'm sure the problem happens but I'm not sure it can happen > because PGXACT.xmin is the oldest xid when taking a snapshot. I think > that the oldest PGXACT.xmin can be either the same as or younger than > the oldest PGXACT.xid. > I sent the previous mail before checking the mail by Tom, and now I understood that the necessity of including PGXACT.xid. Sorry for the stupid question. Regards, -- Masahiko Sawada NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center