wosdata resource behaviour & tuning

blp

Hi,
I'm using a 3 node vertica cluster (9.2) and noticed while performing some benchmarks the following errors in the ERROR_MESSAGE table :

WARNING Unable to reserve memory (17 K) for the WOS

Specs per node are the following :
16 cores, 120 GB ram , 1TB storage for vertica

All the major writes queries that are sent to the cluster are either COPY or MERGE statements COPY having the following shape :

COPY TABLE FROM 'path.orc' ON ANY NODE orc DIRECT NO COMMIT

Shouldn't the DIRECT hint prevent the usage of WOS ?

In this case the wosdata pool would not be used by the COPY query having the direct hint ?

I don't see any DIRECT hint capacity for the MERGE query.
Large MERGE are supposed to be not really efficient on Vertica, would there be any improvement that could be applied to make a large MERGE perform better ?

Changes tried so far :

• Increase wosdata memory size from 2GB to 6GB, MaxPlanedConcurency=2, PlanedConcurency=2
• Increase wosdata memory size from 6GB to 14GB, MaxPlanedConcurency=2, PlanedConcurency=2
• wosdata memory size 14GB, MaxPlanedConcurency=4, PlanedConcurency=4

Also tried to change tuple mover values
Nothing improved the query runtimes, yet the number of error dropped significantly or disappeared when increasing memory for the wosdata pool.
Would you have tuning tips to provide ?
Any feedback to understand the problem is very welcome

Here are the initial pool parameters that were used :
wosdata
Configuration Values
Maximum Memory (MB)2048
Planned Concurrency2
Maximum Concurrency0
Queue Timeout (sec)0
Priority0
Runtime Priority Threshold (sec)0
tm
Configuration Values
Maximum Memory (MB)50235
Planned Concurrency2
Maximum Concurrency3
Queue Timeout (sec)300
Priority105
Runtime Priority Threshold (sec)60
general
Configuration Values
Maximum Memory (MB)104688
Planned Concurrency16
Maximum Concurrency0
Queue Timeout (sec)300
Priority0
Runtime Priority Threshold (sec)2

Thanks !

Bryan_H

Are you able to upgrade? v10.0 eliminates this issue entirely: "As of 10.0, WOS and related functionality has been removed from Vertica."

[Deleted User]

WOS will not get used if you pass /*+DIRECT*/ hint . For Merge statement you need to pass immediately after MERGE keyword.

See: https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/LanguageElements/Hints/direct.htm