The update job in meronense takes too long to finish
It seems the update script on meronense isn't finishing in the same amount of time it would need before. I am not sure if this is because of the complexity of the views that we have in the DB or the recent postgresql update.
One of the views that take several hours to load look like this:
ipv6servers=> \d+ ipv6servers
View "public.ipv6servers"
Column | Type | Collation | Nullable | Default | Storage | Description
------------------------------------+-------------+-----------+----------+---------+---------+-------------
valid_after_date | date | | | | plain |
server | server_enum | | | | plain |
guard_relay | boolean | | | | plain |
exit_relay | boolean | | | | plain |
announced_ipv6 | boolean | | | | plain |
exiting_ipv6_relay | boolean | | | | plain |
reachable_ipv6_relay | boolean | | | | plain |
server_count_sum_avg | numeric | | | | main |
advertised_bandwidth_bytes_sum_avg | numeric | | | | main |
View definition:
WITH included_statuses AS (
SELECT statuses.status_id,
statuses.server,
statuses.valid_after
FROM statuses
JOIN aggregated_ipv6 USING (status_id)
GROUP BY statuses.status_id, statuses.server, statuses.valid_after
HAVING statuses.running_count > 0 AND (1000 * sum(aggregated_ipv6.server_count_sum)) > (999 * statuses.running_count)
), included_dates AS (
SELECT date(included_statuses.valid_after) AS valid_after_date,
included_statuses.server
FROM included_statuses
GROUP BY (date(included_statuses.valid_after)), included_statuses.server
HAVING count(included_statuses.status_id) >= 12 AND date(included_statuses.valid_after) < (( SELECT max(date(included_statuses_1.valid_after)) AS max
FROM included_statuses included_statuses_1))
), grouped_by_status AS (
SELECT statuses.valid_after,
statuses.server,
CASE
WHEN statuses.server = 'relay'::server_enum THEN aggregated_ipv6.guard_relay
ELSE NULL::boolean
END AS guard_relay_or_null,
CASE
WHEN statuses.server = 'relay'::server_enum THEN aggregated_ipv6.exit_relay
ELSE NULL::boolean
END AS exit_relay_or_null,
aggregated_ipv6.announced_ipv6,
CASE
WHEN statuses.server = 'relay'::server_enum THEN aggregated_ipv6.exiting_ipv6_relay
ELSE NULL::boolean
END AS exiting_ipv6_relay_or_null,
CASE
WHEN statuses.server = 'relay'::server_enum THEN aggregated_ipv6.reachable_ipv6_relay
ELSE NULL::boolean
END AS reachable_ipv6_relay_or_null,
sum(aggregated_ipv6.server_count_sum) AS server_count_sum,
CASE
WHEN statuses.server = 'relay'::server_enum THEN sum(aggregated_ipv6.advertised_bandwidth_bytes_sum)
ELSE NULL::numeric
END AS advertised_bandwidth_bytes_sum
FROM statuses
JOIN aggregated_ipv6 USING (status_id)
WHERE (statuses.status_id IN ( SELECT included_statuses.status_id
FROM included_statuses)) AND (date(statuses.valid_after) IN ( SELECT included_dates.valid_after_date
FROM included_dates
WHERE included_dates.server = statuses.server))
GROUP BY statuses.status_id, statuses.valid_after, statuses.server, (
CASE
WHEN statuses.server = 'relay'::server_enum THEN aggregated_ipv6.guard_relay
ELSE NULL::boolean
END), (
CASE
WHEN statuses.server = 'relay'::server_enum THEN aggregated_ipv6.exit_relay
ELSE NULL::boolean
END), aggregated_ipv6.announced_ipv6, (
CASE
WHEN statuses.server = 'relay'::server_enum THEN aggregated_ipv6.exiting_ipv6_relay
ELSE NULL::boolean
END), (
CASE
WHEN statuses.server = 'relay'::server_enum THEN aggregated_ipv6.reachable_ipv6_relay
ELSE NULL::boolean
END)
)
SELECT date(grouped_by_status.valid_after) AS valid_after_date,
grouped_by_status.server,
grouped_by_status.guard_relay_or_null AS guard_relay,
grouped_by_status.exit_relay_or_null AS exit_relay,
grouped_by_status.announced_ipv6,
grouped_by_status.exiting_ipv6_relay_or_null AS exiting_ipv6_relay,
grouped_by_status.reachable_ipv6_relay_or_null AS reachable_ipv6_relay,
floor(avg(grouped_by_status.server_count_sum)) AS server_count_sum_avg,
floor(avg(grouped_by_status.advertised_bandwidth_bytes_sum)) AS advertised_bandwidth_bytes_sum_avg
FROM grouped_by_status
GROUP BY (date(grouped_by_status.valid_after)), grouped_by_status.server, grouped_by_status.guard_relay_or_null, grouped_by_status.exit_relay_or_null, grouped_by_status.announced_ipv6, grouped_by_status.exiting_ipv6_relay_or_null, grouped_by_status.reachable_ipv6_relay_or_null
ORDER BY (date(grouped_by_status.valid_after)), grouped_by_status.server, grouped_by_status.guard_relay_or_null, grouped_by_status.exit_relay_or_null, grouped_by_status.announced_ipv6, grouped_by_status.exiting_ipv6_relay_or_null, grouped_by_status.reachable_ipv6_relay_or_null;
The quick fix, is that we need to make this a materialized view and store the data instead of running it every time.
So the next step is going through all these view and materialize them, so they are stored into memory and the queries will be faster.
The next possible step could be using timescaledb on the website already. They have the continuous aggregates that can help us with updating these views https://docs.timescale.com/timescaledb/latest/how-to-guides/continuous-aggregates/about-continuous-aggregates/#about-continuous-aggregates
Edited by Hiro