Database Maintenance Tasks — คู่มืองาน Maintenance ฐานข้อมูลประจำวัน สัปดาห์ และเดือน

Categories

ฐานข้อมูลที่ทำงานอยู่ในระบบ Production ต้องการการดูแลรักษาอย่างสม่ำเสมอ เหมือนกับรถยนต์ที่ต้องเปลี่ยนถ่ายน้ำมันเครื่องตามระยะ การปล่อยให้ฐานข้อมูลทำงานโดยไม่มีการบำรุงรักษาจะทำให้ประสิทธิภาพลดลงเรื่อย ๆ จนถึงจุดที่เกิดปัญหาร้ายแรง บทความนี้รวบรวมงาน Maintenance ที่ต้องทำเป็นประจำสำหรับ MySQL, PostgreSQL และ MongoDB พร้อมตัวอย่างคำสั่งและ Script สำหรับทำ Automation

งาน Maintenance รายวัน

ตรวจสอบสถานะฐานข้อมูล

สิ่งแรกที่ต้องทำทุกวันคือตรวจสอบว่าฐานข้อมูลทำงานปกติ ไม่มี Error ใน Log และไม่มี Process ค้าง การตรวจสอบเหล่านี้ใช้เวลาไม่กี่นาทีแต่ช่วยป้องกันปัญหาใหญ่ได้

# MySQL — ตรวจสอบสถานะ
mysqladmin -u root -p status
mysql -u root -p -e "SHOW PROCESSLIST;" | grep -v "Sleep"

# PostgreSQL — ตรวจสอบสถานะ
psql -U postgres -c "SELECT state, COUNT(*) FROM pg_stat_activity GROUP BY state;"
psql -U postgres -c "SELECT pid, now() - query_start AS duration, query
  FROM pg_stat_activity WHERE state != 'idle' AND now() - query_start > interval '5 minutes';"

# MongoDB — ตรวจสอบสถานะ
mongosh -u adminUser -p 'password' --authenticationDatabase admin --eval "
  print('Status:', db.serverStatus().ok);
  print('Connections:', db.serverStatus().connections.current);
  db.currentOp().inprog.filter(op => op.secs_running > 60).forEach(op =>
    print('Long running:', op.opid, op.secs_running, 's'));"

ตรวจสอบ Log Files

# MySQL — ดู Error ใน Log
sudo tail -100 /var/log/mysql/error.log | grep -i "error\|warning\|fatal"

# PostgreSQL — ดู Error ใน Log
sudo tail -100 /var/log/postgresql/postgresql-16-main.log | grep -i "error\|fatal\|panic"

# MongoDB — ดู Error ใน Log
sudo tail -100 /var/log/mongodb/mongod.log | grep -i '"s":"E"\|"s":"W"'

ตรวจสอบ Disk Space

# ตรวจพื้นที่ Disk โดยรวม
df -h /var/lib/mysql /var/lib/postgresql /var/lib/mongodb 2>/dev/null

# MySQL — ตรวจขนาดฐานข้อมูล
mysql -u root -p -e "
  SELECT table_schema AS db,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
  FROM information_schema.tables
  GROUP BY table_schema ORDER BY size_mb DESC;"

# PostgreSQL — ตรวจขนาดฐานข้อมูล
psql -U postgres -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
  FROM pg_database ORDER BY pg_database_size(datname) DESC;"

# MongoDB — ตรวจขนาดฐานข้อมูล
mongosh -u adminUser -p 'password' --authenticationDatabase admin --eval "
  db.adminCommand('listDatabases').databases.forEach(d =>
    print(d.name, ':', (d.sizeOnDisk/1024/1024).toFixed(2), 'MB'));"

ตรวจสอบ Backup

ตรวจสอบทุกวันว่า Backup ที่ตั้งไว้ทำงานสำเร็จ ไฟล์ Backup มีขนาดสมเหตุสมผล และสามารถ Restore ได้จริง

