Monitoring APEX with Prometheus and Grafana

 

When Database 23 Free was released, I went through the process of setting up a Podman Pod with the image of the database and ORDS, so that I could play around with all the new features:

  • container-registry.oracle.com/database/free:latest [Details]
  • container-registry.oracle.com/database/ords:latest [Details]

Over the Christmas break, I was going through the process of updating the images (having had a new ORDS release since I last used this, and subsequently a minor update to the Database image).

As I was browsing Container Registry, I noticed this repository listed, Observability Exporter. So I thought I would have a look into this.

By adding this monitoring, we will end up with 3 new images added to our stack:

  • container-registry.oracle.com/database/observability-exporter:1.1.1 [Details] [GitHub]
  • quay.io/prometheus/prometheus:latest
  • docker.io/grafana/grafana-oss:latest

If you are not using pods, throughout this document I am referring to podman-create statements. These can be adjusted according to your needs. This is my pod definition:

podman pod create \
  -p 8181:8181 \
  -p 1521:1521 \
  -p 9161:9161 \
  -p 9090:9090 \
  -p 3000:3000 \
  dbfree-pod

What is Observability Exporer?

This is software that provides metrics data that can be consumed by Prometheus. It produces output in a text-based format that essentially follows the format: metric_name{label1=xx,label2=yy} value

For example, once you run the container and look at some of the default metrics, one such metric is the tablespace bytes. That metric outputs like this:

oracledb_tablespace_bytes{tablespace="USERS",type="PERMANENT"} 3.473408e+06

Setting up Observability Exporter

Before we can use this, we need a DB user that can query the relevant dictionary views. For this I created a database user exporter. 

You can notice I added the apex_administrator_read_role to the user. That is so that we can extend the exporter data to include some useful APEX related information.

The other piece of information, when running the image you need to have specified the following environment variables:

  • DB_USERNAME
  • DB_PASSWORD
  • DB_CONNECT_STRING

Before starting the Observability Exporter container, you will want to provide some additional queries to suit your monitoring needs. The container contains some default metrics, but nothing for APEX (and perhaps not the specific metrics you are after). To address the APEX side, some rough idea of things you may want to monitor:

  • How many sessions with lifetime left
  • Average page elapsed time by application
  • Failed login attempts (in the last hour)
  • Application errors (in the last hour)

There are probably other metrics you can think of that may be suitable to your application suite, that are useful from a monitoring point of view. With those businesss requirements defined, we can create our metrics file outlining our queries.  

Now we are ready to create the container to start exposing metrics for our database.

podman create \
  --name exporter \
  --pod dbfree-pod \
  -e DB_USERNAME=exporter \
  --secret EXPORTER_PWD,type=env,target=DB_PASSWORD \
  -e DB_CONNECT_STRING=db:1521/freepdb1 \
  -e CUSTOM_METRICS=/apex_metrics.toml \
  -v "$(pwd)"/apex_metrics.toml:/apex_metrics.toml \
  container-registry.oracle.com/database/observability-exporter:1.1.1

Here, I have used a Podman secret for the DB_PASSWORD environment variable. To do that you can do echo "xxxxxx" | podman secret create EXPORTER_PWD -. Or adjust the above statements to use environment variables directly.

(This container using port 9161).

With the container attached to the pod, I can now start the container

podman container start exporter

Once started, you can validate by navigating to localhost:9161/metrics, and if all went well you should see some Oracle metrics data - like the oracledb_tablespace_bytes I referred to earlier on.

Exposing to Prometheus

Now that our metrics exporter is in place, the next step is to configure our Prometheus server to consume that data. For that, we need to define a YAML configuration file that defines a scrape config to point to the Observability Exporter server. We name this file prometheus.yml. That config file should look like this:

With that file created, we define our Prometheus container and start it up. This one uses port 9090.

podman create \
  --name prometheus \
  --pod dbfree-pod \
  -v "$(pwd)"/monitoring/prometheus.yml:/etc/prometheus/prometheus.yml \
  quay.io/prometheus/prometheus:latest
  
podman container start prometheus

And, if all went well, after navigating to localhost:9090, we should be able to search for one of our metrics and display the graph. For example, oracledb_tablespace_bytes{tablespace="USERS"}:


Putting it all together in a Grafana Dashboard

Prometheus is good if we want to look at the graphs in an ad-hoc nature, but ultimately, we want to build some nice, persistent dashboards, in Grafana. 

First step is to set up our third and final container. I am just using the OSS edition - there are two repos, grafana/grafana and grafana/grafana-oss. The former version can be upgraded to Enterprise edition but I didn't ever anticipate doing that for my home lab, so I stuck with grafana-oss.

Before we create the container, we need to define a configuration file for our data sources - so that it can pick up the Prometheus data. That file will looks like this.

With that file created, we define our Grafana container and start it up. This one uses port 3000.

podman create \
  --name grafana \
  --pod dbfree-pod \
  -e GF_AUTH_ANONYMOUS_ENABLED=true \
  -e GF_AUTH_ANONYMOUS_ORG_ROLE=Admin \
  -v "$(pwd)"/grafana_datasources.yml:/etc/grafana/provisioning/datasources/datasources.yaml \
  -v grafana_vol:/var/lib/grafana \
  grafana-oss:latest

