อ่านจากฐานสำรอง ต้องมีป้ายบอกว่าข้อมูลเก่ากี่นาที

พอดีระบบรายงานที่ผมทำให้หน่วยงานหนึ่งไม่ได้อ่านจากฐานหลัก แต่อ่านจากฐานสำรองที่ replicate มาอีกที เหตุผลคือฐานหลักเป็นระบบงานประจำวันที่มีคนใช้กันทั้งวัน ผมไม่อยากเอา query รายงานหนัก ๆ ไปกวน วันหนึ่งมีคนโทรมาบอกว่าตัวเลขในรายงานไม่ตรงกับที่เห็นในระบบหลัก ผมไปไล่ดูถึงรู้ว่า replication หยุดไปตั้งแต่เมื่อคืน หน้าเว็บยังทำงานปกติทุกอย่าง กราฟขึ้นสวยงาม ไม่มี error สักตัว แค่ข้อมูลค้างอยู่ที่เมื่อวาน ผมเลยนั่งเขียนตัวเฝ้าขึ้นมาแล้วบังคับให้หน้าเว็บต้องมีป้ายบอกเสมอว่าข้อมูลที่กำลังดูอยู่นี้เก่าไปกี่นาที

ชื่อฐาน ชื่อตาราง และเลข IP ในบทความเป็นของสมมติ ส่วนตรรกะและค่า threshold คือของที่ใช้อยู่จริง

ปัญหาคือความเงียบ ไม่ใช่ความช้า

สองสัญญาณที่ต้องดูคู่กัน และวัดคนละเรื่องกัน

สัญญาณแรกคือของที่ MariaDB ให้มาเอง ดูได้จาก SHOW SLAVE STATUS

mariadb -h 10.10.10.3 -u"$DB_USER" -p"$DB_PASS" -e "SHOW SLAVE STATUS\G" \
  | grep -E 'Slave_(IO|SQL)_Running|Seconds_Behind_Master|Last_SQL_Error'

สัญญาณที่สองคือของที่ผมวัดเอง คือความสดของข้อมูลธุรกิจ เทียบวันที่ล่าสุดของตารางเดียวกันระหว่างฐานหลักกับฐานสำรอง

-- ยิงคำสั่งนี้ทั้งสองเครื่อง แล้วเอาผลมาลบกัน
SELECT MAX(closed_at) FROM core_db.tx_daily;

ผมย้ำตรงนี้เพราะเป็นจุดที่ทำให้ผมโดนหลอกมาแล้วครับ ตอนนั้นเปิดดู Seconds_Behind_Master เห็น 0 ก็สบายใจ แต่ข้อมูลค้างมาสองวันแล้ว สาเหตุคือ IO thread ตายไปตั้งแต่แรก ของใน relay log ถูกไล่จนหมดไปนานแล้ว ตัวเลข 0 ที่เห็นแปลว่า "ไม่มีอะไรค้างให้ทำ" ไม่ได้แปลว่า "ข้อมูลสด"

credential ต้องมาจากข้างนอกสคริปต์เท่านั้น

ก่อนจะลงรายละเอียดตัวสคริปต์ ขอพูดเรื่องนี้ก่อนเพราะเป็นจุดที่ผมทำผิดมาเองในเวอร์ชันแรก คือใส่ user กับรหัสไว้บนหัวสคริปต์ตรง ๆ แล้วสคริปต์นั้นก็อยู่ใน repo ที่ commit ขึ้นไปเรียบร้อย

# /etc/default/replica-watch  — chmod 600 root:root ไม่อยู่ใน repo
DB_USER=monitor_ro
DB_PASS=<ใส่ค่าจริงที่นี่>
MASTER_HOST=10.10.10.2
REPLICA_HOSTS="10.10.10.3 10.10.10.4"
WHITELIST="10.10.10.7,10.10.10.2"

#!/bin/bash
# replica_watch.sh — READ-ONLY ต่อ master/replica ; เขียนผลลงฐานของแอปเราเองเท่านั้น
set -o pipefail
. /etc/default/replica-watch
: "${DB_USER:?ยังไม่ได้ตั้งค่าใน /etc/default/replica-watch}"
: "${DB_PASS:?}"

LAG_SECS_WARN=1800     # ตามหลังเกิน 30 นาที = lagging
LAG_DAYS_WARN=2        # ข้อมูลธุรกิจเก่ากว่า master เกิน 2 วัน = lagging

q(){ mariadb -h "$1" -u"$DB_USER" -p"$DB_PASS" -N -e "$2" 2>/dev/null; }

อ่าน SHOW SLAVE STATUS ให้ถูกวิธี

ตรงนี้มีกับดักเล็ก ๆ ที่ทำให้ผมงงอยู่พักหนึ่ง คือถ้าใส่ -N เพื่อตัดหัวตารางออกตามความเคยชิน ผลของ \G จะไม่ออกมาเป็นรูปแบบ Field: value ให้ grep ได้

# ผิด: ใส่ -N แล้ว \G ไม่ออกเป็น Field: value
SS=$(mariadb -h "$H" -u"$DB_USER" -p"$DB_PASS" -N -e "SHOW SLAVE STATUS\G" 2>/dev/null)

# ถูก: ไม่ใส่ -N สำหรับ \G โดยเฉพาะ แล้วดึงค่าด้วยฟังก์ชันเล็ก ๆ
SS=$(mariadb -h "$H" -u"$DB_USER" -p"$DB_PASS" -e "SHOW SLAVE STATUS\G" 2>/dev/null)
fld(){ printf '%s\n' "$SS" | grep -m1 -E "[[:space:]]$1:" | sed "s/.*$1:[[:space:]]*//" | tr -d ' \r'; }
IO=$(fld Slave_IO_Running); SQLT=$(fld Slave_SQL_Running); SEC=$(fld Seconds_Behind_Master)
[ "$SEC" = "NULL" ] && SEC=""

ค่าที่ต้องระวังคือ Seconds_Behind_Master มันคืนคำว่า NULL มาเป็นข้อความได้ ซึ่งแปลว่า SQL thread ไม่ได้ทำงานอยู่ ถ้าเอาไปเทียบตัวเลขตรง ๆ โดยไม่ล้างก่อน bash จะตีความเป็นศูนย์แล้วบอกว่าปกติดี ซึ่งเป็นคำตอบที่ผิดที่สุดเท่าที่จะผิดได้

จับคนที่แอบเขียนลงฐานสำรอง

อีกอาการที่ทำให้ replication พังคือมีคนต่อเข้าไปเขียนบนฐานสำรองตรง ๆ ส่วนใหญ่ไม่ได้เจตนา เป็นโปรแกรมเก่าที่ config ชี้ผิดเครื่อง หรือคนที่เปิดโปรแกรมจัดการฐานข้อมูลค้างไว้แล้วเผลอกดบันทึก พอ row ฝั่งสำรองไม่ตรงกับที่ master ส่งมา SQL thread จะหยุดพร้อม duplicate key error

-- หา foreign writer: ใครกำลังยิงคำสั่งเขียนอยู่บนเครื่องนี้ นอกจาก replication
SELECT GROUP_CONCAT(DISTINCT CONCAT(SUBSTRING_INDEX(host,':',1),'/',user) SEPARATOR ', ')
FROM information_schema.processlist
WHERE db IN ('core_db','ref_db')
  AND user <> 'system user'
  AND SUBSTRING_INDEX(host,':',1) NOT IN ('10.10.10.7','10.10.10.2')
  AND command IN ('Query','Execute')
  AND info REGEXP '^[[:space:]]*(INSERT|UPDATE|DELETE|REPLACE|LOAD|ALTER|DROP|TRUNCATE|CREATE)';

