Re: [PERFORM] DELETE vs TRUNCATE explanation

On Mon, Jul 16, 2012 at 12:36 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Mon, Jul 16, 2012 at 12:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> 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.
>
> Well, that argument is exactly why the code is designed the way it is...
> but we are now finding out that sending useless fsync requests isn't as
> cheap as all that.

I agree, but I think the problem can be solved for a pretty modest
amount of effort without needing to make fsync PGC_POSTMASTER. Your
proposal to refactor the pendingOpsTable representation seems like it
will help a lot. Perhaps you should do that first and then we can
reassess.

> The larger point here, in any case, is that I don't believe anyone wants
> to expend a good deal of skull sweat and possibly performance on
> ensuring that transitioning from fsync off to fsync on in an active
> database is a reliable operation. It does not seem like something we
> are ever going to recommend, and we have surely got nine hundred ninety
> nine other things that are more useful to spend development time on.

We may not recommend it, but I am sure that people will do it anyway,
and requiring them to bounce the server in that situation seems
unfortunate, especially since it will also require them to bounce the
server in order to go the other direction.

In my view, the elephant in the room here is that it's dramatically
inefficient for every backend to send an fsync request on every block
write. For many users, in many workloads, all of those requests will
be for just a tiny handful of relation segments. The fsync queue
compaction code works as well as it does for precisely that reason -
when it triggers, we typically can compact a list of thousands or
millions of entries down to less than two dozen. In other words, as I
see it, the issue here is not so much that 100% of the fsync requests
are useless when fsync=off, but rather that 99.9% of them are useless
even when fsync=on.

In any case, I'm still of the opinion that we ought to try making one
fix (your proposed refactoring of the pendingOpsTable) and then see
where we're at.

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