Schema change without locking.

Schema change without locking.

Performing schema changes on production databases comes with significant overhead, such as locking entire tables and potentially causing replication lag in master-slave architectures. For MySQL and its variants, there's a powerful tool that mitigates these issues: pt-online-schema-change.

What is pt-online-schema-change?

pt-online-schema-change is a tool that alters a table's structure without blocking reads or writes, making it ideal for use in production environments.

How It Works

  1. Table Copying: It creates an empty copy of the table with the desired alterations.

  2. Data Migration: It copies rows from the original table to the new one in small, manageable chunks.

  3. Real-time Updates: During the copy process, any modifications to the original table are reflected in the new table using triggers.

  4. Atomic Swap: Once copying is complete, it uses an atomic RENAME TABLE operation to switch the old and new tables.

  5. Cleanup: Finally, it drops the original table.

Key Features and Safety Measures

  • Chunk-based Processing: Data is copied in small chunks, optimized for performance (configurable with --chunk-time).

  • Trigger Mechanism: Ensures data consistency during the migration process.

  • Foreign Key Handling: Supports methods to update foreign key references after the schema change.

  • Safety Checks:

    • Requires a PRIMARY KEY or UNIQUE INDEX in most cases.

    • Detects and respects replication filters.

    • Pauses operations if replicas lag behind.

    • Monitors server load and can pause or abort if thresholds are exceeded.

    • Sets conservative lock wait timeouts to minimize disruption.

    • Careful handling of foreign key constraints.

Considerations

  • By default, it doesn't modify the table unless the --execute option is specified.

  • It may rename foreign keys and indexes slightly to avoid naming collisions.

  • Existing triggers on the table will prevent the tool from working.

Example Usage

#!/bin/bash

USERNAME="root"
PASSWORD="your_password_here"
HOST="localhost"
DATABASE="critical_db"
TABLE="critical_table"
ALTER_STATEMENT="ADD COLUMN guest VARCHAR(45), ADD COLUMN name VARCHAR(100)"

if [[ "$1" == "--execute" ]]; then
    EXECUTE=true
else
    EXECUTE=false
fi

echo "Performing dry run..."
pt-online-schema-change \
    --user="$USERNAME" \
    --password="$PASSWORD" \
    --host="$HOST" \
    D="$DATABASE",t="$TABLE" \
    --alter="$ALTER_STATEMENT" \
    --preserve-triggers \
    --dry-run

if [ "$EXECUTE" = true ]; then
    echo "Applying changes..."
    pt-online-schema-change \
        --user="$USERNAME" \
        --password="$PASSWORD" \
        --host="$HOST" \
        D="$DATABASE",t="$TABLE" \
        --alter="$ALTER_STATEMENT" \
        --preserve-triggers \
        --execute
fi

To use this script:

  1. Save it to a file (e.g., alter_table.sh)

  2. Make it executable: chmod +x alter_table.sh

  3. Run a dry run: ./alter_table.sh

  4. Execute the changes: ./alter_table.sh --execute

Conclusion

pt-online-schema-change offers a robust solution for performing schema changes in production environments, minimizing downtime and reducing the risks associated with traditional ALTER TABLE operations.

Did you find this article valuable?

Support Cosmicoppai by becoming a sponsor. Any amount is appreciated!