Create new table or indexes to calculate relay and network download throughput average
For tpo/network-health/team#313, we should calculate relay and network download throughput average.
For instance this query takes too long if not limiting it (it isn't calculating averages yet):
select
SUBSTRING (circuit_path::json -> 1 ->> 0, '([0-9a-fA-F]+)') as fingerprint,
byte_info_total_bytes_recv::decimal / (time_info_usecs_to_last_byte_recv::decimal / 1000000) as stream_throughput
from onionperf_tgen_stream
inner join onionperf_tor_circuit
on onionperf_tgen_stream.onionperf_node=onionperf_tor_circuit.onionperf_node
where is_success=true
and byte_info_total_bytes_recv != -1
and SUBSTRING (circuit_path::json -> 1 ->> 0, '([0-9a-fA-F]+)') != ''
and onionperf_tgen_stream.onionperf_node = 'op-de8a'
limit 10;
Before optimizing indexes or creating a new table, we should decide the data we need.
-
The circuit middle relay, in the query it can be obtained by
SUBSTRING (circuit_path::json -> 1 ->> 0, '([0-9a-fA-F]+)')
fromonionperf_tor_circuit
table -
The throughput. I'm not sure it's calculated with either by:
-
byte_info_total_bytes_recv
(~5243015) andtime_info_usecs_to_last_byte_recv
or -
stream_info_recvsize
(always 5242880, when not -1) andelapsed_seconds_payload_bytes_recv::json->'5242880'
(same astime_info_usecs_to_last_byte_recv::decimal / 1000000
?)
If we create a new table, maybe the throughput could be stored by dividing bytes/seconds and we can assume bytes are always 5242880
-
-
the timestamp in which the measurement was done: i think either
unix_ts_start
orunix_ts_end
would work, but i'm not sure whether they should be took fromonionperf_tgen_stream
oronionperf_tor_circuit
tables and i don't know why the values are quite difference, eg., for fingerprint005F829917C13B46D2FF5DC3228DE442C67B4D50
withbyte_info_total_bytes_recv::decimal
= 100.7562730000000000, unix_ts_start from stream table is 2024-03-10 00:00:00 and unix_ts_start from circuit table is 2024-02-20 06:28:44.