Memory usage

Moderator: NorbertKrupa

Post Reply
vcarusi
Beginner
Beginner
Posts: 29
Joined: Mon Apr 20, 2015 11:03 am

Memory usage

Post by vcarusi » Fri May 20, 2016 8:28 am

Hi,
We have a web-app platform that uses Vertica server.
Our machines are on cloud, from AWS.
Vertica cluster has 3 nodes, m3.2xlarge, 8 vCPU, 30 GB memory.
SIze of the db files - 25 GB.
The database contains around 10 tables, maximum 15.000.000 records/table, 1000 columns/table.
The tables have only the default superprojections. There isn't any possibility to know which combination of columns will be used. It is up to the web-app user to chose the columns combination (=parameters) that will be used.
Also, there is an ETL process that load/update data.
Our problem is related to memory usage.
Sometimes, during the ETL process, the web-app is stuck because the SELECT-s requested by the web-app are put on queue and then rejected due to 'timeout' or 'issuficient memory'.
As I wrote on an other topic viewtopic.php?f=47&t=2516, the resource pool used by web-app has maximum 25 G, but only 6 SELECTs (2.5GB) can run simultaneously, that means only 6 web reports can be done.
The responsiveness for only few web-app users is unacceptable.
My questions are:
- how to set the aws instances/vertica server/resource_pool to accept more queries ?
create resource pool xxxx_pool
memorysize '30%'
maxmemorysize '95%'
priority 100
runtimepriority MEDIUM
runtimeprioritythreshold 30
queuetimeout 120
runtimecap '10 seconds'
cascade to general
- how to calculate the memory necessary for 100 (?!?) SELECTs ?
- how to calculate the maximum number of supported SELECTs for 25 GB ?
- how to decrease the memory used by a SELECT ?
- how to calculate memory is the database will increase to 1 T ?
Thank you

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Memory usage

Post by NorbertKrupa » Mon May 23, 2016 3:08 am

vcarusi wrote:Hi,
We have a web-app platform that uses Vertica server.
Our machines are on cloud, from AWS.
Vertica cluster has 3 nodes, m3.2xlarge, 8 vCPU, 30 GB memory.
SIze of the db files - 25 GB.
The database contains around 10 tables, maximum 15.000.000 records/table, 1000 columns/table.
Vertica does not perform well with wide tables. The memory allocated to these instances is quite low. Have you run the validation scripts to check CPU performance and I/O?
Checkout vertica.tips for more Vertica resources.

vcarusi
Beginner
Beginner
Posts: 29
Joined: Mon Apr 20, 2015 11:03 am

Re: Memory usage

Post by vcarusi » Mon May 23, 2016 2:17 pm

Hi,
Thank you for your answers.

You said: "Vertica does not perform well with wide tables."
My question: "wide" is referring to number of rows? number of columns ?
From my 1000 columns , 900 are number(16,5).

You said: "The memory allocated to these instances is quite low."
My question is: How to calculate an appropriate memory ?


Thank you,
Veronica

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Memory usage

Post by NorbertKrupa » Mon May 23, 2016 7:46 pm

vcarusi wrote:My question: "wide" is referring to number of rows? number of columns ?
Wide refers to the number of columns. The catalog may have improved in more recent versions, but wider tables could potentially degrade performance; especially if not optimized. Run DBD if you haven't already; it could potentially improve your encoding.

See the AWS guide for recommended instance types.

Take a look at the Concurrency and Workload Management guide for optimizing workload management. In the guide, see section 3.4 on cascading resource pools.
Checkout vertica.tips for more Vertica resources.

vcarusi
Beginner
Beginner
Posts: 29
Joined: Mon Apr 20, 2015 11:03 am

Re: Memory usage

Post by vcarusi » Tue May 24, 2016 2:56 pm

Thank you, again.

vcarusi
Beginner
Beginner
Posts: 29
Joined: Mon Apr 20, 2015 11:03 am

Re: Memory usage

Post by vcarusi » Wed Jun 01, 2016 11:45 am

Hi,
I found :

For select queries , memory allocation is related to the amount of columns on the select list (its around 2M per column) , in many cases it also related to many other factors like if your query include aggregation and sorting and it also related to your cluster setup (how many nodes )

https://community.dev.hpe.com/t5/Vertic ... rue#M11427

Veronica

Post Reply

Return to “Vertica Database Administration”