Re: [HACKERS] Question about durability and postgresql.
On Fri, Feb 20, 2015 at 5:09 PM, Alfred Perlstein bri...@mu.org wrote: Hello, We have a combination of 9.3 and 9.4 databases used for logging of data. We do not need a strong durability guarantee, meaning it is ok if on crash a minute or two of data is lost from our logs. (This is just stats for our internal tool). I am looking at this page: http://www.postgresql.org/docs/9.4/static/non-durability.html And it's not clear which setting I should turn on. What we do NOT want is to lose the entire table or corrupt the database. We do want to gain speed though by not making DATA writes durable. Which setting is appropriate for this use case? At a glance it looks like a combination of 1) Turn off synchronous_commit and possibly: 2) Increase checkpoint_segments and checkpoint_timeout ; this reduces the frequency of checkpoints, but increases the storage requirements of /pg_xlog. I feel changing above two configuration points are enough for your requirement. 3) Turn off full_page_writes; there is no need to guard against partial page writes. Turning off this may lead to a corrupted database in case if the system crash during the page write until unless your file system supports guard against partial page writes. Regards, Hari Babu Fujitsu Australia -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Question about durability and postgresql.
Hi Alfred, These questions would be better posted to the general list, but I'll take a crack at them here: On 2/20/15 1:18 AM, Alfred Perlstein wrote: We have a combination of 9.3 and 9.4 databases used for logging of data. We do not need a strong durability guarantee, meaning it is ok if on crash a minute or two of data is lost from our logs. (This is just stats for our internal tool). I am looking at this page: http://www.postgresql.org/docs/9.4/static/non-durability.html And it's not clear which setting I should turn on. What we do NOT want is to lose the entire table or corrupt the database. We do want to gain speed though by not making DATA writes durable. Which setting is appropriate for this use case? At a glance it looks like a combination of 1) Turn off synchronous_commit and possibly: This is perfectly safe from a database corruption standpoint. However, you may lose transactions that the client thinks have committed but have not yet been flushed to disk (only if the db/system crashes, though). You can use commit_delay to increase the amount of time before transactions are flushed and perhaps get efficiency by flushing multiple transactions at once. 2) Increase checkpoint_segments and checkpoint_timeout ; this reduces the frequency of checkpoints, but increases the storage requirements of /pg_xlog. These settings are really safe, but affect how long you'll spend in recovery after a database crash. My practice is to increase these settings as much as practical (considering disk space and recovery time) not only because there are fewer fsyncs, but also fewer full page writes for busy pages. The full page is only written once after each checkpoint. 3) Turn off full_page_writes; there is no need to guard against partial page writes. This setting can lead to a corrupt database on a system failure. I'd use the checkpoint settings above to reduce full-page writes instead and see how that works out. -- - David Steele da...@pgmasters.net signature.asc Description: OpenPGP digital signature
[HACKERS] Question about durability and postgresql.
Hello, We have a combination of 9.3 and 9.4 databases used for logging of data. We do not need a strong durability guarantee, meaning it is ok if on crash a minute or two of data is lost from our logs. (This is just stats for our internal tool). I am looking at this page: http://www.postgresql.org/docs/9.4/static/non-durability.html And it's not clear which setting I should turn on. What we do NOT want is to lose the entire table or corrupt the database. We do want to gain speed though by not making DATA writes durable. Which setting is appropriate for this use case? At a glance it looks like a combination of 1) Turn off synchronous_commit and possibly: 2) Increase checkpoint_segments and checkpoint_timeout ; this reduces the frequency of checkpoints, but increases the storage requirements of /pg_xlog. 3) Turn off full_page_writes; there is no need to guard against partial page writes. The point here is to never get a corrupt database, but in case of crash we might lose a few minutes of last transactions. Any suggestions please? thank you, -Alfred
[HACKERS] Question about durability and postgresql.
Hello, We have a combination of 9.3 and 9.4 databases used for logging of data. We do not need a strong durability guarantee, meaning it is ok if on crash a minute or two of data is lost from our logs. (This is just stats for our internal tool). I am looking at this page: http://www.postgresql.org/docs/9.4/static/non-durability.html And it's not clear which setting I should turn on. What we do NOT want is to lose the entire table or corrupt the database. We do want to gain speed though by not making DATA writes durable. Which setting is appropriate for this use case? At a glance it looks like a combination of 1) Turn off synchronous_commit and possibly: 2) Increase checkpoint_segments and checkpoint_timeout ; this reduces the frequency of checkpoints, but increases the storage requirements of /pg_xlog. 3) Turn off full_page_writes; there is no need to guard against partial page writes. The point here is to never get a corrupt database, but in case of crash we might lose a few minutes of last transactions. Any suggestions please? thank you, -Alfred
