Re: [HACKERS] MultiXactID Wrap-Around
Thank you for the feed-back. In fact the risk seems neglibible, but it means that at least I'm not misunderstanding what that code does...Tom Lane [EMAIL PROTECTED] ha scritto: paolo romano <[EMAIL PROTECTED]> writes: My doubts now concern MultixactID wrap-around management. Afaics, it is possible to spawn multixactids so quickly to have a wrap-around and to start overwriting the data stored in the offset slru (but analogous considerations apply to the member slru as well).I looked into this when the multixact code was written. There is atheoretical risk but I think it's entirely theoretical. MXIDs areunlikely to be consumed faster than XIDs over the long term, and alsocan be recycled sooner. So you'd run up against XID wraparound (whichwe do defend against) first. regards, tom lane Chiacchiera con i tuoi amici in tempo reale! http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com
Re: [HACKERS] [COMMITTERS] pgsql: Do a pass of code review for the ALTER TABLE
I'm keeping on studying multixact.c and log management, and I hope you can help me, as usual, in clearing my doubts.My doubts now concern MultixactID wrap-around management. Afaics, it is possible to spawn multixactids so quickly to have a wrap-around and to start overwriting the data stored in the offset slru (but analogous considerations apply to the member slru as well). This would cause corruption, if the overwritten info was still needed, e.g., by a (very) long-running transaction. This is of course very unlikely in practice, but yet still possible in theory.In GetNewMultiXactId() wrap-around of MultiXactId seems to be simply handled this way: 00780 _/* Handle wraparound of the nextMXact counter */00781 if (MultiXactState-nextMXact FirstMultiXactId)00782 MultiXactState-nextMXact = FirstMultiXactId; I cannot see how this may avoid possible overwriting of still needed data. To address such an issue shouldn't one need to check against OldestMemberMXactId, OldestVisibleMXactId? Or, alternatively, rely on an approach similar to the one taken to handle standard XID generation (xidWarnLimit, see GetNewTransactionId)?Is it me who's missing something or is it just that such a case has been considered so unlikely not to motivate additional overheads/checks?Thanks in advance! Paolo Chiacchiera con i tuoi amici in tempo reale! http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com
[HACKERS] MultiXactID Wrap-Around
ops, i did forget to update the e-mail subject, sorry. I am reposting it with an appropriate one. I'm keeping on studying multixact.c and log management, and I hope you can help me, as usual, in clearing my doubts.My doubts now concern MultixactID wrap-around management. Afaics, it is possible to spawn multixactids so quickly to have a wrap-around and to start overwriting the data stored in the offset slru (but analogous considerations apply to the member slru as well). This would cause corruption, if the overwritten info was still needed, e.g., by a (very) long-running transaction. This is of course very unlikely in practice, but yet still possible in theory.In GetNewMultiXactId() wrap-around of MultiXactId seems to be simply handled this way: 00780 _/* Handle wraparound of the nextMXact counter */00781 if (MultiXactState-nextMXact FirstMultiXactId)00782 MultiXactState-nextMXact = FirstMultiXactId; I cannot see how this may avoid possible overwriting of still needed data. To address such an issue shouldn't one need to check against OldestMemberMXactId, OldestVisibleMXactId? Or, alternatively, rely on an approach similar to the one taken to handle standard XID generation (xidWarnLimit, see GetNewTransactionId)?Is it me who's missing something or is it just that such a case has been considered so unlikely not to motivate additional overheads/checks?Thanks in advance! Paolo Chiacchiera con i tuoi amici in tempo reale! http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com
Re: [HACKERS] MultiXacts WAL
No, it's not safe to release them until 2nd phase commit.Imagine table foo and table bar. Table bar has a foreign key reference to foo.1. Transaction A inserts a row to bar, referencing row R in foo. This acquires a shared lock on R.2. Transaction A precommits, releasing the lock.3. Transaction B deletes R. The new row inserted by A is not visible to B, so the delete succeeds.4. Transaction A and B commit. Oops, the new row in bar references R that doesn't exist anymore.Holding the lock until the true end of transaction, the 2nd phase of commit, blocks B from deleting R.- Heikki---(end of broadcast)---TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanlyHeikki, thanks for the clarifications. I was not considering the additional issues arising in case of referential integrity constraints... in fact i was citing a known result from theory books on 2PC, which did not include FK in their speculations... But as usual in theory things look always much simpler than in practice!Anyway, again in theory, if one wanted to minimize logging overhead for shared locks, one might adopt a different treatment for (i) regular shared locks (i.e. locks due to plain reads not requiring durability in case of 2PC) and (ii) shared locks held because some SQL command is referencing a tuple via a FK, which have to be persisted until the 2-nd 2PC phase (There is no any other scenario in which you *must* persist shared locks, is there?)Of course, in practice distinguishing the 2 above situations may not be so simple and it still has to be shown whether such an optimization is really worth of... By the way, postgresql is detailedly logging *every* single shared lock, even though this is actually needed only if (i) the transaction turns out to be a distributed one (i.e. prepare is issued on that transactions), AND (ii) the shared lock is due to ensure validity of a FK. AFAICS, in most practical workloads (i) local transactions dominate distributed ones and (ii) shared locks due to plain reads dominate locks due to FK, so the current implementaion does not seem to be optimizing the most frequent scenario.regards, paolo Chiacchiera con i tuoi amici in tempo reale! http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com
Re: [HACKERS] MultiXacts WAL
There is no "regular shared locks" in postgres in that sense. Shared locks are only used for maintaining FK integrity. Or by manually issuing a SELECT FOR SHARE, but that's also for maintaining integrity. MVCC rules take care of the "plain reads". If you're not familiar with MVCC, it's explained in chapter 12 of the manual.The source code in heapam.c also mentions Point In Time Recovery to require logging the locks, though I'm not sure why.Thanks for your explanations, now I can see what was confusing me.The problem with is that we don't know beforehand if a transaction is a distributed one or not.Feel free to write a benchmark to see how much difference the logging makes! If it's significant, I'm sure we can figure out ways to improve it.Now that i finally see that multixacts are due only to explicit shared lock requests or to FKs, I tend to agree with tom's original doubts about the actual impact of the multixact related logging activities. Of course in practice such an impact would vary from application to application, so it may still make sense for some classes of workloads to avoid multixact logging, assuming they contain no distributed transactions and finding an hack to know beforehand whether a transaction is distributed or not... BTW, if i manage to find some free time to do some performance tests, i'll sure let you know!Thanks again, Paolo Chiacchiera con i tuoi amici in tempo reale! http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com
Re: [HACKERS] MultiXacts WAL
May be this is needed to support savepoints/subtransactions? Or is it something else that i am missing?It's for two-phase commit. A prepared transaction can hold locks that need to be recovered.When a transaction enters (successfully) the prepared state it only retains its exclusive locks and releases any shared locks (i.e. multixacts)... or, at least, that's how it should be in principle according to serializiaton theory, i haven't yet checked out if this is what is done in postgresql . Chiacchiera con i tuoi amici in tempo reale! http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com
Re: [HACKERS] MultiXacts WAL
Tom Lane [EMAIL PROTECTED] ha scritto: paolo romano <[EMAIL PROTECTED]> writes: The point i am missing is the need to be able to completely recover multixacts offsets and members data. These carry information about current transactions holding shared locks on db tuples, which should not be essential for recovery purposes.This might be optimizable if we want to assume that multixacts will neverbe used for any purpose except holding locks, but that seems a bit shortsighted. Is there any actually significant advantage to not loggingthis information? regards, tom laneI can see two main advantages:* Reduced I/O Activity: during transaction processing: current workloads are typically dominated by reads (rather than updates)... and reads give rise to multixacts (if there are at least two transactions reading the same page or if an explicit lock request is performed through heap_lock_tuple). And (long) transactions can read a lot of tuples, which directly translates into (long) multixact logging sooner or later. To accurately estimate the possible performance gain one should perform some profiling, but at first glance ISTM that there are good potentialities.* Reduced Recovery Time: because of shorter logs less data structures to rebuild... and reducing recovery time helps improving system availability so should not be overlooked.Regards, Paolo Chiacchiera con i tuoi amici in tempo reale! http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com
Re: [HACKERS] MultiXacts WAL
In PostgreSQL, shared locks are not taken when just reading data. They're used to enforce foreign key constraints. When inserting a row to a table with a foreign key, the row in the parent table is locked to keep another transaction from deleting it. It's not safe to release the lock before end of transaction.Releasing shared locks (whether used for plain reading or enforcing foreign keys) before transaction end would be clearly wrong.The original point I was moving is if there were any concrete reason (which still I can't see) to require Multixacts recoverability (by means of logging). Concerning the prepare state of two phase commit, as I was pointing out in my previous post, shared locks can safely be released once a transaction gets precommitted, hence they do not have to be made durable. Chiacchiera con i tuoi amici in tempo reale! http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com
Re: [HACKERS] MultiXacts WAL
<[EMAIL PROTECTED]>Yeah, it's difficult to believe that multixact stuff could form anoticeable fraction of the total WAL load, except perhaps under reallypathological circumstances, because the code just isn't supposed to beexercised often. So I don't think this is worth pursuing. Paolo's freeto try to prove the opposite of course ... but I'd want to see numbersnot speculation. regards, tom laneTom is right, mine are indeed just plain speculations, motivated by my original doubt concerning whether there were hidden reasons for requiring multixacts recoverability.I don't know if I'll find the time to do some performance tests, at least in the short term, but I've enjoyed to exchange my views with you all, so thanks a lot for your feedback!Just a curiosity, what kind of benchmarks would you use to evaluate this effect? I am quite familiar with TPC-C and TPC-W, but i am a newbie of postgresql community so i was wondering if you were using any reference benchmark Chiacchiera con i tuoi amici in tempo reale! http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com
[HACKERS] MultiXacts WAL
I am working on a possible extension of postgresql mvcc to support very timely failure masking in the context of three-tier applications so i am currently studying Postgresql internals...I am wondering what are the reasons why both the MultiXactIds and the corresponding OFFSETs and MEMBERs are currently persisted.In multixact.c 's documentation on the top of the file you can find the following statement:"...This allows us to completely rebuild the data entered since the last checkpoint during XLOG replay..."I can see the need to persist (not eagerly) multixactids to avoid wraparounds. Essentially, mass storage is used to extend the limited capabity of slrus data structures in shared memory.The point i am missing is the need to be able to completely recover multixacts offsets and members data. These carry information about current transactions holding shared locks on db tuples, which should not be essential for recovery purposes. After a crash you want to recover the content of your data, not the presence of shared locks on any tuple. AFAICS, this seems true for both committed/aborted transactions (which being concluded do not care any more about the fact that they could have held any shared lock), as well as prepared transactions (which only need to recover their exclusive locks).I have tried to dig around the comments within the main multixact.c functions and i have walked through this comment (CreateMultiXactId())):"...The only way for the MXID to be referenced from any data page is for heap_lock_tuple() to have put it there, and heap_lock_tuple() generates an XLOG record that must follow ours... "But still I cannot see the need to recover complete shared locks info (i.e. not only multixactids but also the corresponding registered transactionids that were holding the lock)...May be this is needed to support savepoints/subtransactions? Or is it something else that i am missing?Thanks for your precious help! Paolo Chiacchiera con i tuoi amici in tempo reale! http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com
