SoFunction
Updated on 2025-04-14

Three solutions to add fields for MySQL billion-level large tables

Risk assessment of ALTER in the 100 million scale

1.1 Potential issues with direct execution of ALTER

ALTER TABLE `orders` ADD COLUMN `is_priority` TINYINT NULL DEFAULT 0;
  • Lock table time estimate (empirical value):
    • MySQL 5.6: About 2-6 hours (complete blockage)
    • MySQL 5.7+: 10-30 minutes (short blocking write)
  • Business Impact:
    • All read and write requests timed out
    • Connection pool exhausted (Too many connections）
    • May trigger high availability switches (such as MHA)

1.2 Key indicator inspection

-- Check table size（GB）
SELECT 
    table_name, 
    ROUND(data_length/1024/1024/1024,2) AS size_gb
FROM information_schema.tables 
WHERE table_schema = 'your_db' AND table_name = 'orders';

-- Check the current long transaction
SELECT * FROM information_schema.innodb_trx 
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

2. Comparison of three security solutions

plan tool Execution time Blocking situation Applicable version Complexity
Online DDL Native MySQL 30min-2h Short blocking writing 5.7+ ★★☆
pt-osc Percona Toolkit 2-4h Zero blockage All versions ★★★
gh-ost GitHub 1-3h Zero blockage All versions ★★★★

3. Solution 1: MySQL native Online DDL (5.7+)

3.1 Optimal execution of commands

ALTER TABLE `orders` 
ADD COLUMN `is_priority` TINYINT NULL DEFAULT 0,
ALGORITHM=INPLACE, 
LOCK=NONE;

3.2 Monitor progress (sessment is held separately)

-- Check DDL state
SHOW PROCESSLIST;

-- Check InnoDB Operation progress
SELECT * FROM information_schema.innodb_alter_table;

3.3 Estimated execution time (empirical formula)

time(min) = Table size(GB) × 2 + 10
  • Assume the table size is 50GB → about 110 minutes

4. Plan 2: pt-online-schema-change practical combat

4.1 Installation and execution

# Install Percona Toolkitsudo yum install percona-toolkit

# Perform changes (automatically create triggers)pt-online-schema-change \
--alter "ADD COLUMN is_priority TINYINT NULL DEFAULT 0" \
D=your_db,t=orders \
--chunk-size=1000 \
--max-load="Threads_running=50" \
--critical-load="Threads_running=100" \
--execute

4.2 Key parameter description

parameter effect Recommended value (billion-level table)
--chunk-size Number of rows copied each time 500-2000
--max-load Automatic pause threshold Threads_running=50
--critical-load Forced abort threshold Threads_running=100
--sleep Batch interval time 0.5 (seconds)

4.3 Java application compatibility processing

// During the trigger's active period, repeated primary key exceptions need to be handledtry {
    (newOrder);
} catch (DuplicateKeyException e) {
    // Automatic retry or downgrade logic    (newOrder);
}

5. Solution 3: Advanced usage of gh-ost

5.1 Execute commands (no trigger required)

gh-ost \
--database="your_db" \
--table="orders" \
--alter="ADD COLUMN is_priority TINYINT NULL DEFAULT 0" \
--assume-rbr \
--allow-on-master \
--cut-over=default \
--execute

5.2 Core Advantages

  • No trigger design: avoid performance loss
  • Dynamic current limit: automatically adapt to server load
  • Interactive control: Supports pause/resume
# Runtime Controlecho throttle | nc -U /tmp/
echo no-throttle | nc -U /tmp/

6. Java application layer adaptation strategy

6.1 Double write compatibility mode (recommended)

// Write new and old fields at the same time during the changepublic void createOrder(Order order) {
    (0); // New field default value    (order);
    
    // Compatible with old code    if (order.getV2() == null) {
        ((), 0);
    }
}

6.2 Dynamic SQL routing

<!-- MyBatis Dynamic field mapping -->
<insert >
    INSERT INTO orders 
    (id, user_id, amount
    <if test="isPriority != null">, is_priority</if>)
    VALUES
    (#{id}, #{userId}, #{amount}
    <if test="isPriority != null">, #{isPriority}</if>)
</insert>

7. Monitoring and rollback schemes

7.1 Real-time monitoring indicators

# Monitor replication delay (master-slave architecture)pt-heartbeat --monitor --database=your_db

# Check gh-ost progresstail -f

7.2 Emergency rollback steps

# pt-osc rollback (automatically clean up temporary tables)pt-online-schema-change --drop-new-table --alter="..." --execute

# gh-ost Rollbackgh-ost --panic-on-failure --revert

8. Summary of suggestions

  1. Preferred plan:

    • MySQL 8.0 → NativeALGORITHM=INSTANT(Completed in seconds)
    • MySQL 5.7 → gh-ost(No trigger influence)

  2. Execution window:

    • Select the lowest time period for business traffic (such as 2-4 am)
    • Notify the business party in advance to prepare a downgrade plan

  3. Verification process:

-- Check data consistency after change
SELECT COUNT(*) FROM orders WHERE is_priority IS NULL;
  • Follow-up optimization:
-- After the addition is completed, it can be changed to NOT NULL
ALTER TABLE orders 
MODIFY COLUMN is_priority TINYINT NOT NULL DEFAULT 0;

Through reasonable selection of tool + application layer adaptation, even tables with 135 million data can be added with zero-aware fields.

The above is the detailed content of three solutions for safe adding fields for MySQL billion-level large tables. For more information about adding fields for MySQL large tables, please pay attention to my other related articles!