# ตรวจไฟล์ Backup ล่าสุด
ls -lhrt /backup/mysql/ | tail -5
ls -lhrt /backup/postgresql/ | tail -5
ls -lhrt /backup/mongodb/ | tail -5

# ตรวจว่า Backup ไม่เล็กผิดปกติ (อาจเป็นไฟล์เปล่า)
find /backup -name "*.gz" -mtime -1 -size +1M -ls
find /backup -name "*.gz" -mtime -1 -size -100k -ls  # ไฟล์เล็กน่าสงสัย

# ตรวจ Cron Log ว่า Backup Job ทำงานสำเร็จ
grep -i "backup" /var/log/syslog | tail -10

งาน Maintenance รายสัปดาห์

MySQL — Optimize Tables

ตารางที่มีการ UPDATE และ DELETE บ่อย ๆ จะมี Fragmentation สะสม การทำ OPTIMIZE TABLE ช่วยจัดเรียงข้อมูลใหม่และคืนพื้นที่ว่าง

# ตรวจ Fragmentation ของตาราง
mysql -u root -p -e "
  SELECT table_schema, table_name,
    ROUND(data_free / 1024 / 1024, 2) AS fragmented_mb,
    ROUND((data_free / (data_length + index_length + data_free)) * 100, 2) AS frag_pct
  FROM information_schema.tables
  WHERE data_free > 10485760
  ORDER BY data_free DESC LIMIT 20;"

# Optimize ตารางที่มี Fragmentation สูง
mysql -u root -p -e "OPTIMIZE TABLE mydb.orders;"

# Optimize ทุกตารางในฐานข้อมูล
mysqlcheck -u root -p --optimize mydb

# Analyze ตารางเพื่ออัพเดตสถิติ
mysqlcheck -u root -p --analyze mydb

PostgreSQL — VACUUM และ ANALYZE

แม้ PostgreSQL จะมี Autovacuum ทำงานอยู่แล้ว แต่บางตารางที่มี Write หนักอาจต้อง VACUUM ด้วยมือเพิ่มเติม การ ANALYZE ช่วยอัพเดตสถิติให้ Query Planner ทำงานได้ดีขึ้น

# ดูตารางที่ต้อง VACUUM
psql -U postgres -d mydb -c "
  SELECT relname, n_dead_tup, n_live_tup,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
    last_autovacuum
  FROM pg_stat_user_tables
  WHERE n_dead_tup > 10000
  ORDER BY n_dead_tup DESC;"

# VACUUM ANALYZE ทั้งฐานข้อมูล
vacuumdb -U postgres -d mydb --analyze

# VACUUM FULL เฉพาะตารางที่มี Bloat สูง (ล็อคตาราง — ระวังใช้ในเวลาที่ Traffic ต่ำ)
psql -U postgres -d mydb -c "VACUUM FULL orders;"

# REINDEX ตารางที่มี Index Bloat
psql -U postgres -d mydb -c "REINDEX TABLE orders;"

# ดู Table Bloat โดยประมาณ
psql -U postgres -d mydb -c "
  SELECT relname,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
  FROM pg_stat_user_tables
  ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;"

MongoDB — Compact Collections

# ดูขนาด Collection
mongosh -u adminUser -p 'password' --authenticationDatabase admin --eval "
  use myapp;
  db.getCollectionNames().forEach(c => {
    const s = db[c].stats();
    print(c, '- Size:', (s.size/1024/1024).toFixed(2), 'MB',
      'Storage:', (s.storageSize/1024/1024).toFixed(2), 'MB',
      'Ratio:', (s.size / s.storageSize * 100).toFixed(1) + '%');
  });"

# Compact Collection (คืนพื้นที่ให้ WiredTiger)
mongosh -u adminUser -p 'password' --authenticationDatabase admin --eval "
  use myapp;
  db.runCommand({ compact: 'orders' });"

