From: Jignesh K. Shah <J.K.Shah@Sun.COM>
To: Simon Riggs <simon@2ndQuadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Subject: Re: Proposal of tunable fix for scalability of 8.4
Date: Tue, 17 Mar 2009 17:41:20 -0400
Message-ID: <49C01900.5030406@sun.com> (raw)
In-Reply-To: <1237225198.3963.60.camel@ebony.2ndQuadrant>
References: <49B824DD.7090302@sun.com>
<49B7F470.EE98.0025.0@wicourts.gov>
<29080.1236821556@sss.pgh.pa.us>
<49B87161.10201@sun.com>
<1237225198.3963.60.camel@ebony.2ndQuadrant>
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
*** lwlock.c Tue Mar 17 12:27:49 2009
--- lwlock.c.orig Wed Mar 11 12:48:27 2009
***************
*** 87,93 ****
static int lock_addin_request = 0;
static bool lock_addin_request_allowed = true;
- int LWLockWakeupAlgorithm;
#ifdef LWLOCK_STATS
static int counts_for_pid = 0;
--- 87,92 ----
***************
*** 564,570 ****
PGPROC *head;
PGPROC *proc;
int i;
- int runq;
PRINT_LWDEBUG("LWLockRelease", lockid, lock);
--- 563,568 ----
***************
*** 612,631 ****
* as many waiters as want shared access.
*/
proc = head;
! if (LWLockWakeupAlgorithm || !proc->lwExclusive)
! {
! if (LWLockWakeupAlgorithm <= 1)
! {
while (proc->lwWaitLink != NULL &&
! (proc->lwExclusive == proc->lwWaitLink->lwExclusive))
proc = proc->lwWaitLink;
- }
- else
- {
- runq= LWLockWakeupAlgorithm;
- while (proc->lwWaitLink != NULL && --runq)
- proc = proc->lwWaitLink;
- }
}
/* proc is now the last PGPROC to be released */
lock->head = proc->lwWaitLink;
--- 610,620 ----
* as many waiters as want shared access.
*/
proc = head;
! if (!proc->lwExclusive)
! {
while (proc->lwWaitLink != NULL &&
! !proc->lwWaitLink->lwExclusive)
proc = proc->lwWaitLink;
}
/* proc is now the last PGPROC to be released */
lock->head = proc->lwWaitLink;
*** lwlock.h.orig Tue Mar 17 14:27:10 2009
--- lwlock.h Tue Mar 17 08:24:40 2009
***************
*** 103,106 ****
--- 103,107 ----
extern void RequestAddinLWLocks(int n);
+ extern int LWLockWakeupAlgorithm;
#endif /* LWLOCK_H */
*** guc.c.orig Tue Mar 17 07:30:26 2009
--- guc.c Tue Mar 17 07:47:10 2009
***************
*** 57,62 ****
--- 57,63 ----
#include "postmaster/walwriter.h"
#include "regex/regex.h"
#include "storage/bufmgr.h"
+ #include "storage/lwlock.h"
#include "storage/fd.h"
#include "tcop/tcopprot.h"
#include "tsearch/ts_cache.h"
***************
*** 167,172 ****
--- 168,174 ----
static bool assign_maxconnections(int newval, bool doit, GucSource source);
static bool assign_autovacuum_max_workers(int newval, bool doit, GucSource source);
static bool assign_effective_io_concurrency(int newval, bool doit, GucSource source);
+ static bool assign_lock_wakeup_algorithm(int newval, bool doit, GucSource source);
static const char *assign_pgstat_temp_directory(const char *newval, bool doit, GucSource source);
static char *config_enum_get_options(struct config_enum *record,
***************
*** 416,421 ****
--- 418,424 ----
static int wal_segment_size;
static bool integer_datetimes;
static int effective_io_concurrency;
+ static int lock_wakeup_algorithm;
/* should be static, but commands/variable.c needs to get at these */
char *role_string;
***************
*** 1727,1732 ****
--- 1730,1745 ----
},
{
+ {"lock_wakeup_algorithm", PGC_USERSET, RESOURCES,
+ gettext_noop("Select algorithm for lock waiters wakeup. 0 is default"),
+ gettext_noop("Select 1 for SMP systems, and higher for manual conrol of number of waiters to wake up.")
+ },
+ &lock_wakeup_algorithm,
+ 0, 0, 32,
+ assign_lock_wakeup_algorithm, NULL
+ },
+
+ {
{"log_rotation_age", PGC_SIGHUP, LOGGING_WHERE,
gettext_noop("Automatic log file rotation will occur after N minutes."),
NULL,
***************
*** 7668,7673 ****
--- 7681,7698 ----
#endif /* USE_PREFETCH */
}
+ static bool
+ assign_lock_wakeup_algorithm(int newval, bool doit, GucSource source)
+ {
+ if ( newval < 0 && newval > 32)
+ return false;
+
+ if (doit)
+ LWLockWakeupAlgorithm= newval ;
+
+ return true;
+ }
+
static const char *
assign_pgstat_temp_directory(const char *newval, bool doit, GucSource source)
{
Attachments:
[text/plain] lwlock.c.patch (1.6K, ../49C01900.5030406@sun.com/3-lwlock.c.patch)
download | inline diff:
*** lwlock.c Tue Mar 17 12:27:49 2009
--- lwlock.c.orig Wed Mar 11 12:48:27 2009
***************
*** 87,93 ****
static int lock_addin_request = 0;
static bool lock_addin_request_allowed = true;
- int LWLockWakeupAlgorithm;
#ifdef LWLOCK_STATS
static int counts_for_pid = 0;
--- 87,92 ----
***************
*** 564,570 ****
PGPROC *head;
PGPROC *proc;
int i;
- int runq;
PRINT_LWDEBUG("LWLockRelease", lockid, lock);
--- 563,568 ----
***************
*** 612,631 ****
* as many waiters as want shared access.
*/
proc = head;
! if (LWLockWakeupAlgorithm || !proc->lwExclusive)
! {
! if (LWLockWakeupAlgorithm <= 1)
! {
while (proc->lwWaitLink != NULL &&
! (proc->lwExclusive == proc->lwWaitLink->lwExclusive))
proc = proc->lwWaitLink;
- }- else - { - runq= LWLockWakeupAlgorithm;- while (proc->lwWaitLink != NULL && --runq)- proc = proc->lwWaitLink;- }
}
/* proc is now the last PGPROC to be released */
lock->head = proc->lwWaitLink;
--- 610,620 ----
* as many waiters as want shared access.
*/
proc = head;
! if (!proc->lwExclusive)
! {
while (proc->lwWaitLink != NULL &&
! !proc->lwWaitLink->lwExclusive)
proc = proc->lwWaitLink;
}
/* proc is now the last PGPROC to be released */
lock->head = proc->lwWaitLink;
[text/plain] lwlock.h.patch (241B, ../49C01900.5030406@sun.com/4-lwlock.h.patch)
download | inline diff:
*** lwlock.h.orig Tue Mar 17 14:27:10 2009
--- lwlock.h Tue Mar 17 08:24:40 2009
***************
*** 103,106 ****
--- 103,107 ----
extern void RequestAddinLWLocks(int n);
+ extern int LWLockWakeupAlgorithm;
#endif /* LWLOCK_H */
[text/plain] guc.c.patch (2.1K, ../49C01900.5030406@sun.com/5-guc.c.patch)
download | inline diff:
*** guc.c.orig Tue Mar 17 07:30:26 2009
--- guc.c Tue Mar 17 07:47:10 2009
***************
*** 57,62 ****
--- 57,63 ----
#include "postmaster/walwriter.h"
#include "regex/regex.h"
#include "storage/bufmgr.h"
+ #include "storage/lwlock.h"
#include "storage/fd.h"
#include "tcop/tcopprot.h"
#include "tsearch/ts_cache.h"
***************
*** 167,172 ****
--- 168,174 ----
static bool assign_maxconnections(int newval, bool doit, GucSource source);
static bool assign_autovacuum_max_workers(int newval, bool doit, GucSource source);
static bool assign_effective_io_concurrency(int newval, bool doit, GucSource source);
+ static bool assign_lock_wakeup_algorithm(int newval, bool doit, GucSource source);
static const char *assign_pgstat_temp_directory(const char *newval, bool doit, GucSource source);
static char *config_enum_get_options(struct config_enum *record,
***************
*** 416,421 ****
--- 418,424 ----
static int wal_segment_size;
static bool integer_datetimes;
static int effective_io_concurrency;
+ static int lock_wakeup_algorithm;
/* should be static, but commands/variable.c needs to get at these */
char *role_string;
***************
*** 1727,1732 ****
--- 1730,1745 ----
},
{
+ {"lock_wakeup_algorithm", PGC_USERSET, RESOURCES,+ gettext_noop("Select algorithm for lock waiters wakeup. 0 is default"),+ gettext_noop("Select 1 for SMP systems, and higher for manual conrol of number of waiters to wake up.")+ },+ &lock_wakeup_algorithm,+ 0, 0, 32,+ assign_lock_wakeup_algorithm, NULL+ },+ + {
{"log_rotation_age", PGC_SIGHUP, LOGGING_WHERE,
gettext_noop("Automatic log file rotation will occur after N minutes."),
NULL,
***************
*** 7668,7673 ****
--- 7681,7698 ----
#endif /* USE_PREFETCH */
}
+ static bool+ assign_lock_wakeup_algorithm(int newval, bool doit, GucSource source)+ {+ if ( newval < 0 && newval > 32)+ return false;+ + if (doit)+ LWLockWakeupAlgorithm= newval ;+ + return true;+ }+
static const char *
assign_pgstat_temp_directory(const char *newval, bool doit, GucSource source)
{
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: J.K.Shah@Sun.COM, simon@2ndQuadrant.com, tgl@sss.pgh.pa.us, Kevin.Grittner@wicourts.gov
Subject: Re: Proposal of tunable fix for scalability of 8.4
In-Reply-To: <49C01900.5030406@sun.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox