PmaControl logo PmaControl
  • Home
  • PmaControl
    • AI Agents 13 on-premise agents
    • Plans Community, Cloud, On-Premise, Premium
    • Documentation Guides, API, architecture
    • Changelog Every stable 5.x release
    • Marketplace Community plugins
    • Clients 28+ companies
    • FAQ 25 questions / 7 categories
    Databases
    • MariaDB 37 articles
    • MySQL 15 articles
    • Galera Cluster 6 articles
    • MaxScale 4 articles
    • ProxySQL 3 articles
    • Amazon Aurora MySQL 0 article
    • Azure Database 0 article
    • ClickHouse 0 article
    • GCP CloudSQL 0 article
    • Percona Server 0 article
    • SingleStore 0 article
    • TiDB 0 article
    • Vitess 0 article
    Solutions
    • Support 24×7 MariaDB & MySQL emergencies
    • SQL Observability Monitoring, alerts, topology
    • High Availability Replication, failover, Galera
    • Disaster Recovery Backup, restore, RPO/RTO
    • Security & Compliance Audit, GDPR, SOC2
    • Migration & Upgrade Zero downtime, pt-osc, gh-ost
  • Plans
  • Resources
    • Documentation Technical guides & API
    • MySQL optimization center Markdown index, metrics, parameters, incidents
    • FAQ 25 frequently asked questions
    • Testimonials Client feedback & use cases
    • Blog Articles & insights
    • Roadmap Upcoming features
    Areas of expertise
    • SQL Observability Monitoring, alerts, Dot3 topology
    • High Availability Replication, failover, Galera
    • Security & Compliance Audit, GDPR, SOC2, ISO 27001
    • Disaster Recovery Backup, restore, RPO/RTO
    • Performance & Optimisation Digests, EXPLAIN, tuning
    • Migration & Upgrade Zero downtime, pt-osc
    Quick links
    • GitHub Wiki 26 pages — install, engine, plugins
    • Source code Official GitHub repository
    • Support 24×7 MariaDB & MySQL emergencies
    • Book a demo 30 min — real architecture
  • Support 24×7
  • Book a demo
Book a demo
🇫🇷 FR Français 🇬🇧 EN English 🇵🇱 PL Polski 🇷🇺 RU Русский 🇨🇳 ZH 中文 🇸🇦 AR العربية
← Back to blog

One MariaDB read to recognize an alert observation that has already been recorded

Published on November 21, 2026 By Aurélien LEQUOY
pmacontrol mariadb myrocks performance sql benchmark
Share X LinkedIn Facebook Email PDF

When PmaControl receives the same observation twice, it must recognize the duplicate to avoid opening or closing an incident twice. It used to ask MariaDB for the date of the latest observation and then look up the point it had just received. We removed the first read.

Across five real checks, median SQL read time for replay fell from 6.645 to 0.363 ms, a 95% reduction. In the lab, the median for the complete transaction, including locking and writes, fell from 2.554 to 0.904 ms. The application patch has been tested and awaits deployment.

The time-zone check, query by query

At 22:29:19 on 21 November, the time-zone alignment check reported an alert. What happens if that observation arrives again? The original implementation starts with the query below. The question mark stands for the check’s fingerprint and has the same value in both queries.

EXPLAIN PARTITIONS
SELECT observed_at
FROM pmc_alert_level_point
WHERE fingerprint = UNHEX(?)
ORDER BY observed_epoch DESC, observed_at DESC
LIMIT 1;

The caller used the presence of a row to decide whether history existed. It then looked up the exact observation and read its state. The chosen implementation starts here. A matching point and state identify a replay. A conflicting state at the same instant raises an error.

EXPLAIN PARTITIONS
SELECT is_open
FROM pmc_alert_level_point
WHERE fingerprint = UNHEX(?)
  AND observed_epoch = 1795296559
  AND observed_at = '2026-11-21 22:29:19.000000';

Plans captured on the server for this check. The first result column shows the removed read. The second shows the read that suffices for replay.

EXPLAIN PARTITIONS Latest date Exact point
Access (type) range const
Index (key) PRIMARY PRIMARY
Key bytes (key_len) 32 48
Reference (ref) — const,const,const
Estimated rows (rows) 8,617 1
Partitions in plan 45 1 (p740307)
Extra Using where; Using index —

