Monitoring MySQL, JVM, AMQ with Prometheus

# Monitoring MySQL, JVM, AMQ with Prometheus :::danger **Just read the drill section!** ::: [TOC] ## VM setup Create a CentOS 8 VM with 4 vcpus, 8G memory, and 60G disk. ```bash 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. ```bash 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. ```bash 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](https://computingforgeeks.com/install-mysql-5-7-on-centos-rhel-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](https://dev.mysql.com/doc/refman/5.7/en/linux-installation-yum-repo.html) [MySQL Yum Repository](https://dev.mysql.com/downloads/repo/yum/) #### TL;DR ```bash 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 ```bash # 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 ```bash systemctl --now enable mysqld grep 'temporary password' /var/log/mysqld.log mysql -uroot -p ``` ```sql 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](https://adoptopenjdk.net/installation.html?variant=openjdk8&jvmVariant=hotspot#linux-pkg-rpm). 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 ``` ```bash 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](https://activemq.apache.org/activemq-5011000-release) - [How to install ActiveMQ on RHEL 8](https://linuxconfig.org/how-to-install-activemq-on-redhat-8) ```bash # 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 ``` :::warning - http://karaf.apache.org/ - https://activemq.apache.org/osgi-integration - https://activemq.apache.org/examples ::: ## Monitoring - [Monitoring broker runtime data using Prometheus](https://access.redhat.com/documentation/en-us/red_hat_amq/7.6/html/deploying_amq_broker_on_openshift/assembly_br-broker-monitoring_broker-ocp) - [JMX Exporter](https://github.com/prometheus/jmx_exporter)  - [How to Monitor MySQL Deployments with Prometheus & Grafana at ScaleGrid](https://scalegrid.io/blog/how-to-monitor-mysql-deployments-with-prometheus-and-grafana-at-scalegrid/) ### jmx_exporter - [Architecture and Monitoring Apache ActiveMQ with Grafana](https://www.metricfire.com/blog/architecture-and-monitoring-apache-activemq-with-grafana/) - [How to Use Prometheus Monitoring With Java to Gather Data](https://www.openlogic.com/blog/prometheus-java-monitoring-and-gathering-data) - [Github: prometheus/jmx_exporter](https://github.com/prometheus/jmx_exporter) ### mysqld_exporter ```sql 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](https://github.com/prometheus/mysqld_exporter/blob/master/README.md) instructs that we compile the exporter from source: ```bash 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: ```bash 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 ``` :::warning 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. :::info 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" ``` ::: :::warning 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: ```bash 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*. :::danger 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. ::: ```bash sudo docker network inspect podman ``` In my case, I have: ```json { "gateway": "10.88.0.1", "subnet": "10.88.0.0/16" } ``` According to the [offical documentation](https://dev.mysql.com/doc/refman/5.7/en/account-names.html), 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. ```sql 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>'; ``` :::info I grant access to whole subnet as, I won't configure static IPs for containers. ::: ```bash 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. ```bash curl -s localhost:8081/metrics | grep "mysql_up 1" ``` ### ActiveMQ Pub/Sub [GitHub: tomitribe/JMS-1.1-PubSub-Queue-Example-with-ActiveMQ](https://www.tomitribe.com/blog/5-minutes-or-less-activemq-with-jms-queues-and-topics/) ## Prometheus ```bash 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: ```yaml 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: ```bash 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 ```bash sudo docker network inspect podman ``` You should see something like: ```json { "gateway": "10.88.0.1", "subnet": "10.88.0.0/16" } ``` #### For docker ```bash 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](https://dev.mysql.com/doc/refman/5.7/en/account-names.html), 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: ```bash mysql -u root -p ``` Then, run these commands: ```sql 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 ```bash 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: ```bash 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. :::spoiler JMX exporter is meant to be run as a javaagent, e.g., ```bash java -javaagent:<agent> -jar <program> ``` I read about dynamically loaded javaagent. With this in mind, I experimented with [jattach](https://github.com/apangin/jattach). ```bash jattach <pid> load jmx_prometheus_javaagent-0.15.0.jar true <jmx_exporter_port> jmx_exporter/example_configs/activemq.yml ``` *<pid>* is determined by: ```bash ${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: ```yaml 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). ```bash 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., ```bash apache-activemq-5.11.0/bin/activemq stop ``` Edit the file `apache-activemq-5.11.0/bin/env` by changing ```bash ACTIVEMQ_OPTS="$ACTIVEMQ_OPTS_MEMORY -Djava.util.logging.config.file=logging.properties -Djava.security.auth.login.config=$ACTIVEMQ_CONF/login.config" ``` to ```bash 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`.