Page 1 of 1

mc-activity-query monitoring-running queries-memory column

Posted: Thu May 19, 2016 11:30 am
by vcarusi
Hi,
I have the following situation:
- resource pool configured to use max 25 G memory
create resource pool xxxx_pool
memorysize '30%'
maxmemorysize '95%'
priority 100
runtimepriority MEDIUM
runtimeprioritythreshold 30
queuetimeout 120
runtimecap '10 seconds'
cascade to general

- 6 SELECTs running, that has associated 2.5 GB memory in column Memory(MB) = 15 GB
activity-query monitoring-running queries- total memory reserved = 15 GB

- in the tab 'Queued Queries' there are 4 queries that have , in column Avg Memory Reserved, 2.5 GB

- in mc-activity-resource pool monitoring-resource usage per pool -xxxx_pool
maximum memory size = 25 GB
average free memory - during the process - 25G decreased to 2G increased to 25 GB
- the system memory is used 30%
- part of them crashed due to:
1. Execution time exceeded run time cap of 00:00:10
2. Insufficient resources to execute plan on pool general [Timedout waiting for resource request: Request exceeds limits: Memory(KB) Exceeded: Requested = 2732190, Free = 1270742 (Limit = 17831818, Used = 16561076) (queueing threshold)]

I don't understand why the entire memory can't be used by the queued queries.
How can I see information about allocated/reserved memory for each resource pool ? seems to be different.
What is the algorithm used for allocation/reservation of memory ?

Thank you

Re: mc-activity-query monitoring-running queries-memory column

Posted: Thu May 19, 2016 1:22 pm
by vcarusi
On mc-activity-resource pool monitoring-query details by pools in xxxx_pool, I see for each query , one row for each node.

For the queries that are running, I have 3 rows (=3nodes) , with the same transaction_id,
column 'Execution ended'= 'Currently running' , column 'memory usage'=2,5G.

Also, for other queries, completed, the same 3 rows, with the same transaction_id,
column 'request type'=''Reserve', column 'memory usage'=18.9.

For an other query, there is the following evolution in time:
node/memory usage/request type
- node1, 100 MB , acquire
- node3, 1G, reserve
- node2, 1G, reserve
- node1, 1G, reserve

Could someone explain me how the server plays with the memory ?
There is a memory request for each node ?
What means 'acquire' and 'reserve' ?

Thank you

Re: mc-activity-query monitoring-running queries-memory column

Posted: Thu May 19, 2016 1:25 pm
by JimKnicely
Can u let us know what version of Vertica you are using.

Re: mc-activity-query monitoring-running queries-memory column

Posted: Thu May 19, 2016 1:35 pm
by vcarusi
Vertica Analytic Database v7.1.1-0

Re: mc-activity-query monitoring-running queries-memory column

Posted: Thu May 19, 2016 1:43 pm
by vcarusi
Using the following select :

select --i.node_name,i.session_id,i.transaction_id,i.statement_id,i.request_id,
i.time,i.request,
r.memory_acquired_mb,
r.request_duration_ms ,
r.success,
a.request_type,
a.pool_name,
a.memory_inuse_kb/1024 memory_inuse_mb,
a.acquisition_timestamp,
a.release_timestamp,
e.event_timestamp,
e.message
from v_internal.dc_requests_issued i
join query_requests r
on ( i.node_name = r.node_name
and i.session_id = r.session_id
and i.transaction_id = r.transaction_id
and i.statement_id = r.statement_id
and i.request_id = r.request_id )
left join RESOURCE_ACQUISITIONS a
on ( i.node_name = a.node_name
and i.transaction_id = a.transaction_id
and i.statement_id = a.statement_id)
left join error_messages e
on ( i.node_name = e.node_name
and i.session_id = e.session_id
and i.transaction_id = e.transaction_id
and i.statement_id = e.statement_id
and i.request_id = e.request_id )
where i.time >= cast('2016-05-19 11:48:00' as datetime )
and i.time <= cast('2016-05-19 11:55:00' as datetime )
and i.user_name = 'xxxxxxxx'
order by time


can be seen the difference between memory_acquired and memory_inuse.
Probably, the memory_acquired is memory reserved for statement and the second one is the really used memory.
memory_inuse < memory_acquired

