Skip to main content
Question

SYSQUERY_POOL_SPILL event in dc_execution engine events: Any concerns?

  • December 15, 2020
  • 6 replies
  • 14 views
Sudhakar_B
Forum|alt.badge.img+1

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

    6 replies

    SruthiA
    Forum|alt.badge.img+1
    • SruthiA
    • Participating Frequently
    • December 15, 2020

    @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?

    Sudhakar_B
    Forum|alt.badge.img+1
    • Sudhakar_B
    • Author
    • Participating Frequently
    • December 15, 2020

    @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.

    SruthiA
    Forum|alt.badge.img+1
    • SruthiA
    • Participating Frequently
    • December 15, 2020

    @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.htm

    Start 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.

    Sudhakar_B
    Forum|alt.badge.img+1
    • Sudhakar_B
    • Author
    • Participating Frequently
    • December 15, 2020

    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

    SruthiA
    Forum|alt.badge.img+1
    • SruthiA
    • Participating Frequently
    • December 15, 2020

    @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.

    Sudhakar_B
    Forum|alt.badge.img+1
    • Sudhakar_B
    • Author
    • Participating Frequently
    • December 15, 2020

    Thanks @SruthiA
    Much appreciated.
    This can be closed.

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement