Loading...
1# export-to-sqlite.py: export perf data to a sqlite3 database
2# Copyright (c) 2017, Intel Corporation.
3#
4# This program is free software; you can redistribute it and/or modify it
5# under the terms and conditions of the GNU General Public License,
6# version 2, as published by the Free Software Foundation.
7#
8# This program is distributed in the hope it will be useful, but WITHOUT
9# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
10# FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
11# more details.
12
13from __future__ import print_function
14
15import os
16import sys
17import struct
18import datetime
19
20# To use this script you will need to have installed package python-pyside which
21# provides LGPL-licensed Python bindings for Qt. You will also need the package
22# libqt4-sql-sqlite for Qt sqlite3 support.
23#
24# Examples of installing pyside:
25#
26# ubuntu:
27#
28# $ sudo apt-get install python-pyside.qtsql libqt4-sql-psql
29#
30# Alternately, to use Python3 and/or pyside 2, one of the following:
31#
32# $ sudo apt-get install python3-pyside.qtsql libqt4-sql-psql
33# $ sudo apt-get install python-pyside2.qtsql libqt5sql5-psql
34# $ sudo apt-get install python3-pyside2.qtsql libqt5sql5-psql
35# fedora:
36#
37# $ sudo yum install python-pyside
38#
39# Alternately, to use Python3 and/or pyside 2, one of the following:
40# $ sudo yum install python3-pyside
41# $ pip install --user PySide2
42# $ pip3 install --user PySide2
43#
44# An example of using this script with Intel PT:
45#
46# $ perf record -e intel_pt//u ls
47# $ perf script -s ~/libexec/perf-core/scripts/python/export-to-sqlite.py pt_example branches calls
48# 2017-07-31 14:26:07.326913 Creating database...
49# 2017-07-31 14:26:07.538097 Writing records...
50# 2017-07-31 14:26:09.889292 Adding indexes
51# 2017-07-31 14:26:09.958746 Done
52#
53# To browse the database, sqlite3 can be used e.g.
54#
55# $ sqlite3 pt_example
56# sqlite> .header on
57# sqlite> select * from samples_view where id < 10;
58# sqlite> .mode column
59# sqlite> select * from samples_view where id < 10;
60# sqlite> .tables
61# sqlite> .schema samples_view
62# sqlite> .quit
63#
64# An example of using the database is provided by the script
65# exported-sql-viewer.py. Refer to that script for details.
66#
67# The database structure is practically the same as created by the script
68# export-to-postgresql.py. Refer to that script for details. A notable
69# difference is the 'transaction' column of the 'samples' table which is
70# renamed 'transaction_' in sqlite because 'transaction' is a reserved word.
71
72pyside_version_1 = True
73if not "pyside-version-1" in sys.argv:
74 try:
75 from PySide2.QtSql import *
76 pyside_version_1 = False
77 except:
78 pass
79
80if pyside_version_1:
81 from PySide.QtSql import *
82
83sys.path.append(os.environ['PERF_EXEC_PATH'] + \
84 '/scripts/python/Perf-Trace-Util/lib/Perf/Trace')
85
86# These perf imports are not used at present
87#from perf_trace_context import *
88#from Core import *
89
90perf_db_export_mode = True
91perf_db_export_calls = False
92perf_db_export_callchains = False
93
94def printerr(*args, **keyword_args):
95 print(*args, file=sys.stderr, **keyword_args)
96
97def printdate(*args, **kw_args):
98 print(datetime.datetime.today(), *args, sep=' ', **kw_args)
99
100def usage():
101 printerr("Usage is: export-to-sqlite.py <database name> [<columns>] [<calls>] [<callchains>] [<pyside-version-1>]");
102 printerr("where: columns 'all' or 'branches'");
103 printerr(" calls 'calls' => create calls and call_paths table");
104 printerr(" callchains 'callchains' => create call_paths table");
105 printerr(" pyside-version-1 'pyside-version-1' => use pyside version 1");
106 raise Exception("Too few or bad arguments")
107
108if (len(sys.argv) < 2):
109 usage()
110
111dbname = sys.argv[1]
112
113if (len(sys.argv) >= 3):
114 columns = sys.argv[2]
115else:
116 columns = "all"
117
118if columns not in ("all", "branches"):
119 usage()
120
121branches = (columns == "branches")
122
123for i in range(3,len(sys.argv)):
124 if (sys.argv[i] == "calls"):
125 perf_db_export_calls = True
126 elif (sys.argv[i] == "callchains"):
127 perf_db_export_callchains = True
128 elif (sys.argv[i] == "pyside-version-1"):
129 pass
130 else:
131 usage()
132
133def do_query(q, s):
134 if (q.exec_(s)):
135 return
136 raise Exception("Query failed: " + q.lastError().text())
137
138def do_query_(q):
139 if (q.exec_()):
140 return
141 raise Exception("Query failed: " + q.lastError().text())
142
143printdate("Creating database ...")
144
145db_exists = False
146try:
147 f = open(dbname)
148 f.close()
149 db_exists = True
150except:
151 pass
152
153if db_exists:
154 raise Exception(dbname + " already exists")
155
156db = QSqlDatabase.addDatabase('QSQLITE')
157db.setDatabaseName(dbname)
158db.open()
159
160query = QSqlQuery(db)
161
162do_query(query, 'PRAGMA journal_mode = OFF')
163do_query(query, 'BEGIN TRANSACTION')
164
165do_query(query, 'CREATE TABLE selected_events ('
166 'id integer NOT NULL PRIMARY KEY,'
167 'name varchar(80))')
168do_query(query, 'CREATE TABLE machines ('
169 'id integer NOT NULL PRIMARY KEY,'
170 'pid integer,'
171 'root_dir varchar(4096))')
172do_query(query, 'CREATE TABLE threads ('
173 'id integer NOT NULL PRIMARY KEY,'
174 'machine_id bigint,'
175 'process_id bigint,'
176 'pid integer,'
177 'tid integer)')
178do_query(query, 'CREATE TABLE comms ('
179 'id integer NOT NULL PRIMARY KEY,'
180 'comm varchar(16),'
181 'c_thread_id bigint,'
182 'c_time bigint,'
183 'exec_flag boolean)')
184do_query(query, 'CREATE TABLE comm_threads ('
185 'id integer NOT NULL PRIMARY KEY,'
186 'comm_id bigint,'
187 'thread_id bigint)')
188do_query(query, 'CREATE TABLE dsos ('
189 'id integer NOT NULL PRIMARY KEY,'
190 'machine_id bigint,'
191 'short_name varchar(256),'
192 'long_name varchar(4096),'
193 'build_id varchar(64))')
194do_query(query, 'CREATE TABLE symbols ('
195 'id integer NOT NULL PRIMARY KEY,'
196 'dso_id bigint,'
197 'sym_start bigint,'
198 'sym_end bigint,'
199 'binding integer,'
200 'name varchar(2048))')
201do_query(query, 'CREATE TABLE branch_types ('
202 'id integer NOT NULL PRIMARY KEY,'
203 'name varchar(80))')
204
205if branches:
206 do_query(query, 'CREATE TABLE samples ('
207 'id integer NOT NULL PRIMARY KEY,'
208 'evsel_id bigint,'
209 'machine_id bigint,'
210 'thread_id bigint,'
211 'comm_id bigint,'
212 'dso_id bigint,'
213 'symbol_id bigint,'
214 'sym_offset bigint,'
215 'ip bigint,'
216 'time bigint,'
217 'cpu integer,'
218 'to_dso_id bigint,'
219 'to_symbol_id bigint,'
220 'to_sym_offset bigint,'
221 'to_ip bigint,'
222 'branch_type integer,'
223 'in_tx boolean,'
224 'call_path_id bigint,'
225 'insn_count bigint,'
226 'cyc_count bigint)')
227else:
228 do_query(query, 'CREATE TABLE samples ('
229 'id integer NOT NULL PRIMARY KEY,'
230 'evsel_id bigint,'
231 'machine_id bigint,'
232 'thread_id bigint,'
233 'comm_id bigint,'
234 'dso_id bigint,'
235 'symbol_id bigint,'
236 'sym_offset bigint,'
237 'ip bigint,'
238 'time bigint,'
239 'cpu integer,'
240 'to_dso_id bigint,'
241 'to_symbol_id bigint,'
242 'to_sym_offset bigint,'
243 'to_ip bigint,'
244 'period bigint,'
245 'weight bigint,'
246 'transaction_ bigint,'
247 'data_src bigint,'
248 'branch_type integer,'
249 'in_tx boolean,'
250 'call_path_id bigint,'
251 'insn_count bigint,'
252 'cyc_count bigint)')
253
254if perf_db_export_calls or perf_db_export_callchains:
255 do_query(query, 'CREATE TABLE call_paths ('
256 'id integer NOT NULL PRIMARY KEY,'
257 'parent_id bigint,'
258 'symbol_id bigint,'
259 'ip bigint)')
260if perf_db_export_calls:
261 do_query(query, 'CREATE TABLE calls ('
262 'id integer NOT NULL PRIMARY KEY,'
263 'thread_id bigint,'
264 'comm_id bigint,'
265 'call_path_id bigint,'
266 'call_time bigint,'
267 'return_time bigint,'
268 'branch_count bigint,'
269 'call_id bigint,'
270 'return_id bigint,'
271 'parent_call_path_id bigint,'
272 'flags integer,'
273 'parent_id bigint,'
274 'insn_count bigint,'
275 'cyc_count bigint)')
276
277do_query(query, 'CREATE TABLE ptwrite ('
278 'id integer NOT NULL PRIMARY KEY,'
279 'payload bigint,'
280 'exact_ip integer)')
281
282do_query(query, 'CREATE TABLE cbr ('
283 'id integer NOT NULL PRIMARY KEY,'
284 'cbr integer,'
285 'mhz integer,'
286 'percent integer)')
287
288do_query(query, 'CREATE TABLE mwait ('
289 'id integer NOT NULL PRIMARY KEY,'
290 'hints integer,'
291 'extensions integer)')
292
293do_query(query, 'CREATE TABLE pwre ('
294 'id integer NOT NULL PRIMARY KEY,'
295 'cstate integer,'
296 'subcstate integer,'
297 'hw integer)')
298
299do_query(query, 'CREATE TABLE exstop ('
300 'id integer NOT NULL PRIMARY KEY,'
301 'exact_ip integer)')
302
303do_query(query, 'CREATE TABLE pwrx ('
304 'id integer NOT NULL PRIMARY KEY,'
305 'deepest_cstate integer,'
306 'last_cstate integer,'
307 'wake_reason integer)')
308
309do_query(query, 'CREATE TABLE context_switches ('
310 'id integer NOT NULL PRIMARY KEY,'
311 'machine_id bigint,'
312 'time bigint,'
313 'cpu integer,'
314 'thread_out_id bigint,'
315 'comm_out_id bigint,'
316 'thread_in_id bigint,'
317 'comm_in_id bigint,'
318 'flags integer)')
319
320# printf was added to sqlite in version 3.8.3
321sqlite_has_printf = False
322try:
323 do_query(query, 'SELECT printf("") FROM machines')
324 sqlite_has_printf = True
325except:
326 pass
327
328def emit_to_hex(x):
329 if sqlite_has_printf:
330 return 'printf("%x", ' + x + ')'
331 else:
332 return x
333
334do_query(query, 'CREATE VIEW machines_view AS '
335 'SELECT '
336 'id,'
337 'pid,'
338 'root_dir,'
339 'CASE WHEN id=0 THEN \'unknown\' WHEN pid=-1 THEN \'host\' ELSE \'guest\' END AS host_or_guest'
340 ' FROM machines')
341
342do_query(query, 'CREATE VIEW dsos_view AS '
343 'SELECT '
344 'id,'
345 'machine_id,'
346 '(SELECT host_or_guest FROM machines_view WHERE id = machine_id) AS host_or_guest,'
347 'short_name,'
348 'long_name,'
349 'build_id'
350 ' FROM dsos')
351
352do_query(query, 'CREATE VIEW symbols_view AS '
353 'SELECT '
354 'id,'
355 'name,'
356 '(SELECT short_name FROM dsos WHERE id=dso_id) AS dso,'
357 'dso_id,'
358 'sym_start,'
359 'sym_end,'
360 'CASE WHEN binding=0 THEN \'local\' WHEN binding=1 THEN \'global\' ELSE \'weak\' END AS binding'
361 ' FROM symbols')
362
363do_query(query, 'CREATE VIEW threads_view AS '
364 'SELECT '
365 'id,'
366 'machine_id,'
367 '(SELECT host_or_guest FROM machines_view WHERE id = machine_id) AS host_or_guest,'
368 'process_id,'
369 'pid,'
370 'tid'
371 ' FROM threads')
372
373do_query(query, 'CREATE VIEW comm_threads_view AS '
374 'SELECT '
375 'comm_id,'
376 '(SELECT comm FROM comms WHERE id = comm_id) AS command,'
377 'thread_id,'
378 '(SELECT pid FROM threads WHERE id = thread_id) AS pid,'
379 '(SELECT tid FROM threads WHERE id = thread_id) AS tid'
380 ' FROM comm_threads')
381
382if perf_db_export_calls or perf_db_export_callchains:
383 do_query(query, 'CREATE VIEW call_paths_view AS '
384 'SELECT '
385 'c.id,'
386 + emit_to_hex('c.ip') + ' AS ip,'
387 'c.symbol_id,'
388 '(SELECT name FROM symbols WHERE id = c.symbol_id) AS symbol,'
389 '(SELECT dso_id FROM symbols WHERE id = c.symbol_id) AS dso_id,'
390 '(SELECT dso FROM symbols_view WHERE id = c.symbol_id) AS dso_short_name,'
391 'c.parent_id,'
392 + emit_to_hex('p.ip') + ' AS parent_ip,'
393 'p.symbol_id AS parent_symbol_id,'
394 '(SELECT name FROM symbols WHERE id = p.symbol_id) AS parent_symbol,'
395 '(SELECT dso_id FROM symbols WHERE id = p.symbol_id) AS parent_dso_id,'
396 '(SELECT dso FROM symbols_view WHERE id = p.symbol_id) AS parent_dso_short_name'
397 ' FROM call_paths c INNER JOIN call_paths p ON p.id = c.parent_id')
398if perf_db_export_calls:
399 do_query(query, 'CREATE VIEW calls_view AS '
400 'SELECT '
401 'calls.id,'
402 'thread_id,'
403 '(SELECT pid FROM threads WHERE id = thread_id) AS pid,'
404 '(SELECT tid FROM threads WHERE id = thread_id) AS tid,'
405 '(SELECT comm FROM comms WHERE id = comm_id) AS command,'
406 'call_path_id,'
407 + emit_to_hex('ip') + ' AS ip,'
408 'symbol_id,'
409 '(SELECT name FROM symbols WHERE id = symbol_id) AS symbol,'
410 'call_time,'
411 'return_time,'
412 'return_time - call_time AS elapsed_time,'
413 'branch_count,'
414 'insn_count,'
415 'cyc_count,'
416 'CASE WHEN cyc_count=0 THEN CAST(0 AS FLOAT) ELSE ROUND(CAST(insn_count AS FLOAT) / cyc_count, 2) END AS IPC,'
417 'call_id,'
418 'return_id,'
419 'CASE WHEN flags=0 THEN \'\' WHEN flags=1 THEN \'no call\' WHEN flags=2 THEN \'no return\' WHEN flags=3 THEN \'no call/return\' WHEN flags=6 THEN \'jump\' ELSE flags END AS flags,'
420 'parent_call_path_id,'
421 'calls.parent_id'
422 ' FROM calls INNER JOIN call_paths ON call_paths.id = call_path_id')
423
424do_query(query, 'CREATE VIEW samples_view AS '
425 'SELECT '
426 'id,'
427 'time,'
428 'cpu,'
429 '(SELECT pid FROM threads WHERE id = thread_id) AS pid,'
430 '(SELECT tid FROM threads WHERE id = thread_id) AS tid,'
431 '(SELECT comm FROM comms WHERE id = comm_id) AS command,'
432 '(SELECT name FROM selected_events WHERE id = evsel_id) AS event,'
433 + emit_to_hex('ip') + ' AS ip_hex,'
434 '(SELECT name FROM symbols WHERE id = symbol_id) AS symbol,'
435 'sym_offset,'
436 '(SELECT short_name FROM dsos WHERE id = dso_id) AS dso_short_name,'
437 + emit_to_hex('to_ip') + ' AS to_ip_hex,'
438 '(SELECT name FROM symbols WHERE id = to_symbol_id) AS to_symbol,'
439 'to_sym_offset,'
440 '(SELECT short_name FROM dsos WHERE id = to_dso_id) AS to_dso_short_name,'
441 '(SELECT name FROM branch_types WHERE id = branch_type) AS branch_type_name,'
442 'in_tx,'
443 'insn_count,'
444 'cyc_count,'
445 'CASE WHEN cyc_count=0 THEN CAST(0 AS FLOAT) ELSE ROUND(CAST(insn_count AS FLOAT) / cyc_count, 2) END AS IPC'
446 ' FROM samples')
447
448do_query(query, 'CREATE VIEW ptwrite_view AS '
449 'SELECT '
450 'ptwrite.id,'
451 'time,'
452 'cpu,'
453 + emit_to_hex('payload') + ' AS payload_hex,'
454 'CASE WHEN exact_ip=0 THEN \'False\' ELSE \'True\' END AS exact_ip'
455 ' FROM ptwrite'
456 ' INNER JOIN samples ON samples.id = ptwrite.id')
457
458do_query(query, 'CREATE VIEW cbr_view AS '
459 'SELECT '
460 'cbr.id,'
461 'time,'
462 'cpu,'
463 'cbr,'
464 'mhz,'
465 'percent'
466 ' FROM cbr'
467 ' INNER JOIN samples ON samples.id = cbr.id')
468
469do_query(query, 'CREATE VIEW mwait_view AS '
470 'SELECT '
471 'mwait.id,'
472 'time,'
473 'cpu,'
474 + emit_to_hex('hints') + ' AS hints_hex,'
475 + emit_to_hex('extensions') + ' AS extensions_hex'
476 ' FROM mwait'
477 ' INNER JOIN samples ON samples.id = mwait.id')
478
479do_query(query, 'CREATE VIEW pwre_view AS '
480 'SELECT '
481 'pwre.id,'
482 'time,'
483 'cpu,'
484 'cstate,'
485 'subcstate,'
486 'CASE WHEN hw=0 THEN \'False\' ELSE \'True\' END AS hw'
487 ' FROM pwre'
488 ' INNER JOIN samples ON samples.id = pwre.id')
489
490do_query(query, 'CREATE VIEW exstop_view AS '
491 'SELECT '
492 'exstop.id,'
493 'time,'
494 'cpu,'
495 'CASE WHEN exact_ip=0 THEN \'False\' ELSE \'True\' END AS exact_ip'
496 ' FROM exstop'
497 ' INNER JOIN samples ON samples.id = exstop.id')
498
499do_query(query, 'CREATE VIEW pwrx_view AS '
500 'SELECT '
501 'pwrx.id,'
502 'time,'
503 'cpu,'
504 'deepest_cstate,'
505 'last_cstate,'
506 'CASE WHEN wake_reason=1 THEN \'Interrupt\''
507 ' WHEN wake_reason=2 THEN \'Timer Deadline\''
508 ' WHEN wake_reason=4 THEN \'Monitored Address\''
509 ' WHEN wake_reason=8 THEN \'HW\''
510 ' ELSE wake_reason '
511 'END AS wake_reason'
512 ' FROM pwrx'
513 ' INNER JOIN samples ON samples.id = pwrx.id')
514
515do_query(query, 'CREATE VIEW power_events_view AS '
516 'SELECT '
517 'samples.id,'
518 'time,'
519 'cpu,'
520 'selected_events.name AS event,'
521 'CASE WHEN selected_events.name=\'cbr\' THEN (SELECT cbr FROM cbr WHERE cbr.id = samples.id) ELSE "" END AS cbr,'
522 'CASE WHEN selected_events.name=\'cbr\' THEN (SELECT mhz FROM cbr WHERE cbr.id = samples.id) ELSE "" END AS mhz,'
523 'CASE WHEN selected_events.name=\'cbr\' THEN (SELECT percent FROM cbr WHERE cbr.id = samples.id) ELSE "" END AS percent,'
524 'CASE WHEN selected_events.name=\'mwait\' THEN (SELECT ' + emit_to_hex('hints') + ' FROM mwait WHERE mwait.id = samples.id) ELSE "" END AS hints_hex,'
525 'CASE WHEN selected_events.name=\'mwait\' THEN (SELECT ' + emit_to_hex('extensions') + ' FROM mwait WHERE mwait.id = samples.id) ELSE "" END AS extensions_hex,'
526 'CASE WHEN selected_events.name=\'pwre\' THEN (SELECT cstate FROM pwre WHERE pwre.id = samples.id) ELSE "" END AS cstate,'
527 'CASE WHEN selected_events.name=\'pwre\' THEN (SELECT subcstate FROM pwre WHERE pwre.id = samples.id) ELSE "" END AS subcstate,'
528 'CASE WHEN selected_events.name=\'pwre\' THEN (SELECT hw FROM pwre WHERE pwre.id = samples.id) ELSE "" END AS hw,'
529 'CASE WHEN selected_events.name=\'exstop\' THEN (SELECT exact_ip FROM exstop WHERE exstop.id = samples.id) ELSE "" END AS exact_ip,'
530 'CASE WHEN selected_events.name=\'pwrx\' THEN (SELECT deepest_cstate FROM pwrx WHERE pwrx.id = samples.id) ELSE "" END AS deepest_cstate,'
531 'CASE WHEN selected_events.name=\'pwrx\' THEN (SELECT last_cstate FROM pwrx WHERE pwrx.id = samples.id) ELSE "" END AS last_cstate,'
532 'CASE WHEN selected_events.name=\'pwrx\' THEN (SELECT '
533 'CASE WHEN wake_reason=1 THEN \'Interrupt\''
534 ' WHEN wake_reason=2 THEN \'Timer Deadline\''
535 ' WHEN wake_reason=4 THEN \'Monitored Address\''
536 ' WHEN wake_reason=8 THEN \'HW\''
537 ' ELSE wake_reason '
538 'END'
539 ' FROM pwrx WHERE pwrx.id = samples.id) ELSE "" END AS wake_reason'
540 ' FROM samples'
541 ' INNER JOIN selected_events ON selected_events.id = evsel_id'
542 ' WHERE selected_events.name IN (\'cbr\',\'mwait\',\'exstop\',\'pwre\',\'pwrx\')')
543
544do_query(query, 'CREATE VIEW context_switches_view AS '
545 'SELECT '
546 'context_switches.id,'
547 'context_switches.machine_id,'
548 'context_switches.time,'
549 'context_switches.cpu,'
550 'th_out.pid AS pid_out,'
551 'th_out.tid AS tid_out,'
552 'comm_out.comm AS comm_out,'
553 'th_in.pid AS pid_in,'
554 'th_in.tid AS tid_in,'
555 'comm_in.comm AS comm_in,'
556 'CASE WHEN context_switches.flags = 0 THEN \'in\''
557 ' WHEN context_switches.flags = 1 THEN \'out\''
558 ' WHEN context_switches.flags = 3 THEN \'out preempt\''
559 ' ELSE context_switches.flags '
560 'END AS flags'
561 ' FROM context_switches'
562 ' INNER JOIN threads AS th_out ON th_out.id = context_switches.thread_out_id'
563 ' INNER JOIN threads AS th_in ON th_in.id = context_switches.thread_in_id'
564 ' INNER JOIN comms AS comm_out ON comm_out.id = context_switches.comm_out_id'
565 ' INNER JOIN comms AS comm_in ON comm_in.id = context_switches.comm_in_id')
566
567do_query(query, 'END TRANSACTION')
568
569evsel_query = QSqlQuery(db)
570evsel_query.prepare("INSERT INTO selected_events VALUES (?, ?)")
571machine_query = QSqlQuery(db)
572machine_query.prepare("INSERT INTO machines VALUES (?, ?, ?)")
573thread_query = QSqlQuery(db)
574thread_query.prepare("INSERT INTO threads VALUES (?, ?, ?, ?, ?)")
575comm_query = QSqlQuery(db)
576comm_query.prepare("INSERT INTO comms VALUES (?, ?, ?, ?, ?)")
577comm_thread_query = QSqlQuery(db)
578comm_thread_query.prepare("INSERT INTO comm_threads VALUES (?, ?, ?)")
579dso_query = QSqlQuery(db)
580dso_query.prepare("INSERT INTO dsos VALUES (?, ?, ?, ?, ?)")
581symbol_query = QSqlQuery(db)
582symbol_query.prepare("INSERT INTO symbols VALUES (?, ?, ?, ?, ?, ?)")
583branch_type_query = QSqlQuery(db)
584branch_type_query.prepare("INSERT INTO branch_types VALUES (?, ?)")
585sample_query = QSqlQuery(db)
586if branches:
587 sample_query.prepare("INSERT INTO samples VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
588else:
589 sample_query.prepare("INSERT INTO samples VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
590if perf_db_export_calls or perf_db_export_callchains:
591 call_path_query = QSqlQuery(db)
592 call_path_query.prepare("INSERT INTO call_paths VALUES (?, ?, ?, ?)")
593if perf_db_export_calls:
594 call_query = QSqlQuery(db)
595 call_query.prepare("INSERT INTO calls VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
596ptwrite_query = QSqlQuery(db)
597ptwrite_query.prepare("INSERT INTO ptwrite VALUES (?, ?, ?)")
598cbr_query = QSqlQuery(db)
599cbr_query.prepare("INSERT INTO cbr VALUES (?, ?, ?, ?)")
600mwait_query = QSqlQuery(db)
601mwait_query.prepare("INSERT INTO mwait VALUES (?, ?, ?)")
602pwre_query = QSqlQuery(db)
603pwre_query.prepare("INSERT INTO pwre VALUES (?, ?, ?, ?)")
604exstop_query = QSqlQuery(db)
605exstop_query.prepare("INSERT INTO exstop VALUES (?, ?)")
606pwrx_query = QSqlQuery(db)
607pwrx_query.prepare("INSERT INTO pwrx VALUES (?, ?, ?, ?)")
608context_switch_query = QSqlQuery(db)
609context_switch_query.prepare("INSERT INTO context_switches VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)")
610
611def trace_begin():
612 printdate("Writing records...")
613 do_query(query, 'BEGIN TRANSACTION')
614 # id == 0 means unknown. It is easier to create records for them than replace the zeroes with NULLs
615 evsel_table(0, "unknown")
616 machine_table(0, 0, "unknown")
617 thread_table(0, 0, 0, -1, -1)
618 comm_table(0, "unknown", 0, 0, 0)
619 dso_table(0, 0, "unknown", "unknown", "")
620 symbol_table(0, 0, 0, 0, 0, "unknown")
621 sample_table(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
622 if perf_db_export_calls or perf_db_export_callchains:
623 call_path_table(0, 0, 0, 0)
624 call_return_table(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
625
626unhandled_count = 0
627
628def is_table_empty(table_name):
629 do_query(query, 'SELECT * FROM ' + table_name + ' LIMIT 1');
630 if query.next():
631 return False
632 return True
633
634def drop(table_name):
635 do_query(query, 'DROP VIEW ' + table_name + '_view');
636 do_query(query, 'DROP TABLE ' + table_name);
637
638def trace_end():
639 do_query(query, 'END TRANSACTION')
640
641 printdate("Adding indexes")
642 if perf_db_export_calls:
643 do_query(query, 'CREATE INDEX pcpid_idx ON calls (parent_call_path_id)')
644 do_query(query, 'CREATE INDEX pid_idx ON calls (parent_id)')
645 do_query(query, 'ALTER TABLE comms ADD has_calls boolean')
646 do_query(query, 'UPDATE comms SET has_calls = 1 WHERE comms.id IN (SELECT DISTINCT comm_id FROM calls)')
647
648 printdate("Dropping unused tables")
649 if is_table_empty("ptwrite"):
650 drop("ptwrite")
651 if is_table_empty("mwait") and is_table_empty("pwre") and is_table_empty("exstop") and is_table_empty("pwrx"):
652 do_query(query, 'DROP VIEW power_events_view');
653 drop("mwait")
654 drop("pwre")
655 drop("exstop")
656 drop("pwrx")
657 if is_table_empty("cbr"):
658 drop("cbr")
659 if is_table_empty("context_switches"):
660 drop("context_switches")
661
662 if (unhandled_count):
663 printdate("Warning: ", unhandled_count, " unhandled events")
664 printdate("Done")
665
666def trace_unhandled(event_name, context, event_fields_dict):
667 global unhandled_count
668 unhandled_count += 1
669
670def sched__sched_switch(*x):
671 pass
672
673def bind_exec(q, n, x):
674 for xx in x[0:n]:
675 q.addBindValue(str(xx))
676 do_query_(q)
677
678def evsel_table(*x):
679 bind_exec(evsel_query, 2, x)
680
681def machine_table(*x):
682 bind_exec(machine_query, 3, x)
683
684def thread_table(*x):
685 bind_exec(thread_query, 5, x)
686
687def comm_table(*x):
688 bind_exec(comm_query, 5, x)
689
690def comm_thread_table(*x):
691 bind_exec(comm_thread_query, 3, x)
692
693def dso_table(*x):
694 bind_exec(dso_query, 5, x)
695
696def symbol_table(*x):
697 bind_exec(symbol_query, 6, x)
698
699def branch_type_table(*x):
700 bind_exec(branch_type_query, 2, x)
701
702def sample_table(*x):
703 if branches:
704 for xx in x[0:15]:
705 sample_query.addBindValue(str(xx))
706 for xx in x[19:24]:
707 sample_query.addBindValue(str(xx))
708 do_query_(sample_query)
709 else:
710 bind_exec(sample_query, 24, x)
711
712def call_path_table(*x):
713 bind_exec(call_path_query, 4, x)
714
715def call_return_table(*x):
716 bind_exec(call_query, 14, x)
717
718def ptwrite(id, raw_buf):
719 data = struct.unpack_from("<IQ", raw_buf)
720 flags = data[0]
721 payload = data[1]
722 exact_ip = flags & 1
723 ptwrite_query.addBindValue(str(id))
724 ptwrite_query.addBindValue(str(payload))
725 ptwrite_query.addBindValue(str(exact_ip))
726 do_query_(ptwrite_query)
727
728def cbr(id, raw_buf):
729 data = struct.unpack_from("<BBBBII", raw_buf)
730 cbr = data[0]
731 MHz = (data[4] + 500) / 1000
732 percent = ((cbr * 1000 / data[2]) + 5) / 10
733 cbr_query.addBindValue(str(id))
734 cbr_query.addBindValue(str(cbr))
735 cbr_query.addBindValue(str(MHz))
736 cbr_query.addBindValue(str(percent))
737 do_query_(cbr_query)
738
739def mwait(id, raw_buf):
740 data = struct.unpack_from("<IQ", raw_buf)
741 payload = data[1]
742 hints = payload & 0xff
743 extensions = (payload >> 32) & 0x3
744 mwait_query.addBindValue(str(id))
745 mwait_query.addBindValue(str(hints))
746 mwait_query.addBindValue(str(extensions))
747 do_query_(mwait_query)
748
749def pwre(id, raw_buf):
750 data = struct.unpack_from("<IQ", raw_buf)
751 payload = data[1]
752 hw = (payload >> 7) & 1
753 cstate = (payload >> 12) & 0xf
754 subcstate = (payload >> 8) & 0xf
755 pwre_query.addBindValue(str(id))
756 pwre_query.addBindValue(str(cstate))
757 pwre_query.addBindValue(str(subcstate))
758 pwre_query.addBindValue(str(hw))
759 do_query_(pwre_query)
760
761def exstop(id, raw_buf):
762 data = struct.unpack_from("<I", raw_buf)
763 flags = data[0]
764 exact_ip = flags & 1
765 exstop_query.addBindValue(str(id))
766 exstop_query.addBindValue(str(exact_ip))
767 do_query_(exstop_query)
768
769def pwrx(id, raw_buf):
770 data = struct.unpack_from("<IQ", raw_buf)
771 payload = data[1]
772 deepest_cstate = payload & 0xf
773 last_cstate = (payload >> 4) & 0xf
774 wake_reason = (payload >> 8) & 0xf
775 pwrx_query.addBindValue(str(id))
776 pwrx_query.addBindValue(str(deepest_cstate))
777 pwrx_query.addBindValue(str(last_cstate))
778 pwrx_query.addBindValue(str(wake_reason))
779 do_query_(pwrx_query)
780
781def synth_data(id, config, raw_buf, *x):
782 if config == 0:
783 ptwrite(id, raw_buf)
784 elif config == 1:
785 mwait(id, raw_buf)
786 elif config == 2:
787 pwre(id, raw_buf)
788 elif config == 3:
789 exstop(id, raw_buf)
790 elif config == 4:
791 pwrx(id, raw_buf)
792 elif config == 5:
793 cbr(id, raw_buf)
794
795def context_switch_table(*x):
796 bind_exec(context_switch_query, 9, x)
1# export-to-sqlite.py: export perf data to a sqlite3 database
2# Copyright (c) 2017, Intel Corporation.
3#
4# This program is free software; you can redistribute it and/or modify it
5# under the terms and conditions of the GNU General Public License,
6# version 2, as published by the Free Software Foundation.
7#
8# This program is distributed in the hope it will be useful, but WITHOUT
9# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
10# FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
11# more details.
12
13from __future__ import print_function
14
15import os
16import sys
17import struct
18import datetime
19
20# To use this script you will need to have installed package python-pyside which
21# provides LGPL-licensed Python bindings for Qt. You will also need the package
22# libqt4-sql-sqlite for Qt sqlite3 support.
23#
24# Examples of installing pyside:
25#
26# ubuntu:
27#
28# $ sudo apt-get install python-pyside.qtsql libqt4-sql-psql
29#
30# Alternately, to use Python3 and/or pyside 2, one of the following:
31#
32# $ sudo apt-get install python3-pyside.qtsql libqt4-sql-psql
33# $ sudo apt-get install python-pyside2.qtsql libqt5sql5-psql
34# $ sudo apt-get install python3-pyside2.qtsql libqt5sql5-psql
35# fedora:
36#
37# $ sudo yum install python-pyside
38#
39# Alternately, to use Python3 and/or pyside 2, one of the following:
40# $ sudo yum install python3-pyside
41# $ pip install --user PySide2
42# $ pip3 install --user PySide2
43#
44# An example of using this script with Intel PT:
45#
46# $ perf record -e intel_pt//u ls
47# $ perf script -s ~/libexec/perf-core/scripts/python/export-to-sqlite.py pt_example branches calls
48# 2017-07-31 14:26:07.326913 Creating database...
49# 2017-07-31 14:26:07.538097 Writing records...
50# 2017-07-31 14:26:09.889292 Adding indexes
51# 2017-07-31 14:26:09.958746 Done
52#
53# To browse the database, sqlite3 can be used e.g.
54#
55# $ sqlite3 pt_example
56# sqlite> .header on
57# sqlite> select * from samples_view where id < 10;
58# sqlite> .mode column
59# sqlite> select * from samples_view where id < 10;
60# sqlite> .tables
61# sqlite> .schema samples_view
62# sqlite> .quit
63#
64# An example of using the database is provided by the script
65# exported-sql-viewer.py. Refer to that script for details.
66#
67# The database structure is practically the same as created by the script
68# export-to-postgresql.py. Refer to that script for details. A notable
69# difference is the 'transaction' column of the 'samples' table which is
70# renamed 'transaction_' in sqlite because 'transaction' is a reserved word.
71
72pyside_version_1 = True
73if not "pyside-version-1" in sys.argv:
74 try:
75 from PySide2.QtSql import *
76 pyside_version_1 = False
77 except:
78 pass
79
80if pyside_version_1:
81 from PySide.QtSql import *
82
83sys.path.append(os.environ['PERF_EXEC_PATH'] + \
84 '/scripts/python/Perf-Trace-Util/lib/Perf/Trace')
85
86# These perf imports are not used at present
87#from perf_trace_context import *
88#from Core import *
89
90perf_db_export_mode = True
91perf_db_export_calls = False
92perf_db_export_callchains = False
93
94def printerr(*args, **keyword_args):
95 print(*args, file=sys.stderr, **keyword_args)
96
97def printdate(*args, **kw_args):
98 print(datetime.datetime.today(), *args, sep=' ', **kw_args)
99
100def usage():
101 printerr("Usage is: export-to-sqlite.py <database name> [<columns>] [<calls>] [<callchains>] [<pyside-version-1>]");
102 printerr("where: columns 'all' or 'branches'");
103 printerr(" calls 'calls' => create calls and call_paths table");
104 printerr(" callchains 'callchains' => create call_paths table");
105 printerr(" pyside-version-1 'pyside-version-1' => use pyside version 1");
106 raise Exception("Too few or bad arguments")
107
108if (len(sys.argv) < 2):
109 usage()
110
111dbname = sys.argv[1]
112
113if (len(sys.argv) >= 3):
114 columns = sys.argv[2]
115else:
116 columns = "all"
117
118if columns not in ("all", "branches"):
119 usage()
120
121branches = (columns == "branches")
122
123for i in range(3,len(sys.argv)):
124 if (sys.argv[i] == "calls"):
125 perf_db_export_calls = True
126 elif (sys.argv[i] == "callchains"):
127 perf_db_export_callchains = True
128 elif (sys.argv[i] == "pyside-version-1"):
129 pass
130 else:
131 usage()
132
133def do_query(q, s):
134 if (q.exec_(s)):
135 return
136 raise Exception("Query failed: " + q.lastError().text())
137
138def do_query_(q):
139 if (q.exec_()):
140 return
141 raise Exception("Query failed: " + q.lastError().text())
142
143printdate("Creating database ...")
144
145db_exists = False
146try:
147 f = open(dbname)
148 f.close()
149 db_exists = True
150except:
151 pass
152
153if db_exists:
154 raise Exception(dbname + " already exists")
155
156db = QSqlDatabase.addDatabase('QSQLITE')
157db.setDatabaseName(dbname)
158db.open()
159
160query = QSqlQuery(db)
161
162do_query(query, 'PRAGMA journal_mode = OFF')
163do_query(query, 'BEGIN TRANSACTION')
164
165do_query(query, 'CREATE TABLE selected_events ('
166 'id integer NOT NULL PRIMARY KEY,'
167 'name varchar(80))')
168do_query(query, 'CREATE TABLE machines ('
169 'id integer NOT NULL PRIMARY KEY,'
170 'pid integer,'
171 'root_dir varchar(4096))')
172do_query(query, 'CREATE TABLE threads ('
173 'id integer NOT NULL PRIMARY KEY,'
174 'machine_id bigint,'
175 'process_id bigint,'
176 'pid integer,'
177 'tid integer)')
178do_query(query, 'CREATE TABLE comms ('
179 'id integer NOT NULL PRIMARY KEY,'
180 'comm varchar(16),'
181 'c_thread_id bigint,'
182 'c_time bigint,'
183 'exec_flag boolean)')
184do_query(query, 'CREATE TABLE comm_threads ('
185 'id integer NOT NULL PRIMARY KEY,'
186 'comm_id bigint,'
187 'thread_id bigint)')
188do_query(query, 'CREATE TABLE dsos ('
189 'id integer NOT NULL PRIMARY KEY,'
190 'machine_id bigint,'
191 'short_name varchar(256),'
192 'long_name varchar(4096),'
193 'build_id varchar(64))')
194do_query(query, 'CREATE TABLE symbols ('
195 'id integer NOT NULL PRIMARY KEY,'
196 'dso_id bigint,'
197 'sym_start bigint,'
198 'sym_end bigint,'
199 'binding integer,'
200 'name varchar(2048))')
201do_query(query, 'CREATE TABLE branch_types ('
202 'id integer NOT NULL PRIMARY KEY,'
203 'name varchar(80))')
204
205if branches:
206 do_query(query, 'CREATE TABLE samples ('
207 'id integer NOT NULL PRIMARY KEY,'
208 'evsel_id bigint,'
209 'machine_id bigint,'
210 'thread_id bigint,'
211 'comm_id bigint,'
212 'dso_id bigint,'
213 'symbol_id bigint,'
214 'sym_offset bigint,'
215 'ip bigint,'
216 'time bigint,'
217 'cpu integer,'
218 'to_dso_id bigint,'
219 'to_symbol_id bigint,'
220 'to_sym_offset bigint,'
221 'to_ip bigint,'
222 'branch_type integer,'
223 'in_tx boolean,'
224 'call_path_id bigint,'
225 'insn_count bigint,'
226 'cyc_count bigint,'
227 'flags integer)')
228else:
229 do_query(query, 'CREATE TABLE samples ('
230 'id integer NOT NULL PRIMARY KEY,'
231 'evsel_id bigint,'
232 'machine_id bigint,'
233 'thread_id bigint,'
234 'comm_id bigint,'
235 'dso_id bigint,'
236 'symbol_id bigint,'
237 'sym_offset bigint,'
238 'ip bigint,'
239 'time bigint,'
240 'cpu integer,'
241 'to_dso_id bigint,'
242 'to_symbol_id bigint,'
243 'to_sym_offset bigint,'
244 'to_ip bigint,'
245 'period bigint,'
246 'weight bigint,'
247 'transaction_ bigint,'
248 'data_src bigint,'
249 'branch_type integer,'
250 'in_tx boolean,'
251 'call_path_id bigint,'
252 'insn_count bigint,'
253 'cyc_count bigint,'
254 'flags integer)')
255
256if perf_db_export_calls or perf_db_export_callchains:
257 do_query(query, 'CREATE TABLE call_paths ('
258 'id integer NOT NULL PRIMARY KEY,'
259 'parent_id bigint,'
260 'symbol_id bigint,'
261 'ip bigint)')
262if perf_db_export_calls:
263 do_query(query, 'CREATE TABLE calls ('
264 'id integer NOT NULL PRIMARY KEY,'
265 'thread_id bigint,'
266 'comm_id bigint,'
267 'call_path_id bigint,'
268 'call_time bigint,'
269 'return_time bigint,'
270 'branch_count bigint,'
271 'call_id bigint,'
272 'return_id bigint,'
273 'parent_call_path_id bigint,'
274 'flags integer,'
275 'parent_id bigint,'
276 'insn_count bigint,'
277 'cyc_count bigint)')
278
279do_query(query, 'CREATE TABLE ptwrite ('
280 'id integer NOT NULL PRIMARY KEY,'
281 'payload bigint,'
282 'exact_ip integer)')
283
284do_query(query, 'CREATE TABLE cbr ('
285 'id integer NOT NULL PRIMARY KEY,'
286 'cbr integer,'
287 'mhz integer,'
288 'percent integer)')
289
290do_query(query, 'CREATE TABLE mwait ('
291 'id integer NOT NULL PRIMARY KEY,'
292 'hints integer,'
293 'extensions integer)')
294
295do_query(query, 'CREATE TABLE pwre ('
296 'id integer NOT NULL PRIMARY KEY,'
297 'cstate integer,'
298 'subcstate integer,'
299 'hw integer)')
300
301do_query(query, 'CREATE TABLE exstop ('
302 'id integer NOT NULL PRIMARY KEY,'
303 'exact_ip integer)')
304
305do_query(query, 'CREATE TABLE pwrx ('
306 'id integer NOT NULL PRIMARY KEY,'
307 'deepest_cstate integer,'
308 'last_cstate integer,'
309 'wake_reason integer)')
310
311do_query(query, 'CREATE TABLE context_switches ('
312 'id integer NOT NULL PRIMARY KEY,'
313 'machine_id bigint,'
314 'time bigint,'
315 'cpu integer,'
316 'thread_out_id bigint,'
317 'comm_out_id bigint,'
318 'thread_in_id bigint,'
319 'comm_in_id bigint,'
320 'flags integer)')
321
322# printf was added to sqlite in version 3.8.3
323sqlite_has_printf = False
324try:
325 do_query(query, 'SELECT printf("") FROM machines')
326 sqlite_has_printf = True
327except:
328 pass
329
330def emit_to_hex(x):
331 if sqlite_has_printf:
332 return 'printf("%x", ' + x + ')'
333 else:
334 return x
335
336do_query(query, 'CREATE VIEW machines_view AS '
337 'SELECT '
338 'id,'
339 'pid,'
340 'root_dir,'
341 'CASE WHEN id=0 THEN \'unknown\' WHEN pid=-1 THEN \'host\' ELSE \'guest\' END AS host_or_guest'
342 ' FROM machines')
343
344do_query(query, 'CREATE VIEW dsos_view AS '
345 'SELECT '
346 'id,'
347 'machine_id,'
348 '(SELECT host_or_guest FROM machines_view WHERE id = machine_id) AS host_or_guest,'
349 'short_name,'
350 'long_name,'
351 'build_id'
352 ' FROM dsos')
353
354do_query(query, 'CREATE VIEW symbols_view AS '
355 'SELECT '
356 'id,'
357 'name,'
358 '(SELECT short_name FROM dsos WHERE id=dso_id) AS dso,'
359 'dso_id,'
360 'sym_start,'
361 'sym_end,'
362 'CASE WHEN binding=0 THEN \'local\' WHEN binding=1 THEN \'global\' ELSE \'weak\' END AS binding'
363 ' FROM symbols')
364
365do_query(query, 'CREATE VIEW threads_view AS '
366 'SELECT '
367 'id,'
368 'machine_id,'
369 '(SELECT host_or_guest FROM machines_view WHERE id = machine_id) AS host_or_guest,'
370 'process_id,'
371 'pid,'
372 'tid'
373 ' FROM threads')
374
375do_query(query, 'CREATE VIEW comm_threads_view AS '
376 'SELECT '
377 'comm_id,'
378 '(SELECT comm FROM comms WHERE id = comm_id) AS command,'
379 'thread_id,'
380 '(SELECT pid FROM threads WHERE id = thread_id) AS pid,'
381 '(SELECT tid FROM threads WHERE id = thread_id) AS tid'
382 ' FROM comm_threads')
383
384if perf_db_export_calls or perf_db_export_callchains:
385 do_query(query, 'CREATE VIEW call_paths_view AS '
386 'SELECT '
387 'c.id,'
388 + emit_to_hex('c.ip') + ' AS ip,'
389 'c.symbol_id,'
390 '(SELECT name FROM symbols WHERE id = c.symbol_id) AS symbol,'
391 '(SELECT dso_id FROM symbols WHERE id = c.symbol_id) AS dso_id,'
392 '(SELECT dso FROM symbols_view WHERE id = c.symbol_id) AS dso_short_name,'
393 'c.parent_id,'
394 + emit_to_hex('p.ip') + ' AS parent_ip,'
395 'p.symbol_id AS parent_symbol_id,'
396 '(SELECT name FROM symbols WHERE id = p.symbol_id) AS parent_symbol,'
397 '(SELECT dso_id FROM symbols WHERE id = p.symbol_id) AS parent_dso_id,'
398 '(SELECT dso FROM symbols_view WHERE id = p.symbol_id) AS parent_dso_short_name'
399 ' FROM call_paths c INNER JOIN call_paths p ON p.id = c.parent_id')
400if perf_db_export_calls:
401 do_query(query, 'CREATE VIEW calls_view AS '
402 'SELECT '
403 'calls.id,'
404 'thread_id,'
405 '(SELECT pid FROM threads WHERE id = thread_id) AS pid,'
406 '(SELECT tid FROM threads WHERE id = thread_id) AS tid,'
407 '(SELECT comm FROM comms WHERE id = comm_id) AS command,'
408 'call_path_id,'
409 + emit_to_hex('ip') + ' AS ip,'
410 'symbol_id,'
411 '(SELECT name FROM symbols WHERE id = symbol_id) AS symbol,'
412 'call_time,'
413 'return_time,'
414 'return_time - call_time AS elapsed_time,'
415 'branch_count,'
416 'insn_count,'
417 'cyc_count,'
418 'CASE WHEN cyc_count=0 THEN CAST(0 AS FLOAT) ELSE ROUND(CAST(insn_count AS FLOAT) / cyc_count, 2) END AS IPC,'
419 'call_id,'
420 'return_id,'
421 'CASE WHEN flags=0 THEN \'\' WHEN flags=1 THEN \'no call\' WHEN flags=2 THEN \'no return\' WHEN flags=3 THEN \'no call/return\' WHEN flags=6 THEN \'jump\' ELSE flags END AS flags,'
422 'parent_call_path_id,'
423 'calls.parent_id'
424 ' FROM calls INNER JOIN call_paths ON call_paths.id = call_path_id')
425
426do_query(query, 'CREATE VIEW samples_view AS '
427 'SELECT '
428 'id,'
429 'time,'
430 'cpu,'
431 '(SELECT pid FROM threads WHERE id = thread_id) AS pid,'
432 '(SELECT tid FROM threads WHERE id = thread_id) AS tid,'
433 '(SELECT comm FROM comms WHERE id = comm_id) AS command,'
434 '(SELECT name FROM selected_events WHERE id = evsel_id) AS event,'
435 + emit_to_hex('ip') + ' AS ip_hex,'
436 '(SELECT name FROM symbols WHERE id = symbol_id) AS symbol,'
437 'sym_offset,'
438 '(SELECT short_name FROM dsos WHERE id = dso_id) AS dso_short_name,'
439 + emit_to_hex('to_ip') + ' AS to_ip_hex,'
440 '(SELECT name FROM symbols WHERE id = to_symbol_id) AS to_symbol,'
441 'to_sym_offset,'
442 '(SELECT short_name FROM dsos WHERE id = to_dso_id) AS to_dso_short_name,'
443 '(SELECT name FROM branch_types WHERE id = branch_type) AS branch_type_name,'
444 'in_tx,'
445 'insn_count,'
446 'cyc_count,'
447 'CASE WHEN cyc_count=0 THEN CAST(0 AS FLOAT) ELSE ROUND(CAST(insn_count AS FLOAT) / cyc_count, 2) END AS IPC,'
448 'flags'
449 ' FROM samples')
450
451do_query(query, 'CREATE VIEW ptwrite_view AS '
452 'SELECT '
453 'ptwrite.id,'
454 'time,'
455 'cpu,'
456 + emit_to_hex('payload') + ' AS payload_hex,'
457 'CASE WHEN exact_ip=0 THEN \'False\' ELSE \'True\' END AS exact_ip'
458 ' FROM ptwrite'
459 ' INNER JOIN samples ON samples.id = ptwrite.id')
460
461do_query(query, 'CREATE VIEW cbr_view AS '
462 'SELECT '
463 'cbr.id,'
464 'time,'
465 'cpu,'
466 'cbr,'
467 'mhz,'
468 'percent'
469 ' FROM cbr'
470 ' INNER JOIN samples ON samples.id = cbr.id')
471
472do_query(query, 'CREATE VIEW mwait_view AS '
473 'SELECT '
474 'mwait.id,'
475 'time,'
476 'cpu,'
477 + emit_to_hex('hints') + ' AS hints_hex,'
478 + emit_to_hex('extensions') + ' AS extensions_hex'
479 ' FROM mwait'
480 ' INNER JOIN samples ON samples.id = mwait.id')
481
482do_query(query, 'CREATE VIEW pwre_view AS '
483 'SELECT '
484 'pwre.id,'
485 'time,'
486 'cpu,'
487 'cstate,'
488 'subcstate,'
489 'CASE WHEN hw=0 THEN \'False\' ELSE \'True\' END AS hw'
490 ' FROM pwre'
491 ' INNER JOIN samples ON samples.id = pwre.id')
492
493do_query(query, 'CREATE VIEW exstop_view AS '
494 'SELECT '
495 'exstop.id,'
496 'time,'
497 'cpu,'
498 'CASE WHEN exact_ip=0 THEN \'False\' ELSE \'True\' END AS exact_ip'
499 ' FROM exstop'
500 ' INNER JOIN samples ON samples.id = exstop.id')
501
502do_query(query, 'CREATE VIEW pwrx_view AS '
503 'SELECT '
504 'pwrx.id,'
505 'time,'
506 'cpu,'
507 'deepest_cstate,'
508 'last_cstate,'
509 'CASE WHEN wake_reason=1 THEN \'Interrupt\''
510 ' WHEN wake_reason=2 THEN \'Timer Deadline\''
511 ' WHEN wake_reason=4 THEN \'Monitored Address\''
512 ' WHEN wake_reason=8 THEN \'HW\''
513 ' ELSE wake_reason '
514 'END AS wake_reason'
515 ' FROM pwrx'
516 ' INNER JOIN samples ON samples.id = pwrx.id')
517
518do_query(query, 'CREATE VIEW power_events_view AS '
519 'SELECT '
520 'samples.id,'
521 'time,'
522 'cpu,'
523 'selected_events.name AS event,'
524 'CASE WHEN selected_events.name=\'cbr\' THEN (SELECT cbr FROM cbr WHERE cbr.id = samples.id) ELSE "" END AS cbr,'
525 'CASE WHEN selected_events.name=\'cbr\' THEN (SELECT mhz FROM cbr WHERE cbr.id = samples.id) ELSE "" END AS mhz,'
526 'CASE WHEN selected_events.name=\'cbr\' THEN (SELECT percent FROM cbr WHERE cbr.id = samples.id) ELSE "" END AS percent,'
527 'CASE WHEN selected_events.name=\'mwait\' THEN (SELECT ' + emit_to_hex('hints') + ' FROM mwait WHERE mwait.id = samples.id) ELSE "" END AS hints_hex,'
528 'CASE WHEN selected_events.name=\'mwait\' THEN (SELECT ' + emit_to_hex('extensions') + ' FROM mwait WHERE mwait.id = samples.id) ELSE "" END AS extensions_hex,'
529 'CASE WHEN selected_events.name=\'pwre\' THEN (SELECT cstate FROM pwre WHERE pwre.id = samples.id) ELSE "" END AS cstate,'
530 'CASE WHEN selected_events.name=\'pwre\' THEN (SELECT subcstate FROM pwre WHERE pwre.id = samples.id) ELSE "" END AS subcstate,'
531 'CASE WHEN selected_events.name=\'pwre\' THEN (SELECT hw FROM pwre WHERE pwre.id = samples.id) ELSE "" END AS hw,'
532 'CASE WHEN selected_events.name=\'exstop\' THEN (SELECT exact_ip FROM exstop WHERE exstop.id = samples.id) ELSE "" END AS exact_ip,'
533 'CASE WHEN selected_events.name=\'pwrx\' THEN (SELECT deepest_cstate FROM pwrx WHERE pwrx.id = samples.id) ELSE "" END AS deepest_cstate,'
534 'CASE WHEN selected_events.name=\'pwrx\' THEN (SELECT last_cstate FROM pwrx WHERE pwrx.id = samples.id) ELSE "" END AS last_cstate,'
535 'CASE WHEN selected_events.name=\'pwrx\' THEN (SELECT '
536 'CASE WHEN wake_reason=1 THEN \'Interrupt\''
537 ' WHEN wake_reason=2 THEN \'Timer Deadline\''
538 ' WHEN wake_reason=4 THEN \'Monitored Address\''
539 ' WHEN wake_reason=8 THEN \'HW\''
540 ' ELSE wake_reason '
541 'END'
542 ' FROM pwrx WHERE pwrx.id = samples.id) ELSE "" END AS wake_reason'
543 ' FROM samples'
544 ' INNER JOIN selected_events ON selected_events.id = evsel_id'
545 ' WHERE selected_events.name IN (\'cbr\',\'mwait\',\'exstop\',\'pwre\',\'pwrx\')')
546
547do_query(query, 'CREATE VIEW context_switches_view AS '
548 'SELECT '
549 'context_switches.id,'
550 'context_switches.machine_id,'
551 'context_switches.time,'
552 'context_switches.cpu,'
553 'th_out.pid AS pid_out,'
554 'th_out.tid AS tid_out,'
555 'comm_out.comm AS comm_out,'
556 'th_in.pid AS pid_in,'
557 'th_in.tid AS tid_in,'
558 'comm_in.comm AS comm_in,'
559 'CASE WHEN context_switches.flags = 0 THEN \'in\''
560 ' WHEN context_switches.flags = 1 THEN \'out\''
561 ' WHEN context_switches.flags = 3 THEN \'out preempt\''
562 ' ELSE context_switches.flags '
563 'END AS flags'
564 ' FROM context_switches'
565 ' INNER JOIN threads AS th_out ON th_out.id = context_switches.thread_out_id'
566 ' INNER JOIN threads AS th_in ON th_in.id = context_switches.thread_in_id'
567 ' INNER JOIN comms AS comm_out ON comm_out.id = context_switches.comm_out_id'
568 ' INNER JOIN comms AS comm_in ON comm_in.id = context_switches.comm_in_id')
569
570do_query(query, 'END TRANSACTION')
571
572evsel_query = QSqlQuery(db)
573evsel_query.prepare("INSERT INTO selected_events VALUES (?, ?)")
574machine_query = QSqlQuery(db)
575machine_query.prepare("INSERT INTO machines VALUES (?, ?, ?)")
576thread_query = QSqlQuery(db)
577thread_query.prepare("INSERT INTO threads VALUES (?, ?, ?, ?, ?)")
578comm_query = QSqlQuery(db)
579comm_query.prepare("INSERT INTO comms VALUES (?, ?, ?, ?, ?)")
580comm_thread_query = QSqlQuery(db)
581comm_thread_query.prepare("INSERT INTO comm_threads VALUES (?, ?, ?)")
582dso_query = QSqlQuery(db)
583dso_query.prepare("INSERT INTO dsos VALUES (?, ?, ?, ?, ?)")
584symbol_query = QSqlQuery(db)
585symbol_query.prepare("INSERT INTO symbols VALUES (?, ?, ?, ?, ?, ?)")
586branch_type_query = QSqlQuery(db)
587branch_type_query.prepare("INSERT INTO branch_types VALUES (?, ?)")
588sample_query = QSqlQuery(db)
589if branches:
590 sample_query.prepare("INSERT INTO samples VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
591else:
592 sample_query.prepare("INSERT INTO samples VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
593if perf_db_export_calls or perf_db_export_callchains:
594 call_path_query = QSqlQuery(db)
595 call_path_query.prepare("INSERT INTO call_paths VALUES (?, ?, ?, ?)")
596if perf_db_export_calls:
597 call_query = QSqlQuery(db)
598 call_query.prepare("INSERT INTO calls VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
599ptwrite_query = QSqlQuery(db)
600ptwrite_query.prepare("INSERT INTO ptwrite VALUES (?, ?, ?)")
601cbr_query = QSqlQuery(db)
602cbr_query.prepare("INSERT INTO cbr VALUES (?, ?, ?, ?)")
603mwait_query = QSqlQuery(db)
604mwait_query.prepare("INSERT INTO mwait VALUES (?, ?, ?)")
605pwre_query = QSqlQuery(db)
606pwre_query.prepare("INSERT INTO pwre VALUES (?, ?, ?, ?)")
607exstop_query = QSqlQuery(db)
608exstop_query.prepare("INSERT INTO exstop VALUES (?, ?)")
609pwrx_query = QSqlQuery(db)
610pwrx_query.prepare("INSERT INTO pwrx VALUES (?, ?, ?, ?)")
611context_switch_query = QSqlQuery(db)
612context_switch_query.prepare("INSERT INTO context_switches VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)")
613
614def trace_begin():
615 printdate("Writing records...")
616 do_query(query, 'BEGIN TRANSACTION')
617 # id == 0 means unknown. It is easier to create records for them than replace the zeroes with NULLs
618 evsel_table(0, "unknown")
619 machine_table(0, 0, "unknown")
620 thread_table(0, 0, 0, -1, -1)
621 comm_table(0, "unknown", 0, 0, 0)
622 dso_table(0, 0, "unknown", "unknown", "")
623 symbol_table(0, 0, 0, 0, 0, "unknown")
624 sample_table(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
625 if perf_db_export_calls or perf_db_export_callchains:
626 call_path_table(0, 0, 0, 0)
627 call_return_table(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
628
629unhandled_count = 0
630
631def is_table_empty(table_name):
632 do_query(query, 'SELECT * FROM ' + table_name + ' LIMIT 1');
633 if query.next():
634 return False
635 return True
636
637def drop(table_name):
638 do_query(query, 'DROP VIEW ' + table_name + '_view');
639 do_query(query, 'DROP TABLE ' + table_name);
640
641def trace_end():
642 do_query(query, 'END TRANSACTION')
643
644 printdate("Adding indexes")
645 if perf_db_export_calls:
646 do_query(query, 'CREATE INDEX pcpid_idx ON calls (parent_call_path_id)')
647 do_query(query, 'CREATE INDEX pid_idx ON calls (parent_id)')
648 do_query(query, 'ALTER TABLE comms ADD has_calls boolean')
649 do_query(query, 'UPDATE comms SET has_calls = 1 WHERE comms.id IN (SELECT DISTINCT comm_id FROM calls)')
650
651 printdate("Dropping unused tables")
652 if is_table_empty("ptwrite"):
653 drop("ptwrite")
654 if is_table_empty("mwait") and is_table_empty("pwre") and is_table_empty("exstop") and is_table_empty("pwrx"):
655 do_query(query, 'DROP VIEW power_events_view');
656 drop("mwait")
657 drop("pwre")
658 drop("exstop")
659 drop("pwrx")
660 if is_table_empty("cbr"):
661 drop("cbr")
662 if is_table_empty("context_switches"):
663 drop("context_switches")
664
665 if (unhandled_count):
666 printdate("Warning: ", unhandled_count, " unhandled events")
667 printdate("Done")
668
669def trace_unhandled(event_name, context, event_fields_dict):
670 global unhandled_count
671 unhandled_count += 1
672
673def sched__sched_switch(*x):
674 pass
675
676def bind_exec(q, n, x):
677 for xx in x[0:n]:
678 q.addBindValue(str(xx))
679 do_query_(q)
680
681def evsel_table(*x):
682 bind_exec(evsel_query, 2, x)
683
684def machine_table(*x):
685 bind_exec(machine_query, 3, x)
686
687def thread_table(*x):
688 bind_exec(thread_query, 5, x)
689
690def comm_table(*x):
691 bind_exec(comm_query, 5, x)
692
693def comm_thread_table(*x):
694 bind_exec(comm_thread_query, 3, x)
695
696def dso_table(*x):
697 bind_exec(dso_query, 5, x)
698
699def symbol_table(*x):
700 bind_exec(symbol_query, 6, x)
701
702def branch_type_table(*x):
703 bind_exec(branch_type_query, 2, x)
704
705def sample_table(*x):
706 if branches:
707 for xx in x[0:15]:
708 sample_query.addBindValue(str(xx))
709 for xx in x[19:25]:
710 sample_query.addBindValue(str(xx))
711 do_query_(sample_query)
712 else:
713 bind_exec(sample_query, 25, x)
714
715def call_path_table(*x):
716 bind_exec(call_path_query, 4, x)
717
718def call_return_table(*x):
719 bind_exec(call_query, 14, x)
720
721def ptwrite(id, raw_buf):
722 data = struct.unpack_from("<IQ", raw_buf)
723 flags = data[0]
724 payload = data[1]
725 exact_ip = flags & 1
726 ptwrite_query.addBindValue(str(id))
727 ptwrite_query.addBindValue(str(payload))
728 ptwrite_query.addBindValue(str(exact_ip))
729 do_query_(ptwrite_query)
730
731def cbr(id, raw_buf):
732 data = struct.unpack_from("<BBBBII", raw_buf)
733 cbr = data[0]
734 MHz = (data[4] + 500) / 1000
735 percent = ((cbr * 1000 / data[2]) + 5) / 10
736 cbr_query.addBindValue(str(id))
737 cbr_query.addBindValue(str(cbr))
738 cbr_query.addBindValue(str(MHz))
739 cbr_query.addBindValue(str(percent))
740 do_query_(cbr_query)
741
742def mwait(id, raw_buf):
743 data = struct.unpack_from("<IQ", raw_buf)
744 payload = data[1]
745 hints = payload & 0xff
746 extensions = (payload >> 32) & 0x3
747 mwait_query.addBindValue(str(id))
748 mwait_query.addBindValue(str(hints))
749 mwait_query.addBindValue(str(extensions))
750 do_query_(mwait_query)
751
752def pwre(id, raw_buf):
753 data = struct.unpack_from("<IQ", raw_buf)
754 payload = data[1]
755 hw = (payload >> 7) & 1
756 cstate = (payload >> 12) & 0xf
757 subcstate = (payload >> 8) & 0xf
758 pwre_query.addBindValue(str(id))
759 pwre_query.addBindValue(str(cstate))
760 pwre_query.addBindValue(str(subcstate))
761 pwre_query.addBindValue(str(hw))
762 do_query_(pwre_query)
763
764def exstop(id, raw_buf):
765 data = struct.unpack_from("<I", raw_buf)
766 flags = data[0]
767 exact_ip = flags & 1
768 exstop_query.addBindValue(str(id))
769 exstop_query.addBindValue(str(exact_ip))
770 do_query_(exstop_query)
771
772def pwrx(id, raw_buf):
773 data = struct.unpack_from("<IQ", raw_buf)
774 payload = data[1]
775 deepest_cstate = payload & 0xf
776 last_cstate = (payload >> 4) & 0xf
777 wake_reason = (payload >> 8) & 0xf
778 pwrx_query.addBindValue(str(id))
779 pwrx_query.addBindValue(str(deepest_cstate))
780 pwrx_query.addBindValue(str(last_cstate))
781 pwrx_query.addBindValue(str(wake_reason))
782 do_query_(pwrx_query)
783
784def synth_data(id, config, raw_buf, *x):
785 if config == 0:
786 ptwrite(id, raw_buf)
787 elif config == 1:
788 mwait(id, raw_buf)
789 elif config == 2:
790 pwre(id, raw_buf)
791 elif config == 3:
792 exstop(id, raw_buf)
793 elif config == 4:
794 pwrx(id, raw_buf)
795 elif config == 5:
796 cbr(id, raw_buf)
797
798def context_switch_table(*x):
799 bind_exec(context_switch_query, 9, x)