Monitor QuestDB Database With Prometheus and Grafana

Since many businesses around the world rely on applications to run, monitoring makes an integral part of the system administration role. This is mainly done to determine the health and performance of servers. There are several stacks available to perform monitoring. Among these stacks are Observium, New Relic, Elastic Stack, Prometheus, Dynatrace, N-able RMM, PRTG Network Monitor, Datadog Infrastructure Monitoring, Zabbix, Zenoss, Nagios e.t.c

In our previous guide, we went through how to install and use QuestDB Database. QuestDB is an open-source high-performance time-series database(TSDB) written in Java and C languages. It finds high use for applications in IoT, event monitoring, machine learning, DevOps, financial services e.t.c.

What is Prometheus?

Prometheus is a free and open-source event monitoring and alerting tool. It was developed in 2012 by SoundCloud and then later in 2016, promoted to the Cloud Native Computing Foundation.

Prometheus works by collecting and storing metrics as time series data. Metrics is the numeric quantification that varies from time to time. The metrics collected are timestamped and stored with key/value pairs.

Prometheus offers the following features:

• Single server nodes are autonomous and don’t rely on distributed storage
• It uses the PromQL query language which is flexible to leverage this dimensionality.
• It supports multiple graphing and dashboarding modes.
• The time-series metrics collection happens via a pull model over the HTTP protocol.
• Targets are discovered via service discovery or static configuration
• Multi-dimensional data model where time series data is identified by metric name and key/value pairs

What is Grafana?

Grafana is an open-source tool that offers analytics and visualization of metrics. It is preferred since it provides context-rich visualizations, not only through graphs but also through other data presentation methods. Futhermore, each dashboard can be customized to meet the needs of the particular software development project. It can be extended through plugins that make it possible to query, visualize, monitor, alert on and comprehend metrics from several data sources that include Google sheets, Amazon Timestream, ElasticSearch, InfluxDB, Prometheus, Graphite, PostgreSQL, MySQL e.t.c

Grafana offers the following:

• List of errors with trace IDs: to help one debug errors in Grafana, error alerts include a trace ID. These alert errors can be accessed from the Profile
• Explore to Dashboard: you can create panels/dashboards directly from Explore tab.
• Prometheus query builder: due to the complexity of the PromQL query language, Grafana 9 introduces the new UI query builder from which you can easily compose and learn how to run Prometheus queries.
• Heatmap panel: that offers
  • Multiple orders of magnitude faster
  • Support for Prometheus sparse histograms
  • Supports changing the number of colors steps
  • For un-bucketed data, it performs smarter auto bucket sizing
  • Allows filtering out bucket values close to but not exactly zero

By following this guide to the end, you should be able to monitor QuestDB Database With Prometheus and Grafana.

1. Monitor QuestDB Database With Prometheus

It is possible to measure the internal status of a QuestDB instance via an HTTP endpoint exposed by QuestDB at port 9003.

a. Run QuestDB Database

QuestDB must be running and accessible. You can do so from Docker, the binaries, or Homebrew for macOS users. The guide below can be used to achieve this:

• How To Run QuestDB SQL database in Docker Container

To be able to scrape metrics you need to have metrics.enabled key in the server configuration. This can be done on docker as shown:

docker run  -t -d \
 -e QDB_METRICS_ENABLED=TRUE \
 -p 9000:9000 \
 -p 9009:9009 \
 -p 8812:8812 \
 -p 9003:9003 \
 --name docker_questdb \
 -v "$(pwd):/var/lib/questdb" \
 questdb/questdb

Once the container is running, verify if metrics are being exposed correctly;

$ curl http://127.0.0.1:9003/metrics
# TYPE questdb_json_queries_total counter
questdb_json_queries_total 0

# TYPE questdb_json_queries_completed_total counter
questdb_json_queries_completed_total 0

# TYPE questdb_json_queries_cached gauge
questdb_json_queries_cached 0

# TYPE questdb_pg_wire_select_queries_cached gauge
questdb_pg_wire_select_queries_cached 0

# TYPE questdb_pg_wire_update_queries_cached gauge
questdb_pg_wire_update_queries_cached 0

# TYPE questdb_unhandled_errors_total counter
questdb_unhandled_errors_total 0

# TYPE questdb_commits_total counter
questdb_commits_total 1

# TYPE questdb_o3_commits_total counter
questdb_o3_commits_total 0

# TYPE questdb_committed_rows_total counter
questdb_committed_rows_total 1
.........

b. Install and configure Prometheus

Prometheus should also be installed. This can be done using any of the guides below:

• Install Prometheus with Node Exporter on Rocky Linux 9/AlmaLinux 9
• Install Prometheus Server on Ubuntu
• Install Prometheus and node_exporter on Debian

Once installed, you need to create a QuestDB job that scrapes the metrics on port 9003.

sudo vim /etc/prometheus/prometheus.yml

In the opened file, add the below lines under scrape_configs:

scrape_configs:
....
  - job_name: 'questdb'
    scrape_interval: 5s
    static_configs:
      - targets: ['127.0.0.1:9003']

