Airflow Metadata DB 최적화 및 디스크 용량 회수

배경

master VM의 디스크 용량이 꽉 차 정리를 통한 디스크 용량 회수 결정.

서버(GCE) 부하를 줄이기 위한 과정의 하나로 진행함.

DB 점검

전체 확인 후 size 큰 table만 따로 추림.

[me@de-airflow-master ~]$ sudo docker exec -it 212ac15566ad psql -U airflow
psql (13.7 (Debian 13.7-1.pgdg110+1))
Type "help" for help.

airflow=# SELECT relname AS "table_name",
       pg_size_pretty(pg_total_relation_size(relid)) AS "size"
FROM pg_catalog.pg_statio_user_tables
WHERE relname IN ('task_instance', 'log', 'job', 'celery_taskmeta', 'dag_run')
ORDER BY pg_total_relation_size(relid) DESC;
   table_name    |  size
-----------------+---------
 task_instance   | 8748 MB
 log             | 4215 MB
 job             | 1550 MB
 celery_taskmeta | 915 MB
 dag_run         | 586 MB
(5 rows)

 

작업

1. 백업
   1. VM: master
   2. 경로: 접속 계정/airflow_backup
2. 정리
   1. 구분해서 진행: master(DB), standby VM
   2. 이유: 분산환경 운영
   3. airflow db clean: master vm에서 airflow metadata db 정리
   4. VACUUM FULL: standby vm에서 scheduler container 정리

즉, master vm에서는 백업 및 논리적 삭제, standby vm에서는 물리적 삭제 진행

작업: Master VM

Docker system 로그 정리

system 로그 정리로 5.41GB 여유공간 확보

sudo docker system prune -a --volumes

[me@de-airflow-master ~]$ sudo docker system prune -af
Deleted Containers:
e30d4115be5c2b5e2ae1c4314772c217fef0228a43817f0daa1b738f1c412a6c
e48a5ca8801d0a64ece3ccfa7ffbf62ed9a1236728c6b34e6aa6ef8c6370ac09

Deleted Volumes:
72185b7fd15e63152c07555a34cd5f354f2d5819bce39c5257e18319aee7bd41

Total reclaimed space: 5.41 GB

 

Backup

# 개별 테이블 압축 백업
sudo docker exec 212ac15566ad pg_dump -U airflow -t task_instance | gzip > task_instance_$(date +%Y%m%d).sql.gz
sudo docker exec 212ac15566ad pg_dump -U airflow -t log | gzip > log_$(date +%Y%m%d).sql.gz
sudo docker exec 212ac15566ad pg_dump -U airflow -t job | gzip > job_$(date +%Y%m%d).sql.gz
sudo docker exec 212ac15566ad pg_dump -U airflow -t celery_taskmeta | gzip > celery_taskmeta_$(date +%Y%m%d).sql.gz
sudo docker exec 212ac15566ad pg_dump -U airflow -t dag_run | gzip > dag_run_$(date +%Y%m%d).sql.gz

 

논리적 최적화

table을 특정 순서대로 지우는 것으로 보임.

[me@de-airflow-standby ~]$ sudo docker exec -u airflow d6bacca2d82d airflow db clean --clean-before-timestamp '2025-02-01' --yes --verbose
/home/airflow/.local/lib/python3.8/site-packages/airflow/configuration.py:356: FutureWarning: The auth_backends setting in [api] has had airflow.api.auth.backend.session added in the running config, which is needed by the UI. Please update your config before Apache Airflow 3.0.
  warnings.warn(
/home/airflow/.local/lib/python3.8/site-packages/airflow/utils/log/file_task_handler.py:52 DeprecationWarning: Passing filename_template to FileTaskHandler is deprecated and has no effect
/home/airflow/.local/lib/python3.8/site-packages/airflow/utils/db_cleanup.py:137 SAWarning: SELECT statement has a cartesian product between FROM element(s) "callback_request" and FROM element "xcom".  Apply join condition(s) between each element to resolve.

Found 0 rows meeting deletion criteria.
Performing Delete...
Finished Performing Delete

Found 1567533 rows meeting deletion criteria.
Performing Delete...
Finished Performing Delete

Found 0 rows meeting deletion criteria.
Performing Delete...
Finished Performing Delete

...

 

작업: standby VM

airflow CLI 접속

# docker conatiner 밖에서 접속과 동시에 airflow 명령어 작성
sudo docker exec -u airflow d6bacca2d82d airflow db clean --clean-before-timestamp '2025-02-01' --yes --verbose

 

점검

• 연결된 세션 있나 확인

[me@de-airflow-master ~]$ sudo docker exec -it de-pipeline-airflow-postgres-1 psql -U airflow
psql (13.7 (Debian 13.7-1.pgdg110+1))
Type "help" for help.

airflow=# SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE datname = 'airflow' AND pid <> pg_backend_pid();
 pg_terminate_backend
----------------------
(0 rows)

 

• 세션이 남아있다면 강제 종료

airflow=# SET max_parallel_maintenance_workers = 0;
SET

물리적 최적화

• 아래 예시: task_instance
• df -h . 로 대상 하나하나 디스크 용량 회수 됐는지 확인

airflow=# VACUUM (FULL, VERBOSE, ANALYZE) task_instance;
INFO:  vacuuming "public.task_instance"
INFO:  "task_instance": found 191 removable, 3438168 nonremovable row versions in 216739 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 5.79 s, system: 10.10 s, elapsed: 27.22 s.
INFO:  analyzing "public.task_instance"
INFO:  "task_instance": scanned 30000 of 137739 pages, containing 748730 live rows and 0 dead rows; 30000 rows in sample, 3437644 estimated total rows

 

결과: 시스템 로그 제거 및 DB 최적화

점검: master VM의 postgres 컨테이너

• 디스크 사용량: 31GB → 16GB (14GB 절감)
• 여유 공간: 13GB (30%) → 27GB (63%) (2배 이상 확보)

최적화 전

최적화

점검: Grafana

• master VM 디스크 사용률: 71.5% → 37.5%(-34%, 절반 수준 감소)

최적화 전

최적화 후

 

• standby VM 디스크 사용률: 39% → 18%(-21%, 절반 수준 감소)

최적화 전

최적화 후

 

성과

• master VM, standby VM 모두 기존 대비 여유공간 2배 이상 확보
• 기대: DB 쿼리 성능 향상 및 Disk Full 장애 미리 방지