효율적인 DB관리를 위한 Data Storage Automation
안녕하세요. SpoonRadio SRE 팀에서 DBA 업무를 담당하고 있는 Beaver(이선영)입니다. SpoonRadio에서 DBA는 AWS CloudWatch, Enhanced Monitoring, 그리고 Datadog을 통해 데이터베이스 환경을 모니터링하고 관리하는 역할을 수행하고 있습니다. 이 과정에서 몇 가지 핵심적인 문제에 직면했습니다:
- 데이터베이스 관리 테이블 모니터링을 통한 시스템 변화 예측과 관리의 어려움: 데이터베이스의 변화를 사전에 예측하고 효과적으로 관리하는데 필요한 인사이트 부족
- Datadog의 데이터 수집 빈도와 네트워크 지연으로 인한 알림 지연: 장애 인지가 늦어지며 신속한 대응의 어려움
- Multi Account 및 Multi Region 운영에서 발생하는 작업의 반복성과 시간 소모: 리소스 사용증가로 효율성 저하
우리가 직면한 문제들을 근본적으로 해결하기 위해, SpoonRadio에서는 Data Storage Automation 프로젝트를 진행하기로 하였습니다. 이 프로젝트의 핵심 목표는 데이터 정리 및 이력 관리를 최적화하고, 작업 프로세스를 자동화하여 운영 효율을 개선하며, 시스템의 변동 사항을 사전에 예측하고 대응할 수 있는 능력을 강화하는 것입니다. 이를 통해, 우리는 AWS CloudWatch와 Datadog의 기존 메트릭과 알림을 더욱 효과적으로 활용하고, 데이터베이스 운영의 전반적인 성능과 안정성을 높일 수 있을 것으로 기대합니다.
모니터링 대상 항목
프로젝트 시작하기에 제일 먼저 우리는 모니터링할 주요 대상을 선정하였습니다. 고려한 지표들 중에는 Vacuum, Index, Partition, Transaction, 그리고 AWS 관련 지표들(Pending Maintenance, Snapshot, Cloudwatch Metric 등)이 포함되었습니다. 이 중에서 AWS 관련 지표들을 제외하고 하루에 한번 실행할 수 있는 Vacuum, Index, Partition 지표들에 초점을 맞췄습니다.
- Vacuum
Vacuum 처리는 PostgreSQL 데이터베이스의 고유한 특징 중 하나로, 삭제된 레코드의 공간을 회수하여 데이터베이스 성능을 최적화하는 중요한 작업입니다. 이 과정을 정기적으로 수행하지 않으면, 디스크 공간의 낭비, 성능 저하, 그리고 트랜잭션 ID 충돌과 같은 여러 문제가 발생할 수 있습니다. 이러한 문제를 예방하기 위해 PostgreSQL은 Autovacuum 기능을 통해 해당 작업을 자동으로 관리합니다. 발생하는 주기는 autovacuum_vacuum_scale_factor 설정으로 관리됩니다. 예를 들어 0.2로 지정되어 있다면, 테이블 내 튜플의 약 20%가 변경되었을 때 자동으로 VACUUM 작업이 실행됩니다.
현재, AWS CloudWatch Logs와 Datadog을 통해 Autovacuum 작업의 발생 내역을 모니터링하고 있습니다. 그러나 이 지표들은 데이터베이스 내에서 직접 확인할 수 있는 지표들과 일부 차이를 보였습니다. 지표상 차이가 있지만 Vacuum에 대해서 추가적인 모니터링은 진행하지 않고 있었지만 Autovacuum이 발생하는 시점에 Query의 패턴이 변경되거나 Slow Query가 다량으로 발생하게 되어 모니터링의 필요성을 느꼈습니다.
이에 따라, 데이터베이스 내에서 직접 확인 가능한 지표를 통해 Vacuum의 정확한 발생 현황 및 주기를 확인하기로 하였습니다. 기본적인 발생 현황과 시간을 기록해 두었으며, 추후에 더 많은 정보가 필요하다고 판단될 경우 데이터 수집을 고도화할 계획입니다.
SELECT psut.relname,
to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') AS last_vacuum,
vacuum_count,
to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI') AS last_autovacuum,
autovacuum_count
FROM pg_stat_user_tables psut
JOIN pg_class ON psut.relid = pg_class.oid- Index
인덱스는 데이터 검색 속도를 향상시키는 핵심 요소입니다. 인덱스를 통해 데이터베이스는 전체 테이블을 순차적으로 검색하는 대신, 효율적으로 필요한 데이터를 빠르게 찾을 수 있습니다. 그러나 데이터가 지속적으로 적재되고 변화함에 따라, 인덱스의 크기와 구조도 변할 수 있으며, 이는 시스템의 성능에 영향을 미칠 수 있습니다.
최근에 brin 인덱스로 설계한 인덱스의 효율이 낮아져 btree로 변경하는 작업을 준비하고 있습니다. 이와 같이 데이터베이스의 성능을 최적화하고 지속적으로 유지하기 위해서는 정기적인 인덱스 관리와 최적화가 필수적입니다. 이를 통해서 검색 속도를 유지하고, 데이터베이스의 전반적인 효율을 보장할 수 있습니다.
인덱스를 관리하기 위해서 우선적으로 미사용 중인 인덱스를 확인하여 불필요한 인덱스는 제거하며, 유지 관리 작업 시 재구성(Rebuild) 대상을 파악하고자 하였습니다. 추후에는 효율이 낮아진 인덱스에 대해서도 모니터링을 진행하고자 합니다.
# PostgreSQL
SELECT schemaname AS index_schema,
relname AS table_name,
indexrelname AS index_name,
pg_catalog.pg_total_relation_size(indexrelid) AS index_size,
idx_scan AS index_scan_count
FROM pg_catalog.pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = 'public'
# MySQL
SELECT i.INDEX_SCHEMA AS index_schema,
i.TABLE_NAME AS table_name,
i.INDEX_NAME AS index_name,
s.stat_value * @@innodb_page_size AS index_size,
p.COUNT_READ AS index_scan_count
FROM information_schema.statistics i
INNER JOIN mysql.innodb_index_stats s ON i.index_name = s.index_name and i.table_name = s.table_name
INNER JOIN performance_schema.table_io_waits_summary_by_index_usage p
ON s.index_name = p.index_name and s.table_name = p.object_name
WHERE INDEX_SCHEMA = 'test'
AND s.STAT_NAME = 'size'
AND p.COUNT_READ < 1000
AND i.INDEX_NAME != 'PRIMARY'
AND i.SEQ_IN_INDEX = 1- Partition
파티션은 대용량의 테이블을 관리하고 성능을 최적화하기 위한 효율적인 방법입니다. 저희는 주로 히스토리성으로 대용량 적재되는 테이블을 생성일 기준으로 파티션을 생성하고 있습니다. 파티션의 날짜는 작업일 기준으로 1년치의 파티션 추가를 진행하고 있습니다.
1년치의 파티션을 추가하는 이유는 불필요한 리소스 사용을 최소화하고 데이터 관리의 효율성을 높이기 위합니다. 너무 먼 미래의 파티션까지 생성할 경우 생성되어 있는 파티션 대비 사용하는 파티션이 적어집니다. 이는 스토리지 공간이 낭비되며 파티션 관리에 대한 오버헤드를 증가시킵니다.
따라서, 저희는 매년 만료되는 파티션을 확인하고, 3개월 이내에 만료되는 테이블에 대해서만 새로운 파티션 생성 쿼리 작업을 진행하고 있습니다. 해당 모니터링을 통해서 관리에 대한 리소스 사용을 최소화하면서 데이터 관리의 효율성을 최적화 하고자 하였습니다.
# PostgreSQL
WITH partition_base AS (
SELECT ipbase.relname as TABLE_NAME,
inhrelid::regclass AS partition_table, SPLIT_PART(
SPLIT_PART(SPLIT_PART(pg_get_expr(base.relpartbound, base.oid, true), 'FROM (''', 2), ''') TO (''', 2),
'''', 1) AS end_date
FROM pg_class base
INNER JOIN pg_inherits i ON i.inhrelid = base.oid
INNER JOIN pg_class ipbase ON i.inhparent = ipbase.oid
INNER JOIN pg_partitioned_table pt ON pt.partrelid = i.inhparent
WHERE pt.partstrat = 'r'
)
SELECT TABLE_NAME,
max(end_date)
FROM partition_base
WHERE end_date::DATE <= (CURRENT_DATE - INTERVAL '3 months')::DATE
GROUP BY TABLE_NAME
# MySQL
SELECT TABLE_SCHEMA,
TABLE_NAME,
PARTITION_EXPRESSION,
MAX(CAST(REPLACE(PARTITION_DESCRIPTION, '''', '') AS DATE)) AS max_date
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'test'
AND PARTITION_DESCRIPTION != 'MAXVALUE'
AND LEFT(TABLE_NAME, 1) != '_'
GROUP BY TABLE_SCHEMA, TABLE_NAME, PARTITION_EXPRESSION
HAVING max_date <= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 3 MONTH), '%Y-%m-%d')이 세 가지 모니터링 대상은 프로젝트의 초기 단계에서 선정된 핵심 지표들로, 데이터베이스의 성능과 관리 효율성을 높이는 데 중요한 역할을 합니다. 앞으로는 이 지표들을 정확하게 모니터링하고, 필요에 따라 추가적인 지표들로 확장해 나가고자 합니다.
배포
AWS Multi Account 및 Multi Region 배포를 진행하기 위해서 고려한 사항은 다음과 같습니다:
- 국가별 EC2를 분리 vs 하나의 EC2에서 모든 국가
- 국가별 Container를 각각 실행 vs 하나의 Container로 모든 국가 처리
- AWS Account 간의 접근
프로젝트 초기에는 각 국가마다 별도의 EC2 인스턴스를 배포하는 방식을 고려했습니다. 하지만, 비용과 관리의 복잡성을 고려하여 모든 국가의 컨테이너를 하나의 EC2 인스턴스에서 실행하기로 결정했습니다. 이 접근 방식은 관리의 용이성을 크게 향상시키고, 전반적인 시스템 비용을 절감하는 결과를 가져왔습니다.
또한, 각 국가별로 별도의 컨테이너를 실행하는 방식과 비교하여, 하나의 컨테이너에서 모든 국가의 데이터를 처리하는 방식을 선택했습니다. 이 결정은 애플리케이션의 확장성과 데이터 관리의 효율성을 높이는 데 기여했습니다. 특히, 공통된 인프라와 코드베이스를 활용하여, Multi Region에 대한 배포 시간을 줄일 수 있었습니다.
마지막으로 AWS Account 간의 접근을 용이하게 하기 위해 AWS의 Assume Role 기능을 활용했습니다. 이를 통해, 한 계정에서 다른 계정의 리소스에 대한 접근 권한을 안전하게 부여할 수 있었고, 이는 보안성 강화와 함께 운영 효율성을 높이는 결정이었습니다. Assume Role 설정을 통해, 특정 서비스의 데이터에 대한 접근을 필요로 하는 다양한 계정 간에 안전한 데이터 공유 및 관리가 가능해졌습니다.
마치며
Data Storage Automation 프로젝트를 진행하며 데이터베이스 내에서 모니터링 시 확인할 수 있는 테이블들을 자세히 알아볼 수 있는 좋은 기회가 되었습니다. 또한, SpoonRadio의 Multi Account 및 Multi Region을 고려하며 배포를 진행하는 과정에서 해당 환경에 대한 이해가 높아졌으며 필수 고려 사항을 이해하는 데 많은 도움이 되었습니다.
다른 업무와의 병행으로 프로젝트를 신속하게 진행해야 했기에, 추후 개선사항들이 아직 많이 남아있습니다. 지속적인 진행을 통해 프로젝트를 더욱 향상시키고 고도화하는 것을 목표로 하고 있으며, 이를 통해 보다 완성도 높게 구축해 나갈 계획입니다.
