Adam Bookatz | d5aa763 | 2021-08-25 16:13:44 -0700 | [diff] [blame] | 1 | -- |
| 2 | -- Copyright 2021 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 | -- https://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 | |
| 17 | -- Collect the important timestamps for Multiuser events. |
| 18 | DROP VIEW IF EXISTS multiuser_events; |
| 19 | CREATE VIEW multiuser_events AS |
| 20 | SELECT |
| 21 | {{start_event}}_time_ns AS event_start_time_ns, |
| 22 | {{end_event}}_time_ns AS event_end_time_ns |
| 23 | FROM |
| 24 | ( |
| 25 | SELECT slice.ts AS user_start_time_ns |
| 26 | FROM slice |
| 27 | WHERE (slice.name LIKE "onBeforeStartUser%") |
| 28 | -- TODO: Use a signal based on SysUi so that it covers all switching, not just starting. |
| 29 | ), |
| 30 | ( |
| 31 | SELECT slice.ts + slice.dur AS launcher_end_time_ns |
| 32 | FROM slice |
| 33 | WHERE (slice.name = "launching: com.google.android.apps.nexuslauncher") |
| 34 | ); |
| 35 | |
| 36 | -- Calculation of the duration of the Multiuser event of interest. |
| 37 | DROP VIEW IF EXISTS multiuser_timing; |
| 38 | CREATE VIEW multiuser_timing AS |
| 39 | SELECT |
| 40 | CAST((event_end_time_ns - event_start_time_ns) / 1e6 + 0.5 AS INT) AS duration_ms |
| 41 | FROM |
| 42 | multiuser_events; |
| 43 | |
| 44 | |
| 45 | -- Calculate CPU usage during the Multiuser event of interest. |
| 46 | |
| 47 | -- Get all the scheduling slices. |
| 48 | DROP VIEW IF EXISTS sp_sched; |
| 49 | CREATE VIEW sp_sched AS |
| 50 | SELECT ts, dur, cpu, utid |
| 51 | FROM sched; |
| 52 | -- Get all the cpu frequency slices. |
| 53 | DROP VIEW IF EXISTS sp_frequency; |
| 54 | CREATE VIEW sp_frequency AS |
| 55 | SELECT |
| 56 | ts, |
| 57 | lead(ts) OVER (PARTITION BY track_id ORDER BY ts) - ts as dur, |
| 58 | cpu, |
| 59 | value as freq_khz |
| 60 | FROM counter |
| 61 | JOIN cpu_counter_track ON counter.track_id = cpu_counter_track.id; |
| 62 | -- Create the span joined table which combines cpu frequency with scheduling slices. |
| 63 | DROP TABLE IF EXISTS sched_with_frequency; |
| 64 | CREATE VIRTUAL TABLE sched_with_frequency |
| 65 | USING SPAN_JOIN(sp_sched PARTITIONED cpu, sp_frequency PARTITIONED cpu); |
| 66 | |
| 67 | -- Calculate the CPU cycles spent per process during the duration. |
| 68 | DROP VIEW IF EXISTS cpu_usage_all; |
| 69 | CREATE VIEW cpu_usage_all AS |
| 70 | SELECT |
| 71 | process.uid / 100000 AS user_id, |
| 72 | process.name AS process_name, |
| 73 | SUM(dur * freq_khz) / 1e9 AS cpu_kcycles |
| 74 | FROM |
| 75 | sched_with_frequency |
| 76 | JOIN thread USING (utid) |
| 77 | JOIN process USING (upid) |
| 78 | WHERE |
| 79 | ts >= (SELECT event_start_time_ns FROM multiuser_events) |
| 80 | AND |
| 81 | ts <= (SELECT event_end_time_ns FROM multiuser_events) |
| 82 | GROUP BY upid, process.name |
| 83 | ORDER BY cpu_kcycles DESC; |
| 84 | |
| 85 | -- Get the data from cpu_usage_all, but also with the percentage. |
| 86 | DROP VIEW IF EXISTS cpu_usage; |
| 87 | CREATE VIEW cpu_usage AS |
| 88 | SELECT |
| 89 | user_id, |
| 90 | process_name, |
Adam Bookatz | bb229e6 | 2021-10-04 17:20:33 -0700 | [diff] [blame] | 91 | process_name || ":" || (CASE WHEN user_id = 0 THEN "system" ELSE "secondary" END) AS identifier, |
Adam Bookatz | d5aa763 | 2021-08-25 16:13:44 -0700 | [diff] [blame] | 92 | CAST(cpu_kcycles / 1e3 AS INT) AS cpu_mcycles, |
| 93 | cpu_kcycles / (SELECT SUM(cpu_kcycles) FROM cpu_usage_all) * 100 AS cpu_percentage |
| 94 | FROM |
| 95 | cpu_usage_all |
| 96 | ORDER BY cpu_mcycles DESC LIMIT 6; |
| 97 | |
| 98 | |
| 99 | -- Record the output for populating the proto. |
Dan Elphick | d4dac39 | 2021-09-16 15:39:46 +0100 | [diff] [blame] | 100 | DROP VIEW IF EXISTS {{output_table_name}}; |
Adam Bookatz | d5aa763 | 2021-08-25 16:13:44 -0700 | [diff] [blame] | 101 | CREATE VIEW {{output_table_name}} AS |
| 102 | SELECT AndroidMultiuserMetric_EventData( |
| 103 | 'duration_ms', ( |
| 104 | SELECT duration_ms |
| 105 | FROM multiuser_timing |
| 106 | ), |
| 107 | 'cpu_usage', ( |
| 108 | SELECT RepeatedField( |
| 109 | AndroidMultiuserMetric_EventData_CpuUsage( |
Adam Bookatz | bb229e6 | 2021-10-04 17:20:33 -0700 | [diff] [blame] | 110 | 'identifier', identifier, |
Adam Bookatz | d5aa763 | 2021-08-25 16:13:44 -0700 | [diff] [blame] | 111 | 'user_id', user_id, |
| 112 | 'process_name', process_name, |
| 113 | 'cpu_mcycles', cpu_mcycles, |
| 114 | 'cpu_percentage', cpu_percentage |
| 115 | ) |
| 116 | ) |
| 117 | FROM cpu_usage |
| 118 | ) |
Dan Elphick | d4dac39 | 2021-09-16 15:39:46 +0100 | [diff] [blame] | 119 | ); |