Re: Proposal of tunable fix for scalability of 8.4

Robert Haas wrote:
>> Actually the patch I submitted shows no overhead from what I have seen and I
>> think it is useful depending on workloads where it can be turned on even on
>> production.
>>
>
> Well, unless I'm misunderstanding something, waking all waiters every
> time could lead to arbitrarily long delays for writers on mostly
> read-only workloads... and by arbitrarily along, we mean to say
> "potentially just about forever". That doesn't sound safe for
> production to me.
>
>

Hi Robert,
The patch I submmitted does not do any manipulation with the list. All
it changes is gives the flexibility to change how many to wake up at one
go. 0 is default which wakes up only 1 X (Exclusive) at a time or all
sequential S (Shared). Changing the value to 1 will wake up all
sequential X or all sequential S as they are in the queue (no
manipulation). Values 2 and higher upto 32 wakes up the next n waiter in
the queue (X or S) AS they are in the queue. It absolutely does no
manipulation and hence there is no overhead. Absolutely safe for
Production as Scott mentioned there are other things in postgresql.conf
which can be more dangerous than this tunable.

>> I dont think anything is majorly wrong in my system.. Sometimes it is
>> PostgreSQL locks in play and sometimes it can be OS/system related locks in
>> play (network, IO, file system, etc). Right now in my patch after I fix
>> waiting procarray problem other PostgreSQL locks comes into play:
>> CLogControlLock, WALInsertLock , etc. Right now out of the box we have no
>> means of tweaking something in production if you do land in that problem.
>> With the patch there is means of doing knob control to tweak the bottlenecks
>> of Locks for the main workload for which it is put in production.
>>
>
> I'll reiterate my previous objection: I think your approach is too
> simplistic. I think Tom said it the best: a lot of work has gone into
> making the locking mechanism lightweight and safe. I'm pretty
> doubtful that you're going to find a change that is still safe, but
> performs much better. The discussions by Heikki, Simon, and others
> about changing the way locks are used or inventing new kinds of locks
> seem much more promising to me.
>
>
That is the beauty : The approach is simplistic but very effective. Lot
of work has gone which is more incremental and this is another one of
those incremental changes which allows minor tweaks which the workload
may like very much and perform very well.. Performance tuning game is
almost like harmonic frequency. I agree that other kinds of locks seem
more promising. I had infact proposed one last year too:
http://archives.postgresql.org//pgsql-hackers/2008-06/msg00291.php

Seriously speaking a change will definitely cannot be done before 8.5
time frame while this one is simple enough to go for 8.4.
The best thing one can contribute to the thread is to actually try the
patch on the test system and run your own tests to see how it behaves.

-Jignesh

>> Right now.. the standard answer applies.. nope you are running the wrong
>> workload for PostgreSQL, use a connection pooler or your own application
>> logic. Or maybe.. you have too many users for PostgreSQL use some
>> proprietary database.
>>
>
> Well I certainly agree that we need to get away from that mentality,
> although there's nothing particularly evil about a connection
> pooler... it might not be suitable for every workload, but you haven't
> specified why one couldn't or shouldn't be used in the situation
> you're trying to simulate here.
>
> ...Robert
>