Running a MySQL Database Service (MDS) DB System, the MySQL managed solution by Oracle MySQL on Oracle Cloud Infrastructure, means forgetting about time-consuming duties such as upgrades, backups and configuration, among others. It is so possible to focus on the service, on data and at the same time take advantage of the usual monitoring assets to configure and scale the service optimally. That’s what I want to talk about in this post: how to monitor a DB System in a few steps.

OCI MDS OS Metrics Charts

An MDS DB System compute instance file system is protected from any access, but you don’t need to access it to create system metrics, as these are already exposed on your DB System administration page in OCI dashboard.

It is also possible to take advantage of OCI services to raise alarms when certain metric reaches a configurable threshold and be notified about the event. Refer to docs. So let’s move forward to more interesting stuff.

OCI MDS DB System Charts

Talking about the very DB System metrics to understand how well is doing the server with our service workload, OCI dashboard related panel provides basic information about MySQL metrics, such as:

• Current connections
• Active connections
• Statement count

This is probably not enough to a skilled DBA, but MySQL Database Service is not different from any other non managed MySQL Server, as it can be queried as usual to fetch information, just as an example, from:

• Performance schema
• Information schema
• Show engine InnoDB Status
• Status variables

And with these, it is possible to build a monitoring system, or simply configure one of the many existing monitoring solutions. In this post I’ll show how to quickly setup a well known and professional (and open, and free) solution: the famous Grafana monitoring system. Grafana is web dashboard which can fetch metrics for anything measurable, by adding the proper data source. Here I will show a popular solution, that is the set Grafana + Prometheus + mysqld_exporter.

Let’s go in order, and I’ll go through setting up the different modules. This is not meant to be a tutorial to Grafana, but just a proof of concept and I strongly invite you to secure your setup once tested that everything working. Read more here about security.

Install Grafana

Let’s start playing with this. Log into your bastion host compute shape, which we’ll use to install Grafana. Check the download page and get the latest package.

wget https://dl.grafana.com/oss/release/grafana-7.3.5-1.x86_64.rpm

Now install the package, it will install grafana-server service:

sudo yum localinstall grafana-7.3.5-1.x86_64.rpm

If the installation completes without errors, you can go ahead and start the service.

service grafana-server start

Make sure it is started:

service grafana-server status

And make the service restart at host reboot:

sudo systemctl enable grafana-server 

Grafana service runs on default port 3000, open in the firewall of this compute instance:

sudo firewall-cmd --add-port=3000/tcp --permanent
sudo firewall-cmd --reload

Now browse to the subnet panel where your compute instance is deployed, click on the subnet and add an ingress rule allowing traffic on port 3000 (3000 is Grafana’s default port), you can simply allow traffic from any endpoint, but this is surely the place where you can select your client IP for increased security.

Save, and now you can point your browser to compute shape public IP (your compute shape bastion host is already on a public subnet, requirement to having a public IP). So, browse to:

http://<SUBNET_PUBLIC_IP>:3000

And you’re in! Just reset the password and authenticate.

Grafana will show metrics collected for the instance you plan to monitor. For that, it can use the built-in MySQL data source, or an external data source. In this post I’ll show how to setup the classical solution with the well known Prometheus and mysqld_exporter.

Before jumping into configuring the data source, be aware that you can also monitor OCI compute instances using the OCI data source plugin for Grafana, but we are not interested in this option because the host where the MySQL DB System is running is protected, and is already monitored as shown before. Anyway, here’s instructions if you’d like to monitor your bastion host or any other compute shape.

Install Prometheus

I have followed these clear instructions to setup Prometheus, just create user and group, download the latest package and set it up using the provided configuration. Refer to the blog linked, jump to “Installing & Configuring the Prometheus Server” and just use following configuration in file /etc/prometheus/prometheus.yml

# my global config
global:
  scrape_interval:     15s # Set the scrape interval to every 15 seconds. Default is every 1 minute.
  evaluation_interval: 15s # Evaluate rules every 15 seconds. The default is every 1 minute.
  # scrape_timeout is set to the global default (10s).

# Alertmanager configuration
alerting:
  alertmanagers:
  - static_configs:
    - targets:
      # - alertmanager:9093

# Load rules once and periodically evaluate them according to the global 'evaluation_interval'.
rule_files:
  # - "first_rules.yml"
  # - "second_rules.yml"

# A scrape configuration containing exactly one endpoint to scrape:
# Here it's Prometheus itself.
scrape_configs:
  - job_name: 'mysql_server1'
    static_configs:
    - targets: ['localhost:9104']

  # The job name is added as a label `job=<job_name>` to any timeseries scraped from this config.
  - job_name: 'prometheus'

    # metrics_path defaults to '/metrics'
    # scheme defaults to 'http'.

    static_configs:
    - targets: ['localhost:9090']

Once done with the procedure, you can start the service as usual. If you want to access Prometheus console in your browser, which needs TCP port 9090 open, remember to configure the firewall on your compute instance accordingly:

sudo firewall-cmd --add-port=9090/tcp --permanent
sudo firewall-cmd --reload

Remember also to add an ingress rule in your public subnet, as done for Grafana, but this time on port 9090.

