-
MySQL Community Spotlight - Omar Farhat
Omar shares how he got started with MySQL, his experience at the contributor summit, and his enthusiasm for replication performance, extensibility, and data interoperability.
-
Heavy MySQL Reports in Seconds: Open Source HTAP with DBTrail and Percona Server for MySQL
In Part 1 we ran analytical reports in DuckDB on a DBTrail copy instead of on MySQL. This time I want to look at a different question: what if the application does not need to know the copy exists?
Most MySQL servers have a few queries that make someone wait. The monthly report that aggregates a whole table. Page 5,000 of a sorted list. The screen that reads years of history. MySQL is built for many small reads and writes, and these queries are a different kind of work.
So what happens to them with DBTrail next to the server? The same 14 reports ran for a day on a Percona Server for MySQL replica and on the copy of the same database that DBTrail keeps.
Short answer: twelve were faster on the copy, and the slowest went from 225 seconds to 7.94 seconds.
Not everything got faster, though. The two lightest reports were faster on Percona Server: one takes 4.0 ms there and 103 ms on the copy. Both sides ran the same SQL.
Full disclosure: I did not run this benchmark myself. It was run and published by the DBTrail project (routing guide at dbtrail.com/docs): 14 reports, an average of 317 runs each, on a 200-warehouse order-entry database kept under constant writes by HammerDB, with 4 CPUs on each side. Each engine was timed answering the reports by itself. The copy ran one to five minutes behind the source; the replica had no lag. Over 4,436 paired rounds the copy was 6.5x faster (geometric mean).
The 225-second report
The slowest of the 14 is the kind of query every reporting screen ends up with sooner or later. It adds up every order line and every stock row and joins them by item:WITH demand AS (
SELECT ol_i_id AS i_id, SUM(ol_quantity) AS units, COUNT(*) AS order_lines,
SUM(CASE WHEN ol_delivery_d IS NULL THEN ol_quantity ELSE 0 END) AS pending_units
FROM order_line
WHERE ol_quantity >= 1
GROUP BY ol_i_id
), supply AS (
SELECT s_i_id AS i_id, SUM(s_quantity) AS on_hand, SUM(s_ytd) AS shipped_ytd,
SUM(CASE WHEN s_data LIKE '%ORIGINAL%' THEN 1 ELSE 0 END) AS brand_warehouses
FROM stock
GROUP BY s_i_id
)
SELECT i.i_id, i.i_name, i.i_price,
d.units, d.order_lines, d.pending_units,
s.on_hand, s.shipped_ytd, s.brand_warehouses,
ROUND(1.0 * s.on_hand / d.units, 3) AS cover_ratio
FROM demand d
JOIN supply s ON s.i_id = d.i_id
JOIN item i ON i.i_id = d.i_id
ORDER BY cover_ratio, d.units DESC, i.i_id
LIMIT 20This is Q14 of tplytics, the report set used in the benchmark. The minimum quantity (1 or 5) and the limit (20 or 50) change from run to run.
No index will fix this one, because the query has to read both tables from end to end. On the replica that takes 225 seconds. On the copy, where the data is stored by column and scanned in parallel, it takes 7.94 seconds.
HTAP with two open source pieces
HTAP, hybrid transactional/analytical processing, means the application’s small transactions and the heavy analytical queries are served side by side. The reports that read this database do not need a warehouse loaded for them. In practice the heavy ones are reports, deep pagination, and queries over years of history.
The basic idea is simple. It takes two open source pieces. Percona Server keeps answering the transactions. DBTrail, an independent project under Apache 2.0, follows the server the way a replica does and answers the heavy queries from its own columnar copy.
I show it here on Percona Server, but it works the same way with MySQL from Oracle, including RDS and Aurora, and with MariaDB.
What the application sees
To the application, DBTrail is just a MySQL server. It connects to DBTrail’s port over the MySQL protocol, with the driver it already uses and the SQL it already sends. No query is rewritten and no query is tagged.$ mysql -h -P 3309 -u shop -p shopFor most SELECTs, DBTrail first asks the server for its plan, the same way EXPLAIN does, and the plan decides who answers:
Statement
Answered by
Why
SELECT * FROM orders WHERE id = 42
Percona Server
The plan finds a few rows through an index. The answer is live, to the last write.
SELECT status, COUNT(*) FROM orders GROUP BY status
DBTrail’s copy
The plan has a cost of 10,000 or more, or reads a whole table of 100,000 rows or more.
A heavy SELECT with GROUP_CONCAT, DATE_FORMAT or a JSON function
Percona Server
The copy would answer it differently, or cannot. A correct answer comes before a fast one.
Any SELECT inside a transaction
Percona Server
A read after your own write has to see that write.
A SELECT that returns more than 10,000 rows
Percona Server
The copy answers reports, not exports.
The full rules are in the routing guide. What I like here is that nobody maintains a list of queries. A query that is light today and heavy next year goes to the copy when its plan changes.
Column names in the result are the server’s, whoever answers, so code that reads a result by column name keeps working. And before you move an application, the sql-compare command runs its queries on both sides and lists the ones that would answer differently.
DBTrail’s web console counts who answered what, so you can see the split for your own workload:
Writes and reads on one connection
DBTrail’s port only reads. If your application uses one connection for everything, put ProxySQL in front. Writes, transactions and locking reads go to Percona Server; every other SELECT goes to DBTrail, which then decides between the server and the copy as above.
In ProxySQL that is two servers and two rules, with the application’s user defaulting to hostgroup 10:INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES
(10, 'percona-server-host', 3306),
(20, 'dbtrail-host', 3309);
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES
(1, 1, '^SELECT .* FOR (UPDATE|SHARE)', 10, 1),
(2, 1, '^SELECT ', 20, 1);The application keeps its host, its port and its login.
But does the application actually feel the difference? This setup was measured with a MariaDB source, an application at a fixed 400 transactions per second, and three reports running in a loop. It is the only run that measures the application and the reports together, so for me it is the most interesting one:
Where the reports ran
Application latency, p95
Reports finished in 5 minutes
No reports running
13.7 ms
0
On the database
6,960 ms
12
Through DBTrail
13.2 ms
266
MariaDB 10.11, ProxySQL 3.1, sysbench. The copy was 0.9 to 3.0 seconds behind. Benchmark by the DBTrail project.
With the reports on the database, p95 latency went from 13.7 ms to 6,960 ms. Through DBTrail it stayed at 13.2 ms, and 22 times more reports finished.
One of those three reports was a page five million rows deep, ORDER BY k LIMIT 20 OFFSET 5000000. It took 92 seconds on the database and 0.7 seconds through DBTrail.
What it takes to run
Part
What it needs
Application
Point its reads at another host and port. Same driver, same SQL, no code to rewrite.
Database
Nothing is installed in it. It needs the binary log in ROW format with full row images, a primary key on every InnoDB table, and two users.
Hardware
One machine next to the database. In the 14-report benchmark it had 4 CPUs, the same as the replica. The copy is columnar files on local disk or in S3.
Cost
Apache 2.0, with no license fee. For these queries there is no warehouse to load and no pipeline to maintain.
The quick start is five steps on a machine with Docker Compose:curl -fsSL https://raw.githubusercontent.com/dbtrail/dbtrail/main/install.sh | sh
Install with the line above. It opens a web form.
Fill in the host and port of your server. The form shows the SQL that creates DBTrail’s user.
Take the first copy. It reads every table once, with a short lock, so pick a quiet time. Then turn on “Update the copy”.
Turn on the MySQL port.
Connect with your mysql client and run your slowest report.
Routing between the server and the copy is one more user and one switch, described in the routing guide.
Try it on your own report
These are DBTrail’s numbers on DBTrail’s workload. The only benchmark that really matters is your own. Put DBTrail on a separate machine, point it at a replica, and run your slowest report on both.
The documentation lists MySQL and Percona Server for MySQL 8.0 and 8.4, and MariaDB 10.11 and later. Install steps and the routing guide are at dbtrail.com/docs, and the source is at github.com/dbtrail/dbtrail.
If you try it, I am looking for your feedback: which of your queries went to the copy, and how did they do?
The post Heavy MySQL Reports in Seconds: Open Source HTAP with DBTrail and Percona Server for MySQL appeared first on Percona.
-
Diagnosing MySQL at Scale (Part 2): Storage Subsystems, Buffer Pool Latches, and Memory Spills
In transactional database systems, the latency gap between memory access (tens to hundreds of nanoseconds) and persistent storage access (tens to hundreds of microseconds for local NVMe devices and milliseconds for remote cloud block storage such as Amazon EBS) spans multiple orders of magnitude. Once a workload exhausts available memory and begins interacting heavily with storage, query response times can deteriorate rapidly.
However, storage-related performance problems in MySQL are rarely solved by simply adding IOPS. In many production incidents, latency originates within the database engine itself:
Redo-log checkpoint advancement falling behind incoming write velocity, forcing foreground transactions to participate in synchronous flushing activity ("sync flush").
Doublewrite buffering increasing write amplification, contributing to storage-pressure events and elevated flush queue depth.
Binary log durability synchronization (fsync) serializing transaction commits, creating commit-pipeline bottlenecks under sustained write throughput.
Buffer pool synchronization primitives and latch contention, causing worker-thread serialization during periods of intense page churn, eviction pressure, and cache turnover.
Analytical workloads spilling large temporary datasets and filesort merge passes to disk, converting microsecond-scale in-memory operations into high-latency storage activity.
Undo tablespace growth and MVCC purge lag, driven by long-running transactions, abandoned read views, and purge workers falling behind transactional churn.
This article explores the diagnosis and remediation of storage and memory contention patterns in MySQL 8.4 LTS, including wait-event analysis, checkpoint behavior, buffer-pool contention, temporary-table spills, purge worker lag, and transaction-commit bottlenecks.
Operational Disclaimer: The diagnostic queries, Performance Schema instrumentation configurations, engine parameter recommendations, stored routines, and architectural remediations presented across this series are provided for investigative engineering analysis and technical reference. Contention characteristics, wait-event profiles, and optimal parameter thresholds vary significantly depending on transactional access patterns, data volume, schema design, storage subsystem throughput, replication topology, cloud virtualization constraints (e.g., AWS Nitro, EBS bandwidth limits, Aurora multi-tenant architectures), and specific MySQL engine release baselines. Administrative overrides, dynamic parameter modifications, session terminations (KILL), and schema modifications should never be executed blindly against production clusters. All queries, DDL statements, and configuration changes must be reviewed, benchmarked, and validated in representative staging environments through established change-management procedures prior to production implementation.
1. Storage & I/O Subsystem Bottlenecks: Taxonomy
Wait Event
Production Root Cause & Impact
Recommended Engineering Remediation
wait/io/file/innodb/innodb_redo_log
Transaction commits stall during sustained write bursts because redo-log generation outpaces checkpoint advancement and flushing activity, forcing foreground transactions to participate in synchronous flushing.
Batch write operations into larger transactions where appropriate; increase innodb_log_buffer_size (commonly 64M–128M for write-intensive workloads); in MySQL 8.4 LTS, size innodb_redo_log_capacity appropriately to absorb sustained write bursts while maintaining healthy checkpoint-age progression.
wait/io/file/innodb/innodb_doublewrite_file
Doublewrite buffering increases write amplification and may become a storage bottleneck during aggressive dirty-page flushing.
Self-managed MySQL: tune innodb_doublewrite_files and evaluate storage throughput. AWS RDS for MySQL: consider RDS Optimized Writes where supported. Aurora MySQL: Aurora uses a different storage architecture and does not rely on the traditional InnoDB doublewrite mechanism.
wait/io/file/innodb/innodb_data_file
Synchronous page reads occur when required data is absent from the buffer pool, resulting in storage-access latency and throughput degradation.
Increase innodb_buffer_pool_size where memory permits; target a sustained buffer-pool hit ratio near 99%+ for OLTP workloads; add appropriate composite and covering indexes to eliminate unnecessary table scans and random I/O.
wait/io/file/sql/binlog
Binary log durability synchronization (fsync) and commit-order preservation limit transaction throughput under heavy write concurrency.
Do not disable sync_binlog solely for performance reasons in production. First evaluate binlog cache spillover, increase binlog_cache_size where warranted, and verify Binlog Group Commit efficiency before considering durability trade-offs.
2. Redo Log Checkpoint Saturation (innodb_redo_log)
Contention on wait/io/file/innodb/innodb_redo_log occurs when transactional workloads generate redo records faster than InnoDB can advance checkpoints and flush dirty pages. As redo generation outpaces checkpoint progression, foreground transactions increasingly experience delays associated with redo-log pressure and flushing activity.
Official Documentation: MySQL 8.4 Reference Manual: InnoDB Redo Log.
Production Failure Scenario: Checkpoint Pressure Under Sustained Write Load
Under sustained write concurrency, the gap between the current Log Sequence Number (LSN) and the most recent checkpoint grows continuously. This gap, commonly referred to as Checkpoint Age, represents the volume of uncheckpointed redo that must be retained for crash recovery.
As Checkpoint Age grows, InnoDB progressively increases background flushing pressure through adaptive flushing mechanisms. If dirty-page generation continues to outpace checkpoint advancement, foreground transactions may begin participating in flushing activity, increasing transaction latency and producing elevated waits on wait/io/file/innodb/innodb_redo_log.
Unlike traditional storage bottlenecks, the underlying issue is often not raw IOPS capacity alone, but the inability of checkpoint advancement and page flushing to keep pace with incoming write velocity.
Diagnostic Commands & Calculation
1. Inspect Current LSN and Checkpoint Position:
SHOW ENGINE INNODB STATUS\G
-- Locate the LOG section:
-- Log sequence number 214748364800
-- Log flushed up to 214748364750
-- Pages flushed up to 214648364800
-- Last checkpoint at 213248364800
2. Calculate Checkpoint Age:
-- Checkpoint Age = (Log sequence number - Last checkpoint at)
-- 214748364800 - 213248364800 = 1,500,000,000 bytes (~1.4 GB)
-- Compare this value against current redo capacity:
SHOW GLOBAL VARIABLES LIKE 'innodb_redo_log_capacity';
A steadily increasing Checkpoint Age, combined with elevated redo-log waits and aggressive flushing activity, is a strong indicator that checkpoint advancement is struggling to keep pace with redo generation.
3. Monitor Dirty Page Pressure:
-- Compute dirty-page and data-page metrics in the buffer pool
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN ('Innodb_buffer_pool_pages_dirty', 'Innodb_buffer_pool_pages_data');
A persistently high dirty-page ratio accompanied by growing redo-log waits indicates checkpoint pressure and insufficient flushing throughput.
Remediation & Engine Configuration
Dynamically Expand Redo Capacity (MySQL 8.4 LTS):
In MySQL 8.4 LTS, innodb_redo_log_capacity is dynamic and can be resized online without requiring a server restart:
-- Expand redo capacity to better absorb sustained write bursts
SET GLOBAL innodb_redo_log_capacity = 8589934592; -- 8 GB
SHOW GLOBAL VARIABLES LIKE 'innodb_redo_log_capacity';
Increasing redo capacity provides additional recovery headroom and reduces the likelihood of checkpoint-age pressure during large write bursts.
Expand the In-Memory Log Buffer:
For write-intensive workloads, scale innodb_log_buffer_size (typical production ranges: 64 MB–128 MB). This reduces contention and allocation pressure associated with large transactions and bursty write workloads.
Verify Storage Throughput and Latency:
Redo writes ultimately depend on sequential storage durability operations (write() and fsync()). Validate that underlying storage can sustain expected throughput:
Local NVMe SSDs, AWS EBS io2, or provisioned AWS EBS gp3.
Monitor write latency, queue depth, flush latency spikes, and storage throughput saturation.
Reduce Redo Generation Where Possible:
Reducing redo generation is often more cost-effective than continuously scaling storage throughput:
Batch DML operations into larger deterministic transactions.
Eliminate unused or redundant secondary indexes that amplify log records.
Reduce write amplification from frequent row updates.
Optimize bulk-ingestion and ETL workflows using chunked staging.
Key Operational Takeaway: wait/io/file/innodb/innodb_redo_log is rarely the result of a simple disk-performance problem. In most production incidents, the root cause is an imbalance between redo generation, checkpoint advancement, and page-flushing capacity. When write velocity persistently exceeds flushing throughput, InnoDB increases flushing pressure and foreground transaction latency rises. The most effective remedies are usually a combination of appropriately sized redo capacity, healthy checkpoint progression, efficient flushing, and controlled redo generation rates.
3. Doublewrite Buffer & Hardware Torn-Write Prevention
The wait event wait/io/file/innodb/innodb_doublewrite_file is associated with dirty-page flushing activity within InnoDB's doublewrite subsystem. Elevated waits typically indicate sustained flushing pressure, significant dirty-page accumulation, or storage throughput limitations during checkpoint advancement and buffer-pool page eviction.
Official Documentation: MySQL 8.4 Reference Manual: InnoDB Doublewrite Buffer.
Production Mechanics & Write Amplification
InnoDB stores data in 16 KiB pages, while underlying operating systems and storage devices typically perform physical writes at 4 KiB sector boundaries. If a power loss, kernel crash, or hardware failure occurs mid-write, only part of the page may reach persistent storage, producing a torn page and catastrophic tablespace corruption.
To protect against this failure mode, InnoDB first writes dirty pages sequentially into the doublewrite buffer, performs the required durability operations (fsync), and then writes those pages to their final tablespace locations. During crash recovery, InnoDB reads the doublewrite buffer to repair partially written pages before normal recovery proceeds.
Because every flushed dirty page is written twice, this mechanism introduces a 2× write amplification factor. Under heavy checkpoint pressure or aggressive dirty-page flushing, elevated activity within the doublewrite subsystem becomes a primary bottleneck, visible via wait/io/file/innodb/innodb_doublewrite_file.
Production Optimization Standards
1. AWS RDS for MySQL: RDS Optimized Writes
On supported AWS Nitro-based RDS instance classes (such as Graviton db.r6g, db.r7g and Intel/AMD db.r6i, db.m6i), AWS provides RDS Optimized Writes:
# Applied via AWS RDS Parameter Group (requires instance reboot)
rds.optimized_writes = 1
Hardware Mechanism: Nitro SSD controllers feature hardware-enforced Torn-Write Prevention (TWP), guaranteeing atomic 16 KiB page writes at the hardware controller level.
Operational Result: MySQL automatically sets innodb_doublewrite = 0, eliminating doublewrite overhead, halving write I/O amplification, and eliminating wait/io/file/innodb/innodb_doublewrite_file entirely without compromising ACID crash recovery.
Note: Availability depends on engine version, storage type, and instance class. Always verify support in the AWS RDS documentation before enabling.
2. Aurora MySQL Architecture Note
Aurora MySQL uses a fundamentally different storage architecture than standard InnoDB deployments:
Instead of relying on traditional InnoDB doublewrite buffers and local data files, Aurora persists redo records directly into a distributed, six-way replicated storage fleet ("the log is the database") and reconstructs data pages in the storage layer.
Consequently, Aurora does not rely on the traditional InnoDB doublewrite mechanism, RDS Optimized Writes does not apply to Aurora, and wait/io/file/innodb/innodb_doublewrite_file analysis is not relevant in Aurora environments.
3. Self-Managed MySQL on Dedicated NVMe Storage
For self-managed cloud or bare-metal servers running MySQL 8.4 LTS, scale doublewrite worker concurrency across dedicated parallel files:
[mysqld]
# Parallelize doublewrite flushing across multiple files
innodb_doublewrite_files = 4
innodb_doublewrite_pages = 128
Benchmark optimal values based on:
NVMe storage throughput and provisioned IOPS
Storage queue depth
Buffer pool size and dirty-page generation velocity
Checkpoint workload characteristics
Diagnostic Indicators
Investigate the doublewrite subsystem when you observe wait/io/file/innodb/innodb_doublewrite_file together with:
Elevated dirty-page counts (Innodb_buffer_pool_pages_dirty)
Aggressive checkpoint activity and rising Checkpoint Age
Increased page-flush rates (Innodb_buffer_pool_pages_flushed)
Storage write throughput approaching saturation or high queue depths
Sustained buffer-pool eviction pressure
Key Operational Takeaway: wait/io/file/innodb/innodb_doublewrite_file is rarely the primary bottleneck. In most production incidents, it is a downstream symptom reflecting excessive page-flushing pressure driven by checkpoint advancement, dirty-page accumulation, or storage-layer saturation. The correct remediation is usually to address the underlying flushing workload and checkpoint progression rather than focusing exclusively on the doublewrite subsystem itself.
4. Data File I/O & Working-Set Cache Misses
The wait event wait/io/file/innodb/innodb_data_file is commonly associated with synchronous page reads that occur when required data pages are not present in the InnoDB buffer pool. In these situations, foreground threads must wait while the storage subsystem retrieves the necessary 16 KiB pages from disk before query execution can proceed.
Official Documentation:
MySQL 8.4 Reference Manual: InnoDB Buffer Pool
MySQL 8.4 Reference Manual: Performance Schema File I/O Instrumentation
Production Failure Scenario: Working-Set Exceeds Available Memory
Under sustained pressure, the entire workload can become storage-bound and IOPS-constrained even though database CPU utilization remains deceptively low.
Diagnostic Commands & Hit-Ratio Calculation
1. Compute Global InnoDB Buffer Pool Hit Ratio:
SELECT ROUND(
100 * (1 - (bp_reads.variable_value / bp_requests.variable_value)),
4
) AS hit_ratio_pct
FROM performance_schema.global_status bp_reads
JOIN performance_schema.global_status bp_requests
ON bp_reads.variable_name = 'Innodb_buffer_pool_reads'
AND bp_requests.variable_name = 'Innodb_buffer_pool_read_requests';
Operational Hit Ratio Standards:
> 99% — Standard target for high-concurrency OLTP workloads.
95% – 99% — Well-cached, optimal production workload.
< 95% — Investigate working-set sizing, cold cache sweeps, or unindexed queries.
Contextual Interpretation: The hit ratio should always be evaluated alongside storage latency and access patterns. Lower hit ratios are typical and expected for reporting, DSS, analytical range scans, or batch ETL workloads where scanning full tables is required.
2. Identify Queries Driving Physical Disk Reads:
SELECT DIGEST_TEXT,
COUNT_STAR AS exec_count,
SUM_NO_INDEX_USED AS scans_without_index,
SUM_ROWS_EXAMINED,
ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_latency_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 5;
Look for:
Full table scans cycling cold pages through the LRU list
Wide range scans examining large fractions of large tables
Missing composite indexes causing secondary index to primary table lookups
Disproportionate rows examined versus rows returned
Remediation Standards
Size the Buffer Pool to the Active Working Set:
Configure innodb_buffer_pool_size so the primary transactional dataset remains resident in memory. On dedicated database hosts, a standard guideline is 70–80% of available physical RAM, balanced against:
Thread stack and connection memory (max_connections × thread buffers)
Internal temporary table memory limits (temptable_max_ram)
Replication worker buffers
Host operating system and kernel page cache requirements
Local backup utilities and monitoring agents
Eliminate Full Scans Using Composite Covering Indexes:
Problem Query:
SELECT order_id, total
FROM customer_orders
WHERE customer_id = 918 AND status = 'SHIPPED';
Without an optimal index, MySQL performs table or range scans touching millions of disk pages.
Remediation:
ALTER TABLE customer_orders
ADD INDEX idx_cust_status_covered (customer_id, status, order_id, total);
A composite covering index satisfies both filtering and projection directly out of the index B-tree, eliminating physical clustered index page reads.
Validate Buffer Pool Eviction Pressure:
Inspect page turnover and eviction velocity in InnoDB status:
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests%';
SHOW ENGINE INNODB STATUS\G
Under the BUFFER POOL AND MEMORY section, inspect:
Pages read per second vs. Pages created per second
Evicted without access rate (high values indicate LRU thrashing)
Read-ahead efficiency (low hit rates indicate unhelpful prefetching)
Key Operational Takeaway: wait/io/file/innodb/innodb_data_file is usually not a storage problem first; it is primarily a memory-efficiency and indexing problem. In most production incidents, elevated data-file waits indicate that the active working set no longer fits within the buffer pool, forcing foreground threads to perform synchronous disk reads. The most effective remediation is a combination of proper buffer-pool sizing, covering indexes, and query refactoring rather than simply provisioning additional IOPS.
5. Binlog Wait Diagnostic Workflow
When wait/io/file/sql/binlog dominates the wait profile, setting sync_binlog = 0 is often treated as a quick operational shortcut. In reality, this introduces severe durability and consistency risks by delegating fsync() timing entirely to the operating system page cache.
If an operating system crash, kernel panic, or power failure occurs before pending binary log writes are flushed to disk, transactions acknowledged as committed can be permanently lost. Furthermore, replicas may never receive those transactions—or may have already received transactions that are subsequently lost on the source after reboot—producing severe replication divergence (split-brain) and complicating disaster recovery.
Before modifying durability settings, engineers should execute this three-step diagnostic triage workflow.
Official Documentation: MySQL 8.4 Reference Manual: Binary Log Group Commit.
Step 1: Check Binlog Cache Spillage
SHOW GLOBAL STATUS LIKE 'Binlog_cache%';
Status Variable
Production Meaning
Binlog_cache_use
Total number of transactions that utilized the thread-local binlog cache.
Binlog_cache_disk_use
Transactions that exceeded binlog_cache_size and spilled into temporary disk files.
Calculate the Disk Spillage Ratio:
Disk Spillage Ratio (%) = [Binlog_cache_disk_use / (Binlog_cache_use + Binlog_cache_disk_use)] × 100
A persistently elevated spillage ratio indicates that large transactions are exceeding available thread memory, generating temporary-file disk I/O before the binary-log synchronization phase begins.
Operational Guideline: Investigate spillage ratios exceeding 1–5%, particularly when accompanied by elevated transactional commit latency or high waits on wait/io/file/sql/binlog.
Step 2: Evaluate Cache Sizing vs. Concurrency
SHOW GLOBAL VARIABLES LIKE 'binlog_cache_size';
SHOW GLOBAL VARIABLES LIKE 'max_binlog_cache_size';
If Binlog_cache_disk_use is increasing rapidly while binlog_cache_size remains at the 32 KB default, consider scaling:
# Scale thread-local binlog cache to reduce disk spillage
binlog_cache_size = 256K .. 1M
Always validate available host memory first, as this buffer is allocated per active transactional thread:
Max Potential Cache Memory = max_connections × binlog_cache_size
Simultaneously audit application workloads for large, unbatched operations:
Multi-million-row UPDATE or DELETE statements
Bulk ETL and data ingestion jobs
Unbounded transactional loops
Chunking large transactions into deterministic batches (e.g., 1,000–5,000 rows) is far more effective and memory-safe than continuously inflating thread cache allocations.
Step 3: Optimize Binlog Group Commit (Maintain Full Durability)
SHOW GLOBAL VARIABLES LIKE 'binlog_group_commit_sync_delay';
SHOW GLOBAL VARIABLES LIKE 'binlog_group_commit_sync_no_delay_count';
Rather than compromising crash safety, leverage Binary Log Group Commit to batch concurrent transactional fsync operations into a single disk write:
[mysqld]
# Full ACID durability baseline
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
# Binary Log Group Commit batching
binlog_group_commit_sync_delay = 1000 # Pause up to 1000 microseconds (1 ms)
binlog_group_commit_sync_no_delay_count = 100 # Flush immediately if 100 transactions accumulate
Production Behavior:
binlog_group_commit_sync_delay = 1000 (1 ms): The commit-group leader thread pauses for up to 1 millisecond before issuing fsync(), allowing concurrent transactions to join the same commit group.
binlog_group_commit_sync_no_delay_count = 100: If 100 transactions accumulate before the 1 ms timer expires, the group flushes immediately to prevent latency degradation.
This configuration achieves write throughput comparable to sync_binlog = 0 under high-concurrency OLTP workloads while preserving 100% crash durability and preventing replication drift.
Key Operational Takeaway: wait/io/file/sql/binlog does not justify disabling durability settings. In production, the correct remediation sequence is to eliminate binlog-cache disk spillage, right-size binlog_cache_size, enforce deterministic DML chunking, and tune Binary Log Group Commit. Durability baselines (sync_binlog = 1 and innodb_flush_log_at_trx_commit = 1) should remain non-negotiable standards wherever transactional integrity and replication consistency are business requirements.
6. Memory Contention & Engine Overhead
Wait Event
Production Root Cause & Impact
Recommended Engineering Remediation
wait/synch/mutex/innodb/buf_pool_mutex
Concurrent sessions contend for synchronization protecting InnoDB buffer-pool structures while loading, flushing, evicting, or modifying cached data pages. Elevated waits are commonly associated with buffer-pool pressure, excessive page churn, storage misses, or cache-efficiency problems rather than mutex overhead alone.
Verify innodb_buffer_pool_size is sufficient for the active working set; investigate page-read, dirty-page, and eviction rates; eliminate unnecessary scans; validate storage latency; and reduce buffer-pool churn. MySQL 8.4 automatically manages buffer-pool parallelism and internal partitioning, significantly reducing historical buffer-pool mutex contention.
wait/io/file/sql/FILE_TMPFILE
Internal temporary tables, filesort merge passes, hash aggregation structures, or intermediate result sets exceed memory thresholds and spill to disk, converting memory operations into storage I/O.
Tune temptable_max_ram; review tmp_table_size and max_heap_table_size where applicable; optimize GROUP BY, ORDER BY, and large joins; increase sort_buffer_size cautiously after validating per-session memory impact; and add covering indexes that allow filtering, grouping, and ordering directly from index structures.
wait/synch/cond/innodb/purge_worker_cond
Purge workers are coordinating, waiting for work, or processing undo cleanup tasks. Elevated History List Length (HLL), growing undo tablespaces, and long-running transactions together indicate that purge activity is falling behind transactional churn.
Monitor HLL through INNODB_METRICS; identify long-running transactions and stale read views using information_schema.innodb_trx; investigate replication, backup, or analytical workloads holding snapshots open; and consider increasing innodb_purge_threads when purge consistently lags behind workload demand.
Operational Analysis Standard: Wait events should never be analyzed in isolation. For memory-related waits, always correlate Performance Schema telemetry with buffer-pool statistics, temporary-table metrics, undo history growth, storage latency, and workload characteristics before identifying a root cause. A wait event often reflects downstream symptoms rather than the originating bottleneck.
7. Deep-Dive: Buffer Pool Contention, Page Churn & Cache Pressure (buf_pool_*)
The wait family wait/synch/mutex/innodb/buf_pool_* represents contention within InnoDB buffer-pool management structures responsible for tracking, locating, loading, evicting, and flushing data pages. In modern MySQL 8.4 LTS environments, these waits are typically downstream symptoms of excessive page churn, cache inefficiency, or storage pressure rather than a standalone mutex bottleneck.
Official Documentation:
MySQL 8.4 Reference Manual: InnoDB Buffer Pool
MySQL 8.4 Reference Manual: Buffer Pool Sizing & Resize
Production Failure Scenario: Working Set Exceeds Memory
When the active transactional working set becomes larger than available buffer pool capacity, pages are evicted faster than they can be reused. Simultaneously, unindexed analytical scans, poorly selective range filters, or elevated storage latency create a continuous cycle of cold-page loading and hot-page eviction.
Common Production Symptoms:
Elevated waits on wait/synch/mutex/innodb/buf_pool_*
Steep increase in physical synchronous disk reads (Innodb_buffer_pool_reads)
Plummeting buffer pool cache efficiency (< 95%)
Increasing disk queue depth and storage latency
Aggressive page-flush velocity and free list exhaustion
Near-100% CPU utilization driven by mutex spin-locks despite falling transactional throughput
Diagnostic Commands & Investigation
1. Identify Buffer Pool Related Waits:
SELECT EVENT_NAME,
COUNT_STAR AS total_waits,
ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_wait_sec,
ROUND(AVG_TIMER_WAIT / 1e6, 2) AS avg_wait_us
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'wait/synch/%/innodb/buf_pool%'
ORDER BY SUM_TIMER_WAIT DESC;
Look for: Rapidly increasing wait counts, high cumulative wait duration, and lock thrashing across Page Hash Table latches and LRU list mutexes.
2. Inspect Buffer Pool Churn in InnoDB Status:
SHOW ENGINE INNODB STATUS\G
Under the BUFFER POOL AND MEMORY section, inspect:
Pages read/sec, created/sec, written/sec: Gauges active page turnover rate.
Evicted without access per second: A high rate is a definitive indicator of cold cache sweeps thrashing the LRU list.
Free buffers: Persistently low or zero values force foreground worker threads to perform synchronous on-demand page evictions.
Pending reads / Pending normal aio writes: Indicates whether storage queue depths are saturated.
3. Calculate Buffer Pool Hit Ratio:
SELECT ROUND(
100 * (1 - (bp_reads.variable_value / bp_requests.variable_value)),
4
) AS hit_ratio_pct
FROM performance_schema.global_status bp_reads
JOIN performance_schema.global_status bp_requests
ON bp_reads.variable_name = 'Innodb_buffer_pool_reads'
AND bp_requests.variable_name = 'Innodb_buffer_pool_read_requests';
> 99% — Optimal OLTP cache efficiency.
95% – 99% — Acceptable production baseline.
< 95% — Investigate working-set sizing, cold table scans, and memory allocation.
Remediation Standards
Size the Buffer Pool to the Active Working Set:
Configure innodb_buffer_pool_size so the primary hot dataset remains 100% memory-resident. For dedicated MySQL instances, allocate 70–80% of physical RAM, after deducting:
Thread memory allocations (max_connections × session buffers)
Internal temporary table buffers (temptable_max_ram)
Replication worker queues
Host operating system and filesystem page cache requirements
Local backup utilities and telemetry daemons
Eliminate Unnecessary Page Churn:
Audit and optimize queries driving cold-page sweeps through the buffer pool:
Full table scans on multi-gigabyte historical tables
Inefficient join plans cycling outer rows
Missing composite indexes causing unindexed range scans
Low-selectivity index predicates forcing widespread index-to-clustered-table lookups
Buffer Pool Instances in MySQL 8.4 LTS:
In MySQL 8.4 LTS, innodb_buffer_pool_instances is automatically calculated by InnoDB based on logical CPU count (up to 64 instances when innodb_buffer_pool_size >= 1GB). Manual instance tuning is obsolete; focus engineering efforts on working-set sizing, index optimization, and storage latency reduction.
Key Operational Takeaway: Buffer-pool waits in MySQL 8.4 are usually downstream symptoms rather than root causes. When wait/synch/mutex/innodb/buf_pool_* dominates Performance Schema profiles, the underlying pathology is typically excessive page churn, insufficient memory residency, unindexed access patterns, or storage bottlenecks. The most effective remediation is improving cache efficiency and eliminating unnecessary physical I/O rather than attempting to tune mutex synchronization directly.
8. Deep-Dive: On-Disk Temp Table Spills (FILE_TMPFILE)
The wait event wait/io/file/sql/FILE_TMPFILE occurs when MySQL materializes internal temporary tables, filesort merge runs, hash aggregation structures, window-function state, or intermediate result sets onto storage because in-memory thresholds have been exceeded.
Official Documentation: MySQL 8.4 Reference Manual: Internal Temporary Tables.
Production Failure Scenario: Large Grouping, Sorting & Intermediate Result Materialization
Complex relational queries involving:
GROUP BY and DISTINCT aggregations
ORDER BY sorting operations
Window Functions (OVER (...))
Large multi-table hash joins
Derived tables and Common Table Expressions (CTEs)
frequently require MySQL to construct intermediate working sets. When those working sets exceed available in-memory thresholds, MySQL spills them to disk:
A workload that normally completes using microsecond-scale memory operations can rapidly become storage-bound and IOPS-constrained once repeated temp-table and filesort spills begin occurring.
Representative Execution Plan Pattern
EXPLAIN ANALYZE
SELECT customer_id,
memo,
SUM(amount)
FROM transaction_ledger
GROUP BY customer_id, memo
ORDER BY SUM(amount) DESC;
Execution plans displaying Using temporary; Using filesort do not automatically indicate a production emergency; however, they frequently correlate with severe FILE_TMPFILE waits when the intermediate row count or projected byte width exceeds in-memory buffers.
Diagnostic Commands & Spill Ratio Analysis
1. Monitor Temporary-Table Spill Rates:
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
Status Variable
Production Meaning
Created_tmp_tables
Total internal temporary tables created by MySQL sessions.
Created_tmp_disk_tables
Internal temporary tables that exceeded in-memory thresholds and spilled to disk.
Calculate the Disk Spill Ratio:
Disk Spill Ratio (%) = (Created_tmp_disk_tables / Created_tmp_tables) × 100
Operational Guidance:
< 5% — Excellent in-memory caching efficiency.
5% – 20% — Acceptable range; worth periodic monitoring during peak hours.
> 20% — Investigate query design, result-set row widths, and memory allocations.
Contextual Reality: A high spill ratio is not inherently a configuration bug for dedicated analytics, reporting, batch ETL, or data warehouse replicas, where scanning multi-gigabyte datasets legitimately requires storage-backed materialization.
2. Identify Filesort and Spill Contributors:
SELECT DIGEST_TEXT,
COUNT_STAR AS exec_count,
SUM_SORT_MERGE_PASSES,
SUM_SORT_ROWS,
ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_wait_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_SORT_MERGE_PASSES > 0
ORDER BY SUM_SORT_MERGE_PASSES DESC
LIMIT 5;
Look for: Repeated multi-pass sort merges (SUM_SORT_MERGE_PASSES > 0), massive row sort volumes, and expensive analytical statements. Multiple merge passes indicate that intermediate sort chunks could not fit within sort_buffer_size, requiring external merge sort runs on disk.
Remediation & Engine Configuration
Tune the TempTable Engine Memory Budget (MySQL 8.4 LTS):
In MySQL 8.4 LTS, temptable_max_ram governs how much memory the TempTable engine can consume before materializing temporary tables to disk:
-- Dynamically expand TempTable in-memory limit
SET GLOBAL temptable_max_ram = 1073741824; -- 1 GiB
SHOW GLOBAL VARIABLES LIKE 'temptable_max_ram';
Caution: Increasing temptable_max_ram does not prevent spills entirely; it merely raises the ceiling. Validate available physical host memory, concurrent active connection volume, and replication buffer requirements before increasing globally.
Review Sort Memory Allocation Carefully:
The variable sort_buffer_size influences whether sorting completes in a single memory pass or requires multi-pass disk merges:
# Moderate increase for reporting-heavy workloads
sort_buffer_size = 2M .. 4M
Critical Memory Warning: sort_buffer_size is allocated per session per sort operation. Inflating this setting globally on servers with hundreds of concurrent connections can rapidly trigger out-of-memory (OOM) kernel terminations. Validate changes via session-level overrides or controlled staging benchmarks rather than setting large global values.
Reduce Intermediate Row Width Through Query Design:
Eliminate SELECT *: Only project columns required by downstream application consumers to minimize the memory footprint per row in the intermediate TempTable buffer.
Push Predicates Early: Apply WHERE filters before joins, derived tables, or window functions to reduce the volume of rows entering temporary buffers.
Stage Large Aggregations: Split massive multi-table analytical transformations into bounded, deterministic stages.
Covering and Ordering Indexes:
Creating composite indexes that match query grouping or ordering columns (e.g., (customer_id, transaction_date)) allows the B-tree to return records in pre-sorted order, avoiding both filesorts and temporary tables.
Important Constraint: Covering indexes cannot prevent temporary table materialization for expressions such as ORDER BY SUM(amount) or complex window aggregations, which inherently require computing intermediate aggregate values.
Key Operational Takeaway: wait/io/file/sql/FILE_TMPFILE is rarely a storage capacity problem. It is typically evidence that query execution requires more intermediate memory than is currently available or that unoptimized execution plans are materializing excessively wide working sets. The most effective remediation is improving query design, trimming projected row width, tuning TempTable memory thresholds, and adding composite ordering indexes rather than simply provisioning additional IOPS.
9. Deep-Dive: Purge Lag, Undo Growth & History List Length (HLL)
The wait event wait/synch/cond/innodb/purge_worker_cond represents synchronization activity among InnoDB purge worker threads responsible for removing obsolete Multi-Version Concurrency Control (MVCC) row versions from undo logs. By itself, the wait event is not evidence of an engine malfunction. However, when accompanied by a rapidly growing History List Length (HLL), expanding undo tablespaces, and long-running transactions, it indicates that purge processing is falling behind transactional churn.
Official Documentation: MySQL 8.4 Reference Manual: InnoDB Multi-Versioning.
Production Failure Scenario: The Abandoned Read-View Trap
Under MySQL's default REPEATABLE READ isolation level, InnoDB must preserve older row versions for any active transaction that still requires access to its original snapshot view.
A single abandoned or uncommitted transaction will prevent purge processing from reclaiming obsolete undo records across the entire database, regardless of which tables that transaction originally touched:
As the History List Length balloons, InnoDB must maintain increasingly large pointer chains of historical row versions. This produces:
Substantial CPU overhead as queries traverse lengthy undo chains looking for visible versions
Uncontrolled growth of undo tablespaces on disk
Significantly prolonged crash-recovery times during restart
Severe systemic query latency degradation
Diagnostic Commands & Investigation
1. Check Current History List Length (HLL):
SELECT NAME,
COUNT AS history_list_length
FROM information_schema.innodb_metrics
WHERE NAME = 'trx_rseg_history_len';
Operational Guidance:
< 10,000 — Typically healthy baseline.
10,000 – 100,000 — Monitor trend, growth rate, and write velocity.
100,000 – 1,000,000 — Investigate long-running transactions and abandoned read views.
> 1,000,000 — Critical purge backlog; immediate intervention required.
Trend Over Absolute Value: There is no universal "safe" absolute threshold. In high-throughput OLTP systems generating tens of thousands of DML statements per second, HLL may briefly rise during batch peaks. A steadily rising slope over hours is far more dangerous than a temporary spike.
2. Identify the Oldest Active Transaction:
SELECT trx_id,
trx_mysql_thread_id AS connection_id,
trx_state,
trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds,
trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started ASC
LIMIT 1;
Focus on:
Idle or sleeping connections (trx_state = 'RUNNING' with trx_query IS NULL in information_schema.processlist marked as Sleep)
Leaked connection-pool sessions
Long-running analytical reports
Abandoned database backup locks
3. Correlate Purge Progress in InnoDB Status:
SHOW ENGINE INNODB STATUS\G
Under the TRANSACTIONS section, inspect:
History list length <value>
Purge done for trx's n:o <trx_id> undo n:o <undo_id>
Number of active read views held open across all connected sessions
Remediation Standards
Eliminate the Root Blocking Transaction:
Identify the connection holding the oldest read view. Only after confirming that termination is operationally safe and will not violate application data integrity:
KILL <connection_id>;
Caution: Never terminate transactions blindly in production. Always validate the application identity, business context, and transaction duration before executing KILL.
Review Application Transaction Hygiene:
Audit connection pool frameworks (HikariCP, DBCP, c3p0) to ensure connections with uncommitted transactions are rolled back before returning to the pool.
Verify ORM configurations (e.g., Hibernate, SQLAlchemy, Prisma) to prevent autocommit-disabled connections from lingering in Sleep state.
Move long-running analytical queries to dedicated read replicas or use READ COMMITTED isolation if historical snapshot consistency is not required.
Scale Purge Threads When Appropriate:
For systems sustaining massive continuous DML write velocity, scale purge concurrency in my.cnf:
[mysqld]
# Scale purge concurrency on multi-core database servers
innodb_purge_threads = 4 # Typical 4-8 on dedicated multi-core hosts
Crucial Operational Prerequisite: Adjust innodb_purge_threads only after verifying that long-running transactions are not the root cause, and after verifying sufficient host CPU headroom exists.
Chunk Large DML Operations:
Avoid unconstrained multi-million-row DELETE or UPDATE operations:
-- DANGEROUS: Floods undo tablespaces and overwhelms purge workers
DELETE FROM audit_log WHERE created_at < NOW() - INTERVAL 2 YEAR;
Instead, enforce deterministic, bounded chunking executed iteratively:
-- RECOMMENDED: Bounded batch deletion allowing purge to pace smoothly
DELETE FROM audit_log
WHERE created_at < NOW() - INTERVAL 2 YEAR
LIMIT 10000;
Introduce brief sleep intervals between chunks if necessary to allow purge workers to keep pace with undo generation.
Key Operational Takeaway: wait/synch/cond/innodb/purge_worker_cond is not, by itself, evidence of a performance bottleneck. The true indicators of purge lag are a continuously growing History List Length, expanding undo tablespaces, and long-running transactions that prevent the removal of obsolete MVCC row versions. In most production incidents, the root cause is not insufficient purge-worker capacity, but an application session holding an old read view open far longer than intended.
Summary & What's Next
Storage and memory bottlenecks represent the physical layer of database performance. While sizing buffers properly is crucial, improper query design and uncommitted snapshots will overwhelm even the most capable NVMe storage arrays.
In Part 3: Network Transit, Connection Storms, and Replication Pipelines, the series moves to connection management and replication dynamics:
Thread cache mutex contention during microservice scaling events
Client socket I/O waits and slow consumer remediation via Keyset Pagination
Telemetry scrape storms (LOCK_status) and Prometheus exporter optimization
Multi-Threaded Replication (MTR) worker bottlenecks and missing Primary Keys
Distinguishing benign idle waits from genuine network delivery lag in replication receivers
Continue to Part 3: Network Transit, Connection Storms, and Replication Pipelines.7TKU66CE8V5W
-
Vector search in MySQL: from a plugin to the top of the Contributor Summit agenda
A plugin started the fire, the M-ecosystem picked it up, the hyperscalers went native, and now the benchmark wars have begun.
Not long ago, if you asked about vector search in MySQL, you got a polite smile and a link to pgvector. Even after MySQL 9 added a VECTOR column type, the obvious next questions had no answer. Where is the index? Where is the distance function? For a while the honest answer was “use something else.”
This week the MySQL Contributor Summit (Nov 4–5, online) opened its technical program with vector search. The first discussion block, AI & Cloud, had four vector and AI proposals back to back from Oracle, Google, Percona and Intel. It was the clearest sign yet of how far vector search in MySQL has come.
Looking back, vector search in MySQL has moved through four phases:
The spark: MyVector
Adoption: the M-ecosystem picks up the idea
Going native: the hyperscalers arrive
The benchmark wars: where we are now
Phase 1: The spark, MyVector
We didn’t want to wait for a server release, so I co-founded MyVector, which I now own. It gives MySQL HNSW indexing, distance functions and approximate nearest neighbor search, all open source.
MyVector started as a plugin on MySQL 8.0. It has since moved to MySQL’s component architecture, which is far more robust on recent MySQL releases, and it now supports MySQL Community Edition 8.4, 9.7 and 26.10.
We took it to FOSDEM and SCALE, and the conversation changed from “MySQL can’t do this” to “wait, MySQL can do this?” You can try it yourself at demo.myvector.online.
Phase 2: Adoption, the M-ecosystem picks it up
Once MyVector showed that vector search fits naturally inside a MySQL-compatible engine, the idea spread across the M-ecosystem. MariaDB, TiDB, VillageSQL and Percona all started adopting the idea in their own products and distributions.
Each took its own path, but they all headed the same way: embeddings belong next to the data, inside the database people already run.
Phase 3: Going native, the hyperscalers arrive
The third phase is native implementations, with contributions now coming from the hyperscalers and the biggest names in the ecosystem. You could see it at the summit, where the four AI & Cloud talks came from the upstream owner, a hyperscaler, a fork vendor and a chip maker:
SessionFromGitHubEvolving MySQL VECTOR Support: Native Indexing and Similarity SearchMayank Prasad, Kuba LopuszanskiOracleIssue #3Invoking AI models from MySQLJulia OffermanGoogleIssue #158DISTANCE() Functions for the VECTOR Data TypeCatalin BesleagaPerconaPR #860Bringing SVS’s Graph-Based Vector Search to MySQLKelly L. McKeighanIntelIssue #116AI & Cloud discussion block, MySQL Contributor Summit Fall 2026, followed by a design review with all attendees
All four brought proposals, bug reports and pull requests for the same feature.
Phase 4: The benchmark wars
With several implementations in play, the next step was inevitable: people started measuring in public and answering each other’s numbers.
Percona, Aug 27: Benchmarking vector indexes
Evgeniy Patlan set the ground rules with vector-bench, an open harness that runs each engine in its own pinned container on the same cores with the same data. Every measurement is a pair of throughput and recall, and recall is a setting, not a property of the engine.
ef_search 10 → 800 · recall 0.959 → 0.999 · 3,678 → 409 QPS
askdba, Oct 14: How fast is vector search inside MySQL?
I ran MyVector on MySQL against MariaDB Vector on 50,000 vectors. MariaDB’s first run came in at only 29 QPS. Instead of publishing that, I traced it to the 16 MB default HNSW cache, which could not hold the 27 MB index, and reran it.
MyVector ≈ 548 QPS @ recall 0.82 · MariaDB ≈ 257 QPS @ recall 0.95
lefred (MariaDB), Oct 16: Vector Search in MySQL vs MariaDB: Look Beyond QPS
lefred answered: two QPS figures at different recall are not apples to apples. He estimated MariaDB at matched recall, showed that a bigger cache cut index build from about 583 to 92 seconds, and argued the 16 MB default deserves a rethink. Fair points, made in good faith.
MariaDB est. ≈ 1,196 QPS @ recall 0.82 · build 583 s → 92 s
VillageSQL, Oct 22: Benchmarking vector indexes in MySQL with vector-dev-bench
Adam Levin, working with Percona, released vector-dev-bench, a laptop-scale companion that answers in minutes. It sweeps ef_search and measures what writers cost readers.
20k × 32-dim · recall 0.787 → 0.988 · 8,829 → 2,044 QPS · reads −40% with writers
Every round raised the bar. The community converged on pairing throughput with recall, exposing defaults and testing writes alongside reads. MyVector is being added to vector-bench as an engine, so anyone will be able to rerun the numbers. Results for MyVector and other engines are published side by side at bench.myvector.online. That is how an open-source ecosystem decides something matters.
The spark is now a fire.
Why this matters
We have seen this pattern before. JSON in MySQL went through the same steps: store it as text, then a native type, then functions, then indexes, and finally the default choice.
Vectors are moving through those steps much faster, because a plugin proved it was possible, the ecosystem adopted it, the hyperscalers are building it in, and the benchmarks keep everyone honest.
For teams running MySQL in production, the question is moving away from “which separate vector database?” and toward “can my embeddings live next to my transactional data, in the database my team already knows how to run?”
Join the conversation
The Contributor Summit is open and online. Proposals are filed as issues in the mysql/mysql-community GitHub repo, and the hallway track continues in the MySQL Community Slack in #contributor-summit-hallway-track. If you work on vector search in MySQL, bring your ideas, your pull requests and your benchmarks to the next one.
What’s next
Native indexing and similarity search proposals moving through upstream design review
Standard DISTANCE() functions replacing vendor-specific workarounds
MyVector continuing as a way to run vector search on MySQL Community Edition today, with open, reproducible benchmarks
We’re not done. Thanks to Heather VanCura and the MySQL Community team for running an open, design-review-style summit.
Links
myvector.online
bench.myvector.online
demo.myvector.online
github.com/askdba/myvector
-
MySQL Ecosystem Project Spotlight of the Week - ProxySQL
This week, we're highlighting ProxySQL. As applications grow, so do the demands on MySQL. ProxySQL helps developers and DBAs manage database traffic through connection pooling, query routing, and load balancing, providing greater control over how applications connect to MySQL.
|
