Infra — GatheringDB Incident & DB Update Monitoring

# Infra — GatheringDB Incident & DB Update Monitoring

AI Summary

Purpose:

• Capture two durable infra learnings: the gatherdb01 memory-exhaustion/swap

incident (and its root cause + fix), and the ELK-based DB Update Monitoring system that watches binlog shipping and alerts via JIRA/KakaoWork.

Key points:

• gatherdb01 incident (2025-11-26, Critical): the lab-gath-m MySQL container

hit 99.96% memory and 14GB swap with disk thrashing.

• Root cause = sizing/config misses, not load: innodb_buffer_pool_size ~85GB

was 89% of the 96GB container limit, leaving ~11GB for OS + MySQL + 512 connections; innodb_dedicated_server=1 auto-tuned against the 125GB HOST RAM (not the 96GB Docker limit), conflicting with manual settings.

• Fix (durable rule): in containerized MySQL, disable innodb_dedicated_server

and size the buffer pool to ~75% of the CONTAINER limit (here 96GB → 72GB = 77309411328). Swap is a band-aid; right-sizing the buffer pool is the real fix.

• DB Update Monitoring system: a 5-layer ELK pipeline (RSYNC sources →

create-binlog-index + Filebeat → Logstash 12-pipeline + Elasticsearch → Kibana/Grafana → elk-jira-kakao alerting), run per instance (other, filecomp), to monitor binlog/access-log driven DB update progress and alert on delays.

Relevant when:

• Diagnosing MySQL memory/swap in containers, tuning buffer pool, or working on

binlog/update monitoring and its alerting.

Do not read full document unless:

• You need the full Logstash pipeline graph, cron schedule, or alert thresholds.

Linked documents:

• [[distribution-db.md]] (monitoring watches dist binlog shipping)
• [[bts.md]]
• [[labrador-platform.md]] (GatheringDB is a platform load target)
• [[index.md]]

Open Questions

• Whether the 72GB buffer-pool change was applied and verified in production.

Needs confirmation.

Details

gatherdb01 memory/swap incident (ITS-2025-11-26-001)

• Symptom: lab-gath-m MySQL container at MEM 99.96% (95.96/96 GiB), 14GB swap

in use, very high BLOCK I/O (disk thrashing). Risk of master OOM-kill / replica lag.

• Root causes:

1. Buffer pool too large vs container limit: innodb_buffer_pool_size 91268055040 (~85GB) = 89% of the 96G compose memory limit; max_connections 512. Insufficient headroom for OS/process/connections → swap. 2. innodb_dedicated_server=1 in a Docker container: it auto-sizes against host RAM (125GB), not the 96GB container limit, misjudging capacity and conflicting with manual config.

• Fix guidance (target file

/data/product/data-infra/gatheringdb/master/conf/mysql.cnf): - Comment out / remove innodb_dedicated_server=1. - Set innodb_buffer_pool_size=77309411328 (~72GB ≈ 75% of 96GB). - Expected: ~24GB headroom freed, swap eliminated, swap-alert mail stops, master load drops and replica transfer normalizes.

• Durable lesson: never enable innodb_dedicated_server inside a memory-limited

container, and keep the buffer pool at ~75% of the container limit (not the host).

DB Update Monitoring system (db-update-monitoring)

• Purpose: monitor DB update progress by collecting and analyzing binlog and

Nginx access logs, then alert on delays.

• 5 layers:

1. Sources (RSYNC): binlog files from dist-r (172.30.1.202, hourly :10) and dist-m (211.115.125.164, hourly :05); access log + allowed-IP list from 172.30.1.41 every 5 min. Managed separately per instance (other, filecomp). 2. Collect: create-binlog-index scans the binlog dir and emits binlog.json (file_name, created_date, file_size); container restarted hourly at :07. Filebeat ships binlog.json + access log over Beats with SSL/TLS. 3. Process: Logstash runs 12 pipelines (initial → intermediate → final indexes) reading/writing Elasticsearch (ES 8GB/4cpu, Logstash 4GB/4cpu). Final index final_update_index is the main source for dashboards/alerts. 4. Visualize: Kibana (ad-hoc) + Grafana (db_update_monitoring.json dashboard). 5. Alert: elk-jira-kakao polls ES and raises JIRA issues (Epic→Story→Subtask) and KakaoWork messages. Modes: warning (hourly except 9:00), resolve (9:00 only). Categories: file_delay_count (≥24 delayed files), file_update_time (predicted completion ≥24h), file_created_date (binlog creation delay ≥24h).

• Per-instance isolation: separate binlog dirs, ES indexes, and Logstash

pipelines for other vs filecomp (e.g. binlog_index_other, binlog_index_filecomp). All inter-service traffic is SSL/TLS; monitoring network 172.28.0.0/16.