Both queries use PRIMARY. The first fixes the 32-byte fingerprint and leaves 45 partitions in the plan. The second also fixes the epoch and date, covering all 48 bytes of the key. MariaDB can locate one key in the partition for that day.

The 8,617 in rows is an estimate. In ANALYZE FORMAT=JSON, the first query’s iterator returned one row (r_rows = 1, r_loops = 1), consistent with LIMIT 1. These counters do not measure internal partition probes. From 19 to 21 November the digest recorded 1,643,071 calls and 8 hours 8 minutes of cumulative SQL time.

Open all 20 MariaDB plans for the five checks (text)

The five checks in the benchmark

The digest page showed five executions of the same SQL with five different fingerprint values. Each value identifies one monitored condition. We recovered its rule name from the corresponding observation. The letters in parentheses are the identifiers used in the CSV files.

Check (CSV key) Rule code Alerting points / total
Performance Schema enabled (A) AdvisorPerformanceSchema 0 / 5,692
Time-zone alignment (B) AdvisorTimeZoneAlignment 5,694 / 5,695
Galera state transfer method (SST) (C) AdvisorWsrepSstMethod 0 / 5,692
Galera transaction applier threads (D) AdvisorWsrepSlaveThreads 0 / 5,692
Galera node name (E) AdvisorWsrepNodeName 0 / 5,692

All five replayed observations were dated 21 November at 22:29:19. Only the time-zone check was in alarm at that instant. Each line in the chart joins the original median to the median for the read path that starts with the exact point.

Median SQL read time by alert check, comparing two reads with one

Each check ran 50 times per version on the observed server. The last row pools all 250 measurements for each version. Both tables show SQL time in milliseconds.

Replaying an existing point · two reads down to one

Check Mean before Mean after Median before Median after
Performance Schema enabled 7.171 0.372 6.641 0.360
Time-zone alignment 8.808 0.400 8.346 0.362
Galera state transfer method (SST) 7.372 0.575 6.257 0.369
Galera transaction applier threads 6.970 0.389 6.379 0.366
Galera node name 7.132 0.374 6.295 0.362
All five checks 7.491 0.422 6.645 0.363

Adding a point to existing history · four reads down to three

Check Mean before Mean after Median before Median after
Performance Schema enabled 39.832 29.522 29.189 26.465
Time-zone alignment 41.518 32.461 35.533 29.123
Galera state transfer method (SST) 39.431 31.138 31.509 28.157
Galera transaction applier threads 36.855 31.667 33.790 25.943
Galera node name 39.696 31.043 35.195 28.511
All five checks 39.466 31.166 33.243 27.975

Why appending a point saves less time

A late observation can fall between two retained points and change an incident’s boundaries. On an exact-key miss the application therefore looks up the preceding and following points in (observed_epoch, observed_at) order. Finding either neighbor also proves that history exists.

These are the time-zone check’s two neighbor plans for the append parameter, dated 22 November at 22:29:19. Both reads still contribute to the cost after the latest-date query is removed.

Neighbor Access Index Partitions Estimated rows
Predecessor range PRIMARY 45 8,653
Successor range PRIMARY 45 81

V1 is the original implementation. V2 removes the latest-date lookup and uses the exact point and its neighbors. If all three reads miss, it checks for state left by a previous installation. In the first campaign, median restoration time rose from 9.665 to 12.963 ms because V2 searched for neighbors in a ledger it had just found empty.

V3, the chosen implementation, handles that case directly. When the ledger is empty and there is no legacy incident, it restores the single saved healthy point with its date, epoch and payload. It then checks the incoming point under the same lock.

Complete lab transaction, mean / median in milliseconds. The PHP timer includes lock acquisition, reads, writes, COMMIT and lock release.

Operation V1 · original V2 · probe removed V3 · direct restoration
Replay a known point 3.954 / 2.554 1.586 / 0.924 1.518 / 0.904
Append to existing history 23.907 / 17.297 23.516 / 13.954 22.895 / 14.017
Create the first point 28.373 / 10.477 20.031 / 8.689 22.708 / 8.660
Restore one legacy healthy point 23.609 / 10.422 29.137 / 13.423 17.151 / 7.916

The largest difference between V2 and V3 is in restoration. Their append medians are close. All three produced identical decisions, points, incidents, intervals and last states.

Can fingerprint be the primary key?

On 20 November we counted 543,719 points for 9,478 fingerprints. One fingerprint occurred 83 times that day. The fingerprint identifies a check. The triplet (fingerprint, observed_epoch, observed_at) identifies one observation of that check.

