High Memory Utilization

AlokT · August 2020

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)

Hibiki · August 2020

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;

Hibiki · August 2020

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;

High Memory Utilization

Answers

Leave a Comment