Servers Tested:

• MySQL 9.7.0 (PGO-enabled build released by Oracle)
• MySQL 9.7.0 Non-PGO (built without Profile-Guided Optimization — see BUILD.md)

Tier Configurations:

• Tier 2G: 2GB InnoDB buffer pool
• Tier 12G: 12GB InnoDB buffer pool
• Tier 32G: 32GB InnoDB buffer pool

View Results

Interactive Reports

The benchmark reports are available as interactive HTML pages at:

https://percona-lab-results.github.io/2026-pgo/index.html

Performance Graphs

Tier 2G (2GB Buffer Pool):

Tier 12G (12GB Buffer Pool):

Tier 32G (32GB Buffer Pool):

Key Findings

Performance Impact of PGO

MySQL 9.7.0 with Profile-Guided Optimization (PGO) demonstrates measurable performance improvements over the non-PGO build:

Overall Performance Summary:

• Average improvement: 6.5% across all configurations
• Peak improvement: 14.3% (Tier 32G, 1 thread), gradually tapering to 10.3% at 512 threads as concurrency increases
• Performance gains range from 0.5% to 14.3% in most scenarios
• Minor regression (-3.1% at Tier 12G, 128 threads)

Performance by Buffer Pool Size:

• Tier 2G (2GB buffer pool): Average improvement of 3.0%

– Best gains at 4 threads (5.5% improvement)

– Gains range from 0.5% to 5.5% across all thread counts

– Modest improvements with no regressions

• Tier 12G (12GB buffer pool): Average improvement of 4.1%

– Best gains at 4 threads (8.6% improvement)

– Strong gains at low concurrency (1-4 threads: 7.3%-8.6%)

– Minor regression at 128 threads (-3.1%), neutral at 512 threads (-0.0%)

• Tier 32G (32GB buffer pool): Average improvement of 12.2%

– Consistently strong gains across all thread counts (10.3% to 14.3%)

– Peak performance at lowest concurrency (1 thread: 14.3%)

– Maintains 11-12% improvement even at highest concurrency (128-512 threads)

Key Observations:

• PGO provides the most significant benefits with larger buffer pools (32GB tier shows 12.2% average improvement)
• Largest buffer pool configuration benefits from PGO across all concurrency levels with no regressions
• Low to moderate concurrency (1-32 threads) shows best PGO gains across all tiers
• Smaller buffer pools (2GB, 12GB) show more modest improvements and occasional regressions at very high thread counts
• The performance improvements demonstrate PGO’s effectiveness in optimizing hot code paths, particularly when memory resources are abundant

InnoDB Metrics Analysis

Deep analysis of InnoDB metrics reveals the source of PGO’s performance improvements:

Root Cause: CPU-Level Optimizations

• PGO improvements are NOT from I/O optimization, caching, or lock reduction
• Buffer pool hit ratios remain virtually identical between PGO and non-PGO builds
• Lock contention is minimal in both builds
• All I/O metrics scale proportionally with increased throughput

What PGO Actually Optimizes:

• ✓ Better instruction cache utilization
• ✓ Improved branch prediction in hot code paths
• ✓ Optimized function inlining
• ✓ More efficient CPU instruction ordering

The metrics confirm that PGO’s 6.5% average improvement comes entirely from making the CPU more efficient at executing MySQL’s hot code paths, allowing it to process more transactions per second with the same hardware resources.

What is PGO?

Profile-Guided Optimization (PGO) is a compiler optimization technique that uses runtime profiling data to guide code optimization. The compiler first instruments the code, collects execution profiles during typical workload runs, and then recompiles the code with optimizations targeted at the most frequently executed code paths.

Benefits of PGO:

• Improved branch prediction
• Better instruction cache utilization
• Optimized function inlining
• Reduced code bloat
• Better register allocation

Benchmark Methodology

Workload

• Tool: Sysbench OLTP Read/Write benchmark
• Tables: 20 tables
• Table Size: 5,000,000 rows per table
• Thread Counts: 1, 4, 16, 32, 64, 128, 256, 512

Configuration

• Warmup:

– Read-only: 180 seconds

– Read-write: 600 seconds

• Measurement Duration: 900 seconds (15 minutes) per thread count
• Runs: Single run per configuration

System Metrics Collected

• InnoDB storage engine metrics
• MySQL status variables
• MySQL system variables
• System I/O statistics (iostat)
• Virtual memory statistics (vmstat)
• CPU statistics (mpstat)
• System statistics (dstat)

Appendix

For Repository structure, Build steps and Technical details go to https://github.com/Percona-Lab-results/2026-pgo/blob/main/README.md#report-categories

The post MySQL 9.7.0 PGO Benchmark Analysis appeared first on Percona.

v1.26.5 introduces a MySQL Component build for MySQL 8.4 LTS and 9.7 LTS, a unified logging abstraction, and a set of plugin stability fixes. The plugin path for MySQL 8.0, 8.4, and 9.0 is unchanged.

Why the Component architecture matters

MySQL has been deprecating the legacy plugin API in favor of the Component architecture since 8.0. Components install via INSTALL COMPONENT, integrate through typed service interfaces, and are better isolated from server internals — meaning fewer breakages across MySQL minor versions and a supported path forward as the plugin API winds down. For MyVector, this move isn’t optional in the long run: the component model is where MySQL’s extension ecosystem is heading, and building on it now means users on 8.4 LTS and 9.7 LTS get a stable, upgrade-resilient foundation rather than a deprecated one.

