DB for data related to continuous benchmarks

This document describes the setup and administration of the database which stores data related to continuous benchmarks. Each benchmark, for instance ft transfer throughput, has its own table storing data like measured throughput and metadata related to the execution of the benchmark.

Data store selection

The data collected for continuous benchmarks shall be displayed in Grafana dashboards to visualize how nearcore performance metrics evolve. Data is pushed after benchmark execution and therefore stored in a SQL database. In particular, we use a PostgreSQL database since there is prior knowledge about adding them to Grafana as a data source.

Cloud SQL set up and Grafana integration

We set up the Cloud SQL instance crt-benchmark in the nearone-crt project. Within that instance data is stored in the benchmarks table.

Network

In Grafana the database is available as data source crt_benchmarks. Since the database does not contain sensitive data, we open the instance for connections from any IPv4 and delegate authentication to PostgreSQL:

gcloud sql instances patch crt-benchmarks \
  --authorized-networks=0.0.0.0/0

This simplifies remote connections.

Role

A role with read-only permissions is created with for Grafana:

create role grafana_reader login password 'store_it_in_1password';
grant connect on database benchmarks to grafana_reader;

-- Execute these statements when connected to the benchmarks db.
grant usage on schema public to grafana_reader;
-- Repeat this when creating a new db that should be available in Grafana.
grant select on ft_transfer to grafana_reader;

The grafana_reader may use up to 20 connections. To verify that the PostgreSQL instance allows a sufficient number of connections you can run:

select setting from pg_settings where name = 'max_connections';

Remote connection

To connect to the database remotely, you can execute the psql recipe in the Justfile.