diff --git a/README.md b/README.md
index 7ff7bf9..76b393f 100644
--- a/README.md
+++ b/README.md
@@ -1,174 +1,175 @@
# 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 amount of concurrent connections, also every second (!)
- monitor all "sleep" connections for each MariaDB database, also every second (!)
+- monitor the global config "max_connections"
- 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_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
```
You may want to also trust the repository, to avoid some warnings (I like the repo to be owned by root, so it's hardened: nobody can write but root).
```
sudo git config --global --add safe.directory /opt/zabbix_template_mariadb_sleep_connections
```
Install the systemd unit:
```
sudo cp /opt/zabbix_template_mariadb_sleep_connections/zabbix_monitor_mariadb_sleeps.service /etc/systemd/system/
```
Startup the systemd unit:
```
systemctl daemon-reload
systemctl enable --now zabbix_monitor_mariadb_sleeps.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 `6` 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_monitor_mariadb_sleeps.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_monitor_mariadb_sleeps.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:
```
sudo journalctl -xfu zabbix_monitor_mariadb_sleeps.service
```
Restart the systemd unit, if you make changes to the monitoring script:
```
sudo systemctl restart zabbix_monitor_mariadb_sleeps.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-send-mariadb-metrics.php b/zabbix-send-mariadb-metrics.php
index 5130208..e6d1eb8 100755
--- a/zabbix-send-mariadb-metrics.php
+++ b/zabbix-send-mariadb-metrics.php
@@ -1,222 +1,226 @@
#!/usr/bin/env php
0,
];
log_msg('INFO', "Waiting DAEMON_SLEEP_SECONDS_INIT=$DAEMON_SLEEP_SECONDS_INIT seconds for the initial warmup.");
sleep($DAEMON_SLEEP_SECONDS_INIT);
/* DATABASE SETUP START */
$mysqli = null;
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
/* DATABASE SETUP END */
/* DAEMON START */
$i = 0;
while (true) {
// Start again the database connection, if needed.
if (!$mysqli) {
$mysqli = mysqli_start();
}
// Run this always at startup.
// Run this also again after some loops.
// We reduce the amount of times we run the Discovery, as micro-optimization.
if (($i % $DAEMON_DISCOVERY_EVERY_N_LOOPS) === 0) {
// Run the Zabbix discovery of database names.
log_msg('INFO', "Zabbix Discovery of database names...");
zabbix_discovery_databases($mysqli);
log_msg('INFO', "Zabbix Discovery of database names... Completed");
+
+ // Store the "max connections" global limit.
+ $mariadb_max_connections = (int) query_row($mysqli, 'SELECT @@max_connections AS value')->value;
+ zabbix_sender_key_value('er.mariadb.maxconnections', $mariadb_max_connections);
}
// Send amount of current MariaDB connections.
$mariadb_connections = (int) query_row($mysqli, "SHOW STATUS WHERE variable_name = 'Threads_connected'")->Value;
zabbix_sender_key_value('er.mariadb.connections', $mariadb_connections);
// Count all 'Sleep' queries.
// Send the Zabbix items.
zabbix_send_database_sleeps($mysqli);
// Free the connection ASAP.
// Since, if you want to monitor connections,
// probably it means we don't have many free connections...
$mysqli->close();
$mysqli = null;
// Cleanup data
foreach ($databases_info as $database_name => $database_info) {
$databases_info[$database_name] = $databases_info_default;
}
$i++;
// Do not flood the MariaDB server.
log_msg('INFO', "Waiting DAEMON_SLEEP_SECONDS_LOOP={$DAEMON_SLEEP_SECONDS_LOOP} seconds for the next data collection #{$i}.");
sleep($DAEMON_SLEEP_SECONDS_LOOP);
}
/* DAEMON END */
/* FUNCTIONS START */
function zabbix_send_database_sleeps(mysqli $mysqli): void {
global $databases_info;
global $databases_info_default;
$results = query_results($mysqli, 'SHOW FULL PROCESSLIST');
foreach ($results as $row) {
$db = $row->db;
// Skip MariaDB "event schedulers".
if ($db === null) {
continue;
}
$databases_info[$db] ??= $databases_info_default;
$database_command_lower = strtolower($row->Command);
if ($database_command_lower === 'sleep') {
$databases_info[$db]['sleeps']++;
}
}
// Send all Zabbix values.
foreach ($databases_info as $database_name => $database_info) {
$zabbix_item_key = sprintf(
'er.mariadb.database[%s,sleeps]',
$database_name
);
zabbix_sender_key_value($zabbix_item_key, $database_info['sleeps']);
}
}
function zabbix_discovery_databases(mysqli $mysqli): void {
global $databases_info;
global $databases_info_default;
// Find all databases, for the Zabbix discovery.
$results = query_results($mysqli, 'SHOW DATABASES');
foreach ($results as $row) {
$db = $row->Database;
$databases_info[$db] ??= $databases_info_default;
}
// Populate the Zabbix Discovery, sending all database names in a single JSON.
// TODO: Run this less than once per hour.
$payload = [
'data' => [],
];
foreach ($databases_info as $database_name => $database_info) {
$payload['data'][] = [
'{#DB}' => $database_name,
];
}
$value = json_encode($payload);
zabbix_sender_key_value('er.mariadb.database.discovery', $value);
}
function query_results(mysqli $mysqli, string $query): Generator {
$result = $mysqli->query($query);
while ($row = $result->fetch_object()) {
yield $row;
}
$result->close();
}
function query_row(mysqli $mysqli, string $query): stdclass {
foreach (query_results($mysqli, $query) as $row) {
return $row;
}
throw new Exception("No results from this query:\n{$query}");
}
function zabbix_sender_key_value(string $key, string $value): int {
global $ZABBIX_CONFIG;
$command = sprintf('zabbix_sender -c %s --key %s --value %s',
escapeshellarg($ZABBIX_CONFIG),
escapeshellarg($key),
escapeshellarg($value)
);
// Capture the output of the next command, for logging purposes.
ob_start();
// Include the original command in the logger, and its output.
echo '$ ' . $command . "\n";
// Execute the command.
// The output is captured by 'ob_start()'.
$exit_status = null;
passthru($command, $exit_status);
$log_msg = ob_get_contents();
ob_end_clean();
if ($exit_status === 0) {
log_msg('DEBUG', $log_msg);
} else {
log_msg('ERROR', $log_msg);
}
return $exit_status;
}
function mysqli_start(): mysqli {
// Connect to MariaDB.
// Use 'null' as hostname to just use the Unix socket,
// so we can auto-magically login.
$mysqli = new mysqli(null, 'root');
$mysqli->set_charset('utf8mb4');
return $mysqli;
}
function getenv_or_default_int(string $envname, int $default): int {
$value = getenv_or_default($envname, (string)$default);
if (!is_numeric($value)) {
throw new Exception("Failed to parse ENV variable $envname. Received $value. Not an integer.");
}
return (int)$value;
}
function getenv_or_default(string $envname, string $default): string {
$value = getenv($envname);
if ($value === false) {
$value = $default;
}
log_msg('DEBUG', "Got ENV $envname={$value}.");
return $value;
}
function log_msg(string $severity, string $msg): void {
$severity = sprintf('%-5s', $severity);
$msg = trim($msg);
$lines = explode("\n", $msg);
foreach ($lines as $i => $line) {
if ($i) {
$line = " $line";
}
echo "[$severity] $line\n";
}
}
/* FUNCTIONS END */
diff --git a/zabbix_template_mariadb_sleep_connections-v4.xml b/zabbix_template_mariadb_sleep_connections-v4.xml
index 8c32e5f..cb3e93c 100644
--- a/zabbix_template_mariadb_sleep_connections-v4.xml
+++ b/zabbix_template_mariadb_sleep_connections-v4.xml
@@ -1,345 +1,401 @@
4.0
2025-12-18T11:44:54Z
ER
Template ER MariaDB sleeps
Template ER MariaDB sleeps
Template to monitor MariaDB "Sleep" database connections.
Repository in ER Informatica (to be used only if you work for ER Informatica):
https://sviluppo.erinformatica.it/source/zabbix_template_mariadb_sleep_connections/
Personal repository:
https://gitpull.it/source/zabbix_template_mariadb_sleep_connections/
ER
-
MariaDB connections
2
er.mariadb.connections
0
90d
365d
0
3
0
0
0
0
Amount of MariaDB threads currently running. That is, the amount of concurrent MariaDB connections.
0
3s
200
1
0
0
0
0
0
0
0
+ -
+ MariaDB max connections
+ 2
+
+
+ er.mariadb.maxconnections
+ 0
+ 90d
+ 365d
+ 0
+ 3
+
+
+
+
+ 0
+ 0
+
+ 0
+
+
+
+ 0
+
+
+
+
+
+ Maximum amount of MariaDB connections allowed in the server.
+This is usually obtained with 'SELECT @@max_connections AS value''.
+ 0
+
+
+
+
+
+ 3s
+
+
+
+ 200
+ 1
+ 0
+
+
+ 0
+ 0
+ 0
+ 0
+
+
+
+ 0
+ 0
+
+
ER Database discovery
2
er.mariadb.database.discovery
0
0
0
0
0
0
0
{#DB}
^(information_schema|mysql|performance_schema|sys|zabbix|zabbix_proxy)$
9
A
30d
MariaDB database {#DB}: sleep connections
2
er.mariadb.database[{#DB},sleeps]
0
90d
0
0
3
0
0
0
0
0
3s
200
1
0
0
0
0
0
0
0
{Template ER MariaDB sleeps:er.mariadb.database[{#DB},sleeps].min(6d)}>{$ER_MARIADB_TOO_MANY_SLEEP_CONNECTIONS}
0
MariaDB database {#DB}: too many sleep connections - consider reducing the connection pool
0
0
0
https://sviluppo.erinformatica.it/T2394
0
0
MariaDB database {#DB}: minimum sleep connections
1200
300
0.0000
100.0000
1
1
0
1
0
0.0000
0.0000
0
0
0
0
0
4
199C0D
0
7
0
-
Template ER MariaDB sleeps
er.mariadb.database[{#DB},sleeps]
MariaDB database {#DB}: sleep connections max/min
300
300
0.0000
0.0000
0
0
2
1
0
0.0000
0.0000
0
0
0
0
0
0
00FF00
0
4
0
-
Template ER MariaDB sleeps
er.mariadb.database[{#DB},sleeps]
1
0
FF8000
0
1
0
-
Template ER MariaDB sleeps
er.mariadb.database[{#DB},sleeps]
3s
200
1
0
0
0
0
0
0
{$ER_MARIADB_TOO_MANY_SLEEP_CONNECTIONS}
40
MariaDB concurrent connections
1200
300
0.0000
100.0000
1
1
0
1
0
0.0000
0.0000
0
0
0
0
0
4
199C0D
0
7
0
-
Template ER MariaDB sleeps
er.mariadb.connections