Monitoring your database with Prometheus and Grafana
Tags: opensource, monitoring, database
Published: 2021-04-12 16:12:49


[Prometheus](https://prometheus.io/) is an open-source systems’ monitoring and alerting toolkit. It is very useful for monitoring machine metrics and service-oriented architectures, supporting multi-dimensional data collection and querying. If paired with [Grafana](https://grafana.com/), the open-source visualization tool that we introduced [in this previous post](https://crate.io/a/monitoring-cratedb-on-kubernetes-with-prometheus-and-grafana/), one can build very nice monitoring dashboards.

In this tutorial, we will show you: 
* How to set up CrateDB (the [open source database](https://crate.io) we develop at Crate.io), Prometheus, and Grafana with `docker-compose`
* Hoe to enable JMX monitoring in CrateDB
* How to set up a monitoring dashboard in Grafana, giving you the option of importing a complete pre-built dashboard 

We're using CrateDB 4.5.0, Prometheus 2.26.0 and Grafana 7.5.2.	

Let's do it! 

## Starting Prometheus, Grafana and CrateDB with Docker (+ JMX monitoring)

To run our tools, we will be using [Docker](https://docs.docker.com/compose/). We will build our containers by using docker-compose, which comes very handy to define and run multi-container Docker applications very quickly. 

Docker-compose comes pre-installed with your Docker installation. If you don't have Docker installed, you can download it [here](https://www.docker.com/products/docker-desktop) — just follow the instructions that will pop-up.

Once your installation of Docker is complete, create a working directory and navigate there with your terminal. There, create a `docker-compose.yml` file with the following content:

```java
version: "3.9"
services:
  cratedb:
    image: "crate"
    volumes:
      - ./crate-jmx-exporter-1.0.0.jar:/jmxdir/crate-jmx-exporter-1.0.0.jar
    ports:
      - "4200:4200"
      - "7071:7071"
    environment:
      CRATE_JAVA_OPTS: "-javaagent:/jmxdir/crate-jmx-exporter-1.0.0.jar=7071 -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.authenticate=false"
  prometheus:
    image: "prom/prometheus"
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml
    ports:
      - "9090:9090"
  grafana:
    image: "grafana/grafana"
    ports:
      - "3000:3000"
```

(You can create a [.yml](https://www.reviversoft.com/file-extensions/yml) file through multiple methods. In macOS, you can open a terminal text editor like nano, saving the file with the .yml extension. You can also use a [text editor](https://support.apple.com/guide/textedit/welcome/mac).)

Now, let's move on to the next step. As we briefly mentioned in the introduction, to scrape CrateDB we're making use of the [Java Management Extensions (JMX)](https://en.wikipedia.org/wiki/Java_Management_Extensions) and the CrateDB [JMX monitoring feature](https://crate.io/docs/crate/reference/en/latest/admin/monitoring.html).

To set it up, download the latest JMX monitoring .jar [here](https://repo1.maven.org/maven2/io/crate/crate-jmx-exporter/). Click on /1.0.0; in this blogpost, I’m using the file called [crate-jmx-exporter-1.0.0.jar](https://repo1.maven.org/maven2/io/crate/crate-jmx-exporter/1.0.0/crate-jmx-exporter-1.0.0.jar). Then, move the .jar file into the working directory you created before.

*Note: let's take a closer look at what's happening here. With the help of the `.yml` file we defined earlier, we are going to start three containers at the same time (CrateDB, Prometheus, and Grafana) and expose their relevant ports. Besides, we are including the `JMXExporter.jar` into the Docker container, by using the `volumes` directive. Then, we are using the `-javaagent` directive to enable the JMXExporter and configure it to use port 7071. The other arguments you see in the file are needed to fully enable JMX Monitoring.*

One last configuration item. Before we can start Prometheus, we need a new configuration file for it. In order to do that, create a new .yml file named `prometheus.yml` in your working folder, and paste the following content in it:

```java
global:
  scrape_interval: 15s
  scrape_timeout: 10s
  evaluation_interval: 15s
scrape_configs:
- job_name: prometheus
  honor_timestamps: true
  metrics_path: /metrics
  scheme: http
  static_configs:
  - targets:
    - cratedb:7071
```

Now, we are ready to start all the containers. Navigate to your working folder with the terminal and run the following command:

```
docker-compose up
```

Wait a few seconds for the process to end. When it finishes, you can access CrateDB, Prometheus, and Grafana.

👉 To connect to the CrateDB, open [http://localhost:4200/](http://localhost:4200/)  in your browser

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k7ydoz87jxb0tmdrbzzh.png)

👉 To connect to Prometheus, open [http://localhost:9090/](http://localhost:9090/) in your browser

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g7xhb4vxyezh8tuynw3b.png)

If you click on ”Status -> Targets”, you will see that CrateDB is already set up as an endpoint:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d44ln1nutj9a18obvzj1.png)

👉 To connect to Grafana, open [http://localhost:3000/](http://localhost:3000/) in your browser

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2rvrtdu87kre7d6ze9ov.png)

*Note: if this is your first time using Grafana, first fill “admin” in both the username and the password field. You can define your credentials on the next screen.*

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pnsjhvrla4aymw7cof1b.png)

## Setting up a pre-built monitoring dashboard in Grafana

Now that we have all our tools ready, let’s set up a dashboard to monitor our cluster in Grafana using Prometheus as the data source.

In Grafana, go to “Configuration -> Data sources”:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b8xm4fkr0aywz6uqej06.png)

Now, click on ”Add data source”:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/18pq11zuwknhxhrses6e.png)

Select ”Prometheus”:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6j7yc5phi47j8k3rmcmx.png)

A configuration page will show up. Fill up the following fields:
* Name: Prometheus
* Host URL: http://prometheus:9090/
You can leave all the other fields with the default configuration.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mbtpi72rw55zss3efi75.png)

When you’re done, scroll to the end of the page, and click on “Save & Test”. If everything goes well, you’ll see a message saying “Data source is working”.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/klx17eva5nv6zsj1kjc1.png)

Now, let's set up our dashboard. On the left menu, click on "Create -> Dashboard":

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/66zfrqtm8lz2h0itz7og.png)

