Re: [PATCHES] Synchronous Commit Doc Patch
bruce wrote: > > Uh, we need a context diff for this. Uh, never mind. The final version has a context diff. --- > > --- > > Simon Riggs wrote: > > On Fri, 2007-07-13 at 15:01 +0200, Reviewer wrote: > > > > > I just read your patch in order to understand a little bit what will > > > happen in the next release. > > > > Thanks for the review. v2 attached. > > > > > About the guc variable wal_writer_delay, you say at the end: > > > "This parameter can only > > > + be set in the postgresql.conf file or on the server command > > > line." > > > > > > Well, I don't really understand if this parameter is set only at server > > > startup or if it is possible to modify the postgresql.conf file and then > > > reload the conf-files. > > > > A common confusion. I'm not personally in favour of using that phrase in > > the docs, though it is the standard description of when to set this type > > of parameter, so I have followed the convention. > > > > > Also, in the part about synchronous commit and fsync=off, there's just a > > > missing verb, I think you should insist between the two possibilities : > > > system or database server crash, maybe by using some bold. > > > > I've reworded that part. Thanks for spotting this. > > > > > From a user point of view, I think the documentation is clear enough, > > > but I admit beeing a bit lost in the real end of the explanation, the > > > part about the transaction status hint bits but I think it's not that > > > important. I think I understood synchronous and asynchronous commits. > > > > I've added to and reworked that a bit also. > > > > -- > > Simon Riggs > > EnterpriseDB http://www.enterprisedb.com > > [ Attachment, skipping... ] > > > > > ---(end of broadcast)--- > > TIP 2: Don't 'kill -9' the postmaster > > -- > Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us > EnterpriseDB http://www.enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Synchronous Commit Doc Patch
Uh, we need a context diff for this. --- Simon Riggs wrote: > On Fri, 2007-07-13 at 15:01 +0200, Reviewer wrote: > > > I just read your patch in order to understand a little bit what will > > happen in the next release. > > Thanks for the review. v2 attached. > > > About the guc variable wal_writer_delay, you say at the end: > > "This parameter can only > > + be set in the postgresql.conf file or on the server command line." > > > > Well, I don't really understand if this parameter is set only at server > > startup or if it is possible to modify the postgresql.conf file and then > > reload the conf-files. > > A common confusion. I'm not personally in favour of using that phrase in > the docs, though it is the standard description of when to set this type > of parameter, so I have followed the convention. > > > Also, in the part about synchronous commit and fsync=off, there's just a > > missing verb, I think you should insist between the two possibilities : > > system or database server crash, maybe by using some bold. > > I've reworded that part. Thanks for spotting this. > > > From a user point of view, I think the documentation is clear enough, > > but I admit beeing a bit lost in the real end of the explanation, the > > part about the transaction status hint bits but I think it's not that > > important. I think I understood synchronous and asynchronous commits. > > I've added to and reworked that a bit also. > > -- > Simon Riggs > EnterpriseDB http://www.enterprisedb.com [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] Synchronous Commit Doc Patch
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Simon Riggs wrote: > On Fri, 2007-07-13 at 15:01 +0200, Reviewer wrote: > > > I just read your patch in order to understand a little bit what will > > happen in the next release. > > Thanks for the review. v2 attached. > > > About the guc variable wal_writer_delay, you say at the end: > > "This parameter can only > > + be set in the postgresql.conf file or on the server command line." > > > > Well, I don't really understand if this parameter is set only at server > > startup or if it is possible to modify the postgresql.conf file and then > > reload the conf-files. > > A common confusion. I'm not personally in favour of using that phrase in > the docs, though it is the standard description of when to set this type > of parameter, so I have followed the convention. > > > Also, in the part about synchronous commit and fsync=off, there's just a > > missing verb, I think you should insist between the two possibilities : > > system or database server crash, maybe by using some bold. > > I've reworded that part. Thanks for spotting this. > > > From a user point of view, I think the documentation is clear enough, > > but I admit beeing a bit lost in the real end of the explanation, the > > part about the transaction status hint bits but I think it's not that > > important. I think I understood synchronous and asynchronous commits. > > I've added to and reworked that a bit also. > > -- > Simon Riggs > EnterpriseDB http://www.enterprisedb.com [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Synchronous Commit Doc Patch
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Simon Riggs wrote: > Doc patch only... > > To aid with the understanding of the synchronous commit technique, I've > completed the docs prior to the completion of the final version of the > patch. > > Any review comments, final doubts etc.., please say them now... > > Applies cleanly to CVS HEAD, sgml make OK. > > -- > Simon Riggs > EnterpriseDB http://www.enterprisedb.com [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.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: [PATCHES] Synchronous Commit Doc Patch
On Fri, 2007-07-13 at 14:00 -0500, Jim C. Nasby wrote: > On Fri, Jul 13, 2007 at 07:11:54PM +0100, Simon Riggs wrote: > > On Fri, 2007-07-13 at 12:38 -0500, Jim C. Nasby wrote: > > > One question... can operations that shortcut the WAL by fsyncing direct > > > to disk also use async commit? I suspect the answer is an implicit "no" > > > because the only examples I can think of all involve DDL, but it's > > > probably worth spelling that out... > > > > They can, but the WAL-avoiding ops are all designed for bulk ops, so > > you'll get something like 0.0001% benefit by using async commit for > > them. > > Yeah, I knew it wouldn't help much, I just wasn't clear on what the > effect would be. I guess I'm also a bit confused... the only shortcut I > know of (COPY into a table created in the same transaction) involves > creating files, so it would be ineligible for async commit, or are there > other shortcuts? Do we list the shortcuts anywhere in the docs? Wrote that too... http://developer.postgresql.org/pgdocs/postgres/performance-tips.html > > Thanks for reading. > > Thanks for writing. :) -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] Synchronous Commit Doc Patch
On Fri, Jul 13, 2007 at 07:11:54PM +0100, Simon Riggs wrote: > On Fri, 2007-07-13 at 12:38 -0500, Jim C. Nasby wrote: > > One question... can operations that shortcut the WAL by fsyncing direct > > to disk also use async commit? I suspect the answer is an implicit "no" > > because the only examples I can think of all involve DDL, but it's > > probably worth spelling that out... > > They can, but the WAL-avoiding ops are all designed for bulk ops, so > you'll get something like 0.0001% benefit by using async commit for > them. Yeah, I knew it wouldn't help much, I just wasn't clear on what the effect would be. I guess I'm also a bit confused... the only shortcut I know of (COPY into a table created in the same transaction) involves creating files, so it would be ineligible for async commit, or are there other shortcuts? Do we list the shortcuts anywhere in the docs? > Thanks for reading. Thanks for writing. :) -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpsgLUnwM8br.pgp Description: PGP signature
Re: [PATCHES] Synchronous Commit Doc Patch
On Fri, 2007-07-13 at 12:38 -0500, Jim C. Nasby wrote: > One question... can operations that shortcut the WAL by fsyncing direct > to disk also use async commit? I suspect the answer is an implicit "no" > because the only examples I can think of all involve DDL, but it's > probably worth spelling that out... They can, but the WAL-avoiding ops are all designed for bulk ops, so you'll get something like 0.0001% benefit by using async commit for them. They are just as safe/unsafe as other async commits. Thanks for reading. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] Synchronous Commit Doc Patch
One question... can operations that shortcut the WAL by fsyncing direct to disk also use async commit? I suspect the answer is an implicit "no" because the only examples I can think of all involve DDL, but it's probably worth spelling that out... -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgp8uCTZZQ8EF.pgp Description: PGP signature
Re: [PATCHES] Synchronous Commit Doc Patch
On Fri, 2007-07-13 at 14:59 +0100, Simon Riggs wrote: > On Fri, 2007-07-13 at 15:01 +0200, Reviewer wrote: ...A few more minor changes... thanks very much. I'll wrap these into a final submission with the main patch. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Synchronous Commit Doc Patch
On Fri, 2007-07-13 at 15:01 +0200, Reviewer wrote: > I just read your patch in order to understand a little bit what will > happen in the next release. Thanks for the review. v2 attached. > About the guc variable wal_writer_delay, you say at the end: > "This parameter can only > + be set in the postgresql.conf file or on the server command line." > > Well, I don't really understand if this parameter is set only at server > startup or if it is possible to modify the postgresql.conf file and then > reload the conf-files. A common confusion. I'm not personally in favour of using that phrase in the docs, though it is the standard description of when to set this type of parameter, so I have followed the convention. > Also, in the part about synchronous commit and fsync=off, there's just a > missing verb, I think you should insist between the two possibilities : > system or database server crash, maybe by using some bold. I've reworded that part. Thanks for spotting this. > From a user point of view, I think the documentation is clear enough, > but I admit beeing a bit lost in the real end of the explanation, the > part about the transaction status hint bits but I think it's not that > important. I think I understood synchronous and asynchronous commits. I've added to and reworked that a bit also. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com Index: doc/src/sgml/config.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/config.sgml,v retrieving revision 1.130 diff -r1.130 config.sgml 1414a1415,1454 > > > wal_writer_delay (integer) > >wal_writer_delay configuration parameter > > > > Specifies the delay between activity rounds for the WAL Writer. In each > round the writer will flush WAL to disk. It then sleeps for > wal_writer_delay milliseconds, and repeats. The default value is 200 > milliseconds (200ms). Note that on many systems, the effective > resolution of sleep delays is 10 milliseconds; setting wal_writer_delay > to a value that is not a multiple of 10 might have the same results as > setting it to the next higher multiple of 10. This parameter can only > be set in the postgresql.conf file or on the server command line. > > > > > > synchronous_commit (boolean) > >synchronous_commit configuration parameter > > > > Specifies whether explicit or implicit commit commands will wait > for WAL to be written to disk before the command returns success. > Turning this parameter off will give you asynchronous commits, > which will increase performance for some workloads, though > introduces risk of data loss, see . > > > This parameter can be set for individual transactions or sessions, > so it is advisable to use asynchronous commits only for those > transactions for which risk of data loss is acceptable. > > > Index: doc/src/sgml/wal.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/wal.sgml,v retrieving revision 1.44 diff -r1.44 wal.sgml 26c26,30transactions will remain intact. We refer to this mode of operation >as synchronous commit, since the user issuing the commit waits for >the writing of the to permanent storage. >Asynchronous commit is also possible as a performance option, >described in 396a401,594 > > > Asynchronous Commit > > > synchronous commit > > > > asynchronous commit > > > >Asynchronous Commit allows a commit command to complete faster, at the >cost that the most recent transactions will be lost if the database >should crash. This feature is particularly useful for real-time >sensor data collection applications, such as RFID tags or other >monitoring applications. > > > >Normal commits, or as we now refer to them, synchronous commits, wait >for the writing of the WAL to permanent storage >before returning control to the user. An asynchronous commit will >return control to the user before WAL >data has been written and flushed to disk, which gives a significant >performance boost if there has been few other disk accesses over the course >of this transaction. From an SQL perspective, this makes asynchronous >commits particularly useful for shorter, mainly INSERT transactions or >writes on tables small enough to reside mainly in memory. > > > >Asynchronous commits introduce the risk of data loss. There >is a time window between the time that a commit has returned >successfully to the user and the time of the WAL write during which >data will certainly be lost if the server crashes. >The data loss is deterministic: if
[PATCHES] Synchronous Commit Doc Patch
Doc patch only... To aid with the understanding of the synchronous commit technique, I've completed the docs prior to the completion of the final version of the patch. Any review comments, final doubts etc.., please say them now... Applies cleanly to CVS HEAD, sgml make OK. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com Index: doc/src/sgml/config.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/config.sgml,v retrieving revision 1.130 diff -c -r1.130 config.sgml *** doc/src/sgml/config.sgml 30 Jun 2007 19:12:01 - 1.130 --- doc/src/sgml/config.sgml 13 Jul 2007 11:17:36 - *** *** 1412,1417 --- 1412,1457 + + + wal_writer_delay (integer) + +wal_writer_delay configuration parameter + + + + Specifies the delay between activity rounds for the WAL Writer. In each + round the writer will flush WAL to disk. It then sleeps for + wal_writer_delay milliseconds, and repeats. The default value is 200 + milliseconds (200ms). Note that on many systems, the effective + resolution of sleep delays is 10 milliseconds; setting wal_writer_delay + to a value that is not a multiple of 10 might have the same results as + setting it to the next higher multiple of 10. This parameter can only + be set in the postgresql.conf file or on the server command line. + + + + + + synchronous_commit (boolean) + +synchronous_commit configuration parameter + + + + Specifies whether explicit or implicit commit commands will wait + for WAL to be written to disk before the command returns success. + Turning this parameter off will give you asynchronous commits, + which will increase performance for some workloads, though + introduces risk of data loss, see . + + + This parameter can be set for individual transactions or sessions, + so it is advisable to use asynchronous commits only for those + transactions for which risk of data loss is acceptable. + + + wal_buffers (integer) Index: doc/src/sgml/wal.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/wal.sgml,v retrieving revision 1.44 diff -c -r1.44 wal.sgml *** doc/src/sgml/wal.sgml 28 Jun 2007 00:02:37 - 1.44 --- doc/src/sgml/wal.sgml 13 Jul 2007 11:17:38 - *** *** 23,29 ordinarily meets this requirement. In fact, even if a computer is fatally damaged, if the disk drives survive they can be moved to another computer with similar hardware and all committed !transactions will remain intact. --- 23,33 ordinarily meets this requirement. In fact, even if a computer is fatally damaged, if the disk drives survive they can be moved to another computer with similar hardware and all committed !transactions will remain intact. We refer to this mode of operation !as synchronous commit, since the user issuing the commit waits for !the writing of the WAL to permanent storage. !Asynchronous commit is also possible as a performance option, !described in *** *** 394,397 --- 398,586 seem to be a problem in practice. + + + Asynchronous Commit + + + synchronous commit + + + + asynchronous commit + + + +Asynchronous Commit allows a commit command to complete faster, at the +cost that the most recent transactions will be lost if the database +should crash. This feature is particularly useful for real-time +sensor data collection applications, such as RFID tags or other +monitoring applications. + + + +Normal commits, or as we now refer to them, synchronous commits, wait +for the writing of the WAL to permanent storage +before returning control to the user. An asynchronous commit will +return control to the user before WAL +data has been written and flushed to disk, which gives a significant +performance boost if there has been few other disk accesses over the course +of this transaction. From an SQL perspective, this makes asynchronous +commits particularly useful for shorter, mainly INSERT transactions or +writes on tables small enough to reside mainly in memory. + + + +Asynchronous commits introduce the risk of data loss. There +is a time window between the time that a commit has returned +successfully to the user and the time of the WAL write during which +data will certainly be lost if the server crashes. +The data loss is deterministic: if the server stays up then the +commits will be durable, while if the server crashes there will be +certain data loss of the transactions that have mo