Save the file and restart Prometheus;

sudo systemctl restart prometheus

c. Dashboard access and Metrics viewing on Prometheus

Now access the Prometheus dashboards using the URL http://IP_Address:9090.

Once on this dashboard, navigate to Status->Targets. You should see the QuestDB target added to show that the metrics have been scraped successfully:

Now the metrics can be used to create graphs. This can be done by clicking on graphs and using the PromQL query beginning with questdb_.

There are several metrics here, including:

Run a preferred query. For example:

Once executed, navigate to the Graph tab to view the populated graph

2. Monitor QuestDB Database With Grafana

For Grafana, we will interact with the following terms:

• Data source: This instructs Grafana where your data is stored and how you want to access it. In this guide, we will use access QuestDB via Postgres Wire using the PostgreSQL data source plugin.
• Dashboard: consists of widgets that are displayed together on the same screen.
• Panel: This is a single visualization which can be a graph or table.

a. Install Grafana

Grafana can be installed using the aid provided in the guides below;

• Install Grafana with Prometheus on Rocky Linux 9|AlmaLinux 9
• How To Install Grafana on CentOS 7 / RHEL 7
• How To Install Grafana on Debian

You can also run Grafana on docker:

docker run -d -p 3000:3000 grafana/grafana

I also assume that you have QuestDB running with port 8812 for the PostgreSQL connection and port 9000 for the web and REST interface exposed. You can achieve this by running a similar docker command as that in the first step above.

Once Grafana has been installed, access the web UI using the URL http://IP_address:3000

Login using the admin credentials

username: admin
password: admin

b. Loading the demo dataset

Now we will load a demo dataset(taxi data for the whole of February 2018) to our QuestDB using the REST API. First, download the CSV file:

curl https://s3-eu-west-1.amazonaws.com/questdb.io/datasets/grafana_tutorial_dataset.tar.gz > grafana_data.tar.gz
tar -xvf grafana_data.tar.gz

Once extracted, you will have two files, weather.csv and taxi_trips_feb_2018.csv. Now import the data using the commands:

curl -F data=@taxi_trips_feb_2018.csv http://localhost:9000/imp
curl -F [email protected] http://localhost:9000/imp

c. Creating your first visualization

To create a graph on Grafana, you need to add a data source. From the web UI, navigate to  Data Sources and click the Add data source button. Now choose the PostgreSQL plugin as the data source and configure it with the following:

host: IP_Address:8812
database: qdb
user: admin
password: quest
SSL mode: disable

You can also replace the IP address of your QuestDB server appropriately. Once filled in, the details should appear as shown here:

Click Save & Test to see if the connection is okay. If everything is okay, you will see this:

Once filled, you can proceed and create a dashboard by clicking on + New Dashboard:

Now add a new panel and provide a query in the bottom half.

Toggle the query editor to text edit mode by clicking the pencil icon or by clicking the Edit SQL button. This will allow you to write and execute SQL queries directly.

Now paste the below content in the editor:

SELECT pickup_datetime AS time,
       avg(trip_distance) AS distance
FROM ('taxi_trips_feb_2018.csv' timestamp(pickup_datetime))
WHERE $__timeFilter(pickup_datetime)
SAMPLE BY $__interval

Save and apply the query then proceed and set the time range from 2018-02-07 00:00:00 to 2018-02-14 23:59:59 and click Apply time range.

You will be able to see the below graph.

d. Adding multiple queries

Now you can add other panels using similar steps. Using the added datasets, we can have other panels:

• For cash payments

SELECT pickup_datetime AS time,
       avg(trip_distance) AS cash
FROM ('taxi_trips_feb_2018.csv' timestamp(pickup_datetime))
WHERE $__timeFilter(pickup_datetime)
AND payment_type IN ('Cash')
SAMPLE BY $__interval

• For card payments

SELECT pickup_datetime AS time,
       avg(trip_distance) AS card
FROM ('taxi_trips_feb_2018.csv' timestamp(pickup_datetime))
WHERE $__timeFilter(pickup_datetime)
AND payment_type IN ('Card')
SAMPLE BY $__interval

This can be filled in two separate tabs A and B as shown:

In the above diagram, the queries are layered in one panel with green line for cash and a yellow line for card payments.

You can also add another panel:

SELECT
pickup_datetime AS "time",
count()
FROM ('taxi_trips_feb_2018.csv' timestamp(pickup_datetime))
WHERE $__timeFilter(pickup_datetime)
SAMPLE BY $__interval;

This is the output of the query:

Now you can save the dashboards and view them together.

Verdict

That is it on how to monitor QuestDB Database With Prometheus and Grafana. Using the gathered knowledge, you should be able to create and view graphs in both Prometheus and Grafana and therefore monitor your QuestDB Database. I hope this was important to you.

See more:

• Monitor Percona MySQL / Percona XtraDB Cluster With Prometheus & Grafana
• Monitoring MySQL / MariaDB with Prometheus in five minutes
• Monitoring MySQL and MongoDB with Prometheus and Grafana – PMM