0 views collected around this technical thread.
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.
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.
This article examines how INSERT, REPLACE and INSERT … ON DUPLICATE KEY UPDATE statements can cause deadlocks in MySQL InnoDB under various scenarios, explains the underlying lock types such as GAP, NEXT‑KEY and INSERT‑INTENTION locks, and offers practical recommendations to avoid them.
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.
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.
This article explains how to identify when a MySQL DDL operation is blocked, locate the blocking session using the sys.schema_table_lock_waits view, and resolve the issue by enabling necessary instruments and killing the appropriate connection, with examples for MySQL 5.7 and 8.0.
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.
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.
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.
This article explains how the default MySQL SHOW PROCESSLIST command can create a global mutex that slows down busy systems, demonstrates the problem with slow INSERTs reproduced via mysqlslap, and shows how enabling the performance_schema_show_processlist variable in MySQL 8.0.22+ eliminates the contention while providing best‑practice recommendations.
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.
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.
This article explains how to diagnose a sudden rise in MySQL file handle usage by reproducing the issue with large tables, using Linux perf to trace open system calls, analyzing hash join behavior, and querying performance_schema.file_instances to pinpoint the underlying cause.
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.
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.
The article investigates a MySQL 8.0.18 replication deadlock caused by numerous set global read_only commands, explains the lock‑waiting chain involving global read and commit locks, reproduces two deadlock scenarios, and proposes practical solutions such as killing offending sessions or disabling slave_preserve_commit_order.
This article explains why MySQL 8.0 appears to lose about 1 GB of disk space when temporary tables are used, detailing the experiment, the role of TempTable, mmap allocation, and how performance_schema and procfs can be used to observe the process.
The article investigates a MySQL replication issue where a continuously rising master‑slave lag was traced to a global read lock held by mysqldump, leading to a deadlock between the SQL thread and the stop‑slave operation, and explains how killing the lock‑waiting thread restores normal replication.
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.
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.