Monitoring MySQL, JVM, AMQ with Prometheus

Just read the drill section!

• Monitoring MySQL, JVM, AMQ with Prometheus
  • VM setup
    • Post-installation
  • Serivce Installation
    • MySQL 5.7.26 community
    • OpenJDK 1.8.0_232
    • ActiveMQ 5.11.0
  • Monitoring
    • jmx_exporter
    • mysqld_exporter
    • ActiveMQ Pub/Sub
  • Prometheus
• Drill
  • Docker
  • Prometheus
  • MySQL exporter
    • Identify container network configuration
    • Add a SQL user
    • Start the exporter
  • JMX exporter

VM setup

Create a CentOS 8 VM with 4 vcpus, 8G memory, and 60G disk.

virt-install \
    --name prom \
    --memory 8192 \
    --vcpus 4 \
    --os-variant centos8 \
    --disk size=60,cache=none,io=native,bus=virtio \
    --disk vol=iso/centos8.iso,device=cdrom \
    --boot hd,cdrom,useserial=on \
    --network network=default,model=virtio \
    --graphics none \
    --autostart \
    --noreboot \
    --noautoconsole

virsh start prom --console

1. Press TAB on boot menu, and append the parameter console=ttyS0.
2. Don't create user; just set the password for root.
3. Configure network.
4. Use LVM.
5. NTP: tw.pool.ntp.org.
6. Repo URL: http://mirror01.idc.hinet.net/centos/8/BaseOS/x86_64/os/.
7. Choose minimal install with container management.

Post-installation

Resize root partition.

umount /dev/mapper/cl_prom-home
lvremove /dev/mapper/cl_prom-home
lvresize /dev/mapper/cl_prom-root /dev/vda2
xfs_growfs /dev/mapper/cl_prom-root
sed -i '/\/home/d' /etc/fstab

Upgrade system.

dnf upgrade --refresh && reboot
dnf system-upgrade reboot

Serivce Installation

MySQL 5.7.26 community

Install MySQL 5.7 on CentOS 8 / RHEL 8 Linux

There is no MySQL repository for EL 8, so we’ll use EL 7 repository instead. Create a new repository file.

Installing MySQL on Linux Using the MySQL Yum Repository

MySQL Yum Repository

TL;DR

dnf module disable mysql
dnf install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
dnf config-manager --disable mysql80-community
dnf config-manager --enable mysql57-community
dnf install 'dnf-command(versionlock)' mysql-community-server-5.7.26
dnf versionlock add mysql-community-server mysql-community-common

Detail

# Disable MySQL default AppStream repository 
dnf module disable mysql
# Install MySQL community repo for CentOS 7, as that of CentOS 8 is deprived of MySQL 5.7
dnf install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
# Removal of the mysql repo
dnf remove $(rpm -aq | grep mysql)

# Check the installation
dnf repolist all | grep mysql
# Switch to 5.7
dnf config-manager --disable mysql80-community
dnf config-manager --enable mysql57-community
# Check for minor version 5.7.26
dnf repoquery --showduplicates mysql-community-server | grep 5.7.26
dnf install mysql-community-server-5.7.26

# Lock MySQL package version
dnf install 'dnf-command(versionlock)'
dnf versionlock add mysql-community-server mysql-community-common

podman pull mysql:5.7.26

dnf module disable mysql

Post-installation

systemctl --now enable mysqld
grep 'temporary password' /var/log/mysqld.log
mysql -uroot -p

ALTER USER 'root'@'localhost' IDENTIFIED BY '<password>';
exit

Create ~/.my.cnf with content:

[client]
user=root
password=<passwd>

Running the command mysql in the future will automatically log you in with the MySQL root account.

OpenJDK 1.8.0_232

Use AdoptOpenJDK.

Create /etc/yum.repos.d/adoptopenjdk.repo with content:

[AdoptOpenJDK]
name=AdoptOpenJDK
baseurl=http://adoptopenjdk.jfrog.io/adoptopenjdk/rpm/centos/$releasever/$basearch
enabled=1
gpgcheck=1
gpgkey=https://adoptopenjdk.jfrog.io/adoptopenjdk/api/gpg/key/public

