Memory Used by Each Query in Vertica

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

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

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

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

any suggestions/ thoughts on this?

Regards & Thanks in Advance,

Abhishek