안녕하세요. SQLER의 코난 김대우입니다.
이번 강좌에서는, 4-6. 데이터베이스의 데이터와 로그를 진행 하겠습니다.
SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록
이번 강좌에서는 데이터베이스 데이터와 로그에 대해 조금 더 살펴봅니다. 데이터에 대해서는 이미 잘 이해하셨을 테니, 로그를 더 깊게 살펴보게 됩니다. SQL Server 내부 프로세스라 내용이 조금 어려울 수 있으니 가볍게 읽고 넘어가셔도 됩니다.
TL;DR
로그 파일은 변경된 데이터 기록을 저장합니다. 트랜잭션은 메모리에 변경 내용을 저장한 후 완료 시 로그에 기록되고, 체크 포인트 프로세스를 통해 디스크에 실제로 기록됩니다.
데이터와 로그
데이터 파일은 사용하는 데이터가 저장되고, 로그 파일은 변경된 데이터 기록이 쌓입니다.
예를 들어,
INSERT구문으로 데이터를 추가하는 명령을 수행하면 과연 어떤 일이 발생하는지 살펴보겠습니다.
INSERT구문이 실행되면 삽입될 내용이 데이터에 기록되는 게 아니라 먼저 로그에 기록됩니다. 좀 더 엄밀하게 말씀드리면? 로그에 기록되는 게 아니라 일종의 캐시 기능으로 SQL Server의 Buffer 메모리 영역에 기록됩니다.
좀 더 정확히 “메모리에 저장된다”의 의미는, 트랜잭션 안에서 변경이 일어날 경우만 메모리에 기록되며 트랜잭션이 완료되면 즉시 로그에 기록됩니다.(트랜잭션 중에 기록이 될 수도 있습니다. 이를 더티 페이지(Dirty Page)라고 합니다. 후반 강좌인 잠금(LOCK) 강좌에서 자세히 소개합니다.
참고로 트랜잭션이란 작업 단위로, 분할할 수 없는 한 번에 처리하는 작업 단위입니다.
메모리에 저장? 휘발성 데이터? 어떤 의미인가요?
메모리는 디스크와는 달라서, 비정상 종료 같은 시스템 문제 발생 시 복구할 방법이 없습니다.
트랜잭션은 ALL or NOTHING 작업입니다. 어떻게 동작한다는 말인가요?
우선, 트랜잭션이라는 일의 단위가 끝나면 이 메모리에 기록된 내용(변경내용)을 로그에 기록합니다. 그리고 체크 포인트 프로세스(검사점 - Checkpoint Process)라는, 약 60초 정도에 한 번씩 퍼지 알고리즘과 더티 페이지 양에 따라 랜덤 하게 실행되는 SQL Server 프로세스에 의해, 메모리에 변경된 내용이 디스크의 파일로 실제 기록됩니다. 디스크에 쓸 때, 로그 파일과 데이터 파일 양쪽에 기록합니다.
예를 들어,
EXEC sp_who2;
라는 SQL Server의 세션 정보를 보면 LAZY WRITER , LOG WRITER , CHECKPOINT라는 명령을 실행하는 시스템 세션 프로세스가 보입니다. 참고로 LAZY WRITER(지연 기록)는 메모리 버퍼 캐시를 비울 때 처리 되는 녀석으로 역시나 캐시의 내용을 무조건 디스크로 기록하게 됩니다. LOG WRITER는 트랜잭션 완료 시 완료된 로그만 디스크에 기록을 하는 내부 프로세스로 SQL Server의 내부 스케줄러에 의해 4개 이상(NUMA 노드와 MAX_LOG_WRITERS 설정에 따라) 유지됩니다.
이 과정은 SQL Server의 성능 튜닝 과정을 위해 많은 이해가 필요합니다. 특히, CPU를 많이 사용하는 IO과 밀접한 관련이 있기 때문에 대용량 데이터베이스 운용 시 중요합니다.
☑️ 챗GPT 활용: SQL Server NUMA 노드에 대해서 알려줘
버퍼 캐시 메모리? 로그? 디스크? SELECT 쿼리 동작
그럼 우리가 SELECT 하는 순서는 내부적으로 어떻게 동작할까요? 먼저 데이터 파일에서 읽고 관련 데이터가 로그에 있는지 판단해 읽고, 이어서 캐시 내에 데이터가 있는지 판단해 읽어오는 내부적인 순서로 SELECT 작업이 이루어집니다.
데이터베이스 로그와 복구
끝으로, 간단하게 데이터베이스 복원에서 로그의 역할을 소개합니다. 백업과 복원 강좌에서 다양한 복원의 방식을 실제로 배우게 됩니다.
데이터베이스 백업 작업은 일반적으로 전체 백업(Full Backup)과 변경본인 로그를 백업(Transaction Log Backup)합니다.
그럼 복구할 경우에는 어떻게 동작하나요?
풀 백업본을 복원하고 이어서 데이터 변경본인 로그를 순차적으로 재실행해 복원시키면 원상태가 됩니다.
하지만 중요한 로그 백업이 없다면! 최근 데이터 변경본까지 데이터베이스의 복구가 어려워집니다. (데이터베이스 복구 모델이 단순(Simple)이 아니고, 로그가 존재해야 복구가 가능합니다.)
데이터베이스 로그가 얼마나 중요한 녀석인지 이해하셨을 거에요.
로그 내용을 보고 싶어요
종종 이런 질문이 올라옵니다. “데이터베이스 LOG를 사용자가 볼 수는 있는가”입니다. 어느 정도 암호화된 내용을 볼 수는 있습니다.
-- 모든 데이터베이스의 로그 파일과 사용량을 확인
DBCC SQLPERF(LOGSPACE)
GO
-- 로그 데이터 파일 정보 조회
USE AdventureWorks
GO
DBCC LOGINFO
GO
-- 로그 정보 확인
DBCC LOG('AdventureWorks', 4)
GO
-- 로그 상세 정보 확인
DBCC LOG('AdventureWorks')
GO
DBCC LOG라는 명령으로 일부 볼 수 있습니다. 실제 공식 가이드에서 DBCC LOG 명령은 자료를 제공하지 않습니다. 실행하면 이상한 암호 같은 정보가 보입니다. 이 결과가 로그 데이터이며, 상세 내용을 볼 수는 없습니다.
좀 더 DBCC LOG 명령과 LOG가 궁금하시면 아래 링크를 참고하세요.
- How to determine SQL Server database transaction log usage
- DBCC LOG - Google groups
- DBCC LOG in SQL 2000 Part 1
간략히 데이터베이스에서 어떤 일들이 진행되는지 내부적인 처리 과정을 소개해 드렸습니다.
로그의 중요성을 다시 한번 생각해 보시고, 항상 데이터와 로그를 모두 잘 관리하시길 바랍니다.
SQL 강좌 책 구매
강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다.
책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022
