wosdata resource behaviour & tuning
blp
Vertica Customer Employee
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 :
wosdataConfiguration ValuesMaximum Memory (MB)2048Planned Concurrency2Maximum Concurrency0Queue Timeout (sec)0Priority0Runtime Priority Threshold (sec)0
tmConfiguration ValuesMaximum Memory (MB)50235Planned Concurrency2Maximum Concurrency3Queue Timeout (sec)300Priority105Runtime Priority Threshold (sec)60
generalConfiguration ValuesMaximum Memory (MB)104688Planned Concurrency16Maximum Concurrency0Queue Timeout (sec)300Priority0Runtime Priority Threshold (sec)2
Thanks !
Comments
Are you able to upgrade? v10.0 eliminates this issue entirely: "As of 10.0, WOS and related functionality has been removed from Vertica."
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