When a tens‑million‑row order table needs a new business column, directly running ALTER TABLE can lock the table and disrupt services, so the article explores master‑slave promotion, online DDL tools, schema‑less JSON extensions, and a clever use of an existing redundant column to achieve the change safely.
This guide breaks down a real‑world interview scenario where a backend engineer must design a scalable database solution for a billing‑log or feed system handling 100 million daily inserts and 100 k QPS reads, covering partitioning vs sharding, sharding key selection, shard count, read‑write separation, multi‑level caching, consistency patterns, hot‑key mitigation, and online schema changes.
When a core order table with tens of millions of rows needs a new business field, executing a plain ALTER TABLE can lock the table and disrupt services, so the article explores master‑slave switching, online schema‑change tools, extension tables, JSON fields, and a clever reuse of an existing redundant column to achieve the change with minimal risk.
When a core order table with tens of millions of rows needs a new field, a naive ALTER TABLE can lock the table and disrupt services, so this article explores master‑slave switching, online DDL tools, schema‑less extensions, and other low‑cost strategies to add the column safely.
When a core order table with tens of millions of rows needs a new column, naïve ALTER TABLE can lock the table and cause outages, so this article explores master‑slave switching, online schema‑change tools, JSON or redundant fields, and practical tips for low‑risk, production‑grade schema evolution.
When a core MySQL order table reaches tens of millions of rows, adding a new column can lock the table and disrupt services, so the article walks through master‑slave switching, online DDL tools, log‑based workarounds, extension tables, JSON fields, and a final redundant‑field trick to achieve the change safely.
This article explains how to handle MySQL disk‑space alerts by using the pt‑online‑schema‑change (pt‑osc) tool to selectively retain needed rows, migrate data to a temporary table, and drop the original table, thereby achieving fast, low‑impact data cleanup.
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 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 explains how StarRocks manages petabyte‑level user behavior logs, ads and orders through a shared‑nothing architecture, tablet‑based data distribution, MPP compute, high‑availability metadata, real‑time mini‑batch ingestion, and online schema changes, enabling 24/7 analytical services for diverse internet companies.
This article introduces the gh-ost tool for performing online schema changes on large MySQL tables, explains its architecture and operation modes, provides detailed command‑line parameters and practical examples, and compares its features and performance with the traditional pt‑osc solution.
The article explains how distributed databases can perform online, asynchronous schema changes without blocking reads or writes by using lease‑based coordination, intermediate delete‑only and write‑only states, and a multi‑step algorithm inspired by Google F1 and implemented in CB‑SQL.
This article explains why MySQL auto‑increment primary keys can reach their limit, shows how switching from INT to BIGINT prevents overflow, and reviews three online schema‑change methods—including native ALTER, pt‑osc, and gh‑ost—plus considerations for triggers, foreign keys, and master‑slave setups.
This article explains the detailed steps of MySQL ALTER operations, compares single‑instance and master‑slave scenarios, introduces various online schema‑change tools such as pt‑OSC, outlines native MySQL OSC support, discusses limitations, and provides practical recommendations for safely performing large‑table DDL.
gh‑ost is a lightweight, trigger‑free, Go‑based online MySQL schema‑change tool that reads binary logs, applies alterations to temporary tables, and offers pauseable, auditable, and dynamically controllable migrations via socket commands, supporting master‑only, replica‑assisted, or replica‑testing modes and used extensively at GitHub.
Learn a step-by-step method to safely alter large MySQL tables in production without locking, by creating a new table, syncing data via triggers, copying rows, swapping tables, and using Percona’s pt-online-schema-change tool with example commands and installation instructions.
