1-- 2-- Copyright 2022 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 17SELECT IMPORT('common.timestamps'); 18 19-- Count Binder transactions per process. 20-- 21-- @column process_name Name of the process that started the binder transaction. 22-- @column pid PID of the process that started the binder transaction. 23-- @column slice_name Name of the slice with binder transaction. 24-- @column event_count Number of binder transactions in process in slice. 25CREATE VIEW android_binder_metrics_by_process AS 26SELECT 27 process.name AS process_name, 28 process.pid AS pid, 29 slice.name AS slice_name, 30 COUNT(*) AS event_count 31FROM slice 32JOIN thread_track ON slice.track_id = thread_track.id 33JOIN thread ON thread.utid = thread_track.utid 34JOIN process ON thread.upid = process.upid 35WHERE 36 slice.name GLOB 'binder*' 37GROUP BY 38 process_name, 39 slice_name; 40 41-- Breakdown synchronous binder transactions per txn. 42-- It returns data about the client and server ends of every binder transaction. 43-- 44-- @column aidl_name name of the binder interface if existing 45-- @column binder_txn_id slice id of the binder txn 46-- @column client_process name of the client process 47-- @column client_thread name of the client thread 48-- @column client_upid name of the client upid 49-- @column client_utid name of the client utid 50-- @column client_ts timestamp of the client txn 51-- @column client_dur dur of the client txn 52-- @column is_main_thread Whether the txn was initiated from the main thread of the client process 53-- @column binder_reply_id slice id of the binder reply 54-- @column server_process name of the server process 55-- @column server_thread name of the server thread 56-- @column server_upid name of the server upid 57-- @column server_utid name of the server utid 58-- @column server_ts timestamp of the server txn 59-- @column server_dur dur of the server txn 60CREATE VIEW android_sync_binder_metrics_by_txn AS 61WITH 62 -- Fetch the broken binder txns first, i.e, the txns that have children slices 63 -- They are definietly broken because synchronous txns are blocked sleeping while 64 -- waiting for a response. 65 -- These broken txns will be excluded below in the binder_txn CTE 66 broken_binder_txn AS ( 67 SELECT ancestor.id FROM slice 68 JOIN slice ancestor ON ancestor.id = slice.parent_id 69 WHERE ancestor.name = 'binder transaction' 70 GROUP BY ancestor.id 71 ), 72 -- Adding MATERIALIZED here matters in cases where there are few/no binder 73 -- transactions in the trace. Our cost estimation is not good enough to allow 74 -- the query planner to see through to this fact. Instead, our cost estimation 75 -- causes repeated queries on this table which is slow because it's an O(n) 76 -- query. 77 -- 78 -- We should fix this by doing some (ideally all) of the following: 79 -- 1) Add support for columnar tables in SQL which will allow for 80 -- "subsetting" the slice table to only contain binder transactions. 81 -- 2) Make this query faster by adding improving string filtering. 82 -- 3) Add caching so that even if these queries happen many times, they are 83 -- fast. 84 -- 4) Improve cost estimation algorithm to allow the joins to happen the 85 -- right way around. 86 binder_txn AS MATERIALIZED ( 87 SELECT 88 slice.id AS binder_txn_id, 89 process.name AS process_name, 90 thread.name AS thread_name, 91 thread.utid AS utid, 92 thread.tid AS tid, 93 process.pid AS pid, 94 process.upid AS upid, 95 slice.ts, 96 slice.dur, 97 thread.is_main_thread 98 FROM slice 99 JOIN thread_track ON slice.track_id = thread_track.id 100 JOIN thread USING (utid) 101 JOIN process USING (upid) 102 LEFT JOIN broken_binder_txn ON broken_binder_txn.id = slice.id 103 WHERE slice.name = 'binder transaction' 104 AND broken_binder_txn.id IS NULL 105 ), 106 binder_reply AS ( 107 SELECT 108 binder_txn.*, 109 binder_reply.ts AS server_ts, 110 binder_reply.dur AS server_dur, 111 binder_reply.id AS binder_reply_id, 112 reply_thread.name AS server_thread, 113 reply_process.name AS server_process, 114 reply_thread.utid AS server_utid, 115 reply_thread.tid AS server_tid, 116 reply_process.pid AS server_pid, 117 reply_process.upid AS server_upid, 118 aidl.name AS aidl_name 119 FROM binder_txn 120 JOIN flow binder_flow ON binder_txn.binder_txn_id = binder_flow.slice_out 121 JOIN slice binder_reply ON binder_flow.slice_in = binder_reply.id 122 JOIN thread_track reply_thread_track 123 ON binder_reply.track_id = reply_thread_track.id 124 JOIN thread reply_thread ON reply_thread.utid = reply_thread_track.utid 125 JOIN process reply_process ON reply_process.upid = reply_thread.upid 126 LEFT JOIN slice aidl 127 ON aidl.parent_id = binder_reply.id AND aidl.name LIKE 'AIDL::%' 128 ) 129SELECT 130 MIN(aidl_name) AS aidl_name, 131 binder_txn_id, 132 process_name AS client_process, 133 thread_name AS client_thread, 134 upid AS client_upid, 135 utid AS client_utid, 136 tid AS client_tid, 137 pid AS client_pid, 138 is_main_thread, 139 ts AS client_ts, 140 dur AS client_dur, 141 binder_reply_id, 142 server_process, 143 server_thread, 144 server_upid, 145 server_utid, 146 server_tid, 147 server_pid, 148 server_ts, 149 server_dur 150FROM binder_reply 151WHERE client_dur != -1 AND server_dur != -1 AND client_dur >= server_dur 152GROUP BY 153 process_name, 154 thread_name, 155 binder_txn_id, 156 binder_reply_id; 157 158CREATE VIEW internal_binder_txn 159AS 160SELECT client_ts AS ts, client_dur AS dur, client_utid AS utid, * 161FROM android_sync_binder_metrics_by_txn; 162 163CREATE VIEW internal_binder_reply 164AS 165SELECT server_ts AS ts, server_dur AS dur, server_utid AS utid, * 166FROM android_sync_binder_metrics_by_txn; 167 168CREATE VIRTUAL TABLE internal_sp_binder_txn_thread_state 169USING 170 SPAN_JOIN(internal_binder_txn PARTITIONED utid, thread_state PARTITIONED utid); 171 172CREATE VIRTUAL TABLE internal_sp_binder_reply_thread_state 173USING 174 SPAN_JOIN(internal_binder_reply PARTITIONED utid, thread_state PARTITIONED utid); 175 176-- Aggregated thread_states on the client and server side per binder txn 177-- This builds on the data from |android_sync_binder_metrics_by_txn| and 178-- for each end (client and server) of the transaction, it returns 179-- the aggregated sum of all the thread state durations. 180-- The |thread_state_type| column represents whether a given 'aggregated thread_state' 181-- row is on the client or server side. 'binder_txn' is client side and 'binder_reply' 182-- is server side. 183-- 184-- @column binder_txn_id slice id of the binder txn 185-- @column binder_reply_id slice id of the binder reply 186-- @column thread_state_type whether thread state is on the txn or reply side 187-- @column thread_state a thread_state that occurred in the txn 188-- @column thread_state_dur aggregated dur of the |thread_state| in the txn 189-- @column thread_state_count aggregated count of the |thread_state| in the txn 190CREATE VIEW android_sync_binder_thread_state_by_txn 191AS 192SELECT 193 binder_txn_id, 194 client_ts, 195 client_tid, 196 binder_reply_id, 197 server_ts, 198 server_tid, 199 'binder_txn' AS thread_state_type, 200 state AS thread_state, 201 SUM(dur) AS thread_state_dur, 202 COUNT(dur) AS thread_state_count 203FROM internal_sp_binder_txn_thread_state 204GROUP BY binder_txn_id, binder_reply_id, thread_state_type, thread_state 205UNION ALL 206SELECT 207 binder_txn_id, 208 client_ts, 209 client_tid, 210 binder_reply_id, 211 server_ts, 212 server_tid, 213 'binder_reply' AS thread_state_type, 214 state AS thread_state, 215 SUM(dur) AS thread_state_dur, 216 COUNT(dur) AS thread_state_count 217FROM internal_sp_binder_reply_thread_state 218GROUP BY binder_txn_id, binder_reply_id, thread_state_type, thread_state; 219 220-- Aggregated blocked_functions on the client and server side per binder txn 221-- This builds on the data from |android_sync_binder_metrics_by_txn| and 222-- for each end (client and server) of the transaction, it returns 223-- the aggregated sum of all the kernel blocked function durations. 224-- The |thread_state_type| column represents whether a given 'aggregated blocked_function' 225-- row is on the client or server side. 'binder_txn' is client side and 'binder_reply' 226-- is server side. 227-- 228-- @column binder_txn_id slice id of the binder txn 229-- @column binder_reply_id slice id of the binder reply 230-- @column thread_state_type whether thread state is on the txn or reply side 231-- @column blocked_function blocked kernel function in a thread state 232-- @column blocked_function_dur aggregated dur of the |blocked_function| in the txn 233-- @column blocked_function_count aggregated count of the |blocked_function| in the txn 234CREATE VIEW android_sync_binder_blocked_functions_by_txn 235AS 236SELECT 237 binder_txn_id, 238 client_ts, 239 client_tid, 240 binder_reply_id, 241 server_ts, 242 server_tid, 243 'binder_txn' AS thread_state_type, 244 blocked_function, 245 SUM(dur) AS blocked_function_dur, 246 COUNT(dur) AS blocked_function_count 247FROM internal_sp_binder_txn_thread_state 248WHERE blocked_function IS NOT NULL 249GROUP BY binder_txn_id, binder_reply_id, blocked_function 250UNION ALL 251SELECT 252 binder_txn_id, 253 client_ts, 254 client_tid, 255 binder_reply_id, 256 server_ts, 257 server_tid, 258 'binder_reply' AS thread_state_type, 259 blocked_function, 260 SUM(dur) AS blocked_function_dur, 261 COUNT(dur) AS blocked_function_count 262FROM internal_sp_binder_reply_thread_state 263WHERE blocked_function IS NOT NULL 264GROUP BY binder_txn_id, binder_reply_id, blocked_function; 265