SYSQUERY_POOL_SPILL event in dc_execution engine events: Any concerns?
Sudhakar_B
Vertica Customer ✭✭
Good morning to all,
At one of the customer's production cluster, I found several SYSQUERY_POOL_SPILL event. Is this a performance or configuration concern? I searched the forum/and googled for this which was not much help.
Vertica cluster:
4 node cluster with Vertica version 9.3.1 64 GB, 8 core nodes Total RAW data volume 1.5 TB Catalog size ~2GB ~40 schema, 5K tables across all schema, with most having only super projections SYSQUERY pool settings: Memory size: 1GB (Everything else default).
Average/Max memory usage on cluster is 35-40% and once or twice a day peaks at 85-90%.
As a side question how is the SYSDATA pool used?
Any help/pointers will be of great help.
TIA
Best Answers
-
OptionsSruthiA
Vertica Employee Administrator
@Sudhakar_B: Yes you are correct. As you are aware WOS is deprecated in 10.0. There were many changes that were done as a part of 9.3 in order to remove WOS Support. Sysdata pool prior to 9.3 was using WOS to store temporary results. In 9.3 that has been disabled. Even though the pool exists , it is not used. Please find the links mentioning the same. In 10.x the pool has been removed completely. Hence you are not seeing it in 10.x
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/NewFeatures/_VersionIndependent/RemoveWOS_Support.htm
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Statements/Built-inPools.htmStart 9.3, we are using sysquery for storing the intermediate results of the queries that are run against system tables and if there is not sufficient memory they spill to disk. hence the reason you are seeing SYSQUERY_POOL_SPILL. Please try to increase resources for sysquery pool.
0 -
OptionsSruthiA
Vertica Employee Administrator
@Sudhakar_B : I don't think this spill creates an impact on overall performance of the cluster unless you execute hundreds of queries against the system tables. This event can be ignored.
0
Answers
@Sudhakar_B Sysdata pool is used for storing the intermediate results of the queries that are run against system tables. How many concurrent queries are run using system tables? What is the vertica version? Are you still using WOS?
@SruthiA,
Thank for prompt response. Much appreciated.
Vertica version at customer is 9.3.1 and yes, WOS is being used. Most of the queries against system tables are being run by dbadmin, so concurrency is determined by internal processes and resource pool definition, correct?
I asked about sysdata as I do not see that pool in version 10.0 VMware CE version.
Thanks much @SruthiA.
That explains. Is this "event" even worth pursuing from overall cluster performance perspective? What would be the guidelines for resource increase for sysquery pool?
There are several other fronts we are working on improving cluster/DB performance. Just was not sure of the "true impact" of sysquery spill". Since we'll upgrade to 10.x in near future, may be we can ignore this event?
Your thoughts please?
Thanks
Thanks @SruthiA
Much appreciated.
This can be closed.