Basic introduction to Prometheus (Part 2)
Disclaimer: this is a continuation of my previous article where I decided to jot down my own discovery into Prometheus. Familiarity with PHP and an elementary understanding of database replication is helpful for following along, but not essential.
The goal of this article is to set up a basic alert and see it fire successfully.
Using the Database to find something “alertable”
In this instance, I’ve already got two MariaDB databases running as follows:
root@linuxpc:~# maxctrl list servers
┌─────────┬──────────────┬──────┬─────────────┬─────────────────┬───────────┬─────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├─────────┼──────────────┼──────┼─────────────┼─────────────────┼───────────┼─────────────────┤
│ server1 │ 192.168.2.37 │ 3306 │ 0 │ Master, Running │ 0-1-21019 │ MariaDB-Monitor │
├─────────┼──────────────┼──────┼─────────────┼─────────────────┼───────────┼─────────────────┤
│ server2 │ 192.168.2.99 │ 3306 │ 0 │ Slave, Running │ 0-1-21019 │ MariaDB-Monitor │
└─────────┴──────────────┴──────┴─────────────┴─────────────────┴───────────┴─────────────────┘
A simple, alertable thing could be replication lag. Measure how far in seconds server2 is behind server1, and emit an alert if the average of replication lag over the past 5 minutes stays above 10 seconds, for at least one minute. That’s a bit convoluted on a surface level, but emitting an alert for a small blip might get noisy, so it’s more sensible to emit an alert if the replication average has consistently stayed high.
This approach could end up masking or delaying cases where a very sudden spike appears, so to catch that, a separate alert looking for raw high values of replication lag might make sense. But for this exercise, the focus will stay on the average lag over time.
Measuring replication lag, and average of replication lag over 5 minutes
There are many ways to do it, and the example here is deliberately kept simple and sub-optimal, to focus on the mechanics of Prometheus, rather than the best exact way to measure replication lag, so we’ll keep it simple by creating a table with one row that stores the curent unix timestamp:
CREATE TABLE `replica_timestamp_table` (
`timestamp` bigint(20) DEFAULT NULL
) ENGINE=InnoDB;
This can be updated every second. If replication is lagging, the value in this table should get more and more out of date on the replica, relative to the value in the master database. So for calculating the lag on the replica, we can take a unix timestamp in bash, deduct the value of this table from it.
If we want to keep a historical record of these values, we should create another table:
CREATE TABLE `replica_timestamp_history` (
`timestamp` int(11) NOT NULL,
`lag` int(11) NOT NULL,
PRIMARY KEY (`timestamp`,`lag`)
) ENGINE=InnoDB;
And insert the current timestamp of the server, and the value of the lag in seconds back into it, every second. This gives us an easy way to query the average of the replica lag over the last 5 minutes as follows:
SELECT AVG(lag) AS replica_lag_average_over_5_mins FROM replica_timestamp_history WHERE timestamp >= (SELECT MAX(timestamp) FROM replica_timestamp_history) - 300;
For now, the core of the setup boils down to a simple bash script running on the master server every minute from crontab:
#!/bin/bash
for((x=0; x<60; x+=1)); do
echo "UPDATE replica_timestamp_table SET timestamp=$(date +%s);" | mysql -u root -A test
LAG=$(echo "SELECT UNIX_TIMESTAMP() - timestamp AS replica_lag_seconds FROM test.replica_timestamp_table;" | mysql -u devel -pdevel -h192.168.2.99 --skip-column-names -A test)
echo "INSERT INTO replica_timestamp_history (timestamp, lag) VALUES ("$(date +%s)", "$LAG");" | mysql -u root -A test
sleep 1
done
The first statement simply updates the timestamp on the master that we can then compare against in the replica. If there’s a replication lag, updates of this value won’t reach the replica server, so the calculation in the second statement will give the (approximate) lag in seconds.
The third statement simply saves the value of the lag back into the master, since the master is the only server that can be meaningfully queried while there is an ongoing replication lag. For being able to calculate averages, we have to rely on the master, since replica_timestamp_history itself would be out of date on the replica during a replication lag situation.
Setting up the Monitoring
Before any alerting can take place, we need to set up the monitoring of these values we’ve just set up. In the previous article, we’ve established that we can use apache to expose php files as endpoints for Prometheus to scrape, so given that, we can simply drop a PHP file mariadb_replication.php into /var/www/html/prometheus_lab:
<?php
header('Content-Type: text/plain; version=0.0.4; charset=utf-8');
$mysqli = new mysqli("127.0.0.1", "devel", "devel", "test");
if ($mysqli->connect_errno) {
echo "# HELP mariadb_replication_up 1 if connection succeeded, 0 otherwise\n";
echo "# TYPE mariadb_replication_up gauge\n";
echo "mariadb_replication_up 0\n";
exit;
}
$sql = <<<SQL
SELECT UNIX_TIMESTAMP() - `timestamp` AS replica_lag_seconds FROM test.replica_timestamp_table;
SQL;
$res = $mysqli->query($sql);
if (!$res) {
echo "# HELP mariadb_replication_query_ok 1 if query succeeded, 0 otherwise\n";
echo "# TYPE mariadb_replication_query_ok gauge\n";
echo "mariadb_replication_query_ok 0\n";
exit;
}
echo "# HELP mariadb_replication_query_ok 1 if query succeeded, 0 otherwise\n";
echo "# TYPE mariadb_replication_query_ok gauge\n";
echo "mariadb_replication_query_ok 1\n\n";
$total = "NaN";
while ($row = $res->fetch_row()) {
if ($row[0] !== null) {
$total = $row[0];
}
}
echo "\n# HELP mariadb_replica_lag_seconds Estimated MariaDB replica lag \n";
echo "# TYPE mariadb_replica_lag_seconds gauge\n";
echo "mariadb_replica_lag_seconds $total\n";
$mysqli->close();
$sql = <<<SQL
SELECT AVG(lag) AS replica_lag_average_over_5_mins FROM replica_timestamp_history WHERE timestamp >= (SELECT MAX(timestamp) FROM replica_timestamp_history) - 300;
SQL;
$mysqli = new mysqli("192.168.2.37", "devel", "devel", "test");
if ($mysqli->connect_errno) {
echo "# HELP mariadb_replication_up 1 if connection succeeded, 0 otherwise\n";
echo "# TYPE mariadb_replication_up gauge\n";
echo "mariadb_replication_up 0\n";
exit;
}
echo "# HELP mariadb_replication_up 1 if connection succeeded, 0 otherwise\n";
echo "# TYPE mariadb_replication_up gauge\n";
echo "mariadb_replication_up 1\n\n";
$res = $mysqli->query($sql);
if (!$res) {
echo "# HELP mariadb_replication_avg_over_5min_ok 1 if query succeeded, 0 otherwise\n";
echo "# TYPE mariadb_replication_avg_over_5min_ok gauge\n";
echo "mariadb_replication_avg_over_5min_ok 0\n";
}
echo "# HELP mariadb_replication_avg_over_5min_ok 1 if query succeeded, 0 otherwise\n";
echo "# TYPE mariadb_replication_avg_over_5min_ok gauge\n";
echo "mariadb_replication_avg_over_5min_ok 1\n";
$total = "NaN";
while ($row = $res->fetch_row()) {
if ($row[0] !== null) {
$total = $row[0];
}
}
echo "\n# HELP mariadb_replication_avg_over_5min Estimated MariaDB replica lag average over the last 5 minutes \n";
echo "# TYPE mariadb_replication_avg_over_5min gauge\n";
echo "mariadb_replication_avg_over_5min $total\n";
?>
The point here is that we need one connection to get the current replica lag on the replica server, we could potentially use this to create an alert for immediate jumps of this value, but we won’t.
And another connection to get the calculated value for the average of replica lag over the last 5 minutes. We will be building an alert based on this value.
Create the relevant configuration block in prometheus.yml:
- job_name: "mariadb_replication"
scrape_interval: 15s
static_configs:
- targets: ['localhost:80']
metrics_path: '/prometheus_lab/mariadb_replication.php'
Restart Prometheus, and check that the change took place as intended:
root@debian-test:/opt/prometheus/prometheus-3.9.1.linux-amd64# curl -s "http://localhost:9090/api/v1/query?query=mariadb_replica_lag_seconds" | read_response
{
"status":"success",
"data":
{
"resultType":"vector",
"result":
[
{
"metric":
{
"__name__":"mariadb_replica_lag_seconds",
"instance":"localhost:80",
"job":"mariadb_replication"
},
"value":
[
1771773024
107,
"1"
]
}
]
}
}
That brings us to the end of setting up the metrics. Time to get alerting.
Setting up our first Prometheus alert
Now that we have some “alertable” measurable, we can go right ahead with this:
In prometheus.yml we can ask it to interpret further yaml files, which is a nice way to keep all the alerting rules in their own structure:
rule_files:
- "rules.yml"
Then in the same directory, let’s define what goes into rules.yml:
groups:
- name: replication
rules:
- alert: ReplicaLagHigh
expr: mariadb_replication_avg_over_5min > 10
for: 1m
labels:
severity: warning
annotations:
summary: "Replica lag is high"
Basically, if the 5-minute average replica lag is above 10 seconds continuously for at least one minute, the alert transitions to “firing”.
Restart Prometheus, and check that this rule has taken effect:
root@debian-test:/opt/prometheus/prometheus-3.9.1.linux-amd64# curl -s http://localhost:9090/api/v1/rules | read_response
{
"status":"success",
"data":
{
"groups":
[
{
"name":"replication",
"file":"rules.yml",
"rules":
[
{
"state":"inactive",
"name":"ReplicaLagHigh",
"query":"mariadb_replication_avg_over_5min \u003e 10",
"duration":60,
"keepFiringFor":0,
"labels":
{
"severity":"warning"
},
"annotations":
{
"summary":"Replica lag is high"
},
"alerts":
[
],
"health":"ok",
"evaluationTime":0.000216958,
"lastEvaluation":"2026-02-22T15:59:20.963143738+01:00",
"type":"alerting"
}
],
"interval":15,
"limit":0,
"evaluationTime":0.000255047,
"lastEvaluation":"2026-02-22T15:59:20.963110309+01:00"
}
]
}
}
That is it. Now it is time to see if we can get it to trigger.
Triggering our first alert
In order for this alert to trigger, we need to induce a replication lag on server2. A simple way is via metadata locking. Start a transaction on server2 on some table like table_for_breaking_replica, using SELECT ... FOR UPDATE;, hold it open without COMMIT and send a TRUNCATE on this table on the master. While the SELECT query on server2 is running, the replication won’t be able to replay the incoming TRUNCATE, thereby inducing a replication lag effect.
So, on the replica:
MariaDB [test]> START TRANSACTION;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> SELECT * FROM table_for_breaking_replica FOR UPDATE;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
5 rows in set (0.000 sec)
Then, on the master:
(root@linuxpc) [test]> TRUNCATE TABLE table_for_breaking_replica;
Query OK, 0 rows affected (0.018 sec)
Then, just wait around a few minutes for these values to start climbing, and we should be able to verify that our alert triggers:
root@debian-test:/opt/prometheus/prometheus-3.9.1.linux-amd64# curl -s http://localhost:9090/api/v1/alerts | read_response
{
"status":"success",
"data":
{
"alerts":
[
{
"labels":
{
"alertname":"ReplicaLagHigh",
"instance":"localhost:80",
"job":"mariadb_replication",
"severity":"warning"
},
"annotations":
{
"summary":"Replica lag is high"
},
"state":"firing",
"activeAt":"2026-02-22T15:54:35.961938726Z",
"value":"5.00687e+01"
}
]
}
}
We can also check the raw metrics:
root@debian-test:/opt/prometheus/prometheus-3.9.1.linux-amd64# curl -s "http://localhost:9090/api/v1/query?query=mariadb_replica_lag_seconds" | read_response
{
"status":"success",
"data":
{
"resultType":"vector",
"result":
[
{
"metric":
{
"__name__":"mariadb_replica_lag_seconds",
"instance":"localhost:80",
"job":"mariadb_replication"
},
"value":
[
1771775737
637,
"143"
]
}
]
}
}
root@debian-test:/opt/prometheus/prometheus-3.9.1.linux-amd64# curl -s "http://localhost:9090/api/v1/query?query=mariadb_replication_avg_over_5min" | read_response
{
"status":"success",
"data":
{
"resultType":"vector",
"result":
[
{
"metric":
{
"__name__":"mariadb_replication_avg_over_5min",
"instance":"localhost:80",
"job":"mariadb_replication"
},
"value":
[
1771775747
342,
"41.6151"
]
}
]
}
}
And that is it! Alert successfully fired off.
Final Note
Prometheus comes equipped with a language specifically designed to query its time series database. The whole work in saving these historical replica lag values is in fact not strictly necessary for those who are familiar with PromQL. Instead of creating a calculated metric like mariadb_replication_avg_over_5min, one could use avg_over_time(mariadb_replica_lag_seconds[5m]) from PromQL to arrive at a similar result.
Conclusion
When it comes to alerting, Prometheus just evaluates the rules and tracks the state internally. Out of the box, there are no further actions taken, it is up to do something with the results of alerting, render it on web application, send it as an e-mail or Telegram message etc. This means that Prometheus gives a lot of flexibility not only on the monitoring side, but also on the alerting side.