Tagged articles
performance_schema
51 articles · Page 1 of 1
This comprehensive guide covers MySQL slow query analysis from log configuration to index design, EXPLAIN plan interpretation, SQL rewriting techniques, and production troubleshooting workflows with real-world examples and concrete metrics.
EXPLAINMySQLSQL Optimization
0 likes · 84 min read

This guide provides a structured 10-minute triage process for sudden MySQL replication lag, covering IO/SQL thread diagnosis, GTID gap analysis, source write-rate checks, replica resource contention, and safe mitigation actions with ready-to-run evidence-collection scripts.
GTIDLAGMySQL
0 likes · 41 min read


21CTOSep 25, 2026 · Databases
The author benchmarks MariaDB 10.3, MySQL 8.0, and PostgreSQL 12 using a real Turkish motorcycle classifieds app with many simple queries, finding MariaDB 5% faster on average and 7% faster at p95 with lower CPU usage, though MySQL handles tail latency better and memory differences stem from default performance_schema settings.
Database PerformanceMariaDBMySQL
0 likes · 10 min read

This article presents a rigorous, production-ready framework for MySQL index governance that moves beyond simplistic zero-usage checks, using performance_schema observation windows, structural gatekeeping, invisible index canary testing, and quantified rollback metrics to safely identify and remove redundant indexes without risking query regressions.
MySQLSQL Tuningdatabase administration
0 likes · 29 min read

This article details a production-grade MySQL index governance system for high-concurrency e-commerce, covering evidence collection, invisible index validation, automated change gates, regression detection, and a case study reducing order-list P99 from 2.3s to 170ms while preventing unsafe deletions.
MySQLe-commercehigh concurrency
0 likes · 38 min read

This article explains why MySQL deadlocks occur in production, distinguishes them from simple lock waits, and provides a step‑by‑step guide—including enabling deadlock logging, analyzing InnoDB lock types, and applying four practical solutions such as distributed locks, unique constraints, isolation‑level changes, and SQL reordering—to reliably troubleshoot and prevent deadlocks.
InnoDBMySQLSQL Tuning
0 likes · 31 min read

This article explains how MySQL deadlocks arise, details the four necessary conditions, compares lock types, shows how to enable detailed deadlock logging, query lock metadata, interpret logs, and provides practical code‑level and configuration strategies to prevent and resolve common deadlock scenarios in production environments.
InnoDBMySQLdeadlock
0 likes · 20 min read

This article explains InnoDB's lock mechanisms—including table, intention, row, GAP, next‑key, and auto‑increment locks—shows how to inspect them via performance_schema tables, demonstrates lock behavior under different isolation levels with concrete SQL examples, and clarifies lock compatibility rules.
InnoDBLocksMySQL
0 likes · 20 min read

The article explains how MySQL InnoDB deadlocks occur, details the four necessary conditions, shows how to enable full deadlock logging, demonstrates queries against information_schema and performance_schema, and provides concrete scenarios with code‑level solutions to prevent and resolve deadlocks in production environments.
InnoDBMySQLdeadlock
0 likes · 22 min read

This guide walks through building a Prometheus‑Grafana monitoring stack for MySQL, selecting exporters, defining key metric groups, leveraging Performance Schema for deep insights, configuring tiered alerts, and applying trend‑based capacity planning to anticipate resource needs.
AlertmanagerGrafanaMySQL
0 likes · 7 min read

This article examines why a SELECT … FOR UPDATE query that appears to use a covering index in MySQL actually performs a table‑row lookup, detailing indirect evidence from performance_schema locks and direct proof through InnoDB source code, and explains the necessity of accessing the primary key for transaction isolation.
Database InternalsIndex ScanInnoDB
0 likes · 9 min read

This guide walks you through a step‑by‑step process for identifying the root cause of sudden 100% CPU usage on AWS RDS MySQL instances using Performance Insights, SHOW FULL PROCESSLIST, and Performance Schema, and provides practical tips for remediation.
AWSCPUMySQL
0 likes · 9 min read

A batch INSERT triggers a massive lock record buildup, and a concurrent SELECT on performance_schema.data_locks causes a memory allocation error that leaves trx_sys‑mutex unreleased, leading to a full MySQL hang; the article reproduces the bug, analyses the stack, explains the root cause, and shows the official fix in MySQL 8.0.37.
InnoDBMySQLbug analysis
0 likes · 9 min read