dnf repoquery --showduplicates adoptopenjdk-8-hotspot | grep 232
dnf install adoptopenjdk-8-hotspot-0:8u232_b09-1
dnf versionlock add adoptopenjdk-8-hotspot
# Check java version
java -version

ActiveMQ 5.11.0

• Release page
• How to install ActiveMQ on RHEL 8

# Create a non-privileged user to run amq
useradd activemq
mkdir /opt/activemq
chown activemq:activemq /opt/activemq
su activemq

cd /tmp
curl -LO http://archive.apache.org/dist/activemq/5.11.0/apache-activemq-5.11.0-bin.tar.gz
tar zxvf apache-activemq-5.11.0-bin.tar.gz -C /opt/activemq
/opt/activemq/bin/activemq start

• http://karaf.apache.org/
• https://activemq.apache.org/osgi-integration
• https://activemq.apache.org/examples

Monitoring

• Monitoring broker runtime data using Prometheus
• JMX Exporter

• How to Monitor MySQL Deployments with Prometheus & Grafana at ScaleGrid

jmx_exporter

• Architecture and Monitoring Apache ActiveMQ with Grafana
• How to Use Prometheus Monitoring With Java to Gather Data
• Github: prometheus/jmx_exporter

mysqld_exporter

CREATE USER 'exporter'@'localhost' IDENTIFIED BY '<password>' WITH MAX_USER_CONNECTIONS 5;
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'exporter'@'localhost';
GRANT SELECT ON performance_schema.* TO 'exporter'@'localhost';
SHOW VARIABLES WHERE Variable_name = 'port';

The typical result of port is 3306. I'll denote this number by <port>.

The GitHub Readme page instructs that we compile the exporter from source:

git clone https://github.com/prometheus/mysqld_exporter.git\
mv mysqld_exporter
make

This requires that go exists on the host; if not, run dnf install golang, and make again. If you face the following message among assertions, check the existence of the executable mysqld_exporter.

.level=debug msg="Error querying version" err="Error 1045: Access denied for user 'root'@'localhost' (using password: NO)"

If mysqld_exporter is found, don't worry about the error message; it's the part of the Makefile that queries the version of mysqld via 'root'@'localhost'. As long as mysqld_exporter is successfully compiled, we are good. We test it as follows:

export DATA_SOURCE_NAME='exporter:<password>@(localhost:<port>)/'
./mysqld_exporter --web.listen-address localhost:8081 &
curl -s localhost:8081/metrics | grep "mysql_up 1"
# Kill the job

By default, mysqld_exporter exposes the metrics for prometheus on port 9104. We intentionally changed it to 8081 with the web.listen-address flag.

If the output of grep is empty, something is wrong, as it should be mysql_up 1; consider the following:

1. Check if <password> and <port> is correct.
2. Check if the sql grants are correct.

Then, one would have to write a systemd unit file for mysqld_exporter. This could be quite a hassle. I suggest using podman on CentOS 8.

Podman is a closely-compatible docker alternative. Podman's is singular in its daemonless container runtime and rootless containers. However, its default rootless containers are painful when it comes to networking, so we will be running rootfull containers, i.e., running sudo podman instead of podman.

The pain comes from having to deal with:

rootless CNI infra image not present - please build image from https://github.com/containers/podman/blob/v2.2.1-rhel/contrib/rootless-cni-infra/ and tag as "rootless-cni-infra"

The docker package of CentOS 8 is, in fact, a facade of podman. Running docker in CentOS 8 prints the following message.

Emulate Docker CLI using podman. Create /etc/containers/nodocker to quiet msg.

To hide this message, simply do:

touch /etc/containers/nodocker

To install the containerized mysqld_exporter, the mysql grants have to updated. First, find out subnet and gateway associated with podman's default network – podman.

1. Podman or docker in the rest of this section must be run as root.
2. If the docker package is installed, the command docker can be used and is synonymous with podman.

sudo docker network inspect podman

In my case, I have:

{
    "gateway": "10.88.0.1",
    "subnet": "10.88.0.0/16"
}