You will see a screen like the one below. Click on "Add new panel". (Panels are the building blocks of Grafana's dashboards).

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k06frr7cmeccoj0curs2.png)

The configuration screen for your new panel will open up. Here, you can define all the elements of your panel, like its name, queries, type of visualization, and so on. 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/byg3ffso127jvc3oj2tl.png)

To learn about all the possibilities that Grafana offers, check out their [documentation](https://grafana.com/docs/grafana/latest/panels/), and experiment by building your own panels. 

In this tutorial, however, I am going to give you the option of **importing a pre-built monitoring dashboard**.

In order to do so, first download [this JSON file](https://gitlab.com/cratedb1/monitoring-grafana/-/snippets/2101894).

Then, on the Grafana home page, click on “Create -> Import”:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kxs24qmzk0kg5bgwyxmu.png)

Now, press on “Upload JSON file”, and select the JSON file you just downloaded. Then, click on “Import”.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zgz6kyivbrs1mgk30au9.png)

Voilà! You now have a complete dashboard monitoring CrateDB in real-time 👏 Your panels show:

* Queries per second
* Average query duration over the last minute
* Query error rate
* GC rates
* Number of shards
* Circuit breaker memory in use
* JMV memory in use

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/94y0ai8xvaybq0ist1f6.png)

*PS: This dashboard is similar to what we're using at Crate.io for monitoring real clusters in production. This is how it looks like for one of our customers:*

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qhc17hc256j61msqlw0f.png)

Hope this was useful 😁

____________________________________________________________

*You can also find the [Crate.io](https://crate.io/) team at:*

* *[Github](https://github.com/crate) (We'd love a* ⭐️ 🙏*)*
* *[Twitter](https://twitter.com/crateio)* 
* *[Our community page](https://community.crate.io/)*

*See you around* 🐐

____________________________________________________________