Re: Linux/PostgreSQL scalability issue - problem with 8 cores

On Mon, 2008-01-07 at 19:54 -0500, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Perhaps it would make sense to try to take the "fast path" in
> > SIDelExpiredDataEntries with only a shared lock rather than exclusive.
>
> I think the real problem here is that sinval catchup processing is well
> designed to create contention :-(.

Thinking some more about handling TRUNCATEs...

> Some ideas for improving matters:
>
> 1. Try to avoid having all the backends hit the queue at once. Instead
> of SIGUSR1'ing everybody at the same time, maybe hit only the process
> with the oldest message pointer, and have him hit the next oldest after
> he's done reading the queue.
>
> 2. Try to take more than one message off the queue per SInvalLock cycle.
> (There is a tuning tradeoff here, since it would mean holding the lock
> for longer at a time.)
>
> 3. Try to avoid having every backend run SIDelExpiredDataEntries every
> time through ReceiveSharedInvalidMessages. It's not critical to delete
> entries until the queue starts getting full --- maybe we could rejigger
> the logic so it only happens once when somebody notices the queue is
> getting full, or so that only the guy(s) who had nextMsgNum == minMsgNum
> do it, or something like that?

(2) is unnecessary if we can reduce the number of Exclusive lockers so
that repeated access to the backend's messages is not contended.

(1) would do this, but seems like it would be complex. We can reduce the
possibility of multiple re-signals though.

(3) seems like the easiest route, as long as we get a reasonable
algorithm for reducing the access rate to a reasonable level.

I'm posting a patch for discussion to -patches now that will do this. It
seems straightforward enough to include in 8.3, but that may rise a few
eyebrows, but read the patch first.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com