SHOW INDEX estimated 3,672,630 distinct values for the first key prefix and 2,448,420 for each longer prefix. Adding a column cannot lower the exact count. The inversion comes from estimates. On the daily-partitioned copy, PRIMARY KEY(fingerprint) raised error 1503 because every unique key must include observed_at. On an unpartitioned copy, a second observation with the same fingerprint raised duplicate-key error 1062. MariaDB · SHOW INDEX · MariaDB · Partitioning

Other ways to test whether history exists

Times cover all five checks. The illustrated plan is for time-zone alignment. An arrow marks the fallback to the full history on a miss. A dash denotes an unpartitioned table. Each series includes its own control.

Series and query Access / index Partitions Mean / median (ms)
I · Latest date range / PRIMARY 45 7.577 / 6.660
I · Latest date, FORCE INDEX (PRIMARY) ref / PRIMARY 45 4.927 / 4.283
I · SELECT 1 … LIMIT 1 ref / level_neighbour 45 6.361 / 5.434
I · SELECT EXISTS(…) ref / level_neighbour 45 6.870 / 5.625
I · Verify saved state in history, then fall back const / PRIMARY + const / PRIMARY → ref / level_neighbour — + 45 → 45 4.543 / 3.995
II · Latest date range / PRIMARY 45 6.401 / 5.623
II · Search the observation day, then fall back ref / level_neighbour_epoch → ref / level_neighbour 1 → 45 3.739 / 3.411
II · Read last state only const / PRIMARY — 0.277 / 0.257

The last-state table alone returned the same date in all 250 comparisons. It can retain state from an older release or outlive points removed by retention. On its own it cannot establish whether history needs rebuilding.

Read the SQL and the plans for every variant (text)

What a time index and partitioning change

To compare table layouts we ran the original ordered query on 621,865 identical points with MariaDB 11.8.10 and MyRocks. The plans are for time-zone alignment. The candidate time index is when_observed(observed_epoch, observed_at).

Layout Access / index Partitions in plan Mean / median (ms)
Composite key, daily partitions range / PRIMARY 45 612.119 / 549.937
With added time index range / PRIMARY 45 637.779 / 525.603
Forced time index index / when_observed 45 64.429 / 48.306
Unpartitioned table range / PRIMARY — 14.052 / 7.676
16 fingerprint partitions ref / PRIMARY 1 0.786 / 0.410

The optimizer kept PRIMARY for all five checks after adding the index. Forcing when_observed yields an index scan in date order until the fingerprint is found. Partitioning by fingerprint narrows the lookup to one of sixteen partitions and requires a different daily retention strategy.

The lab had one CPU and a 96 MiB MyRocks block cache. The baseline median was 1.350 ms on an initial 571,957-point copy and 549.937 ms on the extended copy. The difference between campaigns remains unexplained. These figures compare layouts within a single series. Production latency and migration cost require separate measurements.

Method and downloadable data

We froze the five examples shown together on the digest page. In each of 50 rounds, we shuffled parameter and variant order with a fixed seed, yielding 50 measurements per check and variant, 250 per variant. First runs and latency spikes are included. Server comparisons used read-only snapshots, an inactive query result cache and SHOW PROFILES.

Write transactions ran on separate 45-partition copies with all 28,463 historical keys for the five checks and synthetic alert payloads. Checks also covered late arrivals, a daylight-saving transition, conflicting points and retry after rollback. The archive includes individual measurements, SQL variants and full plans by check.

Download measurements, the five rule names and SQL plans (ZIP)

The choice for PmaControl

Return to the time-zone observation. Over 50 replays, mean read time fell from 8.808 to 0.400 ms, and the median fell from 8.346 to 0.362 ms. The decision depends on the check, the instant and the state already recorded. We keep V3 and the composite primary key for a single replay lookup and a history that can still accept the next point out of order.

Share X LinkedIn Facebook Email PDF
← Back to blog

Comments (0)

No comments yet.

Leave a comment

Paste a screenshot with Ctrl+V (Cmd+V on Mac) or choose an image.

Up to 3 images, 2 MiB per image.

PmaControl
contact@pmacontrol.com
About Privacy Legal notice GitHub Developers Contact
Do not wait for an incident to understand your architecture. © 2014-2026 PmaControl — 68Koncept