Afterwards, you’ll be able to load the panel at the usual url, but won’t show any MySQL metric, because we haven’t configured

http://<SUBNET_PUBLIC_IP>:9090

Install mysqld_exporter

mysqld_exporter is a middleware which captures and exposes MySQL metrics in a format Prometheus can understand. Prometheus has been configured to scrape a target on port 9104, the default mysqld_exporter data source, which we will now link to our MDS DB System we want to monitor. Also in this case, there are very good instructions to address the setup, such as these. So let’s download latest package:

wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.12.1/mysqld_exporter-0.12.1.linux-386.tar.gz

Uncompress it, browse and move the package:

tar xf mysqld_exporter-0.12.1.linux-386.tar.gz
cd  mysqld_exporter-0.12.1.linux-386
sudo mv mysqld_exporter /usr/local/bin/

Now create a MySQL user that mysqld_exporter will use to fetch metrics from the DB System (not monitoring the DB System locally, which is impossible because we can’t access the related host, then I will simply allow all “%” hosts).

CREATE USER 'exporter'@'%' IDENTIFIED BY '<PASSWORD>' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'%';

Now edit configuration at /etc/.exporter.cnf, here’s my config.

[client]
user=exporter
password=<PASSWORD>
host=<DB_SYSTEM_IP>

Add mysqld_exporter as service, and edit this file:

sudo vi /lib/systemd/system/mysql_exporter.service

With this configuration (here you can choose port and metric to be collected, let’s stick to port 9104 as configured earlier in Prometheus scrape endpoints):

 [Unit]
 Description=Prometheus MySQL Exporter
 After=network.target
 User=prometheus
 Group=prometheus

 [Service]
 Type=simple
 Restart=always
 ExecStart=/usr/local/bin/mysqld_exporter \
 --config.my-cnf /etc/.exporter.cnf \
 --collect.global_status \
 --collect.info_schema.innodb_metrics \
 --collect.auto_increment.columns \
 --collect.info_schema.processlist \
 --collect.binlog_size \
 --collect.info_schema.tablestats \
 --collect.global_variables \
 --collect.info_schema.query_response_time \
 --collect.info_schema.userstats \
 --collect.info_schema.tables \
 --collect.perf_schema.tablelocks \
 --collect.perf_schema.file_events \
 --collect.perf_schema.eventswaits \
 --collect.perf_schema.indexiowaits \
 --collect.perf_schema.tableiowaits \
 --collect.slave_status \
 --web.listen-address=0.0.0.0:9104
 
 [Install]
 WantedBy=multi-user.target

Finally reload services, enable the service at restart, start it and test it.

sudo systemctl daemon-reload
sudo systemctl enable mysql_exporter.service
sudo systemctl start mysql_exporter.service
sudo systemctl status mysql_exporter.service

All done, verify that the three services you have setup so far are enabled at compute instance restart.

systemctl list-unit-files | egrep -i "mysql_exporter|grafana|prometheus" 
grafana-server.service                        enabled 
mysql_exporter.service                        enabled 
prometheus.service                            enabled 

You can now check that Prometheus can fetch metrics correctly from the mentioned panel at:

http://<SUBNET_PUBLIC_IP>:9090

Make a search and verify it’s all working.

Point Grafana to Prometheus data source

So far we have achieved:

• Grafana is set, reachable from the internet (port was opened on the public subnet, but it could also be reached within a VPN).
• Prometheus is running and configured and pointing to mysqld exporter
• And indeed mysqld exporter is running and pointing to the DB System

We just need to point Grafana to Prometheus to complete the setup. Browse to Grafana/Configuration/Data Source/Add Data Source and configure the Prometheus instance we already set.

Make sure all is working when you click “Save & Test”, a message will inform if the connection was successful or not. Then you are good to go. Otherwise… jump to Troubleshooting section at the bottom of this post.

Choose a Grafana Dashboard

Ok, Grafana can fetch all the data it needs, it must only be instructed to show it in a convenient way. For that you need to either create your own dashboard or use one of those that already available. Browse here to list dashboards (remember to filter by “Data Source=Prometheus” and “Name/Description=MySQL”). In this example I have used this one Percona Monitoring and Management project (or this one too loads well), pick the ID and browse via Grafana’s navigator to +/Import and provide the Dashboard ID.

Click import and this dashboard is finally available!

Mission accomplished, happy monitoring!

Troubleshooting

I have faced several issues to setup this monitoring system, such as the infamous error when trying to display data in a dashboard “Cannot read property ‘result’ of undefined”. Just a few hints.

• Verify entries in /var/log/grafana/grafana.log
• Verify prometheus, mysqld_exporter are running
• You can restart mysqld_exporter manually to make sure no error is reported (command as specified in /lib/systemd/system/mysql_exporter.service)
• Read more here

References

http://oracle-help.com/oracle-cloud/devops/installation-of-grafana-on-oci-instance/
https://scalegrid.io/blog/how-to-monitor-mysql-deployments-with-prometheus-and-grafana-at-scalegrid/
https://iamabhishek-dubey.medium.com/setting-up-mysql-monitoring-with-prometheus-6029cec87db0