Our database is melting down under heavy load

[bajtos]

Grafana dashboards are now taking ages to load. Queries executed by spark-api are gradually becoming slower and slower.

1. Setup monitoring to understand how DB performance changes over time
2. Identify opportunities for "quick win" performance improvements and apply them

Future improvements:

• Cache current round details to reduce the load on our DB server #140

[bajtos]

Todo:

• finish feat: report per-round retrieval stats to InfluxDB spark-evaluate#67
• rework all Grafana visualisations to source data from InfluxDB
• periodically delete old rounds & measurements from our DB - keep data for last 30 days

  DELETE FROM measurements WHERE finished_at < '2023-11-01T00:00:00.000Z';
  DELETE FROM spark_rounds WHERE created_at < '2023-11-01T00:00:00.000Z';

[bajtos]

periodically delete old rounds & measurements from our DB - keep data for last 30 days

This isn't urgent, I opened a follow-up issue to implement it later:

• Periodically purge historic data #154

[bajtos]

More optimisation opportunities:

• Create an index to support the query we use to visualise SPARK rounds

  SELECT created_at as time, id as round_number
  FROM spark_rounds
  WHERE "created_at" BETWEEN '2023-11-27T20:12:13.471Z' AND '2023-11-28T08:12:13.471Z'