This article summarizes MySQL 9.0’s new VECTOR column type, inline and implicit foreign‑key constraints, JSON EXPLAIN ANALYZE storage, prepared event statements, performance‑schema variable tables, enhanced EXPLAIN output, LIMIT 1 subquery support, as well as deprecated and removed features with practical examples.
Deprecated featuresEXPLAIN ANALYZEInline foreign key
0 likes · 13 min read

MySQL 9.0, released July 2 2024, introduces EXPLAIN JSON output stored in variables, native EVENT DDL statements, and two new performance‑schema tables for variable metadata, while deprecating the old variables_info columns and removing the mysql_native_password plugin in favor of caching_sha2_password, enhancing observability and security.
AuthenticationEXPLAINEvent
0 likes · 11 min read

This article explains how to use MySQL's performance_schema to monitor and analyze per‑connection memory consumption, provides SQL queries to list memory instruments, shows Python scripts for sampling and visualizing memory usage over time, and demonstrates practical usage with example commands and output.
Pythonmemory profilingperformance_schema
0 likes · 14 min read

The article explains how MySQL 8 enhances replication observability by exposing detailed metrics through Performance Schema tables and sys schema views, providing DBAs with richer information such as per‑channel lag, worker thread states, and full replication status beyond the traditional SHOW REPLICA STATUS output.
InnoDB ClusterMySQLReplication
0 likes · 14 min read

The article details how Meituan built a MySQL autonomous platform by constructing kernel observability to split OnCPU/OffCPU wait time, capturing full SQL directly from the kernel with compression, designing a safe exception‑handling workflow, and generating cost‑based index‑tuning suggestions—including what‑if analysis and workload‑driven recommendations—to enable comprehensive SQL governance.
Exception HandlingFull‑SQLMySQL
0 likes · 34 min read

This article explains how to collect extensive MySQL performance metrics—including connections, buffer pool statistics, lock information, SQL status, statement counts, throughput, server configuration, and slow‑query analysis—using only MySQL's native SHOW commands, providing practical commands, calculations, and optimization tips for effective database monitoring.
monitoringperformance_schemaslow query
0 likes · 10 min read

This article explains how InnoDB implements row‑level locking in MySQL, detailing the lock types (record, gap, next‑key), the scenarios that trigger each lock, how unique and non‑unique indexes affect locking, and provides step‑by‑step commands and examples for analyzing locks with performance_schema.
Database ConcurrencyInnoDBLock Types
0 likes · 45 min read

This article explains how to collect comprehensive MySQL performance data—including connections, buffer cache, locks, statement counts, throughput, server configuration, and slow‑query logs—using only native SHOW commands, providing step‑by‑step SQL snippets, calculation formulas, and best‑practice tips for efficient monitoring.
SHOW commandsSQL Optimizationdatabase monitoring
0 likes · 10 min read

This article explains how to collect extensive MySQL performance metrics—including connections, buffer cache, locks, SQL status, statement counts, throughput, server configuration, and slow‑query logs—using only MySQL's native SHOW commands and the performance_schema, providing practical code snippets and optimization tips.
MySQLSQLdatabase
0 likes · 10 min read

This comprehensive guide explains how MySQL's InnoDB engine applies row‑level locks—including shared, exclusive, gap, and next‑key locks—based on different SQL statements, isolation levels, and index types, and shows how to analyze and avoid phantom reads with practical experiments.
InnoDBMySQLRow-Level Locking
0 likes · 46 min read

This article explains how to collect a wide range of MySQL performance metrics—including connections, buffer pool usage, locks, SQL statistics, statement counts, throughput, server variables, and slow‑query logs—using only MySQL's native SHOW commands and performance_schema tables, while also offering practical tuning tips and analysis tools.
MySQLSQLdatabase monitoring
0 likes · 11 min read

The article details the refactoring of memory monitoring in MySQL 8.0.28, introducing a new PFS metadata structure for precise tracking of memory allocations and deallocations across various components like Buffer Pool and user variables.
InnoDBMySQLdatabase optimization
0 likes · 9 min read

