MySQL insights

InnoDB undo log history list length growing due to long-running transactions or failed purge. Growing history list increases tablespace size, degrades query performance due to version traversal, and can eventually exhaust undo log space.

Table cache insufficient for working set, causing frequent table opens/closes. High table cache miss rate degrades performance as MySQL repeatedly opens/closes table files, consuming CPU and causing latency spikes.

InnoDB checkpointing cannot keep pace with write workload, causing log files to fill and write stalls. Occurs when innodb_io_capacity is too low, storage IOPS insufficient, or during sustained high write load. Manifests as periodic write latency spikes.

Queries exceeding long_query_time threshold accumulate, indicating systematic performance issues. High slow query rate signals need for query optimization, missing indexes, or resource scaling. Patterns in slow query types reveal specific optimization opportunities.

Galera cluster transactions failing certification due to conflicts detected during optimistic locking. High certification failure rate indicates concurrent writes to same data across nodes, reducing effective write throughput and causing application-level errors.

Galera/PXC cluster experiencing flow control pauses due to replication lag between nodes. Slower nodes trigger flow control to prevent unbounded lag, causing write stalls across the entire cluster and reducing overall throughput.

InnoDB change buffer (insert buffer) accumulating changes faster than they can be merged to secondary indexes. Large change buffer improves write performance but can cause read stalls and long crash recovery times.

Number of prepared statements approaching max_prepared_stmt_count limit. When limit reached, new prepared statements are rejected, causing application errors. Often indicates prepared statement leaks in application code.

Group Replication replica falling behind in applying transactions from the group. Apply queue grows as transaction receive rate exceeds apply rate, leading to data staleness on the lagging member and potential flow control activation.

Group Replication transactions failing certification due to write conflicts. In multi-primary mode, concurrent writes to same rows on different nodes cause certification failures and transaction rollbacks, reducing effective throughput.

Performance Schema statement digest table full, losing query performance data. When performance_schema_digests_size limit reached, new unique query patterns cannot be instrumented, causing blind spots in performance monitoring.

Sort operations exceeding sort_buffer_size, requiring multi-pass merge sorts and temporary files. Causes significant performance degradation for queries with ORDER BY, GROUP BY, or large JOINs requiring sorting.

Contention on InnoDB adaptive hash index causing performance degradation. Multiple threads compete for AHI mutex access, reducing query throughput. Common in high-concurrency workloads with point lookups on frequently accessed indexes.

High rate of aborted connections indicates network issues, client timeouts, or application connection handling problems. Aborted connections waste resources and can indicate larger infrastructure or application issues.

Queries creating temporary tables exceeding tmp_table_size/max_heap_table_size, spilling to disk. Disk-based temporary tables are much slower than in-memory, causing query latency increases. Common with large JOINs, GROUP BY, or DISTINCT operations.

MyISAM table locks blocking concurrent queries, causing query queuing and latency spikes. MyISAM uses table-level locking, so writes block all other operations on the table. Mixed read/write workloads suffer severe contention.

Query cache experiencing high invalidation rate, causing more overhead than benefit. Frequent table updates invalidate cached queries, leading to cache thrashing. Note: Query cache is deprecated in MySQL 5.7.20 and removed in MySQL 8.0.

Storage I/O capacity exhausted by high write workload. Write operations queue waiting for disk, causing latency increases and potential transaction timeouts. Often occurs during batch updates, bulk inserts, or when storage IOPS provisioning is insufficient.

Storage fills up due to binary log retention exceeding available disk space. Binary logs accumulate faster than they are purged, often during replication lag or when binlog_expire_logs_seconds is set too high, leading to storage exhaustion.

Working set exceeds available buffer pool memory, forcing excessive disk reads. Buffer pool efficiency drops as pages are constantly evicted and reloaded, causing disk I/O saturation and query latency increases.

Sudden spike in binary log disk usage triggers CPU, network, and IOPS saturation, creating a cascading slowdown. Large write operations (bulk updates, schema changes) generate excessive binlog data faster than it can be purged, leading to storage pressure and performance degradation.