ผลลัพธ์ที่ได้มาต้องล้างอักขระก่อนเอาไปใส่ SQL ของเราเสมอ เพราะมันคือข้อความที่มาจากข้างนอก

# ล้าง quote กับ backslash + ตัดความยาว ก่อนเอาไปประกอบเป็น SQL
san(){ tr -d "'\"\\\\" | tr '\n' ' ' | sed 's/^[[:space:]]*//; s/[[:space:]]*$//' | cut -c1-240; }

เก็บผลลงตารางเดียว แล้วให้หน้าเว็บอ่าน

ผมไม่ให้สคริปต์ยิงแจ้งเตือนเองครับ ให้มันเขียนสถานะล่าสุดลงตารางของแอปเราเอง แล้วหน้าเว็บไปอ่านตารางนั้น ข้อดีคือคนที่เปิดหน้าเว็บอยู่จะเห็นทันทีโดยไม่ต้องรอใครส่งข้อความมาบอก

CREATE TABLE replica_watch (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  host            VARCHAR(20)  NOT NULL UNIQUE,
  role            VARCHAR(12)  NOT NULL DEFAULT 'slave',
  io_running      VARCHAR(8)   NULL,
  sql_running     VARCHAR(8)   NULL,
  secs_behind     INT          NULL,
  lag_days        INT          NULL,
  max_biz_date    DATE         NULL,
  foreign_writers VARCHAR(255) NULL,
  status          VARCHAR(16)  NOT NULL DEFAULT 'ok',  -- ok|stopped|lagging|foreign-write|unreachable
  detail          VARCHAR(255) NULL,
  checked_at      DATETIME     NULL
) ENGINE=InnoDB;

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

if [ "$IO" != "Yes" ] || [ "$SQLT" != "Yes" ]; then
  ST=stopped;  DT="replication หยุด (IO=$IO SQL=$SQLT) — ตรวจสาเหตุก่อนสั่งเดินต่อ"
  [ -n "$ERR" ] && DT="$DT | err: $ERR"
elif [ -n "$FW" ]; then
  ST=foreign-write; DT="พบการเขียนลงเครื่องนี้โดยตรงจาก: $FW"
elif [ -n "$SEC" ] && [ "$SEC" -gt "$LAG_SECS_WARN" ] 2>/dev/null; then
  ST=lagging;  DT="ตามหลัง master $(( SEC / 60 )) นาที — ข้อมูลยังไม่สด"
elif [ -n "$LAG" ] && [ "$LAG" -gt "$LAG_DAYS_WARN" ] 2>/dev/null; then
  ST=lagging;  DT="ข้อมูลธุรกิจเก่ากว่า master $LAG วัน (ล่าสุด $DCH vs $MASTER_DCH)"
else
  ST=ok;       DT="ปกติ (delay ${SEC:-?}s, ห่าง ${LAG:-?} วัน)"
fi

ตัวเฝ้าของตัวเฝ้า

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

// อ่านผลจากตาราง คืน null = ปกติ ; คืน array = ให้ layout ขึ้นแถบเตือนบนหัวเว็บ
class ReplicaAlert
{
    public const SOURCE_HOST = '10.10.10.3';   // เครื่องที่รายงานอ่านจริง
    private const STALE_MIN  = 130;            // cron รายชั่วโมง เผื่อพลาดได้ 1 รอบ

    public static function check(): ?array
    {
        try {
            $r = DB::table('replica_watch')->where('host', self::SOURCE_HOST)->first();
        } catch (\Throwable $e) {
            return null;                        // ฐานยังไม่พร้อม อย่าให้ตัวเฝ้าทำเว็บล่ม
        }
        if (!$r) return null;

        $checked = $r->checked_at ? strtotime((string) $r->checked_at) : null;
        if ($checked && (time() - $checked) > self::STALE_MIN * 60) {
            return ['level' => 'warn',
                    'title' => 'ตัวเฝ้า replication ไม่ได้อัปเดต',
                    'msg'   => 'ตรวจล่าสุด ' . $r->checked_at . ' — ไปดู cron ที่เครื่องรายงาน'];
        }
        if (($r->status ?? 'ok') !== 'ok') {
            return ['level' => 'err',
                    'title' => 'แหล่งข้อมูลผิดปกติ: ' . $r->status,
                    'msg'   => trim((string) $r->detail) . ' · ตรวจล่าสุด ' . $r->checked_at];
        }
        return null;
    }
}

