Hi ,
One of our customer is monitoring his new production system , and is having some queries reserving a huge amount of memory , and apparently not using it.
To give you an example we have some queries with RESERVED_EXTRA_MEMORY reaching 96226,75 (Mb) , and MEMORY_INUSE_KB at 4620,74 (Mb).
Is there an explanation for this , or am I missing something , or misunderstanding what RESERVED_EXTRA_MEMORY is ?
Could this be due to old statistics ?
Here's the query used to monitor this :
select ri.node_name, ri.user_name, ra.pool_name, ri.session_id, ri.request_id, ri.transaction_id, ri.statement_id, ri.request_type, ri.label as request_label, ri.search_path, round(ra.memory_mb_with_plan, 2) as memory_acquired_mb_with_plan, --Total amount of memory in kilobytes acquired by this query. round(ra.memory_mb_without_plan, 2) as memory_acquired_mb_without_plan, round(rc.reserved_extra_memory/1024/1024::float,2) as reserved_extra_memory_mb , -- Reserved memory but not used -- Shows how much unused memory (in bytes) remains that is reserved for a given query but is unassigned to a specific operator. This is the memory from which unbounded operators pull first. -- The MEMORY_INUSE_KB column in system table RESOURCE_ACQUISITIONS shows how much total memory was acquired for each query. -- If operators acquire all memory acquired for the query, the plan must request more memory from the Vertica -- Memory Plan + reserve rc.success, de.error_count, ri.time as start_timestamp, rc.time as end_timestamp, rc.time - ri.time as request_duration, cast(datediff('us', ri.time, rc.time)/1000 as number(15,3)) as request_duration_ms, rs.is_running IS NOT NULL AND rc.time IS NULL as is_executing , --added rc.processed_row_count, cast(ra.queue_wait_us/1000 as number(15,3)) as queue_wait_ms , replace(replace(ri.request, E'\n', ' '), E'\t', ' ') as request from v_internal.dc_requests_issued ri JOIN (select acq.node_name, acq.transaction_id, acq.statement_id, acq.pool_name, max(acq.memory_kb)/1024::float as memory_mb_with_plan, max(case when acq.request_type != 'Acquire' then acq.memory_kb else 0 end)/1024::float as memory_mb_without_Plan, sum(datediff(us, rel.queue_time, rel.acquire_time)) AS queue_wait_us from v_internal.dc_resource_acquisitions acq left join v_internal.dc_resource_releases rel on (acq.node_name=rel.node_name and acq.transaction_id=rel.transaction_id and acq.statement_id=rel.statement_id and acq.start_time = rel.queue_time) -- where result = 'Granted' group by 1,2,3,4) ra USING (node_name, transaction_id, statement_id) LEFT OUTER JOIN (select node_name, session_id, request_id, reserved_extra_memory, time, processed_row_count, schema_name, table_name, command_tag, completion_tag, success FROM v_internal.dc_requests_completed) rc USING (node_name, session_id, request_id) LEFT OUTER JOIN (select node_name, session_id, request_id, count(*) as error_count from v_internal.dc_errors where error_level >= 20 group by 1,2,3) de USING (node_name, session_id, request_id) LEFT OUTER JOIN (select node_name, user_name, session_id, statement_id, true as is_running -- Is session still running? Always true if it's in vs_sessions from v_internal.vs_sessions group by 1,2,3,4) rs USING (node_name, user_name, session_id, statement_id) order by memory_acquired_mb_with_plan desc
Regards
Bernard
