2014-02-25 11:29 GMT+01:00 Pavel Stehule <pavel.steh...@gmail.com>:

> Hello
>
>
> 2014-02-24 21:31 GMT+01:00 Jeff Janes <jeff.ja...@gmail.com>:
>
> On Mon, Feb 24, 2014 at 7:02 AM, Pavel Stehule <pavel.steh...@gmail.com>wrote:
>>
>>>
>>>
>>>
>>> 2014-02-23 21:32 GMT+01:00 Andres Freund <and...@2ndquadrant.com>:
>>>
>>> Hi,
>>>>
>>>> On 2014-02-23 20:04:39 +0100, Pavel Stehule wrote:
>>>> > There is relative few very long ProcArrayLocks lwlocks
>>>> >
>>>> > This issue is very pathologic on fast computers with more than 8 CPU.
>>>> This
>>>> > issue was detected after migration from 8.4 to 9.2. (but tested with
>>>> same
>>>> > result on 9.0)  I see it on devel 9.4 today actualized.
>>>> >
>>>> > When I moved PREPARE from cycle, then described issues is gone. But
>>>> when I
>>>> > use a EXECUTE IMMEDIATELY, then the issue is back. So it looks it is
>>>> > related to planner, ...
>>>>
>>>> In addition to the issue Jeff mentioned, I'd suggest trying the same
>>>> workload with repeatable read. That can do *wonders* because of the
>>>> reduced number of snapshots.
>>>>
>>>>
>>> I tested it, and it doesn't help.
>>>
>>> Is there some patch, that I can test related to this issue?
>>>
>>
>> This is the one that I was referring to:
>>
>> http://www.postgresql.org/message-id/11927.1384199...@sss.pgh.pa.us
>>
>
> I tested this patch with  great success. Waiting on ProcArrayLocks are
> off. Throughput is expected.
>
> For described use case it is seriously interesting.
>

Here is a update for 9.4

Regards

Pavel


>
> Regards
>
> Pavel
>
>
> light weight locks
>             lockname       mode      count        avg
>         DynamicLocks  Exclusive       8055       5003
>         DynamicLocks     Shared       1666         50
>         LockMgrLocks  Exclusive        129         36
>      IndividualLock   Exclusive         34         48
>      IndividualLock      Shared         21          7
>      BufFreelistLock  Exclusive         12          8
>         WALWriteLock  Exclusive          1      38194
>        ProcArrayLock     Shared          1          8
>
>
>
>> Cheers,
>>
>> Jeff
>>
>>
>>
>
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
new file mode 100644
index d525ca4..261473d
*** a/src/backend/utils/adt/selfuncs.c
--- b/src/backend/utils/adt/selfuncs.c
*************** get_actual_variable_range(PlannerInfo *r
*** 4958,4963 ****
--- 4958,4964 ----
  			HeapTuple	tup;
  			Datum		values[INDEX_MAX_KEYS];
  			bool		isnull[INDEX_MAX_KEYS];
+ 			SnapshotData	SnapshotDirty;
  
  			estate = CreateExecutorState();
  			econtext = GetPerTupleExprContext(estate);
*************** get_actual_variable_range(PlannerInfo *r
*** 4980,4985 ****
--- 4981,4987 ----
  			slot = MakeSingleTupleTableSlot(RelationGetDescr(heapRel));
  			econtext->ecxt_scantuple = slot;
  			get_typlenbyval(vardata->atttype, &typLen, &typByVal);
+ 			InitDirtySnapshot(SnapshotDirty);
  
  			/* set up an IS NOT NULL scan key so that we ignore nulls */
  			ScanKeyEntryInitialize(&scankeys[0],
*************** get_actual_variable_range(PlannerInfo *r
*** 4997,5003 ****
  			if (min)
  			{
  				index_scan = index_beginscan(heapRel, indexRel,
! 											 GetActiveSnapshot(), 1, 0);
  				index_rescan(index_scan, scankeys, 1, NULL, 0);
  
  				/* Fetch first tuple in sortop's direction */
--- 4999,5005 ----
  			if (min)
  			{
  				index_scan = index_beginscan(heapRel, indexRel,
! 											 &SnapshotDirty, 1, 0);
  				index_rescan(index_scan, scankeys, 1, NULL, 0);
  
  				/* Fetch first tuple in sortop's direction */
*************** get_actual_variable_range(PlannerInfo *r
*** 5029,5035 ****
  			if (max && have_data)
  			{
  				index_scan = index_beginscan(heapRel, indexRel,
! 											 GetActiveSnapshot(), 1, 0);
  				index_rescan(index_scan, scankeys, 1, NULL, 0);
  
  				/* Fetch first tuple in reverse direction */
--- 5031,5037 ----
  			if (max && have_data)
  			{
  				index_scan = index_beginscan(heapRel, indexRel,
! 											 &SnapshotDirty, 1, 0);
  				index_rescan(index_scan, scankeys, 1, NULL, 0);
  
  				/* Fetch first tuple in reverse direction */
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to