Understanding Vertica's memory requirements

Navin_CNavin_C Vertica Customer



This questions is related to understanding Vertica's memory requirements.

I want to know the exact memory required by vertica for a sample query and how its calculated.


Below is simple example :


create table test_explain (id int)
insert into test_explain values (1)
insert into test_explain values (2);
insert into test_explain values (3);
insert into test_explain values (4);
insert into test_explain values (5);
insert into test_explain values (6);
insert into test_explain values (7);
insert into test_explain values (8);

Profile the table


Initiator memory for query: [on pool general: 18742 KB, minimum: 18742 KB]
Total memory required by query: [18742 KB]



Explain Verbose

+-GROUPBY NOTHING [Cost: 2.000000, Rows: 1.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 32.000000 Netwrk(B): 8.000000 Parallelism: 3.000000] [OutRowSz (B): 8] (PATH ID: 1)
| Aggregates: count(*)
| Execute on: All Nodes
| +---> STORAGE ACCESS for test_explain [Cost: 1.000000, Rows: 8.000000 (1.000000 RLE) Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 3.000000] [OutRowSz (B): 0] (PATH ID: 2)
| | Column Cost Aspects: [ Disk(B): 64.000000 CPU(B): 0.000000 Memory(B): 8.000000 Netwrk(B): 0.000000 Parallelism: 3.000000 ]
| | Projection: public.test_explain_b0
| | Execute on: All Nodes
| | Sort Key: (test_explain.id)



Pool_name Request_type Memory_kb result
general Reserve 15192 Granted
general Acquire 204800 Granted
general Reserve 18742 Granted
general Reserve 15192 Granted


Questions :

1. The table has 8 int records, why does it requrire 18 MB to process this query at first place (from profiles).

2. Can we some how calculate the amount of memory that the query will ask for (not budgeted memory) in     this case, is there a calculation that gets a result of 18 MB

3. Explain Verbose, shows lots of detail lables, For Path ID 1, we see memory as 32 bytes and [OutRowSz (B): 8], is there any correlation between these numbers and the total memory used by the query.

4. For both steps in Explain verbose , we see total 40 B used, why does query ask for 18 MB of memory.

5. OutRowSz label , does this mean the size of each row given as an output from this step OR total size of rows given out by this step.

6. In result of DC_RESOURCE_ACQUISITIONS, what does acquire / reserve / acquireadditional mean

7. This table shows acquire as 204 MB and reserved as 18 MB, why ?


We are trying to understand this, since some of our queries as asking for 75GB memory.

We have implemented Resources pools and are aware of query budgeting, but these questions seem to be related to  how vertica manages the memory.


Thanks in Advance


Leave a Comment

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