What’s New

MySQL Component build. MyVector can now be installed via INSTALL COMPONENT on MySQL 8.4 and 9.7. The component ships UDF registration, binlog-driven index synchronization, and a query rewrite service hook — all backed by the same core vector index engine as the plugin. A new myvector_log.h abstraction unifies logging across both build modes, replacing scattered #ifdef blocks.

MySQL 9.7 LTS. The 9.x CI, release, and Docker targets move from 9.6 to 9.7 LTS. A new Docker image tag ghcr.io/askdba/myvector:mysql9.7 is now available. If you’re on :mysql9.6, switch to :mysql9.7.

Plugin stability fixes. gmtime() and asctime() replaced with their thread-safe _r variants; concurrency fixes in plugin init/deinit; null-guard added to myvector_ann_set row function. Also: config file permission hardening, a binary_log namespace conflict fix on 8.4/9.7, and a Quick Start wget URL correction (issue #89).

How to Install

Component — pre-built binaries (Linux amd64) are attached to this release:

bash

tar -xzf myvector-component-mysql8.4.8-linux-amd64.tar.gz
cp libmyvector_component.so $(mysql_config --plugindir)/
mysql -u root -p -e "INSTALL COMPONENT 'file://libmyvector_component';"

Also available: myvector-component-mysql9.7.0-linux-amd64.tar.gz and checksums.txt. To build from source, use scripts/build-component-8.4-docker.sh or scripts/build-component-9.7-docker.sh.

Plugin — Docker or build from source. Pre-built plugin .so files are not on the release page. Use Docker (multi-arch, amd64 + arm64):

bash

docker pull ghcr.io/askdba/myvector:mysql8.4   # or :mysql8.0, :mysql9.7

Or build from source with scripts/build-release.sh (requires MySQL dev headers).

Upgrade Notes

• No schema or index migration required.
• Docker tag change: :mysql9.6 → :mysql9.7.
• The component path is the forward path for MySQL 8.4+. The plugin remains supported for 8.0/8.4/9.0 through their EOL dates.
• Windows is not supported — use Linux container images.

See docs/BUILD_MODES.md and docs/COMPONENT_MIGRATION_PLAN.md for migration guidance. Bugs and feedback welcome on GitHub Issues.

Cross blog from Oracle https://blogs.oracle.com/mysql/mysql-9-7-is-out-and-the-community-wins

MySQL 9.7 came out on April 21 and I’ve been going through the release notes so you don’t have to. The short version: Oracle has made several previously Enterprise-only features available in the Community Edition; the Hypergraph Optimizer is now free for everyone; and if you’re still on MySQL 8.0, it has reached End-of-Life. Like right now. We’ll get to that.

Let’s go through what matters most.

First: MySQL 8.0 has reached End-of-Life

MySQL 8.0.46 shipped alongside 9.7, and it is the last 8.0 release. As of April 2026, 8.0 is officially End-of-Life. No more security patches. No more bug fixes. Oracle’s release notes now encourage users to upgrade to MySQL 8.4 LTS or 9.7.

If you’re on 8.0 in production, you’ve got two paths. MySQL 8.4 LTS is the safest, most conservative upgrade with a well-trodden migration path from 8.0. MySQL 9.7 is if you want all the new stuff, including everything in this post. Either way, the clock has run out on 8.0. It is strongly recommended to plan your upgrade soon.

Some Enterprise features available in the Community Edition

This is the headline. Oracle has moved five components from Enterprise Edition into Community Edition with this release. For self-hosted MySQL users, this is genuinely good news.

Four of them are replication components that now ship in Community Edition.

Replication Applier Metrics gives you real visibility into how your replica is processing events. Lag monitoring, throughput, the works. This was always the kind of thing you had to implement separately or access the Enterprise Edition.

Group Replication Flow Control Statistics now provides visibility into why your GR cluster is throttling. If you’ve ever stared at a slow cluster and had no idea what was happening under the hood, this one’s for you.

Group Replication Resource Manager lets you control how resources get allocated so replication stops starving your app workloads.

Group Replication Primary Election gives better observability into failover behavior during primary elections, which is exactly when you most need to know what’s going on.

And then there’s the Telemetry component, which is big if you’re running MySQL in a cloud-native setup. Metrics and traces can now flow to Prometheus, OpenTelemetry, and whatever else your observability stack includes. This was Enterprise-only until today.

The Hypergraph Optimizer is in Community Edition now

This is the one that stands out as particularly impactful.

Quick background: MySQL’s traditional query optimizer uses a left-deep tree approach to figure out join order. Works fine for simple queries, but with complex multi-table joins it can miss a lot of better execution plans. The Hypergraph Optimizer takes a completely different approach. It models the whole query as a hypergraph and uses dynamic programming to search a much bigger space of possible plans.

If you’ve got complex reporting queries or anything with a lot of joins, it’s worth turning on and seeing what happens.

To try it:

SET optimizer_switch=’hypergraph_optimizer=on’;

You can set it at session scope (great for testing), globally, persistently across restarts, or hint it on a per-query basis. Start with session scope on your actual slow queries before you go global. The optimizer is solid, but you don’t want surprises in production.

JSON Duality Views are now fully supported in Community

Previously, in Community Edition, you could define JSON Duality Views, but couldn’t do INSERTs, UPDATEs, or DELETEs through them. That was an Enterprise thing. This capability is now available in Community Edition. Full DML is now in Community.

They also added auto-increment support for duality view inserts, which means you can stop manually wiring up primary keys:

INSERT INTO orders_view

VALUES (‘{“customer_id”: 42, “product_id”: 7, “quantity”: 3}’);

The order_id gets generated automatically. Small change, but it removes a real friction point if you’ve been experimenting with duality views.

A few other things worth knowing

Password hashing got stronger.caching_sha2_password now supports PBKDF2 with SHA-512 storage format. Your existing clients don’t need to change anything since this is server-side only, but it makes stored hashes significantly harder to brute-force. Worth noting if you’re in a compliance-heavy environment like PCI-DSS or HIPAA.

Rolling upgrades just got easier. There’s a new variable called replica_allow_higher_version_source that lets a lower-version replica connect to a higher-version primary. Practically, this means you can upgrade your primary first, verify everything looks good, and then roll through your replicas at your own pace instead of taking the whole fleet down at once.

Container users, this one’s for you. MySQL now correctly reads cpuset cgroup limits to figure out how many CPUs are actually available to it. If you’d constrained MySQL to 4 CPUs in Kubernetes, it might have been sizing its thread pools against all 32 host CPUs anyway. That’s fixed.

OpenSSL got bumped to 3.5.5. Not exciting, but good hygiene.

What about Vector search?

A lot of people are asking about this, and the current status is that it is not yet generally available. Oracle is clearly building toward native vector search across the 9.x innovation releases, and you can see pieces of the foundation being put in place. But 9.7 isn’t the release where it lands as something you can use. It’s on the roadmap, it’s coming, the team is looking for feedback, and it is expected in future releases.  As some of you know, I’m especially interested in vector features as a maintainer of the MyVector project. We’ve had a couple of calls with the engineering team to collaborate on this subject. This might be an area for community contributions. Please stay tuned. 

So, should you upgrade?

MySQL 9.7 is available at dev.mysql.com/downloads. If you end up benchmarking the Hypergraph Optimizer or trying the new replication components, share your results. The community learns from real-world numbers a lot more than from release notes. Drop them in the MySQL Community Forums or tag #MySQL97.

Let’s see how the maintenance goes. It was supposed to be a simple restart. The kind you’ve done a hundred times. You SSH in, run the maintenance, bring the node back up, and go grab a coffee. Except this time, the coffee went cold on the desk… because MySQL refused to start.

The Problem

The error log of Percona XtraDB Cluster (8.0) had the following information:

2025-11-05T05:26:10.982984Z 0 [ERROR] [MY-000059]   [Server] SSL error: Unable to get certificate from '/var/lib/mysql/server-cert.pem'.
2025-11-05T05:26:10.983030Z 0 [Warning] [MY-013595] [Server] Failed to initialize TLS for channel: mysql_main. See below for the description of exact issue.
2025-11-05T05:26:10.983045Z 0 [Warning] [MY-010069] [Server] Failed to set up SSL because of the following SSL library error: Unable to get certificate
2025-11-05T05:26:10.983052Z 0 [Note] [MY-000000] [WSREP] New joining cluster node configured to use specified SSL artifacts
2025-11-05T05:26:10.983083Z 0 [Note] [MY-000000] [Galera] Loading provider /usr/lib64/galera4/libgalera_smm.so initial position: 07c67757-0d18-11ef-b5a9-ee5d87b39aa8:4147053897
2025-11-05T05:26:10.983098Z 0 [Note] [MY-000000] [Galera] wsrep_load(): loading provider library '/usr/lib64/galera4/libgalera_smm.so'
2025-11-05T05:26:10.983742Z 0 [Note] [MY-000000] [Galera] wsrep_load(): Galera 4.22(f6c0465) by Codership Oy <info@codership.com> (modified by Percona <https://percona.com/>) loaded successfully.
2025-11-05T05:26:10.983771Z 0 [Note] [MY-000000] [Galera] Resolved symbol 'wsrep_node_isolation_mode_set_v1'
2025-11-05T05:26:10.983784Z 0 [Note] [MY-000000] [Galera] Resolved symbol 'wsrep_certify_v1'
2025-11-05T05:26:10.983807Z 0 [Note] [MY-000000] [Galera] CRC-32C: using 64-bit x86 acceleration.
2025-11-05T05:26:10.983995Z 0 [Note] [MY-000000] [Galera] not using SSL compression
2025-11-05T05:26:10.984341Z 0 [ERROR] [MY-000000] [Galera] Bad value '/var/lib/mysql/server-cert.pem' for SSL parameter 'socket.ssl_cert': 336245135: 'error:140AB18F:SSL routines:SSL_CTX_use_certificate:ee key too small'
         at /mnt/jenkins/workspace/pxc80-autobuild-RELEASE/test/rpmbuild/BUILD/Percona-XtraDB-Cluster-8.0.42/percona-xtradb-cluster-galera/galerautils/src/gu_asio.cpp:ssl_prepare_context():471
2025-11-05T05:26:10.984401Z 0 [ERROR] [MY-000000] [Galera] Failed to create a new provider '/usr/lib64/galera4/libgalera_smm.so' with options 'gcache.size=1G;gcache.recover=yes;socket.ssl=yes;socket.ssl_ca=/data00/mysqldata/ca.pem;socket.ssl_cert=/data00/mysqldata/server-cert.pem;socket.ssl_key=/data00/mysqldata/server-key.pem;socket.ssl_key=/var/lib/mysql/server-key.pem;socket.ssl_ca=/var/lib/mysql/ca.pem;socket.ssl_cert=/var/lib/mysql/server-cert.pem': Failed to initialize wsrep provider
2025-11-05T05:26:10.984434Z 0 [ERROR] [MY-000000] [WSREP] Failed to load provider
2025-11-05T05:26:10.984448Z 0 [ERROR] [MY-010119] [Server] Aborting
2025-11-05T05:26:10.984602Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.42-33.1)  Percona XtraDB Cluster (GPL), Release rel33, Revision 6673f8e, WSREP version 26.1.4.3.
2025-11-05T05:26:10.985473Z 0 [ERROR] [MY-010065] [Server] Failed to shutdown components infrastructure.

MySQL was down, and the maintenance clock was running. The certificate file sitting at /var/lib/mysql/server-cert.pem was the same file that had been working perfectly fine before the restart!!
From past history, it was known that the following commands were executed correctly on the same cluster node

SET GLOBAL ssl_ca = '/var/lib/mysql/ca.pem';
  SET GLOBAL ssl_cert = '/var/lib/mysql/server-cert.pem';
  SET GLOBAL ssl_key = '/var/lib/mysql/server-key.pem';
  ALTER INSTANCE RELOAD TLS;

Clients connected over TLS. Galera nodes communicated securely. There were zero complaints from the error log.
In other words, the SSL reload at runtime inherited the process environment that existed when MySQL originally booted. Everything was smooth, but after a restart? MySQL complains and declines to start. So what has changed?

Checking Usual Suspects

File permissions

We checked the PEM files. 

Ownership: mysql:mysql.
Permissions: 644 for the cert, 600 for the key. 

We compared them against the other Galera nodes, and they were identical. This didn’t look like a permissions problem.

Is SELinux to blame here?

SELinux has ruined enough DBA time that it is one of the top spots on such checklists – but it was permissive.

$ getenforce
Permissive

That means it was logging any security issues, but not blocking. And there were no AVC denials related to MySQL or the PEM files in /var/log/audit/audit.log or dmesg!

File corruption

Did the files get corrupted/replaced during or before the MySQL restart?

$ openssl x509 -in /var/lib/mysql/server-cert.pem -noout -text
# Output looked perfectly valid when compared to the output from other nodes

$ openssl rsa -in /var/lib/mysql/server-key.pem -check
RSA key ok

The files were fine. They parsed cleanly. OpenSSL could read them. So why couldn’t MySQL?

More Logs review

We scanned /var/log/messages and journalctl for anything unusual around the time of the restart. No disk errors. No OOM kills. No kernel panics. Nothing that screamed “I am the Dhurandhar that’s destroyed your node.” At this point, most of the usual suspects were guilt-free, staring at us, asking, “Who did it?”

The Clue

It is good to communicate with stakeholders, and we did – “Was there any recent change on your side?” to the client, and then uttered the golden words “Last week the crypto-policy was updated on all of the DB servers to comply with PCI.”

PCI > Crypto-policy – Let’s go and check it !!

$ update-crypto-policies --show
FUTURE

The system was running RHEL’s FUTURE cryptographic policy.

For those unfamiliar (including me at the time), Red Hat Enterprise Linux (and its derivatives, such as Rocky, Alma, and Oracle Linux) ships with a system-wide cryptographic policy framework. It’s a centralized way to enforce minimum standards for TLS versions, cipher suites, key lengths, and signature algorithms across all applications on the system that include OpenSS and yes, anything that links against those libraries… like MySQL.

Here’s a table that shows information about the crypto-policy levels:

So FUTURE demands a 3072-bit RSA key; otherwise, it is blocked. What do we have?

$ openssl rsa -in server-key.pem -text -noout | head -1
RSA Private Key: (2048 bit, 2 primes)

2048 bits! C’mon! And now I recall the error log again… The hint was there:

error:140AB18F:SSL routines:SSL_CTX_use_certificate:ee key too small

Now we have our story straight.
On restart, our PXC cluster node started a new process linked against OpenSSL, which now enforced the FUTURE policy. OpenSSL looked at the 2048-bit RSA certificate and said: “Nope. Too small.”

Fixture

The quick fix here would be to adjust the policy to DEFAULT.

sudo update-crypto-policies --set DEFAULT

This will accept the current SSLs, and the node will join the cluster readily.

Alternatively, to remain compliant and adhere to the security policy strictness, the fixture will be to

• Generate new certificates
• Deploy the keys/certs to all Galera nodes
• Perform a rolling restart

Conclusion

This was a classic case of a problem hiding at the boundary between two domains, database administration and operating system security. The DBA saw valid certificates and correct MySQL configuration. The sysadmin saw a properly hardened system with a strong crypto policy. Neither was wrong. But the intersection of their two correct configurations produced a failure.

This incident reinforces the importance of cross-domain awareness, where resolving database issues sometimes requires understanding and challenging system-level security decisions.

The post Curious case of PXC node that refused to start due to SSL appeared first on Percona.

We all know and love the Query Analytics (QAN) dashboard… It’s the first place we look when an incident alert fires or when a developer asks, “Why is the app slow?” or “What was going on during the midnight production outage?”

But sometimes, the standard dashboards just don’t tell the whole story or maybe are not clear enough. QAN is great, but shouldn’t we have more? If you have PMM running, you already have a Ferrari engine under the hood: ClickHouse. Most of us just drive it in first gear using the default UI.

In this post, we are going to take the training wheels off. We will bypass the standard QAN interface and talk directly to the ClickHouse backend to build highly specialised dashboards. We aren’t just looking for “slow” queries anymore; we are hunting for inefficiency, volatility, and the “silent killers” that standard monitoring often misses.

This is the hands-on blog, so grab your coffee and let’s turn that PMM instance into a deep-dive forensic tool.

Create a New Dashboard in PMM

1. Connect to PMM > Dashboards > Create New Dashboard
2. Save it with name “Slow Query Analysis” and Description “Slow Query Analysis from PMM’s QAN database (clickhouse)”
3. Click on add visualisation & select datasource “ClickHouse”
   
   
4. Choose SQL Builder
   
   
5. Paste the following query to get top 10 slow queries from the database
   
   

   SELECT fingerprint
       FROM pmm.metrics
       WHERE service_type = 'mysql'
         AND $__timeFilter(period_start)
       GROUP BY fingerprint
       ORDER BY sum(m_query_time_sum) DESC
       LIMIT 10

6. Choose “Table View” on the top to view the list
   When you click “Run Query” you will see the top 10 slow queries in the chosen time period.
7. Let’s Save the dashboard after Panel Options updates as follows7.1 Change Panel Name and Description to: “Slow Query Analysis”7.2 Legend Placement to “Bottom”, Values to “min”,”max”, “mean”7.3 Change Axis’ Scale to “Logarithmic”Logarithmic scale on an axis compresses large ranges of data, making it ideal for visualizing metrics with vastly different magnitudes. This provides good visualisation for queries of different execution time frames.7.4 Save DashboardAlright, we’re at our first step. This first result set shows the top 10 slow query fingerprints across all MySQL services tracked by PMM for the selected time range. It provides a quick, environment-wide view of the most expensive query patterns. But this does not provide a clear picture. Let’s refine the dashboard to focus on specific queries, servers and observe their performance over time.Now, let’s introduce a variable to filter the data.
8. Click on Settings on Dashboard’s home page8.1 Choose “Variables” tab and click on “Add Variable”8.2 Add variable configuration and Save Dashboard 
9. Go Back to Dashboard and Edit “Slow Query Analysis” Panel.
   • Now you should see the Query ID filter on the top.
10. Change the query to the following
    
    

    SELECT
      period_start AS time,
      left(fingerprint, 80) AS query_text,
      sum(m_query_time_sum/m_query_time_cnt) AS query_time
    FROM
      pmm.metrics
    WHERE
      service_type = 'mysql'
      AND $__timeFilter(period_start)
      AND fingerprint IN (
        SELECT fingerprint
        FROM pmm.metrics
        WHERE service_type = 'mysql'
          AND $__timeFilter(period_start)
          AND ($queryid = '' OR queryid = $queryid)
        GROUP BY fingerprint
        ORDER BY sum(m_query_time_sum) DESC
        LIMIT 10
      )
    GROUP BY
      time,
      fingerprint
    ORDER BY
      time,
      query_time DESC

    • Basically the query is fetching start time, query text and average query time for the selected period for the top 10 Queries in that time-frame.
    • There is a filter for the “queryid” variable which you may use if you want to filter on a specific queryid.
    • Choose “Time Series” as “Query Type”
11. Adjust Panel Options11.1 Choose “Standard options” > “Unit” as “Time / Seconds (s)” from drop down.11.2 Choose “Standard options” > “Display name” as “${__field.labels.query_text}”11.3 Click on “Save Dashboard”
12. Your dashboard should be ready

Now, by default this dashboard is plotting top 10 queries. If you have a query fingerprint handy, you may be able to filter the search by that specific query.  That said, this is still plotting queries across all the monitored instances. Let’s move on to add the service_name filter.

Adding service_name filter

1. Add Variable
   1. Create new variable named “service_name”
   2. Use variable type “Query”
   3. Use Data Source as “ClickHouse”
   4. Query:
      
      

      select distinct service_name from pmm.metrics where service_type = 'mysql';

   5. Unselect all checkboxes in “Selection options”
   6. Save Dashboard
2. Update Query

SELECT
  period_start AS time,
  left(fingerprint, 80) AS query_text,
  sum(m_query_time_sum/m_query_time_cnt) AS query_time
FROM
  pmm.metrics
WHERE
  (service_name = '' OR service_name = '$service_name')
  AND service_type = 'mysql'
  AND $__timeFilter(period_start)
  AND fingerprint IN (
    SELECT fingerprint
    FROM pmm.metrics
    WHERE service_type = 'mysql'
      AND $__timeFilter(period_start)
      AND (service_name = '' OR service_name = '$service_name')
    GROUP BY fingerprint
    ORDER BY sum(m_query_time_sum) DESC
    LIMIT 10
  )
GROUP BY
  time,
  left(fingerprint, 80) 
ORDER BY
  time,
  query_time DESC

I know many of you are naturally curious and enjoy experimenting with PMM and Grafana… So you’ve probably already started thinking about how far this can be taken. Feel free to share your ideas or custom dashboards in the comments.

Sample Dashboards:

The Query Analysis and Insights Dashboard

Okay, for those who are looking to have quick results, I’ve prepared the complete Query Analysis and Insights Dashboard for you to import and use instantly.

By importing the JSON file, you’ll get the full working dashboard with all panels preconfigured, including:

• Slow Query Analysis
• Latency Distribution Heatmap
• Query Volatility (P99 vs Average)
• Lock Wait Ratio Over Time (Top Contended Queries)
• Temporary Table Usage (Disk & Memory)
• Query Efficiency (Rows Examined vs Rows Sent)
• Error Rate vs Throughput
• Workload Distribution by User
• Query Volume by Client Host
• Execution Time vs Lock Wait Time

This allows you to instantly explore PMM Query Analytics data, adjust time ranges and filters, and correlate query performance, contention, and workload behavior without recreating the dashboard from scratch.

Dashboard JSON available here:

• Grafana: https://grafana.com/grafana/dashboards/24896
• GitHub:  https://github.com/Percona-Lab/pmm-dashboards/query_analysis_insights.json

Give it a go and let me know if you have suggestions or requests. Also consider sharing if you create something interesting.

Cheers.

The post Building Query Analysis and Insights Dashboard in PMM appeared first on Percona.

At 80%, we have enough time to change it to BIGINT.…. Right? Let’s see.

So we used pt-online-schema-change to perform the alter.

It started running at a good pace but slowed over time.

Why?

Well, let’s look at the definition of the table:

mysql> show create table myschema.mytableG
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `long_column` varchar(1000) NOT NULL,
  `state` tinyint unsigned NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `short_column` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_long_column` (`long_column`,`state`),
  KEY `idx_short_column` (`short_column`,`state`),
  KEY `idx_short_col2` (`short_column`)
) ENGINE=InnoDB AUTO_INCREMENT=4009973818 DEFAULT CHARSET=utf8mb3

NOTE1: The index on long_column is for a varchar column with a length of 1000; it may not be required, and an index prefix may be more helpful here.

NOTE2: The index idx_short_col2 is duplicated, as it is covered by the index idx_short_column.

Those changes require testing and are out of scope for this emergency, but they are worth mentioning.

Table size:

+---------------+------------+------------+---------+----------+---------+----------+--------+
| TABLE_SCHEMA  | TABLE_NAME | TABLE_ROWS | DATA_GB | INDEX_GB | FREE_GB | TOTAL_GB | ENGINE |
+---------------+------------+------------+---------+----------+---------+----------+--------+
| myschema      | mytable    | 3906921584 |    1118 |     1790 |       0 |     2907 | InnoDB |
+---------------+------------+------------+---------+----------+---------+----------+--------+

Look at the indexes being way bigger than the data.

mysql> SELECT database_name, table_name, index_name, ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_in_mb FROM mysql.innodb_index_stats WHERE stat_name = 'size' AND index_name != 'PRIMARY' and database_name='myschema' and table_name='mytable' ORDER BY size_in_mb DESC;
+---------------+------------+-------------------+------------+
| database_name | table_name | index_name        | size_in_mb |
+---------------+------------+-------------------+------------+
| myschema      | mytable    | idx_long_column   | 1583538.95 |
| myschema      | mytable    | idx_short_column  |  126432.98 |
| myschema      | mytable    | idx_short_col2    |  122699.95 |
+---------------+------------+-------------------+------------+
3 rows in set (0.01 sec)

While the pt-online-schema-change runs, it copies the data to a new table. As the data is being copied, the secondary indexes must be maintained.

NOTE the huge index for a varchar(1000) that is ~1.5T in size. Maintaining such an index becomes increasingly expensive as the data size increases.

The pt-online-schema-change had been running for ~8 days, and its latest estimate was 53 more days, which we can’t afford, since the maximum value would be exceeded in ~15 days. 

Copying `myschema`.`mytable`:  12% 53+16:48:01 remain
Copying `myschema`.`mytable`:  12% 53+16:48:30 remain
Copying `myschema`.`mytable`:  12% 53+16:48:59 remain
Copying `myschema`.`mytable`:  12% 53+16:49:26 remain
Copying `myschema`.`mytable`:  12% 53+16:49:53 remain
Copying `myschema`.`mytable`:  12% 53+16:50:19 remain
Copying `myschema`.`mytable`:  12% 53+16:50:49 remain
Copying `myschema`.`mytable`:  12% 53+16:51:17 remain
Copying `myschema`.`mytable`:  12% 53+16:51:45 remain

So what do we do now?

We suggested canceling the pt-online-schema-change and creating an Aurora blue-green deployment.

Then perform the direct ALTER on the green cluster. And finally, when ready, do the failover.

Sounds good, doesn’t it?

First, we need to ensure that the new cluster (green) has the replica_type_conversions  parameter in its cluster parameter group to “ALL_NON_LOSSY, ALL_UNSIGNED” in order to be able to replicate from an int unsigned column to a bigint unsigned column.

So we tried that, it started too fast ~0.036% per minute, that’s 2 days. That’s great!

We left the process running over the weekend, but we noticed it started to slow down again… By Monday, it was advancing at ~0.01% every 5 mins, which gives an ETA of 34 days. 

Why? 

Again, using the direct ALTER MySQL copies the data to a temp table, and the bigger the data, the harder it is to maintain the indexes. 

Again, unacceptable.

Note that with the above 2 approaches, we lost ~12 days of precious time, and the deadline for auto_increment exhaustion was approaching.

Then we thought: What if we drop the secondary indexes, do the alter, and then add the indexes back?

In theory, it should be faster, as:

• Dropping the indexes is a metadata-only operation with ONLINE DDL.
• Altering the column datatype from INT to BIGINT is not an ONLINE operation, but the fact that it doesn’t have to update secondary indexes during row copying to a new temporary table prevents the slowdown.
  
• Adding back the secondary indexes is an ONLINE DDL operation:

“Online DDL support for adding secondary indexes means that you can generally speed the overall process of creating and loading a table and associated indexes by creating the table without secondary indexes, then adding secondary indexes after the data is loaded.”

https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html

So let’s do this:

The deletion of the indexes was really quick, as expected (metadata-only operation):

mysql> ALTER TABLE myschema.mytable DROP INDEX idx_long_column, DROP INDEX idx_short_column, DROP INDEX idx_short_col2;
Query OK, 0 rows affected (49.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

Then the change of the datatype:

mysql> ALTER TABLE myschema.mytable CHANGE COLUMN id id bigint unsigned NOT NULL AUTO_INCREMENT;
Query OK, 4058047205 rows affected (13 hours 9 min 10.62 sec)
Records: 4058047205  Duplicates: 0  Warnings: 0

Looks very promising!!!

The final step, add back the indexes:

mysql> ALTER TABLE myschema.mytable ADD INDEX `idx_long_column` (`long_column`,`state`), ADD INDEX `idx_short_column` (`short_column`,`state`), ADD INDEX `short_col2` (`short_column`);
ERROR 1878 (HY000): Temporary file write failure.

Why?

Well, the INPLACE operation uses the tmp dir to write sort files. In Aurora, there are certain limits for the temporary space based on the instance type. 

In a regular MySQL instance, we can modify the innodb_tmpdir to another location with enough disk space; however, in Aurora, the parameter is not modifiable, which could have made the whole process easier.

Even with a larger instance type, it’s hard to create the 1.5T index without breaking open the piggy bank.

Last resort, add the indexes back with the COPY algorithm:

mysql> ALTER TABLE myschema.mytable ALGORITHM=COPY, ADD INDEX `idx_long_column` (`long_column`,`state`), ADD INDEX `idx_short_column` (`short_column`,`state`), ADD INDEX `idx_short_col2` (`short_column`);
Query OK, 4147498819 rows affected (6 days 1 hour 55 min 57.00 sec)
Records: 4147498819  Duplicates: 0  Warnings: 0

Why does it work? Because ALTER TABLE using the COPY algorithm uses the datadir as the destination for the temporary table, the rows are copied there. It doesn’t have the limitation of the temporary directory mentioned above.

We were able to make it on time about 4 days before the auto_increment exhaustion, preventing downtime.

In retrospective we could have used the following approach to avoid the use of the blue/green deployment:

1. Perform a pt-online-schema-change on the main table, dropping the indexes, and changing the column type to bigint. ( with –no-swap-tables –no-drop-old-table –no-drop-new-table –no-drop-triggers).
2. Add the secondary indexes using the direct alter with the COPY algorithm in the _new table.
3. Once the alter finishes, swap the tables and drop the triggers.

Conclusion:

What initially looked like an easy task with pt-online-schema-change, ended up being more complex. 

You need to check the data definition, the index sizes, the Aurora limits, and how the different algorithms work to make a decision on the best way to proceed with those tasks, specially on situations like these where you have the pressure of the auto_increment being exhausted and there’s risk of downtime if it is not done on time.

And of course, monitor auto_increment exhaustion for your tables, and use a reasonable threshold that gives you enough time to plan and change the table definition. You can use Percona Monitoring and Management for this, specifically on the MySQL > MySQL Table Details dashboard.

The post Run an ALTER TABLE for a huge table in Aurora appeared first on Percona.

Percona XtraBackup is a 100% open-source backup solution for Percona Server for MySQL and MySQL®. It is designed for high-availability environments, performing online, non-blocking, and highly secure backups of transactional systems without interrupting your production traffic.

While full backups work for small databases, large-scale systems rely on incremental backups to save space and time. However, the “prepare” stage, required to make the incremental backups consistent, was slow because XtraBackup processed the .delta files serially. The .delta files are generated per table and store only the modifications since the last backup.

Great news! In XtraBackup versions 8.0.35-33 and 8.4.0-3 and later, we’ve added support for the --parallel option during the prepare stage. This option lets XtraBackup process multiple .delta files simultaneously, significantly reducing the preparation time, especially when you have a large number of IBD files.

Please add --parallel=X, with the number of threads to use, to the xtrabackup --prepare --apply-log-only command to speed up the incremental prepare operation.

The Incremental Backup Workflow

Before we dive into the performance gains, it’s important to understand how Incremental backups work.

1. Creating the Backups

The process starts with a full backup followed by a backup that captures the changes since the last backup. This smaller backup is called an incremental backup. XtraBackup creates .delta files during incremental backups. Let’s review an example.

• Take Full Backup: Your starting point is Point A. This backup is an entire copy of your data.
• Take Inc1 Backup: XtraBackup identifies the changes between Point A and Point B. It creates a .delta file for every table that has been changed. Delta files contain only the pages that changed between the backups.
• Take Inc2 Backup: XtraBackup identifies the changes between Point B and Point C. It creates a new set of .delta files for this specific period.

For more detailed steps/commands, please check the documentation here: https://docs.percona.com/percona-xtrabackup/8.0/create-incremental-backup.html

2. Preparing the Backups

To restore the data to the latest point, you must merge these changes back into the full backup. The “prepare” phase works differently here:

• Prepare Inc1: You merge the Inc1 changes into the full backup using the --apply-log-only option. In this step, XtraBackup applies the .delta files and the redo logs, but does not apply the Undo logs
• Prepare Inc2: You merge the Inc2 changes into the updated base using the --apply-log-only option. XtraBackup applies the .delta files and the Redo logs but skips the Undo logs.
• Final Prepare: After all the incremental backups are merged, you run a final prepare command on the full backup. This final step applies the Undo logs to make the entire dataset consistent. If you apply the Undo logs during the intermediate steps, you cannot merge any further backups.

More detailed steps to prepare an incremental backup are described here: https://docs.percona.com/percona-xtrabackup/8.0/prepare-incremental-backup.html

The Improvement: Parallel Incremental Delta Apply

We have improved the Incremental Delta Apply phase. These are “Prepare inc1” and “Prepare inc2” phases as described above. --parallel option should be used along with the --apply-log-only to apply the .delta files in parallel.

We completed this essential improvement as part of [PXB-3427].

In previous versions, XtraBackup applied the .delta files as soon as a file was discovered in the incremental backup directory. Starting with versions 8.0.35-33 and 8.4.0-3, to apply the .delta files, XtraBackup scans the backup directory and builds a queue of delta files. Multiple threads (defined by --parallel ) consume this queue simultaneously. Each thread reads a .delta file and writes its pages to the corresponding InnoDB Data File (.ibd file).

Benchmarks

This benchmark is created using the scripts, and the instructions are in JIRA: PXB-3427

When your backup contains a large number of small .delta files, increasing the --parallel value can drastically reduce the time taken to prepare the incremental backup by distributing the high per-file overhead across more threads. However, for other categories with fewer or larger files, performance typically plateaus after 16 threads, and pushing higher can even lead to slight regressions due to thread management overhead. While there is no single “golden value” to recommend for every scenario, we recommend starting with a value of 8 to find the optimal balance for your specific environment.

Disk Utilization with XtraBackup prepare using --parallel=1 vs --parallel=64

The PMM graphs below show the Disk IOPs used by the XtraBackup prepare command. The graph is generated when XtraBackup applies the incremental backup to a full backup directory. Incremental backup directory that has 20,608 .delta files, each of which is 2.5 MB.

With --parallel=1

With --parallel=1, max Disk IOPs utilized is 18.2 K, and the XtraBackup prepare operation finished in 3.76 minutes.

With --parallel=64

With --parallel=64, the max Disk Write IOPs utilized is 85K, and the XtraBackup prepare operation finished in around a minute. XtraBackup utilized 4.67x more disk IOPS and finished 3.49x faster.

Results from the bug reporter

We saw some amazing results shared by the reporter on PXB-3427.  The time required for XtraBackup prepare command (--prepare --apply-log-only)  to complete, reduced from 237 minutes to just 6 minutes. That’s an incredible 40X speed-up!

Here are the details from their setup:

• Full backup: 235,188 *.ibd files
• Incremental backup: 236,214 *.ibd.delta files
• Average .delta size: 53,041 bytes (~53KB)
• Threads used: 48 (–parallel=48)
• Disk specs: 25K IOPS performance and an average of 500 to 600 MB/s of throughput

We hear you! This specific feature came to us from a post on the community forum. We reached out, asked them to create a JIRA ticket, and then implemented the improvement. We wanted to share this story as a demonstration of our commitment to listening to and acting on community feedback!

The post XtraBackup incremental prepare phase is 2x-3x faster! appeared first on Percona.

We want to briefly share Percona’s position on the news.

We welcome this

Orchestrator became the de facto standard for MySQL topology management and automated failover, and it has been a foundational tool in the ecosystem for over a decade. When the upstream project was archived, many operators were left running internal forks. A revived project under active development, with a stated roadmap and continued Apache 2.0 licensing, is good news for the MySQL community, and we’re glad to see ProxySQL step up to take it on. Thanks are due to Shlomi Noach for creating Orchestrator in the first place, and to everyone who contributed to it over the years.

A small clarification on Percona’s role

The ProxySQL announcement kindly credited Percona alongside GitHub for “stewardship over the years.” To be accurate: Percona has never been a maintainer of the upstream Orchestrator project. What we have done, and will continue to do, is support our customers who rely on it. That includes operational guidance, troubleshooting, and carrying internal patches where a customer situation requires it. The upstream project itself has always lived with Shlomi and later with the team at GitHub.

Nothing changes for Percona customers

If you are a Percona customer running Orchestrator today, your support experience is unchanged. We will continue helping you operate it in production, diagnose issues, and plan around its role in your high-availability stack. That commitment is steady regardless of where the upstream project lives.

Orchestrator’s maintenance also matters to us beyond support engagements. Percona Operator for MySQL uses Orchestrator to manage asynchronous topologies, so our own product depends on the project staying healthy. That’s part of why we plan to coordinate closely with the ProxySQL team as the next chapter unfolds.

Coordinating with the ProxySQL team

We plan to open coordination conversations with the ProxySQL team to make sure that operators running Orchestrator today, including our customers, have a smooth path as the project evolves. We wish the ProxySQL team well in this next chapter and look forward to supporting the community alongside them.

If you’re a Percona customer, reach out to your account team with any questions about your Orchestrator deployment. If you’re running Orchestrator outside of a Percona engagement and want to talk through support options, get in touch with our MySQL team.

The post Orchestrator’s Next Chapter: What It Means for Percona Customers appeared first on Percona.