Memory Used by Each Query in Vertica

Abhishek_2

Hi,

      is there a way to see memory usage for each vertica Sessions during a period of time. 

I am currently using vertica 5.6.1.

 

Thanks in advance,

Abhishek

DerrickR

Hi Abhishek,

 

Whoa! That's an old version of Vertica you've got there. I'm uncomfortable coming up with answers regarding workload management (or any topic, really) for version 5.*. I suspect all or most of the approaches I would normally recommend will not be available.

 

I'd recommend upgrading to Vertica 7.1 and using Resource Manager's Resource Pools. You can create a pool with a planned concurrency and query budget according to your needs and assign users into that pool. You could also give each user a specific pool and put a max memory limit on it for more fine-grained control.

 

The documentation on workload management and resource manager can give you more guidance on this topic.

 

- Derrick

DerrickR

I also realized you just asked about seeing memory usage. 

 

Yes, you can use resource_pool_status and other monitoring tables for resource management to see the currently active reservations.

 

http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Monitoring/Vertica/MonitoringResourcePoolsAndResourceUsageByQueries.htm?Highlight=resource_pool_status

Sharon_Cutter

Hi Abhishek,

 

In 5.1, the RESOURCE_ACQUISITIONS table shows the memory in use for the currently running statements.  In 6.x and later it changed to include historical data as well.  To get historical data in 5.1, you could use the corresponding data collector tables if you want to review was has happened vs what is currently happening.  The dc tables aren't documented, but you can find them in the vs_system_tables table.  

 

In 6.x and later, the QUERY_REQUESTS table includes details for current and past queries, including how much memory the query used.  You could install 7.1 somewhere - in a VM perhaps - and then view the QUERY_REQUESTS system "table" (really a view) definition using the VS_SYSTEM_VIEWS table, and then create a view like that in 5.1 to get the same or similar details.

 

There are many good reasons though to upgrade from 5.1 to a more recent version.

 

  --Sharon

 

Abhishek_2

Hi Derrick and Sharon,

     Thanks for the reply, i was able to get the memory using the query.

 

I found Insert Statements are very slow, the user issuing the command belongs to a custom pool, during the load time i dont see the custom pool borrowing memory from general pool. but i see its taking time.  i am looking at execution_engine_profiles of the queries.

 

we dont have any other write load on the machine, but i still see wosdata pool borrowing memory.

 

Below are the pool details

 

pool_name	is_internal	memory_size_kb	memory_size_actual_kb	memory_inuse_kb	general_memory_borrowed_kb	queueing_threshold_kb	max_memory_size_kb	planned_concurrency	max_concurrency
wosdata	TRUE	0	0	0	4096	1992294	2097152	12	0
userQuerypool1	FALSE	139252138	139252138	91318673	0	178123696	187498629	32	30

 

 

any suggestions/ thoughts on this?

 

Regards & Thanks in Advance,

Abhishek