Обсуждение: Buffer Cache question....

I am using PG 7.2 with fsync truned on but still need a more immediate
write thru.
Does PG uses the fsync (or open_sync, etc) just for the WAL or for
tables as well ?

I understand the consequence of limiting buffer cache but my application
cares more about
integrity than performance. I'd like to be able to write the data to the
physical storage ASAP,
or ideally 100% in sync mode.

I have found that by reducing the checkpoint parameters I can write the
data to disk sooner.
For example

checkpoint_segments = 1
checkpoint_timeout = 30

What else do I have to work with....?

Thanks

Medi Montaseri wrote:
> I am using PG 7.2 with fsync truned on but still need a more immediate
> write thru.
> Does PG uses the fsync (or open_sync, etc) just for the WAL or for
> tables as well ?

Just for WAL.

> I understand the consequence of limiting buffer cache but my application
> cares more about
> integrity than performance. I'd like to be able to write the data to the
> physical storage ASAP,
> or ideally 100% in sync mode.
>
> I have found that by reducing the checkpoint parameters I can write the
> data to disk sooner.
> For example
>
> checkpoint_segments = 1
> checkpoint_timeout = 30
>
> What else do I have to work with....?

PostgreSQL is already 100% reliable (pull plug and see) so I don't see
any value to changing those parameters.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Wed, 30 Apr 2003, Bruce Momjian wrote:

> Medi Montaseri wrote:
> > I am using PG 7.2 with fsync truned on but still need a more immediate
> > write thru.
> > Does PG uses the fsync (or open_sync, etc) just for the WAL or for
> > tables as well ?
>
> Just for WAL.
>
> > I understand the consequence of limiting buffer cache but my application
> > cares more about
> > integrity than performance. I'd like to be able to write the data to the
> > physical storage ASAP,
> > or ideally 100% in sync mode.
> >
> > I have found that by reducing the checkpoint parameters I can write the
> > data to disk sooner.
> > For example
> >
> > checkpoint_segments = 1
> > checkpoint_timeout = 30
> >
> > What else do I have to work with....?
>
> PostgreSQL is already 100% reliable (pull plug and see) so I don't see
> any value to changing those parameters.

What about commit_siblings and commit_delay?  I haven't really played a
lot with those.  I would think increasing the commit delay and the number
of siblings should give better, but slightly bursty performance.

On Wed, Apr 30, 2003 at 03:09:52PM -0600, scott.marlowe wrote:
> What about commit_siblings and commit_delay?  I haven't really played a
> lot with those.  I would think increasing the commit delay and the number
> of siblings should give better, but slightly bursty performance.

This depends very heavily on your actual traffic.  If you have
primarily write activity, it is a definite advantage, because you get
a boost from ganging writes to disk.  If your traffic leans more
toward reads, I find that increasing these settings is a net loss:
you just end up waiting for nothing.

BTW, it is tricky to construct a correct model load to test this,
because you have to model both the load and its distribution.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110