Prometheus & Grafana

Detailed steps and solutions for installing prometheus and grafana on the testing server.

Record of done

1. Grafana & Prometheus Integration
2. Vmware & Prometheus Integration
3. Kafka & JMX

Table of Content

1. Grafana & Prometheus Installation
2. Prometheus Configuration
3. Enable Service for Prometheus and Grafana
4. Import Grafana Dashboard - Leeman
5. Screenshots - Leeman
6. References

Installation

Since the testing server does not have docker environment, so we built and used the released binary for installation.

Install Prometheus from link

  tar -zxvf prometheus-2.36.0.linux-amd64.tar.gz
  sudo mv prometheus-*.linux-amd64 /opt/

Install Grafana link - Install from binary .tar.gz file

  tar -zxvf grafana-enterprise-8.5.4.linux-amd64.tar.gz

Configure Prometheus for Linux Service

Configure a Linux service to restart automatically after a reboot or crash using systemctl.

The configuration file depends on where you would like to put. For me, I put it inside the installation directory.

Open the configuration file/create a new one(prometheus.yml).

touch /opt/prometheus/prometheus.yml.
vi /opt/prometheus/prometheus.yml

Modify the file as shown as below

Note that ExecStart --config.file need to change where the configuration file is.

[Unit]
Description=Prometheus Server
Documentation=https://prometheus.iodocs/introduction/overview
After=network-online.target

[Service]
User=root
Restart=on-failure

ExecStart=/opt/prometheus \
    --config.file /opt/prometheus/prometheus.yml 

[Install]
WantedBy=default.target

Enable Service

sudo systemctl reload-daemon
sudo systemctl start prometheus
cd /opt/grafana-?.?.?
sudo systemctl start grafana-server
sudo systemctl enable grafana-server
sudo systemctl enable prometheus

Endpoint

Prometheus::9090/
Grafana::3000

Dashboard - Leeman

1. Download JSON file from github release page
2. Go to domain:3000/dashboard/import
3. Upload JSON file

Grafana - Overview

1. MySQL Uptime: Show server uptime
2. Current QPS: Queries per second (update every 5 mins)
3. Buffer pool size
4. Network
5. Perfomance
6. MySQL connection
7. MySQL Queries

MySQL uptime: mysql_global_status_uptime
QPS: rate(mysql_global_status_queries[$__interval]) or irate(mysql_global_status_queries[5m])

Grafana - Network Traffic

Inbound: Received Outbound: Sent Display as Graph (5mins)

rate(mysql_global_status_bytes_received[$__interval]) or 
irate(mysql_global_status_bytes_received[5m])

Grafana - Network Usage

Received

increase(mysql_global_status_bytes_received[60m])

Sent

increase(mysql_global_status_bytes_sent[60m])

Grafana - Client Thread Activity

Peak Threads Connected

max_over_time(mysql_global_status_threads_connected{instance="YOUR ADDRESS"}[$__interval]) or
max_over_time(mysql_global_status_threads_connected{instance="{YOUR ADDRESS"}[TIME])

Peak Threads Running

max_over_time(mysql_global_status_threads_running{instance="YOUR ADDRESS}[TIME]) or 
max_over_time(mysql_global_status_threads_running{instance="YOUR ADDRESS"}[$__interval])

Grafana - Table open status

Hit ratio

Hits / Hits + Missed

(rate(mysql_global_status_table_open_cache_hits[TIME]) or 
    rate(mysql_global_status_table_open_cache_hits[$__interval])) /
    ((rate(mysql_global_status_table_open_cache_hits[TIME]) or 
    rate(mysql_global_status_table_open_cache_hits[$__interval])) +
    (rate(mysql_global_status_table_open_cache_misses[TIME]) or 
    rate(mysql_global_status_table_open_cache_misses[$__interval])))

Hit rate

rate(mysql_global_status_table_open_cache_hits[$__interval]) or 
    irate(mysql_global_status_table_open_cache_hits[5m])

Grafana - MySQL Select Type

Select_scan

The number of joins that did a full scan of the first table.

rate(mysql_global_status_select_scan[$__interval]) or 
    irate(mysql_global_status_select_scan[TIME])

Select Full Join

The number of joins that perform table scans because they do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.

rate(mysql_global_status_select_full_join[$__interval]) or 
    irate(mysql_global_status_select_full_join[TIME])

Select Full Range Join

The number of joins that used a range search on a reference table.

rate(mysql_global_status_select_full_range_join[$__interval]) or 
    irate(mysql_global_status_select_full_range_join[TIME])

Mysql Questions

The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.

Questions

rate(mysql_global_status_questions[TIME]) OR 
    rate(mysql_global_status_questions[$__interval])

Grafana - MySQL commands

sum(rate(mysql_global_status_commands_total{command=~"select|insert"}[TIME])) without (command)
sum(rate(mysql_global_status_commands_total{command=~"delete|update"}[TIME])) without (command)

Screenshots - Leeman

References

1. Export and import Grafana
2. Prometheus Book
3. Grafana Install on RPM-based Linux
4. Prometheus Installation
5. Prometheus Configuration
6. Grafana Configuration
7. MySQL server status