Some ideas for viz pipeline

[gfr10598]

This query runs in a minute or two, with minimal resources. It generates 1/4 billion rows constituting a MinRTT histogram for each hour and each host.

SELECT
_partitiontime as pt,
timestamp_trunc(log_time, HOUR) as hour,
connection_spec.server_hostname as host,
ROUND(log(web100_log_entry.snap.MinRTT+1), 1) AS bucket,
round(avg(web100_log_entry.snap.MinRTT), 1) as avg,
COUNT(*) AS COUNT
FROM mlab-oti.base_tables.ndt
GROUP BY pt, hour, host, bucket

[gfr10598]

Note that this does require doing a separate query for each metric we want to summarize. For the case of hourly RTT at 1.25x spaced bins, this reduces the number of rows by only a factor of about 8.

Still, a couple minutes to summarize the entire history is great. We then get a table for each metric of interest, and I'm guessing further queries to aggregate those will be very fast.

[gfr10598]

Another query, grouping by hourofday, dayofweek, quarter, year, host.
This results in 38 million rows (but this is only by hostname, not by, e.g., ASN)
FWIW, there are about 167K histogram bins with more than 1000 samples.
There are about a million groups by year, quarter, dayofweek, hour, host. Since there are 1.9B rows, this means there are an average of about 1200 samples per group, which should provide pretty nice histograms (before breaking down by ASN).

select * from
(SELECT
extract(year from log_time) as year,
extract(quarter from log_time) as quarter,
extract(dayofweek from log_time) as weekday,
extract(hour from log_time) as hour,
connection_spec.server_hostname as host,
ROUND(log(web100_log_entry.snap.MinRTT+1), 1) AS bucket,
round(avg(web100_log_entry.snap.MinRTT), 1) as avg,
COUNT(*) AS num
FROM mlab-oti.base_tables.ndt
GROUP BY year, quarter, weekday, hour, host, bucket)

[gfr10598]

http://blog.shlomoid.com/2011/08/how-to-quickly-create-histogram-in.html

[gfr10598]

http://www.silota.com/docs/recipes/sql-histogram-summary-frequency-distribution.html