Re: [HACKERS] MVCC, undo log, and HOT
On Tue, 23 Oct 2007 09:07:52 -0400 "Jonah H. Harris" <[EMAIL PROTECTED]> wrote: > On 10/23/07, Andrew Dunstan <[EMAIL PROTECTED]> wrote: > > It would be a major mistake to think there's no work left to > > do in improving update performance. > > Agreed. That would be a very short-sighted move. > I don't think anyone is suggesting that. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ signature.asc Description: PGP signature
Re: [HACKERS] MVCC, undo log, and HOT
On 10/23/07, Simon Riggs <[EMAIL PROTECTED]> wrote: > Don't *need* would be better. Forgot to mention I agree. What's done is done. I'm not beating that UNDO horse anymore; it's long past dead. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] MVCC, undo log, and HOT
On 10/23/07, Andrew Dunstan <[EMAIL PROTECTED]> wrote: > It would be a major mistake to think there's no work left to > do in improving update performance. Agreed. That would be a very short-sighted move. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] MVCC, undo log, and HOT
On 10/23/07, Simon Riggs <[EMAIL PROTECTED]> wrote: > > We never actually considred undo > > I did, but eventually ruled it out during the HOT design process. But > then I considered a ton of other things and ruled them out also. > > Can't see a reason to bring it up again, so perhaps we should add it to > the definitely don't want list. Don't *need* would be better. Actually, my initial UNDO design and prototype had several other advantages over HOT (better performance, inherent time-travel, smaller on-disk footprint, and *no* vacuum). The major issue was due to the amount of architecture changes Postgres would've required and Bruce's advice regarding the community's inability to accept such a large and complex change. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] MVCC, undo log, and HOT
On Mon, 2007-10-22 at 11:00 -0400, Bruce Momjian wrote: > Joshua D. Drake wrote: > > Josh Berkus wrote: > > > Bruce Momjian wrote: > > >> Those who have been with the community from long ago might remember > > >> discussion about implementing a undo log. The big advantage of this is > > >> that it allows UPDATE to _replace_ rows and limits the amount of cleanup > > >> required for UPDATEs. > > >> > > >> I am hoping that with HOT we will no longer have any need to even > > >> consider undo. > > >> > > > > > > We were considering it? > > > > I don't ever remember us considering it seriously. > > > > > > > > I certainly wasn't. I've enough experience with Oracle and InnoDB to > > > see that an undo log is its own set of performane issues. No thanks. > > > > > > > It certainly does. > > We never actually considred undo I did, but eventually ruled it out during the HOT design process. But then I considered a ton of other things and ruled them out also. Can't see a reason to bring it up again, so perhaps we should add it to the definitely don't want list. Don't *need* would be better. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] MVCC, undo log, and HOT
"Andrew Dunstan" <[EMAIL PROTECTED]> writes: > HOT is cool, but it really doesn't solve the whole problem. It works for a > significant class of problems, but for example it won't have any significant > effect on the app I'm currently working on which is very index-rich. It would > be a major mistake to think there's no work left to do in improving update > performance. Another use case it doesn't address is updating a large fraction of the table. If you do a single unconstrained "UPDATE foo SET x=y" your table and indexes will double in size and never shrink back. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(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 cleanly
Re: [HACKERS] MVCC, undo log, and HOT
Bruce Momjian wrote: Josh Berkus wrote: Bruce, We never actually considred undo, but high UPDATE activity was one of the areas we historically handled poorly compared to undo systems, and undo would have been one way to improve that area. I think with HOT we have improved high UPDATE activity enough that the undo benefits are no longer attractive (and of course the undo disadvantages were never attractive). If you're asking if we should remove the TODO (is there one?) and replace it with a FAQ item called "Why PostgreSQL doesn't have an UNDO LOG", then my vote is "yes". No, it never got close to being a TODO item. It was more a limitation we had that is now fixed. HOT is cool, but it really doesn't solve the whole problem. It works for a significant class of problems, but for example it won't have any significant effect on the app I'm currently working on which is very index-rich. It would be a major mistake to think there's no work left to do in improving update performance. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] MVCC, undo log, and HOT
Josh Berkus wrote: > Bruce, > > > We never actually considred undo, but high UPDATE activity was one of > > the areas we historically handled poorly compared to undo systems, and > > undo would have been one way to improve that area. I think with HOT we > > have improved high UPDATE activity enough that the undo benefits are no > > longer attractive (and of course the undo disadvantages were never > > attractive). > > > > If you're asking if we should remove the TODO (is there one?) and > replace it with a FAQ item called "Why PostgreSQL doesn't have an UNDO > LOG", then my vote is "yes". No, it never got close to being a TODO item. It was more a limitation we had that is now fixed. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] MVCC, undo log, and HOT
Bruce, We never actually considred undo, but high UPDATE activity was one of the areas we historically handled poorly compared to undo systems, and undo would have been one way to improve that area. I think with HOT we have improved high UPDATE activity enough that the undo benefits are no longer attractive (and of course the undo disadvantages were never attractive). If you're asking if we should remove the TODO (is there one?) and replace it with a FAQ item called "Why PostgreSQL doesn't have an UNDO LOG", then my vote is "yes". --Josh ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] MVCC, undo log, and HOT
Joshua D. Drake wrote: > Josh Berkus wrote: > > Bruce Momjian wrote: > >> Those who have been with the community from long ago might remember > >> discussion about implementing a undo log. The big advantage of this is > >> that it allows UPDATE to _replace_ rows and limits the amount of cleanup > >> required for UPDATEs. > >> > >> I am hoping that with HOT we will no longer have any need to even > >> consider undo. > >> > > > > We were considering it? > > I don't ever remember us considering it seriously. > > > > > I certainly wasn't. I've enough experience with Oracle and InnoDB to > > see that an undo log is its own set of performane issues. No thanks. > > > > It certainly does. We never actually considred undo, but high UPDATE activity was one of the areas we historically handled poorly compared to undo systems, and undo would have been one way to improve that area. I think with HOT we have improved high UPDATE activity enough that the undo benefits are no longer attractive (and of course the undo disadvantages were never attractive). -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] MVCC, undo log, and HOT
Josh Berkus wrote: Bruce Momjian wrote: Those who have been with the community from long ago might remember discussion about implementing a undo log. The big advantage of this is that it allows UPDATE to _replace_ rows and limits the amount of cleanup required for UPDATEs. I am hoping that with HOT we will no longer have any need to even consider undo. We were considering it? I don't ever remember us considering it seriously. I certainly wasn't. I've enough experience with Oracle and InnoDB to see that an undo log is its own set of performane issues. No thanks. It certainly does. Joshua D. Drake ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] MVCC, undo log, and HOT
Bruce Momjian wrote: Those who have been with the community from long ago might remember discussion about implementing a undo log. The big advantage of this is that it allows UPDATE to _replace_ rows and limits the amount of cleanup required for UPDATEs. I am hoping that with HOT we will no longer have any need to even consider undo. We were considering it? I certainly wasn't. I've enough experience with Oracle and InnoDB to see that an undo log is its own set of performance issues. No thanks. Hopefully the "multi-thread the backend" idea is dead, too. --Josh ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
