Understanding Vertica's memory requirements

Navin_C · July 2016

Hello,

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
|  LDISTRIB_UNSEGMENTED
| +---> 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)
| |      LDISTRIB_SEGMENTED

Data from DC_RESOURCE_ACQUISITIONS

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

Understanding Vertica's memory requirements

Leave a Comment