High Memory Utilization
AlokT
Community Edition User
We have 26 nodes cluster on 9.2.1-5. We are observing high memory utilization on Node1, it approx 4 times high in comparison other nodes. Please help me to investigate this.
OS - CentOS 7.6
Please find the below output for you reference.
dbadmin=> select date_trunc('DAY',time),(resident_size_min_value/1024^3)::int "Min Mem Util in GB",(resident_size_max_value/1024^3)::int "Max Mem utili in GB" from dc_process_info_by_day where node_name='v_vdb_node0001' order by 1 desc limit 30;
date_trunc
Min Mem Util in GB
Max Mem utili in GB
------------------------+--------------------+---------------------
2020-07-09 00:00:00+00
28
52
2020-07-08 00:00:00+00
28
51
2020-07-07 00:00:00+00
28
52
2020-07-06 00:00:00+00
27
52
2020-07-05 00:00:00+00
28
51
2020-07-04 00:00:00+00
27
53
2020-07-03 00:00:00+00
26
51
2020-07-02 00:00:00+00
26
46
2020-07-01 00:00:00+00
24
55
2020-06-30 00:00:00+00
24
49
2020-06-29 00:00:00+00
25
48
2020-06-28 00:00:00+00
25
49
2020-06-27 00:00:00+00
24
51
2020-06-26 00:00:00+00
23
49
2020-06-25 00:00:00+00
22
44
2020-06-24 00:00:00+00
22
46
2020-06-23 00:00:00+00
21
43
2020-06-22 00:00:00+00
21
40
2020-06-21 00:00:00+00
21
37
2020-06-20 00:00:00+00
21
38
2020-06-19 00:00:00+00
21
42
2020-06-18 00:00:00+00
21
37
2020-06-17 00:00:00+00
22
38
2020-06-16 00:00:00+00
22
43
2020-06-15 00:00:00+00
21
37
2020-06-14 00:00:00+00
20
37
2020-06-13 00:00:00+00
20
35
2020-06-12 00:00:00+00
20
36
2020-06-11 00:00:00+00
19
35
2020-06-10 00:00:00+00
19
38
(30 rows)
dbadmin=>
dbadmin=> select date_trunc('DAY',time),(resident_size_min_value/1024^3)::int "Min Mem Util in GB",(resident_size_max_value/1024^3)::int "Max Mem utili in GB" from dc_process_info_by_day where node_name='v_vdb_node0002' order by 1 desc limit 30;
date_trunc
Min Mem Util in GB
Max Mem utili in GB
------------------------+--------------------+---------------------
2020-07-09 00:00:00+00
6
28
2020-07-08 00:00:00+00
6
28
2020-07-07 00:00:00+00
6
28
2020-07-06 00:00:00+00
6
29
2020-07-05 00:00:00+00
6
27
2020-07-04 00:00:00+00
7
30
2020-07-03 00:00:00+00
7
29
2020-07-02 00:00:00+00
6
27
2020-07-01 00:00:00+00
7
34
2020-06-30 00:00:00+00
6
30
2020-06-29 00:00:00+00
6
30
2020-06-28 00:00:00+00
6
28
2020-06-27 00:00:00+00
6
28
2020-06-26 00:00:00+00
6
28
2020-06-25 00:00:00+00
6
26
2020-06-24 00:00:00+00
6
32
2020-06-23 00:00:00+00
6
27
2020-06-22 00:00:00+00
6
23
2020-06-21 00:00:00+00
6
23
2020-06-20 00:00:00+00
6
22
2020-06-19 00:00:00+00
5
25
2020-06-18 00:00:00+00
5
22
2020-06-17 00:00:00+00
6
21
2020-06-16 00:00:00+00
5
26
2020-06-15 00:00:00+00
5
19
2020-06-14 00:00:00+00
6
19
2020-06-13 00:00:00+00
5
21
2020-06-12 00:00:00+00
5
20
2020-06-11 00:00:00+00
5
19
2020-06-10 00:00:00+00
5
21
(30 rows)
Answers
Please check the catalog size on each node by using the following query.
SELECT node_name ,max(ts) AS ts ,max(catalog_size_in_mb) AS catalog_size_in_mb FROM ( SELECT node_name ,trunc(("time")::TIMESTAMP, 'SS') AS ts ,sum((total_memory_max_value - free_memory_min_value)) / 1024 / 1024 AS catalog_size_in_mb FROM v_internal.dc_allocation_pool_statistics_by_second GROUP BY node_name ,trunc(("time")::TIMESTAMP, 'SS') ) a GROUP BY node_name ORDER BY node_name;If you store the enough history data in DC_EXECUTION_SUMMARIES table, please run the following query and check which transaction consumed more memory on node#1 than node#2.
WITH node1 AS ( SELECT transaction_id ,statement_id ,node_name ,peak_memory_kb FROM v_internal.dc_execution_summaries WHERE node_name ILIKE '%0001' ORDER BY transaction_id ,statement_id ) , node2 AS ( SELECT transaction_id ,statement_id ,node_name ,peak_memory_kb FROM v_internal.dc_execution_summaries WHERE node_name ILIKE '%0002' ORDER BY transaction_id ,statement_id ) SELECT node1.transaction_id ,node1.statement_id ,node1.peak_memory_kb AS node1_peak_memory_kb ,node2.peak_memory_kb AS node2_peak_memory_kb ,(node1.peak_memory_kb - node2.peak_memory_kb) AS diff_peak_memory_kb FROM node1 JOIN node2 USING (transaction_id, statement_id) ORDER BY 5 DESC LIMIT 20;Please check if the transactions were load balanced by using the following query.
SELECT node_name ,count( case when request_type ='LOAD' then 1 else null end ) AS loads_initiated ,count( case when request_type ='QUERY' then 1 else null end ) AS selects_initiated FROM v_internal.dc_requests_issued a JOIN v_internal.vs_nodes b ON a.node_name = b.name AND nodetype = 0 GROUP BY node_name ORDER BY node_name;You are using 9.2 so DC_EXECUTION_SUMMARIES table doesn't have peak_memory_kb column. Please use the below query to check which transaction consumed more memory on node#1 than node#2.
WITH node1 AS ( SELECT transaction_id ,statement_id ,node_name ,memory_acquired_mb FROM v_monitor.query_requests WHERE node_name ILIKE '%0001' ORDER BY transaction_id ,statement_id ) , node2 AS ( SELECT transaction_id ,statement_id ,node_name ,memory_acquired_mb FROM v_monitor.query_requests WHERE node_name ILIKE '%0002' ORDER BY transaction_id ,statement_id ) SELECT node1.transaction_id ,node1.statement_id ,node1.memory_acquired_mb AS node1_memory_acquired_mb ,node2.memory_acquired_mb AS node2_memory_acquired_mb ,(node1.memory_acquired_mb - node2.memory_acquired_mb) AS diff_memory_acquired_mb FROM node1 JOIN node2 USING (transaction_id, statement_id) ORDER BY 5 DESC LIMIT 20;