-
Getting VECTOR capabilities in MySQL 8.4 using VillageSQL
For this quick verification of the 0.0.7 development branch of VillageSQL with the new vsql-vector plugin.
Recreate the VillageSQL Percona Live presentation using MySQL version 8.4 and SVECTOR. Demonstrate a more detailed example using SVECTOR(1024) string embedded data.
-
MySQL Community Engagement in Brazil and Europe
This September and October, Heather VanCura will meet with MySQL users, contributors, developers, DBAs, customers, and community leaders across Brazil and Europe. With more than 30 years of innovation behind it, the MySQL community is entering an important new phase. The focus is on expanding engagement, collaboration, and contributions while increasing and driving innovation and unification of the ecosystem. The tour will […]
-
Using DuckDB inside MySQL
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.
-
Group Replication Beyond a Single Cluster: DC-DR with Percona (PS MySQL) Operator
A while ago, we discussed the cross-site replication feature of the Percona PXC operator. Recently, a similar cross-site replication feature was introduced in the Percona (PS MySQL) operator v1.2.0, a topology based on Group Replication/InnoDB Cluster.
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.
Group Replication (DC-DR) using Innodb ClusterSet
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 28mshell> 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 26mStep 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"}'
pscluster1Step 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 29mStep 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.yamlWe 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.yamlshell> yq eval '.metadata.namespace = "ps-dr"' -i replica-secret.yamlshell> sed -i '' 's/ps-cluster1/ps-cluster2/g' replica-secret.yamlThe 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 createdshell> 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-secretsmysql:
clusterType: group-replication
size: 3
image: percona/percona-server:8.4.10-10.1
bootstrap:
mode: manualshell> kubectl apply -f deploy/cr.yaml -n psNote: 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 17mshell> 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-0Step 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"}'
pscluster2Step 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 37mBy 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 -- shsh-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.1We will apply the changes only to the DC node.kubectl apply -f deploy/clusterset.yamlPlease 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 3sAs 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 10hIf 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 Trueshell> 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:3306Also, we can manually access any running MySQL Pod and confirm the ClusterSet status.shell> kubectl exec -it ps-cluster1-mysql-0 -n ps -- shsh-5.1$ mysqlsh --uri=root@localhost:3306MySQL 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 -pmysql> 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 -pmysql> 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 switchoverAfter 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 Truekubectl exec -it ps-cluster1-mysql-0 -n ps -- sh
sh-5.1$ mysqlsh --uri=root@localhost:3306MySQL 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.
-
Too many GCache Page Files in MySQL Data Directory
A few thousand gcache.page.* files in a Percona XtraDB Cluster (PXC) data directory is not something you see every day. We came across a case where these files had been accumulating over time and slowly consuming disk space. So, let’s dig into what happened.
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.005978The 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 bytesThis 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.00Following 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.000006After 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.
|