According to the offical documentation, it seems that access to mysqld can be granted to a whole subnet but the netmask must be spelled out in full, instead of just the netmask length, e.g., 10.88.0.0/255.255.0.0. I'll refer to 10.88.0.1 and 10.88.0.0/255.255.0.0 as <gateway> and <subnet>, respectively.

CREATE USER 'exporter'@'<subnet>' IDENTIFIED BY '<password>' WITH MAX_USER_CONNECTIONS 5;
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'exporter'@'<subnet>';
GRANT SELECT ON performance_schema.* TO 'exporter'@'<subnet>';

I grant access to whole subnet as, I won't configure static IPs for containers.

docker run -d \
    --name sql_exporter \
    -p 9104:9104 \
    -e DATA_SOURCE_NAME="exporter:<password>@(<gateway>:<port>)/" \
    prom/mysqld-exporter

Similarly, we test with the following, where the output should read mysql_up 1, precisely.

curl -s localhost:8081/metrics | grep "mysql_up 1"

ActiveMQ Pub/Sub

GitHub: tomitribe/JMS-1.1-PubSub-Queue-Example-with-ActiveMQ

Prometheus

chown nobody:nobody <prom_data>
# In case that nobody is not defined:
# chown 65534:65534 <prom_data>
docker run \
    -p 9090:9090 \
    -v <prom_data>:/prometheus \
    -v <prom_config.yml>:/etc/prometheus/prometheus.yml \
    prom/prometheus

Drill

Docker

With a fresh install, I can confirm that CentOS 8 uses an emulated docker by podman. To prevent networking pitfalls, prepend sudo to every docker command on CentOS 8; CentOS 7 is not subject to this problem.

Prometheus

Create the prometheus config file prom_config.yml with content:

global:
  scrape_interval: 15s # default 60s
  evaluation_interval: 15s # default 60s
  scrape_timeout: 10s # default 10s

scrape_configs:
  - job_name: 'activemq'
    static_configs:
      - targets: ['<docker_bridge_ip>:<jmx_exporter_port>']
  - job_name: 'mysql'
    static_configs:
      - targets: ['<docker_bridge_ip>:<mysqld_exporter_port>']

Start prometheus with:

sudo docker run -d \
    --restart unless-stopped \
    --name prom \
    -p 9090:<prometheus_port> \
    -v prom_config.yml:/etc/prometheus/prometheus.yml:z \
    prom/prometheus

MySQL exporter

We will run the mysqld_exporter as a container. The exporter will log into the MySQL database to be monitored and read metrics. Hence, we have to create a special user in the database for this occasion.

Identify container network configuration

Identify the network configuration of the default bridge maintained by the container-runtime.

For podman

sudo docker network inspect podman

You should see something like:

{
    "gateway": "10.88.0.1",
    "subnet": "10.88.0.0/16"
}

For docker

ip a show docker0

This should result in something like:

6: docker0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default 
    link/ether XX:XX:XX:XX:XX:XX brd ff:ff:ff:ff:ff:ff
    inet 172.17.0.1/16 brd 172.17.255.255 scope global docker0
       valid_lft forever preferred_lft forever
    inet6 fe80::42:3aff:fe13:cb14/64 scope link 
       valid_lft forever preferred_lft forever

Add a SQL user

According to the offical documentation, it seems that access to mysqld can be granted to a whole subnet but the netmask must be spelled out in full, instead of just the netmask length. To assist discussion, I'll define variables <gateway> and <subnet>. Taking the two examples above:

• <gateway>:
  • 10.88.0.1 for podman.
  • 172.17.0.1 for docker.
• <subnet>:
  • 10.88.0.0/255.255.0.0 for podman.
  • 172.17.0.0/255.255.0.0 for docker.

The SQL user will be called <user> whose password will be <password>. With MySQL 5.7, the SQL variable MAX_USER_CONNECTIONS can be set to mitigate the load on the database due to metric-scraping; I'll denote this number by <connections> which is typically 3.

Log in the database as the root user:

mysql -u root -p

Then, run these commands:

CREATE USER '<user>'@'<subnet>' IDENTIFIED BY '<password>' WITH MAX_USER_CONNECTIONS <connections>;
GRANT PROCESS, REPLICATION CLIENT ON *.* TO '<user>'@'<subnet>';
GRANT SELECT ON performance_schema.* TO '<user>'@'<subnet>';