ค่า 130 นาทีมาจากการที่ cron รันทุกชั่วโมง เผื่อให้พลาดได้หนึ่งรอบก่อนขึ้นเตือน ถ้าตั้งชิดกว่านี้จะมีเตือนหลอกเวลาเครื่องโหลดหนักแล้ว cron เลื่อนไปนิดหน่อย ส่วนใครที่รันถี่กว่านี้ก็ปรับตามสะดวก หลักคิดเดียวกันคือเผื่อพลาดหนึ่งรอบเสมอ

# /etc/cron.d/replica-watch
0 * * * * appops /opt/app/ops/replica_watch.sh >> /var/log/replica_watch.log 2>&1

สรุปสิ่งที่ระบบนี้บอกได้ และบอกไม่ได้

1. แยกให้ออกก่อนว่า Seconds_Behind_Master กับความสดของข้อมูลธุรกิจเป็นคนละเรื่อง แล้วเก็บทั้งคู่
2. สร้างบัญชีอ่านอย่างเดียวสำหรับตัวเฝ้า เก็บรหัสไว้นอกสคริปต์และนอก repo เสมอ
3. อย่าใส่ -N ตอนอ่าน SHOW SLAVE STATUS แบบ \G และล้างค่า NULL ก่อนเทียบตัวเลขทุกครั้ง
4. ตั้ง read_only บนฐานสำรองก่อน แล้วค่อยใช้ processlist เป็นตัวช่วยหาว่าใครแอบเขียน
5. ให้สคริปต์เขียนสถานะลงตารางของแอปเอง ไม่ต้องให้มันส่งแจ้งเตือนเองหรือแก้ระบบเอง
6. ให้ฝั่งอ่านเช็คอายุของผลอีกชั้น แล้วครอบ try/catch ไว้เสมอเพื่อไม่ให้ตัวเฝ้าทำเว็บล่ม
7. ขึ้นแถบเตือนบนหัวเว็บทุกหน้า อย่าซ่อนไว้ในหน้า admin ที่ไม่มีใครเปิด

ข้อสุดท้ายสำคัญกว่าที่คิดครับ ตอนแรกผมทำหน้า status แยกไว้หน้าหนึ่งสวยงามมาก มีทั้งตารางทั้งสีเขียวสีแดง แล้วก็ไม่มีใครเปิดเลยสักคนตลอดสองเดือน พอย้ายมาเป็นแถบแดงบนหัวทุกหน้าแทน วันที่ replication หยุดจริงมีคนถามผมภายในยี่สิบนาที นั่นคือสิ่งที่อยากได้ตั้งแต่แรก

ส่วนที่ระบบนี้บอกไม่ได้ก็มีนะครับ มันไม่รู้ว่าข้อมูลที่ replicate มาถูกต้องหรือเปล่า รู้แค่ว่ามาถึงแล้วหรือยัง ถ้าต้นทางเขียนข้อมูลผิดมาตั้งแต่แรก ตัวเฝ้าตัวนี้จะบอกว่าทุกอย่างปกติดี ใครที่ต้องการระดับนั้นต้องทำ checksum เทียบรายตารางเพิ่มอีกชั้น ซึ่งเป็นอีกเรื่องที่ยาวพอ ๆ กัน ไว้ผมทำจนใช้งานได้นิ่งแล้วจะเอามาเล่าให้ฟัง ตรงไหนผมอธิบายตกหล่นหรือพิมพ์ผิด เดี๋ยวกลับมาแก้เพิ่มให้ภายหลังครับ