Welcome back. This issue covers five weeks rather than the usual one — the gap since the August issue took in Percona Live Amsterdam, the launch of the OurSQL Foundation, and the run-up to the MySQL Galera Cluster end of life on 30 September.

If you want to get these updates, just subscribe to the blog.

Enjoy!

MySQL News

Note: Aggregated MySQL news can be found at Planet for MySQL Community and Planet MySQL (Oracle curated)

DISTANCE() and VECTOR_DISTANCE(): Vector Similarity in Percona Server for MySQL 9.7 MySQL Performance Blog, Percona

TL;DR - Percona Server for MySQL 9.7.2-2 adds a DISTANCE() function with COSINE, EUCLIDEAN, MANHATTAN and DOT metrics, so embeddings can be ranked in SQL. ANN indexing is still to come.

Using DuckDB inside MySQL VillageSQL

TL;DR - A new VillageSQL Server extension runs DuckDB queries from inside MySQL and joins the results with MySQL result sets, aimed at querying Parquet and other analytical formats in place.

Group Replication Beyond a Single Cluster: DC-DR with Percona (PS MySQL) Operator MySQL Performance Blog, Percona

TL;DR - Percona's PS MySQL Operator v1.2.0 adds cross-site replication for Group Replication/InnoDB Cluster topologies. The post walks through wiring a DR cluster to a primary one.

Traceability Matters: Gopal Shankar on Opening Up MySQL Development for the Next Decade Roberto V. Zicari, ODBMS.org

TL;DR - Oracle's Gopal Shankar explains the reasoning behind moving Enterprise-tier features into Community Edition and what "opening up" MySQL development is meant to mean in practice.

Vector search in MySQL: an early look at HNSW and custom indexes in VillageSQL VillageSQL

TL;DR - MySQL 9.x ships a VECTOR type but no distance function and no vector index. This post covers an HNSW index built through VillageSQL's custom index support.

Performance improvements in Percona Server 8.4.11-11 MySQL Performance Blog, Percona

TL;DR - A read/write benchmark breakdown of what changed in Percona Server for MySQL 8.4.11-11. It follows the earlier "Performance Progression of Percona Server for MySQL 8.4", which is worth reading first.

Diagnosing MySQL Memory Problems with Jemalloc Libing Song

TL;DR - Performance Schema memory instrumentation is too coarse to find many leaks. This walks through using jemalloc's profiling to locate where MySQL memory actually goes.

Percona releases Galera Cluster Version 9.7 Oli Sennhauser, FromDual

TL;DR - Percona has shipped Galera Cluster 9.7 for MySQL, after MariaDB Corporation acquired Codership and announced the end of support for Galera Cluster for MySQL 8.4 this September.

Introducing MySQL Workbench 26 Oracle MySQL Group

TL;DR - Workbench 26.7 is the first release of a rebuilt Workbench on a MySQL Shell foundation, replacing the end-of-life C++ Workbench 8. A companion post covers five things to try in it.

Advanced Cryptography in MySQL with vsql-crypto VillageSQL

TL;DR - The vsql-crypto extension brings common backend cryptography into MySQL itself: hashing stored passwords, signing payloads for downstream verification, and keeping a column unreadable outside the database.

OAuth2 and JWT Logins for MySQL VillageSQL

TL;DR - A VillageSQL extension that authenticates MySQL logins from OAuth2 and JWT tokens, so database access is revoked along with a departing engineer's single sign-on instead of being maintained separately in the database.

OpenID Connect Authentication for MySQL, Now Fully Open Source MySQL Performance Blog, Percona

TL;DR - Percona Server for MySQL now ships an open source OIDC authentication plugin, letting accounts authenticate against any standards-compliant identity provider, from 8.4.11-11 and 9.7.2-2.

Introducing the OurSQL Foundation OurSQL Foundation

TL;DR - A re-launch of the OurSQL Foundation website.

MySQL CPU and CSPU Versioning for More Frequent Security Updates The Oracle MySQL Blog

TL;DR - Oracle is adding monthly Critical Security Patch Update releases alongside the quarterly CPU cycle, issued only when a critical security or stability fix warrants one.

Announcing VillageSQL Server 0.0.6 VillageSQL

TL;DR - VillageSQL Server 0.0.6 moves the mainline to MySQL Server 8.4.11 and adds support for MySQL Server 9.7.2 and Percona Server 8.4.10.

MySQL Galera Cluster EOL: Your Practical Paths Forward Continuent

TL;DR - MySQL Galera Cluster reaches end of life on 30 September 2026. Five migration paths compared — MariaDB Galera, PXC, self-managed MySQL, managed cloud MySQL and Tungsten Cluster. A companion post covers the cost of delaying the decision.

Database News

The architecture of Neki PlanetScale

TL;DR - PlanetScale's sharded Postgres, from the team behind Vitess, is in platform preview. Companion posts cover the router, the lifecycle of a sharded query, and a 118.5M queries/sec benchmark across 512 shards.

Evaluating LLM models for DBA tasks Percona Database Blog

TL;DR - A purpose-built harness measuring how well current LLMs handle real database administration tasks, rather than general systems admin.

Introducing TIN: full-text search for Postgres Patrick Reynolds, PlanetScale

TL;DR - TIN is a new full-text search index for Postgres. A follow-up introduces Lead, a feature-equivalent version you can run in CI.

Announcing ProxySQL 3.0.11, 3.1.11, and 4.0.11 ProxySQL

TL;DR - Three parallel releases improving multiplexing, causal reads, modern authentication, fast-forward/TSDB reliability and MCP operations. ProxySQL.Cloud, a SaaS control plane for MySQL and Postgres, was also announced this month.

Upcoming Database Events

MySQL BR Conf 2026 (VillageSQL is a sponsor) September 26, 2026 São Paulo, Brazil

Postgres Summit US (formerly PGConf.NYC) September 30 – October 2, 2026 (PgUS) New York City, NY

High Performance Transaction Systems (HPTS) October 4-7, 2026 Asilomar Conference Grounds Pacific Grove, CA

Open Source Summit Europe October 7-9, 2026 Prague, Czechia

All Things Open 2026 October 19-20, 2026 Raleigh Convention Center Raleigh, NC

PGConf.EU October 20–23, 2026 (PostgreSQL Europe) Valencia, Spain

Oracle AI World October 25–28, 2026 Las Vegas, NV

KubeCon + CloudNativeCon North America (VillageSQL is a sponsor) November 9-12, 2026 Salt Lake City, Utah

Open Source Summit Japan December 7-9, 2026 Tokyo, Japan

FOSDEM 2027 January 30-31, 2027 ULB Solbosch Campus Brussels, Belgium

KubeCon + CloudNativeCon Europe 2027 March 15-18, 2027 Barcelona, Spain

SCaLE 24x April 1-4, 2027 Pasadena Convention Center Pasadena, CA

Percona Server for MySQL 9.7.2-2 now supports DISTANCE() for vector similarity scoring directly in SQL (COSINE, EUCLIDEAN, MANHATTAN, DOT metrics). This is the compute primitive you need to rank or filter embeddings by similarity directly in SQL. ANN indexing (e.g. HNSW, IVF) is the next milestone for fast large-scale similarity search; and this function provides the scoring layer that indexing strategies will further accelerate.

Why we’re adding this

MySQL’s native DISTANCE() and VECTOR_DISTANCE() functions are only available in HeatWave MySQL on OCI, not included in Community or Commercial MySQL, and limited to three metrics (COSINE, DOT, EUCLIDEAN). Percona is bringing the same capability to anyone running Percona Server for MySQL on any supported platform.

MySQL 9.7 already supports the VECTOR data type (TO_VECTOR() and FROM_VECTOR() functions) for storing embeddings. DISTANCE() is the natural next step: it lets you query by similarity directly in SQL, ranking or filtering rows based on vector distance, without leaving MySQL.

Background on Vectors & Distance Metrics

What’s a vector?

A fixed-length list of numbers, an “embedding” produced by an ML model to represent something (text, an image, a product, a user preference). The key insight: “similar” things end up with numerically close vectors, so you can compute their distance to rank or filter them.

What’s a distance metric?

A mathematical function that turns two vectors into a single number describing similarity. Here are the five metrics this release supports:

• EUCLIDEAN (L2): Straight-line distance, the intuitive notion of geometric distance. General-purpose; works well when magnitude matters.
• EUCLIDEAN_SQUARED: Same ranking as EUCLIDEAN, but skips the square root. Faster for comparisons and ORDER BY clauses when you only care about ordering, not the actual value.
• MANHATTAN (L1): Grid-style distance (like taxicab distance on a city grid). More robust to outliers than Euclidean in some applications.
• COSINE similarity: Measures the angle between two vectors. Cosine distance is computed as 1 minus the cosine similarity so that smaller value = more similar.
• DOT (inner product): Raw dot product sum(a[i] * b[i]). Used by models trained specifically for dot-product similarity (e.g., some matrix-factorization systems). Normally higher values mean more similar but since it is common in the industry to multiply by -1 and flip the scale: now smaller values = more similar, in line with the other metrics above.

What’s SIMD?

Single Instruction, Multiple Data: a CPU capability that performs the same arithmetic on many numbers at once instead of one at a time. Distance math over hundreds of dimensions (embedding size) is exactly the kind of repetitive arithmetic that SIMD accelerates. The Percona implementation automatically detects and uses the best SIMD tier available on your hardware at startup, no recompilation needed per target architecture.

Meet DISTANCE() / VECTOR_DISTANCE()

Function signature:

DISTANCE(vector1, vector2, metric)

Where: – vector1 and vector2 are VECTOR data type or binary string literals (via TO_VECTOR()). – metric is a fixed literal string (case-insensitive, not a column nor an expression): ‘EUCLIDEAN’, ‘EUCLIDEAN_SQUARED’, ‘MANHATTAN’, ‘COSINE’, or ‘DOT’. – Returns a DOUBLE representing the distance/similarity score.

Synonym: VECTOR_DISTANCE() is an alias with identical behavior.

Choosing a metric: Match the metric to how your embedding model was trained. Most modern embedding models (OpenAI, Cohere, etc.) are trained with COSINE similarity, so use ‘COSINE’. If magnitude carries meaning (e.g., you’re working with raw feature vectors, not normalized embeddings), use ‘EUCLIDEAN’ or ‘EUCLIDEAN_SQUARED’. When in doubt, check your embedding model’s documentation.

• All dimensions must match: Both vectors must have the same dimensionality, or the function returns an error.
• If either input is NULL, the result is NULL.

Getting started: Step-by-step example

1. Create a table with vector embeddings

CREATE TABLE products (
 id INT PRIMARY KEY,
 name VARCHAR(255),
 embedding VECTOR(1536)  -- Example: 1536-dimensional embedding
);

2. Insert some embeddings

INSERT INTO products VALUES
 (1, 'Product A', TO_VECTOR('[0.1, 0.2, 0.3, ..., 0.384]')),
 (2, 'Product B', TO_VECTOR('[0.15, 0.25, 0.35, ..., 0.385]')),
 (3, 'Product C', TO_VECTOR('[0.5, 0.6, 0.7, ..., 0.800]'));

3. Query by similarity

Find the top-5 products most similar to a query embedding:

SELECT id, name, DISTANCE(embedding, TO_VECTOR('[0.12, 0.22, 0.32, ..., 0.382]'), 'EUCLIDEAN') AS similarity_score
FROM products
ORDER BY similarity_score
LIMIT 5;

Results ordered by highest similarity first (best/closest matches first) :

id | name       | similarity_score
---|------------|------------------
2  | Product B  | 0.5432109
1  | Product A  | 0.9654321
3  | Product C  | 0.9876543

Under the hood: SIMD dispatch that adapts to your hardware

The engineering story behind the performance claim: instead of compiling the binary once for a specific CPU (with -march=native), Percona’s implementation detects the CPU’s capabilities at startup and selects the best available SIMD tier:

• SSE4.2 (x86_64) or NEON (aarch64):  128-bit SIMD, ~2–3× speedup over scalar.
• AVX2 (x86_64):  256-bit SIMD, ~4–6× speedup.
• AVX-512F (x86_64): 512-bit SIMD, ~8–12× speedup on CPUs that support it.
• SVE2 (aarch64): scalable SIMD on ARM, adapts to the CPU’s vector width.
• Scalar: unoptimized fallback, works everywhere.

Dimension-aware kernel selection: Distance calculations on small vectors (<16 dimensions) uses the narrower 128-bit tier, because the overhead of setting up larger SIMD registers outweighs the benefit. Larger vectors automatically use the widest available tier.

Unaligned loads by design: VECTOR column data isn’t guaranteed to be cache-line aligned (and shouldn’t require alignment). All SIMD kernels use unaligned-load intrinsics as modern CPUs have identical throughput for aligned and unaligned loads when data is in cache. By always using unaligned loads, we avoid faulting on misaligned input without sacrificing performance.

Current limitations and what’s coming next

DISTANCE() is a scalar function that computes the distance between two vectors and returns a single number. Without an approximate-nearest-neighbor (ANN) index, a query like ORDER BY DISTANCE(…) LIMIT k over a large table is a full table scan: you call the distance function on every row, then sort. It’s correct and SIMD-accelerated per row, but it scales as O(n), not O(log n).

The natural next step: ANN indexing. To make large-scale similarity search fast (e.g., finding the 10 nearest neighbors in a table of 1 million vectors in milliseconds), you need an approximate-nearest-neighbor index: HNSW (Hierarchical Navigable Small World), IVF (Inverted File with refinement), or similar. These are graph-based or clustering-based structures that prune the search space and return approximate results much faster. This is the direction the vector feature is building toward, and it’s on the roadmap. For now, DISTANCE() is the scoring primitive that those indexing strategies will accelerate.

While ANN indexing delivers speed, it relies on approximate results. Should your application require exact precision instead of estimates, the DISTANCE() function is available in Percona Server for MySQL 9.7.2-2.

We want your feedback

Try it out and let us know what you think: – Report bugs or feature ideas on JIRA. – Join the conversation on Percona community forum. – Questions about usage or performance? Reach out to us.

Your feedback shapes the roadmap, especially use cases you’d like to see (e.g., specific ANN index strategies, embedding model integrations, performance tuning for your workload).

See also

• VECTOR data type documentation
• DISTANCE() / VECTOR_DISTANCE() reference
• Getting started with vector search in Percona Server for MySQL (coming soon)
• An Introduction to Vector Databases

Written by Catalin Besleaga. Reviewed by Dennis Kittrell and Peter Zaitsev.
Percona® is a registered trademark of Percona LLC. MySQL® is a registered trademark of Oracle Corporation.

The post DISTANCE() and VECTOR_DISTANCE(): Vector Similarity in Percona Server for MySQL 9.7 appeared first on Percona.

We are pleased to announce a new extension for VillageSQL Server that enables running DuckDB queries from within MySQL and joining those results with MySQL query results. DuckDB has emerged as the analytical engine of choice for fast querying of data formats such as Parquet. It excels with analytics queries because of its columnar storage, vectorized execution, and embedded architecture. Applications often need to combine the results of analytical queries with operational results, though. There are multiple ways to do this, but many are suboptimal when the query's results need to be returned to an application that is connected to an operational database such as MySQL.

The new vsql-duckdb extension from VillageSQL solves this by embedding DuckDB inside MySQL, keeping your existing database connection and SQL as the interaction point. It embeds DuckDB inside VillageSQL Server and exposes three functions that pass query text to it, which is similar to what pg_duckdb offers for PostgreSQL.

VillageSQL is the innovation platform for MySQL that adds an extension framework (VEF), similar to PostgreSQL's extension framework, to enable permissionless innovation. Instead of waiting for a feature to be implemented in a few years in a future version of MySQL, new functionality can be dynamically added to a version of MySQL you run today.

Three functions

The extension has three functions. Two of the functions take DuckDB query text as a string and differ only in what they hand back. duckdb_scalar() returns the first value of the first row, which covers counts, sums, and anything else that is a single answer. duckdb_query() returns the whole result as a JSON array with one object per row, and MySQL's JSON_TABLE turns that array back into rows you can join. The third function, duckdb_status(), takes no query at all and reports which DuckDB version is compiled in, which file readers the bundle was built with, and whether your object storage credential loaded.

Installing