# ตรวจ Index ว่ายังมีประสิทธิภาพ
mongosh -u adminUser -p 'password' --authenticationDatabase admin --eval "
  use myapp;
  db.orders.getIndexes().forEach(idx => {
    const stats = db.orders.aggregate([{\$indexStats: {}}])
      .toArray().find(s => s.name === idx.name);
    if (stats) print(idx.name, '- Accesses:', stats.accesses.ops);
  });"

งาน Maintenance รายเดือน

ตรวจสอบและทำความสะอาด Log

# MySQL — Rotate Binary Logs
mysql -u root -p -e "PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 7 DAY);"

# ดูขนาด Binary Logs
mysql -u root -p -e "SHOW BINARY LOGS;"

# PostgreSQL — ดู WAL Files
psql -U postgres -c "SELECT pg_size_pretty(sum(size)) AS wal_size
  FROM pg_ls_waldir();"

# ทำความสะอาด Log เก่า
sudo find /var/log/mysql -name "*.log" -mtime +30 -delete
sudo find /var/log/postgresql -name "*.log" -mtime +30 -delete

ตรวจสอบ User และ Permission

เดือนละครั้งควรตรวจสอบว่ามี User ที่ไม่ได้ใช้งานแล้วหรือไม่ และ Permission ที่ให้ไว้ยังเหมาะสมอยู่

# MySQL — ดู User ทั้งหมด
mysql -u root -p -e "SELECT user, host, account_locked FROM mysql.user;"

# ดู User ที่ไม่เคย Login
mysql -u root -p -e "
  SELECT user, host FROM mysql.user
  WHERE user NOT IN (
    SELECT DISTINCT user FROM performance_schema.accounts WHERE user IS NOT NULL);"

# ดู Permission ของ User
mysql -u root -p -e "SHOW GRANTS FOR 'appuser'@'localhost';"

# PostgreSQL — ดู User และ Role
psql -U postgres -c "SELECT rolname, rolsuper, rolcreatedb, rolcanlogin
  FROM pg_roles WHERE rolcanlogin = true ORDER BY rolname;"

# ดู User ที่ไม่เคย Login (ถ้ามี pg_stat_activity tracking)
psql -U postgres -c "
  SELECT rolname FROM pg_roles
  WHERE rolcanlogin = true
    AND rolname NOT IN (SELECT DISTINCT usename FROM pg_stat_activity);"

# MongoDB — ดู User
mongosh -u adminUser -p 'password' --authenticationDatabase admin --eval "
  db.getSiblingDB('admin').getUsers().users.forEach(u =>
    print(u.user, '- Roles:', JSON.stringify(u.roles)));"

ทดสอบ Backup Restore

Backup ที่ไม่เคยทดสอบ Restore ไม่ถือว่าเป็น Backup ที่เชื่อถือได้ ควรทดสอบ Restore อย่างน้อยเดือนละครั้งบนเซิร์ฟเวอร์ทดสอบ