This guide explains MySQL 8.0.17's Clone Plugin—how to install it, perform local and remote clones, monitor progress via performance_schema, build replicas from cloned data, understand its internal stages, configure parameters, and compare it with XtraBackup.
Clone PluginDatabase ReplicationInnoDB
0 likes · 19 min read

The article explains MySQL's metadata lock (MDL) mechanism, shows how to enable its instrumentation, demonstrates various lock scenarios with SELECT, INSERT and ALTER statements, and provides guidance on diagnosing and resolving MDL‑related blocking issues.
DDLDMLMDL
0 likes · 12 min read

The article investigates a MySQL 8.0.26 crash triggered by setting the terminology_use_previous parameter to BEFORE_8_0_26, analyzes stack traces and core dumps, compares processlist access methods, references related bugs, and offers mitigation recommendations for monitoring tools.
MySQLinformation_schemaperformance_schema
0 likes · 12 min read

This article demonstrates how to use Google’s tcmalloc library and the pprof tool to capture and analyze MySQL heap dumps, revealing memory allocations that performance_schema cannot track, and explains the experimental setup, data collection, and interpretation of the resulting memory allocation graphs.
MySQLTCMallocperformance_schema
0 likes · 4 min read

This article introduces MySQL 8.0's resource group feature, detailing its concepts, configuration commands, query hint usage, and the underlying implementation including new parser classes, platform APIs, runtime components, performance schema integration, and persistence mechanisms, with code examples throughout.
MySQLParserResource Group
0 likes · 17 min read

This article explains how to use MariaDB's Query Profiling, Slow Query Log, and Performance Schema together with the Monyog monitoring tool to diagnose, visualize, and optimize database performance, covering configuration, log analysis, schema queries, and dashboard reporting.
MariaDBMonyogdatabase optimization
0 likes · 15 min read

MySQL 8.0.22 introduces prepared‑statement improvements, a new SHOW PROCESSLIST implementation via Performance Schema, UTC timestamp handling, read‑only schemas, error‑log access through Performance Schema, user‑management enhancements, optimizer push‑down, replication auto‑failover, Router scalability upgrades, and several deprecations and removals.
8.0.22MySQLPrepared Statements
0 likes · 9 min read

The MySQL Clone Plugin, added in 8.0.17, enables fast local or remote cloning of InnoDB data by installing the plugin, issuing CLONE commands, monitoring progress via performance_schema tables, and using the resulting GTID or binlog coordinates to build replicas, while observing limitations such as DDL restrictions and single‑instance operation, and offering advantages over XtraBackup through an extra page‑copy stage and simplified GTID handling.
Clone PluginInnoDBMySQL
0 likes · 22 min read

This article details a comprehensive investigation of MySQL out‑of‑memory (OOM) alerts on a Tencent Cloud CDB instance during a high‑traffic marketing event, covering background, observed symptoms, step‑by‑step debugging, analysis of server and engine memory usage, relevant MySQL bugs, fetch_cache behavior, and the final configuration recommendations to prevent OOM.
MySQLOOMTencent Cloud
0 likes · 14 min read

This article walks through a practical MySQL troubleshooting experiment that shows how to identify a CPU‑intensive query using the processlist, locate the offending thread with top‑H, interpret performance_schema IDs, and safely terminate the problematic SQL statement.
CPUMySQLdatabase troubleshooting
0 likes · 3 min read

This article demonstrates how to enable MySQL's performance_schema, generate test data, run an ALTER TABLE operation, and continuously query a custom SQL statement to estimate the operation's progress and remaining time, providing practical insight into DDL monitoring.
ALTER TABLEDDL MonitoringMySQL
0 likes · 4 min read

This guide walks through downloading, installing, and configuring MySQL Enterprise Monitor, explains the agent and agentless client architectures, and demonstrates how to use its overview, event, metric, and query‑analysis features for comprehensive MySQL performance monitoring.
Enterprise MonitorMySQLdatabase monitoring
0 likes · 8 min read

This article explains why MySQL connections can fail even when the concurrent client count is below max_connections, by analyzing the max_connect_errors parameter, its interaction with the host cache, and how to resolve the issue using flush hosts and proper configuration.
MySQLTCP handshakeconnection errors
0 likes · 7 min read

This article explains MySQL's metadata lock mechanism and demonstrates how to use the ext.check.get_locks() function from the MySQL Shell plugin—along with a supporting SQL query—to list lock owners, statements, and lock types, including setup steps for MySQL 5.7.
CTEMetadata LockMySQL
0 likes · 3 min read

This article explains how to monitor and estimate the progress of a massive MySQL UPDATE by observing rows_examined in performance_schema, deriving a progress formula, and adjusting the coefficient based on whether primary keys or only data columns are modified.
MySQLProgress MonitoringUpdate
0 likes · 3 min read

This article explains the causes of MySQL InnoDB row lock wait timeout errors, distinguishes row and metadata lock waits, and provides practical methods—including manual replication, monitoring scripts, general_log analysis, and Performance Schema queries—to locate, diagnose, and resolve such locking issues.
InnoDBMySQLlock_wait_timeout
0 likes · 18 min read

Through a hands‑on experiment, this article demonstrates how MySQL’s internal temporary tables, governed by tmp_table_size and max_heap_table_size, transition from in‑memory heap storage to on‑disk storage, showing the resulting I/O behavior, disk usage statistics, and the impact of engine configuration.
Disk SpillMySQLTemporary tables
0 likes · 5 min read

The article explains the shortcomings of traditional MySQL replication lag measurement, introduces the community heartbeat‑table approach and its limitations, and then details MySQL 8 enhancements (WL#7319, WL#7374) that embed transaction timestamps in binlog events and expose new performance_schema tables, providing precise SQL queries to monitor lag at various stages of the replication pipeline.
GTIDLag MonitoringMySQL
0 likes · 8 min read

This article summarizes a MySQL webinar covering cost‑based query optimization, monitoring and analysis tools, data‑access methods and index selection, join optimization techniques, and ways to influence the optimizer through hints and configuration, providing a concise technical reference for database practitioners.
Index SelectionMySQLOptimizer Hints
0 likes · 11 min read

This article explains how the MySQL 8.0 CLONE plugin simplifies creating reliable replica instances by installing the plugin, granting necessary privileges, executing the CLONE SQL statement, monitoring progress via performance_schema, and verifying successful completion of all clone stages.
CloneDatabase ReplicationInnoDB
0 likes · 12 min read

Starting with MySQL 8.0.16, Group Replication introduces an automatic member rejoin feature that allows expelled or disconnected nodes to attempt reconnection without manual intervention, configurable via the group_replication_autorejoin_tries variable, with monitoring via Performance Schema and trade‑offs compared to expel timeout.
Auto RejoinGroup ReplicationHigh Availability
0 likes · 11 min read

This article explains how to create a lightweight MySQL tool that extracts connection details from information_schema.processlist, groups them by user, host, database, command and state, fingerprints SQL statements, and correlates them with InnoDB transaction data to quickly pinpoint performance problems.
Connection AnalysisIncident DiagnosisInnoDB
0 likes · 12 min read

By enabling MySQL's Performance Schema and integrating its comprehensive SQL execution metrics into the open‑source myawr tool, this guide shows how to collect, store, and visualize full‑query statistics—beyond slow‑log—through table design, data ingestion, and multi‑dimensional reporting.
MySQLSQL statisticsmyawr
0 likes · 11 min read


ITPUBMar 19, 2017 · Databases
This article walks through a real‑world MySQL 5.7 memory‑leak case, showing how to use performance_schema, pmap, and thread analysis to identify runaway threads and fix the issue by closing unclosed cursors.
Memory LeakMonitoring PluginMySQL
0 likes · 6 min read

This article introduces MySQL 5.7's sys schema, explains its purpose and components—including views, functions, and stored procedures—demonstrates practical queries for redundant indexes, unused indexes, and full table scans, and discusses potential future extensions.
MySQLSQLdatabase monitoring
0 likes · 8 min read

This article examines how enabling MySQL 5.6 performance_schema with extremely high max_connections causes excessive virtual memory usage, swap pressure, and eventual out‑of‑memory errors, presents test data, references the related bug, and offers practical recommendations and discussion questions.
LinuxMySQLOOM
0 likes · 5 min read
