Re: Proposal of tunable fix for scalability of 8.4

On 3/19/09 1:49 PM, "Robert Haas" <robertmhaas(at)gmail(dot)com> 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.

The other discussion going on indicates that that condition already can
happen, shared can always currently cut in line while other shared locks
have the lock, though I don't understand all the details.
Also, the tests on the 'wake all' version clearly aren't starving anything
in a load test with thousands of threads and very heavy lock contention,
mostly for shared locks.
Instead throughput increases and all wait times decrease.
There are several other proposals to make starvation less possible (wake
only shared and other proposals that alternate between shared and exclusive;
waking only X sized chunks, etc -- its all just investigation into fixing
what can be improved on -- solutions that are easily testable should not
just be thrown out: the first ones were just the easiest to try).

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

The data shows that in this use case, it is not lightweight enough.
Enhancing or avoiding a few of these larger global locks is necessary to
scale up to larger systems.

The other discussions are a direct result of this and excellent -- I don't
see the separation you are defining.
But If I understand correctly what was said in that other discussion, the
current lock implementation can starve out both exclusive access and some
shared too. If it hasn't happened in this version, its not likely to happen
in the 'wake all' version either, especially since it has been shown to
decrease contention.

Sometimes, the simplest solution is a good one. I can't tell you how many
times I've seen a ton of sophisticated enhancements / proposals to improve
scalability or performance be defeated by the simpler solution that most
engineers thought was not good enough until faced with empirical evidence.

That evidence is what should guide this.

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

There's nothing evil about a pooler, and there is nothing evil about making
Postgres' concurrency overhead a lot lower either.