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.
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 use MySQL's native SHOW commands and performance_schema tables to collect comprehensive monitoring metrics—including connections, buffer cache, locks, SQL activity, throughput, and slow queries—while minimizing overhead in a single‑node environment.
This guide explains how to gather extensive MySQL monitoring data—including connections, buffer cache, locks, SQL activity, statement counts, throughput, server variables, and slow‑query analysis—solely with MySQL's native SHOW statements, providing low‑overhead, real‑time insight for database administrators.
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.
Learn how to identify when a MySQL DDL statement is blocked, use SHOW PROCESSLIST and the sys.schema_table_lock_waits view to pinpoint the blocking session, and apply targeted KILL commands or transaction queries to safely unblock operations in both MySQL 5.7 and 8.0.
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.
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 locate a MySQL statement that holds a lock but does not appear in SHOW PROCESSLIST, by enabling performance_schema, examining INNODB_LOCK_WAITS, INNODB_LOCKS, INNODB_TRX, and using performance_schema.threads and events_statements_current to retrieve the blocking SQL.
This article explains the evolution of MySQL parallel replay from schema‑based MTS to logical‑clock and write‑set mechanisms, shows how to configure and tune parallel replication, and introduces a Jain‑index based fairness metric for evaluating the optimal number of slave_parallel_workers.
This article demonstrates a step‑by‑step experiment using sysbench and performance_schema to measure how different values of innodb_buffer_pool_instances affect buffer‑pool lock latency, analyzes the collected data, and discusses the observed trends and limitations.
Through a series of experiments using MySQL's performance_schema and explicit MEMORY tables, this article demonstrates how internal temporary tables consume memory, reveals that they allocate significantly more space than raw data size, and discusses the implications for accurate memory estimation.
This article demonstrates how to reproduce disk‑IO saturation in MySQL, enable performance_schema instruments and consumers, apply load, and use sys.x$latest_file_io together with events_waits_history_long to pinpoint whether binlog, redo log, or specific tables are causing the slowdown.
This article explains how MySQL 8.0 introduces the SET PERSIST and SET PERSIST_ONLY statements to modify configuration variables dynamically and persist them across restarts, demonstrates usage with both dynamic and static parameters, shows the generated mysqld-auto.cnf file, discusses required privileges, and illustrates how to query persisted settings via performance_schema tables.
This article explains several practical techniques—including using the performance_schema.metadata_locks view, events_statements_history, a gdb script, and filtered SHOW PROCESSLIST queries—to locate and terminate the MySQL session that holds a global read lock, thereby restoring normal write operations.
This article explains the two types of MySQL temporary tables, the Memory and user‑defined engines, how to create them, monitor their usage with status variables, and the new TempTable engine introduced in MySQL 8.0 along with relevant configuration parameters.
