Coverage for procpath/sqliteviz.py: 100%
53 statements
« prev ^ index » next coverage.py v6.5.0, created at 2026-06-14 10:49 +0000
1import hashlib
2import http.server
3import io
4import json
5import logging
6import textwrap
7import zipfile
8from functools import partial
9from pathlib import Path
10from urllib.request import urlopen
12from . import procret
15__all__ = 'get_visualisation_bundle', 'install_sqliteviz', 'serve_dir', 'symlink_database'
17logger = logging.getLogger(__package__)
20def install_sqliteviz(zip_url: str, target_dir: Path):
21 with urlopen(zip_url) as response: (empty)procpath.test.cmd.TestExploreCommand.test_exploreprocpath.test.cmd.TestExploreCommand.test_explore_preload_databaseprocpath.test.cmd.TestExploreCommand.test_explore_preload_database_missing
22 with zipfile.ZipFile(io.BytesIO(response.read())) as z: (empty)procpath.test.cmd.TestExploreCommand.test_exploreprocpath.test.cmd.TestExploreCommand.test_explore_preload_databaseprocpath.test.cmd.TestExploreCommand.test_explore_preload_database_missing
23 z.extractall(target_dir) (empty)procpath.test.cmd.TestExploreCommand.test_exploreprocpath.test.cmd.TestExploreCommand.test_explore_preload_databaseprocpath.test.cmd.TestExploreCommand.test_explore_preload_database_missing
25 bundle = json.dumps(get_visualisation_bundle(), sort_keys=True) (empty)procpath.test.cmd.TestExploreCommand.test_exploreprocpath.test.cmd.TestExploreCommand.test_explore_preload_databaseprocpath.test.cmd.TestExploreCommand.test_explore_preload_database_missing
26 (target_dir / 'inquiries.json').write_text(bundle) (empty)procpath.test.cmd.TestExploreCommand.test_exploreprocpath.test.cmd.TestExploreCommand.test_explore_preload_databaseprocpath.test.cmd.TestExploreCommand.test_explore_preload_database_missing
29def _get_line_chart_config(title: str) -> dict:
30 return { (empty)procpath.test.cmd.TestExploreCommand.test_exploreprocpath.test.cmd.TestExploreCommand.test_explore_preload_databaseprocpath.test.cmd.TestExploreCommand.test_explore_preload_database_missingprocpath.test.unit.TestSqlitevizQuery.test_process_forest_timeline_graphprocpath.test.unit.TestSqlitevizQuery.test_process_timeline_cpuprocpath.test.unit.TestSqlitevizQuery.test_process_timeline_pidprocpath.test.unit.TestSqlitevizQuery.test_process_tree_sankey_indicesprocpath.test.unit.TestSqlitevizQuery.test_total_cpu_usageprocpath.test.unit.TestSqlitevizQuery.test_total_disk_ioprocpath.test.unit.TestSqlitevizQuery.test_total_memory_consumption
31 'data': [{
32 'meta': {'columnNames': {'x': 'ts', 'y': 'value'}},
33 'mode': 'lines',
34 'type': 'scatter',
35 'x': None,
36 'xsrc': 'ts',
37 'y': None,
38 'ysrc': 'value',
39 'transforms': [{
40 'groups': None,
41 'groupssrc': 'pid',
42 'meta': {'columnNames': {'groups': 'pid'}},
43 'styles': [],
44 'type': 'groupby',
45 }],
46 }],
47 'frames': [],
48 'layout': {
49 'autosize': True,
50 'title': {'text': title},
51 'xaxis': {
52 'autorange': True,
53 'range': [],
54 'type': 'date'
55 },
56 'yaxis': {
57 'autorange': True,
58 'range': [],
59 'type': 'linear'
60 },
61 },
62 }
65def _get_sqliteviz_only_charts():
66 return [ (empty)procpath.test.cmd.TestExploreCommand.test_exploreprocpath.test.cmd.TestExploreCommand.test_explore_preload_databaseprocpath.test.cmd.TestExploreCommand.test_explore_preload_database_missingprocpath.test.unit.TestSqlitevizQuery.test_process_forest_timeline_graphprocpath.test.unit.TestSqlitevizQuery.test_process_timeline_cpuprocpath.test.unit.TestSqlitevizQuery.test_process_timeline_pidprocpath.test.unit.TestSqlitevizQuery.test_process_tree_sankey_indicesprocpath.test.unit.TestSqlitevizQuery.test_total_cpu_usageprocpath.test.unit.TestSqlitevizQuery.test_total_disk_ioprocpath.test.unit.TestSqlitevizQuery.test_total_memory_consumption
67 # Process Timeline PID
68 {
69 'id': 'csfOTEpzlFfYz7OUc2aGI',
70 'createdAt': '2023-09-03T12:00:00Z',
71 'name': 'Process Timeline, PID',
72 'query': textwrap.dedent('''
73 WITH RECURSIVE tree(pid, ppid, pid_comm) AS (
74 SELECT stat_pid, stat_ppid, stat_pid || ' ' || stat_comm
75 FROM record
76 GROUP BY 1
77 UNION
78 SELECT pid, stat_ppid, stat_pid || ' ' || stat_comm
79 FROM record, tree
80 WHERE record.stat_pid = tree.ppid
81 ), lookup AS (
82 SELECT pid, group_concat(pid_comm, ' / ') path_to_root
83 FROM tree
84 GROUP BY 1
85 )
86 SELECT
87 ts * 1000 AS ts,
88 stat_pid,
89 stat_pid || ' ' || stat_comm AS pid_comm,
90 iif(
91 length(cmdline) > 0,
92 substr(cmdline, 0, 75) || iif(length(cmdline) > 75, '...', ''),
93 stat_comm
94 ) || '<br>' || path_to_root AS cmd
95 FROM record
96 JOIN lookup ON stat_pid = pid
97 ''').strip(),
98 'viewType': 'chart',
99 'viewOptions': {
100 'data': [{
101 'type': 'scattergl',
102 'mode': 'markers',
103 'meta': {'columnNames': {'x': 'ts', 'y': 'stat_pid', 'text': 'cmd'}},
104 'transforms': [{
105 'type': 'groupby',
106 'styles': [],
107 'meta': {'columnNames': {'groups': 'pid_comm'}},
108 'groups': None,
109 'groupssrc': 'pid_comm',
110 }],
111 'y': None,
112 'ysrc': 'stat_pid',
113 'x': None,
114 'xsrc': 'ts',
115 'text': None,
116 'textsrc': 'cmd',
117 'marker': {'size': 12, 'maxdisplayed': 0},
118 'line': {'width': 3},
119 'hoverinfo': 'x+text',
120 }],
121 'layout': {
122 'xaxis': {
123 'type': 'date',
124 'range': [],
125 'autorange': True,
126 },
127 'yaxis': {
128 'type': 'category',
129 'range': [],
130 'autorange': True,
131 'showticklabels': False,
132 },
133 'title': {'text': 'Process Timeline, PID'},
134 'hovermode': 'closest',
135 },
136 'frames': [],
137 },
138 },
139 # Process Timeline CPU
140 {
141 'id': '4PBtpi7inEAe-yjtRHCi0',
142 'createdAt': '2023-09-03T12:00:00Z',
143 'name': 'Process Timeline, CPU',
144 'query': textwrap.dedent('''
145 WITH RECURSIVE tree(pid, ppid, pid_comm) AS (
146 SELECT stat_pid, stat_ppid, stat_pid || ' ' || stat_comm
147 FROM record
148 GROUP BY 1
149 UNION
150 SELECT pid, stat_ppid, stat_pid || ' ' || stat_comm
151 FROM record, tree
152 WHERE record.stat_pid = tree.ppid
153 ), path_lookup AS (
154 SELECT pid, group_concat(pid_comm, ' / ') path_to_root
155 FROM tree
156 GROUP BY 1
157 ), cpu_diff AS (
158 SELECT
159 ts,
160 stat_pid,
161 stat_ppid,
162 stat_priority,
163 stat_comm,
164 cmdline,
165 stat_utime + stat_stime - LAG(stat_utime + stat_stime) OVER (
166 PARTITION BY stat_pid
167 ORDER BY record_id
168 ) tick_diff,
169 ts - LAG(ts) OVER (
170 PARTITION BY stat_pid
171 ORDER BY record_id
172 ) ts_diff
173 FROM record
174 ), record_ext AS (
175 SELECT
176 *,
177 100.0 * tick_diff / (
178 SELECT value FROM meta WHERE key = 'clock_ticks'
179 ) / ts_diff cpu_usage
180 FROM cpu_diff
181 WHERE tick_diff IS NOT NULL
182 )
183 SELECT
184 ts * 1000 AS ts,
185 stat_pid,
186 stat_pid || ' ' || stat_comm AS pid_comm,
187 power(1.02, -r.stat_priority) priority_size,
188 cpu_usage,
189 iif(
190 length(cmdline) > 0,
191 substr(cmdline, 0, 75) || iif(length(cmdline) > 75, '...', ''),
192 stat_comm
193 )
194 || '<br>' || path_to_root
195 || '<br>' || 'CPU, %: ' || printf('%.2f', cpu_usage)
196 || '<br>' || 'priority: ' || stat_priority AS cmd
197 FROM record_ext r
198 JOIN path_lookup p ON r.stat_pid = p.pid
199 -- Tune the following CPU usage inequality for a clearer figure
200 WHERE cpu_usage > 0
201 ''').strip(),
202 'viewType': 'chart',
203 'viewOptions': {
204 'data': [{
205 'type': 'scattergl',
206 'mode': 'markers',
207 'meta': {
208 'columnNames': {
209 'text': 'cmd',
210 'x': 'ts',
211 'y': 'stat_pid',
212 'marker': {
213 'color': 'cpu_usage',
214 'size': 'priority_size',
215 },
216 },
217 },
218 'y': None,
219 'ysrc': 'stat_pid',
220 'x': None,
221 'xsrc': 'ts',
222 'text': None,
223 'textsrc': 'cmd',
224 'marker': {
225 'maxdisplayed': 0,
226 'color': None,
227 'colorsrc': 'cpu_usage',
228 'size': None,
229 'sizesrc': 'priority_size',
230 'sizeref': 0.00667,
231 'sizemode': 'area',
232 'showscale': True,
233 'colorbar': {'title': {'text': 'CPU, %'}},
234 'line': {'width': 0},
235 },
236 'line': {'width': 3},
237 'hoverinfo': 'x+text',
238 }],
239 'layout': {
240 'xaxis': {
241 'type': 'date',
242 'range': [],
243 'autorange': True,
244 },
245 'yaxis': {
246 'type': 'category',
247 'range': [],
248 'autorange': True,
249 'showticklabels': False,
250 },
251 'title': {'text': 'Process Timeline, CPU'},
252 'hovermode': 'closest',
253 },
254 'frames': [],
255 },
256 },
257 # Process Tree
258 {
259 'id': '3XXe7a80GvD6Trk9FyXRz',
260 'name': 'Process Tree',
261 'createdAt': '2023-09-03T12:00:00Z',
262 'query': textwrap.dedent('''
263 WITH lookup(pid, num) AS (
264 SELECT stat_pid, ROW_NUMBER() OVER(ORDER BY stat_pid) - 1
265 FROM record
266 GROUP BY 1
267 ), nodes AS (
268 SELECT
269 stat_pid,
270 -- Opt-in for special bare column processing to prefer the
271 -- first values (the minimum value is not used per se)
272 MIN(ts),
273 stat_ppid,
274 stat_pid || ' ' || stat_comm AS pid_comm,
275 iif(
276 length(cmdline) > 0,
277 substr(cmdline, 0, 75) || iif(length(cmdline) > 75, '...', ''),
278 stat_comm
279 ) cmd
280 FROM record
281 GROUP BY 1
282 )
283 SELECT p.num p_num, pp.num pp_num, pid_comm, cmd, 1 value
284 FROM nodes
285 JOIN lookup p ON stat_pid = p.pid
286 LEFT JOIN lookup pp ON stat_ppid = pp.pid
287 ORDER BY p.num
288 ''').strip(),
289 'viewType': 'chart',
290 'viewOptions': {
291 'data': [
292 {
293 'type': 'sankey',
294 'mode': 'markers',
295 'node': {'labelsrc': 'pid_comm'},
296 'link': {
297 'valuesrc': 'value',
298 'targetsrc': 'p_num',
299 'sourcesrc': 'pp_num',
300 'labelsrc': 'cmd'
301 },
302 'meta': {
303 'columnNames': {
304 'node': {'label': 'pid_comm'},
305 'link': {
306 'source': 'pp_num',
307 'target': 'p_num',
308 'value': 'value',
309 'label': 'cmd'
310 }
311 }
312 },
313 'orientation': 'h',
314 'hoverinfo': 'name',
315 'arrangement': 'freeform'
316 }
317 ],
318 'layout': {
319 'xaxis': {'range': [], 'autorange': True},
320 'yaxis': {'range': [], 'autorange': True},
321 'autosize': True,
322 'title': {'text': 'Process Tree'}
323 },
324 'frames': []
325 }
326 },
327 # Process Forest Timeline Graph
328 {
329 'id': 'OXntoZ7rQmIoAXLlIk8h-',
330 'name': 'Process Forest Timeline',
331 'createdAt': '2023-09-03T12:00:00Z',
332 'query': textwrap.dedent('''
333 SELECT
334 json_object(
335 'object_type', 0,
336 'stat_pid', stat_pid,
337 'stat_ppid', stat_ppid,
338 'stat_comm', stat_comm,
339 'stat_starttime', MIN(stat_starttime),
340 'stat_num_threads', MAX(stat_num_threads),
341 'stat_priority', MIN(stat_priority),
342 'cmdline', cmdline,
343 'stat_utime_s', 1.0 * MAX(stat_utime)
344 / (SELECT value FROM meta WHERE key = 'clock_ticks'),
345 'stat_stime_s', 1.0 * MAX(stat_stime)
346 / (SELECT value FROM meta WHERE key = 'clock_ticks'),
347 'max_stat_rss_mib', CAST(printf('%.2f', MAX(stat_rss) / 1024.0 / 1024
348 * (SELECT value FROM meta WHERE key = 'page_size')) AS FLOAT),
349 'p50_stat_rss_mib', CAST(printf('%.2f', MEDIAN(stat_rss) / 1024.0 / 1024
350 * (SELECT value FROM meta WHERE key = 'page_size')) AS FLOAT),
351 'avg_stat_rss_mib', CAST(printf('%.2f', AVG(stat_rss) / 1024.0 / 1024
352 * (SELECT value FROM meta WHERE key = 'page_size')) AS FLOAT),
353 'avg_cpu_load_pct', IFNULL(100.0 * (MAX(stat_utime) + MAX(stat_stime))
354 / (SELECT value FROM meta WHERE key = 'clock_ticks')
355 / (MAX(ts) - MIN(ts)), 0),
356 'avg_io_wait_pct', IFNULL(100.0 * MAX(stat_delayacct_blkio_ticks)
357 / (SELECT value FROM meta WHERE key = 'clock_ticks')
358 / (MAX(ts) - MIN(ts)), 0),
359 'lifetime_s', CAST(printf('%.2f', MAX(ts) - MIN(ts)) AS FLOAT)
360 ) AS graph_object
361 FROM record
362 GROUP BY stat_pid
363 UNION ALL
364 SELECT
365 json_object(
366 'object_type', 1,
367 'source', stat_ppid,
368 'target', stat_pid
369 )
370 FROM record
371 GROUP BY stat_pid, stat_ppid
372 ''').strip(),
373 'viewType': 'graph',
374 'viewOptions': {
375 'layout': {
376 'options': {
377 'adjustSizes': False,
378 'barnesHutOptimize': True,
379 'barnesHutTheta': 0.5,
380 'edgeWeightInfluence': 0,
381 'gravity': 0.15,
382 'initialAlgorithm': 'circular',
383 'initialIterationsAmount': 100,
384 'linLogMode': False,
385 'outboundAttractionDistribution': False,
386 'scalingRatio': 10,
387 'slowDown': 5,
388 'strongGravityMode': True
389 },
390 'type': 'forceAtlas2',
391 },
392 'structure': {
393 'edgeSource': 'source',
394 'edgeTarget': 'target',
395 'nodeId': 'stat_pid',
396 'objectType': 'object_type',
397 },
398 'style': {
399 'backgroundColor': 'rgb(255, 255, 255)',
400 'edges': {
401 'color': {
402 'type': 'constant',
403 'value': '#a2b1c6',
404 },
405 'label': {
406 'color': '#a2b1c6',
407 'source': None,
408 },
409 'showDirection': True,
410 'size': {
411 'type': 'constant',
412 'value': 1,
413 },
414 },
415 'highlightMode': 'node_and_neighbors',
416 'nodes': {
417 'color': {
418 'colorscale': [
419 '#00224e', '#123570', '#3b496c', '#575d6d', '#707173', '#8a8678',
420 '#a59c74', '#c3b369', '#e1cc55', '#fee838'
421 ],
422 'colorscaleDirection': 'normal',
423 'mode': 'continious',
424 'opacity': 67,
425 'source': 'stat_starttime',
426 'sourceUsage': 'map_to',
427 'type': 'variable',
428 },
429 'label': {
430 'color': 'rgb(189, 117, 17)',
431 'source': 'stat_comm',
432 },
433 'size': {
434 'method': 'outDegree',
435 'min': 19,
436 'mode': 'area',
437 'scale': 1.1,
438 'type': 'calculated',
439 },
440 },
441 },
442 },
443 },
444 # Total Memory Consumption
445 {
446 'id': 'boSs15w7Endl5V9bABjXv',
447 'createdAt': '2023-09-03T12:00:00Z',
448 'name': 'Total Resident Set Size, MiB',
449 'query': textwrap.dedent('''
450 WITH downsampled_record AS (
451 SELECT
452 stat_pid,
453 -- Adjust downsampling factor
454 CAST(ts / 10 as INT) * 10 ts,
455 stat_comm,
456 cmdline,
457 MAX(stat_rss) stat_rss
458 FROM record
459 -- Adjust time range
460 -- WHERE ts BETWEEN
461 -- unixepoch('2025-01-20 00:00:00', 'utc')
462 -- AND unixepoch('2025-01-30 00:00:00', 'utc')
463 GROUP BY 1, 2
464 ), proc_group AS (
465 SELECT
466 -- Comment "stat_comm" group and uncomment this to have coarser grouping
467 -- CASE
468 -- WHEN cmdline LIKE '%firefox%' THEN '1. firefox'
469 -- WHEN cmdline LIKE '%chromium%' THEN '2. chromium'
470 -- ELSE '3. other'
471 -- END pgroup,
472 stat_comm pgroup,
473 ts,
474 SUM(stat_rss)
475 / 1024.0 / 1024 * (SELECT value FROM meta WHERE key = 'page_size') value
476 FROM downsampled_record
477 GROUP BY ts, 1
478 ORDER BY ts
479 ), proc_group_avg AS (
480 SELECT
481 ts,
482 pgroup,
483 AVG(value) OVER (
484 PARTITION BY pgroup
485 ORDER BY ts
486 -- Adjust centred moving average window
487 RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING
488 ) value
489 FROM proc_group
490 ), total_lookup(ts, total) AS (
491 SELECT ts, SUM(value)
492 FROM proc_group_avg
493 GROUP BY 1
494 )
495 SELECT
496 proc_group_avg.ts * 1000 ts,
497 pgroup,
498 value,
499 'total: ' || round(total, 1) || ' MiB' total
500 FROM proc_group_avg
501 JOIN total_lookup ON proc_group_avg.ts = total_lookup.ts
502 ORDER BY ts
503 ''').strip(),
504 'viewType': 'chart',
505 'viewOptions': {
506 'data': [{
507 'type': 'scatter',
508 'mode': 'lines',
509 'meta': {'columnNames': {'x': 'ts', 'y': 'value'}},
510 'transforms': [{
511 'type': 'groupby',
512 'groupssrc': 'pgroup',
513 'groups': None,
514 'styles': [],
515 'meta': {'columnNames': {'groups': 'pgroup'}},
516 }],
517 'stackgroup': 1,
518 'x': None,
519 'xsrc': 'ts',
520 'y': None,
521 'ysrc': 'value',
522 'text': None,
523 'textsrc': 'total',
524 'hoverinfo': 'x+text+name',
525 }],
526 'layout': {
527 'xaxis': {
528 'type': 'date',
529 'range': [],
530 'autorange': True,
531 },
532 'yaxis': {
533 'type': 'linear',
534 'range': [],
535 'autorange': True,
536 'separatethousands': True,
537 },
538 'title': {'text': 'Total Resident Set Size, MiB'},
539 'hovermode': 'closest',
540 },
541 'frames': []
542 },
543 },
544 # Total CPU Usage
545 {
546 'id': 'kd17-XGI85L2Oogj74Uyb',
547 'createdAt': '2023-09-03T12:00:00Z',
548 'name': 'Total CPU Usage, %',
549 'query': textwrap.dedent('''
550 WITH downsampled_record AS (
551 SELECT
552 stat_pid,
553 -- Adjust downsampling factor
554 CAST(ts / 10 as INT) * 10 ts,
555 stat_comm,
556 cmdline,
557 MAX(stat_utime) stat_utime,
558 MAX(stat_stime) stat_stime
559 FROM record
560 -- Adjust time range
561 -- WHERE ts BETWEEN
562 -- unixepoch('2025-01-20 00:00:00', 'utc')
563 -- AND unixepoch('2025-01-30 00:00:00', 'utc')
564 GROUP BY 1, 2
565 ), proc_cpu_diff AS (
566 SELECT
567 stat_pid,
568 ts,
569 stat_comm,
570 cmdline,
571 stat_utime + stat_stime - LAG(stat_utime + stat_stime) OVER (
572 PARTITION BY stat_pid
573 ORDER BY ts
574 ) tick_diff,
575 ts - LAG(ts) OVER (
576 PARTITION BY stat_pid
577 ORDER BY ts
578 ) ts_diff
579 FROM downsampled_record
580 ), proc_group AS (
581 SELECT
582 -- Comment "stat_comm" group and uncomment this to have coarser grouping
583 -- CASE
584 -- WHEN cmdline LIKE '%firefox%' THEN '1. firefox'
585 -- WHEN cmdline LIKE '%chromium%' THEN '2. chromium'
586 -- ELSE '3. other'
587 -- END pgroup,
588 stat_comm pgroup,
589 ts,
590 SUM(tick_diff) tick_diff,
591 AVG(ts_diff) ts_diff
592 FROM proc_cpu_diff
593 WHERE tick_diff IS NOT NULL
594 GROUP BY ts, 1
595 ORDER BY ts
596 ), proc_group_avg AS (
597 SELECT
598 ts,
599 pgroup,
600 AVG(
601 100.0
602 * tick_diff
603 / ts_diff
604 / (SELECT value FROM meta WHERE key = 'clock_ticks')
605 ) OVER (
606 PARTITION BY pgroup
607 ORDER BY ts
608 -- Adjust centred moving average window
609 RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING
610 ) value
611 FROM proc_group
612 ), total_lookup(ts, total) AS (
613 SELECT ts, SUM(value)
614 FROM proc_group_avg
615 GROUP BY 1
616 )
617 SELECT
618 proc_group_avg.ts * 1000 ts,
619 pgroup,
620 value,
621 'total: ' || round(total, 1) || ' %' total
622 FROM proc_group_avg
623 JOIN total_lookup ON proc_group_avg.ts = total_lookup.ts
624 ORDER BY ts
625 ''').strip(),
626 'viewType': 'chart',
627 'viewOptions': {
628 'data': [{
629 'type': 'scatter',
630 'mode': 'lines',
631 'meta': {'columnNames': {'x': 'ts', 'y': 'value'}},
632 'transforms': [{
633 'type': 'groupby',
634 'groupssrc': 'pgroup',
635 'groups': None,
636 'styles': [],
637 'meta': {'columnNames': {'groups': 'pgroup'}},
638 }],
639 'stackgroup': 1,
640 'x': None,
641 'xsrc': 'ts',
642 'y': None,
643 'ysrc': 'value',
644 'text': None,
645 'textsrc': 'total',
646 'hoverinfo': 'x+text+name',
647 }],
648 'layout': {
649 'xaxis': {
650 'type': 'date',
651 'range': [],
652 'autorange': True,
653 },
654 'yaxis': {
655 'type': 'linear',
656 'range': [],
657 'autorange': True,
658 'separatethousands': True,
659 },
660 'title': {'text': 'Total CPU Usage, %'},
661 'hovermode': 'closest',
662 },
663 'frames': []
664 },
665 },
666 # Total Disk IO
667 {
668 'id': 'ZXYEQObtemObLtygW731A',
669 'createdAt': '2023-09-03T12:00:00Z',
670 'name': 'Total Disk IO, B/s and % IO wait',
671 'query': textwrap.dedent('''
672 WITH downsampled_record AS (
673 SELECT
674 stat_pid,
675 -- Adjust downsampling factor
676 CAST(ts / 10 as INT) * 10 ts,
677 stat_comm,
678 cmdline,
679 MAX(io_read_bytes) io_read_bytes,
680 MAX(io_write_bytes) io_write_bytes,
681 MAX(stat_delayacct_blkio_ticks) stat_delayacct_blkio_ticks
682 FROM record
683 -- Adjust time range
684 -- WHERE ts BETWEEN
685 -- unixepoch('2025-01-20 00:00:00', 'utc')
686 -- AND unixepoch('2025-01-30 00:00:00', 'utc')
687 GROUP BY 1, 2
688 ), proc_io_diff AS (
689 SELECT
690 stat_pid,
691 ts,
692 stat_comm,
693 cmdline,
694 io_read_bytes - LAG(io_read_bytes) OVER (
695 PARTITION BY stat_pid
696 ORDER BY ts
697 ) rb,
698 io_write_bytes - LAG(io_write_bytes) OVER (
699 PARTITION BY stat_pid
700 ORDER BY ts
701 ) wb,
702 stat_delayacct_blkio_ticks - LAG(stat_delayacct_blkio_ticks) OVER (
703 PARTITION BY stat_pid
704 ORDER BY ts
705 ) tick_diff,
706 ts - LAG(ts) OVER (
707 PARTITION BY stat_pid
708 ORDER BY ts
709 ) ts_diff
710 FROM downsampled_record
711 ), proc_group AS (
712 SELECT
713 -- Comment "stat_comm" group and uncomment this to have coarser grouping
714 -- CASE
715 -- WHEN cmdline LIKE '%firefox%' THEN '1. firefox'
716 -- WHEN cmdline LIKE '%chromium%' THEN '2. chromium'
717 -- ELSE '3. other'
718 -- END pgroup,
719 stat_comm pgroup,
720 ts,
721 SUM(rb) rb,
722 SUM(wb) wb,
723 SUM(tick_diff) tick_diff,
724 AVG(ts_diff) ts_diff
725 FROM proc_io_diff
726 WHERE tick_diff IS NOT NULL
727 GROUP BY ts, 1
728 ORDER BY ts
729 ), proc_group_avg AS (
730 SELECT
731 ts,
732 pgroup,
733 AVG(rb / ts_diff) OVER (
734 PARTITION BY pgroup
735 ORDER BY ts
736 -- Adjust centred moving average window
737 RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING
738 ) rbs,
739 AVG(-wb / ts_diff) OVER (
740 PARTITION BY pgroup
741 ORDER BY ts
742 -- Adjust centred moving average window
743 RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING
744 ) wbs,
745 AVG(
746 100.0
747 * tick_diff
748 / ts_diff
749 / (SELECT value FROM meta WHERE key = 'clock_ticks')
750 ) OVER (
751 PARTITION BY pgroup
752 ORDER BY ts
753 -- Adjust centred moving average window
754 RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING
755 ) iowait
756 FROM proc_group
757 )
758 SELECT
759 proc_group_avg.ts * 1000 ts,
760 pgroup,
761 rbs,
762 wbs,
763 iowait
764 FROM proc_group_avg
765 ORDER BY ts
766 ''').strip(),
767 'viewType': 'chart',
768 'viewOptions': {
769 'data': [
770 {
771 'type': 'scatter',
772 'mode': 'lines',
773 'name': 'read',
774 'meta': {'columnNames': {'x': 'ts', 'y': 'rbs'}},
775 'transforms': [{
776 'type': 'groupby',
777 'groupssrc': 'pgroup',
778 'groups': None,
779 'styles': [],
780 'meta': {'columnNames': {'groups': 'pgroup'}},
781 }],
782 'stackgroup': 1,
783 'x': None,
784 'xsrc': 'ts',
785 'y': None,
786 'ysrc': 'rbs',
787 },
788 {
789 'type': 'scatter',
790 'mode': 'lines',
791 'name': 'write',
792 'meta': {'columnNames': {'x': 'ts', 'y': 'wbs'}},
793 'transforms': [{
794 'type': 'groupby',
795 'groupssrc': 'pgroup',
796 'groups': None,
797 'styles': [],
798 'meta': {'columnNames': {'groups': 'pgroup'}},
799 }],
800 'stackgroup': 2,
801 'x': None,
802 'xsrc': 'ts',
803 'y': None,
804 'ysrc': 'wbs',
805 },
806 {
807 'type': 'scatter',
808 'mode': 'lines',
809 'name': 'iowait',
810 'meta': {'columnNames': {'x': 'ts', 'y': 'iowait'}},
811 'transforms': [{
812 'type': 'groupby',
813 'groupssrc': 'pgroup',
814 'groups': None,
815 'styles': [],
816 'meta': {'columnNames': {'groups': 'pgroup'}},
817 }],
818 'x': None,
819 'xsrc': 'ts',
820 'y': None,
821 'ysrc': 'iowait',
822 'yaxis': 'y2',
823 'line': {'dash': 'dot'},
824 },
825 ],
826 'layout': {
827 'xaxis': {
828 'type': 'date',
829 'range': [],
830 'autorange': True,
831 'domain': [0, 0.96],
832 },
833 'yaxis': {
834 'type': 'linear',
835 'range': [],
836 'autorange': True,
837 'separatethousands': True,
838 'title': {'text': '-write|+read byte/second'},
839 },
840 'yaxis2': {
841 'side': 'right',
842 'overlaying': 'y',
843 'type': 'linear',
844 'range': [0, 100],
845 'autorange': False,
846 'title': {'text': 'IO wait, %'},
847 },
848 'title': {'text': 'Total Disk IO, throughput and IO wait'},
849 'hovermode': 'closest',
850 'legend': {'traceorder': 'normal'},
851 },
852 'frames': [],
853 },
854 },
855 ]
858def get_visualisation_bundle() -> dict:
859 """Get Sqliteviz import-able visualisation bundle."""
861 inquiries = [] (empty)procpath.test.cmd.TestExploreCommand.test_exploreprocpath.test.cmd.TestExploreCommand.test_explore_preload_databaseprocpath.test.cmd.TestExploreCommand.test_explore_preload_database_missingprocpath.test.unit.TestSqlitevizQuery.test_process_forest_timeline_graphprocpath.test.unit.TestSqlitevizQuery.test_process_timeline_cpuprocpath.test.unit.TestSqlitevizQuery.test_process_timeline_pidprocpath.test.unit.TestSqlitevizQuery.test_process_tree_sankey_indicesprocpath.test.unit.TestSqlitevizQuery.test_total_cpu_usageprocpath.test.unit.TestSqlitevizQuery.test_total_disk_ioprocpath.test.unit.TestSqlitevizQuery.test_total_memory_consumption
862 result = {'version': 4, 'inquiries': inquiries} (empty)procpath.test.cmd.TestExploreCommand.test_exploreprocpath.test.cmd.TestExploreCommand.test_explore_preload_databaseprocpath.test.cmd.TestExploreCommand.test_explore_preload_database_missingprocpath.test.unit.TestSqlitevizQuery.test_process_forest_timeline_graphprocpath.test.unit.TestSqlitevizQuery.test_process_timeline_cpuprocpath.test.unit.TestSqlitevizQuery.test_process_timeline_pidprocpath.test.unit.TestSqlitevizQuery.test_process_tree_sankey_indicesprocpath.test.unit.TestSqlitevizQuery.test_total_cpu_usageprocpath.test.unit.TestSqlitevizQuery.test_total_disk_ioprocpath.test.unit.TestSqlitevizQuery.test_total_memory_consumption
864 for query in procret.registry.values(): (empty)procpath.test.cmd.TestExploreCommand.test_exploreprocpath.test.cmd.TestExploreCommand.test_explore_preload_databaseprocpath.test.cmd.TestExploreCommand.test_explore_preload_database_missingprocpath.test.unit.TestSqlitevizQuery.test_process_forest_timeline_graphprocpath.test.unit.TestSqlitevizQuery.test_process_timeline_cpuprocpath.test.unit.TestSqlitevizQuery.test_process_timeline_pidprocpath.test.unit.TestSqlitevizQuery.test_process_tree_sankey_indicesprocpath.test.unit.TestSqlitevizQuery.test_total_cpu_usageprocpath.test.unit.TestSqlitevizQuery.test_total_disk_ioprocpath.test.unit.TestSqlitevizQuery.test_total_memory_consumption
865 query_text = query.get_short_query(ts_as_milliseconds=True) (empty)procpath.test.cmd.TestExploreCommand.test_exploreprocpath.test.cmd.TestExploreCommand.test_explore_preload_databaseprocpath.test.cmd.TestExploreCommand.test_explore_preload_database_missingprocpath.test.unit.TestSqlitevizQuery.test_process_forest_timeline_graphprocpath.test.unit.TestSqlitevizQuery.test_process_timeline_cpuprocpath.test.unit.TestSqlitevizQuery.test_process_timeline_pidprocpath.test.unit.TestSqlitevizQuery.test_process_tree_sankey_indicesprocpath.test.unit.TestSqlitevizQuery.test_total_cpu_usageprocpath.test.unit.TestSqlitevizQuery.test_total_disk_ioprocpath.test.unit.TestSqlitevizQuery.test_total_memory_consumption
866 inquiries.append({ (empty)procpath.test.cmd.TestExploreCommand.test_exploreprocpath.test.cmd.TestExploreCommand.test_explore_preload_databaseprocpath.test.cmd.TestExploreCommand.test_explore_preload_database_missingprocpath.test.unit.TestSqlitevizQuery.test_process_forest_timeline_graphprocpath.test.unit.TestSqlitevizQuery.test_process_timeline_cpuprocpath.test.unit.TestSqlitevizQuery.test_process_timeline_pidprocpath.test.unit.TestSqlitevizQuery.test_process_tree_sankey_indicesprocpath.test.unit.TestSqlitevizQuery.test_total_cpu_usageprocpath.test.unit.TestSqlitevizQuery.test_total_disk_ioprocpath.test.unit.TestSqlitevizQuery.test_total_memory_consumption
867 'id': hashlib.md5(query_text.encode()).hexdigest()[:21],
868 'createdAt': '2023-09-03T12:00:00Z',
869 'name': query.title,
870 'query': textwrap.dedent(query_text).strip(),
871 'viewType': 'chart',
872 'viewOptions': _get_line_chart_config(query.title),
873 })
875 inquiries.extend(_get_sqliteviz_only_charts()) (empty)procpath.test.cmd.TestExploreCommand.test_exploreprocpath.test.cmd.TestExploreCommand.test_explore_preload_databaseprocpath.test.cmd.TestExploreCommand.test_explore_preload_database_missingprocpath.test.unit.TestSqlitevizQuery.test_process_forest_timeline_graphprocpath.test.unit.TestSqlitevizQuery.test_process_timeline_cpuprocpath.test.unit.TestSqlitevizQuery.test_process_timeline_pidprocpath.test.unit.TestSqlitevizQuery.test_process_tree_sankey_indicesprocpath.test.unit.TestSqlitevizQuery.test_total_cpu_usageprocpath.test.unit.TestSqlitevizQuery.test_total_disk_ioprocpath.test.unit.TestSqlitevizQuery.test_total_memory_consumption
877 return result (empty)procpath.test.cmd.TestExploreCommand.test_exploreprocpath.test.cmd.TestExploreCommand.test_explore_preload_databaseprocpath.test.cmd.TestExploreCommand.test_explore_preload_database_missingprocpath.test.unit.TestSqlitevizQuery.test_process_forest_timeline_graphprocpath.test.unit.TestSqlitevizQuery.test_process_timeline_cpuprocpath.test.unit.TestSqlitevizQuery.test_process_timeline_pidprocpath.test.unit.TestSqlitevizQuery.test_process_tree_sankey_indicesprocpath.test.unit.TestSqlitevizQuery.test_total_cpu_usageprocpath.test.unit.TestSqlitevizQuery.test_total_disk_ioprocpath.test.unit.TestSqlitevizQuery.test_total_memory_consumption
880class HttpRequestHandler(http.server.SimpleHTTPRequestHandler):
881 def send_head(self):
882 # Disable cache validation based on modified timestamp of the
883 # file because it's a symlink pointing to different files, and
884 # next one can easily be older than current one
885 if self.path == '/db.sqlite':
886 del self.headers['If-Modified-Since']
888 return super().send_head()
890 def end_headers(self):
891 if self.path == '/db.sqlite':
892 # The "no-store" response directive indicates that caches
893 # should not store this response. No point to try to cache
894 # big database files
895 self.send_header('Cache-Control', 'no-store')
896 else:
897 # The "no-cache" response directive indicates that the
898 # response can be stored in caches, but the response must
899 # be validated with the origin server before each reuse
900 self.send_header('Cache-Control', 'no-cache')
902 super().end_headers()
905def serve_dir(
906 bind: str, port: int, directory: str, *, server_cls=http.server.ThreadingHTTPServer
907):
908 handler_cls = partial(HttpRequestHandler, directory=directory)
909 with server_cls((bind, port), handler_cls) as httpd:
910 httpd.serve_forever()
913def symlink_database(database_file: str, sqliteviz_dir: Path) -> Path:
914 db_path = Path(database_file).absolute() procpath.test.cmd.TestExploreCommand.test_explore_preload_databaseprocpath.test.cmd.TestExploreCommand.test_explore_preload_database_missingprocpath.test.cmd.TestExploreCommand.test_explore_serveprocpath.test.cmd.TestExploreCommand.test_symlink_database
915 if not db_path.exists(): procpath.test.cmd.TestExploreCommand.test_explore_preload_databaseprocpath.test.cmd.TestExploreCommand.test_explore_preload_database_missingprocpath.test.cmd.TestExploreCommand.test_explore_serveprocpath.test.cmd.TestExploreCommand.test_symlink_database
916 raise FileNotFoundError procpath.test.cmd.TestExploreCommand.test_explore_preload_database_missing
918 sym_path = sqliteviz_dir / 'db.sqlite' procpath.test.cmd.TestExploreCommand.test_explore_preload_databaseprocpath.test.cmd.TestExploreCommand.test_explore_serveprocpath.test.cmd.TestExploreCommand.test_symlink_database
919 sym_path.unlink(missing_ok=True) procpath.test.cmd.TestExploreCommand.test_explore_preload_databaseprocpath.test.cmd.TestExploreCommand.test_explore_serveprocpath.test.cmd.TestExploreCommand.test_symlink_database
920 sym_path.symlink_to(db_path) procpath.test.cmd.TestExploreCommand.test_explore_preload_databaseprocpath.test.cmd.TestExploreCommand.test_explore_serveprocpath.test.cmd.TestExploreCommand.test_symlink_database
921 return sym_path procpath.test.cmd.TestExploreCommand.test_explore_preload_databaseprocpath.test.cmd.TestExploreCommand.test_explore_serveprocpath.test.cmd.TestExploreCommand.test_symlink_database