Regular table maintenance adalah aspek penting dari database administration yang sering diabaikan. Setelah melihat performance degradation di production akibat fragmented tables, saya memahami pentingnya maintenance rutin.

Mengapa Table Maintenance Penting?

Benefits Maintenance

• Data integrity: Detect dan fix corruption
• Performance: Defragment tables untuk faster queries
• Storage efficiency: Reclaim wasted space
• Index optimization: Rebuild untuk optimal queries
• Statistics update: Fresh statistics untuk query optimizer

Signs Tables Need Maintenance

• Queries lambat pada tables specific
• Increased disk usage tanpa data growth
• Table corruption errors
• Outdated statistics

CHECK TABLE: Verifying Table Integrity

1. Basic Check

-- Check single table
CHECK TABLE users;
-- Check multiple tables
CHECK TABLE orders, order_items, products;
-- Check all tables dalam database
CHECK TABLE db1.users, db1.orders, db1.products;

2. Check Options

-- Quick check (tidak scan rows untuk errors)
CHECK TABLE users QUICK;
-- Fast check (hanya check incorrectly closed tables)
CHECK TABLE users FAST;
-- Medium check (check dan verify deleted links)
CHECK TABLE users MEDIUM;
-- Extended check (full row scan untuk errors)
CHECK TABLE users EXTENDED;
-- Changed check (hanya check sejak last check)
CHECK TABLE users CHANGED;

3. Interpreting Results

CHECK TABLE users;
-- Possible statuses:
-- OK: Table is fine
-- Table is already up to date: No check needed
-- Corrupt: Table needs repair

OPTIMIZE TABLE: Defragmentation dan Statistics

1. When to Optimize

• Setelah DELETE banyak rows
• Setelah UPDATE yang mengubah row size
• Periodically untuk tables dengan banyak writes
• Sebelum major query optimization

2. Basic Optimize

-- Optimize single table
OPTIMIZE TABLE orders;
-- Optimize multiple tables
OPTIMIZE TABLE orders, order_items, inventory;
-- Optimize dengan ONLINE (MySQL 5.7+ InnoDB)
OPTIMIZE TABLE orders;
-- Secara otomatis menggunakan online DDL untuk InnoDB

3. What OPTIMIZE Does

Untuk InnoDB:
– Rebuilds table
– Reclaims unused space
– Updates index statistics
– Defragments clustered index

Untuk MyISAM:
– Defragments data file
– Reclaims unused space
– Sorts index pages
– Updates statistics

4. Monitoring Optimization Progress

-- Check process list selama optimize
SHOW PROCESSLIST;
-- Look untuk: State = 'optimizing'

REPAIR TABLE: Fixing Corruption

1. When to Use REPAIR

Gunakan REPAIR jika:
– CHECK TABLE menunjukkan corruption
– Queries menghasilkan errors aneh
– Table crash indicators

⚠️ Warning:
– Selalu backup sebelum repair
– Repair bisa menyebabkan data loss
– Gunakan sebagai last resort

2. Repair Options

-- Basic repair
REPAIR TABLE corrupted_table;
-- Quick repair (hanya fix index file)
REPAIR TABLE corrupted_table QUICK;
-- Extended repair (full row-by-row repair)
REPAIR TABLE corrupted_table EXTENDED;
-- Use FRM file untuk repair
REPAIR TABLE corrupted_table USE_FRM;

3. Repair Process untuk InnoDB

InnoDB tables tidak support REPAIR TABLE. Jika corrupt:

-- Option 1: Restore dari backup
-- Option 2: Dump dan recreate
-- Export data
mysqldump --no-create-info db_name corrupted_table > table_backup.sql
-- Drop dan recreate
DROP TABLE corrupted_table;
-- Recreate dengan CREATE TABLE
-- Import
mysql db_name < table_backup.sql

Automated Maintenance Scripts

1. Weekly Check Script

#!/bin/bash
# weekly_check.sh
DB_NAME="production"
LOG_FILE="/var/log/mysql_maintenance.log"
EMAIL="[email protected]"
echo "[$(date)] Starting weekly table check..." >> $LOG_FILE
Check all tables
mysql -e "
SELECT CONCAT('CHECK TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = '${DB_NAME}'
AND ENGINE IN ('InnoDB', 'MyISAM', 'Aria');
" | while read table; do
result=$(mysql -e "$table" 2>&1)
if echo "$result" | grep -q "Corrupt"; then
echo "ALERT: $table is corrupt" >> $LOG_FILE
echo "$result" | mail -s "[CRITICAL] Table Corruption Detected" $EMAIL
fi
done
echo "[$(date)] Weekly check completed" >> $LOG_FILE

2. Monthly Optimize Script

#!/bin/bash
# monthly_optimize.sh
DB_NAME="production"
LOG_FILE="/var/log/mysql_optimization.log"
Get tables larger than 100MB yang belum dioptimize dalam 30 hari
tables=$(mysql -e "
SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME)
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = '${DB_NAME}'
AND DATA_LENGTH > 104857600  -- > 100MB
AND ENGINE = 'InnoDB'
ORDER BY DATA_LENGTH DESC;
")
for table in $tables; do
echo "[$(date)] Optimizing $table..." >> $LOG_FILE
mysql -e "OPTIMIZE TABLE $table;" >> $LOG_FILE 2>&1
echo "[$(date)] Optimization of $table completed" >> $LOG_FILE
done

Maintenance Best Practices

1. Schedule

• Daily: Monitor untuk corruption indicators
• Weekly: CHECK TABLE untuk critical tables
• Monthly: OPTIMIZE untuk large tables
• Quarterly: Full maintenance window

2. Before Maintenance

-- 1. Backup
mysqldump --single-transaction db_name > backup.sql
-- 2. Check current status
CHECK TABLE table_name;
SHOW TABLE STATUS LIKE 'table_name';
-- 3. Monitor during
SHOW PROCESSLIST;

3. After Maintenance

-- 1. Verify
CHECK TABLE table_name;
-- 2. Update statistics
ANALYZE TABLE table_name;
-- 3. Verify performance
EXPLAIN SELECT * FROM table_name WHERE ...;

Monitoring Table Health

1. Table Status Query

-- Check table statistics
SHOW TABLE STATUS FROM db_name;
-- Detailed check
SELECT 
TABLE_NAME,
ENGINE,
TABLE_ROWS,
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,
DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) * 100 AS fragmentation_pct,
CREATE_TIME,
UPDATE_TIME,
CHECK_TIME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'db_name'
ORDER BY fragmentation_pct DESC;

2. Fragmentation Detection

-- Tables dengan high fragmentation
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    ENGINE,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
    ROUND(DATA_FREE / 1024 / 1024, 2) AS free_mb,
    ROUND(DATA_FREE / DATA_LENGTH * 100, 2) AS frag_pct
FROM information_schema.TABLES
WHERE DATA_FREE > 10485760  -- > 10MB free space
  AND DATA_LENGTH > 0
  AND ENGINE = 'InnoDB'
ORDER BY frag_pct DESC;

Kesimpulan

Regular maintenance menjaga database dalam kondisi optimal:
– CHECK untuk integrity
– OPTIMIZE untuk performance
– REPAIR untuk recovery
– Automation untuk consistency

Jadwalkan maintenance untuk menghindari performance degradation.