1/*
2 * Copyright (C) 2019 The Android Open Source Project
3 *
4 * Licensed under the Apache License, Version 2.0 (the "License");
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
7 *
8 *      http://www.apache.org/licenses/LICENSE-2.0
9 *
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an "AS IS" BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License.
15 */
16
17DROP VIEW IF EXISTS sched_switch_iowaits_pre;
18
19-- scan for the closest pair such that:
20--                  sched_block_reason pid=$PID iowait=1 ...
21--                  ...
22--                  sched_switch next_pid=$PID
23CREATE VIEW sched_switch_iowaits_pre AS
24    SELECT MAX(sbr.id) AS blocked_id,
25           ss.id AS sched_switch_id,
26           pid,         -- iow.pid
27           iowait,      -- iowait=0 or iowait=1
28           caller,
29           sbr_f.timestamp AS blocked_timestamp,
30           ss_f.timestamp AS sched_switch_timestamp,
31           next_comm,   -- name of next_pid
32           next_pid     -- same as iow.pid
33    FROM sched_blocked_reasons AS sbr,
34         raw_ftrace_entries AS sbr_f,
35         sched_switches AS ss,
36         raw_ftrace_entries AS ss_f
37    WHERE sbr_f.id == sbr.id
38          AND ss_f.id == ss.id
39          AND sbr.pid == ss.next_pid
40          AND sbr.iowait = 1
41          AND sbr_f.timestamp < ss_f.timestamp     -- ensures the 'closest' sched_blocked_reason is selected.
42    GROUP BY ss.id
43;
44
45DROP VIEW IF EXISTS sched_switch_iowaits;
46
47CREATE VIEW sched_switch_iowaits AS
48    SELECT *, MIN(sched_switch_timestamp) AS ss_timestamp      -- drop all of the 'too large' sched_switch entries except the closest one.
49    FROM sched_switch_iowaits_pre
50    GROUP BY blocked_id;
51
52SELECT * FROM sched_switch_iowaits;
53
54-- use a real table here instead of a view, otherwise SQLiteStudio segfaults for some reason.
55DROP TABLE IF EXISTS blocking_durations;
56
57CREATE TABLE blocking_durations AS
58WITH
59    blocking_durations_raw AS (
60        SELECT MAX(ss.id) AS block_id,
61               ssf.timestamp AS block_timestamp,
62               iow.sched_switch_timestamp AS unblock_timestamp,
63               ss.prev_comm as block_prev_comm,
64               iow.next_comm AS unblock_next_comm,
65               ss.prev_state AS block_prev_state,
66               iow.sched_switch_id AS unblock_id,
67               iow.pid AS unblock_pid,
68               iow.caller AS unblock_caller
69        FROM sched_switches AS ss,          -- this is the sched_switch that caused a block (in the future when it unblocks, the reason is iowait=1).
70             sched_switch_iowaits AS iow,    -- this is the sched_switch that removes the block (it is now running again).
71             raw_ftrace_entries AS ssf
72        WHERE ssf.id = ss.id AND ss.prev_pid == iow.next_pid AND ssf.timestamp < iow.sched_switch_timestamp
73        GROUP BY unblock_timestamp
74    ),
75    blocking_durations_tmp AS (
76        SELECT block_id,
77               unblock_timestamp,
78               block_timestamp,
79               block_prev_comm as comm,
80               block_prev_state as block_state,
81               unblock_id,
82               unblock_pid,
83               unblock_caller
84        FROM blocking_durations_raw
85    )
86    SELECT * FROM blocking_durations_tmp;-- ORDER BY block_id ASC;
87    --SELECT SUM(block_duration_ms) AS sum, * FROM blocking_durations GROUP BY unblock_pid ORDER BY sum DESC;
88
89DROP INDEX IF EXISTS "blocking_durations_block_timestamp";
90
91CREATE INDEX "blocking_durations_block_timestamp" ON blocking_durations (
92    block_timestamp COLLATE BINARY COLLATE BINARY
93);
94
95DROP INDEX IF EXISTS "blocking_durations_unblock_timestamp";
96
97CREATE INDEX "blocking_durations_unblock_timestamp" ON blocking_durations (
98    unblock_timestamp COLLATE BINARY COLLATE BINARY
99);
100
101SELECT * FROM blocking_durations;
102