Why deleting records doesn’t free disk space

Why you should care — performance & cost impact

Diagnosing your table — is it worth reclaiming?

Before scheduling any work, run these queries to understand your starting point. A table under 15% fragmentation generally doesn’t justify the risk and effort of a live rebuild.

Step 1 — Find your most fragmented tables

-- Lists all tables sorted by fragmentation percentage
SELECT
  table_schema                                         AS db_name,
  table_name,
  ROUND(data_length  / 1024 / 1024, 2)              AS data_mb,
  ROUND(index_length / 1024 / 1024, 2)             AS index_mb,
  ROUND(data_free    / 1024 / 1024, 2)              AS free_mb,
  ROUND(
    data_free / (data_length + index_length) * 100, 1
  )                                                    AS frag_pct
FROM  information_schema.tables
WHERE table_schema = 'your_database_name'
  AND   engine       = 'InnoDB'
ORDER BY frag_pct DESC
LIMIT 20;

Step 2 — Estimate rebuild time

-- Row count and physical size for a specific table
SELECT
  table_rows,
  ROUND((data_length + index_length) / 1073741824, 3) AS total_gb,
  ROUND(data_free               / 1073741824, 3) AS reclaimable_gb,
  create_time,
  update_time
FROM  information_schema.tables
WHERE table_schema = 'your_database_name'
  AND   table_name   = 'your_table_name';

Step 3 — Check for foreign key dependencies

-- Tables that reference your target table (child tables)
SELECT
  kcu.table_name          AS child_table,
  kcu.column_name         AS fk_column,
  kcu.constraint_name,
  rc.update_rule,
  rc.delete_rule
FROM  information_schema.key_column_usage kcu
JOIN  information_schema.referential_constraints rc
  ON  rc.constraint_name   = kcu.constraint_name
  AND rc.constraint_schema = kcu.constraint_schema
WHERE kcu.referenced_table_name   = 'your_table_name'
  AND   kcu.referenced_table_schema = 'your_database_name';

Pre-flight checklist — do this before running anything

• ✓
  Verify Aurora storage headroom — Check CloudWatch metric FreeStorageSpace. You need at least 1× the table’s total size free. pt-osc creates a full shadow copy.
• ✓
  Confirm you’re on the Writer endpoint — Run SELECT @@read_only; — must return 0. Reader endpoints reject DDL and will cause the tool to fail mid-copy.
• ✓
  Check Aurora replica lag baseline — Note your current AuroraReplicaLag in CloudWatch. If lag is already elevated before you start, wait — the rebuild will amplify it.
• ✓
  Run a dry-run first — Both pt-osc and gh-ost support --dry-run mode. Run it first to catch permission errors, FK issues, and missing binlog access before committing.
• ✓
  Create an Aurora snapshot — Take a manual snapshot before starting. Aurora snapshots are nearly instant. This is your rollback if something catastrophic happens.
• ✓
  Notify your team & schedule off-peak — Even though the table stays live, the rebuild increases CPU and I/O load. Run it during your lowest-traffic window.
• ✓
  Verify MySQL user permissions — The executing user needs: ALTER, CREATE, DROP, INSERT, UPDATE, DELETE, SELECT, TRIGGER, REPLICATION SLAVE on the target database.
• Test on a non-production clone first — Aurora makes cloning trivial. Restore from snapshot to a test cluster and do a full rehearsal including timing.

Choosing your tool — a complete comparison

Method 1 — pt-online-schema-change, step by step

1. Install Percona Toolkit on your bastion or ops server

   pt-osc does not run inside Aurora — it runs on an EC2 instance or your local machine and connects to Aurora over the network.

   Bash · Amazon Linux 2

   # Add the Percona yum repository
   sudo yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
   sudo percona-release setup pt
   sudo yum install -y percona-toolkit
   
   # Verify installation
   pt-online-schema-change --version
   # → pt-online-schema-change 3.x.x

2. Run a dry-run to validate everything

   A dry-run performs all checks — permissions, FK detection, table structure analysis — without making any changes. Always run this first.

   Bash · pt-osc dry-run

   pt-online-schema-change \
     --alter  "ENGINE=InnoDB"        \
     --dry-run                    \
     --print                      \
     D=your_database,t=your_table  \
     h=your-aurora-writer.cluster-xxxx.region.rds.amazonaws.com \
     u=admin,p=your_password

   💡

   Look for the line “Would have used X triggers” in the output. If you see errors about missing privileges or FK issues, fix them before proceeding.

3. Run the live operation with throttling controls

   This is the full production command. Break it down flag by flag below.

   Bash · Production run (inside a screen or tmux session)

   pt-online-schema-change \
     --alter               "ENGINE=InnoDB"           \
     --execute                                         \
     --chunk-size          1000                        \
     --chunk-size-limit    4                           \
     --max-load           "Threads_running=50"       \
     --critical-load      "Threads_running=100"      \
     --max-lag             5                           \
     --pause-file          /tmp/pt-osc-pause.flag       \
     --alter-foreign-keys-method auto                  \
     --no-drop-old-table                               \
     --print                                           \
     --progress            time,30                     \
     D=your_database,t=your_table                      \
     h=aurora-writer-endpoint \
     u=admin,p=secret

   Flag-by-flag explanation

   Flag	What it does	Recommended value
   --chunk-size	Rows copied per transaction batch	Start at 500–1000; increase if lag stays low
   --max-load	Pauses copying if Aurora exceeds this load	Threads_running=50 (adjust for your instance)
   --critical-load	Aborts the operation at this load threshold	Threads_running=100
   --max-lag	Pauses if Aurora replica lag exceeds N seconds	5 — protect read replicas
   --pause-file	Pauses immediately if this file exists on disk	Any path — touch the file to pause, delete to resume
   --no-drop-old-table	Keeps the original table as a backup after rename	Always use this on first run
   --alter-foreign-keys-method	How to handle FK constraints on child tables	auto — pt-osc picks the safest method
   --progress	Print progress every N seconds	time,30 — status every 30 seconds

