Hackers:

I've been trying to get a test result for 8.1 that shows that we can eliminate
commit_delay and commit_siblings, as I believe that these settings no longer
have any real effect on performance. However, the checkpointing performance
issues have so far prevented me from getting a good test result for this.

Just a warning, because I might bring it up after feature freeze.

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Josh Berkus <josh@agliodbs.com> writes:
> I've been trying to get a test result for 8.1 that shows that we can eliminate
> commit_delay and commit_siblings, as I believe that these settings no longer
> have any real effect on performance.

I don't think they ever did :-(. The theory is good, but useful values
for commit_delay would probably be under a millisecond, and there isn't
any portable way to sleep for such short periods. We've been leaving
them there just in case somebody can find a use for 'em, but I wouldn't
object to taking them out.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
>
>>I've been trying to get a test result for 8.1 that shows that we can eliminate
>>commit_delay and commit_siblings, as I believe that these settings no longer
>>have any real effect on performance.
>
>
> I don't think they ever did :-(. The theory is good, but useful values
> for commit_delay would probably be under a millisecond, and there isn't
> any portable way to sleep for such short periods. We've been leaving
> them there just in case somebody can find a use for 'em, but I wouldn't
> object to taking them out.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


We have done extensive testing some time ago.
We could not see any difference on any platform we have tested (AIX,
Linux, Solaris). I don't think that there is one at all - at least not
on common systems.

best regards,

hans

--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/664/393 39 74
www.cybertec.at, www.postgresql.at


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Hans, Tom,

> We have done extensive testing some time ago.
> We could not see any difference on any platform we have tested (AIX,
> Linux, Solaris). I don't think that there is one at all - at least not
> on common systems.

Keen then. Any objections to removing the GUC? We desperately need means
to cut down on GUC options.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Hans-Jürgen Schönig <postgres@cybertec.at> writes:

> > The theory is good, but useful values for commit_delay would probably be
> > under a millisecond, and there isn't any portable way to sleep for such
> > short periods.

Just because there's no "portable" way to be sure it'll work doesn't mean
there's no point in trying. If one user sets it to 5ms and it's effective for
him there's no reason to take out the option for him just because it doesn't
work out as well on all platforms.

Linux, for example has moved to higher clock speeds precisely because things
like movie and music players need to be able to control their timing to much
more precision than 10ms.

--
greg


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

"Josh Berkus" <josh@agliodbs.com> writes
> Hackers:
>
> I've been trying to get a test result for 8.1 that shows that we can
eliminate
> commit_delay and commit_siblings, as I believe that these settings no
longer
> have any real effect on performance. However, the checkpointing
performance
> issues have so far prevented me from getting a good test result for this.
>

In my understadning, the commit_delay/commit_siblings combination simulate
the background xlog writer mechanisms in some database like Oracle.

This might be separate issue. We have code in xlogflush() like:

/* done already? */
if (!XLByteLE(record, LogwrtResult.Flush))
{
/* now wait for the write lock */
LWLockAcquire(WALWriteLock, LW_EXCLUSIVE);
if (XLByteLE(record, LogwrtResult.Flush))
LWLockRelease(WALWriteLock); /* if done already, then release the
lock */
else
/* do it */

If the testing results turns out the "LWLockRelease(WALWriteLock)" actually
happened often, then it indicates that we waste some time on acquiring
WALWriteLock. Would commit_delay/commit_siblings helps or we need a
background xlog writer and notify us the completion of xlogflush is better
(so we don't compete for this lock)?

Regards,
Qingqing

"Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:
> Would commit_delay/commit_siblings helps or we need a
> background xlog writer and notify us the completion of xlogflush is better
> (so we don't compete for this lock)?

The existing bgwriter already does a certain amount of xlog flushing
(since it must flush WAL at least as far as the LSN of any dirty page it
wants to write out). However I'm not sure that this is very effective
--- in a few strace tests that I've done, it seemed that committing
backends still ended up doing the bulk of the xlog writes, especially
if they were doing small transactions. It'd be interesting to look into
making the bgwriter (or a new dedicated xlog bgwriter) responsible for
all xlog writes. You could imagine a loop like

forever do
if (something new in xlog)
write and flush it;
else
sleep 10 msec;
done

together with some kind of IPC to waken backends once xlog was flushed
past the point they needed. (Designing that is the hard part.)

But in any case, the existing commit_delay doesn't seem like it's got
anything to do with a path to a better answer, so this is not an
argument against removing it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

"Tom Lane" <tgl@sss.pgh.pa.us> writes
>
>
> together with some kind of IPC to waken backends once xlog was flushed
> past the point they needed. (Designing that is the hard part.)
>

I think we could use ProcSendSignal()/ProcWaitForSignal() mechanism to cope
with the problem, because they won't lost any wake-ups.

So there will be a MaxBackend sized shared memory arrary with each cell is a

XLogRecPtr recptr; /* record request */
bool status; /* execution results */

structure. The initial value of the cell is <(0, 0), *doesn't matter*>.
Also, we need a spinlock to protect "recptr" value since it is not a
sig_atomic_t value.

A backend requests a xlogflush will do:
spinlock_acquire;
fill in the XLogRecPtr value;
spinlock_release;
ProcWaitForSignal();
After waken up, it will examine the "status" value and acts accordingly.


The xlog-writer is the only one who does real xlog write in postmaster mode.
It does not work in standalone mode or recovery mode. It works based on a
periodical loop + waken up when the xlog buffer is 70% full. A cancel/die
interrupts could happen during wait, so we will plug in a
ProcCancelWaitForSignal() at AbortTransaction() or error handling in
xlog-writer loop. There also could be various error conditions in its life.
Any error happened during xlogflush will be PANIC. Some small errors in the
loop will be hopefully recoverable. If everything is good, it would scan the
arrary, for each cell do:

spinlock_acquire;
make a local copy of XLogRecPtr;
spinlock_release;

if (recptr is (0, 0))
nothing to do; /* no request at all */

if (recptr is satisfied)
set XLogRecPtr to (0, 0);
status = true; /* successfully done */
ProcSendSignal(targetbackendid);
else
check if the recptr is passed the end of xlog file, if so
set XLogRecPtr to (0, 0);
set status = false; /* bad request */
ProcSendSignal(targetbackendid);

I am not sure how to check bad recptr. Currently we could do this by
comparing request and real flush point after xlogwrite(request). However,
seems this is not a solution for the xlog writer case.

Regards,
Qingqing

Josh Berkus wrote:
> Hackers:
>
> I've been trying to get a test result for 8.1 that shows that we can eliminate
> commit_delay and commit_siblings, as I believe that these settings no longer
> have any real effect on performance. However, the checkpointing performance
> issues have so far prevented me from getting a good test result for this.
>
> Just a warning, because I might bring it up after feature freeze.

If we yank them ( and I agree) I think we have to do it before feature
freeze.

--
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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Bruce,

> > Just a warning, because I might bring it up after feature freeze.
>
> If we yank them ( and I agree) I think we have to do it before feature
> freeze.

I believe that we have consensus to yank them. Hans says that he did
extensive testing back as far as 7.4 and the options had no effect.

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match