Memory not utilized above 10% by Vertica

Sajan_Sangraula

We have a 3 node dev vertica cluster, each node with 24 cores, 96G memory, and 4TB storage @10000 rpm. Even at high server loads with concurrent query and data load going on, the memory usage doesnt go about 10% in each of the node. The CPU is utilized at 100% for all the cores in all nodes. The general resource pool is being used for all querying and data load, and the maxmemorysize is "Special: 95%". Swap is not being used. Are there any parameters to tweak for better memory utilization?

[Deleted User]

Hi Sajan, I think my first question would be, why are you trying to increase Vertica's memory usage? Most people want to decrease it :-) Using more memory is easy; I can whip up a program that uses lots of memory in a hurry if you'd like. Low memory utilization can actually be a good thing -- it can mean that you have optimally-tuned projections such that all of your data is pre-sorted; Vertica is streaming data straight from disk, through a processing pipeline, and right to the user without having to buffer it in order to do a re-sort. If you get to this point, then your queries will be incredibly fast and you'll be using very little memory. I assume you suspect that this is not the case for you, and would like to try to improve performance? Here are some relevant knobs that you can try turning: - Regarding resource pools, take a look at the concurrency settings as well. If you force Vertica to reserve enough system resources to run 50 queries at once, but you're only running 10, then Vertica's going to be leaving a lot of memory idle just in case more queries come along. - Do your tables have very large VARCHAR or VARBINARY fields where the data in them is quite tiny? For certain operations (especially data load), Vertica can't make any assumptions about the size of your input records; it has to reserve enough space for the max-possible size, even though it may not actually need to allocate that space. If those don't help and you still feel that you're seeing a performance problem, could you describe the performance you're getting in more detail? Thanks, Adam

Sajan_Sangraula

Hi Adam, Thanks for the detailed response. I can't argue that low memory utilization is a good thing , and most of our queries are tuned to use group by pipeline and run quite fast. However, my concern is more on the disproportionate utilization of resources: the CPU utilization is consistently 100% while most of the memory is sitting idle. I am trying to figure out whether any configuration parameter is restricting memory usage while queries are waiting on I/O, or whether the I/O or the interconnect between the nodes is not keeping up with the query requirements, keeping the CPUs on wait. PlannedConcurrency is set to 8 and ExecutionParallelism is 16. We dont have any varbinary columns, and the varchar columns are all within 1024 characters. So to rephrase my question, can I get better query response time and concurrency by utilizing more of the available memory that is currently sitting idle? Thanks a lot Sajan

Bharath_Anandar

Hey Sajan - Were you able to work around the issue you were facing? We hare having almost the same issue although all our joins and group by are group by pipelined, but we also have a stream or cpoy statements running through out the day. Thanks, Bharath

[Deleted User]

Hi Bharath (and Sajan, sorry for not following up), Do you see your CPUs spending time in %wait, or are they just busy? (%cpu, %nice, etc) Vertica is very careful to compress its data on disk and to do sequential IO rather than random IO. It actually is common for a Vertica cluster to max out its CPUs long before overloading its IO subsystem. (If your CPUs are just plain busy, there's no advantage to using more RAM.) Adam

Sajan_Sangraula

Hi Bharath and Adam, Like Adam mentioned, we see our CPU utilization peak out, more so with concurrent user access, but the memory usage continues to remain low. We have a similar workload to what you mentioned with ongoing data load and queries, and iostat doesnt show anything abnormal as well. /Sajan

Bharath_Anandar

Adam - We will take up our case with Vertica Support, but thanks for your help. -B