Start the exporter

sudo docker run -d \
    --restart unless-stopped \
    --name mysqld_exporter \
    -p 9104:<mysqld_exporter_port> \
    -e DATA_SOURCE_NAME="<user>:<password>@(<gateway>:3306)/" \
    prom/mysqld-exporter

Test with:

curl localhost:<mysqld_exporter_port>/metrics -s | grep "mysql_up 1"

On success, you should see the string "mysql_up 1" being echoed; if the output is empty, something is wrong.

JMX exporter

This will monitor ActiveMQ and its associated JVM. I can't find a way to run the JMX exporter without restarting ActiveMQ.

JMX exporter is meant to be run as a javaagent, e.g.,

java -javaagent:<agent> -jar <program>

I read about dynamically loaded javaagent. With this in mind, I experimented with jattach.

jattach <pid> load jmx_prometheus_javaagent-0.15.0.jar true <jmx_exporter_port> jmx_exporter/example_configs/activemq.yml

<pid> is determined by:

${ACTIVEMQ_HOME}/bin/activemq status

Unfortunately, this failed with:

Connected to remote JVM
Response code = -1

The content of jmx_exporter/example_configs/activemq.yml is:

lowercaseOutputName: true
lowercaseOutputLabelNames: true
blacklistObjectNames:
  - "org.apache.activemq:clientId=*,*"
whitelistObjectNames:
  - "org.apache.activemq:destinationType=Queue,*"
  - "org.apache.activemq:destinationType=Topic,*"
  - "org.apache.activemq:type=Broker,brokerName=*"
  - "org.apache.activemq:type=Topic,brokerName=*"

rules:
- pattern: org.apache.activemq<type=Broker, brokerName=(\S*), destinationType=Queue, destinationName=(\S*)><>(\w+)
  name: activemq_queue_$3
  attrNameSnakeCase: true
  labels:
    destination: $2

- pattern: org.apache.activemq<type=Broker, brokerName=(\S*), destinationType=Topic, destinationName=(\S*)><>(\w+)
  name: activemq_topic_$3
  attrNameSnakeCase: true
  labels:
    destination: $2

- pattern: org.apache.activemq<type=Broker, brokerName=(\S*)><>CurrentConnectionsCount
  name: activemq_connections
  type: GAUGE

- pattern: org.apache.activemq<type=Broker, brokerName=(\S*)><>Total(.*)Count
  name: activemq_$2_total
  type: COUNTER

- pattern: org.apache.activemq<type=Broker, brokerName=(\S*)><>(.*)PercentUsage
  name: activemq_$2_usage_ratio
  type: GAUGE
  valueFactor: 0.01

I will fix the version to 0.15.0. First, clone the GitHub repo to get the configuration file for the JMX exporter. Then, download the the JMX exporter (a jar file).

git clone --depth=1 -b parent-0.15.0 https://github.com/prometheus/jmx_exporter.git
curl -LO https://repo1.maven.org/maven2/io/prometheus/jmx/jmx_prometheus_javaagent/0.15.0/jmx_prometheus_javaagent-0.15.0.jar

Suppose the path to the ActiveMQ directory is apache-activemq-5.11.0. First, stop the ActiveMQ broker, e.g.,

apache-activemq-5.11.0/bin/activemq stop

Edit the file apache-activemq-5.11.0/bin/env by changing

ACTIVEMQ_OPTS="$ACTIVEMQ_OPTS_MEMORY -Djava.util.logging.config.file=logging.properties -Djava.security.auth.login.config=$ACTIVEMQ_CONF/login.config"

to

ACTIVEMQ_OPTS="$ACTIVEMQ_OPTS_MEMORY -javaagent:/path/to/jmx_prometheus_javaagent-0.15.0.jar=8080:/path/to/jmx_exporter/example_configs/activemq.yml -Djava.util.logging.config.file=logging.properties -Djava.security.auth.login.config=$ACTIVEMQ_CONF/login.config"

Test with curl localhost:8080/metrics.