-
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.
-
Diagnosing MySQL at Scale (Part 1): Critical System Blocks, Metadata Locks, and Cluster Convoys
High-concurrency database workloads rarely fail because of simple CPU or disk capacity limits. Instead, performance collapses occur when transactions serialize on shared internal database resources: metadata locks, data dictionary latches, group communication certification queues, and network buffers.
When contention occurs, external monitoring dashboards often show deceptive metrics such as near-zero CPU and disk activity alongside a massive surge in active connections and application timeouts. MySQL exposes these internal contention points through Performance Schema wait events.
This article—the first in a four-part deep dive—covers the foundational mechanics of Performance Schema wait telemetry, the governance of instrumentation overhead in high-throughput environments, and the diagnosis of Critical System Blocks: Metadata Locks (MDL), Data Dictionary latch serialization, and Group Replication Flow-Control stalls.
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. Why Wait Events Matter in High Concurrency
In MySQL, every client query competes for finite shared resources. Rather than guessing whether a latency spike is driven by storage latency, table schema locks, or network transport, wait events quantify the exact time threads spend waiting:
Core Principle: Analyzing wait-event distributions and latency profiles transforms database troubleshooting from speculative trial-and-error into a deterministic diagnostic process.
Official documentation: MySQL 8.4 Reference Manual: Performance Schema Wait Event Tables.
2. Performance Schema Prerequisites & Overhead Governance
By default, MySQL 8.4 LTS enables performance_schema, but detailed wait-event visibility is intentionally limited. Although numerous Performance Schema instruments are active, many wait-related instruments and the wait-event consumers (events_waits_%) remain disabled out of the box to reduce instrumentation overhead. Consequently, Performance Schema may initially expose statement-level and transaction-level diagnostics while providing little or no wait-event data until the relevant instruments and consumers are explicitly enabled.
Instrumentation Enablement Matrix
Component
Default State
Operational Function
performance_schema
ON
Master telemetry engine toggle.
setup_instruments (wait/%)
SELECTIVELY ENABLED
Low-level probes embedded in server source code (mutexes, rwlocks, file I/O, socket I/O, metadata locking, and other synchronization primitives). Some wait instruments are enabled by default, others are disabled.
setup_consumers (events_waits_*)
DISABLED
In-memory aggregation tables and history buffers capturing wait-event telemetry. Wait-event consumers are disabled by default.
Measured Overhead: What Telemetry Costs
Not all Performance Schema instruments impose the same overhead. High-frequency synchronization instruments such as mutexes and condition variables may execute millions of times per second on heavily concurrent OLTP systems. When instrumentation is enabled with TIMED=YES, additional high-resolution timing operations are performed for each instrumented event, increasing CPU consumption relative to untimed collection. The overhead estimates below represent illustrative field-observed ranges and should be interpreted as directional guidance rather than deterministic performance guarantees.
Instrumentation Scope
Typical CPU Overhead*
Throughput Impact
Workload Context
Default Instruments (out-of-the-box)
1–3%
Negligible
Standard production baseline
Targeted (wait/io/%, wait/lock/%)
3–8%
Moderate
Recommended for production triage
All wait/synch/mutex with TIMED=YES
10–25%
High
Heavy concurrent OLTP workloads
Blanket wait/% with TIMED=YES
15–40%
Severe
High-concurrency throughput regression
Instruments ENABLED=YES, TIMED=NO
2–5%
Minimal
Frequency and contention counts only
* Performance Impact Disclaimer: The overhead percentages shown above are illustrative field-observed ranges derived from benchmarking exercises, vendor guidance, and production support experience across diverse environments. Actual impact may vary substantially depending on workload characteristics, transaction concurrency, CPU architecture, storage latency, enabled consumers, instrumentation scope, MySQL version, and cloud platform implementation details. These figures should be interpreted as directional guidance rather than deterministic performance guarantees.
Operational Guideline: The TIMED=YES attribute on high-frequency synchronization instruments (particularly mutexes and condition variables) is often the largest contributor to instrumentation overhead. When investigating contention frequency, hotspot identification, or lock distribution rather than precise latency measurements, consider using TIMED='NO' to reduce timing-related overhead while still collecting event counts and contention statistics.
Dynamic Runtime Activation (Diagnostic Session)
To enable wait instrumentation dynamically during an incident window without restarting the server:
-- Dynamic Runtime Activation (Diagnostic Session)
-- Enable targeted wait instrumentation during an incident window without requiring a MySQL restart.
-- 1. Enable targeted wait instruments
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES',
TIMED = 'YES'
WHERE NAME LIKE 'wait/io/%'
OR NAME LIKE 'wait/lock/%'
OR NAME LIKE 'wait/synch/mutex/innodb/%';
-- 2. Enable wait-event consumers
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME IN (
'events_waits_current',
'events_waits_history',
'events_waits_history_long'
);
-- 3. Verify enabled instrumentation
SELECT NAME,
ENABLED,
TIMED
FROM performance_schema.setup_instruments
WHERE NAME LIKE 'wait/%'
AND ENABLED = 'YES'
ORDER BY NAME
LIMIT 20;
-- 4. Verify enabled consumers
SELECT NAME,
ENABLED
FROM performance_schema.setup_consumers
WHERE NAME LIKE 'events_waits%';
Operational Note: Instrumentation changes affect only future wait events and do not retroactively populate historical data. Enable the required instruments and consumers, allow representative workload activity to execute, and then analyze wait-event telemetry.
Production Guidance: When investigating contention frequency rather than precise latency measurements, consider enabling instruments with TIMED='NO' first. This significantly reduces instrumentation overhead while still exposing contention hotspots and event frequency distributions.
Post-Investigation Cleanup Command
Always return the instance to safe telemetry baselines following an investigation:
-- Post-Investigation Cleanup
-- Revert any instrumentation that was enabled specifically for the investigation.
-- Ideally, restore the pre-investigation configuration captured before enabling additional instruments and consumers.
-- Example: Disable instrumentation enabled for the investigation
UPDATE performance_schema.setup_instruments
SET ENABLED = 'NO',
TIMED = 'NO'
WHERE NAME LIKE 'wait/io/%'
OR NAME LIKE 'wait/lock/%'
OR NAME LIKE 'wait/synch/mutex/innodb/%';
-- Disable wait-event consumers
UPDATE performance_schema.setup_consumers
SET ENABLED = 'NO'
WHERE NAME IN (
'events_waits_current',
'events_waits_history',
'events_waits_history_long'
);
Operational Guideline: Before enabling additional Performance Schema instrumentation, capture the current contents of performance_schema.setup_instruments and performance_schema.setup_consumers. Following the investigation, restore the original configuration rather than assuming all wait instrumentation should be disabled. This avoids inadvertently disabling pre-existing monitoring required for operational observability.
3. High-Concurrency Taxonomy: Critical System Blocks
Critical System Blocks Summary
Wait Event
Production Root Cause & Impact
Recommended Engineering Remediation
wait/lock/metadata/sql/mdl
An administrative DDL operation requests an exclusive Metadata Lock (MDL) but is blocked by a pre-existing transaction holding a shared metadata lock. Once the exclusive request is queued, subsequent shared lock requests may also queue behind it, creating an MDL convoy that can rapidly escalate into application-wide latency, connection-pool exhaustion, and request timeouts.
Follow the MDL Cascade Anatomy workflow below. Inspect sys.schema_table_lock_waits; identify and remediate the root blocking transaction; enforce SET SESSION lock_wait_timeout = 5 for administrative sessions; and use online DDL options such as ALGORITHM=INPLACE and LOCK=NONE where supported by the operation and MySQL version.
wait/synch/sxlock/innodb/dict_operation_lock
Concurrent metadata-changing operations contend for InnoDB's internal Data Dictionary Operation Lock (dict_operation_lock), serializing DDL, tablespace operations, partition maintenance, and dictionary updates. Under sustained contention, dictionary access becomes a bottleneck, causing schema operations and dependent application threads to stall.
Serialize schema deployments; stagger automated partition-maintenance jobs using advisory locks (GET_LOCK()); avoid high-frequency table creation and drop patterns; and eliminate runtime schema churn by pre-provisioning objects whenever possible.
wait/synch/cond/sql/Group_replication_*
Distributed synchronization stalls within Group Replication, typically involving transaction certification, flow control, applier backlogs, or group-communication delays. Common causes include asymmetric node capacity, network latency, storage-performance disparities, or overloaded replica members. These conditions can throttle commit throughput across the entire cluster.
Validate inter-node network health; inspect performance_schema.replication_group_member_stats; review certification queues and flow-control activity; maintain uniform hardware sizing and storage performance across all cluster members; and investigate lagging members before increasing flow-control thresholds.
4. Deep-Dive: Metadata Lock (MDL) & Cascade Anatomy
The wait event wait/lock/metadata/sql/mdl is among the most disruptive contention patterns in high-concurrency MySQL environments because a single blocked DDL statement can trigger a metadata-lock convoy that stalls large portions of the application tier while the database appears otherwise healthy. Database CPU, memory, and storage utilization may remain relatively normal even as application response times, connection counts, and timeout rates escalate.
Official Documentation: MySQL 8.4 Reference Manual: Metadata Locking.
The Convoy Problem: Catalyst vs. Culprit
In major production incidents, the operational culprit is rarely the DDL statement itself. More commonly, an idle transaction acquires a shared metadata lock, and a subsequent DDL request introduces an exclusive lock request that blocks all future access.
Critical Operational Insights
1. The Sleeping Blocker Trap
When troubleshooting via SHOW PROCESSLIST, engineers frequently focus on connections executing long-running queries (Command: Query). In reality, the blocking session is often a connection in Command: Sleep with an open transaction that has not yet committed or rolled back. Common causes include:
Connection pool leaks
Disabled autocommit
Application error paths
Unclosed ORM transactions
Long-running logical backups
The blocking SQL may have completed hours ago.
2. The 1-Year Default Timeout Trap (lock_wait_timeout)
Many engineers confuse innodb_lock_wait_timeout with lock_wait_timeout. The distinction is critical:
Parameter
Governs
innodb_lock_wait_timeout
InnoDB row-lock waits only (Default: 50 seconds)
lock_wait_timeout
Metadata lock (MDL) waits (Default: 31,536,000 seconds / 1 year)
As a result, an unattended migration can remain blocked for an extremely long period unless an explicit session timeout is configured. A blocked DDL statement can therefore hold a convoy open long enough to exhaust application connection pools and trigger widespread outages.
3. Foreign-Key MDL Cascades
4. Connection Pool Amplification
Modern application frameworks rely heavily on connection pools. A single sleeping transaction holding a metadata lock can eventually block hundreds of worker threads as requests accumulate behind a pending exclusive lock request. Consequently, MDL incidents often surface as:
Connection pool exhaustion
API timeouts
Elevated active session counts
Request queue growth
Application-wide degradation
well before any database resource saturation becomes visible.
Rapid Diagnostic SQL
Direct Triage via sys.schema_table_lock_waits (Recommended):
-- Identify blocking and waiting sessions
SELECT object_schema,
object_name,
waiting_account,
waiting_query,
waiting_thread_id,
waiting_pid,
blocking_account,
blocking_lock_type,
blocking_lock_duration,
blocking_thread_id,
blocking_pid,
sql_kill_blocking_connection
FROM sys.schema_table_lock_waits;
Performance Schema Correlation:
-- Correlate metadata lock requests with transactions and threads
SELECT ml.OBJECT_TYPE,
ml.OBJECT_SCHEMA,
ml.OBJECT_NAME,
ml.LOCK_TYPE,
ml.LOCK_STATUS,
t.PROCESSLIST_ID AS connection_id,
t.PROCESSLIST_USER AS user,
t.PROCESSLIST_HOST AS host,
t.PROCESSLIST_COMMAND AS command,
t.PROCESSLIST_STATE AS state,
trx.trx_started,
TIMESTAMPDIFF(SECOND, trx.trx_started, NOW()) AS trx_age_seconds,
esc.SQL_TEXT AS last_executed_sql
FROM performance_schema.metadata_locks ml
JOIN performance_schema.threads t
ON ml.OWNER_THREAD_ID = t.THREAD_ID
LEFT JOIN information_schema.innodb_trx trx
ON t.PROCESSLIST_ID = trx.trx_mysql_thread_id
LEFT JOIN performance_schema.events_statements_current esc
ON t.THREAD_ID = esc.THREAD_ID
WHERE ml.OBJECT_NAME = 'orders'
ORDER BY ml.LOCK_STATUS ASC,
trx.trx_started ASC;
Production Deployment Guardrails
1. Enforce Fail-Fast DDL Sessions
SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders
ADD COLUMN promo_code VARCHAR(20),
ALGORITHM = INPLACE,
LOCK = NONE;
Use online DDL clauses such as ALGORITHM=INPLACE and LOCK=NONE only where supported by the operation and MySQL version.
2. Perform Pre-Flight Validation
Before executing schema changes, check for:
Long-running transactions in information_schema.innodb_trx
Existing metadata lock contention in sys.schema_table_lock_waits
Sleeping sessions with open transactions
Backup or maintenance jobs that may prolong lock lifetimes
3. Avoid Major DDL During Logical Backups
mysqldump --single-transaction generally avoids table locking for InnoDB tables, but it still creates a long-running consistent-read transaction. Large logical backup operations can:
Extend transaction lifetimes
Retain metadata dependencies longer than expected
Increase DDL scheduling conflicts
Amplify metadata-lock contention windows
Avoid scheduling large schema changes during extensive logical backup activity.
Key Operational Takeaway: The session causing an MDL outage is rarely the ALTER TABLE statement itself. In most production incidents, the root cause is an overlooked sleeping transaction holding a shared metadata lock. The DDL statement merely becomes the catalyst that transforms an isolated transaction into a system-wide metadata-lock convoy.
5. Deep-Dive: Data Dictionary Latch Serialization (dict_operation_lock)
Contention on wait/synch/sxlock/innodb/dict_operation_lock indicates that threads are waiting on InnoDB's internal data dictionary synchronization mechanisms while executing metadata-related operations. Since MySQL 8.0, the data dictionary is transactional and integrated into InnoDB; however, certain DDL and dictionary-management activities still require serialized access to internal dictionary structures and tablespace metadata.
Official Documentation:
MySQL 8.4 Reference Manual: MySQL Data Dictionary
MySQL 8.4 Reference Manual: Atomic Data Dictionary Storage
MySQL 8.4 Reference Manual: Performance Schema Wait Tables
Why This Wait Event Matters
Unlike row-lock contention, dictionary-lock contention can affect schema operations across the entire instance. Large-scale DDL operations, partition maintenance, tablespace modifications, and excessive object creation/drop activity may serialize at the dictionary layer, causing application-facing latency spikes even when CPU, memory, and storage metrics appear healthy.
Production Failure Scenario: Concurrent Maintenance Collision
As concurrent DDL operations accumulate, application threads may spend increasing amounts of time waiting on internal dictionary synchronization. The result is often elevated query latency, slower schema deployments, and prolonged maintenance windows.
Critical Operational Insights
1. Parallel DDL Is Frequently Self-Defeating
Executing multiple schema migrations in parallel rarely reduces overall deployment time. Instead, competing DDL operations often serialize internally, increasing contention and extending maintenance duration.
2. Partition Maintenance Is a Common Trigger
Automated partition-management jobs such as:
ALTER TABLE ... DROP PARTITION;
ALTER TABLE ... ADD PARTITION;
ALTER TABLE ... REORGANIZE PARTITION;
can introduce significant dictionary activity when executed concurrently with other administrative operations.
3. Runtime Schema Churn Does Not Scale
Applications that repeatedly execute:
CREATE TABLE ...;
DROP TABLE ...;
TRUNCATE TABLE ...;
during normal request processing place unnecessary pressure on the data dictionary subsystem and frequently become a bottleneck at scale.
4. Symptoms Are Often Misdiagnosed
Production teams frequently attribute the resulting slowdown to:
Storage latency
CPU saturation
Metadata locks
Replication lag
when the actual bottleneck is serialization within dictionary-management operations.
Diagnostic SQL
Identify Active Dictionary Waits:
SELECT THREAD_ID,
EVENT_NAME,
ROUND(TIMER_WAIT / 1000000000, 2) AS wait_ms,
OPERATION,
OBJECT_NAME
FROM performance_schema.events_waits_current
WHERE EVENT_NAME LIKE 'wait/synch/sxlock/innodb/dict_operation_lock%';
Inspect Active DDL Activity:
SELECT ID AS connection_id,
USER,
HOST,
COMMAND,
TIME AS runtime_sec,
STATE,
INFO AS ddl_statement
FROM information_schema.processlist
WHERE INFO REGEXP '^(ALTER|DROP|CREATE|TRUNCATE)'
AND COMMAND <> 'Binlog Dump';
Identify Concurrent Schema Operations:
SELECT PROCESSLIST_ID,
PROCESSLIST_USER,
PROCESSLIST_HOST,
PROCESSLIST_COMMAND,
PROCESSLIST_STATE
FROM performance_schema.threads
WHERE PROCESSLIST_INFO REGEXP '^(ALTER|DROP|CREATE|TRUNCATE)';
Remediation & Operational Standards
1. Serialize Schema Deployments
Allow only one significant DDL operation per instance (or cluster writer) at a time. Avoid parallel schema deployment frameworks unless compatibility has been thoroughly validated.
2. Protect Automated Partition Maintenance
Use application-level locking to ensure partition-management jobs execute sequentially:
SELECT GET_LOCK('partition_maint_lock', 0) INTO @lock_acquired;
-- Continue only if @lock_acquired = 1
SELECT RELEASE_LOCK('partition_maint_lock');
Official Documentation: MySQL 8.4 Reference Manual: Locking Functions.
3. Eliminate Runtime Table Churn
Avoid creating and dropping permanent tables during normal request processing. Prefer:
CREATE TEMPORARY TABLE
Persistent staging tables
Redis or application caches
Queue-based processing architectures
4. Schedule Heavy DDL Windows
Large partition reorganizations, bulk table drops, archive cleanup operations, and schema deployments should be scheduled during dedicated maintenance windows whenever possible.
5. Audit Automation Pipelines
Review:
Flyway/Liquibase pipelines
Partition-rotation jobs
Archival procedures
Tenant-provisioning workflows
for overlapping DDL execution patterns.
Key Operational Takeaway: wait/synch/sxlock/innodb/dict_operation_lock is typically not caused by user queries competing for data. It is usually a signal that multiple metadata-changing operations are competing for access to InnoDB's dictionary-management infrastructure. In high-concurrency environments, the most effective remediation is operational discipline: serialize DDL, coordinate partition maintenance, and eliminate unnecessary schema churn.
6. Deep-Dive: Group Replication Flow-Control Dynamics
In MySQL Group Replication and InnoDB Cluster deployments, the wait/synch/cond/sql/Group_replication_* wait-event family typically indicates distributed synchronization delays involving group communication, transaction certification, applier processing, or flow-control regulation. Under sustained write workloads, the cluster must continuously balance primary-node commit throughput against the processing capacity of all participating members.
Official Documentation:
MySQL 8.4 Reference Manual: Group Replication Performance and Tuning
MySQL 8.4 Reference Manual: Monitoring Group Replication
Production Failure Scenario: Asymmetric Node Throttling
Why This Wait Event Matters
Unlike traditional replication lag, Group Replication actively protects cluster consistency and member convergence. When a member falls behind:
Certification queues increase.
Applier queues accumulate.
Flow Control activates.
Primary commit throughput is throttled.
Application write latency increases across the cluster.
Consequently, a single weak member can impact overall write performance even when the primary node remains healthy.
Diagnostic SQL
1. Inspect Certification Queues and Conflict Rates:
SELECT MEMBER_ID,
COUNT_TRANSACTIONS_IN_QUEUE AS cert_queue_depth,
COUNT_TRANSACTIONS_CHECKED AS total_checked,
COUNT_CONFLICTS_DETECTED AS conflict_count,
COUNT_TRANSACTIONS_ROWS_VALIDATING AS rows_validating
FROM performance_schema.replication_group_member_stats;
Look for:
Large queue depths
Rapidly growing certification backlogs
Elevated conflict rates
Significant imbalance between members
2. Review Flow-Control Configuration:
SELECT VARIABLE_NAME,
VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME LIKE 'group_replication_flow_control%';
Verify:
Flow-control mode
Applier thresholds
Certification thresholds
Member-specific tuning deviations
Remediation & Architecture Standards
1. Maintain Balanced Cluster Sizing
Ensure all cluster members provide comparable:
CPU capacity
Memory allocation
Storage performance
Network throughput
A significantly slower member frequently becomes the trigger for cluster-wide Flow Control.
2. Investigate Before Raising Flow-Control Thresholds
Avoid using threshold increases as a first response. Instead:
Identify lagging members
Review queue growth trends
Examine storage latency
Review CPU utilization
Validate network health
Only adjust flow-control thresholds following controlled performance testing.
3. Monitor Certification and Applier Queues
Regularly review performance_schema.replication_group_member_stats for:
Queue growth
Conflict detection rates
Validation workload increases
Sustained member imbalance
Early detection often prevents cluster-wide throttling events.
4. Audit Inter-Node Network Health
Validate:
Latency consistency
Packet loss
Throughput capacity
Network saturation
Standard Linux utilities assist in identifying communication bottlenecks:
ping <peer_ip>
iperf3 -c <peer_ip>
If Jumbo Frames are deployed, verify end-to-end MTU consistency across all network paths.
Key Operational Takeaway: Group Replication flow-control waits are rarely caused by the primary writer alone. In most production incidents, the root cause is a lagging member that cannot certify or apply transactions at the cluster's incoming write rate. Flow Control intentionally sacrifices writer throughput to preserve cluster consistency, making the slowest member a critical determinant of overall cluster performance.
Summary & What's Next
Metadata-lock convoys, data-dictionary serialization, and Group Replication flow-control events represent three of the most disruptive forms of systemic contention in modern MySQL environments. Unlike conventional CPU, memory, or storage bottlenecks, these failures emerge from contention on shared internal coordination mechanisms and can affect large portions of the application stack while infrastructure metrics remain deceptively normal.
In Part 2: Storage Subsystems, Buffer Pool Latches, and Memory Spills
(Read Part 2),
the series shifts into storage-engine internals, including:
Redo log checkpoint-age saturation and dynamic log sizing
Doublewrite buffer behavior and storage durability characteristics
Buffer pool mutex contention (buf_pool_mutex)
Binary Log Group Commit optimization
TempTable spills (FILE_TMPFILE)
Undo purge lag and History List Length (HLL) growth
Continue to Part 2: Storage Subsystems & Memory Contention.7TKU66CE8V5W
-
WooCommerce Analytics with DuckDB
MySQL and DuckDB
Unlike row-oriented databases, which store data for a single record sequentially on disk, favoring single-row lookups, columnar databases store all the values from a column contiguously in storage, allowing only the selected column to be read from disk.
The default MySQL engine is InnoDB, which is ACID-compliant and great for OLTP (Online Transaction Processing) workloads. That said, OLAP (Online Analytical Processing) queries are built differently; they scan, group, and summarize millions or billions of rows to produce reports. In these cases, the InnoDB engine can be slow to process such queries. This has been a debt in MySQL over the years; it was common to setup ETL/CDC pipelines to export or stream the data to another engine, usually a columnar database system, to run reporting and BI queries on it.
DuckDB is a portable analytical database system, designed to be fast and reliable, similar to SQLite.
Recently, various vendors (MariaDB, Alibaba) started including DuckDB inside MySQL as a storage engine. A table declared with ENGINE=DuckDB stores data in columnar format, and queries are handled by the DuckDB engine.
This is useful in several ways; the main one is to use MySQL asynchronous replication to stream data from the OLTP, using the InnoDB engine, to a Replica where tables are in DuckDB, thereby offloading the OLAP workload to the replica. To showcase this, we thought that the best example was an on-line store which naturally requires both: short, fast, scalable transactions for the store frontend, and analytics for the store’s reporting and Business Intelligence, and what better than the free and open-source software that serves between 20 and 40% of the world’s e-commerce websites: WooCommerce.
WooCommerce
There are around 4M WooCommerce stores worldwide, the most of any e-commerce platform, handling between 20 and 39% of all e-commerce sites, and they use MySQL as their underlying database.
One of the reasons stores have been adopting computers for over 50 years, is the value of having accumulated information about your business’ transactions. This data collection allows business owners to understand trends and find new opportunities, by turning raw data into metrics that marketing and sales departments can act upon. This process is based on the long-term accumulation of large amounts of data and metadata, and that accumulation is often incompatible with transactional/row-oriented storage engines.
WooCommerce has a built-in analytics module to create those reports that provide metrics, so it is the perfect product to test the performance difference between InnoDB and DuckDB.
WooCommerce analytics does not run its reports against live order tables because querying these tables would be too slow. Instead, it keeps a set of aggregations in a number of denormalized “lookup” tables, which are optimized for this purpose. This is a limitation when running reports with InnoDB, as querying the raw tables would kill performance, and thus the reports are limited to what you can get from these lookup tables.
The following lookup tables are created for WooCommerce Analytics:mysql> SHOW TABLES LIKE '%wc_%lookup'; SHOW TABLES LIKE '%wc_order_stats';
+-------------------------------------+
| Tables_in_woocommerce (%wc_%lookup) |
+-------------------------------------+
| wp_wc_category_lookup |
| wp_wc_customer_lookup |
| wp_wc_order_coupon_lookup |
| wp_wc_order_product_lookup |
| wp_wc_order_tax_lookup |
| wp_wc_product_attributes_lookup |
| wp_wc_product_meta_lookup |
+-------------------------------------+
7 rows in set (0.00 sec)
+-----------------------------------------+
| Tables_in_woocommerce (%wc_order_stats) |
+-----------------------------------------+
| wp_wc_order_stats |
+-----------------------------------------+
1 row in set (0.00 sec)The wp_wc_order_stats table is the spine of the revenue and orders reports; it contains one row per order, including dates, status, customer, number of items sold, and the monetary totals.
In the store we’re using, the wc_order_stats table contains 400000 rows, and there are 5000 products. This data was loaded using the WooCommerce Smooth Generator plugin.
The lookup tables are derived data, so WooCommerce has to keep them in sync with the orders they summarize, which it does in the background (which means you have to run a scheduled process that is what keeps these lookup tables updated).
MySQL Deployment
We deployed a Source/Replica MySQL topology with both databases on the same host, each with its respective Docker image.
Source
The Source runs Percona Server for MySQL 8.0.46, which we chose to match the replica’s major version.
WooCommerce tables use the MySQL default engine, which is InnoDB.
We configured an InnoDB Buffer Pool of 4 GiB:[mysqld]
innodb_buffer_pool_size=4G
Replica
For this test, as a Replica we deployed AliSQL 8.0.44-2, Alibaba’s MySQL branch based on MySQL 8.0.44, which includes the DuckDB storage engine along with other features such as Vector Search and Flashback. At this moment, this is the latest available version.
We configured the DuckDB instance with the following settings:[mysqld]
duckdb_memory_limit=4G # maximum memory DuckDB is allowed to use
force_innodb_to_duckdb=ON # force-replace the InnoDB engine with DuckDB
duckdb_threads=0 # total number of threads used by DuckDB. 0 equals system CPU cores.You can find full reference for DuckDB settings here https://github.com/alibaba/AliSQL/blob/master/wiki/duckdb/duckdb_variables-en.md
The force_innodb_to_duckdb option is mandatory in this process, as it is the way to tell MySQL to use DuckDB engine for tables created via replication or mysqldump.
After loading the 5000 products and 400K orders, the size of the WooCommerce database directory using the InnoDB engine was 7.3 GiB:$ du -sch woocommerce/
7.3G woocommerce/While on the replica where tables have been created with DuckDB, the dataset fits in just 601 MiB:$ du -sch duckdb.db
601M duckdb.dbThat is a solid 12x improvement on disk footprint!
Sending reports queries directly to the Replica
We wanted to find a way to make the process as transparent as possible, but WooCommerce uses WordPress’s single database connection object for everything, which means that the reports’ code will use the global $wpdb, and there is no setting that would allow us to send this reporting/analytics traffic to a different server, or an easy way to introduce a second connection object to the code. This means you are stuck with a single server, so there would be no easy way to make use of both InnoDB and DuckDB copies.
Thus, we had to come up with some solution to run the reports against the replica. We like simple stuff, so instead of changing the existing WooCommerce installation at all, we run a whole second copy of the same WooCommerce installation that connects to the replica, and we push the analytics traffic to that second WooCommerce copy.
The original install of your store stays exactly as it is, with its wp-config.php which already points to the primary (InnoDB/transactional) database:define( 'DB_HOST', 'ps8.example.local' ); // Percona Server for MySQL 8.0.46, InnoDBAnd the second install is just a full copy of the first, in its own directory, with just one line of code changed: the database host.$ cp -aL /var/www/html /var/www/html2; # we assume your WooCommerce install is /var/www/html
$ chown -R apache:apache /var/www/html2 # and we also assume you run Apache; should not be hard to adapt for Nginx or other web serversIn the copy’s wp-config.php we change only the host and leave the table prefix, the authentication keys, everything else untouched:define( 'DB_HOST', 'duckdb8.example.local'' ); // AliSQL 8.0.44 with the DuckDB storage engine, the replicaThe second install gets a dedicated pool, listening on its own socket, and its own virtual host on an internal port, handing .php to that pool:# /etc/httpd/conf.d/html2.conf
<VirtualHost 127.0.0.1:8080>
DocumentRoot /var/www/html2
<Directory "/var/www/html2">
AllowOverride All
Require all granted
</Directory>
<FilesMatch \.php$>
SetHandler "proxy:unix:/run/php-fpm/www2.sock|fcgi://localhost"
</FilesMatch>
</VirtualHost>With that in place, we have two addresses for (essentially) the same WooCommerce, each using a different database:
– http://127.0.0.1:80/ is the store, on Percona Server for MySQL 8.0.46 (InnoDB).
– http://127.0.0.1:8080/ is the copy, on DuckDB replica.
Every analytics call we benchmark goes to one or the other, so the same report is run against InnoDB and against DuckDB from the same codebase, changing nothing but the database underneath.
Benchmark Methodology
Each report was run several times, and we kept the fastest run for each.
Before comparing times, we verified that both engines returned the same result set: the same number of rows and the same values, once serialized and sorted. A few reports differed because the DuckDB engine computes decimals as double-precision floating-point rather than exact DECIMAL, so some monetary sums ended up a fraction of a cent apart. While this might be of concern when dealing with real money, for reporting the precision DuckDB provides should be more than enough, as the difference between the reports was always below 1 cent.
The two engines never ran at the same time. Every report ran first on InnoDB and then on DuckDB, so they never competed for CPU, memory, or disk (at the web server layer).
A few reports whose SQL relied on MySQL-only syntax failed outright on DuckDB and were discarded rather than rewritten, since the point was to run the same query on both engines.
We drove the benchmarks through WooCommerce’s own Analytics REST API, sending the same report requests to each engine and timing the responses.
Each report was run with the force_cache_refresh parameter, so the result is computed from the database on every call rather than served from WooCommerce’s cache.
Built-in Analytics Reports
We started with the built-in WooCommerce Analytics reports. All reports used the same window, from 2024-01-01 to 2026-09-01, over a store of 400,000 orders. The stats reports were aggregated by month; the detail reports returned up to 100 rows per page.
Every built-in report ran faster on DuckDB, from about 10x to nearly 63x, and all of them finished in under a second against InnoDB’s 5 to 16. The biggest improvement resulted from the category reports, where the heavy grouping and aggregation show the columnar engine’s strength.
Built-in Analytics Segmented Reports
The built-in reports are not limited to a single total over a date range. Each stats report takes an interval, so you can ask for the same figures broken down by hour, day, week, or month, and several of them also accept a segmentby parameter, which splits the totals along a dimension. Segmenting by category, for example, returns revenue or order numbers per product category rather than as a single combined figure, which is how the Analytics screens generate their breakdowns.
The finer the interval and the more segments involved, the more the report asks of the database. These are the reports we use to compare InnoDB and DuckDB because they are where the underlying engine makes the most difference.
Every segmented report ran faster on DuckDB, ranging from about 14x to 83x faster. Segmenting by category and time pushes InnoDB into multi-minute queries, while DuckDB still answers in seconds, so the heavier the breakdown, the wider the gap.
WooCommerce Customer Analytics Plugin
To see whether the engine helps with real third-party code, not just the built-in reports, we also benchmarked the Customer Analytics for WooCommerce plugin from Coddium.
It extends WooCommerce Analytics with customer metrics the built-in reports do not provide: customer lifetime value, churn rate, RFM segments, cohort retention, repeat purchase rate, order sequence, and acquisition sources, among others.
Like the built-in reports, each of these reads from the same WooCommerce Analytics lookup tables, making it a good test of whether a plugin written for MySQL runs faster on the DuckDB engine without any changes of its own.
Even with a third-party plugin we did not modify, all eleven reports ran faster on DuckDB, from about 2x to nearly 70x faster.
Ad-hoc Query on Live Data
As mentioned, the built-in analytics reports are served from precomputed lookup tables, which only answer the questions they were designed to answer. For anything else, you have to query the live order tables directly. So we tested an expensive query against those tables to compare the performance.
In this case, we wanted to know how many items were sold, broken down by product category, city, and day of the week, across the store’s orders in the date range. We came up with the following query:SELECT
tt.term_taxonomy_id AS category_id,
LOWER(a.city) AS city,
DAYOFWEEK(o.date_created_gmt) AS dow,
SUM(CAST(im_qty.meta_value AS DECIMAL(10,2))) AS items_sold
FROM woocommerce.wp_wc_orders o
JOIN woocommerce.wp_wc_order_addresses a
ON a.order_id = o.id AND a.address_type = 'billing'
JOIN woocommerce.wp_woocommerce_order_items oi
ON oi.order_id = o.id AND oi.order_item_type = 'line_item'
JOIN woocommerce.wp_woocommerce_order_itemmeta im_prod
ON im_prod.order_item_id = oi.order_item_id AND im_prod.meta_key = '_product_id'
JOIN woocommerce.wp_woocommerce_order_itemmeta im_qty
ON im_qty.order_item_id = oi.order_item_id AND im_qty.meta_key = '_qty'
JOIN woocommerce.wp_term_relationships tr
ON tr.object_id = CAST(im_prod.meta_value AS DECIMAL(20,0))
JOIN woocommerce.wp_term_taxonomy tt
ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = 'product_cat'
WHERE o.type = 'shop_order'
AND o.status IN ('wc-completed','wc-processing')
AND o.date_created_gmt >= '2024-01-01' AND o.date_created_gmt < '2026-09-01'
GROUP BY category_id, LOWER(a.city), dow;The execution time difference between both engines can be found below:
Across three runs each, DuckDB was consistently about 4.1 seconds and InnoDB about 4 minutes 16 seconds.
The results are not identical, and the two extra groups come from how each engine compares text. Since the city column uses a case- and accent-insensitive collation that InnoDB applies, it folds an accented spelling and its plain form into a single group. The DuckDB engine does not apply the collation and compares text byte-for-byte, keeping them apart.
No orders are missing on either side; the same rows are simply bucketed into two more groups.
Limitations
We ran into the following limitations when performing the above benchmarks:
DuckDB SQL compatibility
Since the DuckDB storage engine executes queries in its own engine, the SQL syntax should be portable. A query that runs fine on InnoDB can fail on DuckDB when it uses a keyword or data type that DuckDB does not recognize. For example, the CAST function in MySQL accepts the UNSIGNED keyword, but when it’s pushed into DuckDB, it fails with the following error:ERROR 7501 (HY000): [DuckDB] Catalog Error: Type with name UNSIGNED does not exist!
Did you mean "JSON"?.Such queries need to be rewritten so DuckDB can process them. This can be automated with query-rewrite tools such as ProxySQL Query Rewrite feature or MySQL’s Rewriter Query Rewrite plugin, which we will cover in a separate blog post.
Collation
Unlike InnoDB, DuckDB does not apply the column collation and forces binary comparison. This can produce different result sets between engines.
For example, the following SELECT COUNT result set differs between DuckDB and InnoDB:# DuckDB
mysql> SELECT COUNT(DISTINCT city) FROM woocommerce.wp_wc_order_addresses WHERE address_type='billing' AND city<>'';
+----------------------+
| COUNT(DISTINCT city) |
+----------------------+
| 114412 |
+----------------------+
1 row in set (0.02 sec)
# InnoDB
mysql> SELECT COUNT(DISTINCT city) FROM woocommerce.wp_wc_order_addresses WHERE address_type='billing' AND city<>'';
+----------------------+
| COUNT(DISTINCT city) |
+----------------------+
| 114375 |
+----------------------+
1 row in set (1.38 sec)This is caused by DuckDB bypassing the column collation.
If we force binary comparison when running the query on InnoDB, we obtain the same result set as DuckDB:# InnoDB BINARY
mysql> SELECT COUNT(DISTINCT BINARY city) FROM woocommerce.wp_wc_order_addresses WHERE address_type='billing' AND city<>'';
+-----------------------------+
| COUNT(DISTINCT BINARY city) |
+-----------------------------+
| 114412 |
+-----------------------------+
1 row in set, 1 warning (1.34 sec)To prevent this, group and join should be done on numeric IDs rather than text.
Decimals computed as Double
To improve performance, DuckDB does not support DECIMAL precision by default, so monetary columns are summed in floating point instead. This can lead to differences at sub-cent digits. If you need exact decimal values, you can disable the duckdb_use_double_for_decimal variable when importing the data.
We have not yet fully tested performance implications of disabling this, but we plan to cover this in another blogpost, in the near future.
Foreign keys
Traditionally in MySQL, cascaded foreign key operations such as ON DELETE CASCADE were handled internally by InnoDB, below the SQL layer, so they were not written to the binlog files.
Because the DuckDB storage engine does not perform these cascades itself, changes to child rows would be lost when replicating from InnoDB to DuckDB.
Starting with MySQL 9.6, foreign key cascades are handled at the SQL layer by default, so the affected rows are recorded in the binary log, making replication across different engines transparent.
The following MySQL blog post explains this change in detail:
https://blogs.oracle.com/mysql/no-more-hidden-changes-how-mysql-9-6-transforms-foreign-key-management
WooCommerce does not use cascading foreign keys, so this limitation does not apply in this case.
Observability
DuckDB exposes only a few of status variables. Status variables mostly show counters for statements (Com_duckdb_select, Com_duckdb_insert) and rows processed in batches.
InnoDB, by comparison, exposes dozens of status variables covering the buffer pool, I/O, row operations, and locking, and is instrumented in detail through the Performance Schema.
So there is little visibility into what the DuckDB engine is doing internally, such as memory use, disk spilling, or query execution, which makes monitoring and tuning harder than on InnoDB.
This gap makes sense though, given how much longer InnoDB has existed; DuckDB has had far less time to build equivalent instrumentation.
Why Columnar engine wins?
The reason is physical, and it comes down to how the data is stored on disk. InnoDB is a row store; it keeps all of a row’s columns together, which is ideal for transactional work, where you read or write an entire row at a time.
Analytics asks the opposite question. A report rarely needs the whole row; it sums one column, counts another, and groups by a third, across millions of rows.
In a row store, answering that means reading every full row from the B-tree and discarding most of it.
A columnar engine stores each column separately, so it reads only the fields a query touches, compresses them far better because the values share a type, and processes them in batches instead of row by row. Reading less, reading it in compressed form, and processing it in bulk is what turns InnoDB’s minutes into DuckDB’s seconds. The heavier the aggregation, the wider the gap, which is exactly what the benchmarks showed.
P.S. DuckDB is a potential solution for analytics use cases, and this post is part of our testing. At this time, Percona does not have a production DuckDB build and does not support it.
The post WooCommerce Analytics with DuckDB appeared first on Percona.
|