To get started, build the extension from source (https://github.com/villagesql/vsql-duckdb). The build compiles DuckDB inside it, so it takes a few minutes the first time. The extension runs on VillageSQL Server 0.0.6 or newer. The examples in this post that pass a result into JSON_EXTRACT or JSON_TABLE need a server newer than 0.0.6 (0.0.7-dev as of this writing), which hands VEF function results to MySQL's JSON functions as utf8mb4 text. On 0.0.6, wrap the call in CONVERT(... USING utf8mb4) first. Follow the build instructions on the Readme. The install step writes vsql_duckdb.veb (VillageSQL Extension Bundle) into the directory the server loads extensions from. If you want to confirm where that is, ask the server with SHOW VARIABLES LIKE 'veb_dir'.

Next, install the extension from SQL. The extension declares two preview capabilities, sys_var and keyring, so the server has to allow preview extensions first. SET PERSIST takes effect immediately, so these two statements run back to back with no restart between them:

SET PERSIST vsql_allow_preview_extensions = ON;
INSTALL EXTENSION vsql_duckdb;

Ask duckdb_status() which readers the build gave you:

SELECT JSON_EXTRACT(duckdb_status(), '$.readers') AS readers;

["core_functions", "httpfs", "json", "parquet"]

httpfs is the one that makes object storage work. It handles both s3:// and https:// paths.

Querying a remote file

Point duckdb_scalar() at a public 127 MB Parquet file, with no credentials and nothing copied onto your server, and you get an answer back:

SELECT duckdb_scalar('SELECT count(*) FROM read_parquet(''https://blobs.duckdb.org/data/taxi_2019_04.parquet'')');

7433139

A Parquet file records its own row count in a footer. DuckDB fetches that footer over HTTP and reads the count straight out of it, so it never reads the trip data at all.

A query that reads real column values has to pull those columns across the network first, so it takes longer than a count does. vsql_duckdb.timeout_ms bounds how long the calling connection waits, and it defaults to 30 seconds. A query that runs past it stops with an error. You can raise the limit, and you can also cap how much memory DuckDB takes, how many worker threads it starts, and how large a result one call may return. The README lists every setting with its default and range.

Pointing it at your own bucket

Reading a private bucket takes a region, an access key id, and somewhere to keep the secret access key. No setting holds that secret. The extension reads it from the server's keyring through VEF's keyring capability, and the settings only name the entry to look for.

Reading is all it does there, on purpose. VEF can write a key as well, and a key written that way is unreadable from SQL by anyone, which is what a server credential wants. The catch is that extension functions cannot be granted per user, so a setter would let every user of the server replace the credential. The operator stores the key instead, which needs a keyring component loaded and the keyring_udf plugin:

SELECT keyring_key_store('duckdb_s3_secret', 'AES', 'the-secret-access-key');

SET PERSIST vsql_duckdb.s3_region = 'eu-north-1';
SET PERSIST vsql_duckdb.s3_key_id = 'AKIAEXAMPLE';
SET PERSIST vsql_duckdb.s3_secret_keyring_id = 'duckdb_s3_secret';
SET PERSIST vsql_duckdb.s3_secret_keyring_auth_id = 'root@localhost';

A key stored from SQL belongs to the account that stored it, so s3_secret_keyring_auth_id has to name that account in full user@host form. Call duckdb_status() afterwards and it tells you whether the credential loaded. Google Cloud Storage is configured through the same settings with an HMAC key, and the README covers it along with S3-compatible stores like MinIO.

From there an s3:// path behaves exactly like the public URL above:

SELECT duckdb_scalar('SELECT count(*) FROM read_parquet(''s3://sales/2026/*.parquet'')');

Joining a dataset to a real table

DuckDB has no view of your InnoDB tables, and a query that names one fails in DuckDB's catalog rather than in MySQL. So you do the join in MySQL. duckdb_query hands back a JSON array, JSON_TABLE unpacks that array into rows, and those rows join against a real table like any others.

In the example below, the Parquet side is a synthetic sales dataset, 5 million rows over four files under /data/sales/ in the Hive layout that hive_partitioning = true reads. The files sit on the server's own disk, and the extension refuses local paths until you allow them:

SET PERSIST vsql_duckdb.allow_local_files = ON;

regions is an ordinary InnoDB table with one row per city, holding the region it sits in and the manager who owns it. DuckDB rolls up the files and MySQL joins the totals:

SELECT r.region, r.manager, t.orders, t.revenue
FROM JSON_TABLE(
  duckdb_query('SELECT city, count(*) AS orders, sum(amount) AS revenue
                FROM read_parquet(''/data/sales/**/*.parquet'', hive_partitioning = true)
                GROUP BY city'),
  '$[*]' COLUMNS (city    VARCHAR(64) PATH '$.city',
                  orders  BIGINT      PATH '$.orders',
                  revenue BIGINT      PATH '$.revenue')) AS t
JOIN regions r ON r.city = t.city
ORDER BY t.revenue DESC;

+--------+---------+---------+----------+
| region | manager | orders  | revenue  |
+--------+---------+---------+----------+
| east   | ana     | 1250000 | 61249754 |
| west   | dia     | 1250000 | 61249734 |
| west   | ben     | 1250000 | 61249715 |
| north  | cai     | 1250000 | 61249676 |
+--------+---------+---------+----------+

Only four grouped rows cross between the two engines, because DuckDB does the counting and summing before it hands anything over. Keep its side of the work to counts, sums, and rollups, and the JSON string stays small. Ask it for raw rows instead and the array grows until it reaches the one megabyte result cap.

What it does not do yet

This is an initial version of the duckdb extension for VillageSQL Server. You always write the DuckDB query yourself. There is no CREATE FOREIGN TABLE that makes a Parquet file look like a MySQL table, no pushdown of a MySQL WHERE clause into DuckDB, and no routing of ordinary SQL to DuckDB, so every call is an explicit duckdb_query('...'). DuckDB cannot read your InnoDB tables either, which is why the join belongs in the outer query. A result larger than one megabyte raises an error rather than coming back cut short, because a truncated JSON array loses its closing bracket and stops parsing. Four of DuckDB's components are built in today: parquet, json, httpfs, and core_functions.

There are alternative approaches to connecting MySQL and DuckDB too. DuckDB's own mysql extension will ATTACH your database and join an InnoDB table to a Parquet file in one query. dbtrail reads the binlog and archives it as Parquet for DuckDB to query. Alibaba's AliSQL embeds DuckDB in mysqld as a pluggable storage engine, where ALTER TABLE ... ENGINE=DuckDB converts an existing table to columnar storage.

The first two put a Parquet file within reach, but you have to query from somewhere other than your database. You connect to DuckDB, or you query what a pipeline already copied out. AliSQL does run inside MySQL, but it only stores tables you have already loaded, so a Parquet file sitting in a bucket stays out of reach. In none of the three can an application connected to your MySQL server ask that Parquet file a question.

The settings reference, the pg_duckdb migration table, and the full limitations list are in the vsql-duckdb README. To get VillageSQL Server, start at villagesql.com.

Please let us know your feedback. You can find us on Discord or on GitHub Issues.

In this blog post, we will explore how to add a DR Cluster to an existing DC Cluster to form a ClusterSet environment, which provides a seamless switchover between DC/DR members. The good part is that all the complexity and configuration will be managed by the Percona operator, with very few setup steps required.

Let’s discuss how we can implement a cross-site replication (DC-DR) topology across two separate database clusters.

Environment used for Demonstration

• Two separate database clusters (pscluster1 and pscluster2) are deployed within a single GCP/Kubernetes environment, each managed by a dedicated operator.
• Percona Operator for MySQL( based on Percona Server for MySQL) has a version. 1.2.0
• MySQL version 8.4.10.
• Network connectivity between DC/DR components.

DC (pscluster1) configuration

Step 1:  Deploying the cluster on the DC side.

shell> kubectl get ps -n ps
NAME          REPLICATION         ENDPOINT                 STATE   MYSQL   ORCHESTRATOR   HAPROXY   ROUTER   AGE
ps-cluster1   group-replication   ps-cluster1-haproxy.ps   ready   3                      3                  28m

shell> kubectl get pods -n ps
NAME                                             READY   STATUS    RESTARTS   AGE
percona-server-mysql-operator-6b78d5f68c-bzg6r   1/1     Running   0          30m
ps-cluster1-haproxy-0                            2/2     Running   0          28m
ps-cluster1-haproxy-1                            2/2     Running   0          27m
ps-cluster1-haproxy-2                            2/2     Running   0          27m
ps-cluster1-mysql-0                              2/2     Running   0          29m
ps-cluster1-mysql-1                              2/2     Running   0          28m
ps-cluster1-mysql-2                              2/2     Running   0          26m

Step 2: Retrieve the DC’s InnoDB cluster name, which will be required later to construct the ClusterSet.

shell> kubectl get ps ps-cluster1 -n ps -o jsonpath='{.status.innodbClusterName}{"\n"}'
pscluster1

Step 3: Get a service endpoint which is used when setting up the ClusterSet environment. We will use ps-cluster1-mysql-primary, which is mapped to the current Primary node in the existing cluster.

shell> kubectl get services -n ps

NAME                        TYPE        CLUSTER-IP       EXTERNAL-IP   PORT(S)                                           AGE
ps-cluster1-haproxy         ClusterIP   34.118.225.215   <none>        3306/TCP,3307/TCP,3309/TCP,33060/TCP,33062/TCP    29m
ps-cluster1-mysql           ClusterIP   None             <none>        3306/TCP,33062/TCP,33060/TCP,6450/TCP,33061/TCP   29m
ps-cluster1-mysql-primary   ClusterIP   34.118.229.137   <none>        3306/TCP,33062/TCP,33060/TCP,6450/TCP,33061/TCP   29m
ps-cluster1-mysql-proxy     ClusterIP   None             <none>        3306/TCP,33062/TCP,33060/TCP,6450/TCP,33061/TCP   29m
ps-cluster1-mysql-unready   ClusterIP   None             <none>        3306/TCP,33062/TCP,33060/TCP,6450/TCP,33061/TCP   29m

Step 4: The DR site should have the same credentials as DC. We need to export the DC secret containing the credentials and import it into the DR cluster.

shell> kubectl get secret ps-cluster1-secrets -n ps -o yaml > source-secret.yaml

We also need to perform a couple of cleanups in the exported secret file.

• Remove the annotations, creationTimestamp, resourceVersion, selfLink, and uid metadata fields.
• As per the requirement, change the namespace/instance and other unwanted details as per your replica site.

E.g,

shell> yq eval 'del(.metadata.ownerReferences, .metadata.annotations, .metadata.creationTimestamp, .metadata.resourceVersion, .metadata.selfLink, .metadata.uid)' source-secret.yaml > replica-secret.yaml

shell> yq eval '.metadata.namespace = "ps-dr"' -i replica-secret.yaml

shell> sed -i '' 's/ps-cluster1/ps-cluster2/g' replica-secret.yaml

The final ready secret file looks like this. 

apiVersion: v1
data:
  clusterset: XVpMQz0+LEVDe3kyQGR6TQ==
  heartbeat: YmFONW1KTE8oLTxdVVB7Kg==
  monitor: UC1oOHokRksoMnpSMWsmJg==
  operator: SDMrblpSQm0yRHdhV3dteX0=
  orchestrator: cEVqMEUlaGp5fShudyZURHtl
  replication: ZjldZlkwa3ZwNWU8KVpsag==
  root: e3BycEl+LlcrPFVUUTwpaG4=
  xtrabackup: eFhNNENIRF4wJXZ7bjxbKi11ag==
kind: Secret
metadata:
  labels:
    app.kubernetes.io/component: database
    app.kubernetes.io/instance: ps-cluster2
    app.kubernetes.io/managed-by: percona-server-mysql-operator
    app.kubernetes.io/name: mysql
    app.kubernetes.io/part-of: percona-server
  name: ps-cluster2-secrets
  namespace: ps-dr
type: Opaque

DR (pscluster2) Cluster configuration

Step 1: First, we need to import the modified secret file replica-secret.yaml and initialise the DR cluster.

shell> kubectl apply -f deploy/replica-secret.yaml -n ps-dr
secret/ps-cluster2-secrets created

shell> kubectl get secrets -n ps-dr
NAME                      TYPE     DATA   AGE
...
ps-cluster2-secrets   Opaque   8      12s
...

Also, we need to make some modifications to the custom resource file cr.yaml to initialise the DR cluster.

metadata:
  finalizers:
    - percona.com/delete-mysql-pods-in-order
#    - percona.com/delete-ssl
#    - percona.com/delete-mysql-pvc
  name: ps-cluster2

spec:
  crVersion: 1.2.0
  secretsName: ps-cluster2-secrets

mysql:
    clusterType: group-replication
    size: 3
    image: percona/percona-server:8.4.10-10.1
    bootstrap:
      mode: manual

shell> kubectl apply -f deploy/cr.yaml -n ps

Note: We set spec.mysql.bootstrap.mode to manual so Pod mysql-0 does not form a Group Replication group until the ClusterSet adopts it and references the secret file we copied from the DC.

It is also expected that after applying the Custom Resource file cr.yaml, the Pod mysql-0 starts but stays NotReady. The cluster ps-cluster2 reports the Initialising state and the AwaitingExternalBootstrap condition in status. Pods mysql-1 and mysql-2 do not start until Pod mysql-0  joins the Group Replication.

Once we deploy the custom resource, we can notice the status below. Please note that the complete Pods will be ready only once DR successfully syncs and joins the DC cluster. 

shell> kubectl get ps -n ps-dr
NAME          REPLICATION         ENDPOINT                    STATE          MYSQL   ORCHESTRATOR   HAPROXY   ROUTER   AGE
ps-cluster2   group-replication   ps-cluster2-haproxy.ps-dr   initializing                                             17m

shell> kubectl get pods -n ps-dr
NAME                                             READY   STATUS    RESTARTS   AGE
percona-server-mysql-operator-6b78d5f68c-4gsm9   1/1     Running   0          28m
ps-cluster2-mysql-0

Step 2: We need to note down the DR InnoDB cluster name, which will later be used by the ClusterSet.

shell> kubectl get ps ps-cluster2 -n ps -o jsonpath='{.status.innodbClusterName}{"\n"}'
pscluster2

Step 3: We need to identify the DR cluster endpoint that is reachable from the DC. This endpoint will be used to feed the clone and perform other management operations.

We will use the local FQDN ps-cluster2-mysql-0.ps-cluster2-mysql.ps-dr.svc.cluster.local of the ps-cluster2-mysql-0 Pod, which is currently initialised and awaiting the ready state.

shell> kubectl get pods -n ps-dr
NAME                                             READY   STATUS    RESTARTS   AGE
percona-server-mysql-operator-6b78d5f68c-4gsm9   1/1     Running   0          48m
ps-cluster2-mysql-0                              1/2     Running   0          37m

By connecting to the DC Primary Pod, we can verify cross-communication between the DC and the DR.

shell> kubectl exec -it ps-cluster1-mysql-0 -n ps -- sh

sh-5.1$ curl -v ps-cluster2-mysql-0.ps-cluster2-mysql.ps-dr.svc.cluster.local:3306
*   Trying 10.60.1.7:3306...
* Connected to ps-cluster2-mysql-0.ps-cluster2-mysql.ps-dr.svc.cluster.local (10.60.1.7) port 3306 (#0)
> GET / HTTP/1.1
> Host: ps-cluster2-mysql-0.ps-cluster2-mysql.ps-dr.svc.cluster.local:3306
> User-Agent: curl/7.76.1
> Accept: */*

Data Restoration Part

By default, when the DR site initialises and attempts to join the ClusterSet, it receives data from the source/DC via the clone recovery method.

The Operator uses MySQL Shell to create a physical snapshot of the dataset from the Source/DC and transfer it to the DR Replica.

Alternatively, we can manually perform a backup and restore on the target cluster. This strategy helps when integrating the DR site into the ClusterSet, as pre-populating the data and GTID history allows it to apply only delta changes rather than performing a full initial synchronisation. For large datasets or to prevent excessive load and performance degradation on the donor node, a backup-and-restore approach can be considered.

Here, we will use the clone recovery method to sync the DR cluster.

Next, we will deploy the ClusterSet configurations so that DR joins the DC cluster via a data clone and connects via Asynchronous Replication to stay in sync.

Below is the clusterset.yaml file where we pass the various information like (Secrets, DC/DR Endpoints, Cluster Name etc) , which we fetched in some of the above steps earlier.

kind: PerconaServerMySQLClusterSet
metadata:
  name: my-cluster-set
  finalizers:
    - percona.com/clusterset-dissolve
spec:
#  unsafeFlags:
#    forcedFailover: false
#    forcedClusterRemoval: false
  primaryCluster: pscluster1
  credentialsSecret:
    name: ps-cluster1-secrets
    key: clusterset
  sslMode: AUTO
  createReplicaClusterOptions:
    recoveryMethod: clone
  clusters:
    - innodbClusterName: pscluster1
      endpoints:
      - host: ps-cluster1-mysql-primary.ps.svc.cluster.local
        port: 3306
    - innodbClusterName: pscluster2
      endpoints:
      - host: ps-cluster2-mysql-0.ps-cluster2-mysql.ps-dr.svc.cluster.local
        port: 3306
  mysqlshellRunner:
    image: percona/percona-server:8.4.10-10.1

We will apply the changes only to the DC node.

kubectl apply -f deploy/clusterset.yaml

Please note: Member “ps-cluster2-mysql-0” on the DR side will connect to the DC via asynchronous replication. While the local members of the DR will join via the Group Replication mechanism. 

This action will also start a separate backend job on DC, which creates an associated Pod to perform the ClusterSet activity.

shell> kubectl get jobs -n ps
NAME                                    STATUS    COMPLETIONS   DURATION   AGE
my-cluster-set-pscluster2-add-replica   Complete             1/1           23s        53s

….

shell> kubectl get pods -n ps
NAME                                             READY   STATUS    RESTARTS   AGE
my-cluster-set-pscluster2-add-replica-wk7xd      1/1     Running             0          3s

As soon as the process completes successfully, the job and associated Pod will be removed from the list.

There is one extra pod that can be noticed, and it persists. This provides a utility/client for running MySQL Shell commands against the MySQL ClusterSet.

shell> kubectl get pods -n ps
NAME                                             READY   STATUS    RESTARTS   AGE
my-cluster-set-runner-5d68fbcc74-svmpw           1/1     Running   0          10h

If the job fails or errors occur, we need to investigate the exact problem using the information below.

kubectl describe job <jobname>
kubectl describe pod <add-replica pod>
kubectl logs <add-replica pod>

Finally, we can check the Pod status on the DR cluster. It will now reflect all MySQL and haproxy pods in the fully completed/ready state.  

shell> kubectl get pods -n ps-dr
NAME                                             READY   STATUS    RESTARTS      AGE
percona-server-mysql-operator-6b78d5f68c-4gsm9   1/1     Running   0             11h
ps-cluster2-haproxy-0                            2/2     Running   0             36m
ps-cluster2-haproxy-1                            2/2     Running   0             36m
ps-cluster2-haproxy-2                            2/2     Running   0             36m
ps-cluster2-mysql-0                              2/2     Running   1 (37m ago)   11h
ps-cluster2-mysql-1                              2/2     Running   1 (36m ago)   36m
ps-cluster2-mysql-2                              2/2     Running   1 (34m ago)   35m

Verification of ClusterSet completion

We can check the ClusterSet information below if it has been processed successfully without any errors.

shell> kubectl get ps-clusterset my-cluster-set -n ps
NAME             PRIMARY      ENDPOINT                                        READY
my-cluster-set   pscluster1   ps-cluster1-mysql-0.ps-cluster1-mysql.ps:3306   True

shell> kubectl get ps-clusterset my-cluster-set -n ps -o yaml
apiVersion: ps.percona.com/v1
kind: PerconaServerMySQLClusterSet
metadata:
  annotations:
    kubectl.kubernetes.io/last-applied-configuration: |
      {"apiVersion":"ps.percona.com/v1","kind":"PerconaServerMySQLClusterSet","metadata":{"annotations":{},"finalizers":["percona.com/clusterset-dissolve"],"name":"my-cluster-set","namespace":"ps"},"spec":{"clusters":[{"endpoints":[{"host":"ps-cluster1-mysql-primary.ps.svc.cluster.local","port":3306}],"innodbClusterName":"pscluster1"},{"endpoints":[{"host":"ps-cluster2-mysql-0.ps-cluster2-mysql.ps-dr.svc.cluster.local","port":3306}],"innodbClusterName":"pscluster2"}],"createReplicaClusterOptions":{"recoveryMethod":"clone"},"credentialsSecret":{"key":"clusterset","name":"ps-cluster1-secrets"},"mysqlshellRunner":{"image":"percona/percona-server:8.4.10-10.1"},"primaryCluster":"pscluster1","sslMode":"AUTO"}}
  creationTimestamp: "2026-09-11T05:20:08Z"
  finalizers:
  - percona.com/clusterset-dissolve
  generation: 5
  name: my-cluster-set
  namespace: ps
  resourceVersion: "1789143099049519007"
  uid: 6cfdce40-e1e0-47c8-bdae-1cd847938f29
spec:
  clusters:
  - endpoints:
    - host: ps-cluster1-mysql-primary.ps.svc.cluster.local
      port: 3306
    innodbClusterName: pscluster1
  - endpoints:
    - host: ps-cluster2-mysql-0.ps-cluster2-mysql.ps-dr.svc.cluster.local
      port: 3306
    innodbClusterName: pscluster2
  createReplicaClusterOptions:
    recoveryMethod: clone
  credentialsSecret:
    key: clusterset
    name: ps-cluster1-secrets
  mysqlshellRunner:
    image: percona/percona-server:8.4.10-10.1
  primaryCluster: pscluster1
  sslMode: AUTO
status:
  clusters:
    pscluster1:
      clusterRole: PRIMARY
      globalStatus: OK
      primary: ps-cluster1-mysql-0.ps-cluster1-mysql.ps:3306
    pscluster2:
      clusterRole: REPLICA
      globalStatus: OK
      primary: ""
  conditions:
  - lastTransitionTime: "2026-09-11T05:25:07Z"
    message: ""
    reason: DeploymentReady
    status: "True"
    type: MySQLShellRunnerReady
  - lastTransitionTime: "2026-09-11T05:25:10Z"
    message: ""
    reason: ClusterSetBootstrapped
    status: "True"
    type: ClusterSetBootstrapped
  - lastTransitionTime: "2026-09-11T05:25:13Z"
    message: All Clusters available.
    reason: ClusterSetHealthy
    status: "True"
    type: Ready
  lastObservedAt: "2026-09-11T16:11:39Z"
  lastObservedGeneration: 5
  primaryCluster: pscluster1
  primaryClusterEndpoint: ps-cluster1-mysql-0.ps-cluster1-mysql.ps:3306

Also, we can manually access any running MySQL Pod and confirm the ClusterSet status.

shell> kubectl exec -it ps-cluster1-mysql-0 -n ps -- sh

sh-5.1$ mysqlsh --uri=root@localhost:3306

MySQL  localhost:3306 ssl  JS > var clusterset =  dba.getClusterSet()
 MySQL  localhost:3306 ssl  JS > clusterset.status({extended:1})
{
    "clusters": {
        "pscluster1": {
            "clusterRole": "PRIMARY", 
            "globalStatus": "OK", 
            "primary": "ps-cluster1-mysql-0.ps-cluster1-mysql.ps:3306", 
            "status": "OK", 
            "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
            "topology": {
                "ps-cluster1-mysql-0.ps-cluster1-mysql.ps:3306": {
                    "address": "ps-cluster1-mysql-0.ps-cluster1-mysql.ps:3306", 
                    "memberRole": "PRIMARY", 
                    "mode": "R/W", 
                    "readReplicas": {}, 
                    "role": "HA", 
                    "status": "ONLINE", 
                    "version": "8.4.10"
                }, 
                "ps-cluster1-mysql-1.ps-cluster1-mysql.ps:3306": {
                    "address": "ps-cluster1-mysql-1.ps-cluster1-mysql.ps:3306", 
                    "memberRole": "SECONDARY", 
                    "mode": "R/O", 
                    "readReplicas": {}, 
                    "replicationLagFromImmediateSource": "", 
                    "replicationLagFromOriginalSource": "", 
                    "role": "HA", 
                    "status": "ONLINE", 
                    "version": "8.4.10"
                }, 
                "ps-cluster1-mysql-2.ps-cluster1-mysql.ps:3306": {
                    "address": "ps-cluster1-mysql-2.ps-cluster1-mysql.ps:3306", 
                    "memberRole": "SECONDARY", 
                    "mode": "R/O", 
                    "readReplicas": {}, 
                    "replicationLagFromImmediateSource": "", 
                    "replicationLagFromOriginalSource": "", 
                    "role": "HA", 
                    "status": "ONLINE", 
                    "version": "8.4.10"
                }
            }, 
            "transactionSet": "50cc4081-ad9a-11f1-b815-1602eb3926cb:1-4,65edbac6-ad9a-11f1-9dbe-1602eb3926cb:1-139"
        }, 
        "pscluster2": {
            "clusterRole": "REPLICA", 
            "clusterSetReplication": {
                "applierStatus": "APPLIED_ALL", 
                "applierThreadState": "Waiting for an event from Coordinator", 
                "applierWorkerThreads": 4, 
                "receiver": "ps-cluster2-mysql-0.ps-cluster2-mysql.ps-dr:3306", 
                "receiverStatus": "ON", 
                "receiverThreadState": "Waiting for source to send event", 
                "replicationSsl": "TLS_AES_128_GCM_SHA256 TLSv1.3", 
                "replicationSslMode": "REQUIRED", 
                "source": "ps-cluster1-mysql-0.ps-cluster1-mysql.ps:3306"
            }, 
            "clusterSetReplicationStatus": "OK", 
            "globalStatus": "OK", 
            "status": "OK", 
            "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
            "topology": {
                "ps-cluster2-mysql-0.ps-cluster2-mysql.ps-dr:3306": {
                    "address": "ps-cluster2-mysql-0.ps-cluster2-mysql.ps-dr:3306", 
                    "memberRole": "PRIMARY", 
                    "mode": "R/O", 
                    "readReplicas": {}, 
                    "replicationLagFromImmediateSource": "", 
                    "replicationLagFromOriginalSource": "", 
                    "role": "HA", 
                    "status": "ONLINE", 
                    "version": "8.4.10"
                }, 
                "ps-cluster2-mysql-1.ps-cluster2-mysql.ps-dr:3306": {
                    "address": "ps-cluster2-mysql-1.ps-cluster2-mysql.ps-dr:3306", 
                    "memberRole": "SECONDARY", 
                    "mode": "R/O", 
                    "readReplicas": {}, 
                    "replicationLagFromImmediateSource": "", 
                    "replicationLagFromOriginalSource": "", 
                    "role": "HA", 
                    "status": "ONLINE", 
                    "version": "8.4.10"
                }, 
                "ps-cluster2-mysql-2.ps-cluster2-mysql.ps-dr:3306": {
                    "address": "ps-cluster2-mysql-2.ps-cluster2-mysql.ps-dr:3306", 
                    "memberRole": "SECONDARY", 
                    "mode": "R/O", 
                    "readReplicas": {}, 
                    "replicationLagFromImmediateSource": "", 
                    "replicationLagFromOriginalSource": "", 
                    "role": "HA", 
                    "status": "ONLINE", 
                    "version": "8.4.10"
                }
            }, 
            "transactionSet": "50cc4081-ad9a-11f1-b815-1602eb3926cb:1-4,65edbac6-ad9a-11f1-9dbe-1602eb3926cb:1-139", 
            "transactionSetConsistencyStatus": "OK", 
            "transactionSetErrantGtidSet": "", 
            "transactionSetMissingGtidSet": ""
        }
    }, 
    "domainName": "my-cluster-set", 
    "globalPrimaryInstance": "ps-cluster1-mysql-0.ps-cluster1-mysql.ps:3306", 
    "metadataServer": "ps-cluster1-mysql-0.ps-cluster1-mysql.ps:3306", 
    "primaryCluster": "pscluster1", 
    "status": "HEALTHY", 
    "statusText": "All Clusters available."
}

Validate Replication 

• Log in to the Primary member and perform some writes.

shell> export PRIMARY=$(kubectl get pods -n ps -l mysql.percona.com/primary=true -o jsonpath='{.items[0].metadata.name}')
shell> echo $PRIMARY;
ps-cluster1-mysql-0

shell> kubectl exec -it ps-cluster1-mysql-0 -n ps -- sh
shell> mysql -uroot -p

mysql> CREATE DATABASE IF NOT EXISTS test;
mysql> CREATE TABLE IF NOT EXISTS test.t1 (id INT PRIMARY KEY); INSERT INTO test.t1 VALUES (1);

• Connect to the DR and verify the sync.

shell> kubectl exec -it ps-cluster2-mysql-0 -n ps-dr -- sh
shell> mysql -uroot -p

mysql> SELECT * FROM test.t1;
+----+
| id |
+----+
|  1 |
+----+

We can also visit any MySQL Pod and run the following command to get the Primary member and group replication details.

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------------------------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                              | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+------------------------------------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 50cc4081-ad9a-11f1-b815-1602eb3926cb | ps-cluster1-mysql-0.ps-cluster1-mysql.ps |        3306 | ONLINE       | PRIMARY     | 8.4.10         | MySQL                      |
| group_replication_applier | 87137873-ad9a-11f1-ae3f-36b4b899b0ff | ps-cluster1-mysql-1.ps-cluster1-mysql.ps |        3306 | ONLINE       | SECONDARY   | 8.4.10         | MySQL                      |
| group_replication_applier | b952f0d3-ad9a-11f1-95a5-d6e7036e522a | ps-cluster1-mysql-2.ps-cluster1-mysql.ps |        3306 | ONLINE       | SECONDARY   | 8.4.10         | MySQL                      |
+---------------------------+--------------------------------------+------------------------------------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)

DC-DR switchover/failover

Performing a planned switchover or an ad hoc failover process is quite simple here. All we need to execute the operations below.

Switchover:

shell> kubectl patch ps-clusterset my-cluster-set -n $SOURCE_NS \
  --type=merge -p '{"spec":{"primaryCluster":"replicacluster"}}'

Forced Failover:

shell> kubectl patch ps-clusterset my-cluster-set -n $SOURCE_NS --type=merge -p '{
  "spec": {
    "primaryCluster": "replicacluster",
    "unsafeFlags": {
      "forcedFailover": true
    }
  }
}'

So let’s try a Primary switchover activity from DC pscluster1 to DR pscluster2. Currently, pscluster2 has a REPLICA role.

MySQL  localhost:3306 ssl  JS > clusterset.status()
{
    "clusters": {
        "pscluster1": {
            "clusterRole": "PRIMARY", 
            "globalStatus": "OK", 
            "primary": "ps-cluster1-mysql-0.ps-cluster1-mysql.ps:3306"
        }, 
        "pscluster2": {
            "clusterRole": "REPLICA", 
            "clusterSetReplicationStatus": "OK", 
            "globalStatus": "OK"
        }
    }, 
    "domainName": "my-cluster-set", 
    "globalPrimaryInstance": "ps-cluster1-mysql-0.ps-cluster1-mysql.ps:3306", 
    "primaryCluster": "pscluster1", 
    "status": "HEALTHY", 
    "statusText": "All Clusters available."
}

Run Switchover command:

shell> kubectl patch ps-clusterset my-cluster-set -n ps \
  --type=merge -p '{"spec":{"primaryCluster":"pscluster2"}}'

perconaservermysqlclusterset.ps.percona.com/my-cluster-set patched

Watch the progress:

shell> kubectl get ps-clusterset my-cluster-set -n ps -w
shell> kubectl get jobs -n ps | grep switchover

After some time, we can see pscluster2 become the Primary cluster.

shell> kubectl get ps-clusterset my-cluster-set -n ps -w
NAME             PRIMARY      ENDPOINT                                           NAME             PRIMARY      ENDPOINT   READY
my-cluster-set   pscluster1              False
my-cluster-set   pscluster2   ps-cluster2-mysql-0.ps-cluster2-mysql.ps-dr:3306   True

kubectl exec -it ps-cluster1-mysql-0 -n ps -- sh
sh-5.1$ mysqlsh --uri=root@localhost:3306

MySQL  localhost:3306 ssl  JS > clusterset.status()
{
    "clusters": {
        "pscluster1": {
            "clusterRole": "REPLICA", 
            "clusterSetReplicationStatus": "OK", 
            "globalStatus": "OK"
        }, 
        "pscluster2": {
            "clusterRole": "PRIMARY", 
            "globalStatus": "OK", 
            "primary": "ps-cluster2-mysql-0.ps-cluster2-mysql.ps-dr:3306"
        }
    }, 
    "domainName": "my-cluster-set", 
    "globalPrimaryInstance": "ps-cluster2-mysql-0.ps-cluster2-mysql.ps-dr:3306", 
    "primaryCluster": "pscluster2", 
    "status": "HEALTHY", 
    "statusText": "All Clusters available."
}

Once the switchover finishes successfully, all new writes now go to the new Primary cluster ps-cluster2 and the old DC “ps-cluster1” will automatically become the Async Replica. The application should connect with a load balancer (Haproxy/MySQL Router) endpoint, e.g., (ps-cluster2-haproxy), which will forward all requests to the backend node (by default) to the Primary member.

shell> kubectl get svc ps-cluster2-haproxy -n ps-dr
NAME                  TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)                                          AGE
ps-cluster2-haproxy   ClusterIP   34.118.226.22   <none>        3306/TCP,3307/TCP,3309/TCP,33060/TCP,33062/TCP   12h

Key Takeaways

Configuring and managing a multi-region topology manually or on virtual machines can be quite challenging. By packaging these components within the Percona PS Operator/K8S, setting up cross-region environments becomes far simpler. This streamlined deployment is critical for establishing robust disaster recovery solutions, offloading production workloads, or scaling read operations. Furthermore, built-in support for seamless DC-DR switchovers and ad hoc failovers adds significant value to the architecture.

Before deploying these topologies into production environments, it is strongly advised to thoroughly test and validate their behaviour in lower or non-production environments. Proceed with production deployment only after achieving full confidence in the setup. 

The post Group Replication Beyond a Single Cluster: DC-DR with Percona (PS MySQL) Operator appeared first on Percona.

At first glance, it looked like GCache had simply stopped cleaning itself up. The investigation started by answering two simple questions: when did the files start appearing, and what changed in the cluster at that time?

Finding the starting point

The oldest files showed the issue started on July 9.

[hostx] percona@hostx: ~ $ ls -lh /var/lib/mysql/mysql-data/gcache.page.*
-rw-r----- 1 mysql mysql 128M Jul 9 21:42 /var/lib/mysql/mysql-data/gcache.page.000000
-rw-r----- 1 mysql mysql 128M Jul 9 21:42 /var/lib/mysql/mysql-data/gcache.page.000001
-rw-r----- 1 mysql mysql 128M Jul 9 21:42 /var/lib/mysql/mysql-data/gcache.page.000002
...
-rw-r----- 1 mysql mysql 128M Jul 26 07:45 /var/lib/mysql/mysql-data/gcache.page.005976
-rw-r----- 1 mysql mysql 128M Jul 26 07:50 /var/lib/mysql/mysql-data/gcache.page.005977
-rw-r----- 1 mysql mysql 128M Jul 26 07:56 /var/lib/mysql/mysql-data/gcache.page.005978

The newest files showed they stopped being created on July 26, which immediately provided a timeline to investigate.

The creation of GCache page files wasn’t random. It started at a specific point in time and stopped after the next MySQL restart.

Can large transactions cause this?

Normally, you don’t see thousands of GCache page files unless Galera cannot reclaim old pages or an exceptionally large writeset forces additional page allocation. In this environment, the GCache ring file was around 60 GB, making the large writeset theory very unlikely – actually Impossible! Because there’s a hard limit of the largest transaction size at 2GB.

That pushed the investigation toward the error log, where Galera was found logging:

2026-07-09T21:41:04.617132Z 91699 [Note] [MY-000000] [Galera] Freezing gcache purge at 16198595494
2026-07-09T21:41:04.622051Z 0 [Note] [MY-000000] [Galera] Created page /var/lib/mysql/mysql-data/gcache.page.000000 of size 134217728 bytes

This was the first strong clue. When gcache.freeze_purge_at_seqno is active, Galera stops reclaiming old GCache pages. As replication continues, new page files are allocated while existing ones remain on disk.

Correlating with cluster activity

Looking a few seconds later in the error log revealed a cluster partition. The timing is difficult to ignore: Galera froze GCache purging, created the first page file, and then recorded the membership change.

2026-07-09T21:41:04.617132Z 91699 [Note] [MY-000000] [Galera] Freezing gcache purge at 16198595494
2026-07-09T21:41:04.622051Z 0 [Note] [MY-000000] [Galera] Created page /var/lib/mysql/mysql-data/gcache.page.000000 of size 134217728 bytes
2026-07-09T21:41:14.537019Z 53033 [Note] [MY-010559] [Repl] Multi-threaded replica statistics for channel '': seconds elapsed = 122; events assigned = 110401704; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 7659954362300 waited (count) when Workers occupied = 47189196 waited when Workers occupied = 22964040282100
2026-07-09T21:42:02.139966Z 91770 [Note] [MY-010914] [Server] Aborted connection 91770 to db: 'unconnected' user: 'percona' host: '10.1.163.3' (Got an error reading communication packets).
2026-07-09T21:42:17.061513Z 0 [Note] [MY-000000] [Galera] forgetting c3acab8a-8a74 (ssl://10.1.21.5:4567)
2026-07-09T21:42:17.061596Z 0 [Note] [MY-000000] [Galera] Node 7e3c8f1e-ad75 state primary
2026-07-09T21:42:17.061615Z 0 [Note] [MY-000000] [Galera] Current view of cluster as seen by this node
view (view_id(PRIM,7e3c8f1e-ad75,11)
memb {
        7e3c8f1e-ad75,1
        }
joined {
        }
left {
        }
partitioned {
        c3acab8a-8a74,1
        }
)

Although the logs do not explicitly state why purge was frozen, the sequence of events strongly suggests that Galera retained the writesets so the partitioned node could potentially perform an IST when it rejoined.

How could the purge have been frozen?

Further investigation into the codebase and documentation hinted that it is practically impossible that Galera can actually invoke the gcache pages purge pause and only practical way to do it is using:

SET GLOBAL wsrep_provider_options='gcache.freeze_purge_at_seqno=XYZ';

Reference: https://github.com/percona/galera/pull/132

Related reading: No SST node rejoins in PXC

Reproducing the behavior

Rather than stopping with a theory, Peter Sylvester (SoS) reproduced the behavior in the lab by manually setting gcache.freeze_purge_at_seqno and generating workload with Sysbench. The result matched what we observed in the production.

Additionally, even though gcache.keep_pages_count=3, Galera continued creating additional page files because purging was frozen.

mysql> show global status like  'wsrep_last_committed';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| wsrep_last_committed | 94559 |
+----------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL wsrep_provider_options="gcache.freeze_purge_at_seqno=94559";
Query OK, 0 rows affected (0.00 sec)

Sysbench was then run on the cluster’s source host to generate logs…

[root@CENTOS9-1 ~]# sysbench oltp_read_write --db-driver=mysql --mysql-db=sysbench --mysql-user=sysbench --mysql-password=password --mysql-port=3306 --table_size=1000 --tables=4 --threads=4 --rand-type=uniform --range_size=100 --time=0 --rate=0 --report_interval=5 run
WARNING: Both event and time limits are disabled, running an endless test
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 4
Report intermediate results every 5 second(s)
Initializing random number generator from current time

Initializing worker threads...
Threads started!

[ 5s ] thds: 4 tps: 311.80 qps: 6246.62 (r/w/o: 4373.81/1248.40/624.40) lat (ms,95%): 16.71 err/s: 0.00 reconn/s: 0.00

Following page files are present in datadir

[root@CENTOS9-3 ~]# ls -lh /var/lib/mysql/*cache*
-rw-r----- 1 mysql mysql 11M Aug  6 14:14 /var/lib/mysql/galera.cache
-rw-r----- 1 mysql mysql 10M Aug  6 14:14 /var/lib/mysql/gcache.page.000000
-rw-r----- 1 mysql mysql 10M Aug  6 14:14 /var/lib/mysql/gcache.page.000001
-rw-r----- 1 mysql mysql 10M Aug  6 14:15 /var/lib/mysql/gcache.page.000002
-rw-r----- 1 mysql mysql 10M Aug  6 14:15 /var/lib/mysql/gcache.page.000003
-rw-r----- 1 mysql mysql 10M Aug  6 14:15 /var/lib/mysql/gcache.page.000004
-rw-r----- 1 mysql mysql 10M Aug  6 14:16 /var/lib/mysql/gcache.page.000005
-rw-r----- 1 mysql mysql 10M Aug  6 14:16 /var/lib/mysql/gcache.page.000006

After the load completed, the mysqld was restarted to observe if the page files were then cleared! But they were not.

[root@CENTOS9-3 ~]# systemctl stop mysql
[root@CENTOS9-3 ~]# systemctl start mysql
[root@CENTOS9-3 ~]# ls -lh /var/lib/mysql/*cache*
-rw-r----- 1 mysql mysql 11M Aug  6 14:17 /var/lib/mysql/galera.cache
-rw-r----- 1 mysql mysql 10M Aug  6 14:14 /var/lib/mysql/gcache.page.000000
-rw-r----- 1 mysql mysql 10M Aug  6 14:14 /var/lib/mysql/gcache.page.000001
-rw-r----- 1 mysql mysql 10M Aug  6 14:15 /var/lib/mysql/gcache.page.000002
-rw-r----- 1 mysql mysql 10M Aug  6 14:15 /var/lib/mysql/gcache.page.000003
-rw-r----- 1 mysql mysql 10M Aug  6 14:15 /var/lib/mysql/gcache.page.000004
-rw-r----- 1 mysql mysql 10M Aug  6 14:16 /var/lib/mysql/gcache.page.000005
-rw-r----- 1 mysql mysql 10M Aug  6 14:17 /var/lib/mysql/gcache.page.000006

Another interesting observation

Later the MySQL was restarted, expecting the startup to reclaim the unused pages. It didn’t. Every page file remained on disk after a restart. Even after the explicit configuration of gcache.freeze_purge_at_seqno=-1.

This suggests that startup recovery does not automatically remove these accumulated page files simply because gcache.freeze_purge_at_seqno has been cleared. At least in testing, once purge has been frozen and page files accumulate, restarting MySQL alone is not enough to reclaim the space.

We have a new bug in place for getting this behaviour sorted: PXC-5323

Can the files be deleted?

To answer that, MySQL was stopped, the local GCache files were removed and the node was started again.

Galera recreated the required cache structures automatically. More importantly, the node successfully completed an IST; and in our tests,  deleting the local page files did not force an SST.

Before removing the files, make sure the node is stopped and the cluster has another healthy node that can provide the required writesets. Always validate this behavior in your own environment before using it operationally. Our testing showed that an IST was sufficient.

Conclusion

Based on both the production logs and our lab testing, the accumulation of gcache.page.* files was caused by GCache purging being frozen. Restarting MySQL did not reclaim the accumulated files in our testing. The behavior where a PXC node continuously creates new GCache page files is already fixed under: PXC-4495.

The practical workaround is to stop MySQL, remove the local galera.cache and gcache.page.* files, and start the node again. In our testing, the node rejoined the cluster using IST without requiring a full SST. As always, make sure another healthy node has the required writesets before performing this cleanup.

The post Too many GCache Page Files in MySQL Data Directory appeared first on Percona.

“Would making this capability available help a meaningful part of the MySQL community build better applications or run better systems?”

Q1. MySQL has recently made available several features from the enterprise tier to the MySQL Community Edition. What is driving this direction?

The simple answer is that Community Edition needs to stay strong for the people who build, run, and depend on MySQL every day. That includes developers, DBAs, startups, large enterprises, and software vendors.

When we bring broadly useful capabilities into Community Edition, particularly in areas like observability, high availability, performance, and developer experience, the whole MySQL ecosystem benefits. Users get a better platform, and we get better feedback from real workloads at scale.

Recent examples include replication observability and Group Replication capabilities, OpenTelemetry support, the Hypergraph Optimizer, Profile-Guided Optimization, and enhanced JSON Duality View support in Community Edition.

I see this as a clear sign of Oracle’s long-term commitment to MySQL. The Community Edition is fundamental to MySQL. Enterprise Edition continues to address additional commercial support, security, and operational needs, but the success of Community Edition is essential to the success of MySQL overall.

Q2. When Oracle makes a feature available in Community Edition, how are those decisions made?

There is not a single checklist that applies to every feature. We start with a practical question: would making this capability available help a meaningful part of the MySQL community build better applications or run better systems?

From there, we look at technical maturity, operational impact, security, compatibility, documentation, and long-term maintainability. A feature has to work well not only in a carefully controlled environment, but also in the many different environments where MySQL is deployed.

The strongest opportunities are often in areas that improve the daily experience of using MySQL: understanding what is happening in the server, operating highly available systems, diagnosing performance problems, and reducing unnecessary complexity for developers. We will continue to evaluate those opportunities as MySQL evolves.

Q3. What does MySQL do better than competitors in community engagement, and where can it improve?

MySQL’s strength is the combination of a mature open source database, deep engineering investment, and an ecosystem that has been built over decades. MySQL runs important workloads for organizations of every size, so reliability, compatibility, upgrades, tooling, and operational simplicity matter deeply to our community.

We also see opportunities to improve. The best open source communities make it easy for people to understand how an idea moves from discussion to action. We want to improve that path in MySQL through clearer design discussions, better issue triage, more visible contribution paths, and more predictable review and feedback.

Code is important, but it is not the only meaningful contribution. Testing, documentation, benchmarking, production feedback, tools, and community education all make MySQL better. We want contributors to feel that these forms of participation are recognized and useful.

Q4. MySQL 9.7.0 LTS brings capabilities previously limited to MySQL Enterprise Edition—including JSON Duality Views, the Hypergraph Optimizer, and improvements to replication observability and HA behavior—into MySQL Community Edition. Which of these changes do you think will have the greatest real-world impact for DBAs and developers, and what should teams consider before adopting them in production?

I would separate immediate operational impact from longer-term developer impact. For DBAs running Group Replication, the replication observability and HA changes will probably have the fastest and broadest benefit. Better visibility into flow control, applier lag and throughput, unhealthy members, and primary election helps teams diagnose problems earlier and make failover behavior more predictable. This is practical day-to-day value, especially for teams operating clusters at scale.

For developers, JSON Duality Views may be the more consequential change over time. They let teams work with JSON documents while retaining relational integrity and a single source of truth. The Hypergraph Optimizer can also be significant for complex queries, but its benefit will vary more by workload.

Teams should approach an LTS upgrade with thorough validation. Test representative workloads, failure scenarios, replication behavior, upgrades, and monitoring integrations in staging first. For the Hypergraph Optimizer, compare plans and performance for important queries. For JSON Duality Views, validate the data model, update paths, permissions, and concurrency behavior. And for telemetry, make sure the collector, retention policy, and handling of potentially sensitive operational data are ready before turning it on in production. The Community Edition additions cover replication and HA behavior, telemetry, JSON Duality Views, and the Hypergraph Optimizer.

Q5. You were personally involved in designing JSON Duality Views. What problem does it solve?

JSON Duality Views solve a problem many application teams know well. Developers often prefer JSON because it maps naturally to APIs and application objects. But relational modeling gives them normalization, transactional consistency, referential integrity, and SQL.

Historically, teams often had to choose one model, or build and maintain their own mapping layer between application objects and relational tables. In some cases, they also ended up duplicating data across multiple systems.

JSON Duality Views let an application work with hierarchical JSON documents while the underlying data remains relational. The application can use the model that feels natural for the task, but MySQL still provides a single source of truth.

For a team, that can mean less mapping code and simpler synchronization. It does not remove the need for good schema or API design, and it will not fit every application, but it gives suitable workloads a much simpler way to combine document-style development with relational strengths.

Q6. How do you balance new capabilities with MySQL’s simplicity and reliability?

MySQL has earned trust because it is practical. People can deploy it, operate it, upgrade it, and troubleshoot it with confidence. New capabilities must preserve that experience.

We pay close attention to defaults, configuration, backward compatibility, documentation, and operational behavior. Early Access builds, LTS releases, compatibility testing, and upgrade guidance are the practical mechanisms that help us validate that balance before broad adoption. The MySQL 9.6 foreign-key work is a good example. We moved foreign-key checks and cascades into the SQL layer so that those changes are visible to binary logs and CDC tools, while preserving compatibility, validating performance, and providing a temporary `innodb_native_foreign_keys` fallback for staged adoption. A feature should be powerful when users need it, while preserving the straightforward core MySQL experience for everyone else. 

Not every user needs every new capability. Success is giving developers and DBAs useful new options while maintaining the stable, predictable MySQL experience that existing users rely on.

Q7. What does the more open community model look like in practice?

For a developer or DBA, participation should not begin only when they have a patch ready. They can discuss roadmap topics, share use cases, test Early Access releases, file actionable bugs, join GitHub discussions, contribute documentation or benchmarks, and participate in community events and contributor summits.

The important change is connecting these activities more clearly. If someone raises a good issue or proposal, they should be able to see where it goes next. Does it become a design discussion? A bug investigation? A request for testing? A roadmap input? That traceability matters.

Over the coming releases and community cycles, the community should see clearer guidance, more public technical discussion, improved GitHub workflows, and more structured ways to engage early. What matters most is whether people find the process easier to use and receive useful follow-up.

Q8. What changes are being made to improve the contributor experience, and how will you measure success?

The first improvement is clarity. Contributors need to know where to start, what information is needed, how review works, and what happens if a proposal is not accepted as submitted. 

We are working toward clearer templates, better-defined contribution paths, more visible technical discussions, and stronger links between issues, proposals, patches, and bug records. This should make it easier for contributors and users to follow the progress of an idea.

The second improvement is feedback. When a contribution needs further refinement, people should receive a clear outcome, and where possible, practical guidance about what to do next. 
We will look at evidence: response and resolution trends, time to initial triage, review cycle time, contributor growth, contribution quality, roadmap participation, Early Access adoption, and feedback from contributors. We also intend to share progress regularly, pairing timely acknowledgment with meaningful follow-through.The goal is not simply to collect more pull requests. It is to create a community process that produces better outcomes.

Q9. MySQL recently marked 30 years. What will define the next decade?

MySQL must remain the practical, dependable choice for the application workloads that matter most: transactional systems, cloud-native services, distributed applications, and data-intensive workloads.

That means continued investment in performance, high availability, observability, security, developer productivity, and operational simplicity. These may not always be the most visible areas of innovation, but they are the reasons people trust a database in production.

The other important aspect is community participation. MySQL cannot thrive for another decade based only on work from one company. The community can influence priorities earlier, contribute effectively, build tools and extensions, share operational knowledge, and see that its feedback leads to visible action.

By the time MySQL turns 40, I would like it to be known not only for scale and reliability, but also for a community that has a real and practical role in shaping its future.

Qx. Anything else you wish to add?

I would encourage people to engage with MySQL early and directly. Try the Early Access releases, share concrete production experience, bring specific use cases, and tell us where the friction is.

The most useful feedback is grounded in real workloads and comes with enough detail for us to act on it. MySQL has always evolved through the combined work of engineers, users, customers, partners, and contributors. We want the next chapter to be even more collaborative.

………………………………………………………..

Gopal Shankar, Director of MySQL Engineering, Oracle.

For over 20 years, I have worked at the heart of database engine architecture. Currently, as the Director of MySQL Engineering, I lead the organization responsible for the strategy, development, and roadmap of one of the world’s most popular database platforms. My expertise lies in the core internals of MySQL specifically kernel-level development, performance tuning, and scalability. I believe in solving complex technical challenges by prioritizing architectural simplicity and resilience. I have led the design of several important features, including the MySQL 8.0 Data Dictionary and Information Schema, as well as the recent JSON Duality feature. Additionally, I have helped architect the integration of foreign key handling directly into the SQL layer, effectively resolving long-standing trigger cascade limitations in MySQL recently. My goal is to deliver features that are high-performing, reliable and developer-friendly. I am focusing towards executing the MySQL roadmap and ensuring MySQL platform remains a powerful, solid foundation for modern applications. Beyond strategy, I stay connected to the kernel-level complexities tackling issues like high CPU usage, database corruption, and throughput bottlenecks. I am focused on fostering technical excellence and delivering a database engine that evolves with the needs of the industry.

https://www.linkedin.com/in/gopal-shankar-1b34664/

………………….

Follow us on X

Follow us on LinkedIn

That repetition is what turned into DBCanvas, a self-hosted lab for designing, deploying, operating, and stress-testing multi-node database stacks on my own machine. Just design a topology on a canvas, click Deploy, and get real running nodes connected to the services and supporting infrastructure your test requires. Then, use the tools built into it or third party tools to load those databases, watch them work, and figure out why they’re misbehaving.

The code is up at github.com/jaimesicam/dbcanvas.

Figure 1: Deploying a multi-node MySQL topology with monitoring and orchestration

It’s vibe coded, and that’s the point

I want to be upfront about this that DBCanvas is vibe coded and built conversationally with an AI coding assistant rather than hand-written line by line. That turned out to be exactly the right approach for a tool whose whole job is to remove setup friction.

My initial loop or workflow looked like this. I started by asking the assistant to generate UI/UX demos for the frontend with React, a backend with Go, a drag-and-drop node canvas, a user management system and I iterated it until it felt right. Once I was satisfied, I asked it to turn those requirements into a SCAFFOLD.md which contained a full blueprint precise enough for the coding agent to rebuild the app from scratch as it contained the tech stack, naming conventions, directory tree, backend behavior, frontend behavior and the interactive details of the node editor itself.

Figure 2: The initial interface prototype that established the visual direction

Figure 3: The initial node-editor prototype for composing connected services

From there, I added features incrementally, budgeted by whatever tokens I had available in a session and logged every change in an IMPLEMENTATION.md so that I have a record of what it took to go from the original scaffold to wherever the project currently stood.

Figure 4: IMPLEMENTATION.md records each feature added after the initial scaffold

If I ever needed to rebuild the project from nothing, those two files are essentially the whole story. I know it is crude, but it’s my first time building with this many moving parts. It has held up so far at least for me.

The current loop looks like this:

1. Hit friction while testing, debugging, or learning something new.
2. Describe the environment that would remove that friction.
3. Let the assistant scaffold the automation: versions, configuration, identity, data, and tooling for that environment.
4. Keep whatever turned out to be reusable inside DBCanvas for next time.
5. Turn the whole workflow into something you can drive from a browser.

When Percona Server 8.4.11-11 shipped OpenID Connect authentication, I didn’t want to manually set up a Keycloak instance, wire up realms and clients, create sample identities, and configure Percona Server’s OIDC plugin every time I wanted to poke at it. Instead, that became a new DBCanvas setup. Deploy Keycloak + Percona Server + sample identities with a few clicks. You can inspect the generated OIDC configuration, authenticate with an ID token, and verify the mapped MySQL role, all without setting this up yourself. Vibe coding is what made it fast enough to build that scaffolding the same day the feature landed, and DBCanvas turned it into a reusable setup I can deploy again whenever I need it.

Figure 5: A Percona Server OIDC lab with Keycloak and a success mapped-role login

It’s not just about one feature… it’s a whole lab

OIDC with Keycloak is just one example. DBCanvas can also build a broader range of database environments:

• MySQL: Percona XtraDB Cluster, Percona Server, MySQL Community, asynchronous replication, InnoDB Cluster and Group Replication, and MariaDB
• PostgreSQL: standalone, Patroni, repmgr, Spock multi-master, CloudNativePG or Crunchy PGO on Kubernetes
• MongoDB: Percona Server for MongoDB as standalone, replica set, or sharded cluster
• Valkey: standalone and cluster

And around them, the infrastructure that makes a stack behave like a real environment. An Intranet node can provide DNS, mail, OpenLDAP, a Squid proxy and a certificate authority. Other nodes provide PMM, ProxySQL, HAProxy, Orchestrator, SeaweedFS S3, Keycloak, OpenBao, Samba AD DC, and a Kubernetes frame that runs any of six database operators.

A useful lab also needs activity. DBCanvas ships application simulators for a hotel booking system, an airline, a car rental fleet, and a stock exchange. Spin up a MongoDB replica set and point the stock market simulator at it and writes begin flowing through the set. You can observe elections and inspect real oplog activity instead of manually generating load against an idle cluster.

Figure 6: A MongoDB replica set running the stock market simulator with diagnostic tools attached

Leaning on tools other people already built

DBCanvas can make deployments more useful by integrating tools my colleagues have built for managing, troubleshooting and analyzing database environments. I can add them as part of the deployment workflow and place it alongside the database it is intended to work with.

• MClusterAdmin, a MongoDB administration panel created by Przemek Malkowski, runs as its own node alongside a MongoDB deployment. It displays topology and replica-set status, sharding and the balancer, slow queries with explain, indexes, users and roles, all in a browser tab.

Figure 7: MClusterAdmin displaying the replica-set state for a DBCanvas deployment

• Big Hole, an FTDC viewer, developed by Zelmar Michelini that decodes diagnostic.data. Drag in a folder and Big Hole charts every metric it can find. In DBCanvas, collecting the data is just as straightforward: right-click a MongoDB node, open the context menu, and download a compressed archive containing its logs and diagnostic data. Simply decompress the archive to your download directory, then drag the folder into Big Hole to visualize FTDC metrics alongside related log events.

Figure 8: Big Hole visualizing FTDC metrics downloaded from a MongoDB node

The same principle applies to deeper diagnostic work. DBCanvas automates the setup around proven tools instead of replacing them.

For example, the Operator Debugger uses Delve to step through Kubernetes operators with breakpoints, call stacks and variables.

Figure 9: Operator Debugger paused at a breakpoint inside a Percona operator

There’s also the Core Dump Analyzer where you can mount a core dump and matching binaries read-only, then inspect it with GDB.

Figure 10: Web-based Core Dump Analyzer with equivalent terminal command for manual troubleshooting

Ease of Use

While DBCanvas makes it easy to deploy environments but for troubleshooting, you still need to look deeper into the implementation within the nodes. Accessing the deployment via web terminal, regular terminal or web-based Filemanager would be helpful to have command of the deployment.

Figure 11: The file manager inspecting a MongoDB configuration inside a deployed node

The node menu provides the exact Docker exec command for direct access from a regular terminal.

Figure 12: Direct container access from a Docker exec command copied from the node menu

Testing beyond deployment

DBCanvas also comes with a data generator, a parallel query runner, a benchmark tool (OLTP/OLAP, read-write and read-only), and a packet inspector that decodes MySQL, PostgreSQL, MongoDB and Valkey traffic off the wire.

Figure 13: Data Generator creating sample rows for a deployed database

Figure 14: Packet Inspector decoding MongoDB traffic from the lab network

Together, these tools help reproduce problems facing a real deployment as I can generate data, apply load, inspect queries and examine network traffic.

Try it

git clone https://github.com/jaimesicam/dbcanvas.git && cd dbcanvas
make install

That builds the node images and starts DBCanvas at http://localhost:8080. The first run takes a while since it’s building docker images from scratch and learning which software versions are available for each OS.

Three important caveats:

DBCanvas creates disposable labs for previewing, testing and learning. It’s not a production software. It uses default credentials and favors setup speed over production security.

My workstation remains its primary test environment. If you run it elsewhere and encounter a bug or rough edge, please open a GitHub issue.

Some features are still maturing. I chose to bring many capabilities into one project, which means a few areas still need additional refinement and testing. For example, the Core Dump analyzer could benefit from more sophisticated variable extraction as well as the automatic detection of the appropriate operating system and compatible debug libraries to deploy.

If you’ve ever rebuilt the same test cluster for the third time this month, or wished you could hand a colleague a working reproduction instead of a page of setup instructions, try DBCanvas as it might save you some of the time it has saved me.

MySQL 9.x added the VECTOR data type so you can store an embedding. You get a VECTOR column, functions to convert to and from text, and VECTOR_DIM() to ask how wide a vector is.

What you don't get is a way to compare two vectors. The 9.x community server has no distance function and no vector index. MySQL 8.4 has none of this at all. You can't add the missing index yourself either because vanilla MySQL's list of index types is fixed to B-tree, R-tree, hash, and full-text.

That's where VillageSQL comes in. VillageSQL is the innovation platform for MySQL that adds an extension framework (similar to PostgreSQL's extension framework) to enable permissionless innovation. Instead of waiting for a feature to be implemented in a few years in a future version of MySQL, new functionality can be dynamically added to a version of MySQL you run today.

VillageSQL already supports custom functions and custom data types, and we've been working on custom indexes. With custom indexes, an extension can define a whole index type including how it's stored in InnoDB, how it's built as rows arrive, and how it's searched. The server treats the custom index as a first-class index, the same as the built-in index types. The server even plans queries against the custom index and reports it by name.

Vector search is a key feature for AI-era applications, so that's the first use case for custom indexes we are building.

At Percona Live in Amsterdam on September 9, 2026, we demoed an early build of this vector work (not yet merged). Below is the annotated demo.

Start with a server

The demo starts by installing VillageSQL. You copy one line from the website, paste it into a terminal, and you have a prebuilt server.

curl -fsSL https://install.villagesql.com | bash

That gets you a stable server. Please note the vector work highlighted below runs on an unmerged, development build.

Declaring a vector index

With the server up, the demo installs the vsql-vector extension with an ordinary INSTALL EXTENSION, and then creates one table:

CREATE TABLE demo_vectors (
  id INT PRIMARY KEY,
  embedding SVECTOR(3) NOT NULL,
  INDEX idx_embedding (embedding hnsw_l2) USING EXTENDED(hnsw)
) ENGINE=InnoDB;

USING EXTENDED(hnsw) names a custom index type and SVECTOR is a custom column type. The extension supplies both. The server stores the metadata, plans the query, and hands the work off. Once the table exists, the vectors arrive by plain INSERT.

Does the server use it?

You can ask the server whether it will really use that index:

EXPLAIN FORMAT=TREE SELECT id FROM demo_vectors
  ORDER BY l2_distance(embedding, '[1.0,2.0,3.0]') LIMIT 3\G

The plan comes back as Custom index distance scan on idx_embedding, which is the server saying it would answer from the HNSW graph instead of reading every row. Running that same SELECT without EXPLAIN in front of it returns the three nearest rows, closest first.

Fast and correct

The demo then builds two tables that hold the same 20,000 vectors. One table has an HNSW index on the vector column and the other has no index at all, and the same nearest-neighbor query runs against both.

Across 50 queries each, the indexed table averages 0.19 ms and the full scan 3.82 ms, which is 20x slower. This is synthetic data at 3 dimensions, so read it as a mechanism check rather than a benchmark.

Speed alone does not prove an index is beneficial, because an approximate index can be fast by being wrong. The unindexed table holds the same vectors, so it gives the exact answer to compare against. Here the index returns nine of the true top ten, and the tenth row is the price of an approximate search. ef_search is a dial that allows you to tune accuracy and performance.

Where it lands on real embeddings

We ran ann-benchmarks against fashion-mnist-784 on an 8 vCPU Xeon VM. Below are our initial results. When vector indexes are merged, we’ll publish steps to reproduce these benchmark results.

fashion-mnist-784 — 60,000 vectors, k=10, 8 vCPU Xeon

These numbers are a snapshot of the work which is still in progress. They come from one early build, and we expect them to move. The shape of the curve is the interesting part. Each step up in ef_search buys recall and costs throughput. Going from 50 to 100 adds 0.17 points of recall and gives up about 30% of the queries per second. Going to 200 adds another 0.07 points and costs about half. You can choose the point on that curve that your application needs.

How it fits together

The server treats the extension's index as a real one. Ask what indexes a table has, and the vector ones come back as HNSW, sitting next to an ordinary BTREE. SHOW CREATE TABLE (from a different example table with two vector columns) returns the full definition:

KEY `idx_title` (`title_vec` `vsql_vector`.`hnsw_cosine`)
  USING EXTENDED(`vsql_vector`.`hnsw`) WITH (`ef_construction` = 64, `m` = 8),
KEY `idx_body` (`body_vec` `vsql_vector`.`hnsw_l1`),
KEY `idx_tag` (`tag`)

M and ef_construction are the extension's own build knobs. The server stores them without knowing what they mean, and prints them back in DDL you can replay. The vectors themselves sit in InnoDB's own storage, so there's no shadow table and no second write on insert. Columns go up to 3,072 dimensions (for comparison, pgvector supports 2,000 dimensions for an indexed column), and one table can carry several vector indexes, each on its own column with its own distance function.

What's next

Filtered search, deletes, wider version coverage, and comparison benchmarks are all still ahead. We expect to deliver the custom index framework for both 8.4 and 9.7 codebases.

This is an initial build of unreleased work, and we wanted to show it while it's still moving. If you want to read the code in the meantime, the extension is at github.com/villagesql/vsql-vector, and you can get the server from villagesql.com. VillageSQL Server supports MySQL 8.4, 9.7, and Percona Server 8.4.

Please let us know your feedback. You can find us on Discord or on GitHub Issues.

My previous post (Performance Progression of Percona Server for MySQL 8.4) did a brief review of the performance changes in Percona Server for MySQL 8.4 released in 2026. I recommend reading it first to better understand the material in this post.

Version 8.4.11-11 includes patches that deliver significant improvements in performance and scalability.

One of the most impressive illustrations of the performance bump is reflected in the following graph:

Graph 1 – Percona-Server 8.4.X with 8G buffer  [ INTERACTIVE GRAPH ][ TABLE ]

There are two separate improvements in the version 8.4.11-11:

• Faster speed for the number of threads smaller than the number of CPU cores. On a host with 40 CPU cores + hyperthreading, which enables simultaneous execution of 80 threads, all versions of the server keep increasing TPS up to 64 threads. However, the version 8.4.11-11 does a noticeably better job in this section.
• Prevent abrupt performance degradation for high thread numbers, which is an issue in the versions 8.4.8-8 and 8.4.10-10. Also, a similar degradation is observed in Upstream MySQL 8.4.11.

All code changes improved the performance of the InnoDB buffer pool and the way it handled pages flushing.

Why is this important?

InnoDB is the default storage engine in Percona Server for MySQL. Therefore, InnoDB performance influences the overall performance of the database server.

The InnoDB engine has a buffer pool, which is basically a data cache in RAM. Instead of reading from disk for every query, the server keeps the most-used data pages in memory.

Its performance matters because:

• RAM is many times faster than disk – the more queries get results from the buffer pool, the faster the database works.
• Every query uses the buffer pool. If its internal locks are slow, all queries queue up behind each other.
• The buffer pool must constantly free space for new data. So, the queries do not stall because they need to do extra cleanup work themselves.

InnoDB stores data in fixed-size pages (16 KB by default). Tables and indexes are split into pages, and a page is the unit that moves between disk and the buffer pool – it never reads a single row from disk, always the whole page containing it.

To better understand the mechanism responsible for the InnoDB buffer pool handling of pages we need to explain the Least Recently Used (LRU) concept.

LRU is the strategy used to decide which pages should be removed from the buffer pool when space runs low. All pages in memory reside in a list ordered by usage: recently accessed pages stay near the “hot” end, untouched ones drift to the “cold” end, and get evicted first. This keeps frequently used data in fast memory, while rarely used data is written back to disk.

The implementation.

The code is available in the GitHub repository for anyone to see:

https://github.com/percona/percona-server/

PS-11444: Narrow the LRU mutex scope in buf_page_init_for_read()
(GitHub: 52d87557)

Significant improvement for read-heavy (IO-bound) workloads. Previously, every physical page read held the pool-wide LRU list mutex while doing extra work, including zeroing a 16 KB page frame – so all reads on a buffer pool instance were serialized on one lock. Now the mutex only covers the actual LRU list insertion, and the rest runs under a much finer-grained page-hash latch. As a result, pages could temporarily become visible in the page hash table prior to their insertion into the LRU list, which requires careful handling in the codebase. Physical reads can now proceed in parallel rather than queueing behind one another. It also fixes a related race (PS-9837) and removes LRU-mutex contention from the purge thread.

NOTE: This improvement was possible by analyzing the lock waits in the profiling data, which pointed to the most contended mutex, which was in LRU list.

PS-11445: LRU optimization: LRU manager threads are restored

In this multi-patch ticket the LRU threads were restored to their original state and a few other performance improvements were done.

Restore per-buffer-pool LRU manager thread

(GitHub: 53bb2f0f)

With this patch each buffer pool instance gets its own background thread that evicts and flushes cold pages to keep free pages available. When this is not done, user queries needing a free page might start  doing cleanup work inline during the query execution, which increases the execution time.

Close a shutdown/invalidation race

(GitHub: 37cdd537)

This commit has no direct performance impact.

Add innodb_lru_threads sysvar

(GitHub: 1a1185b4)

Adds the ON/OFF switching for the LRU manager through innodb_lru_threads system variable. Also, the patch restructures flush statistics, so LRU threads don’t need to synchronize with each other when updating counters – stats are aggregated by a single coordinator thread.

Count evictions as adaptive-sleep progress

(GitHub: c80f16557)

The LRU thread adapts how long it sleeps based on whether it is “making progress”. In the previous implementation only flushing pages counted as progress, so freeing memory by evicting clean pages looked like failure and made the thread back off wrongly. Now evictions count as progress too, so the LRU thread avoids backing off under clean-page workloads.

Single-page-flush concurrency cap

(GitHub: 2624d116)

Fixes a low-concurrency throughput drop: when a background LRU batch was already running, a user thread needing a free page always sat waiting for the whole batch to finish. Now the thread first scans for a page it can free immediately, only waits when it truly has to, and never waits twice in one call (avoiding starvation). Adds two monitoring counters for observability.

PS-11446: Fix inverted old/young check in LRU scan iterator

(GitHub: dc344fba)

This improvement fixes a bug when the background flusher wasted CPU time re-scanning the same pages over and over because it started from LRU tail each time. After the fix, the LRU scan iterator remembers where it left off and continues from there.

NOTE: This bug existed in MySQL and Percona Server for MySQL for a long time unnoticed. It was discovered only because the LRU code was re-assessed for possible performance improvements.

Summary

The overall performance improvements were delivered by: faster physical reads through much less lock contention (PS-11444), background threads keeping free pages ready so queries don’t stall on evictions (PS-11445) and no wasted rescanning in the flusher (PS-11446).

The post Performance improvements in Percona Server 8.4.11-11 appeared first on Percona.

PXC has a variable for exactly that case, pxc-encrypt-cluster-traffic, which handles SSL encryption for inter-node traffic, including State Snapshot Transfer (SST), Incremental State Transfer (IST), and the group communication the nodes use for replication.

The variable is not dynamic, and turning it on normally costs a full cluster restart since the node that encrypts traffic listens on ssl:// while its peers are still on tcp://. If a node joins the cluster with TLS enabled while remaining nodes don’t, the restarting node fails to reach out to the other peers with the following error:

2026-09-02T02:16:02.359992Z 0 [Note] [MY-000000] [Galera] Failed to establish connection: wrong version number

This is a common OpenSSL error seen when a client sends encrypted TLS traffic to a server that expects unencrypted plaintext.

Starting with PXC 8.0.28 and in all PXC 8.4 versions, there is a way to work around the full cluster restart, which relies on the Galera socket.dynamic option. In this post I’ll go through how this option works and how it can be used to enable TLS without downtime.

The socket.dynamic Option

This provider option lets a node establish communication with other peers using both encrypted and plaintext connections. The instance will initially open an encrypted connection, and if it fails since the other nodes are not using TLS, it will retry to establish an unencrypted connection.

The Joiner error log will show the following sequence:

2026-09-02T02:16:43.321209Z 0 [Note] [MY-000000] [Galera] Failed to establish connection: wrong version number
2026-09-02T02:16:43.321521Z 0 [Note] [MY-000000] [Galera] (565f0a92-8283, 'tcp://0.0.0.0:4567') connection established to 1c72fb64-ac57 tcp://172.18.0.3:4567
2026-09-02T02:16:43.321566Z 0 [Note] [MY-000000] [Galera] (565f0a92-8283, 'tcp://0.0.0.0:4567') connection established to 2001e9dd-a09a tcp://172.18.0.4:4567

The first line is the same error we saw previously: our node initiated a TLS handshake, but the peer responded with something that was not TLS. Without socket.dynamic, the node would stop at that point and never join, but with it, it retries (without encryption) and is able to establish a connection.

This Galera option can’t be changed at runtime, so you need to restart the node to enable it.

The Procedure

In this procedure, we consider a typical PXC architecture with 3 nodes. Enabling encryption requires two rolling restarts: the first turns TLS on while still allowing plaintext connections, and the second removes that option and forces only TLS connections.

1. Distribute the same certificate files across all nodes:

Whether the certificates are auto-generated by MySQL or issued by us, every member of the cluster has to use the same key and certificate files. It’s recommended to keep the certificates outside the data directory for security and configuration clarity, and to keep them clear of SST since a joiner wipes its data directory before a full state transfer, and only files matching the [sst] cpat pattern survive. That default covers *.pem, so the MySQL-generated names are safe, but a .crt or .key left in the data directory is deleted.

2. Update the my.cnf on every node:

– pxc-encrypt-cluster-traffic=ON, this switch adds TLS for group communication, IST, and SST.

– socket.dynamic=YES option inside the wsrep_provider_options variable, so each node can speak both protocols while the rollout is in progress.

– The ssl-* variables, in case we’re setting the certificates outside the default path, which is the data directory.

– An [sst] section with the encrypt=4 option and the ssl-* variables. Since pxc-encrypt-cluster-traffic enforces SST channel to encrypt=4, this is a safety measure for those that did not yet performed the change, so donor and joiner can communicate over TLS while the change has not been applied in all nodes. Even though nothing has been restarted, the SST script re-reads my.cnf on every transfer, so as soon as the file is in place the donors encrypt SST whether or not they have been restarted.

The my.cnf file shows the following values:

[mysqld]
pxc-encrypt-cluster-traffic=ON
wsrep_provider_options="socket.dynamic=YES"
ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

[sst]
encrypt=4
ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

3. Restart node #1 and node #2, one at a time:

Wait for each node to rejoin and report Synced before moving to the next. A restarted node will try TLS first and then fall back.

You can verify the change was correctly applied by running the following query. Since the SSL settings are in the wsrep_provider_options variable, which is a long string of key/value pairs, we pull out only the ones we are interested in:

mysql> SELECT
    -> REGEXP_SUBSTR(@@wsrep_provider_options, 'socket\\.dynamic = [^;]+')  AS socket_dynamic,
    -> REGEXP_SUBSTR(@@wsrep_provider_options, 'socket\\.ssl = [^;]+')      AS socket_ssl,
    -> REGEXP_SUBSTR(@@wsrep_provider_options, 'socket\\.ssl_ca = [^;]+')   AS socket_ssl_ca,
    -> REGEXP_SUBSTR(@@wsrep_provider_options, 'socket\\.ssl_cert = [^;]+') AS socket_ssl_cert,
    -> REGEXP_SUBSTR(@@wsrep_provider_options, 'socket\\.ssl_key = [^;]+')  AS socket_ssl_key\G
*************************** 1. row ***************************
 socket_dynamic: socket.dynamic = YES
     socket_ssl: socket.ssl = YES
  socket_ssl_ca: socket.ssl_ca = /etc/mysql/certs/ca.pem
socket_ssl_cert: socket.ssl_cert = /etc/mysql/certs/server-cert.pem
 socket_ssl_key: socket.ssl_key = /etc/mysql/certs/server-key.pem
1 row in set (0.00 sec)

The provider option socket.ssl and ssl certificates are passed down from MySQL to the provider because pxc-encrypt-cluster-traffic is on.

That confirms the settings reached the provider. That said, the replication traffic is not encrypted yet.

We can check this by using tcpdump on node #1 to listen on the replication port (4567) packets coming from node #2:

$ tcpdump -i any -nn -A -s0 "tcp port 4567 and host node2" -c 2000 | grep -a table_test

On node #2 we create the table_test that will be replicated to the other nodes:

mysql> create table test.table_test (c1 integer primary key);
Query OK, 0 rows affected (0.10 sec)

Since in PXC DDLs are replicated as a statement, tcpdump on node #1 shows the create table command in plaintext:

...x..x.j.....y............................ ..E......std............test.create table test.table_test (c1 integer primary key).......

4. Restart the third node with socket.dynamic=NO:

Node #3 does not need the socket.dynamic option since the other two nodes are already capable of talking either TLS or plaintext.

We modify the my.cnf option file as below:

[mysqld]
wsrep_provider_options="socket.dynamic=NO"

And restart the node. This node will start and request only TLS from the other nodes, which they can respond to, since they already have all the changes in place.

The node will show reaching out to cluster peers over ssl://

2026-09-02T02:19:03.640009Z 0 [Note] [MY-000000] [Galera] (aac049fc-a401, 'ssl://0.0.0.0:4567') connection established to 8d5f826f-8dd1 ssl://172.18.0.3:4567
2026-09-02T02:19:03.640410Z 0 [Note] [MY-000000] [Galera] (aac049fc-a401, 'ssl://0.0.0.0:4567') connection established to 565f0a92-8283 ssl://172.18.0.2:4567

5. Restart node #1 and node #2, turning off the socket.dynamic option:

In order to disallow the non-encrypted option and to establish a TLS connection with the other nodes, nodes #1 and #2 should be restarted.

Similar to the previous step, when restarting, the nodes will establish communication on TLS.

Node #1

2026-09-02T02:20:12.565801Z 0 [Note] [MY-000000] [Galera] (d3d58399-9ea2, 'ssl://0.0.0.0:4567') connection established to aac049fc-a401 ssl://172.18.0.4:4567
2026-09-02T02:20:12.565855Z 0 [Note] [MY-000000] [Galera] (d3d58399-9ea2, 'ssl://0.0.0.0:4567') connection established to 8d5f826f-8dd1 ssl://172.18.0.3:4567

Node #2

2026-09-02T02:21:20.911318Z 0 [Note] [MY-000000] [Galera] (fc923848-a6d1, 'ssl://0.0.0.0:4567') connection established to d3d58399-9ea2 ssl://172.18.0.2:4567
2026-09-02T02:21:20.911339Z 0 [Note] [MY-000000] [Galera] (fc923848-a6d1, 'ssl://0.0.0.0:4567') connection established to aac049fc-a401 ssl://172.18.0.4:4567

To confirm the replication traffic is encrypted, we can perform the same check as in step 3, by running tcpdump on node #1 and listening on the replication port (4567) for packets coming from node #2:

$ tcpdump -i any -nn -A -s0 "tcp port 4567 and host node2" -c 2000 | grep -a table_test

On node #2 we drop the table_test previously created:

mysql> drop table test.table_test;
Query OK, 0 rows affected (0.04 sec)

tcpdump does not show a matching pattern since the replication traffic is now encrypted.

Disabling TLS

We can use the same mechanism to disable TLS for whatever reason. As with enabling it, this takes two rolling restarts. Starting from a cluster where every node has pxc-encrypt-cluster-traffic=ON we perform the following steps:

– Restart nodes #1 and #2 with socket.dynamic=YES, keeping pxc-encrypt-cluster-traffic=ON, so they accept plaintext connections again. 

– Restart node #3 with pxc-encrypt-cluster-traffic=OFF, it will come up in plaintext only, and the other two nodes will accept it because they are back to accepting both protocols.

– Restart nodes #1 and #2 with pxc-encrypt-cluster-traffic=OFF and without socket.dynamic, which leaves the whole cluster in plaintext.

– Finally, remove from the sst section the encrypt and SSL related variables.

Conclusion

Enabling TLS for inter-node traffic can be done without stopping the whole cluster. The socket.dynamic option lets a node accept both encrypted and plaintext connections during the transition, so the change can be rolled out one node at a time. Please note we’ll need at least two rolling restarts to achieve that the cluster fully communicates via TLS.

The post Enabling TLS in PXC without Downtime appeared first on Percona.

There are a few cryptography tasks that are common to many backends. For example, you should store passwords such that a database leak doesn't expose them, you should sign payloads so the receiving side can verify them, you should keep a column's data unreadable outside the database, and you should mint tokens an attacker cannot guess. MySQL's built-in functions cover part of that ground but fall short for many users. The SHA2() function will hash a password, but nothing salts it for you, so two people who pick the same password end up with the same stored value. MySQL does not have a built-in HMAC function. An HMAC is the keyed hash a receiver uses to tell a real message from a forged one.

When a database doesn't solve a problem natively, the work invariably moves up into application code, where every service reimplements it just a little bit differently. This adds ongoing maintenance complexity.

The vsql-crypto extension for VillageSQL Server adds a number of useful encryption and cryptographic capabilities to MySQL. It provides hashing, HMAC (Hash-based Message Authentication Code), salted password hashing, AES (Advanced Encryption Standard) encryption, and secure random generation, all backed by the OpenSSL cryptography library. For those moving from PostgreSQL or familiar with pgcrypto, it uses familiar function names: digest, hmac, crypt, gen_salt, encrypt, decrypt, gen_random_bytes, and gen_random_uuid.

VillageSQL is the innovation platform for MySQL that adds an extension framework (similar to PostgreSQL's extension framework) to enable permissionless innovation. Instead of waiting for a feature to be implemented in a few years in a future version of MySQL, new functionality can be dynamically added to a version of MySQL you run today. VillageSQL Server now supports MySQL 8.4, 9.7, and Percona Server 8.4. The vsql-crypto extension is an example of what we mean by permissionless innovation.

If you installed VillageSQL with the install script, the Docker image, or a release tarball, vsql-crypto comes bundled with the server. Installing the extension is then just one statement:

INSTALL EXTENSION vsql_crypto;

The rest of this post walks through four examples of how to implement common cryptography scenarios using vsql-crypto and VillageSQL: passwords that survive a database hack, signatures your webhooks can check, encryption that hides which rows match, and randomness worth trusting.

Set up a working demo of pgcrypto-style cryptography in MySQL on my machine,
using the vsql-crypto extension for VillageSQL. Work only against a local
throwaway server. If the only VillageSQL or MySQL server you find looks like
something I depend on, stop and ask me before touching it.

Do all of this yourself, and show me the real output of each step:

1. Find a VillageSQL server, or install one. The install script needs a codebase and a method:
   `curl -fsSL https://install.villagesql.com | VSQL_CODEBASE=mysql-8.4 INSTALL_METHOD=prebuilt bash`.
   Confirm with `SELECT VERSION()` before continuing. vsql-crypto needs no
   server flags, so do not turn on preview extensions for it.

2. Install the extension: `INSTALL EXTENSION vsql_crypto;`. It ships with the
   server, so there is nothing to download. Then read
   `INFORMATION_SCHEMA.EXTENSION_REGISTRATION` and tell me every function it
   registered, with argument and return types, and what `crypto_version()`
   reports.

3. Store a password. Hash one with `crypt` over a `gen_salt('pbkdf2-sha256', ...)`
   salt, show me the stored string, and read its parts back to me. Verify the
   right password and a wrong one in a single statement. Then hash the same
   password again at ten times the iteration count and show me that the older
   hash still verifies with no migration step. Time each verification over
   enough repetitions that the number means something, and tell me what the two
   timings cost an attacker.

4. Sign a payload. Compute an `hmac` over a JSON body with a shared secret, then
   show me what changes when you alter one byte of the body, and what changes
   when you alter the secret. Verify a signature in SQL the way a webhook
   receiver would, then tell me whether the comparison you wrote is
   constant-time and whether that matters here.

5. Encrypt a column. Create a table with a `VARBINARY` column, because
   ciphertext is binary, insert the same value twice through `encrypt`, and show
   me that the two stored ciphertexts differ. Do the same in a second table
   using the built-in `AES_ENCRYPT` and show me its answer, along with
   `@@block_encryption_mode`. Decrypt your own rows back. Then tell me which
   attack that difference stops. Also try `encrypt` in a generated column and
   show me what the server says.

6. Generate identifiers. Insert 1000 rows of `gen_random_uuid()` and 1000 rows
   of `HEX(gen_random_bytes(16))` into tables, then count the distinct values in
   each table. Tell me the largest `n` that `gen_random_bytes(n)` will accept
   and what it does past that.

7. Try to break it. One change at a time: decrypt with the wrong key, decrypt a
   ciphertext you flipped a byte in, encrypt with a key shorter than the cipher
   needs, name a cipher that does not exist, and ask `gen_salt` for pgcrypto's
   `bf` scheme. For each one, tell me whether it raised an error, returned NULL,
   or returned something that looks valid and is not, and quote exactly what
   came back.

Then give me a table of what you ran and what came back, tell me anything that
did not behave the way this asked, and drop every table and database you
created, leaving my server back where it started.

Scenario 1: Passwords that survive a database hack

A stolen user table that contains usernames and passwords can keep paying out to the attacker long after the breach. The attacker is off your network and working at their own pace, so the only thing standing between them and your users' passwords or other sensitive data is how those columns were written. Passwords should get hashed rather than encrypted, because anyone holding the key can undo encryption. You store a hash of each password instead. A hash is a one-way fingerprint: easy to compute from the password, but not reversible back into it. To check a login, you hash what the user typed and compare fingerprints.

That alone is not enough though, because the same password always produces the same fingerprint. An attacker who fingerprints a list of common passwords once can then match it against every account in every leak they have. A salt breaks that weakness. A salt is a random value generated per password, stored next to the hash, and mixed into the password before hashing. Identical passwords now produce different fingerprints, so each account has to be attacked on its own.

vsql-crypto hashes with PBKDF2 (Password-Based Key Derivation Function 2), which repeats a keyed hash of the password a number of times. The repetition is there to make the hashing slow on purpose, and what it spends is CPU time. At 100,000 repetitions one check takes roughly 7 milliseconds on a developer laptop, and at a million it takes about 70 milliseconds. Your user pays that once at login and never notices it. An attacker spends it on every password they try, and because each account carries its own salt, none of that work carries over to the next account.

Two functions do the work. gen_salt produces the random salt and records the iteration count with it, and crypt runs the password through PBKDF2. Verification recomputes the hash using the stored value as the salt, so the check is one comparison:

SET @hash = crypt('correct horse battery', gen_salt('pbkdf2-sha256', 100000));
SELECT crypt('correct horse battery', @hash) = @hash AS right_password,
       crypt('tr0ub4dor', @hash) = @hash AS wrong_password;
+----------------+----------------+
| right_password | wrong_password |
+----------------+----------------+
|              1 |              0 |
+----------------+----------------+

The stored string carries the algorithm and the iteration count in front of the salt and the hash:

SELECT crypt('correct horse battery', gen_salt('pbkdf2-sha256', 100000)) AS stored_hash;
+------------------------------------------------------------------------------------------+
| stored_hash                                                                              |
+------------------------------------------------------------------------------------------+
| $pbkdf2-sha256$100000$wxu24gc1Lg24l6WO0sQFYQ$j2DMnv+jgqZxTp+GMbhp+m/PeuO2T0iUNQKj9p99lzw |
+------------------------------------------------------------------------------------------+

Because every hash names its own cost, you can raise the iteration count for new passwords whenever hardware gets cheaper and leave the old rows alone. They keep verifying at the count they were written with, and each user moves up the next time they change their password.

Scenario 2: Signatures your webhooks can check

A webhook is a URL you publish so another system can call you when something happens, such as a payment clearing or a build finishing. It sits on the public internet, and anyone who learns the address can post to it too. So the receiver needs a way to separate a genuine callback from a forged one, and the sender needs a way to prove authorship without shipping a password on every request.

An HMAC does both. The sender hashes the payload together with a secret that only the two parties hold, and sends the resulting signature alongside the message. The receiver recomputes the signature from the payload it just got and its own copy of the secret. A match proves two things at once: the message came from someone holding the secret, and nobody altered it in transit. Change one byte of the payload and the signature no longer agrees.

With hmac in SQL, the database can sign an outgoing payload, or verify an incoming one, in the same statement that reads or writes it:

SELECT HEX(hmac('order-4711', 'shared-secret', 'sha256'));
+------------------------------------------------------------------+
| HEX(hmac('order-4711', 'shared-secret', 'sha256'))               |
+------------------------------------------------------------------+
| C5CAE86AA4B0CE3C183C346C5FED957FB3B2560136EA21CD1C5061A0D33A5AAF |
+------------------------------------------------------------------+

HEX() is there because an HMAC is raw bytes, and hex is how you put those in a header or compare them to what a sender sent. For plain hashing with no secret involved, digest accepts md5, sha1, sha224, sha256, sha384, and sha512.

Scenario 3: Encryption that hides which rows match

Some columns hold data you have to be able to read back: a card number, a bank account, a date of birth. A one-way fingerprint is no help there, so those columns get encrypted rather than hashed. encrypt turns the readable value, the plaintext, into scrambled bytes called the ciphertext, and the key turns it back.

Encryption happens one value at a time, so encrypting a column means each row's value is written as ciphertext and decrypted on the way out. That protects the data where your access controls end: in a backup file, on a disk that leaves the building, or in front of anyone who can read the data files but does not have your key.

The part that catches people is repetition. If the same plaintext always encrypts to the same ciphertext, then an attacker who never breaks the key still learns which rows hold the same value, and often the equality is the secret. For example, grouping an encrypted salary column tells you who is paid the same; grouping an encrypted diagnosis column tells you who shares a condition.

encrypt and decrypt run AES in CBC (Cipher Block Chaining) mode, which mixes each block of plaintext with the encrypted output of the block before it, and starts that chain from a random value called the initialization vector, or IV. encrypt draws a fresh IV on every single call, so in practice the same input never produces the same output twice. Ciphertext is binary, so the column that holds it is VARBINARY or BLOB:

CREATE TABLE secrets (c VARBINARY(255));
INSERT INTO secrets VALUES (encrypt('same message', 'my-secret-key-16', 'aes')),
                           (encrypt('same message', 'my-secret-key-16', 'aes'));
SELECT COUNT(*) AS rows_stored, COUNT(DISTINCT c) AS distinct_ciphertexts FROM secrets;
+-------------+----------------------+
| rows_stored | distinct_ciphertexts |
+-------------+----------------------+
|           2 |                    2 |
+-------------+----------------------+

The same value went in twice under the same key, and two different ciphertexts came out. Run those same two inserts through the built-in AES_ENCRYPT instead and the distinct count comes back as 1, because block_encryption_mode defaults to aes-128-ecb. ECB (Electronic Codebook) encrypts each block on its own with no IV, so identical input gives identical output every time. A GROUP BY on that column then sorts your users into buckets of equal values without anyone needing the key.

A random IV would normally be one more thing to store and hand back at decryption time. Here it is not: encrypt writes the IV into the front of the ciphertext and decrypt reads it from there, so decryption needs only the stored bytes, the key, and the cipher name:

SET @c = encrypt('Hello, World!', 'my-secret-key-16', 'aes');
SELECT CAST(decrypt(@c, 'my-secret-key-16', 'aes') AS CHAR) AS plaintext;
+---------------+
| plaintext     |
+---------------+
| Hello, World! |
+---------------+

Encryption hides the value, but it does not prove that nobody changed it. CBC carries no integrity check of its own, so if tampering is part of what you are defending against, store an hmac of the row alongside the ciphertext and check it before you trust what comes back.

aes and aes-128 name the same cipher, and aes-192 and aes-256 take the longer keys. An unrecognized cipher name returns NULL, while a key shorter than the cipher requires stops the statement, because silently padding a weak key would be worse than failing:

SELECT encrypt('data', 'short', 'aes-256');
ERROR 3200 (HY000): VDF error in function 'encrypt': key too short for aes-256: need 32 bytes, got 5

A fresh IV per call is also why the server won't let encrypt compute a generated column, a column whose value MySQL derives from other columns. A generated column has to give the same answer every time it is recomputed, encrypt deliberately does not, and ERROR 3763 says so. Encrypt the value in the INSERT or UPDATE that writes the row instead.

Scenario 4: Randomness worth trusting

A session token, a password reset link, and an API key are all secrets that hold only as long as nobody can guess them. That is a higher bar than being unique, and it is where MySQL's own UUID() falls down. A UUID (Universally Unique Identifier) comes in several versions, and UUID() returns a version 1, which is built from the current time and the server's network address rather than from bits an attacker cannot predict. Ask for three in a row and you can see it incrementing:

SELECT UUID() FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3) t;
+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| b95ff010-a7d2-11f1-8e99-2a389a5e0709 |
| b95ff011-a7d2-11f1-8e99-2a389a5e0709 |
| b95ff012-a7d2-11f1-8e99-2a389a5e0709 |
+--------------------------------------+

Everything after the first field is shared, and the first field counts up. Hand one of those to a user as a reset link and you have handed them their neighbors' links as well.

gen_random_uuid() returns a version 4 UUID, which is random bits apart from the six that mark the version and the variant, and gen_random_bytes(n) returns up to 1024 raw random bytes for cases where you want your own format. Both draw from OpenSSL's cryptographically secure generator, which is built to be unpredictable even to someone who has already seen its earlier output:

SELECT gen_random_uuid();
+--------------------------------------+
| gen_random_uuid()                    |
+--------------------------------------+
| 758e918c-4096-4b55-b192-150e3fa9d8ea |
+--------------------------------------+

The 4 opening the third group marks the version, and the first digit of the fourth group is a variant marker that is always 8, 9, a, or b. Every other digit is drawn fresh, with no clock and no network address for an attacker to work forward from.

gen_random_uuid() covers what this section is about: one value nobody can guess, generated on the spot. If UUIDs are a column you store, index, and read back, reach for the vsql-uuid extension, which is bundled with the server as well. It adds a real uuid type that holds 16 bytes where a VARCHAR(36) column needs 36 characters, and its UUID_V7() is the generator worth knowing about. A version 7 value opens with a millisecond timestamp, so values written a millisecond or more apart sort in that same order, while version 4 values sort at random. The tail stays random, which is what makes version 7 the usual choice for a primary key you also want to be unguessable.

Try it out

Please try vsql-crypto on your own data, and tell us how we can improve it. The README is the full reference for every function, argument, and error case. Find us on Discord or leave an issue on vsql-crypto.

To get started with VillageSQL Server, go to villagesql.com.

Putting “one more component” in front of your servers to make them highly available just shifts the problem up by one layer. From making the database HA, to making ProxySQL HA, to needing to make HAProxy HA and so on…

At some point, you may land on the common HA strategy, keepalived, but BGP ECMP is a powerful alternative worth considering.

What is BGP, what is ECMP?

As a brief summary, Border Gateway Protocol (BGP) is a routing protocol which operates over TCP. Equal-Cost Multi-Path (ECMP) defines that we want the traffic to be loadbalanced equally across the given routes. Routers acting as BGP speakers are configured to accept routes for defined IP ranges and Autonomous System (AS) numbers, storing information about the networks that the router can reach in a Routing Information Base (RIB) table. To benefit from Anycast, we will assign both/all ProxySQL nodes the same virtual IP address. BGP is then used to let the router know multiple routes to reach that IP. Configuring ECMP will cause the router to balance the traffic over these routes.

User defined health checks are executed by a BGP-speaking daemon, such as ExaBGP, running on the host to ensure that the router has the correct information about the status of a route. As soon as a health check fails, ExaBGP will trigger a BGP update to withdraw the route. The router will remove the entry from its RIB table and stop forwarding packets to that host. The traffic will be redistributed to the remaining healthy ProxySQL nodes.

For a more in depth guide about BGP please refer to Cisco press.

How ECMP based on BGP works

For our setup we can’t have the applications connect to the normal IPs of the ProxySQL nodes, as these are assigned to one node and fixed. Instead we need a single separate anycast IP (let’s take 10.5.200.1/32) that we can assign to both ProxySQL nodes. This IP will be assigned to the loopback interface and has to be of a separate network, not overlapping with the IP-Range the normal ProxySQL node IPs are from.

When a packet with the destination set to the ProxySQL anycast IP (10.5.200.1) arrives at the router, the router checks its internal routing table to determine the next hop for the packet. The router will see the two ProxySQL nodes in the cluster as potential next hop (as they both have the same anycast IP) and will pick one of the ProxySQL nodes, based on ECMP, to forward the packet to. In ECMP the next hop is dynamically decided based on a 5-tuple hash from the packet header fields:

{ source IP address | destination IP address | protocol | source port | destination port }

Because the IP address and ports are in the hash, this ensures that the packets belonging to the same TCP stream are kept on the same path, to prevent packets of the same TCP connection ending up on multiple ProxySQL nodes.

You can visualise the setup like this:

By doing so, we have achieved high availability by leveraging BGP ECMP to loadbalance traffic in an active/active configuration across the ProxySQL nodes. Additionally, the application config can be simplified, as only the single anycast IP (or DNS record for that IP) needs to be used for the ProxySQL cluster, and the logic of routing will be handled by the router. Failures of a ProxySQL host are automatically handled by ExaBGP; a BGP update is sent to the router, and the route to the failed ProxySQL is withdrawn. The router redirects traffic to the remaining healthy ProxySQL, with no manual interventions required. Extra infrastructure components (such as internal loadbalancers) can be avoided, eliminating additional network hops and improving network latency.

Alternative strategies for using BGP with databases

Of course, a ProxySQL cluster is not the only way to leverage BGP ECMP in order to achieve high availability for your databases. Some other strategies could be to use it for read-replica routing, local traffic routing, or for routing towards loadbalancers (e.g. haproxy).

Routing towards the database loadbalancer

Using BGP ECMP does not mean that you have to forego a database loadbalancer. You can configure your database servers to sit behind a database loadbalancer, such as HAproxy or ProxySQL, and implement BGP ECMP in order to route traffic towards the loadbalancers and operate them highly available as true active/active pair. If one of the loadbalancer instances dies, then BGP automatically takes care of routing the traffic to the remaining healthy peers for you.

Read replica routing

You can use BGP ECMP to distribute MySQL-Connections over multiple read replicas without using any Loadbalancer / ProxySQL at all. This saves you the additional latency and network hops of using a loadbalancer/ProxySQL. If you need to ensure that you do not read from a replica which is lagging behind or has stopped replicating, you can implement this logic in the BGP health checks.

BGP Local Preference

If you have a multi-datacenter setup, you can choose to use local preference to keep traffic localised within the same datacenter. For example if you have an application server and a proxysql host in one datacenter (datacenter A), and an application server and proxysql host in a second datacenter (datacenter B), you can tell the router to send traffic from the application to the proxysql within the same datacenter. The advantage of this is that it keeps network latency low, and avoids cross-site transit. Configuring this in BGP means that the application does not need to be aware of which datacenter it is running in. The BGP router handles localised routing for you. If the local route would disappear, then the BGP router would automatically divert traffic from the application in datacenter A to the proxysql in the datacenter B.

Advantages of BGP

• One advantage of BGP over keepalived is that your anycast-nodes don’t need to be in the same subnet (especially useful for multi-datacenter setups). Keepalived instead requires the nodes to be part of the same Layer 2 Network.
• BGP ECMP supports active/active, unlike keepalived which only supports active/passive architectures.
• In BGP, the router has the overview of which node is healthy or unhealthy. In keepalived this knowledge resides in the keepalived process running on the node itself. As long as the nodes running keepalived can see each other, keepalived thinks everything is fine, but the nodes might have lost connection to the router. Whereas with BGP, health checks ensure that BGP is aware of the state of the route. In case there would be a network problem that would make the node unreachable, the route would disappear from the router.
• You can horizontally scale the nodes with BGP.
• BGP Local preference allows you to automatically route traffic within a datacenter, without the application needing to configure logic like “use ProxySQL-A when running in datacenter-A, otherwise ProxySQL-B.”
• You can set BGP to eliminate additional hops of infrastructure components, for example connecting to a pool of read replicas, without needing to connect over a loadbalancer.

Caveats with BGP

BGP ECMP is not connection-state aware, so if instances disappear/die or new instances join and the RIB table is rebuilt, the hashing algorithm will most likely forward packets for existing connections to a different instance than before. As that instance will not be aware of this TCP-Connection, it will send an RST-packet and the application will have to re-open its database connection.

In the next post, we will explain the technical details of setting up BGP ECMP for our ProxySQL cluster using OPNsense as Router.

This post is part of the Percona Community Writers Program.

On a default Ubuntu or Debian server, unattended-upgrades installs security updates on a daily systemd timer, and when one touches MySQL, dpkg stops mysqld, upgrades the data directory to match the new binary, and starts it again while the machine stays up. Nothing rebooted. The database was replaced underneath a host that never stopped running.

That is deliberate, and the part nobody signed up for is that “a service” includes the database. We put a client in front of MySQL on an Amazon Elastic Compute Cloud (EC2) instance and let the timer do what it does every morning.

Is this a MySQL problem?

No. The restart was not written by anyone at MySQL. It comes from a block in the Debian package’s install script, generated by the standard packaging tooling and marked as such in the file, and every Debian package carrying a daemon ships the same one. It starts the service on first install and restarts it on every upgrade after, because the packaging cannot tell a database from a print spooler.

The second mechanism is not MySQL-specific either. Ubuntu ships needrestart, wired into apt, which restarts any service whose libraries were replaced on disk. In one run an OpenSSL update arrived, MySQL was held back and never upgraded, and needrestart restarted it anyway, along with sshd and the journal. Postgres, Redis and nginx would have been on that list too.

What does unattended-upgrades actually do?

It is a Python program plus two systemd units that install updates without a human: refresh the package lists, take what is upgradable from origins it is allowed to touch, install with dpkg, and reboot afterwards if a package asked for one and the operator turned that on. Its whole policy is two small config files, which on a stock Ubuntu 24.04 image say run daily and treat the security pocket as fair game. The blacklist is empty, so there is no exception for databases. Nobody configured that; it is what the image ships.

Does the RHEL family do this too?

Partly, and the difference is the half that matters. We ran the same checks on a stock Amazon Linux 2023 image:

Every row was read off the image rather than from documentation.

The last row matters: the RPM restarts the daemon on upgrade exactly as the Debian package does, and a dnf upgrade of MariaDB stopped and started the database. What Ubuntu adds is not the restart, it is the schedule.

How long does it take the database away?

A systemctl restart mysql costs about four seconds. One automatic security upgrade costs 22.98 seconds, because dpkg does not restart MySQL, it replaces it: mysqld starts and stops three times in those 23 seconds, and two of the three are listening on nothing while the data directory is upgraded. The machine does sometimes reboot on its own, when a kernel update sets /var/run/reboot-required and Automatic-Reboot is on, which Ubuntu ships off: we turned it on and the client lost the database for 19.37 seconds, less than the package upgrade costs.

And 23 seconds is the floor, because our lab server runs the 128 MB default buffer pool with almost nothing in it. Stopping a real database means flushing every dirty page first. On a second instance with an 8 GB buffer pool, a 5.5 GB table and roughly 2.8 GB of pages dirty, an ordinary systemctl stop mysql took 1 minute 59.8 seconds, and nothing bounds it: the MySQL unit Ubuntu ships gives the stop no timeout at all. Scale that pool to the terabyte a real server has and this stops being a blip.

Why does it feel random?

Because it is scheduled to be. The timer runs at 06:00 with RandomizedDelaySec=60m, spreading the start across the following hour independently on every host, which is what the setting is for: it keeps a fleet off the mirrors at the same second. So the restart lands at a different minute every morning and on every machine, and the repetition that would make it obvious never forms. Persistent=true fires a missed run as soon as the machine comes back, which is how one lands ten minutes after a maintenance window rather than during it.

How do I tell which one happened?

First settle whether the machine rebooted, because the two have different fixes and the same application error. Compare uptime -s, when the kernel booted, against systemctl show -p ActiveEnterTimestamp --value mysql, when this mysqld started.

Then three logs and one line. /var/log/apt/history.log names what apt installed, the unattended-upgrades log says what it decided, and journalctl -u mysql shows systemd stopping and starting the service with a gap of tens of seconds where dpkg works. The MySQL error log settles it: MY-013381, the “Server upgrade from” line, appears only when MySQL finds a data directory from an older version and upgrades it, so a crash, an out-of-memory kill, an operator restart and a host reboot cannot produce it.

How do I disable or remove it?

My opinion: on a production database server, automatic installation of database packages should be off. Replacing a running mysqld is a maintenance action, and maintenance actions belong to whoever answers for the outage. On a web tier behind a load balancer, leave it on.

To disable it, set APT::Periodic::Unattended-Upgrade "0" in /etc/apt/apt.conf.d/20auto-upgrades, which is what dpkg-reconfigure -plow unattended-upgrades writes. The daily unit then runs and installs nothing. To stop the timer entirely, systemctl mask it rather than disable it: a disabled unit stays startable and a later upgrade can restore the symlink.

To remove it, apt-get purge unattended-upgrades, which takes the binary and both config files but keeps the logs. It does not take the timers: apt-daily.timer and apt-daily-upgrade.timer ship with apt itself and stay enabled, firing into a binary that is gone. We ran all of this with the upgrade pending and a client probing; MySQL never restarted.

Two caveats. needrestart runs on every apt transaction including yours, so on a purged host a hand-typed apt-get upgrade of OpenSSL still restarted MySQL and cost 4.10 seconds; excluding the database there takes a one-line override in /etc/needrestart/conf.d/. And this moves the outage to a moment you chose rather than removing the need for it.

Conclusion

A database that disappears for twenty seconds at night, on a machine whose uptime says weeks, is unattended-upgrades doing what it was configured to do on the day the machine was built, at a minute systemd picked at random. It is not a MySQL bug, and not really an Ubuntu bug either. What Ubuntu adds is that nobody had to ask, and the machine picks the minute. On a web tier that is a good trade. On a database it should be a decision a person makes, in a window they chose.

O post Something restarts my MySQL every morning. It isn’t MySQL. apareceu primeiro em Another Boring Tech Blog.

The post Database branching explained: How it works and when to use it  appeared first on Devart Blog.

An engineer leaves your company. You disable their single sign-on account, and their access to the wiki, the cloud console, and the CI system goes with it. Typically, access to database resources doesn't follow such a simple plan. Database user and role maintenance too often happens within the database, oblivious to single sign-on. User accounts carry a password which has to be managed and rotated (and the password is probably already sprawled out across shell history and a shared password manager).

An easier way to manage this is to bring the identity-provider model to the database. This is what the VillageSQL Server vsql-oauth2 extension does. It adds an authentication method that accepts an OAuth2/OpenID Connect token (a JWT) from your identity provider in place of a password. Your identity provider decides who can log in, MySQL privileges decide what they can do inside, and disabling someone stops them getting another token. The token already in hand works until it expires, so your provider's token lifetime dictates the access window.

VillageSQL is the innovation platform for MySQL that adds an extension framework (similar to PostgreSQL's extension framework) to enable permissionless innovation. Instead of waiting for a feature to be implemented in a future version of MySQL in a few years, new functionality can be dynamically added to the version of MySQL you run today. VillageSQL Server supports MySQL 8.4, 9.7, and Percona Server 8.4. The vsql-oauth2 extension is an example of what is meant by permissionless innovation.

The rest of this post shows how the vsql-oauth2 extension works. The examples use Microsoft Entra ID, because Entra puts readable role names directly into the token. We start with the simple case: install the extension, point it at Entra's signing keys, and log in to the database with a token instead of a password. We then add role mapping, so a person the database has never seen before can log in for the first time and come away with an account and the roles their identity provider assigned them.

Set up a working demo of passwordless MySQL logins on my machine, using the
vsql-oauth2 extension for VillageSQL. Act as my own identity provider so no real
IdP is needed. Work only against a local throwaway server — if the only
VillageSQL or MySQL server you find looks like something I depend on, stop and
ask me before touching it.

Do all of this yourself, and show me the real output of each step:

1. Find a VillageSQL server, or install one. The install script needs a method:
   `curl -fsSL https://install.villagesql.com | VSQL_CODEBASE=mysql-8.4 INSTALL_METHOD=prebuilt bash`.
   Start it with `--vsql_allow_preview_extensions=ON`; on a server already
   running, `SET PERSIST vsql_allow_preview_extensions=ON` takes effect at once.
   Confirm with `SELECT VERSION()` before continuing.

2. Install the extension: `INSTALL EXTENSION vsql_oauth2;`. It ships with the
   server, so there is nothing to download. Read
   `INFORMATION_SCHEMA.EXTENSION_REGISTRATION` and
   `SHOW GLOBAL VARIABLES LIKE 'vsql_oauth2.%'` and tell me what settings exist.

3. Stand in for the identity provider. Generate an RSA keypair with openssl, and
   write a small script that mints signed RS256 JWTs from a claims payload, each
   header carrying a `kid`. Use openssl only — do not install a JWT library.
   Then publish the public key the way a real provider does: serve a JWKS
   document over HTTP on localhost, holding the key as a JWK with `kty`, `n`,
   `e` and that same `kid`. Let the operating system choose the port rather than
   picking one yourself, and tell me the URL.

4. Do the basic case. Set `issuer`, `audience` and `username_claim`, and point
   `jwks_url` at the JWKS URL from step 3. Create an account bound to
   `vsql_oauth2`, create the account it maps onto, and grant the proxy. Then log
   in with a token in place of the password and show me
   `SELECT CURRENT_USER(), @@external_user;`. Show me your JWKS server's own log
   as evidence that the database really fetched the keys.

5. Do the joiner case. Turn on `roles_claim`, `roles_filter`,
   `roles_transform_pattern`, `roles_transform_replacement`, `auto_create` and
   `auto_grant`. Create two roles named in the rewritten form the transform
   produces, then log in as a user who has never touched this database with a
   token carrying a roles claim. Show me that the account and the role grant both
   appeared, and that the role is active for the session.

6. Try to break it. Take a token you have just shown works and change exactly
   one thing at a time: expire it, alter the issuer, alter the audience, name a
   `kid` your JWKS document does not carry, strip the signature with
   `alg: none`, and re-sign it with HMAC using the public key as the shared
   secret. Show me all six refused. Every refusal prints the same message, so
   for each one name the single change you made, and log in with the unchanged
   token in the same run to prove the refusal came from that change.

Then give me a table of what you ran and what came back, tell me anything that
did not behave the way this asked, and drop every user, role and setting you
created, stop the JWKS server, and leave my server back where it started.

Log in without a database password

Below is the server-side setup for the basic case, using Microsoft Entra ID:

INSTALL EXTENSION vsql_oauth2;

SET GLOBAL vsql_oauth2.issuer   = 'https://login.microsoftonline.com/<tenant-guid>/v2.0';
SET GLOBAL vsql_oauth2.jwks_url = 'https://login.microsoftonline.com/<tenant-guid>/discovery/v2.0/keys';
SET GLOBAL vsql_oauth2.audience = '<database-app-client-id>';
SET GLOBAL vsql_oauth2.username_claim = 'preferred_username';

CREATE USER oauth_user IDENTIFIED WITH vsql_oauth2;
CREATE USER 'dana@myco.example';
GRANT SELECT ON *.* TO 'dana@myco.example';
GRANT PROXY ON 'dana@myco.example' TO oauth_user;

jwks_url is Entra's key endpoint. The extension fetches the signing keys from it and refreshes them hourly by default, so Entra can rotate its keys without anyone touching the database. The public_key setting is the alternative: it pins one key you paste in yourself, which suits a self-signed test and stops working at the provider's next rotation. Set both and jwks_url wins.

Point audience at the app registration that stands for the database, and send Entra's access token for that app rather than the id_token. Only the access token carries App Roles under the readable names the next section filters on. The README's provider settings cover the app registrations Entra needs and the values for other providers.

From there, the standard mysql client logs in with a token where the password would normally go. Passing it through MYSQL_PWD keeps it out of the process list:

$ MYSQL_PWD='<the JWT>' mysql --enable-cleartext-plugin --user=oauth_user \
    -e "SELECT CURRENT_USER(), @@external_user;"
+---------------------+-------------------+
| CURRENT_USER()      | @@external_user   |
+---------------------+-------------------+
| dana@myco.example@% | dana@myco.example |
+---------------------+-------------------+

The credential is a short-lived token from your identity provider, so there is no password to store or rotate. Where the token comes from depends on who is connecting, e.g., a person at a shell, a CI job, or an application. The extension takes the same token in all cases. Obtaining it is ordinary OAuth for your provider.

The vsql-oauth2 README has the commands for all three. It also covers the vsql_oauth_client plugin, which fetches the token itself so it never touches the command line. However you send it, the token travels in cleartext at the protocol level, so the connection has to run over TLS.

You connect as oauth_user, which is bound to the extension and holds no privileges of its own. The username_claim setting says which claim names the account to run as, and GRANT PROXY is what allows the switch. Entra's sub is an opaque identifier, so this example reads preferred_username and gets Dana's sign-in name. The session then has dana@myco.example's privileges. @@external_user reports the identity that arrived in the token, so the audit trail keeps it even though the session runs as another account.

The token has to be signed with one of your provider's published keys for any of this to happen, and the extension accepts RSA and ECDSA signatures only. An unsigned token is refused before any signature check runs, and so is one that switches to a symmetric algorithm hoping the server will reuse your public key as a shared secret.

Easy database access management

The basic case maps a token to an account you created ahead of time. The extension can go further and take its cues from the App Roles Entra assigned the person signing in. You tell it which claim to read and how to rewrite the names, then create the roles those App Roles map onto:

SET GLOBAL vsql_oauth2.roles_claim = 'roles';
SET GLOBAL vsql_oauth2.roles_filter = 'mysql-grp-.*';
SET GLOBAL vsql_oauth2.roles_transform_pattern = '-';
SET GLOBAL vsql_oauth2.roles_transform_replacement = '_';
SET GLOBAL vsql_oauth2.auto_create = ON;
SET GLOBAL vsql_oauth2.auto_grant = ON;

CREATE ROLE mysql_grp_dba;

The two transform settings rewrite each matched App Role before it becomes a role name, turning mysql-grp-dba into mysql_grp_dba. Now someone who has never touched this database logs in with a token that says preferred_username: alice@myco.example, roles: [mysql-grp-dba]:

$ MYSQL_PWD='<her JWT>' mysql --enable-cleartext-plugin --user='alice@myco.example' \
    -e "SELECT CURRENT_USER(); SELECT CURRENT_ROLE();"
+----------------------+
| CURRENT_USER()       |
+----------------------+
| alice@myco.example@% |
+----------------------+
+---------------------+
| CURRENT_ROLE()      |
+---------------------+
| `mysql_grp_dba`@`%` |
+---------------------+

One login created the account, granted the role her App Role entitles her to, and activated it for the session. An auto-created account runs as itself, so this path needs no proxy grant. The DBA never saw a ticket and never ran a CREATE USER. The DBA's job moves up a level: grant each role its privileges once, and let the identity provider say who holds the App Role. When an App Role later disappears from someone's token, that role stops activating at their logins, but the granted membership stays until a human revokes it.

Alice had no account, so auto_create did all of it. auto_grant covers the other case, someone who already has an account, and it grants the roles their token claims each time they log in. Both default to off and work independently, so you can turn on either one without the other. If you leave them off, tokens only ever activate roles you granted by hand, and unknown users stay unknown.

Try it out

Please try out vsql-oauth2 against your identity provider, tell us how it goes, especially which claim layouts your tokens carry. If you are not on Entra, the same settings apply — your provider's discovery document (/.well-known/openid-configuration) gives the issuer and jwks_url values. We would love to hear from you on Discord or leave an issue on vsql-oauth2.

To get started with VillageSQL Server, go to villagesql.com.

Oracle offers the same category of functionality, but its server-side plugin is part of the paid MySQL Enterprise Edition. Percona’s implementation is open source and adds three capabilities the Enterprise plugin does not provide: automatic signing-key synchronization from a JWKS endpoint, IdP group-to-role mapping, and proxy-user support. This article explains how the plugin works and why those differences matter in practice.

What OpenID Connect Brings to MySQL Authentication

OpenID Connect is an identity layer built on top of the OAuth 2.0 authorization framework [5]. Whereas OAuth 2.0 governs delegated access to resources, OIDC adds a standardized way for a client to establish who a user is. After a user signs in to an Identity Provider, the IdP issues a signed JSON Web Token (JWT), called an ID token, that carries the user’s identity and attributes in a verifiable, tamper-evident form.

Using that model for MySQL authentication brings several practical advantages over password-based accounts:

• Alignment with single sign-on. Users authenticate once with their IdP and can reuse that session context across OIDC-aware applications, including databases. User lifecycle and password management remain centralized.
• No long-lived secrets on the wire. ID tokens are short-lived and cryptographically signed, so there is no static password to steal, rotate, or accidentally commit to a configuration file.
• Support for hybrid deployments. Organizations that run MySQL on-premises while hosting applications in the cloud can still authenticate through the same identity plane on both sides.
• Broad interoperability. Because OpenID Connect is a widely adopted standard, the plugin can work with any compliant provider, including Keycloak, Okta, Microsoft Entra ID, and Google Identity.

None of that is unique to Percona; Oracle makes a similar value proposition for the Enterprise plugin. The real difference lies in how much operational burden the plugin removes from the administrator, which becomes clear in the next sections.

How OpenID Connect Authentication Works

Once the plugin and its configuration are in place, the authentication path is the same regardless of which IdP issued the token:

1. The user authenticates to the IdP and receives a signed ID token.
2. The token is written to a local file that only the client operating system account can read.
3. The MySQL client uses an option that causes the client-side OIDC plugin to load and read the token from the file. The token is sent to the server as part of the authentication handshake.
4. The server validates the secure channel and decodes the token. It then verifies the token signature using the selected IdP’s public key, checks the expiration time, and validates the configured claims.
5. The server resolves the final identity as either a personal account or a group-based proxy target. The plugin may also return roles mapped from the user’s group membership.

Configuring Trusted Providers and Letting the Plugin Manage the Keys

Identity Providers rotate their signing keys periodically as a basic security measure. If a key is ever compromised, limiting its lifetime reduces the potential impact, and regular rotation also lowers the long-term value of any one key as a target. In practice, rotation is gradual: a new key is published and accepted before it starts signing tokens, and an old key remains valid for a period after it stops signing so that tokens already in flight can still be verified.

Public keys are exposed through the standard JWKS (JSON Web Key Set) endpoint, which applications can use to verify tokens issued by the IdP [6].

The Percona OpenID Connect authentication plugin can download public keys from a configured JWKS endpoint when the plugin is loaded, typically during installation and server startup, and store them in a cache. It also provides a User Defined Function (UDF) that can refresh the cache on demand or periodically through the Event Scheduler.

By contrast, Oracle’s counterpart plugin requires signing keys to be configured statically through the authentication_openid_connect_configuration server variable, supplied either as an inline JSON string or as a path to a JSON file. There is no retrieval or refresh from the JWKS endpoint, so keeping keys current after each rotation remains a manual task for the administrator. In the window just after a rotation, tokens signed with the previous key are still valid but cannot be verified until the configuration is updated. Percona’s plugin supports static key configuration as well, but that mode is better suited to testing or temporary setups than to production.

Example

Using the feature requires two simple steps. First, JWKS endpoint URL must be set in the plugin’s configuration. For example, the below configuration defines IdP named as example-keycloak (pay attention to jwks-url element):

{
  "example-keycloak": {
  "issuer-name": "https://keycloak.example.com/realms/master",
  "jwks-url": "https://keycloak.example.com/realms/master/protocol/openid-connect/certs",
  "audiences": [ "mysql-oidc" ]
  }
}

The second step is ensuring the MySQL event scheduler is running and creating an event updating the keys. For example, to enable updating the keys for example-keycloak every hour run from MySQL client:

CREATE EVENT update_oidc_keys
  ON SCHEDULE EVERY 1 HOUR
  DO SELECT update_jwks("example-keycloak");

Benefits of Using IdP Groups

This is where Percona’s plugin diverges most clearly from the Enterprise implementation.

Groups are managed by the corporate Identity Provider and group membership may be carried by ID tokens. OIDC does not define a standard claim for that, but most IdP implementations allow adding a group claim to the tokens. The Percona’s plugin allows the administrator to configure the group claim name so that it matches the token format used by the chosen IdP.

There are two practical ways to take advantage of this feature:  group-to-role mapping and proxy users.

Group-to-Role Mapping

Membership in a group can automatically translate into MySQL roles and therefore privileges across multiple MySQL servers at the same time. On a single server, the flow looks like this:

1. The administrator creates roles and grants them privileges.
2. The administrator defines the IdP group-to-MySQL role mapping in the plugin configuration file.
3. When the user connects, the plugin returns the roles that match the user’s groups, and the server automatically grants those roles to the user.
4. The user can activate any granted role and exercise the privileges assigned to it.

Please note, that group-to-role mapping still requires an account created for each user, but automates managing user privileges.

Example

To create roles and grant them some privileges one may run:

CREATE ROLE accounting;
GRANT ALL PRIVILEGES ON accounting_database.* TO accounting;
CREATE ROLE sales;
GRANT ALL PRIVILEGES ON sales_database.* TO sales;

Then, to to define the mapping add to IDP configuration:

"group-claim": "groups",
"group-role": [
  { "/accounting": "accounting" },
  { "/marketing": "marketing" }
]

Any user connecting with an ID token containing claim “groups”:[“/accounting”] will be granted with role accounting and effectively obtain access to accounting_database and so on.

Proxy Users

The proxy capability in MySQL allows an authentication plugin to request that the connecting external user be logged in as a different MySQL user. In this model, the external identity is the proxy user and the mapped MySQL account is the proxied user. The purpose is to let multiple users share accounts with the same privilege set, avoiding the need to create a separate personal database account for every individual.

This feature must be supported by the authentication plugin, whose job is to choose the proxied user according to the specifics of the authentication method. In the Percona OIDC plugin, that selection is based on the group claim in the token and works as follows:

1. The administrator creates a proxy user identified by the OIDC plugin. This can be either a single anonymous account (”@”) without a specific group name, referred to as anonymous proxying, or multiple group-related accounts, referred to as named group proxying.
2. The administrator creates proxied users for each group. These accounts should not use a login plugin, so nobody can connect to them directly. The username must match the group name.
3. The administrator grants the PROXY privilege for each proxy user on all related proxied users.
4. When a user connects, in the anonymous proxying case the plugin returns the user’s first group as the proxied username. In the named group proxying case, the plugin checks whether the user belongs to the group and returns that group as the proxied username.
5. The server verifies that the requested proxied account exists and that the proxy user has the required PROXY privilege on it. If both checks succeed, the session runs with the proxied account’s privileges.

Other Features

Supported signing algorithms include RSASSA-PKCS1-v1_5, RSASSA-PSS, and ECDSA with SHA-256, SHA-384, and SHA-512 hashing functions.

The Percona approach uses the client-side OpenID Connect plugin from upstream MySQL, which ensures compatibility with the standard Oracle client.

Both client-side and server-side OpenID Connect plugins ensure that the token is sent via a secure channel. Accepted protocols are TCP protected by TLS, Unix sockets, and shared memory.

What OpenID Connect Authentication Does Not Do

There are some limits worth knowing.

The first comes from MySQL’s authentication design: any authentication plugin is used at connection time only. In the case of OIDC, the token is validated when the user connects, and a session that stays open may outlive the ID token that opened it. There is no out-of-the-box mechanism to force re-authentication after some time (except for idle connection timeout).

A similar situation applies to group-role mapping. The roles tied to the user’s groups in the ID token are granted or revoked at connection time. As a result, if a user is added to or removed from an IdP group, they must reconnect to Percona Server for the change to be reflected in their granted roles.

The proxying mechanism uses group membership claim instead of the token’s subject, so any token signed by a configured IdP that carries the required group is accepted. Group membership is your trust boundary in those modes, so treat it that way.

The current proxying implementation assumes the proxied user’s name matches the group name. This can be a problem when a group name isn’t a valid MySQL username (for example, it’s too long or contains disallowed characters), or when multiple groups need to map to a single account. We plan to add group-to-proxied-account mapping in future releases to address this.

The client-side plugin doesn’t verify the ID token (for example, check whether it has expired) before connecting, and the server doesn’t report the reason for access being denied (for security reasons). A good practice is to obtain a fresh token before connecting.

Conclusion

Functionally, Percona’s OpenID Connect plugin covers the same core ground as the counterpart in MySQL Enterprise Edition: signed ID tokens, claim validation, subject matching, and secure-transport enforcement.

It goes further in several important areas:

• It is open source.
• Keys can stay current automatically through JWKS synchronization.
• Group-to-role mapping allows IdP group membership to drive MySQL role grants for the lifetime of the session.
• Proxy-user support allows many IdP identities to share a smaller set of MySQL accounts.

Our OIDC implementation is suitable for real-world identity operations at scale. It can automatically map identities and groups managed by an IdP to database users and roles, and synchronize cryptographic keys.

References

1. Percona Server for MySQL documentation: OpenID Connect authentication.
2. Percona Server for MySQL documentation: Get started with OpenID Connect authentication.
3. MySQL 9.7 Reference Manual: OpenID Connect Pluggable Authentication.
4. MySQL 9.7 Reference Manual: Proxy Users.
5. OpenID Foundation: How OpenID Connect Works
6. auth0 Docs: JSON Web Key Sets.

Written by Michal Jankowski. Reviewed by Dennis Kittrell and Oleksiy Lukin.
Percona® is a registered trademark of Percona LLC. MySQL® is a registered trademark of Oracle Corporation.

The post OpenID Connect Authentication for MySQL, Now Fully Open Source appeared first on Percona.

The new MyEnv can be downloaded here. How to install MyEnv is described in the MyEnv Installation Guide.

In the inconceivable case that you find a bug in the MyEnv please report it to our public repository on Codeberg.

Any feedback, statements and testimonials are welcome as well! Please send them to us.

Upgrade from 2.x to 3.0

Please check the MyEnv Installation Guide: Upgrading MyEnv.

In MyEnv v3.0.1 tpl/aliases.conf.template has changed. Thus you should replace the MyEnv aliases.conf as follows:

$ cp /etc/myenv/aliases.conf /etc/myenv/aliases.conf.$(date '+%Y-%m-%d')
$ cp myenv/tpl/aliases.conf.template /etc/myenv/aliases.conf

Changes in MyEnv 3.0.1

MyEnv

• Bug in setMyEnv.php fixed, socket was not set correctly.
• Some more aliases added for log tracking.
• Prompt port was always delayed by one switch, fixed.
• Missing port in PS1 prompt fixed again (issues/12).
• up/down for PostgreSQL should work correctly now.
• PostgreSQL should be evaluated correctly now in up.
• up should show PostgreSQL status correctly now (issues/4).
• Environment variables refactored and PostgreSQL specific variables added (issues/3).
• Discrepancies between my.cnf and myenv.conf leads now to abort if relevant configuration variables are affected (issues/5).
• Database seems OK after start which is wrong, timing issue (issues/2).
• hideschema works better now (issues/13).

MyEnv Installer

• Wrong default for socket fixed in installer.
• Nasty error message when installing PostgreSQL suppressed.
• basedir filter implemented.
• Instance type mariadb was added to MyEnv installer (issues/10).
• MySQL 26.7 is recognized correctly now (issues/1).

MyEnv Utilities

• insert_test.sh made more PostgreSQL friendly.
• test table commands made more comfortable for PostgreSQL.

PostgreSQL

• See MyEnv, MyEnv Installer and MyEnv Utilities.

General

• Cosmetic fixes.
• Some bugs and feature requests moved from TODO to Codeberg.
• Error message added to make more clear what is wrong.
• rc made unique.
• Error messages improved to see base problem better.

Documentation

• “tar ball” vs “tarball” typo fix.
• Documentation updated.
• Upgrade recommendations added.
• CHANGELOG updated.
• README unified and updated.

Packaging

• Some minor build fixes.
• Ubuntu 26.04 container added to build infrastructure.

For subscriptions of commercial use of MyEnv please get in contact with us.

Posted on MySQL Ninjas | August 2026

Every DBA has been there. An alert fires. You SSH into the box, run free -h, and see MySQL consuming far more RAM than you configured. You double-check innodb_buffer_pool_size. It's set correctly. So where is the memory going?

This is the story of debugging exactly that — on a Google Cloud c4d-standard-4 instance with 14.7 GB RAM, MySQL configured with a 7168 MB buffer pool, and mysqld RSS sitting at 10.4 GB. That's a 3.2 GB gap nobody could explain.

The Setup

We run a large MySQL fleet on GCP — over 200 DR replica nodes across three datacenters. As part of a buffer pool tuning project (fitting the right pool size to the right machine class), we set innodb_buffer_pool_size = 7168 MB on our c4d-standard-4 nodes (16 GB RAM, 14.7 GB usable).

Shortly after, memory alerts started firing. When I looked at what was actually happening:

$ ps aux | grep mysqld | awk '{print $6/1024 " MB"}'
10490 MB

mysqld RSS: ~10,490 MB.
Configured buffer pool: 7,168 MB.
Unexplained overhead: ~3,300 MB.

That's not rounding error. Something was eating memory we hadn't accounted for.

Layer 1: The Expected Overhead

The first thing to understand is that MySQL's RSS is never equal to innodb_buffer_pool_size. There are well-known, legitimate sources of overhead:

On our node, max_connections = 3000. That's potentially 3 GB of THD memory root alone — a massive risk. We confirmed via sys.memory_by_thread_by_current_bytes that THD::main_mem_root had peaked at 7.7 GB under load. This alone was an OOM risk, not just a memory mystery.

Fix #1: Reduce max_connections from 3000 → 500 for DR replicas. They don't serve application traffic; they don't need 3000 slots.

After accounting for all of this, we could explain roughly 1,500–1,800 MB of the gap. We still had ~1,500 MB unaccounted for.

Layer 2: InnoDB Buffer Pool Chunk Overhead (~9% mmap Padding)

InnoDB allocates the buffer pool in chunks (innodb_buffer_pool_chunk_size, default 128 MB). When MySQL calls mmap() to allocate these chunks, the kernel doesn't give you exactly what you asked for — there's alignment overhead, guard pages, and internal bookkeeping in the virtual memory subsystem.

In practice, InnoDB's actual RSS from the buffer pool is roughly 9% higher than the configured size:

7168 MB × 1.09 ≈ 7813 MB

This is documented nowhere prominently, but you can verify it empirically by inspecting /proc/<pid>/smaps and looking for the large anonymous mappings:

$ grep -A2 "7[0-9][0-9][0-9][0-9] kB" /proc/$(pgrep mysqld)/smaps | head -40

You'll see several large anon regions just over 128 MB each — these are your buffer pool chunks, with mmap overhead baked in.

So after accounting for chunk overhead: 7813 MB + ~1500 MB other = ~9313 MB. We were at 10,490 MB. Still ~1,177 MB unexplained.

Layer 3: jemalloc — The Hidden Memory Manager

Here's where it gets interesting.

Our MySQL is started with:

LD_PRELOAD=/usr/lib64/libjemalloc.so.2

jemalloc is a high-performance memory allocator that replaces glibc's malloc. It's widely used in MySQL deployments to reduce fragmentation. But jemalloc has a behavior that surprises people: it holds onto freed memory as "dirty" pages, ready to hand back to threads quickly without going back to the OS.

Check if you're running jemalloc:

$ strings /proc/$(pgrep mysqld)/environ | grep LD_PRELOAD

On a busy primary, dirty pages are constantly churned and released. On an idle replica — like a DR slave not actively serving reads — jemalloc dirty pages accumulate and are never returned to the OS because there's no memory pressure to trigger eviction.

We confirmed this by inspecting /proc/<pid>/smaps for dirty anonymous pages not accounted for by InnoDB mappings. The amount held by jemalloc dirty pages on our idle DR replica: approximately 125 MB.

Layer 4: The gdb Arena Purge (Emergency Release)

To prove the jemalloc dirty-page theory and release the memory without restarting MySQL, we used a technique that feels like surgery: calling mallctl via gdb while MySQL was live.

First, find how many arenas exist:

$ gdb -p $(pgrep mysqld) --batch \
  -ex 'call (int)mallctl("opt.narenas", 0, 0, 0, 0)'

Then purge all arenas:

$ gdb -p $(pgrep mysqld) --batch \
  -ex 'call mallctl("arena.0.purge", 0, 0, 0, 0)' \
  -ex 'call mallctl("arena.1.purge", 0, 0, 0, 0)' \
  -ex 'call mallctl("arena.2.purge", 0, 0, 0, 0)' \
  -ex 'detach'

After purging all arenas, RSS dropped by approximately 125 MB — confirming jemalloc dirty pages were the source. But it also confirmed they were only ~125 MB, not the ~1,500 MB we initially suspected.

Warning: Running gdb against a live MySQL instance briefly pauses the process. Use this only on non-critical instances (DR replicas, test nodes). Never on a primary under active traffic.

The Root Cause: MALLOC_CONF Not Set

The deeper issue wasn't the dirty pages themselves — it was that jemalloc's background decay was completely disabled.

jemalloc 5.x has a background_thread feature: a dedicated thread that periodically purges dirty pages back to the OS on a decay schedule. Without it, dirty pages only get released when a new allocation request needs the memory.

Check your jemalloc config:

$ strings /proc/$(pgrep mysqld)/environ | grep MALLOC_CONF
# if nothing returns, MALLOC_CONF is not set

On our node: MALLOC_CONF was not set at all. That means:

• background_thread = false (default)
• dirty_decay_ms = 10000 ms — but only triggered by allocation activity
• muzzy_decay_ms = 10000 ms (same)

On an idle DR replica, there's minimal allocation activity. Decay never triggers. Dirty pages accumulate indefinitely.

The Fixes

Fix 1: Tune innodb_buffer_pool_size down slightly

Instead of 7168 MB, use 6144 MB on c4d-standard-4 nodes. This is a clean multiple of the 128 MB chunk size (48 chunks), saves ~1.1 GB RSS vs. 7168 MB accounting for mmap overhead, and leaves more headroom for thread buffers.

# Puppet/Hiera
percona::server::config::innodb_pool_size: 6144

Fix 2: Reduce max_connections

DR replicas don't serve application reads. 3,000 connections is both wasteful and an OOM risk.

max_connections = 500

Fix 3: Enable jemalloc background_thread with decay

Add to /etc/sysconfig/mysql (requires mysqld restart):

MALLOC_CONF="background_thread:true,dirty_decay_ms:5000,muzzy_decay_ms:5000"

This tells jemalloc to run a dedicated background thread to decay dirty pages within 5 seconds, even on an idle process. After this change, jemalloc RSS overhead on idle replicas dropped to near zero.

Fix 4: Add Swap

Our DR nodes had zero swap. When the OOM killer fires with no swap, it's immediate and brutal — no warning, no time to respond. Even 4 GB buys you time to react.

fallocate -l 4G /swapfile
chmod 600 /swapfile
mkswap /swapfile
swapon /swapfile
echo '/swapfile none swap sw 0 0' >> /etc/fstab

The Full Memory Accounting (Before vs. After)

Key Takeaways

1. mysqld RSS ≈ buffer_pool × 1.09 + everything else. The 9% InnoDB mmap overhead is real and underdocumented.
2. max_connections is memory, not just a connection limit. 3,000 connections × ~1 MB THD overhead = 3 GB potential. Size it for actual use.
3. jemalloc on idle replicas accumulates dirty pages. If MALLOC_CONF isn't set, background_thread is off and decay only happens during allocation pressure. Fix it with background_thread:true.
4. gdb arena purge is a valid diagnostic tool on non-critical instances — but it's a band-aid. Fix MALLOC_CONF properly.
5. No swap = no warning before OOM. Even 4 GB buys you response time before the OOM killer fires.
6. Check /proc/<pid>/smaps when RSS is mysterious. It shows every memory region with sizes and dirty-page counts.

Quick Diagnostic Checklist

# 1. Current mysqld RSS
ps -o rss= -p $(pgrep mysqld) | awk '{print $1/1024 " MB"}'

# 2. Buffer pool size
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

# 3. Are you running jemalloc?
strings /proc/$(pgrep mysqld)/environ | grep LD_PRELOAD

# 4. Is MALLOC_CONF set?
strings /proc/$(pgrep mysqld)/environ | grep MALLOC_CONF

# 5. Max connections configured vs. peak usage
mysql -e "SHOW VARIABLES LIKE 'max_connections'; SHOW STATUS LIKE 'Max_used_connections'"

# 6. Swap availability
free -h

# 7. smaps summary (requires root)
awk '/^Rss:/{r+=$2} /^Dirty:/{d+=$2} END{print "RSS:", r/1024, "MB | Dirty:", d/1024, "MB"}' \
  /proc/$(pgrep mysqld)/smaps

If this helped you track down a memory mystery, drop a comment below. MySQL memory accounting is genuinely complex — the more we share real debugging stories, the better the community gets at it.

This performance investigation aims to look into the read/write performance of Percona Server for MySQL 8.4 and how it changed between versions released in 2026:

• 8.4.8-8 released on 12 March 2026
• 8.4.10-10 released on 30 June 2026
• 8.4.11-11 released on 20 August 2026

We want to see if there are improvements in scalability and performance in OLTP read/write operations, where the improvements are most noticeable and how they were achieved. For some readers this material might help with making the decision whether upgrading to a newer version is worth the effort.

An important note is that the new features or security patches will not be taken into consideration.

Measuring Latency (Percentiles) and Resource Utilization (CPU, RAM, I/O) is not in the scope of this post.

2. Configuration and Methodology

The configuration was as follows:

The benchmarks were done across the following dimensions:

One of the points in benchmarking was to create combinations of similar Buffer to Data Ratios, but with the different Database Sizes. This gives us the following possible combinations of innodb_buffer_pool_size and Database Size:

We should be able to see how efficiently the server manages an increasingly larger number of rows while keeping the Buffer to Data Ratio the same.

Execution of the benchmarks was done as follows:

Important Database Configuration options (the actual config files with specific settings for each run can be downloaded from the interactive graphs):

In the high concurrency scenario when all CPU cores are working under maximum load the performance fluctuations might appear out of the ability of a specific CPU crystal to work at a specific sustainable maximum frequency. Intel Xeon Gold 6230 processors installed in the test servers have a base frequency of 2100 MHz and maximum turbo frequency of 3900 MHz. However, such turbo frequency can only be achieved for a short period of time on an isolated core. The load and the heat production of the physical core neighbours limit the frequency of the whole CPU. Some CPU’s were able to hold 2530 MHz on all cores for 20+ hours of intense load, others could only reach 2420 MHz. For consistency of the tests the turbo frequency was capped to 2400 MHz from the beginning on all servers. It helped to eliminate the struggle between turbo mode trying to increase the frequency beyond sustainable levels and the CPU thermal protection bringing the clock down. More stable hardware performance reduced the measurement fluctuations during the benchmark runs regardless if they were done on the same or a different physical server.

3. Results

First, let’s check the I/O bound scenario where the InnoDB Buffer to Data Size is the smallest (1:12).

The graph shows the configurations with innodb_buffer_pool_size=8G and Data Size 96G (or 20M rows per table, 400M rows in total):

[ INTERACTIVE GRAPH ][ TABLE ]

The first thing that catches the eye is the hugely superior performance of the version 8.4.11-11 over 8.4.10-10 and 8.4.8-8 in the high thread numbers. In the situations when the number of physical cores (80) is smaller than the number of threads (128+) the versions 8.4.10-10 and 8.4.8-8 have a steep performance degradation. However, the TPS for 8.4.11-11 keeps growing. This is due to the optimization done to InnoDB LRU pages flushing algorithm. The optimization specifically targeted the scenario when the data size is larger than the available server buffers and the server has many concurrent connections doing random read-write operations. The optimizations in 8.4.11-11 deserve a separate explanation and they will be published in another blog post.

The less noticeable, but important difference can be spotted between the TPS for 8.4.8-8 and 8.4.10-10.

The version 8.4.10-10 shows better performance (especially at the saturation point with 64 threads), which should mostly be attributed to the introduction of Performance Guided Optimization (PGO). 

More information on PGO can be found here:

https://docs.percona.com/percona-server/8.4/pgo.html

With the smaller data and buffer sizes the performance difference gives an almost identical picture:

4G buffer, 48G data [ INTERACTIVE GRAPH ][ TABLE ]

2G buffer, 24G data [ INTERACTIVE GRAPH ][ TABLE ]

Now let’s review what happens with the ratio 1:2.
This time the buffer pool size also plays a more significant role and the performance difference is not characterized by the Buffer / Data size ratio.

With innodb_buffer_pool_size=12G and 24G data size the performance gap between 8.4.11-11 and older versions is still huge as can be seen on the graph:

[ INTERACTIVE GRAPH ][ TABLE ]

However, setting innodb_buffer_pool_size=24G and 48G data size reduces the gap. The superiority of 8.4.11-11 is still visible:

[ INTERACTIVE GRAPH ][ TABLE ]

Moving to innodb_buffer_pool_size=48G and 96G data size shrinks the gap even more:

[ INTERACTIVE GRAPH ][ TABLE ]

In this post we are not going to talk about mechanisms behind shrinking performance gaps in 1:2 Buffer / Data size ratio.

Holding the entire data set in memory is not the most common thing for the database server, but in some cases it happens. Therefore, we are covering such situations as well.

[ INTERACTIVE GRAPH ][ TABLE ]

As the above graph shows, 8.4.10-10 is slightly ahead of 8.4.11-11, but the gap is very small.

This behavior is consistent with other data sizes for fully buffered data:

innodb_buffer_pool_size=64G and 48G Data Size:

[ INTERACTIVE GRAPH ][ TABLE ]

innodb_buffer_pool_size=128G and 96G Data Size:

[ INTERACTIVE GRAPH ][ TABLE ]

Again, we will not go into details about why this happens. Though it is worth noting that both 8.4.10-10 and 8.4.11-11 do better than 8.4.8-8 in all runs and configurations.

The table interpretation of the results is available as well.

4. Comparing with Upstream MySQL 8.4.11.

The performance improvements in Percona Server for MySQL 8.4.11-11 are not a part of the Upstream MySQL 8.4.11. The patch was specifically designed to address the issue of Percona Server being slower than MySQL in I/O bound scenarios.

Also, the patch eliminated the abrupt performance degradation in the higher thread count after reaching the saturation point at 64 threads:

[ INTERACTIVE GRAPH ][ TABLE ]

As the graph shows – Percona Server 8.4.8-8 / 8.4.10-10 was slower than MySQL in lower thread count. Although it was still faster in 128+ threads, the Percona Server was still subject to a substantial slow-down. That is where Percona Server 8.4.11-11 really shines.

However, with the fully buffered data MySQL goes faster than any Percona Server:

[ INTERACTIVE GRAPH ][ TABLE ]

5. Summary

The Performance of the Percona Server 8.4 for MySQL is progressing well from older to newer version offering significant performance improvements especially in the version 8.4.11-11. This version shows very significant improvements in performance on the data sets that require I/O. Also, it outperformed the upstream MySQL 8.4.11.

With fully buffered data sets the version 8.4.10-10 is slightly better than 8.4.11-11. MySQL Server in this case shows the fastest performance.

The PGO had a positive impact demonstrating the version 8.4.10-10 being faster in all tests on all configurations than 8.4.8-8.

The performance depends not only on the ratio between the buffer and the data size, but also on the buffer size.

The post Performance Progression of Percona Server for MySQL 8.4 appeared first on Percona.