1/* Create the file RSS table. */ 2CREATE VIEW file_rss AS 3SELECT ts, 4 LEAD(ts, 1, ts) OVER(PARTITION BY ref ORDER BY ts) - ts as dur, 5 ref AS upid, 6 value AS file 7FROM counters 8WHERE ref_type = 'upid' AND counters.name IN ("mem.rss.file", "rss_stat.mm_filepages"); 9 10/* Create the anon RSS table. */ 11create view anon_rss as 12select ts, 13 lead(ts, 1, ts) over(PARTITION by ref order by ts) - ts as dur, 14 ref as upid, 15 value as anon 16from counters 17where ref_type = 'upid' and counters.name in ("mem.rss.anon", "rss_stat.mm_anonpages"); 18 19/* Create the oom adj table. */ 20create view oom_adj as 21select ts, 22 lead(ts, 1, ts) over(PARTITION by ref order by ts) - ts as dur, 23 ref as upid, 24 value as oom_score_adj 25from counters 26where ref_type = 'upid' and counters.name = 'oom_score_adj'; 27 28/* Harmonise the three tables above into a single table. */ 29CREATE VIRTUAL TABLE anon_file USING span_join(anon_rss PARTITIONED upid, file_rss PARTITIONED upid); 30 31CREATE VIRTUAL TABLE anon_file_oom USING span_join(anon_file PARTITIONED upid, oom_adj PARTITIONED upid); 32 33/* For each span, compute the RSS (for a given upid) for each category of oom_adj */ 34CREATE VIEW rss_spans_for_oom_upid AS 35SELECT ts, 36 dur, 37 upid, 38 CASE WHEN oom_score_adj < 0 THEN file + anon ELSE 0 END as rss_oom_lt_zero, 39 CASE WHEN oom_score_adj <= 0 THEN file + anon ELSE 0 END as rss_oom_eq_zero, 40 CASE WHEN oom_score_adj < 20 THEN file + anon ELSE 0 END as rss_fg, 41 CASE WHEN oom_score_adj < 700 THEN file + anon ELSE 0 END as rss_bg, 42 CASE WHEN oom_score_adj < 900 THEN file + anon ELSE 0 END as rss_low_bg, 43 file + anon as rss_cached 44FROM anon_file_oom; 45 46/* Convert the raw RSS values to the change in RSS (for a given upid) over time */ 47CREATE VIEW rss_spans_rss_change AS 48SELECT ts, 49 dur, 50 upid, 51 rss_oom_lt_zero - lag(rss_oom_lt_zero, 1, 0) OVER win as rss_oom_lt_zero_diff, 52 rss_oom_eq_zero - lag(rss_oom_eq_zero, 1, 0) OVER win as rss_oom_eq_zero_diff, 53 rss_fg - lag(rss_fg, 1, 0) OVER win as rss_fg_diff, 54 rss_bg - lag(rss_bg, 1, 0) OVER win as rss_bg_diff, 55 rss_low_bg - lag(rss_low_bg, 1, 0) OVER win as rss_low_bg_diff, 56 rss_cached - lag(rss_cached, 1, 0) OVER win as rss_cached_diff 57FROM rss_spans_for_oom_upid 58WINDOW win AS (PARTITION BY upid ORDER BY ts); 59 60/* 61 * Compute a rolling sum of anon + file for each category of process state. 62 * (note: we no longer consider upid in the windows which means we are now 63 * computing a rolling sum for all the processes). 64 */ 65CREATE VIEW output AS 66SELECT ts, 67 lead(ts, 1, ts) over win - ts as dur, 68 SUM(rss_oom_lt_zero_diff) OVER win as rss_oom_lt_zero, 69 SUM(rss_oom_eq_zero_diff) OVER win as rss_oom_eq_zero, 70 SUM(rss_fg_diff) OVER win as rss_fg, 71 SUM(rss_bg_diff) OVER win as rss_bg, 72 SUM(rss_low_bg_diff) OVER win as rss_low_bg, 73 SUM(rss_cached_diff) OVER win as rss_cached 74FROM rss_spans_rss_change 75WINDOW win as (ORDER BY ts) 76ORDER BY ts; 77 78/* 79 * Print out the final result (note: dur = 0 is excluded to account for times 80 * where multiple processes report a new memory value at the same timestamp) 81 */ 82SELECT * 83FROM output 84WHERE dur > 0 85