Re: Proposal of tunable fix for scalability of 8.4

From: "Jignesh K(dot) Shah" <J(dot)K(dot)Shah(at)Sun(dot)COM>
To: Simon Riggs <simon(at)2ndQuadrant(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Proposal of tunable fix for scalability of 8.4
Date: 2009-03-17 21:41:20
Message-ID: 49C01900.5030406@sun.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On 03/16/09 13:39, Simon Riggs wrote:
> On Wed, 2009-03-11 at 22:20 -0400, Jignesh K. Shah wrote:
>
>> A tunable does not impact existing behavior
>
> Why not put the tunable parameter into the patch and then show the test
> results with it in? If there is no overhead, we should then be able to
> see that.
>

I did a patch where I define lock_wakeup_algorithm with default value of
0, and range is 0 to 32
It basically handles three types of algorithms and 32 different
permutations, such that:
When lock_wakeup_algorithm is set to
0 => default logic of wakeup (only 1 exclusive or all
sequential shared)
1 => wake up all sequential exclusives or all sequential
shared
32>= n >=2 => wake up first n waiters irrespective of exclusive or
sequential

I did a quick test with patch. Unfortunately it improves my number even
with default setting 0 (not sure whether I should be pleased or sad -
Definitely no overhead infact seems to help performance a bit. NOTE:
Logic is same, implementation is slightly different for default set)

my Prepatch numbers typically peaked around 136,000 tpm
With the patch and settings:

lock_wakeup_algorithm=0
PEAK: 962: 512: Medium Throughput: 161121.000 Avg Medium Resp: 0.051

When lock_wakeup_algorithm=1
Then my PEAK increases to
PEAK 1560: 832: Medium Throughput: 176577.000 Avg Medium Resp: 0.086
(Couldn't recreate the 184K+ result.. need to check that)

I still havent tested for the rest 2-32 values but you get the point,
the patch is quite flexible with various types of permutations and no
overhead.

Do give it a try on your own setup and play with values and compare it
with your original builds.

Regards,
Jignesh

Attachment Content-Type Size
lwlock.c.patch text/plain 1.6 KB
lwlock.h.patch text/plain 242 bytes
guc.c.patch text/plain 2.1 KB

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Marinos Yannikos 2009-03-17 21:57:44 parallelizing slow queries for multiple cores (PostgreSQL + Gearman)
Previous Message Ron Mayer 2009-03-17 18:28:35 Re: Extremely slow intarray index creation and inserts.