A couple of notes on the inputs. I set this up to allow anonymous access, in your environment you may prefer to require login - which can easily be done. I also specified a named volume (grafana_vol) so that if I need to recreate the container, the database can survive.

At this point if you navigate to http://localhost:3000/connections/datasources you should see the Prometheus data that was defined in the configuration file.

The next step is to build out a dashboard. Fortunately, there is a collection of community submitted dashboards that you easily import into your Grafana environment - this is mostly useful to get a good starting point. Horses for courses if you prefer to start from scratch.

If you navigate to the Grafana Dashboard portal, and search for Oracle - there are a few hits.

https://grafana.com/grafana/dashboards/?search=oracle

If you click into one that you like (I chose the first one), it shows an identifier in the right hand panel.

So this one has an ID of 3333.

Now back to our Grafana server, if we go to the Dashboard page - we can import by ID.


Enter the dashboard ID (3333) and click Load.

After you click Load, there is a final screen before the dashboard gets created where you can give it a new name and also you must specify your Prometheus data source (not selected by default). I chose to give mine the name Oracle 23c Free.

It is worth noting, not all the graphs on this community dashboard are not defined metrics in our Observability Exporter, but it gives us a good starting point.

Our next step is to clean this up (remove all the bad graphs) and add our APEX metrics. We do this by specifying to add visualization and searching for our metric name and any other customisations you'd like to make.

Then it's just a manner of organising it to make a nice layout - Grafana provides a pretty nice drag and drop interface. So, with a few tweaks, we can land on a Dashboard something like this:

Once you have perfected your dashboard, you can get a JSON export of it and you can update your provisioning scripts to load that dashboard by default and also make it available on the landing page.

My actual container provisioning statement looks like this:

podman create \
  --name grafana \
  --pod dbfree-pod \
  -e GF_AUTH_ANONYMOUS_ENABLED=true \
  -e GF_AUTH_ANONYMOUS_ORG_ROLE=Admin \
  -e GF_DASHBOARDS_DEFAULT_HOME_DASHBOARD_PATH=/home/grafana/dashboards/oracledb.json \
  -v "$(pwd)"/monitoring/grafana_datasources.yml:/etc/grafana/provisioning/datasources/datasources.yaml \
  -v "$(pwd)"/monitoring/dashboards:/home/grafana/dashboards \
  -v "$(pwd)"/monitoring/grafana_dashboard.yml:/etc/grafana/provisioning/dashboards/main.yaml \
  -v grafana_vol:/var/lib/grafana \
  grafana-oss:latest

Other Dashboard Techniques

I can't close this out without mentioning that OCI also provides some nice visualizations for created databases. One of my good friends Adrian submitted a live lab about how you can expose custom metrics in OCI. See this LiveLab for more information on that topic: https://apexapps.oracle.com/pls/apex/r/dbpm/livelabs/view-workshop?wid=3587.

Out of the box, OCI does have a few APEX metrics such as page load time and workspace count.

Credits

Splash Photo by nine koepfer on Unsplash 


Popular posts from this blog

Report row buttons firing a dynamic action

Image
A lot of the time on reports of data, we'll define a link column, and specify an image which is the beloved pencil icon. Which is good and all, but looking in the Sample Database Application (packaged application), they have taken a different approach to style links - a button that stretches the width of it's containing cell. So, the UI ends up looking like so: So, is this done? Well first, you define a new column for the placeholder. Suppose I want my link column to be Report, I define an extra column in my query: , 'Report' report_link Then I need to go to my new column's attributes, and change the column type to a link. And, in the link attributes, set the text to what you want displayed. Here, I'm just going to display the value of the new column and point to my desired page. So I set the target page, then also the Link text as #REPORT_LINK#. At this point the report will just show link text, as we expect. So, then to replicate t
Read more

Accessing the last request value from a page submission

Image
When you submit the page, you will typically have page processes that run. For me, I use these request values to determine which processes are run. This is rather straight forward for on submit processes as you can just specify the condition request = Expression 1 Or, if you have multiple requests that link to this process, Request is Contained within Expression 1, and Expression 1 would be a comma delimited set of values. Request simply refers to the bind variable :REQUEST (or apex_application.g_request). If we try and do the same on a page rendering process, we no longer have access to this value - which makes sense. If you update the branch, to set the request portion of the URL, we will then be able to access the bind variable :REQUEST in all page rendering processing points. This has the obvious downside that if the user wants to reload the page,  clicks in the address bar and hits enter (or clicks refresh), the processes on that condition
Read more

Installing Oracle Instant Client on Ubuntu

Now that Oracle has enabled us to download instant client without any click through for accepting the license , I wanted to revisit a seamless install of the instant client on a new set up. Ubuntu has the documentation about installing the instant client here: https://help.ubuntu.com/community/Oracle%20Instant%20Client. First - because Oracle provides their releases in RPM archive format (or a tarball), in order to have an installer you need to create a DEB archive. There is a package in the archives, alien, which aids this process. This gives the start of the script: #!/bin/bash # Install dependencies sudo apt install alien The 3 packages the Ubuntu documentation tells us to retrieve are: - devel - basic (I opt for basiclite instead) - sqlplus So, over at the downloads page: https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html we can grab the link. # Download files. Example specific to 19.3 # Some links were not correct on the do
Read more