4. Emergency pause — if you need to stop immediately

   Bash · Pause and resume

   # Pause the copy immediately (pt-osc checks this file every second)
   touch /tmp/pt-osc-pause.flag
   
   # Resume by removing the pause file
   rm /tmp/pt-osc-pause.flag
   
   # Hard stop (Ctrl+C or kill) — safe to do, the shadow table is just dropped
   kill $(pgrep -f pt-online-schema-change)

   🚨

   If pt-osc is killed mid-run

   The original table is untouched — it’s still running normally. pt-osc will have left behind a shadow table (_tablename_new) and three triggers (pt_osc_*). Clean them up manually: DROP TABLE IF EXISTS _tablename_new; and DROP TRIGGER IF EXISTS pt_osc_ins_tablename; etc. before restarting.

5. Verify the reclaim succeeded

   SQL · Post-run verification

   -- Compare before vs after
   SELECT
     table_name,
     ROUND((data_length + index_length) / 1073741824, 3) AS total_gb,
     ROUND(data_free               / 1073741824, 3) AS free_gb,
     ROUND(data_free / (data_length + index_length) * 100, 1) AS frag_pct
   FROM  information_schema.tables
   WHERE table_schema = 'your_database_name'
     AND   table_name IN ('your_table', '_your_table_old');
   
   -- Confirm row counts match
   SELECT COUNT(*) FROM your_table;
   
   -- Once confirmed, drop the old backup table
   DROP TABLE IF EXISTS _your_table_old;

Method 2 — gh-ost (GitHub Online Schema Tool)

gh-ost is GitHub’s alternative to pt-osc. Instead of triggers, it tails the MySQL binary log to capture changes — this reduces write amplification and makes it safer for very high-throughput tables. The tradeoff is more complex Aurora setup.

1. Install gh-ost

   Bash · Install from GitHub releases

   # Download the latest binary
   wget https://github.com/github/gh-ost/releases/latest/download/gh-ost-binary.tar.gz
   tar -xzf gh-ost-binary.tar.gz
   sudo mv gh-ost /usr/local/bin/
   
   gh-ost --version
   # → gh-ost version 1.x.x

2. Dry-run to validate Aurora connectivity and binlog access

   Bash · gh-ost dry-run

   gh-ost \
     --mysql-port=3306 \
     --host=aurora-writer-endpoint \
     --user=admin \
     --password=secret \
     --database=your_database \
     --table=your_table \
     --alter="ENGINE=InnoDB" \
     --allow-on-master \
     --switch-to-rbr \
     --test-on-replica=false \
     --dry-run

3. Execute the live migration

   Bash · gh-ost production run

   gh-ost \
     --mysql-port=3306                                              \
     --host=aurora-writer-endpoint                                    \
     --user=admin                                                     \
     --password=secret                                               \
     --database=your_database                                        \
     --table=your_table                                              \
     --alter="ENGINE=InnoDB"                                        \
     --chunk-size=1000                                               \
     --max-load="Threads_running=50"                               \
     --critical-load="Threads_running=100"                          \
     --max-lag-millis=5000                                          \
     --allow-on-master                                               \
     --switch-to-rbr                                                 \
     --serve-socket-file=/tmp/gh-ost.sock                           \
     --panic-flag-file=/tmp/gh-ost-panic.flag                       \
     --postpone-cut-over-flag-file=/tmp/gh-ost-postpone.flag        \
     --execute

   Runtime controls via socket

   Bash · gh-ost runtime commands

   # Check status while running
   echo status | nc -U /tmp/gh-ost.sock
   
   # Throttle down chunk size live (no restart needed)
   echo "chunk-size=500" | nc -U /tmp/gh-ost.sock
   
   # Postpone the final cut-over (table rename) until you're ready
   touch /tmp/gh-ost-postpone.flag
   # Remove file when ready to complete the cut-over
   rm /tmp/gh-ost-postpone.flag
   
   # Emergency abort
   touch /tmp/gh-ost-panic.flag

Monitoring during the rebuild — CloudWatch & MySQL metrics

Key CloudWatch metrics to watch in real time

Live progress query inside MySQL

-- Show what pt-osc is currently doing
SHOW PROCESSLIST;

-- Check the shadow table's row count vs original (progress estimate)
SELECT
  (  SELECT COUNT(*) FROM your_table          ) AS original_rows,
  (  SELECT COUNT(*) FROM _your_table_new     ) AS shadow_rows,
  ROUND(
    (  SELECT COUNT(*) FROM _your_table_new   ) /
    (  SELECT COUNT(*) FROM your_table        ) * 100, 1
  )                                                AS pct_complete;

-- Check for trigger presence (confirms pt-osc is running)
SHOW TRIGGERS LIKE 'your_table';

What a healthy run looks like — phase by phase

Quick reference — the complete playbook