# MySQL — ทดสอบ Restore บนเซิร์ฟเวอร์ทดสอบ
LATEST_BACKUP=$(ls -t /backup/mysql/*.gz | head -1)
echo "Testing restore of: $LATEST_BACKUP"

# สร้างฐานข้อมูลทดสอบ
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS restore_test;"
gunzip -c "$LATEST_BACKUP" | mysql -u root -p restore_test

# ตรวจจำนวน Record
mysql -u root -p -e "
  SELECT table_name, table_rows
  FROM information_schema.tables
  WHERE table_schema = 'restore_test'
  ORDER BY table_rows DESC;"

# ลบฐานข้อมูลทดสอบ
mysql -u root -p -e "DROP DATABASE restore_test;"

# PostgreSQL — ทดสอบ Restore
LATEST_BACKUP=$(ls -t /backup/postgresql/*.dump | head -1)
createdb -U postgres restore_test
pg_restore -U postgres -d restore_test "$LATEST_BACKUP"
psql -U postgres -d restore_test -c "
  SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;"
dropdb -U postgres restore_test

Maintenance Automation Script

การรวมงาน Maintenance ทั้งหมดไว้ใน Script เดียวช่วยให้ทำตามขั้นตอนเดียวกันทุกครั้งและสามารถตั้ง Cron Job ให้ทำงานอัตโนมัติได้

Daily Maintenance Script สำหรับ MySQL

#!/bin/bash
# daily_maintenance_mysql.sh
set -euo pipefail

MYSQL_USER="root"
LOG="/var/log/db_maintenance_$(date +%Y%m%d).log"
ALERT_EMAIL="[email protected]"

log() { echo "[$(date '+%H:%M:%S')] $1" | tee -a "$LOG"; }
alert() { echo "$1" | mail -s "DB Maintenance: $2" "$ALERT_EMAIL" 2>/dev/null || true; }

log "=== Daily MySQL Maintenance ==="

# 1. ตรวจสอบว่า MySQL ทำงาน
if ! mysqladmin -u "$MYSQL_USER" ping &>/dev/null; then
    alert "MySQL is DOWN!" "CRITICAL: MySQL Down"
    exit 1
fi
log "MySQL: Running"

# 2. ตรวจสอบ Disk Space
DISK_PCT=$(df /var/lib/mysql | tail -1 | awk '{print $5}' | tr -d '%')
log "Disk Usage: ${DISK_PCT}%"
if [ "$DISK_PCT" -gt 85 ]; then
    alert "Disk usage at ${DISK_PCT}%!" "WARNING: Disk Space"
fi

# 3. ตรวจสอบ Long Running Queries
LONG_QUERIES=$(mysql -u "$MYSQL_USER" -N -e "
  SELECT COUNT(*) FROM information_schema.processlist
  WHERE command != 'Sleep' AND time > 300;")
if [ "$LONG_QUERIES" -gt 0 ]; then
    log "WARNING: $LONG_QUERIES queries running > 5 minutes"
    mysql -u "$MYSQL_USER" -e "
      SELECT id, user, host, db, time, state, LEFT(info, 100) AS query
      FROM information_schema.processlist
      WHERE command != 'Sleep' AND time > 300;" | tee -a "$LOG"
fi

# 4. ตรวจสอบ Replication (ถ้ามี)
REPL=$(mysql -u "$MYSQL_USER" -N -e "SHOW REPLICA STATUS\G" 2>/dev/null || true)
if [ -n "$REPL" ]; then
    LAG=$(echo "$REPL" | grep "Seconds_Behind_Source" | awk '{print $2}')
    IO=$(echo "$REPL" | grep "Replica_IO_Running" | awk '{print $2}')
    SQL=$(echo "$REPL" | grep "Replica_SQL_Running" | awk '{print $2}')
    log "Replication: IO=$IO SQL=$SQL Lag=${LAG}s"
    if [ "$IO" != "Yes" ] || [ "$SQL" != "Yes" ]; then
        alert "Replication broken!" "CRITICAL: Replication"
    fi
fi

# 5. ตรวจสอบ Error Log
ERRORS=$(sudo tail -1000 /var/log/mysql/error.log 2>/dev/null | grep -ci "error" || true)
log "Errors in last 1000 lines of log: $ERRORS"
if [ "$ERRORS" -gt 10 ]; then
    alert "Found $ERRORS errors in MySQL log" "WARNING: Errors in Log"
fi

# 6. ตรวจสอบ Backup ล่าสุด
LATEST_BACKUP=$(find /backup/mysql -name "*.gz" -mtime -1 2>/dev/null | head -1)
if [ -z "$LATEST_BACKUP" ]; then
    alert "No backup found in last 24 hours!" "WARNING: Missing Backup"
    log "WARNING: No recent backup found"
else
    BACKUP_SIZE=$(stat -c%s "$LATEST_BACKUP" 2>/dev/null || echo 0)
    log "Latest backup: $LATEST_BACKUP ($(numfmt --to=iec $BACKUP_SIZE))"
fi

log "=== Daily Maintenance Complete ==="

Weekly Maintenance Script สำหรับ PostgreSQL

#!/bin/bash
# weekly_maintenance_postgresql.sh
set -euo pipefail

PG_USER="postgres"
DB_NAME="mydb"
LOG="/var/log/pg_maintenance_$(date +%Y%m%d).log"

log() { echo "[$(date '+%H:%M:%S')] $1" | tee -a "$LOG"; }

log "=== Weekly PostgreSQL Maintenance ==="

# 1. VACUUM ANALYZE ทุกตาราง
log "Running VACUUM ANALYZE..."
vacuumdb -U "$PG_USER" -d "$DB_NAME" --analyze 2>&1 | tee -a "$LOG"
log "VACUUM ANALYZE complete"

# 2. ตรวจ Table Bloat
log "Checking table bloat..."
psql -U "$PG_USER" -d "$DB_NAME" -c "
  SELECT relname, n_dead_tup,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct
  FROM pg_stat_user_tables
  WHERE n_dead_tup > 10000
  ORDER BY n_dead_tup DESC LIMIT 10;" | tee -a "$LOG"

# 3. ตรวจ Unused Indexes
log "Checking unused indexes..."
psql -U "$PG_USER" -d "$DB_NAME" -c "
  SELECT relname, indexrelname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan
  FROM pg_stat_user_indexes
  WHERE idx_scan = 0
    AND indexrelname NOT LIKE '%pkey%'
    AND pg_relation_size(indexrelid) > 1048576
  ORDER BY pg_relation_size(indexrelid) DESC;" | tee -a "$LOG"

# 4. ตรวจ Duplicate Indexes
log "Checking duplicate indexes..."
psql -U "$PG_USER" -d "$DB_NAME" -c "
  SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size,
    (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
    (array_agg(idx))[1]::regclass AS index1,
    (array_agg(idx))[2]::regclass AS index2
  FROM (
    SELECT indexrelid::regclass AS idx, indrelid, indkey,
      array_agg(indexrelid) OVER (PARTITION BY indrelid, indkey) AS dupes
    FROM pg_index
  ) sub
  WHERE array_length(dupes, 1) > 1
  GROUP BY indrelid, indkey
  HAVING count(*) > 1;" | tee -a "$LOG"

# 5. อัพเดตสถิติ
log "Updating statistics..."
psql -U "$PG_USER" -d "$DB_NAME" -c "ANALYZE;" 2>&1 | tee -a "$LOG"

log "=== Weekly Maintenance Complete ==="

ตั้งค่า Cron Jobs สำหรับ Maintenance อัตโนมัติ

# แก้ไข Crontab
sudo crontab -e

# Daily Maintenance — ทุกวัน ตี 3
0 3 * * * /opt/scripts/daily_maintenance_mysql.sh >> /var/log/cron_maintenance.log 2>&1

# Weekly Maintenance — ทุกวันอาทิตย์ ตี 2
0 2 * * 0 /opt/scripts/weekly_maintenance_postgresql.sh >> /var/log/cron_maintenance.log 2>&1

# Monthly Backup Test — วันที่ 1 ของทุกเดือน ตี 4
0 4 1 * * /opt/scripts/monthly_backup_test.sh >> /var/log/cron_maintenance.log 2>&1

# MongoDB Compact — ทุกวันอาทิตย์ ตี 1 (ก่อน PostgreSQL)
0 1 * * 0 mongosh -u adminUser -p 'password' --authenticationDatabase admin --eval "use myapp; db.runCommand({compact:'orders'})" >> /var/log/cron_maintenance.log 2>&1

# ทำความสะอาด Log เก่า — ทุกวัน
30 3 * * * find /var/log/db_maintenance_*.log -mtime +30 -delete 2>/dev/null

Maintenance Checklist

สรุปงาน Maintenance ทั้งหมดเป็น Checklist สำหรับใช้ตรวจสอบ

รายวัน

  • ตรวจสอบว่าฐานข้อมูลทำงานปกติ (ping/status)
  • ตรวจ Error Log ว่าไม่มี Error ใหม่
  • ตรวจ Disk Space ว่าเพียงพอ (แจ้งเตือนเมื่อเกิน 85%)
  • ตรวจ Backup ล่าสุดว่ามีขนาดสมเหตุสมผล
  • ตรวจ Replication Status (ถ้ามี) ว่าไม่มี Lag ผิดปกติ
  • ตรวจ Long Running Queries ว่าไม่มี Query ค้าง

รายสัปดาห์

  • MySQL: OPTIMIZE TABLE สำหรับตารางที่มี Fragmentation สูง
  • PostgreSQL: VACUUM ANALYZE ทุกฐานข้อมูล ตรวจ Dead Tuples
  • MongoDB: Compact Collection ที่มี Storage Ratio ต่ำ
  • ตรวจ Slow Query Log ว่ามี Query ที่ต้องปรับปรุง
  • ตรวจ Index Usage ว่ายังมีประสิทธิภาพ

รายเดือน

  • ทำความสะอาด Binary Logs / WAL Files เก่า
  • ตรวจสอบ User และ Permission ว่ายังเหมาะสม
  • ทดสอบ Backup Restore บนเซิร์ฟเวอร์ทดสอบ
  • ตรวจ Unused Indexes และ Duplicate Indexes
  • ทบทวน Alert Thresholds ว่ายังเหมาะสม
  • อัพเดต Software Patches ที่เกี่ยวข้องกับ Security

ข้อควรระวังสำหรับ Maintenance

  • ทำ Maintenance ในช่วง Low Traffic เสมอ เช่น กลางดึกหรือเช้ามืด เพราะบางคำสั่ง (เช่น VACUUM FULL, OPTIMIZE TABLE) จะล็อคตาราง
  • สำรองข้อมูลก่อนทำ Maintenance ที่อาจเปลี่ยนแปลงข้อมูล เช่น REINDEX หรือ Compact
  • ทดสอบ Script บนเซิร์ฟเวอร์ทดสอบก่อนนำไปใช้จริง
  • ตั้ง Timeout สำหรับ Script เพื่อป้องกันกรณีค้าง เช่น ใช้ statement_timeout ใน PostgreSQL
  • บันทึก Log ทุกขั้นตอนเพื่อตรวจสอบภายหลังได้
  • อย่าลบ Backup เก่าจนกว่าจะมั่นใจว่า Backup ใหม่ใช้งานได้

สรุป

การดูแลฐานข้อมูลไม่ใช่งานที่ทำครั้งเดียวแล้วจบ แต่เป็นกิจกรรมที่ต้องทำอย่างสม่ำเสมอ งานรายวันเน้นตรวจจับปัญหาเร็ว งานรายสัปดาห์เน้นรักษาประสิทธิภาพ และงานรายเดือนเน้นความปลอดภัยและความน่าเชื่อถือ การเขียน Script และตั้ง Cron Job ช่วยลดภาระงานมือและทำให้ทีมทุกคนปฏิบัติตามขั้นตอนเดียวกัน สิ่งสำคัญที่สุดคือต้องทดสอบ Backup Restore เป็นประจำ เพราะ Backup ที่ Restore ไม่ได้ไม่มีค่าเท่ากับไม่มี Backup เลย

แนะนำบริการ DE

การรัน Maintenance Tasks อัตโนมัติต้องการเซิร์ฟเวอร์ที่มี Root Access สำหรับตั้ง Cron Jobs และ Script รวมถึง Disk I/O ที่เร็วสำหรับการทำ OPTIMIZE, VACUUM และ Compact Cloud VPS ของ DE รองรับ SSD NVMe พร้อม Root Access เต็มรูปแบบ เหมาะสำหรับ Database Server ที่ต้องการจัดการ Maintenance ได้เอง

สำหรับโปรเจกต์ที่ไม่ต้องการดูแลฐานข้อมูลด้วยตัวเอง Cloud Hosting ของ DE มีระบบ Managed Infrastructure ที่จัดการ Maintenance พื้นฐานให้อัตโนมัติ