Re: [PERFORM] DELETE vs TRUNCATE explanation

On Mon, Jul 16, 2012 at 12:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Sun, Jul 15, 2012 at 2:29 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Yeah, you have a point there. It's not real clear that switching fsync
>>> from off to on is an operation that we can make any guarantees about,
>>> short of executing something like the code recently added to initdb
>>> to force-sync the entire PGDATA tree. Perhaps we should change fsync
>>> to be PGC_POSTMASTER (ie frozen at postmaster start), and then we could
>>> skip forwarding fsync requests when it's off?
>
>> I would argue that such a change adds no measure of safety, anyway.
>
> Well, yes it does, and the reason was explained further down in the
> thread: since we have no particular guarantees as to how quickly
> postmaster children will absorb postgresql.conf updates, there could be
> individual processes still running with fsync = off long after the user
> thinks he's turned it on. A forced restart solves that. I believe the
> reason for the current coding in the fsync queuing stuff is so that you
> only have to worry about how long it takes the checkpointer to notice
> the GUC change, and not any random backend that's running a forty-hour
> query.

Hrmf, I guess that's a fair point. But if we believe that reasoning
then I think it's an argument for sending fsync requests even when
fsync=off, not for making fsync PGC_POSTMASTER. Or maybe we could
store the current value of the fsync flag in shared memory somewhere
and have backends check it before deciding whether to enqueue a
request. With proper use of memory barriers it should be possible to
make this work without requiring a lock.

>> But, at a broader level, I am not very excited about this
>> optimization. It seems to me that if this is hurting enough to be
>> noticeable, then it's hurting us when fsync=on as well, and we had
>> maybe think a little harder about how to cut down on the IPC overhead.
>
> Uh, that's exactly what's under discussion. Not sending useless fsync
> requests when fsync is off is just one part of it; a part that happens
> to be quite useful for some test scenarios, even if not so much for
> production. (IIRC, the original complainant in this thread was running
> fsync off.)

My point is that if sending fsync requests is cheap enough, then not
sending them won't save anything meaningful. And I don't see why it
can't be made just that cheap, thereby benefiting people with fsync=on
as well.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company