Thread: tuning postgresql writes to disk
Hi, I was wondering if Postgresql can be tuned such that it writes to disk every n seconds or until the buffer reaches a certain threshold to tune the performance . This is a special case where the application wouldn't care if there is a data loss of 1 seconds or less. I would be interested to know if there is some configuration parameter which already does this. I looked at the WAL parameters and the new async commit but not sure if I am looking at the right place. Say i have 10 clients connecting and each client is inserting a record. I want to force Postgresql only to write to disk when all the 10 records have been written to the buffer or after 1 second (configurable). My question is can I partly achieve this by setting wal_writer_delay = 1000 ms or is there some other parameter which will help me to achieve the desired effect? -- Vinu In a world without fences who needs Gates?
On Thu, Feb 3, 2011 at 7:08 PM, Vinubalaji Gopal <vinubalaji@gmail.com> wrote: > already does this. I looked at the WAL parameters and the new async > commit but not sure if I am looking at the right place. Say i have 10 > clients connecting and each client is inserting a record. I want to > You want the async commit. If you can detect and re-execute "lost" transactions, it gives you the best of everything: defer disk I/O and transaction boundaries are honored so you never have inconsistent data after crash recovery.
Thank you. I will try to run some performance tests using the async commit option. Is there an easy way to find the lost transactions or does it have to be handled by the application? On Mon, Feb 7, 2011 at 6:23 AM, Vick Khera <vivek@khera.org> wrote: > On Thu, Feb 3, 2011 at 7:08 PM, Vinubalaji Gopal <vinubalaji@gmail.com> wrote: >> already does this. I looked at the WAL parameters and the new async >> commit but not sure if I am looking at the right place. Say i have 10 >> clients connecting and each client is inserting a record. I want to >> > > You want the async commit. If you can detect and re-execute "lost" > transactions, it gives you the best of everything: defer disk I/O and > transaction boundaries are honored so you never have inconsistent data > after crash recovery. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Vinu In a world without fences who needs Gates?
On Mon, Feb 7, 2011 at 7:43 PM, Vinubalaji Gopal <vinubalaji@gmail.com> wrote:
Thank you. I will try to run some performance tests using the async
commit option. Is there an easy way to find the lost transactions or
does it have to be handled by the application?
By definition, your application has to be able to detect it. If the DB were able find them, they wouldn't be lost.
Personally, I turn off synchronous commit on a per-connection basis based on what my application is doing. When I insert or update data, I need to ensure it is there; when I move data around or do batch processing on it, it is usually a restartable operation.