diff --git a/README.md b/README.md index 929d108..4b382ab 100644 --- a/README.md +++ b/README.md @@ -1,167 +1,167 @@ # Zabbix Template to monitor MariaDB sleep connections Welcome in the Zabbix Template to monitor MariaDB databases, especially the "SLEEP" connections. My use case of this template was: I'm the database administrator of a company with a damn Java webapp **consuming many database connections mostly in "SLEEP"** (!), and I want to understand how to tune the connection pools. With this template, you can monitor exactly this! ## Goals I've designed this Zabbix template to surgically **identify misconfigured connection pools** (e.g. Java connection pools) to mathematically **determine which databases are wasting too many connections** and **which database is safe to reduce their connection pool**, considering **worst-case scenario peak usages**, so, to decrease connection pools in a scientific way, not based on morning feelings, and **saving RAM**. Example goal: ability to say to your software developer similar phrases: > Hey dev, please, on the databases A, B, C, D, ... reduce the connection pool from 200 to 50, > since these databases **never** executed more than 40 non-sleep queries in the last active month, > and all the remaining time they were keeping 100+ "SLEEP" connections opened, wasting server resources and precious RAM. Example goal: I easily saved 10GB+ of RAM on a database server, thanks to this monitoring and the resulting tuning of the connection pool. I hope you the same, since RAM costs (because of the damn OpenAI). ## Features - monitor all "sleep" connections for each MariaDB database - auto-discovery of all MariaDB databases - ability to ignore some databases through Zabbix filters - receive email when a database has too many SLEEP connections for too long The repository consists in: 1. a Zabbix MariaDB Template (for the Zabbix server) * with a Zabbix "Discovery" for each database * with a Zabbix "Item prototype", to store the "sleep" connections for each database * with a Zabbix "Graph prototype", to visualize all "sleep" connections for each database * with a Zabbix "Trigger prototype", to receive an email when a database is consuming too many SLEEP connections (you can turn this off) 2. a systemd service unit, and its long-running script (all for the monitored host) ## Design Choices Our monitoring script was made in PHP8+, and not Bash, because PHP8+ is very good at interacting with MariaDB, it's really good at producing a valid JSON, and it's really good at executing custom unix commands in a safe way, that is, with **proper escaping** in every single mentioned aspect. Our monitoring script was made in PHP8+, and not in Python, and not in NodeJS, and not in Ruby, since only PHP8+ is able to create a readable script, in less of ~200 lines of code, comments included, and without any external dependency, without composer, without pip, without npm, etc. WARNING: If you dislike our solution, please, do not try to migrate this script to pure Bash, or /bin/sh. Reason: shell stuff like Bash or /bin/sh are NOT real programming languages and they are NOT able to easily produce a 100% valid JSON and they are not designed to run MariaDB queries in a safe way. Escaping these things is just a nightmare in Bash, adding additional complexity and unreadable code parts, introducing non-obvious potential security issues and other potentially nuclear mistakes, leading to a rubbish software and rubbish monitoring. So, really, pretty please, even if it's somehow suggested by the official Zabbix doc... avoid custom sh*t in Bash/sh if you want to produce or read a JSON, or to interact with production databases. ## Installation on the Zabbix Server -First, import the XML template in your Zabbix Server. The template has the name `zabbix_template_trapper_mariadb_sleep_connections-v4.xml`. It's called "4" because it was tested in Zabbix 4. +First, import the XML template in your Zabbix Server. The template has the name `zabbix_template_mariadb_sleep_connections-v4.xml`. It's called "4" because it was tested in Zabbix 4. Then, assign the template 'Template ER MariaDB' to a Zabbix Host (using the usual web interface). Done. The server does not need anything else. ## Installation on the monitored Host In the monitored host, install the dependencies (PHP8 command line interface): ``` sudo apt install php-cli php-mysql ``` Clone this repository in the directory `/opt` of the monitored host (replace '...' with the URL of this repository): ``` sudo git clone ... /opt/zabbix_template_mariadb_sleep_connections ``` Install the systemd unit: ``` sudo cp /opt/zabbix_template_mariadb_sleep_connections.service /etc/systemd/system/ ``` Startup the systemd unit: ``` systemctl daemon-reload systemctl enable --now zabbix_template_mariadb_sleep_connections.service ``` ## Advanced Configuration on the monitored Host These environment variables are available, for the monitored host systemd unit: * `ZABBIX_CONFIG` - path to your Zabbix Agent configuration file, for the Zabbix Sender software. * the default is `/etc/zabbix/zabbix_agentd.conf` * `DAEMON_SLEEP_SECONDS_INIT` - how many seconds should be waited before starting up monitoring MariaDB * the default is `120` seconds, so to mitigate the usual errors you may see when your application or your database is starting up * `DAEMON_SLEEP_SECONDS_LOOP` - how many seconds should be waited before each data transmission * the default is `10` seconds, so to run a MariaDB query only every these seconds * `DAEMON_DISCOVERY_EVERY_N_LOOPS` - premising that, at startup, the app discovers all databases; this configuration allows to configure how many consequent loops should pass before discovering again the list of known databases * the default is `100`, so to run a new databases list discovery only after this amount of loops (where every loop is executed every `DAEMON_SLEEP_SECONDS_LOOP` seconds) To specify these environment variables, just edit the systemd unit file in the monitored host: ``` sudo systemctl edit --full zabbix_template_mariadb_sleep_connections.service ``` And add your env variables, like this: ``` ... [Service] DAEMON_SLEEP_SECONDS_INIT=0 DAEMON_SLEEP_SECONDS_LOOP=1 ... ``` Then restart the service: ``` systemctl restart zabbix_template_mariadb_sleep_connections.service ``` ## MariaDB connection, username, password The monitoring script, as default, just automagically works in most situations:it does NOT need to know your MariaDB username, it does not need your MariaDB password. It works since it assumes that you have a MariaDB server running on the monitored host, and that the command `sudo mysql` works on the monitored host, using the standard "MariaDB socket file". If this is not your case, just hardcode your MariaDB username, password and hostname in the monitoring script. So, you can also monitor different external MariaDB servers. Monitoring external MariaDB servers was not our need, but code patches are very welcome, introducing some extra environment variables for your use case. ## Troubleshooting Inspect the Zabbix Host in your Zabbix Server. The new template must be assigned to the host. Inspect the "Latest Data" tab in your Zabbix Server. The new metrics about "MariaDB" should appear. Inspect the Zabbix Server configuration, visiting the Host, and the Host "Items". You should see many 'ER Database discovery: MariaDB database FOONAME: sleep connections'. You may see useful warnings nearby items. Monitor the systemd unit to look for connection errors to the database, or connection errors to Zabbix: ``` journalctl -xfu zabbix_template_mariadb_sleep_connections.service ``` Restart the systemd unit, if you make changes to the monitoring script: ``` systemctl restart zabbix_template_mariadb_sleep_connections.service ``` Inspect the Zabbix Proxy log file, and inspect the Zabbix Agent log file, to loop for errors. ## License The authors of this script, Valerio Bozzolan and ER Informatica, release the script since December 2025 under the same license of Zabbix: the GNU AGPL v3+, as required by Zabbix. Read all the Zabbix terms and conditions. https://www.zabbix.com/ Copyright (C) 2025 Valerio Bozzolan, ER Informatica, contributors This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more details. You should have received a copy of the GNU Affero General Public License along with this program. If not, see . diff --git a/zabbix_template_trapper_mariadb_sleep_connections-v4.xml b/zabbix_template_mariadb_sleep_connections-v4.xml similarity index 100% rename from zabbix_template_trapper_mariadb_sleep_connections-v4.xml rename to zabbix_template_mariadb_sleep_connections-v4.xml