Re: mc-activity-query monitoring-running queries-memory column

Posted: Thu Jun 02, 2016 10:00 am
by vcarusi
hi,
for a 'easy' query
|request         |memoryacquired_mb|pool_name|start_time         |request_type|acquisition_mb|
|SELECT 'fast'...|	100.0          |pool_1   |2016-06-02 10:41:01|Acquire	  | 100.0        |
|SELECT 'fast',..|	100.0          |pool_1   |2016-06-02 10:41:01|Reserve	  | 25.00        |

for a 'medium' query
|request |memoryacquired_mb|pool_name|start_time |request_type |acquisition_mb|
|SELECT 'medium'...| 1469.49 |pool_1 |2016-06-02 10:41:01|Acquire |100.0 |
|SELECT 'medium',..| 1469.49 |pool_1 |2016-06-02 10:41:03|Reserve |1,001.48 |
|SELECT 'medium'...| 1469.49 |pool_1 |2016-06-02 10:41:09|AcquireAdditional|1,157.48 |
|SELECT 'medium',..| 1469.49 |pool_1 |2016-06-02 10:41:13|AcquireAdditional| 1469.48 |



for a 'heavy' query
pool_1 - queuetimeout : 120, runtimecap: 00:00:30
general_pool - queuetimeout : 300, runtimecap: NULL

|request |memoryacquired_mb|duration_ms|pool_name | msg|start_time |request_type |acquisition_mb|
|SELECT 'heavy'...| 2683.66 | 64 |pool_1 |2016-06-02 10:41:01|Acquire |100.0 |
|SELECT 'heavy',..| 2683.66 | 64 |pool_1 |2016-06-02 10:41:03|Reserve |1,001.48 |
|SELECT 'heavy'...| 100 | 307 |general_pool |2016-06-02 10:41:09|Acquire |1,157.48 |
|SELECT 'heavy',..| 100 | 307 |general_pool |2016-06-02 10:41:13|Reserve | 1469.48 |


message
Execution time exceeded run time cap of 00:00:30
Execution time exceeded run time cap of 00:00:30
Insufficient resources to execute plan on pool general [Timedout waiting for resource request: Request exceeds limits: Memory(KB) Exceeded: Requested = 2733735, Free = 1253280 (Limit = 17831818, Used = 16578538) (queueing threshold)]
Insufficient resources to execute plan on pool general [Timedout waiting for resource request: Request exceeds limits: Memory(KB) Exceeded: Requested = 2733735, Free = 1253280 (Limit = 17831818, Used = 16578538) (queueing threshold)]

result
Granted
Granted
Granted
Timedout waiting for resource request: Request exceeds limits: Memory(KB) Exceeded: Requested = 2733735, Free = 1253280 (Limit =



select i.node_name,i.session_id,i.transaction_id,i.statement_id,i.request_id, i.time,i.request,
r.memory_acquired_mb,
r.request_duration_ms ,
r.success,
e.event_timestamp,
e.message,
a.pool_name,
a.start_time,
a.request_type,
a.memory_kb/1024,
a.result,
a.failing_resource
from v_internal.dc_requests_issued i
join query_requests r
on ( i.node_name = r.node_name
and i.session_id = r.session_id
and i.transaction_id = r.transaction_id
and i.statement_id = r.statement_id
and i.request_id = r.request_id )
left join error_messages e
on ( i.node_name = e.node_name
and i.session_id = e.session_id
and i.transaction_id = e.transaction_id
and i.statement_id = e.statement_id
and i.request_id = e.request_id )
join dc_resource_acquisitions a
on ( i.node_name = a.node_name
and i.transaction_id = a.transaction_id
and i.statement_id = a.statement_id)
where i.time >= cast('2016-06-02 07:41:00' as datetime )
and i.time <= cast('2016-06-02 07:55:00' as datetime )
and i.user_name = 'user_xxxxxx'
order by session_id, transaction_id,statement_id, request_id, a.start_time

Re: mc-activity-query monitoring-running queries-memory column

Posted: Tue Mar 21, 2017 11:44 am
by campertyler
Verry good :idea: