Applications continuously generate transactional, audit, logging, telemetry, and event data. Over time, these tables can grow to billions of rows, causing:

• Larger backups
• Longer recovery times
• Reduced buffer pool efficiency
• Slower index maintenance
• Increased storage costs
• Degraded query performance

To address these problems, organizations typically implement retention policies based on dates or timestamps. Examples include deleting events older than 90 days or purging session data older than 30 days and so forth. The deleted data can then eventually be archived somewhere else, like in another DBMS or on external files.

One of the most widely used tools for implementing these policies in MySQL ecosystems is pt-archiver, part of the Percona Toolkit.

This article provides a review of what pt-archiver is and how to use it, but in particular it focuses on the fact this tool is not partitioning aware, and this can make the deletion phase more costly. The article shows how to extend pt-archiver with a Perl plugin to make it aware of partitioning.

What is pt-archiver?

pt-archiver is a command-line utility from Percona Toolkit designed to:

• Archive rows from MySQL tables
• Purge rows from MySQL tables
• Move data between tables into the local database or a remote one
• Export rows into files

In a few words: implementing retention policies safely.

The tool processes rows incrementally in chunks, avoiding massive transactions and reducing impact on production systems.

Example:

pt-archiver \
  --source h=localhost,D=mydb,t=events \
  --where "created_at < '2026-05-01'" \
  --purge \
  --limit 1000 \
  --commit-each

This command:

• Scans rows matching the WHERE condition
• Processes them in chunks of 1000 rows
• Commits every chunk
• Deletes matching rows from the source table

pt-archiver provides several advantages compared to ad-hoc DELETE statements.

Instead of running:

DELETE FROM events
WHERE created_at < '2026-05-01';

which may:

• Lock rows for a long time
• Generate massive undo/redo logs
• Create replication lag
• Exhaust transaction logs

pt-archiver processes rows incrementally to make the process overhead less impactful for the database performance.

pt-archiver implementation permits flexible archival strategies

Rows can be copied to another table on a remote host, exported to files or removed completely

More details: ps://docs.percona.com/percona-toolkit/pt-archiver.html

Example: Copy rows to a remote archive table

The following example archives rows older than 90 days from a local table into an archive table hosted on a remote MySQL server:

pt-archiver \
  --source h=localhost,D=sales,t=orders,u=archiver,p=secret \
  --dest h=archive-server,D=archive,t=orders_archive,u=archiver,p=secret \
  --where "created_at < '2026-05-01'" \
  --limit 1000 \
  --commit-each \
  --progress 10000 \
  --statistics

In this example:

• –source defines the source table
• –dest defines the remote archive destination
• –where selects rows eligible for archival
• –limit controls batch size
• –commit-each commits every batch independently to reduce transaction overhead

–-progress reports progress every 10,000 rows

If rows should be removed from the source table after being copied, add –purge

Example: Export rows to a file

The following example exports rows older than one year into a text file:

pt-archiver \
  --source h=localhost,D=sales,t=orders,u=archiver,p=secret \
  --where "created_at < NOW() - INTERVAL 1 YEAR" \
  --file '/tmp/orders_archive_%Y-%m-%d.txt' \
  --output-format csv \
  --limit 1000 \
  --commit-each \
  --progress 10000 \
  --statistics

In this example:

• –file specifies the output file
• –-output-format csv exports rows in CSV format
• Date placeholders in the filename are expanded automatically

Rows can optionally be deleted from the source table by adding –purge

This allows pt-archiver to be used both for data retention and for offline archival workflows.

The Hidden Cost of DELETE Statements

Although pt-archiver is much safer than massive DELETE operations, it still fundamentally relies on DELETE statements.

This is a critical point.

Even when there are proper indexes, the rows are processed in chunks, and transactions are small; the large-scale DELETE operations remain expensive.

Deleting rows is expensive in InnoDB because it involves:

• Locating rows via indexes
• Modifying clustered indexes
• Modifying secondary indexes
• Generating undo logs
• Generating redo logs
• Purge thread processing
• Replication event generation
• Page fragmentation

When deleting billions of rows, the overhead becomes enormous.

Indexes help for sure, but only partially.

Consider:

DELETE FROM events
WHERE created_at < '2024-01-01';

If created_at is indexed, MySQL can efficiently locate rows.

However, locating rows efficiently is only part of the cost. The actual delete operations still require all those things we mentioned above.

At considerable scale, this becomes expensive.

Why RANGE Partitioning is Superior for Retention Policies

For time-based retention policies, partitioning is often dramatically more efficient. In particular, RANGE partitioning is very useful for these cases.

Example:

CREATE TABLE events (
    id BIGINT NOT NULL,
    created_at DATETIME NOT NULL,
    payload JSON,
    PRIMARY KEY(id, created_at)
)

PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202604 VALUES LESS THAN (TO_DAYS('2026-05-01')),
    PARTITION p202605 VALUES LESS THAN (TO_DAYS('2026-06-01')),
    PARTITION p202606 VALUES LESS THAN (TO_DAYS('2026-07-01'))
);

With partitioning, dropping old data becomes:

ALTER TABLE events DROP PARTITION p202604;

This operation is dramatically faster than running a DELETE.

Dropping a partition:

• Removes an entire physical partition
• Avoids row-by-row DELETE
• Avoids undo generation for each row
• Avoids secondary index maintenance per row
• Minimizes redo generation
• Is nearly metadata-only

This can remove millions or billions of rows in a matter of seconds without the same large cost of DELETE.

The Problem: pt-archiver is Not Partition-Aware

Unfortunately, pt-archiver does not automatically understand partitioning strategies.

Even if the table is partitioned or the retention policy perfectly matches partition boundaries, pt-archiver still executes DELETE statements.

Example:

pt-archiver \
  --where "created_at < NOW() - INTERVAL 90 DAY" \
  --purge

Internally, this still produces DELETE … instead of ALTER TABLE … DROP PARTITION …

This means organizations may lose the major operational benefits of partitioning, or they need to implement custom scripts for managing the selection of rows to copy using pt-archiver and then use DROP PARTITION separately from the tool. That is doable, and to be honest, not too complicated, but why not make pt-archiver aware of partitioning for some specific use cases?

Extending pt-archiver with Pulg-ins

Fortunately, pt-archiver supports Perl plug-ins.

A plug-in can do plenty of things. Like: inspect runtime conditions, interact with MySQL, override behaviors, and execute custom logic

This gives us an opportunity to implement partition-aware retention handling.

The plug-in can:

1. Inspect partition definitions
2. Analyze the WHERE condition
3. Determine which partitions are fully expired
4. Execute ALTER TABLE DROP PARTITION
5. Prevent row-by-row DELETE processing

This approach combines the scheduling/orchestration power of pt-archiver with the efficiency of partition pruning.

Plug-in Design

Our plug-in will:

• Connect using the pt-archiver DB handle
• Inspect INFORMATION_SCHEMA.PARTITIONS
• Identify partitions older than the retention cutoff
• Issue DROP PARTITION statements
• Log actions
• Skip DELETE processing

Assumptions:

• The table is RANGE partitioned
• Partitions are DATETIME based using the TO_DAYS() function to define ranges
• Partition naming convention contains dates
• Retention policy aligns with partition boundaries; if the plugin cannot determine a specific boundary, pt-archiver does nothing

Full Perl Plug-in for pt-archiver

package pt_archiver_partition_drop;

use strict;
use warnings;

sub new {
    my ($class, %args) = @_;
    my $self = {
        dbh        => $args{dbh},
        db         => $args{db},
        tbl        => $args{tbl},
        statistics => {},
    };

    bless $self, $class;
    return $self;
}

sub statistics {
    my ($self) = @_;
    return $self->{statistics};
}


sub before_begin {
    my ($self) = @_;
    my $dbh = $self->{dbh} or die "Missing dbh from pt-archiver\n";
    my $db  = $self->{db}  or die "Missing db from pt-archiver plugin args\n";
    my $tbl = $self->{tbl} or die "Missing tbl from pt-archiver plugin args\n";
    my $where  = _get_cmdline_option('where');
    my $dryrun = $ENV{PT_PARTITION_DROP_DRY_RUN} ? 1 : 0;

    die "Missing --where from original command line\n" unless $where;

    print "PLUGIN before_begin called\n";
    print "DB=$db TABLE=$tbl\n";
    print "WHERE=$where\n";
    print "PLUGIN_DRY_RUN=$dryrun\n";

    my ($column, $cutoff_date) = _parse_where($where);

    my $partitions = _get_partitions($dbh, $db, $tbl);

    if (!@$partitions) {
        print "Table `$db`.`$tbl` is not partitioned. Refusing DELETE.\n";
        exit(0);
    }

    my $partition_expr = $partitions->[0]->{expression};
    die "Missing PARTITION_EXPRESSION\n"
        unless defined $partition_expr && length $partition_expr;

    print "Partition expression: $partition_expr\n";

    my $cutoff_value = _evaluate_cutoff(
        $dbh,
        $partition_expr,
        $column,
        $cutoff_date,
    );

    print "Cutoff date: $cutoff_date\n";
    print "Cutoff boundary value: $cutoff_value\n";

    my $matched;

    for my $p (@$partitions) {
        next if !defined $p->{description};
        next if uc($p->{description}) eq 'MAXVALUE';

        if ($p->{description} == $cutoff_value) {
            $matched = $p;
            last;
        }
    }


    if (!$matched) {
        print "No exact partition boundary matches cutoff $cutoff_value. Refusing DELETE.\n";
        exit(0);
    }

    print "Matched boundary partition: $matched->{name}, position $matched->{position}\n";

    my @drop;

    for my $p (@$partitions) {
        next if !defined $p->{description};
        next if uc($p->{description}) eq 'MAXVALUE';

        if ($p->{position} <= $matched->{position}) {
            push @drop, $p->{name};
            print "Eligible for DROP: $p->{name}, boundary $p->{description}\n";
        }
    }

    if (!@drop) {
        print "No partitions eligible for DROP. Refusing DELETE.\n";
        exit(0);
    }

    my $sql = sprintf(
        "ALTER TABLE %s.%s DROP PARTITION %s",
        _quote_ident($db),
        _quote_ident($tbl),
        join(", ", map { _quote_ident($_) } @drop),
    );

    print "SQL: $sql\n";

    if ($dryrun) {
        print "PT_PARTITION_DROP_DRY_RUN enabled. Not executing DROP PARTITION.\n";
    }
    else {
        $dbh->do($sql);
        print "Dropped partitions: " . join(", ", @drop) . "\n";
    }

    $self->{statistics}->{partitions_dropped} = scalar @drop;

    exit(0);
}


sub _parse_where {
    my ($where) = @_;

    $where =~ s/^\s+|\s+$//g;

    die "Only WHERE format supported: created_at < 'YYYY-MM-DD'\n"
        unless $where =~ /^`?([A-Za-z0-9_]+)`?\s*<\s*'(\d{4}-\d{2}-\d{2})'\s*$/;

    return ($1, $2);
}

sub _evaluate_cutoff {
    my ($dbh, $partition_expr, $column, $cutoff_date) = @_;

    my $expr = $partition_expr;
    $expr =~ s/`//g;

    die "Partition expression does not reference column `$column`: $partition_expr\n"
        unless $expr =~ /\b\Q$column\E\b/i;

    $expr =~ s/\b\Q$column\E\b/'$cutoff_date'/ig;

    die "Unsafe generated expression: $expr\n"
        unless $expr =~ /^[A-Za-z0-9_\s\(\)\+\-\*\/,\.'":]+$/;

    my $sql = "SELECT $expr";

    print "Boundary evaluation SQL: $sql\n";

    my ($value) = $dbh->selectrow_array($sql);

    die "Cannot evaluate cutoff expression: $sql\n"
        unless defined $value;

    return $value;
}

sub _get_partitions {
    my ($dbh, $db, $tbl) = @_;

    my $sql = q{
        SELECT
            PARTITION_NAME,
            PARTITION_DESCRIPTION,
            PARTITION_EXPRESSION,
            PARTITION_ORDINAL_POSITION
        FROM INFORMATION_SCHEMA.PARTITIONS
        WHERE TABLE_SCHEMA = ?
          AND TABLE_NAME = ?
          AND PARTITION_NAME IS NOT NULL
        ORDER BY PARTITION_ORDINAL_POSITION
    };

    my $sth = $dbh->prepare($sql);
    $sth->execute($db, $tbl);
    my @partitions;

    while (my $row = $sth->fetchrow_hashref()) {
        push @partitions, {
            name        => $row->{PARTITION_NAME},
            description => $row->{PARTITION_DESCRIPTION},
            expression  => $row->{PARTITION_EXPRESSION},
            position    => $row->{PARTITION_ORDINAL_POSITION},
        };
    }

    return \@partitions;
}


sub _get_cmdline_option {

    my ($name) = @_;

    my $opt = "--$name";

    for (my $i = 0; $i < @ARGV; $i++) {
        if ($ARGV[$i] eq $opt && defined $ARGV[$i + 1]) {
            return $ARGV[$i + 1];
        }

        if ($ARGV[$i] =~ /^\Q$opt\E=(.*)$/) {
            return $1;
        }
    }

    if (open my $fh, '<', "/proc/$$/cmdline") {
        local $/;
        my $raw = <$fh>;
        close $fh;

        my @cmd = split /\0/, $raw;

        for (my $i = 0; $i < @cmd; $i++) {
            if ($cmd[$i] eq $opt && defined $cmd[$i + 1]) {
                return $cmd[$i + 1];
            }

            if ($cmd[$i] =~ /^\Q$opt\E=(.*)$/) {
                return $1;
            }
        }
    }

    return undef;
}



sub _quote_ident {

    my ($ident) = @_;

    die "Invalid identifier: $ident\n"
        unless defined $ident && $ident =~ /^[A-Za-z0-9_]+$/;

    return "`$ident`";
}

1;

Create the file named  pt_archiver_partition_drop.pm into the /usr/local/share/perl5 path.

Also set the environment variable PERL5LIB to let pt-archiver where to find the Perl package

export PERL5LIB=/usr/local/share/perl5

Example Usage

First, create the partitioned table events and insert some fake data.

DROP TABLE IF EXISTS events;


CREATE TABLE events (
  id BIGINT NOT NULL,
  created_at DATETIME NOT NULL,
  payload JSON DEFAULT NULL,
  PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202604 VALUES LESS THAN (TO_DAYS('2026-05-01')),
  PARTITION p202605 VALUES LESS THAN (TO_DAYS('2026-06-01')),
  PARTITION p202606 VALUES LESS THAN (TO_DAYS('2026-07-01')),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

INSERT INTO events (id, created_at, payload) VALUES

-- p202604
(1,  '2026-04-01 08:00:00', JSON_OBJECT('event', 'login',    'user', 'alice')),
(2,  '2026-04-03 09:15:00', JSON_OBJECT('event', 'view',     'page', 'home')),
(3,  '2026-04-05 10:30:00', JSON_OBJECT('event', 'click',    'button', 'signup')),
(4,  '2026-04-08 11:45:00', JSON_OBJECT('event', 'search',   'term', 'mysql')),
(5,  '2026-04-10 12:00:00', JSON_OBJECT('event', 'purchase', 'amount', 100)),
(6,  '2026-04-14 13:20:00', JSON_OBJECT('event', 'logout',   'user', 'alice')),
(7,  '2026-04-18 14:35:00', JSON_OBJECT('event', 'download', 'file', 'report.pdf')),
(8,  '2026-04-22 15:50:00', JSON_OBJECT('event', 'upload',   'file', 'image.png')),
(9,  '2026-04-26 16:05:00', JSON_OBJECT('event', 'click',    'button', 'buy')),
(10, '2026-04-30 23:59:59', JSON_OBJECT('event', 'month_end')),

-- p202605

(11, '2026-05-01 00:00:00', JSON_OBJECT('event', 'login',    'user', 'bob')),
(12, '2026-05-03 08:10:00', JSON_OBJECT('event', 'view',     'page', 'pricing')),
(13, '2026-05-06 09:20:00', JSON_OBJECT('event', 'search',   'term', 'percona')),
(14, '2026-05-09 10:30:00', JSON_OBJECT('event', 'purchase', 'amount', 250)),
(15, '2026-05-12 11:40:00', JSON_OBJECT('event', 'logout',   'user', 'bob')),
(16, '2026-05-16 12:50:00', JSON_OBJECT('event', 'download', 'file', 'backup.sql')),
(17, '2026-05-20 13:00:00', JSON_OBJECT('event', 'upload',   'file', 'data.csv')),
(18, '2026-05-24 14:10:00', JSON_OBJECT('event', 'click',    'button', 'subscribe')),
(19, '2026-05-28 15:20:00', JSON_OBJECT('event', 'view',     'page', 'docs')),
(20, '2026-05-31 23:59:59', JSON_OBJECT('event', 'month_end')),

-- p202606

(21, '2026-06-01 00:00:00', JSON_OBJECT('event', 'login',    'user', 'carol')),
(22, '2026-06-03 08:05:00', JSON_OBJECT('event', 'search',   'term', 'partitioning')),
(23, '2026-06-06 09:15:00', JSON_OBJECT('event', 'view',     'page', 'dashboard')),
(24, '2026-06-09 10:25:00', JSON_OBJECT('event', 'purchase', 'amount', 500)),
(25, '2026-06-12 11:35:00', JSON_OBJECT('event', 'logout',   'user', 'carol')),
(26, '2026-06-16 12:45:00', JSON_OBJECT('event', 'login',    'user', 'dave')),
(27, '2026-06-20 13:55:00', JSON_OBJECT('event', 'download', 'file', 'archive.zip')),
(28, '2026-06-24 14:05:00', JSON_OBJECT('event', 'upload',   'file', 'video.mp4')),
(29, '2026-06-28 15:15:00', JSON_OBJECT('event', 'click',    'button', 'checkout')),
(30, '2026-06-30 23:59:59', JSON_OBJECT('event', 'month_end')),

-- pmax
(31, '2026-07-01 00:00:00', JSON_OBJECT('event', 'login',    'user', 'eve')),
(32, '2026-07-05 08:30:00', JSON_OBJECT('event', 'view',     'page', 'future')),
(33, '2026-07-10 09:45:00', JSON_OBJECT('event', 'search',   'term', 'maxvalue')),
(34, '2026-08-01 10:00:00', JSON_OBJECT('event', 'purchase', 'amount', 750)),
(35, '2026-09-01 11:15:00', JSON_OBJECT('event', 'retained_future'));

Now you can run the following command to delete all rows before the 1st of May, which, by the way, matches the entire first partition in the table.

pt-archiver \
  --source h=localhost,D=mydb,t=events,m=pt_archiver_partition_drop \
  --where "created_at < '2026-05-01'" \
  --purge

Notice the Perl plugin must be indicated with the m option in the DSN string.

In practice:

• pt-archiver initializes
• The plug-in runs
• Partitions are dropped
• No DELETE statements are executed

Here is what you get from the execution of the above command:

PLUGIN before_begin called
DB=mydb TABLE=events
WHERE=created_at < '2026-05-01'
PLUGIN_DRY_RUN=0
Partition expression: to_days(`created_at`)
Boundary evaluation SQL: SELECT to_days('2026-05-01')
Cutoff date: 2026-05-01
Cutoff boundary value: 740102
Matched boundary partition: p202604, position 1
Eligible for DROP: p202604, boundary 740102
SQL: ALTER TABLE `mydb`.`events` DROP PARTITION `p202604`
Dropped partitions: p202604

You can simply verify the table has been managed correctly:

SELECT * FROM mydb.events;

SHOW CREATE TABLE mydb.events;

Now TRUNCATE the table and recreate the data and try now to specify the where conditions that match a RANGE that is not the first in the list of the boundaries.

pt-archiver \
  --source h=localhost,D=mydb,t=events,m=pt_archiver_partition_drop \
  --where "created_at < '2026-06-01'" \
  --purge

You should get:

PLUGIN before_begin called
DB=mydb TABLE=events
WHERE=created_at < '2026-06-01'
PLUGIN_DRY_RUN=0
Partition expression: to_days(`created_at`)
Boundary evaluation SQL: SELECT to_days('2026-06-01')
Cutoff date: 2026-06-01
Cutoff boundary value: 740133
Matched boundary partition: p202605, position 2
Eligible for DROP: p202604, boundary 740102
Eligible for DROP: p202605, boundary 740133
SQL: ALTER TABLE `mydb`.`events` DROP PARTITION `p202604`, `p202605`
Dropped partitions: p202604, p202605

In this case, two partitions have been identified and dropped.

Truncate the table and recreate the data again. Try now to provide a WHERE condition that does not match any of the boundaries in the RANGE.

pt-archiver \
  --source h=localhost,D=mydb,t=events,m=pt_archiver_partition_drop \
  --where "created_at < '2026-04-25'" \
  --purge

You get the following:

PLUGIN before_begin called
DB=mydb TABLE=events
WHERE=created_at < '2026-04-25'
PLUGIN_DRY_RUN=0
Partition expression: to_days(`created_at`)
Boundary evaluation SQL: SELECT to_days('2026-04-25')
Cutoff date: 2026-04-25
Cutoff boundary value: 740096
No exact partition boundary matches cutoff 740096. Refusing DELETE.

As expected, the tool now refuses to execute anything if it doesn’t find an exact match.

Operational Benefits

This approach provides major advantages.

Dropping partitions is vastly faster than deleting rows, and minimal binary logging is needed, compared to billions of row deletes. There is no massive transactional overhead for managing undo logs and purging. You get then a better InnoDB Buffer Pool stability because of less page churn.

In the end, retention jobs are completed quickly and consistently in a predictable way and at the minimal cost.

Important Caveats

Partition Boundaries Must Match Retention Policy

If partitions contain mixed retention windows, DROP PARTITION may remove too much data. For this reason, ensure correct partition design.

Recommended:

• daily partitions
• weekly partitions
• monthly partitions

aligned with business retention requirements.

Metadata Locks

ALTER TABLE DROP PARTITION still acquires metadata locks.

Test carefully in production.

Backup Awareness

Ensure dropped partitions are no longer needed before removal or use pt-archiver to also copy the data into a remote server or dump the data into a CSV file before running the DROP PARTITION.

Possible Enhancements

The plug-in can be extended further.

Potential improvements:

• Support for daily partitions
• Support for UNIX timestamp partitions
• Dry-run reporting
• Automatic partition creation
• Push Slack notifications
• Export Prometheus metrics
• Safety checks for replicas
• GTID-aware orchestration
• Integration with pt-online-schema-change workflows

These are just some ideas I had meanwhile doing my tests. What you can do by implementing a Perl plugin is only limited by your imagination and your real needs.

Conclusion

pt-archiver remains an excellent tool for implementing retention policies and archival workflows.

However, DELETE-based purging becomes increasingly expensive at scale, even with proper indexing and chunked processing.

For large time-series or historical datasets, RANGE partitioning is often a dramatically superior strategy.

The challenge is that pt-archiver does not natively leverage partition-level operations.

Fortunately, its Perl plug-in architecture allows advanced users to extend its behavior and implement partition-aware cleanup logic.

By combining:

• pt-archiver orchestration
• MySQL RANGE partitioning
• Custom Perl plug-ins

Organizations can achieve:

• Faster retention enforcement
• Lower operational overhead
• Smaller replication impact
• Dramatically improved scalability

For large MySQL deployments, this hybrid approach can turn multi-hour purge operations into near-instant metadata operations.

The use case presented in this article is limited to a specific scenario, but you can reuse it or customize it if you have a different kind of RANGE partitioning, for example, not using TO_DAYS().

Take this as just an example of how you can extend pt-archiver. What you can do for real is driven by your needs and/or only limited by your imagination.

More info about extending pt-archiver:
https://docs.percona.com/percona-toolkit/pt-archiver.html#extending

The post Extending pt-archiver with a Partition-Aware Plug-in for Fast Retention Policy Enforcement appeared first on Percona.

The problem

Recently I was refining the calculation I use in the MySQL calculator for Operator given I was constantly encountering a very serious problem with the Percona Server Operator.

The problem is that when the deployment was/is serving a high level of traffic, it will, no matter what, end up in getting OMMKill by the K8 system. 

This because the pod was gradually consuming more and more memory, reaching the memory limit set in the CR specification. 

Now let me clarify a few things, to get straight to the facts.

Kubernetes itself does not OOMKill a pod for hitting its memory limit, the mechanism works as described below with mention on how Working Set Size (WSS) is calculated, and how OOMKills are triggered, and in the resource sections, the links to the official documentation and source code.

1. The Reality of OOMKills vs. Kubelet Evictions

It is crucial to distinguish between what the Linux kernel does and what Kubernetes does:

• OOMKilled (Exit Code 137): This is executed entirely by the Linux kernel’s OOM Killer, not Kubernetes. When we set a memory limit in our Pod spec, Kubernetes translates that into a Linux cgroup constraint (memory.limit_in_bytes for cgroups v1, or memory.max for cgroups v2). If our container attempts to allocate more memory than this hard limit, and the kernel cannot reclaim any page cache (like inactive files), the kernel directly intervenes and terminates the process.
• Node-Pressure Evictions: This is where Kubernetes actively observes memory. The kubelet monitors the working_set_bytes metric to protect the node from running out of memory. If the node’s memory drops below an eviction threshold, Kubernetes will actively evict pods to prevent the kernel from initiating a system-wide OOM kill.

2. How Working Set Size (WSS) is Calculated for the container

Kubernetes monitors container memory via cAdvisor, which is integrated directly into the kubelet. cAdvisor calculates the Working Set Size by taking the total memory usage and subtracting the inactive file cache (memory that the kernel can easily reclaim if it faces memory pressure).

Because active file caches and anonymous memory (like our application’s heap) cannot be easily evicted, this working set metric is the most accurate representation of the memory your container is forcing the system to hold.

The Calculation & cgroups Evolution The core mathematical calculation is Memory Usage – Inactive File Cache, but how cAdvisor fetches this data from the Linux kernel depends entirely on your node’s cgroup version. Modern cAdvisor relies heavily on the opencontainers/runc/libcontainer library to read these raw cgroup files:

• cgroups v1: cAdvisor starts with the raw usage from memory.usage_in_bytes and subtracts the reclaimable cache found under the total_inactive_file key.
• cgroups v2 (Unified): cAdvisor starts with the raw usage from memory.current and subtracts the reclaimable cache found under the inactive_file key.

The Underlying Code Logic While older versions used a static setMemoryStats function, modern Kubernetes branches handle this dynamically. The logic executes the following flow before reporting back to the kubelet:

1. Detects Version: It identifies whether the node runs cgroups v1 or v2 to determine the correct inactive file key name.
2. Fetch Usage: It pulls the raw memory usage from the container.
3. Subtract Cache: It looks up the inactive file value and safely subtracts it from the usage (including a safeguard to ensure the working set never drops below zero).
4. Report Metric: It sets this final calculated value as container_memory_working_set_bytes, which the kubelet then uses to decide if the node is under memory pressure.

Back to us 

At the end the point is that if our pod reaches the limit and we ARE NOT using the new swap feature existing in Kubernetes, our pod will be brutally killed, and in 99% of the cases our production will suffer a lot. !Ops spoiler!

To clearly understand what was causing the issue about this memory consumption and having my calculator fail, I started to collect the information about the memory usage in MySQL itself.

SELECT EVENT_NAME,CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024 AS current_usage_mb FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME like ‘memory/%’ and EVENT_NAME not like ‘memory/performance%’  order by current_usage_mb desc limit 25;

Which will give you and output like this:

+---------------------------------------+------------------+
| EVENT_NAME                            | current_usage_mb |
+---------------------------------------+------------------+
| memory/innodb/buf_buf_pool            |   46398.92578125 |
| memory/group_rpl/GCS_XCom::xcom_cache |    1066.66179943 |
| memory/group_rpl/certification_info   |      92.45250702 |
| memory/innodb/log_buffer_memory       |      64.00096130 |
| memory/sql/TABLE                      |      49.90627003 |
| memory/innodb/memory                  |      34.68734741 |
| memory/innodb/ut0link_buf             |      24.00006104 |
| memory/innodb/lock0lock               |      21.40064240 |
| memory/mysqld_openssl/openssl_malloc  |       9.51009655 |
| memory/innodb/read0read               |       8.19496155 |
| memory/mysys/KEY_CACHE                |       8.00215149 |
| memory/innodb/sync0arr                |       7.03147125 |
| memory/innodb/ha_innodb               |       6.87006950 |
| memory/innodb/lock_sys                |       5.25009155 |
| memory/sql/log_sink_pfs               |       5.00003052 |
| memory/innodb/ut0pool                 |       4.00017548 |
| memory/sql/dd::objects                |       2.83031464 |
| memory/innodb/std                     |       2.72618866 |
| memory/innodb/os0file                 |       2.63054657 |
| memory/innodb/os0event                |       2.34302521 |
| memory/sql/TABLE_SHARE::mem_root      |       2.31734467 |
| memory/innodb/trx0trx                 |       2.22647858 |
| memory/temptable/physical_ram         |       1.00003052 |
| memory/sql/dd::String_type            |       0.94942093 |
| memory/innodb/btr0pcur                |       0.89743423 |
+---------------------------------------+------------------+

Plus I used PMM to collect memory information 

To simulate the load I used the sysbench-tpcc (tpc-c derivate test) variant and run the tests simulating a load of 1024 threads against a cluster based on machine with 16 Core and 64Gb volumes ~3k IOPS, so not gigantic but not small. 

The finding was almost immediate:

+---------------------------------------+------------------+
| EVENT_NAME                            | current_usage_mb |
+---------------------------------------+------------------+
| memory/innodb/buf_buf_pool            |   46398.92578125 |
| memory/group_rpl/certification_info   |    1431.67934418 | <constantly increasing
| memory/group_rpl/GCS_XCom::xcom_cache |    1066.63542366 |
| memory/sql/Gtid_set::Interval_chunk   |      95.52413940 |
| memory/innodb/log_buffer_memory       |      64.00096130 |
| memory/sql/TABLE                      |      48.17613125 |
| memory/innodb/memory                  |      35.08897400 |
| memory/innodb/ut0link_buf             |      24.00006104 |
| memory/innodb/lock0lock               |      21.40064240 |
| memory/innodb/read0read               |      14.86782837 |
| memory/mysqld_openssl/openssl_malloc  |      12.05916119 |
| memory/mysys/KEY_CACHE                |       8.00215149 |
| memory/innodb/sync0arr                |       7.03147125 |
| memory/innodb/ha_innodb               |       6.84074974 |
| memory/innodb/lock_sys                |       5.25009155 |
| memory/sql/log_sink_pfs               |       5.00003052 |
| memory/innodb/ut0pool                 |       4.00017548 |
| memory/sql/dd::objects                |       2.82012177 |
| memory/innodb/std                     |       2.72515869 |
| memory/innodb/os0file                 |       2.63054657 |
| memory/innodb/os0event                |       2.35884857 |
| memory/innodb/trx0trx                 |       2.22647858 |
| memory/sql/TABLE_SHARE::mem_root      |       1.83777618 |
| memory/innodb/trx0undo                |       1.26304626 |
| memory/mysys/lf_node                  |       1.08828735 |
+---------------------------------------+------------------+

Ok then … What is the certification info???

What is group_rpl/certification_info?

In MySQL, memory/group_rpl/certification_info is a Performance Schema memory instrument. It tracks the exact amount of RAM allocated to store the Certification Database (or Certification Info).

In Group Replication, nodes do not lock rows across the network while a transaction is executing. Instead, transactions execute locally and optimistically. When it is time to commit, the transaction undergoes a Certification Process to ensure no other concurrent transaction in the cluster has modified the exact same rows. The certification_info buffer is the in-memory hash map that makes this conflict detection possible.

1. What is it used for?

The certification_info structure acts as a tracking ledger for recently modified rows.

Here is how it works under the hood:

• The Key-Value Pair: It is fundamentally an in-memory dictionary. The key is the hash of a modified row (extracted from the transaction’s “write set”), and the value is the Global Transaction Identifier (GTID) of the transaction that successfully modified it.
• Conflict Detection: When a new transaction attempts to commit, it broadcasts its write set and the “snapshot version” of the database it saw when it started. The certifier cross-references the incoming transaction’s write set against the certification_info map.
• The Decision: If the certification_info shows that a row was modified by a newer GTID that the incoming transaction did not “see” when it started, a conflict is flagged, and the transaction is aborted. If no conflict exists, the transaction is certified, and the certification_info map is updated with the new write set and GTID.

The primary does not hold onto this memory out of stubbornness; it does so because purging that data too early would destroy the cluster’s consistency in the event of a failover.

In Group Replication, garbage collection for the certification_info buffer is not triggered just because a transaction commits on the primary. It is triggered by a concept called the Stable Set. 

Every node in the cluster periodically broadcasts a message to the rest of the group saying, “Here are the GTIDs I have successfully applied to my disk.” The cluster then calculates a global low watermark. This watermark is the highest transaction GTID that every single member of the group has successfully applied. Garbage collection is only allowed to purge write-sets from the certification database that fall below this global watermark.
To note that this purge is a synchronous operation during which writes are forbidden.

2. How the Apply Queue Stalls the Watermark

When a secondary node starts lagging, its applier queue grows. This means the secondary is receiving transactions from the network quickly, but its SQL thread is too slow to actually execute them and commit them to disk.

Because the secondary hasn’t applied these transactions, it cannot report those GTIDs back to the group as “finished.”

• The lagging secondary’s local watermark stalls.
• Therefore, the global low watermark for the entire cluster stalls.
• Because the global watermark hasn’t moved forward, the garbage_collect function on the primary (and all other nodes) says, “I am not allowed to delete any write-sets yet.”
• As the primary continues to process new writes, the certification_info memory buffer grows continuously.

3. Why the Primary Cannot Purge Early

we might wonder: If the transaction is already committed on the primary, why does the primary care if the secondary has applied it? Why not just drop the write-set from its own memory?

The answer comes down to Failover Safety and Distributed Conflict Detection. GR is a shared-nothing, decentralized architecture. Even if you are running in Single-Primary  mode (keep this in mind will be important later), the underlying engine uses the exact same logic as Multi-Primary mode. 

Here is why the primary is forbidden from purging that data:

• The Failover Scenario: Imagine our primary node crashes right now. The lagging secondary (which still has a massive apply queue) is immediately elected as the new primary.
• The Conflict Risk: As the new primary, it starts accepting new writes from your application. However, it still has thousands of old transactions in its applier queue that it hasn’t written to disk yet!
• The Necessity of the Buffer: When a new write comes in, the new primary must check if that write conflicts with any of the pending transactions in its apply queue. It does this by checking the certification_info map. If the old primary had purged the global certification data early, the new primary wouldn’t have the write-sets for those pending transactions. It would blindly accept the new write, causing a massive data conflict and breaking the replication group entirely.

Fine Marco, then what is the effect of this?

Well, drums roll …

… When a secondary node is elected as the new primary during a failover, it does not immediately open the floodgates to new writes. It keeps its super_read_only variable set to ON until it has completely drained its local apply queue of all transactions that were certified prior to the election.

This is an intentional design choice to guarantee that the new primary’s state is completely consistent with the old primary before it starts accepting new data.

4. Immediate Write Rejections (No Built-in Queuing)

The most critical impact to understand is that the new primary does not queue or pause new incoming writes while it catches up. It outright rejects them.

If our application or proxy routes a COMMIT, INSERT, UPDATE, or DELETE to the new primary while it is still processing the old queue, MySQL will immediately throw an error back to the client:

ERROR 1290 (HY000): The MySQL server is running with the –super-read-only option so it cannot execute this statement

5. The “Brownout” Window (Write Outage)

Because of this behavior, a failover in MySQL Group Replication does not instantly restore write availability. Our cluster experiences a “brownout”, a period where reads might succeed, but writes are entirely blocked.

The duration of this write outage is directly proportional to the size of the apply queue.

• If the secondary was fully caught up, write availability is restored in milliseconds.
• If the secondary was lagging by 50 minutes, your application will suffer a 50 minute write outage while the node applies the backlog.

6. Impact on Proxies (e.g., MySQL Router or ProxySQL)

If we are using a proxy layer to route your database traffic, the apply queue dictates how the proxy behaves during the transition:

• MySQL Router: It continuously monitors the cluster topology and the super_read_only flag. Even though the node has technically been elected primary, Router will not open the read-write port to it until the apply queue drains and super_read_only flips to OFF. Depending on your application timeouts, client connections will either hang waiting for a writable connection or fail completely.
• ProxySQL: Similar to Router, if it is configured to check for the read_only state, it will temporarily quarantine the new primary from the write hostgroup.
• HAProxy (in Operator): Monitor both Primary state and read_only state, but it expose the Primary to writes causing the application to fail (bug we need to fix)  

7. Read Traffic and Stale Data

During this catch-up phase, the node will accept incoming SELECT queries (since it is still a valid database). However, because it is actively churning through the old primary’s backlog, the data being read is temporarily stale.

If your application reads a row that is sitting in the apply queue but hasn’t been committed to disk yet, it will get the old version of that row.

Why Flow Control is Critical

Because a large apply queue turns a seamless failover into a severe, application-breaking write outage, Group Replication includes the Flow Control feature.

Flow Control monitors the size of the apply queues across all secondaries. If a secondary starts lagging too far behind, Flow Control should actively throttle the write throughput on the current primary to allow the lagging node to catch up. It is essentially a trade-off: we accept a slight performance hit during normal operations to guarantee that your database recovers almost instantly during a failover.

However, this is not what really happens.

1. It is Reactive, Not Proactive (The Polling Blind Spot)

Flow control does not intercept and evaluate every single transaction in real-time. Instead, it relies on a periodic polling interval governed by group_replication_flow_control_period (which defaults to 1 second).

Once a second, the cluster checks the size of the apply queues and the certifier queues.

• The Vulnerability: If our application generates a massive spike of 50,000 writes in 500 milliseconds, the primary will happily accept and certify all of them. Flow control will not even notice the spike until the next 1 second polling interval hits. By the time it decides to apply a throttle, the damage is already done, and the secondary’s queue is already overflowing.

2. The PID Controller’s “Soft Brake” Math

When flow control does decide to throttle, it does not simply freeze the primary. It uses a PID (Proportional-Integral-Derivative) controller algorithm to calculate a “write quota” (the maximum number of transactions the primary is allowed to commit in the next second).

The PID controller is deliberately tuned to be gentle. It wants to gracefully degrade performance rather than cause immediate application timeouts.

• When the secondary’s queue breaches the group_replication_flow_control_applier_threshold (default 25,000 transactions), the PID controller reduces the primary’s quota incrementally.
• The Failure Point: If the primary’s incoming write rate is astronomically higher than the secondary’s disk IO capacity, this incremental “step down” in the quota is too slow. The primary is still allowed to write, say, 10,000 transactions per second, while the secondary is only applying 2,000. The queue continues to grow aggressively despite the throttle being “active.”

3. The Concurrency Mismatch (Parallel vs. Serial)

This is often the silent killer that defeats flow control. Flow control makes mathematical assumptions about how fast the secondary should be able to apply transactions based on recent history.

However, the primary node might be executing writes using hundreds of highly concurrent threads. The secondary relies on the parallel applier to keep up. If the incoming workload suddenly includes transactions that cannot be parallelized, such as writes hitting overlapping rows, cascading foreign key updates, or DDL statements, the secondary’s applier instantly drops from executing in parallel down to a single, serialized thread.

When this serialization happens, the secondary’s applier rate plummets instantly. Flow control, which only checks in once a second and adjusts gradually, cannot brake the primary fast enough to compensate for the secondary suddenly dropping to a crawl.

What can we do?

At the moment of writing there are only two things that can be done.

1. Make Flow control more aggressive
2. Increase the number of replication appliers

1. Making Flow Control More Aggressive

We can configure Flow Control to be a bit more aggressive. It will still remain a suggestion but a strong one.

How it works (The Configuration):

• Lower the Threshold: By reducing group_replication_flow_control_applier_threshold (default is 25,000) to something like 1,000 or 500, we force the PID controller to kick in almost immediately when a spike occurs.
• Remove the Safety Net: By keeping  group_replication_flow_control_min_quota to 0 (default), we remove the minimum write guarantee. If the secondary falls behind, Flow Control is allowed to throttle the primary’s writes down to zero, also if this will never happen.
• Increase the Sensitivity: We can tweak the PID controller’s math (using the derivative and proportional tuning variables) to react much more aggressively to queue growth.
        group_replication_flow_control_hold_percent=100
        group_replication_flow_control_release_percent=5

The reality check, does it work?:

If the expectation is to have a rigid control over the applier queue on the lagging secondary, then the answer is NO. No matter what, at the moment flow control is not designed to act as we are used to in PXC (Percona Xtradb Cluster), where we have a rigid control of the pending queue also at the cost of delaying the writes. In Group Replication  the Flow Control will never bring the write to 0, the unfortunate aspect is that the mechanism is not enough to keep the queue under control.

2. Increasing Replication Appliers 

To help the secondary chew through the queue faster, we can increase the number of parallel threads it uses to write to disk.

How it works: We can increase the replica_parallel_workers (formerly slave_parallel_workers) setting. GR is exceptionally smart about this. Because of the certification process we discussed earlier, GR already knows exactly which transactions modify which rows. It uses a writeset-based dependency tracker to safely hand off non-conflicting transactions to multiple worker threads simultaneously.
The formula that is normally used to calculate the number of replication workers is to set 2.5 workers for each available core. IE if we have 14000m CPUs in our CR (K8) then we can assign ~35 workers, this is definitely higher than the default value of 4.   

The reality check, does it work?:  Yes, but only if our workload allows it.

• The Catch – The Serialization Wall: Parallel appliers only work if the transactions do not conflict. If our application has 50 concurrent threads all trying to update the same “inventory count” row, or updating a highly contentious table, those transactions cannot be parallelized. The secondary’s coordinator thread will see the row-level conflicts and force those transactions to wait in line and execute sequentially. We could allocate 128 parallel workers, but 127 of them will sit idle while one thread does all the work.
• The Catch – Context Switching: More threads do not magically create more disk IOPS. If we set the workers too high (e.g., beyond the physical CPU core count or disk IO capacity), the secondary’s InnoDB engine will spend more time context-switching and fighting over internal mutex locks than actually committing data. In many cases, over-allocating parallel workers actually slows down the apply rate.

Do we have any conclusions?

1. If HA is the goal, enforce Strict Flow Control

If our absolute top priority is High Availability, specifically achieving a near-zero Recovery Time Objective (RTO), we must configure an aggressive flow control.

• The Logic: Fast failovers require small apply queues. To guarantee a small apply queue, we must strictly throttle the primary the millisecond the secondary starts to lag.
• The Trade-off: we are protecting the cluster’s failover readiness at the expense of application write latency. If there is a massive write spike, our application will face timeouts and connection errors, but if the primary server suddenly catches fire, our database will recover and elect a new primary almost instantly.

The problem is that Group Replication is not able to act like that today, this is something we eventually need to implement to have better HA.

2. If Performance is the goal, relax Flow Control

If our top priority is keeping the application fast and ensuring COMMIT latencies remain extremely low, we should relax flow control or rely on the generous defaults.

• The Logic: By relaxing flow control, we allow the primary to run at the absolute maximum speed its local disks and CPU allow. It does not care if the secondaries fall behind. Our application users remain happy and experience zero throttling.
• The Trade-off: We are accepting severe risks to your HA posture. If the primary crashes while the secondaries have a massive apply queue, we will suffer a long write outage (the brownout) while the new primary catches up. Additionally, we are accepting the risk that the certification_info memory buffer will grow significantly on the primary and eventually have the pod OOMKilled .

3. Is this not what Asynchronous replication with semy-sync offers?

1. The Similarities

If we look purely at how a single transaction flows and how a failover behaves, GR and Semi-Sync look like twins:

• The Durability Guarantee: Semi-Sync: The primary waits to commit until at least one secondary confirms it has received the transaction and written it to its local Relay Log. 
  • GR: The primary waits to commit until a majority quorum of nodes confirm they have received the transaction, certified it, and written it to their local relay logs.
• The Failover Delay (The Queue):  In both systems, the secondary receiving the data does not mean the secondary has applied the data to its InnoDB tables.
  • If a crash happens, both systems require the new primary to completely execute its pending queue (Relay Log for Semi-Sync, Apply Queue for GR) before it is safe to accept new writes.

2. The Crucial Differences

If they behave so similarly, why use GR at all?
The differences lie entirely in automation, consensus, and split-brain protection. Semi-Sync is just a data transport mechanism; GR is a full state-machine cluster.

Here is what GR gives you that Semi-Sync does not:

• Automatic Election and Orchestration:
  • Semi-Sync: If the primary dies, Semi-Sync does nothing. The cluster sits there broken. You must rely on external tools (like Orchestrator or manual DBA intervention) to detect the crash, pick the most up-to-date secondary, wait for its relay log to apply, disable read_only, and re-point the application.
  • GR: The cluster detects the failure natively. The remaining nodes use Paxos consensus to elect a new primary automatically, manage the queue drain natively via the super_read_only flip we discussed, and self-heal.
• Split-Brain Protection (Network Partitions):
  • Semi-Sync: If our network splits in half, an external failover tool might accidentally promote a secondary while the old primary is still alive and accepting writes. We now have a split-brain, and our data is permanently corrupted.
  • GR: GR enforces strict quorum. If a network split happens, the side of the network with the minority of nodes will automatically fence itself off and refuse all writes. Split-brain is mathematically prevented.
• The Certification Database:
  • As we established, GR requires the certification map to ensure the new primary doesn’t accept writes that conflict with its unapplied queue. Semi-Sync does not have this; it relies entirely on the external failover tool to guarantee no writes touch the new primary until the relay log is 100% applied.

3. Final observation

If we are using Single-Primary GR with relaxed flow control, we have essentially built a highly-automated, consensus-driven version of Semi-Sync replication. 

We have the exact same apply-queue bottleneck during failover, but we have traded the need for external orchestrator tools for built-in Paxos consensus and native split-brain protection.

Conclusions (for real)

When we run MySQL on a traditional, dedicated Virtual Machine, memory limits are “soft.” If the certification_info database explodes and consumes an extra 10GB of RAM because of the applier lag, the Linux OS might start aggressively swapping inactive pages to disk, but the MySQL process usually survives. Performance degrades, but the database stays online.

In Kubernetes, memory limits are “hard.” As we discussed earlier, Kubernetes enforces pod memory limits via cgroups v2 (memory.max). The Linux kernel’s OOM Killer has no understanding of database quorum, failover states, or apply queues. It only sees math: Working Set Size > memory.max = Terminate Process (Exit Code 137).

The Chain Reaction of Relaxed Flow Control in k8s

If we prioritize “performance” by relaxing Flow Control in a Kubernetes environment, we are essentially setting a ticking time bomb. Here is the chain of events:

1. The Spike: Our application experiences a massive write spike.
2. The Queue: The secondary pod’s disk cannot keep up, and its applier queue grows to 1,000,000 transactions.
3. The Memory Sprawl: Because the queue is large, the global low-watermark stalls. The Primary pod is forbidden from garbage collecting the certification_info map. The in-memory hash map balloons in size.
4. The Execution: The memory.current metric will reach the memory.max, kernel will trigger the OMMKill process. First action will be to try to free the page.cache related to the process. If the purge is successful and the memory.current is less than memory.max then the process will persist, otherwise the kernel will kill it.
   We can use the WSS metric to predict a successful OMMKill.
   The Primary pod’s Working Set Size (WSS) breaches its Kubernetes memory limit, this is a fair estimate not an absolute value.
5. The Catastrophe: The Linux OOM Killer instantly assassinates the Primary MySQL process.

Because we tried to avoid a few seconds of write latency by keeping relaxed Flow Control, we inadvertently caused a hard crash of the primary database pod, with long write downtime.

The Architectural Law

Therefore, here is my statement as architectural law for containerized environments: In Kubernetes, High Availability and Pod stability are so intrinsically linked that Flow Control must act as hard as it can to cap the apply queue.

• We cannot allow unbounded memory growth in a container. The only way to bound certification_info memory is to bound the apply queue.
• The only way to bound the apply queue is with strict, aggressive Flow Control.
• Increasing the number of replication appliers helps but is not the conclusive answer.

In a Kubernetes environment, we must tune group_replication_flow_control_applier_threshold to a strict, low number, and accept that during massive traffic spikes, our application will experience write throttling. It is infinitely better for our application’s connection pool to wait 2 seconds for a COMMIT to succeed than for the primary database pod to be violently OOMKilled by the kernel, and have to wait for minutes or hours to recover write capabilities.

Note

Just as a mention this is exactly how Percona Operator with Percona Xtradb Cluster works. To be more specific, PXC and in general solutions based on Galera have a Flow Control mechanism that enforces the queue to be inside hard limits. While this more invasive control may be noticeable at application level, it guarantees that the other nodes are not lagging behind the primary and this is why it is a stronger HA solution in the Kubernetes environment.

Reference

https://github.com/Tusamarco/mysqloperatorcalculator

Managing Resources and OOMKills: Resource Management for Pods and Containers (This page details how memory limits are enforced reactively by the Linux kernel via OOM kills).

How WSS triggers Evictions: Node-pressure Eviction (This page explicitly details how the kubelet uses the memory.available signal, which is derived from node capacity minus the working set size).

Latest changes. Pointer to the code 

Swap Memory Management (Core Concepts & Configuration): https://kubernetes.io/docs/concepts/cluster-administration/swap-memory-management/

The post The Failover Brownout: Rethinking High Availability in MySQL Group Replication appeared first on Percona.

When moving MySQL to Kubernetes, the ultimate goal is simple: identify a safe, performant set of configuration values for your database pods. But where do you start? Usually, you look at your overall node resources say, a machine with 16 CPUs and 64GB of RAM.

In the old bare-metal days, you'd apply the standard rules of thumb:

• Set innodb_buffer_pool_size to 60-80% of total RAM to maximize caching.

• Allocate 1 innodb_buffer_pool_instances per 1GB of buffer pool.

• Match innodb_io_capacity to your drive speeds.

If you try applying these legacy rules in Kubernetes, your pod won't survive.

The Kubernetes Reality Check: OOMKills and Probe Traps

Why do the old rules fail? Because Kubernetes environments lack swap space. If a pod exceeds its assigned memory limit, Kubernetes executes an immediate, destructive action: an OOMKill.

Standard tuning rules don't account for the hidden memory consumers inside a K8s pod. You aren't just allocating memory for MySQL anymore; you have to share the pod's limits across running connections, the routing proxy, monitoring sidecars, and internal database processes.

For example, extensive testing reveals that Percona Server (PS) with Group Replication consumes about 9% to 11% more memory than Percona XtraDB Cluster (PXC) under the exact same load. If you blindly allocate 80% of your RAM to the buffer pool, that extra 10% overhead from Group Replication will push you right over the edge.

Memory isn't the only trap. During OLTP load testing (using sysbench TPC-C), pods can get killed before memory even peaks. The culprit? Kubernetes liveness and readiness probes. Under heavy load, a perfectly healthy database pod might take slightly longer to respond. If your probe timeouts are too short, K8s assumes the pod is dead and kills it with no questions asked.

Step 1: Discover Your Actual Resources

To avoid these pitfalls, you must know what resources you actually have before tuning anything. A 64GB node does not give you 64GB of pod memory. Cloud providers run system pods to manage the cluster, which silently consume your baseline resources.

Before applying any configurations, check your node:

kubectl describe node <nodename>

You might see something like this in the output:

  Resource           Requests    Limits
  --------           --------    ------
  cpu                702m (4%)   1200m (7%)
  memory             645Mi (1%)  1994Mi (3%)

In this scenario, 7% of the CPU and 3% of the memory are already spoken for. Your 16 CPUs and 64GB of RAM are actually closer to 14 CPUs and 58GB of usable memory. If you base your manual database tuning on the 64GB fantasy, you are already on a collision course with an OOMKill.

You could try to manually scale down your buffers to be "safe" (e.g., arbitrarily dropping the buffer pool to 50%), but then you sacrifice massive amounts of performance.

This is where the guessing game has to stop.

Enter the MySQL Operator Calculator

Built as a lightning-fast, RESTful Go service, the MySQL Operator Calculator is designed to take this exact math entirely out of your hands.

Instead of manually calculating overheads for proxies, monitors, and Group Replication, you simply feed the calculator your actual available pod resources and workload type. It dynamically computes the optimal, mathematically safe configuration parameters for your Kubernetes operator (such as the Percona Operator for MySQL).

Why You Need It in Your Toolkit:

• Say Goodbye to OOM Kills: The tool mathematically balances your total allocated memory across the three critical components of a modern K8s database pod: the mysql engine, the proxy layer, and the monitor agent.

• Workload-Aware Tuning: Simply tell the calculator your load type (Read-Heavy, Light OLTP, or Heavy OLTP), and it adjusts the buffers and threads accordingly.

• Automation: Designed with modern infrastructure in mind, the calculator outputs clean, structured json. You can easily curl the API from your CI/CD pipelines to automatically inject calculated configurations into your Helm charts.

• Auto-Calculated Connections: Not sure how many connections your memory limit can safely handle? Pass 0 for connections, and the tool will calculate the maximum safe threshold for you.

How It Works in Practice

Getting your optimized configuration is as simple as making an HTTP request. Let's say you have a heavy OLTP Percona XtraDB Cluster (PXC), you've identified you have exactly 4 CPUs and 2.5GB of RAM available, and you want the tool to figure out the max connections for MySQL 8.0.33. Just ask:

curl -i -X GET -H "Content-Type: application/json" -d '{
  "output": "human",
  "dbtype": "pxc",
  "dimension": {
    "id": 999,
    "cpu": 4000,
    "memory": "2.5G"
  },
  "loadtype": {"id": 3},
  "connections": 0,
  "mysqlversion": {"major": 8, "minor": 0, "patch": 33}
}' http://127.0.0.1:8080/calculator

Using the human output flag gives you a highly readable, my.cnf-style output, while the json flag provides structured data detailing the exact configuration section, calculated value, and the safe minimums/maximums used in the background math.

Ready to Stop Guessing?

Container orchestration is complex enough without having to manually calculate memory overheads on a calculator app at 2:00 AM during an outage. By programmatically determining your limits, you ensure your database remains stable, performant, and perfectly sized for its environment.

This is why I developed this tool, initially for my personal use, but I think it can be useful to others, so here we go:

Check out the source code, compile the binary, and start optimizing your clusters today by visiting the MySQL Operator Calculator on GitHub.

Or you can try it using the service at tusacentral.net:8080 like:

curl -i -X GET -H "Content-Type: application/json" -d '{

"output":"human",

"dbtype":"group_replication", 

"dimension": {"id": 999, "cpu": 16000, "memory": "64G"}, 

"loadtype": {"id": 3}, 

"connections":1500,

"mysqlversion":{"major":8,"minor":4,"patch":8},

"providercostpct":0.10}' http://tusacentral.net:8080/calculator

This is just for demo and cannot be used as reference for a service, please build your own server for that.

Of course the use of the settings generated is at your own risk, I am not taking any responsability in case they are not working, so test them over and over and see if they match your needs.

Also read the recent blogs https://tusacentral.net/joomla/index.php/mysql-blogs/265-group-replication-vs-percona-xtradb-cluster-the-true-cost-of-consistency and https://tusacentral.net/joomla/index.php/mysql-blogs/266-the-failover-brownout-rethinking-high-availability-in-mysql-group-replication
they are VERY important to understand what is going on in the operator especially the one using Grup Replication.

PR or issue requests are welcome.

The problem

Recently I was refining the calculation I use in the MySQL calculator for Operator given I was constantly encountering a very serious problem with the Percona Server Operator.

The problem is that when the deployment was/is serving a high level of traffic, it will, no matter what, end up in getting OMMKill by the K8 system. 

This because the pod was gradually consuming more and more memory, reaching the memory limit set in the CR specification. 

Now let me clarify a few things, to get straight to the facts.

Kubernetes itself does not OOMKill a pod for hitting its memory limit, the mechanism works as described below with mention on how Working Set Size (WSS) is calculated, and how OOMKills are triggered, and in the resource sections, the links to the official documentation and source code.

1. The Reality of OOMKills vs. Kubelet Evictions

It is crucial to distinguish between what the Linux kernel does and what Kubernetes does:

• OOMKilled (Exit Code 137): This is executed entirely by the Linux kernel's OOM Killer, not Kubernetes. When we set a memory limit in our Pod spec, Kubernetes translates that into a Linux cgroup constraint (memory.limit_in_bytes for cgroups v1, or memory.max for cgroups v2). If our container attempts to allocate more memory than this hard limit, and the kernel cannot reclaim any page cache (like inactive files), the kernel directly intervenes and terminates the process.
• Node-Pressure Evictions: This is where Kubernetes actively observes memory. The kubelet monitors the working_set_bytes metric to protect the node from running out of memory. If the node's memory drops below an eviction threshold, Kubernetes will actively evict pods to prevent the kernel from initiating a system-wide OOM kill.

2. How Working Set Size (WSS) is Calculated for the container

Kubernetes monitors container memory via cAdvisor, which is integrated directly into the kubelet. cAdvisor calculates the Working Set Size by taking the total memory usage and subtracting the inactive file cache (memory that the kernel can easily reclaim if it faces memory pressure).

Because active file caches and anonymous memory (like our application's heap) cannot be easily evicted, this working set metric is the most accurate representation of the memory your container is forcing the system to hold.

The Calculation & cgroups Evolution The core mathematical calculation is Memory Usage - Inactive File Cache, but how cAdvisor fetches this data from the Linux kernel depends entirely on your node's cgroup version. Modern cAdvisor relies heavily on the opencontainers/runc/libcontainer library to read these raw cgroup files:

• cgroups v1: cAdvisor starts with the raw usage from memory.usage_in_bytes and subtracts the reclaimable cache found under the total_inactive_file key.
• cgroups v2 (Unified): cAdvisor starts with the raw usage from memory.current and subtracts the reclaimable cache found under the inactive_file key.

The Underlying Code Logic While older versions used a static setMemoryStats function, modern Kubernetes branches handle this dynamically. The logic executes the following flow before reporting back to the kubelet:

1. Detects Version: It identifies whether the node runs cgroups v1 or v2 to determine the correct inactive file key name.
2. Fetch Usage: It pulls the raw memory usage from the container.
3. Subtract Cache: It looks up the inactive file value and safely subtracts it from the usage (including a safeguard to ensure the working set never drops below zero).
4. Report Metric: It sets this final calculated value as container_memory_working_set_bytes, which the kubelet then uses to decide if the node is under memory pressure.

Back to us 

At the end the point is that if our pod reaches the limit and we ARE NOT using the new swap feature existing in Kubernetes, our pod will be brutally killed, and in 99% of the cases our production will suffer a lot. !Ops spoiler!

To clearly understand what was causing the issue about this memory consumption and having my calculator fail, I started to collect the information about the memory usage in MySQL itself.

SELECT EVENT_NAME,CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024 AS current_usage_mb FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME like 'memory/%' and EVENT_NAME not like 'memory/performance%'  order by current_usage_mb desc limit 25;

Which will give you and output like this:

+---------------------------------------+------------------+
| EVENT_NAME                            | current_usage_mb |
+---------------------------------------+------------------+
| memory/innodb/buf_buf_pool            |   46398.92578125 |
| memory/group_rpl/GCS_XCom::xcom_cache |    1066.66179943 |
| memory/group_rpl/certification_info   |      92.45250702 |
| memory/innodb/log_buffer_memory       |      64.00096130 |
| memory/sql/TABLE                      |      49.90627003 |
| memory/innodb/memory                  |      34.68734741 |
| memory/innodb/ut0link_buf             |      24.00006104 |
| memory/innodb/lock0lock               |      21.40064240 |
| memory/mysqld_openssl/openssl_malloc  |       9.51009655 |
| memory/innodb/read0read               |       8.19496155 |
| memory/mysys/KEY_CACHE                |       8.00215149 |
| memory/innodb/sync0arr                |       7.03147125 |
| memory/innodb/ha_innodb               |       6.87006950 |
| memory/innodb/lock_sys                |       5.25009155 |
| memory/sql/log_sink_pfs               |       5.00003052 |
| memory/innodb/ut0pool                 |       4.00017548 |
| memory/sql/dd::objects                |       2.83031464 |
| memory/innodb/std                     |       2.72618866 |
| memory/innodb/os0file                 |       2.63054657 |
| memory/innodb/os0event                |       2.34302521 |
| memory/sql/TABLE_SHARE::mem_root      |       2.31734467 |
| memory/innodb/trx0trx                 |       2.22647858 |
| memory/temptable/physical_ram         |       1.00003052 |
| memory/sql/dd::String_type            |       0.94942093 |
| memory/innodb/btr0pcur                |       0.89743423 |
+---------------------------------------+------------------+

Plus I used PMM to collect memory information 

To simulate the load I used the sysbench-tpcc (tpc-c derivate test) variant and run the tests simulating a load of 1024 threads against a cluster based on machine with 16 Core and 64Gb volumes ~3k IOPS, so not gigantic but not small. 

The finding was almost immediate:

+---------------------------------------+------------------+
| EVENT_NAME                            | current_usage_mb |
+---------------------------------------+------------------+
| memory/innodb/buf_buf_pool            |   46398.92578125 |
| memory/group_rpl/certification_info   |    1431.67934418 | <constantly increasing
| memory/group_rpl/GCS_XCom::xcom_cache |    1066.63542366 |
| memory/sql/Gtid_set::Interval_chunk   |      95.52413940 |
| memory/innodb/log_buffer_memory       |      64.00096130 |
| memory/sql/TABLE                      |      48.17613125 |
| memory/innodb/memory                  |      35.08897400 |
| memory/innodb/ut0link_buf             |      24.00006104 |
| memory/innodb/lock0lock               |      21.40064240 |
| memory/innodb/read0read               |      14.86782837 |
| memory/mysqld_openssl/openssl_malloc  |      12.05916119 |
| memory/mysys/KEY_CACHE                |       8.00215149 |
| memory/innodb/sync0arr                |       7.03147125 |
| memory/innodb/ha_innodb               |       6.84074974 |
| memory/innodb/lock_sys                |       5.25009155 |
| memory/sql/log_sink_pfs               |       5.00003052 |
| memory/innodb/ut0pool                 |       4.00017548 |
| memory/sql/dd::objects                |       2.82012177 |
| memory/innodb/std                     |       2.72515869 |
| memory/innodb/os0file                 |       2.63054657 |
| memory/innodb/os0event                |       2.35884857 |
| memory/innodb/trx0trx                 |       2.22647858 |
| memory/sql/TABLE_SHARE::mem_root      |       1.83777618 |
| memory/innodb/trx0undo                |       1.26304626 |
| memory/mysys/lf_node                  |       1.08828735 |
+---------------------------------------+------------------+

Ok then … What is the certification info???

What is group_rpl/certification_info?

In MySQL, memory/group_rpl/certification_info is a Performance Schema memory instrument. It tracks the exact amount of RAM allocated to store the Certification Database (or Certification Info).

In Group Replication, nodes do not lock rows across the network while a transaction is executing. Instead, transactions execute locally and optimistically. When it is time to commit, the transaction undergoes a Certification Process to ensure no other concurrent transaction in the cluster has modified the exact same rows. The certification_info buffer is the in-memory hash map that makes this conflict detection possible.

1. What is it used for?

The certification_info structure acts as a tracking ledger for recently modified rows.

Here is how it works under the hood:

• The Key-Value Pair: It is fundamentally an in-memory dictionary. The key is the hash of a modified row (extracted from the transaction's "write set"), and the value is the Global Transaction Identifier (GTID) of the transaction that successfully modified it.
• Conflict Detection: When a new transaction attempts to commit, it broadcasts its write set and the "snapshot version" of the database it saw when it started. The certifier cross-references the incoming transaction's write set against the certification_info map.
• The Decision: If the certification_info shows that a row was modified by a newer GTID that the incoming transaction did not "see" when it started, a conflict is flagged, and the transaction is aborted. If no conflict exists, the transaction is certified, and the certification_info map is updated with the new write set and GTID.

The primary does not hold onto this memory out of stubbornness; it does so because purging that data too early would destroy the cluster's consistency in the event of a failover.

In Group Replication, garbage collection for the certification_info buffer is not triggered just because a transaction commits on the primary. It is triggered by a concept called the Stable Set. 

Every node in the cluster periodically broadcasts a message to the rest of the group saying, "Here are the GTIDs I have successfully applied to my disk." The cluster then calculates a global low watermark. This watermark is the highest transaction GTID that every single member of the group has successfully applied. Garbage collection is only allowed to purge write-sets from the certification database that fall below this global watermark. To note that this purge is a synchronous operation during which writes are forbidden.

2. How the Apply Queue Stalls the Watermark

When a secondary node starts lagging, its applier queue grows. This means the secondary is receiving transactions from the network quickly, but its SQL thread is too slow to actually execute them and commit them to disk.

Because the secondary hasn't applied these transactions, it cannot report those GTIDs back to the group as "finished."

• The lagging secondary's local watermark stalls.
• Therefore, the global low watermark for the entire cluster stalls.
• Because the global watermark hasn't moved forward, the garbage_collect function on the primary (and all other nodes) says, "I am not allowed to delete any write-sets yet."
• As the primary continues to process new writes, the certification_info memory buffer grows continuously.

3. Why the Primary Cannot Purge Early

we might wonder: If the transaction is already committed on the primary, why does the primary care if the secondary has applied it? Why not just drop the write-set from its own memory?

The answer comes down to Failover Safety and Distributed Conflict Detection. GR is a shared-nothing, decentralized architecture. Even if you are running in Single-Primary  mode (keep this in mind will be important later), the underlying engine uses the exact same logic as Multi-Primary mode. 

Here is why the primary is forbidden from purging that data:

• The Failover Scenario: Imagine our primary node crashes right now. The lagging secondary (which still has a massive apply queue) is immediately elected as the new primary.
• The Conflict Risk: As the new primary, it starts accepting new writes from your application. However, it still has thousands of old transactions in its applier queue that it hasn't written to disk yet!
• The Necessity of the Buffer: When a new write comes in, the new primary must check if that write conflicts with any of the pending transactions in its apply queue. It does this by checking the certification_info map. If the old primary had purged the global certification data early, the new primary wouldn't have the write-sets for those pending transactions. It would blindly accept the new write, causing a massive data conflict and breaking the replication group entirely.

Fine Marco, then what is the effect of this?

Well, drums roll …

… When a secondary node is elected as the new primary during a failover, it does not immediately open the floodgates to new writes. It keeps its super_read_only variable set to ON until it has completely drained its local apply queue of all transactions that were certified prior to the election.

This is an intentional design choice to guarantee that the new primary's state is completely consistent with the old primary before it starts accepting new data.

4. Immediate Write Rejections (No Built-in Queuing)

The most critical impact to understand is that the new primary does not queue or pause new incoming writes while it catches up. It outright rejects them.

If our application or proxy routes a COMMIT, INSERT, UPDATE, or DELETE to the new primary while it is still processing the old queue, MySQL will immediately throw an error back to the client:

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

5. The "Brownout" Window (Write Outage)

Because of this behavior, a failover in MySQL Group Replication does not instantly restore write availability. Our cluster experiences a "brownout", a period where reads might succeed, but writes are entirely blocked.

The duration of this write outage is directly proportional to the size of the apply queue.

• If the secondary was fully caught up, write availability is restored in milliseconds.
• If the secondary was lagging by 50 minutes, your application will suffer a 50 minute write outage while the node applies the backlog.

6. Impact on Proxies (e.g., MySQL Router or ProxySQL)

If we are using a proxy layer to route your database traffic, the apply queue dictates how the proxy behaves during the transition:

• MySQL Router: It continuously monitors the cluster topology and the super_read_only flag. Even though the node has technically been elected primary, Router will not open the read-write port to it until the apply queue drains and super_read_only flips to OFF. Depending on your application timeouts, client connections will either hang waiting for a writable connection or fail completely.
• ProxySQL: Similar to Router, if it is configured to check for the read_only state, it will temporarily quarantine the new primary from the write hostgroup.
• HAProxy (in Operator): Monitor both Primary state and read_only state, but it expose the Primary to writes causing the application to fail (bug we need to fix)  

7. Read Traffic and Stale Data

During this catch-up phase, the node will accept incoming SELECT queries (since it is still a valid database). However, because it is actively churning through the old primary's backlog, the data being read is temporarily stale.

If your application reads a row that is sitting in the apply queue but hasn't been committed to disk yet, it will get the old version of that row.

Why Flow Control is Critical

Because a large apply queue turns a seamless failover into a severe, application-breaking write outage, Group Replication includes the Flow Control feature.

Flow Control monitors the size of the apply queues across all secondaries. If a secondary starts lagging too far behind, Flow Control should actively throttle the write throughput on the current primary to allow the lagging node to catch up. It is essentially a trade-off: we accept a slight performance hit during normal operations to guarantee that your database recovers almost instantly during a failover.

However, this is not what really happens.

1. It is Reactive, Not Proactive (The Polling Blind Spot)

Flow control does not intercept and evaluate every single transaction in real-time. Instead, it relies on a periodic polling interval governed by group_replication_flow_control_period (which defaults to 1 second).

Once a second, the cluster checks the size of the apply queues and the certifier queues.

• The Vulnerability: If our application generates a massive spike of 50,000 writes in 500 milliseconds, the primary will happily accept and certify all of them. Flow control will not even notice the spike until the next 1 second polling interval hits. By the time it decides to apply a throttle, the damage is already done, and the secondary's queue is already overflowing.

2. The PID Controller's "Soft Brake" Math

When flow control does decide to throttle, it does not simply freeze the primary. It uses a PID (Proportional-Integral-Derivative) controller algorithm to calculate a "write quota" (the maximum number of transactions the primary is allowed to commit in the next second).

The PID controller is deliberately tuned to be gentle. It wants to gracefully degrade performance rather than cause immediate application timeouts.

• When the secondary's queue breaches the group_replication_flow_control_applier_threshold (default 25,000 transactions), the PID controller reduces the primary's quota incrementally.
• The Failure Point: If the primary's incoming write rate is astronomically higher than the secondary's disk IO capacity, this incremental "step down" in the quota is too slow. The primary is still allowed to write, say, 10,000 transactions per second, while the secondary is only applying 2,000. The queue continues to grow aggressively despite the throttle being "active."

3. The Concurrency Mismatch (Parallel vs. Serial)

This is often the silent killer that defeats flow control. Flow control makes mathematical assumptions about how fast the secondary should be able to apply transactions based on recent history.

However, the primary node might be executing writes using hundreds of highly concurrent threads. The secondary relies on the parallel applier to keep up. If the incoming workload suddenly includes transactions that cannot be parallelized, such as writes hitting overlapping rows, cascading foreign key updates, or DDL statements, the secondary's applier instantly drops from executing in parallel down to a single, serialized thread.

When this serialization happens, the secondary's applier rate plummets instantly. Flow control, which only checks in once a second and adjusts gradually, cannot brake the primary fast enough to compensate for the secondary suddenly dropping to a crawl.

What can we do?

At the moment of writing there are only two things that can be done.

1. Make Flow control more aggressive
2. Increase the number of replication appliers

1. Making Flow Control More Aggressive

We can configure Flow Control to be a bit more aggressive. It will still remain a suggestion but a strong one.

How it works (The Configuration):

• Lower the Threshold: By reducing group_replication_flow_control_applier_threshold (default is 25,000) to something like 1,000 or 500, we force the PID controller to kick in almost immediately when a spike occurs.
• Remove the Safety Net: By keeping  group_replication_flow_control_min_quota to 0 (default), we remove the minimum write guarantee. If the secondary falls behind, Flow Control is allowed to throttle the primary's writes down to zero, also if this will never happen.
• Increase the Sensitivity: We can tweak the PID controller's math (using the derivative and proportional tuning variables) to react much more aggressively to queue growth.       group_replication_flow_control_hold_percent=100       group_replication_flow_control_release_percent=5

The reality check, does it work?:

If the expectation is to have a rigid control over the applier queue on the lagging secondary, then the answer is NO. No matter what, at the moment flow control is not designed to act as we are used to in PXC (Percona Xtradb Cluster), where we have a rigid control of the pending queue also at the cost of delaying the writes. In Group Replication  the Flow Control will never bring the write to 0, the unfortunate aspect is that the mechanism is not enough to keep the queue under control.

2. Increasing Replication Appliers 

To help the secondary chew through the queue faster, we can increase the number of parallel threads it uses to write to disk.

How it works: We can increase the replica_parallel_workers (formerly slave_parallel_workers) setting. GR is exceptionally smart about this. Because of the certification process we discussed earlier, GR already knows exactly which transactions modify which rows. It uses a writeset-based dependency tracker to safely hand off non-conflicting transactions to multiple worker threads simultaneously. The formula that is normally used to calculate the number of replication workers is to set 2.5 workers for each available core. IE if we have 14000m CPUs in our CR (K8) then we can assign ~35 workers, this is definitely higher than the default value of 4.   

The reality check, does it work?:  Yes, but only if our workload allows it.

• The Catch - The Serialization Wall: Parallel appliers only work if the transactions do not conflict. If our application has 50 concurrent threads all trying to update the same "inventory count" row, or updating a highly contentious table, those transactions cannot be parallelized. The secondary's coordinator thread will see the row-level conflicts and force those transactions to wait in line and execute sequentially. We could allocate 128 parallel workers, but 127 of them will sit idle while one thread does all the work.
• The Catch - Context Switching: More threads do not magically create more disk IOPS. If we set the workers too high (e.g., beyond the physical CPU core count or disk IO capacity), the secondary's InnoDB engine will spend more time context-switching and fighting over internal mutex locks than actually committing data. In many cases, over-allocating parallel workers actually slows down the apply rate.

Do we have any conclusions?

1. If HA is the goal, enforce Strict Flow Control

If our absolute top priority is High Availability, specifically achieving a near-zero Recovery Time Objective (RTO), we must configure an aggressive flow control.

• The Logic: Fast failovers require small apply queues. To guarantee a small apply queue, we must strictly throttle the primary the millisecond the secondary starts to lag.
• The Trade-off: we are protecting the cluster's failover readiness at the expense of application write latency. If there is a massive write spike, our application will face timeouts and connection errors, but if the primary server suddenly catches fire, our database will recover and elect a new primary almost instantly.

The problem is that Group Replication is not able to act like that today, this is something we eventually need to implement to have better HA.

2. If Performance is the goal, relax Flow Control

If our top priority is keeping the application fast and ensuring COMMIT latencies remain extremely low, we should relax flow control or rely on the generous defaults.

• The Logic: By relaxing flow control, we allow the primary to run at the absolute maximum speed its local disks and CPU allow. It does not care if the secondaries fall behind. Our application users remain happy and experience zero throttling.
• The Trade-off: We are accepting severe risks to your HA posture. If the primary crashes while the secondaries have a massive apply queue, we will suffer a long write outage (the brownout) while the new primary catches up. Additionally, we are accepting the risk that the certification_info memory buffer will grow significantly on the primary and eventually have the pod OOMKilled .

3. Is this not what Asynchronous replication with semy-sync offers?

1. The Similarities

If we look purely at how a single transaction flows and how a failover behaves, GR and Semi-Sync look like twins:

• The Durability Guarantee: Semi-Sync: The primary waits to commit until at least one secondary confirms it has received the transaction and written it to its local Relay Log. 
  • GR: The primary waits to commit until a majority quorum of nodes confirm they have received the transaction, certified it, and written it to their local relay logs.
• The Failover Delay (The Queue):  In both systems, the secondary receiving the data does not mean the secondary has applied the data to its InnoDB tables.
  • If a crash happens, both systems require the new primary to completely execute its pending queue (Relay Log for Semi-Sync, Apply Queue for GR) before it is safe to accept new writes.

2. The Crucial Differences

If they behave so similarly, why use GR at all? The differences lie entirely in automation, consensus, and split-brain protection. Semi-Sync is just a data transport mechanism; GR is a full state-machine cluster.

Here is what GR gives you that Semi-Sync does not:

• Automatic Election and Orchestration:
  • Semi-Sync: If the primary dies, Semi-Sync does nothing. The cluster sits there broken. You must rely on external tools (like Orchestrator or manual DBA intervention) to detect the crash, pick the most up-to-date secondary, wait for its relay log to apply, disable read_only, and re-point the application.
  • GR: The cluster detects the failure natively. The remaining nodes use Paxos consensus to elect a new primary automatically, manage the queue drain natively via the super_read_only flip we discussed, and self-heal.
• Split-Brain Protection (Network Partitions):
  • Semi-Sync: If our network splits in half, an external failover tool might accidentally promote a secondary while the old primary is still alive and accepting writes. We now have a split-brain, and our data is permanently corrupted.
  • GR: GR enforces strict quorum. If a network split happens, the side of the network with the minority of nodes will automatically fence itself off and refuse all writes. Split-brain is mathematically prevented.
• The Certification Database:
  • As we established, GR requires the certification map to ensure the new primary doesn't accept writes that conflict with its unapplied queue. Semi-Sync does not have this; it relies entirely on the external failover tool to guarantee no writes touch the new primary until the relay log is 100% applied.

3. Final observation

If we are using Single-Primary GR with relaxed flow control, we have essentially built a highly-automated, consensus-driven version of Semi-Sync replication. 

We have the exact same apply-queue bottleneck during failover, but we have traded the need for external orchestrator tools for built-in Paxos consensus and native split-brain protection.

Conclusions (for real)

When we run MySQL on a traditional, dedicated Virtual Machine, memory limits are "soft." If the certification_info database explodes and consumes an extra 10GB of RAM because of the applier lag, the Linux OS might start aggressively swapping inactive pages to disk, but the MySQL process usually survives. Performance degrades, but the database stays online.

In Kubernetes, memory limits are "hard." As we discussed earlier, Kubernetes enforces pod memory limits via cgroups v2 (memory.max). The Linux kernel's OOM Killer has no understanding of database quorum, failover states, or apply queues. It only sees math: Working Set Size > memory.max = Terminate Process (Exit Code 137).

The Chain Reaction of Relaxed Flow Control in k8s

If we prioritize "performance" by relaxing Flow Control in a Kubernetes environment, we are essentially setting a ticking time bomb. Here is the chain of events:

1. The Spike: Our application experiences a massive write spike.
2. The Queue: The secondary pod's disk cannot keep up, and its applier queue grows to 1,000,000 transactions.
3. The Memory Sprawl: Because the queue is large, the global low-watermark stalls. The Primary pod is forbidden from garbage collecting the certification_info map. The in-memory hash map balloons in size.
4. The Execution: The memory.current metric will reach the memory.max, kernel will trigger the OMMKill process. First action will be to try to free the page.cache related to the process. If the purge is successful and the memory.current is less than memory.max then the process will persist, otherwise the kernel will kill it. We can use the WSS metric to predict a successful OMMKill. The Primary pod's Working Set Size (WSS) breaches its Kubernetes memory limit, this is a fair estimate not an absolute value.
5. The Catastrophe: The Linux OOM Killer instantly assassinates the Primary MySQL process.

Because we tried to avoid a few seconds of write latency by keeping relaxed Flow Control, we inadvertently caused a hard crash of the primary database pod, with long write downtime.

The Architectural Law

Therefore, here is my statement as architectural law for containerized environments: In Kubernetes, High Availability and Pod stability are so intrinsically linked that Flow Control must act as hard as it can to cap the apply queue.

• We cannot allow unbounded memory growth in a container. The only way to bound certification_info memory is to bound the apply queue.
• The only way to bound the apply queue is with strict, aggressive Flow Control.
• Increasing the number of replication appliers helps but is not the conclusive answer.

In a Kubernetes environment, we must tune group_replication_flow_control_applier_threshold to a strict, low number, and accept that during massive traffic spikes, our application will experience write throttling. It is infinitely better for our application's connection pool to wait 2 seconds for a COMMIT to succeed than for the primary database pod to be violently OOMKilled by the kernel, and have to wait for minutes or hours to recover write capabilities.

Note

Just as a mention this is exactly how Percona Operator with Percona Xtradb Cluster works. To be more specific, PXC and in general solutions based on Galera have a Flow Control mechanism that enforces the queue to be inside hard limits. While this more invasive control may be noticeable at application level, it guarantees that the other nodes are not lagging behind the primary and this is why it is a stronger HA solution in the Kubernetes environment.

Reference

https://github.com/Tusamarco/mysqloperatorcalculator

Managing Resources and OOMKills: Resource Management for Pods and Containers

(This page details how memory limits are enforced reactively by the Linux kernel via OOM kills).

How WSS triggers Evictions: Node-pressure Eviction

(This page explicitly details how the kubelet uses the memory.available signal, which is derived from node capacity minus the working set size).

Latest changes. Pointer to the code

Swap Memory Management (Core Concepts & Configuration): https://kubernetes.io/docs/concepts/cluster-administration/swap-memory-management/

The post Top 4 MySQL ADO.NET Providers for 2026  appeared first on Devart Blog.