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
mc-activity-query monitoring-running queries-memory column
Moderator: NorbertKrupa
Re: mc-activity-query monitoring-running queries-memory column
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
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
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: mc-activity-query monitoring-running queries-memory column
Can u let us know what version of Vertica you are using.
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: mc-activity-query monitoring-running queries-memory column
Vertica Analytic Database v7.1.1-0
Re: mc-activity-query monitoring-running queries-memory column
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
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
hi,
for a 'easy' query
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
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
-
- Newbie
- Posts: 1
- Joined: Tue Mar 21, 2017 11:34 am
- Contact:
Re: mc-activity-query monitoring-running queries-memory column
Verry good
Dịch vụ giúp việc nhà theo giờ uy tín chất lượng cao, công ty giup viec nha theo gio hàng đầu ở khu vực hcm