memory limit on queries

Filipe

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

kanako_o

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

Filipe

Hi,

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?

 

 

 

[Deleted User]

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. 

 

Eugenia

 

 

Adrian_Oprea_1

 What have you done so far ? 

 

Do you have any explain for that query ? 

 

 

Filipe

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

Thank you...