Wait time for Parallel Synch?

Tom Glaser

Wait time for Parallel Synch?

Hi,

We are encountering threads with a wait time for Parallel Synch.  Does anyone know what this means?  Any idea how to resolve it?  Below are statistics from Mainview.  Thank you in advance for any ideas.

Tom

WAITS IN DB2 (LOCAL)                                  |                      |
LOCK 0 0 us 0 us 0.00 | |
LATCH 2 6 us 12 us 0.00 | |
I/O WAIT 0 0 us 0 us 0.00 | |
LOG WRITE I/O 0 0 us 0 us 0.00 | |
OTHER READ 0 0 us 0 us 0.00 | |
OTHER WRITE 0 0 us 0 us 0.00 | |
TCP/IP LOB 0 0 us 0 us 0.00 | |
ACCELERATOR 0 0 us 0 us 0.00 | |
PARALLEL SYNCH 295 438 us 129 ms 28.08 | ***** |
UNIT SWITCH EVENTS | |
..COMMIT/ROLLBK 0 0 us 0 us 0.00 | |
..OPEN/CLOSE 0 0 us 0 us 0.00 | |
..SYSLGRNG 0 0 us 0 us 0.00 | |
..DATASPACE MGR 0 0 us 0 us 0.00 | |
..AUTONOMS PRC 0 0 us 0 us 0.00 | |
..OTHER 4 189 us 754 us 0.16 | < |
ARCH. LOG(QIS) 0 0 us 0 us 0.00 | |
ARCH.READ(TAPE) 0 0 us 0 us 0.00 | |
DRAIN LOCK 0 0 us 0 us 0.00 | |
CLAIM RELEASE 0 0 us 0 us 0.00 | |
PAGELATCH CONT. 0 0 us 0 us 0.00 | |
SPAS SERVER TCB 0 0 us 0 us 0.00 | |
FORCE-AT-COMMIT 0 0 us 0 us 0.00 | |
WAITS IN DB2 (GLOBAL) | |
LOCKS (GLOBAL) | |
.
.
.
- - - - - - - - - - - - - - - - - PARALLELISM - - - - - - - - - - - - - - - - -
TOTAL AVERAGE TOTAL
MAX DEGREE (MAX= 3)..........3 0.00
MAX EST DEG (MAX= 3)........3 0.00 FALLBACK - RLF LIMITED...........0
MX PLN DEG (MAX= 3)........3 0.00 FALLBACK - NO ESA SORT...........0
GROUPS EXECUTED................3 0.00 FALLBACK - UPDATE CURSOR.........0
PLANNED DEGREE................3 0.00 FALLBACK - AUTONOMOUS PRC........0
REDUCED DEG: NO BUFFER........0 0.00 FALLBACK - NO BUFFER.............0
REDUCED DEG: STRESS...........0 0.00 FALLBACK - SYSTEM STRESS.........0
PARALLEL TASKS.................0 0.00 FALLBACK - NO ENCLAVE............0


Bill Gallagher

Wait time for Parallel Synch?
(in response to Tom Glaser)
Found this via Google:

Parallel Sync Time The accumulated wait time for parallel queries to synchronize parent and child tasks (Field name: QWACPQSW).

https://www.ibm.com/support/knowledgecenter/en/SSUSPS_5.3.0/com.ibm.omegamon.xe.pe_db2.doc_5.3.0/ko2xe/attr_thrdwat2.htm

Bill Gallagher
DB2 Database Administrator
State of Connecticut


From: Tom Glaser [mailto:[login to unmask email]
Sent: Wednesday, April 4, 2018 1:49 PM
To: [login to unmask email]
Subject: [DB2-L] - Wait time for Parallel Synch?


Hi,

We are encountering threads with a wait time for Parallel Synch. Does anyone know what this means? Any idea how to resolve it? Below are statistics from Mainview. Thank you in advance for any ideas.

Tom

WAITS IN DB2 (LOCAL) | |
LOCK 0 0 us 0 us 0.00 | |
LATCH 2 6 us 12 us 0.00 | |
I/O WAIT 0 0 us 0 us 0.00 | |
LOG WRITE I/O 0 0 us 0 us 0.00 | |
OTHER READ 0 0 us 0 us 0.00 | |
OTHER WRITE 0 0 us 0 us 0.00 | |
TCP/IP LOB 0 0 us 0 us 0.00 | |
ACCELERATOR 0 0 us 0 us 0.00 | |
PARALLEL SYNCH 295 438 us 129 ms 28.08 | ***** |
UNIT SWITCH EVENTS | |
..COMMIT/ROLLBK 0 0 us 0 us 0.00 | |
..OPEN/CLOSE 0 0 us 0 us 0.00 | |
..SYSLGRNG 0 0 us 0 us 0.00 | |
..DATASPACE MGR 0 0 us 0 us 0.00 | |
..AUTONOMS PRC 0 0 us 0 us 0.00 | |
..OTHER 4 189 us 754 us 0.16 | < |
ARCH. LOG(QIS) 0 0 us 0 us 0.00 | |
ARCH.READ(TAPE) 0 0 us 0 us 0.00 | |
DRAIN LOCK 0 0 us 0 us 0.00 | |
CLAIM RELEASE 0 0 us 0 us 0.00 | |
PAGELATCH CONT. 0 0 us 0 us 0.00 | |
SPAS SERVER TCB 0 0 us 0 us 0.00 | |
FORCE-AT-COMMIT 0 0 us 0 us 0.00 | |
WAITS IN DB2 (GLOBAL) | |
LOCKS (GLOBAL) | |
.
.
.
- - - - - - - - - - - - - - - - - PARALLELISM - - - - - - - - - - - - - - - - -
TOTAL AVERAGE TOTAL
MAX DEGREE (MAX= 3)..........3 0.00
MAX EST DEG (MAX= 3)........3 0.00 FALLBACK - RLF LIMITED...........0
MX PLN DEG (MAX= 3)........3 0.00 FALLBACK - NO ESA SORT...........0
GROUPS EXECUTED................3 0.00 FALLBACK - UPDATE CURSOR.........0
PLANNED DEGREE................3 0.00 FALLBACK - AUTONOMOUS PRC........0
REDUCED DEG: NO BUFFER........0 0.00 FALLBACK - NO BUFFER.............0
REDUCED DEG: STRESS...........0 0.00 FALLBACK - SYSTEM STRESS.........0
PARALLEL TASKS.................0 0.00 FALLBACK - NO ENCLAVE............0


-----End Original Message-----

Andy Smith

RE: Wait time for Parallel Synch?
(in response to Tom Glaser)

Tom

Do you want/need parallelism for this workload?

Could you easily test REBINDing without parallelism and compare performance?

One possible theory here might be repeated execution of short-running queries (using parallelism).

If this were the case, then you might consider increasing ZPARM SPRMPTH (in DSN6SPRC macro).

The default for this parameter is 120 msecs.

If a query has a cost estimate less than this, then it is not considered for parallelism.

Please report back on here, I'm curious. 

Cheers
Andy

Michael Hannan

RE: Wait time for Parallel Synch?
(in response to Andy Smith)

In Reply to Andy Smith:

Tom

Do you want/need parallelism for this workload?

Could you easily test REBINDing without parallelism and compare performance?

One possible theory here might be repeated execution of short-running queries (using parallelism).

If this were the case, then you might consider increasing ZPARM SPRMPTH (in DSN6SPRC macro).

The default for this parameter is 120 msecs.

If a query has a cost estimate less than this, then it is not considered for parallelism.

Some ideas from me:
Firstly the Optimizer cost estimates can be well off for a B (Bad) type estimate due to range predicates or even for an A (Average) type estimate due to skewed data distributions, and not able to use Freq Value stats to overcome it, also due to subquery filtering not known (typically estimated 50%), and relative similar or disparate sequencing of joined tables not well understood.

When Parallelism is chosen, the amount of work to do in the parallel pipes could vary a lot, one pipe finding qualifying rows very quickly and another pipe not. The parallel pipe results get merged into the parent task, so waits to get next row in the right sequence could be accounted as this wait time, based on its description. I can't say I have used this field though. I will be interested to look at it in the future.

Sometimes transactions or batch extracts are costing a lot of CPU, despite efforts to tune the indexes. Using CPU Parallelism can be a way to reduce the costs by getting offload to zIIP processor. As parallel access paths are not the easiest to understand,it is worth trying both ways, with and without parallel on the high actual cost Cursors and Selects, to see if a saving can be made. Like a tuning method of last resort.  Sometimes the parallel approach costs more General Purpose CPU (or little saving) and that defeats the purpose usually, unless total elapsed time reduction is the objective.

Not sure if overladen CPU processors would be relevant to this field. Would hope not.

In a complex access path with parallel, sometimes parallel causes certain table accesses to be repeated in each pipe, actually increasing the total Getpages to that table significantly. Parallel normally helps with Gen CPU for large partitioned Tablespace scans in the absolute simplest case, or large index scans.

Be careful with parallel processes. We need sensible use.  A single runaway query can chew up several engines on the box/CEC, just by using DEGREE ANY. Have seen that happen. Wise to consider limiting with Resource Limit Facility.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd