memory limit on queries

Vertica limited memory in queries?

I have an amount of 20 million records, which can improve the level of database configuration?


Virtual Machine

Intel(R) Xeon(R) CPU X5675 @ 3.07GHz Quad Core

Memory - 6GB


  • Options
    You can use the memory up to 95% of physical memory at most. https://my.vertica.com/docs/7.2.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/Built-inPools.htm This link might be helpful...
  • Options


    Thank you...

    This is the setting that is in my server...

    But a query takes a long time to run, i can check to improve performance?




  • Options

    the 95% is the resouce pool, but does not mean that is what vertica uses for a query. If you want to see how much uses you can do 


    Profile << query>> and it will print the memory used. 


    when you use profile key word it also print the transaction_id and statment_id. With that info you could check query_event table and see if the query spills. 


    you also can check the query_profile with the transaction_id and statment_id and see the memory used and if need additional memory. 


    In addition, you could work with your projection desing to avoid Hash Joins and Group by Hash that could make the query slow. 


    but in conclusion, it will depend on the query and projection design that will determine the amount of memory needed, you could give more memory to the query or just reduce the memory that the query needs if you can.. 


    Hope this make sense and helps. 





  • Options

     What have you done so far ? 


    Do you have any explain for that query ? 



  • Options

    I was using views for consultations, I decided the situation of performance running my queries directly in the table.

    Thank you...

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file