1 views collected around this technical thread.
An experienced DBA explains why adding a new column in MySQL can unexpectedly raise duplicate entry errors during online DDL, detailing the role of primary and unique keys, the innodb_online_alter_log_max_size setting, and how concurrent DML operations cause the conflict.
This article explains foreign key concepts in relational databases, showing how to create, modify, and drop foreign key constraints with SQL code, provides hands‑on exercises, discusses the pros and cons of using foreign keys, and outlines when they are appropriate or should be avoided.
While MySQL 5.7 native DDL methods (copy, inplace rebuild, index build, metadata‑only) vary in speed, space use, lock impact, and binlog output, GH‑OST consistently offers the fastest execution, lowest lock blocking, real‑time replication, albeit at roughly double storage cost, making it ideal for risk‑averse online schema changes.
This article explains InnoDB lock concepts, differentiates read and write locks, discusses table‑level versus row‑level locking, and examines how MySQL 5.6 and 8.0 handle adding columns without fully locking tables, highlighting online DDL, atomic DDL, and performance considerations.
This article thoroughly explains how the Percona pt-osc tool performs online schema changes in MySQL, analyzes trigger design, deadlock scenarios, data‑consistency issues, and provides detailed test cases, operational tips, and best‑practice recommendations for safe usage.
This article explains MySQL index structures, covers covered and back‑table queries, details InnoDB transaction isolation levels, classifies various lock types (table, row, gap, next‑key, intention, auto‑inc, metadata), analyzes lock behavior in multiple scenarios, discusses deadlock causes, and provides DDL and online schema‑change recommendations.
This article explains the differences between MySQL DDL and DML, describes how online DDL works—including copy and inplace algorithms, execution phases, common pitfalls, limitations, and recent MySQL 8.0 enhancements—providing practical guidance for safely altering large tables.
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 the challenges of slow DDL operations in MySQL, introduces TXSQL’s Parallel DDL framework—including Copy, Inplace, and Instant algorithms—details their design, implementation, code examples, and performance evaluations, and compares them with other database solutions.
This article details a comprehensive approach to optimizing a large MySQL table, including slow‑query analysis with pt‑query‑digest, index redesign, backup and restore using mydumper, online DDL versus pt‑osc, and batch deletion techniques to reduce latency and storage fragmentation.
This article analyzes performance problems of a massive MySQL table, demonstrates how to use pt‑query‑digest and EXPLAIN to identify inefficient composite indexes, proposes index redesign, compares online DDL with pt‑osc for index rebuilding, and presents batch‑delete techniques to reduce SLA‑impacting latency.
This article details a comprehensive performance investigation of a massive MySQL table, using pt‑query‑digest to analyze slow queries, exposing index inefficiencies, proposing index redesign, demonstrating backup/restore with mydumper, comparing online DDL and pt‑osc for index rebuilding, and recommending small‑batch deletions to reduce latency and maintenance overhead.
This article analyzes a MySQL large‑table performance issue caused by slow queries and full‑table scans, demonstrates using pt‑query‑digest, explains execution plans, proposes index redesign, shows backup and restore with mydumper, compares online DDL and pt‑osc, and presents batch delete strategies to reduce latency.
This article details a comprehensive analysis of a large MySQL table's performance issues, including slow query diagnostics with pt‑query‑digest, index evaluation, backup and restore procedures, online DDL versus pt‑osc, and practical strategies for index redesign and batch deletions to reduce latency and maintenance overhead.
The article explains the strict GTID‑based replication limits on CREATE TABLE … SELECT and temporary‑table statements in MySQL 5.6/5.7, demonstrates practical work‑arounds such as splitting statements or using CREATE TABLE LIKE, and shows how MySQL 8.0’s native DDL atomicity eliminates these constraints.
This article introduces and compares three MySQL online schema‑change solutions—pt‑online‑schema‑change, gh‑ost, and native MySQL ONLINE DDL—detailing their workflows, limitations, risks, monitoring features, and practical considerations for safely altering large tables in production environments.
This article analyses why physical backups using Xtrabackup fail when MySQL 5.7 performs online DDL operations that skip redo logging, demonstrates the issue with real‑world logs, and presents four practical solutions—including lock‑DDL parameters, per‑table MDL locks, and disabling online DDL—to ensure consistent backups.
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.
This article evaluates the open‑source SQLE SQL auditing platform against MySQL 8.0.28, demonstrating how DDL, DML, DCL statements and their contextual relationships are checked, highlighting successful detections, mismatches caused by new MySQL 8.0 features, and concluding on overall compatibility.
This article explains how DTLE supports pt-osc table schema changes by describing pt-osc's underlying mechanism, DTLE's DDL capabilities, and step‑by‑step instructions for deploying DTLE, preparing MySQL data, configuring a DTLE job, running pt‑online‑schema‑change, and verifying successful synchronization.