SQLite 성능/특성 정리: WAL/locking/PRAGMA로 database is locked 줄이기

SQLite 성능/특성 정리: WAL/locking/PRAGMA로 database is locked 줄이기

이 글은 SQLite를 성능/운영 관점에서 길게 정리한 개인 노트입니다. 목표는 "지금 느린 이유"를 증거로 좁히고, 재발을 줄이는 체크리스트를 갖추는 것입니다.

전제: 성능은 DB만으로 결정되지 않습니다. 쿼리 패턴, 데이터 분포, 인덱스/스키마, 애플리케이션 트랜잭션 경계, 인프라(IO/네트워크)가 함께 결정합니다. 그래서 이 글은 '성능 모델(어디서 시간이 쓰이나) -> 설계/쿼리 -> 설정 -> 운영' 순서로 정리합니다.

SQLite는 '한 파일'이라는 단순함 덕분에 빠를 때는 매우 빠르지만, 동시 쓰기/락 모델을 모르면 쉽게 병목이 납니다. WAL과 트랜잭션 범위를 이해하는 게 핵심입니다.

이 DB를 언제 선택하나

정답은 항상 케이스 바이 케이스지만, 선택을 빠르게 만들기 위해 기준을 먼저 둡니다. 아래에 해당이 많을수록 이 DB를 고려할 만합니다.

- 모바일/데스크톱/임베디드처럼 로컬 단일 노드에서 강력한 SQL이 필요할 때
- 서버에서도 낮은 동시 쓰기 + 단순 운영이 목표인 내부 도구/에이전트
- 테스트/프로토타입에서 운영 비용을 최소화하고 싶을 때

성능 모델: 어디서 시간이 쓰이나

SQLite의 성능은 파일 IO + 락으로 설명됩니다. 특히 쓰기는 락을 잡고 직렬화되기 쉬워, 트랜잭션이 길어지면 다른 요청이 줄줄이 대기/실패할 수 있습니다.

WAL 모드는 읽기/쓰기를 더 잘 분리해 주고 많은 경우 동시 읽기 성능을 개선합니다. 하지만 체크포인트 정책과 디스크 특성에 따라 지연 스파이크가 생길 수 있습니다.

핵심 용어 빠른 사전

성능/장애 분석에서 자주 쓰는 단어는 팀마다 다르게 이해되기 쉽습니다. 용어를 짧게 정의하고, 어떤 지표/증상과 연결되는지 같이 적어둡니다.

용어	설명	실무에서 연결되는 것
WAL	Write-Ahead Logging 모드	동시 읽기 개선, 대신 체크포인트/디스크 IO 영향
Checkpoint	WAL 내용을 메인 DB로 반영	지연 스파이크/디스크 IO와 연결
SQLITE_BUSY	락 경합으로 인한 실패/대기	동시 쓰기/긴 트랜잭션에서 급증
busy_timeout	락 대기 시간	바로 실패 대신 짧게 대기/재시도
VACUUM	DB 파일 재작성/정리	파일이 비대해졌거나 freelist가 많을 때

옵션/핵심 요소(3~6)

자주 부딪히는 핵심 요소를 표로 먼저 고정해 두면, 장애/튜닝 때 팀 커뮤니케이션이 빨라집니다.

항목	의미	언제 쓰는지(실무 상황)
PRAGMA journal_mode=WAL	WAL 모드 전환	동시 읽기 성능을 올리고 싶을 때
PRAGMA synchronous	fsync 강도(내구성/지연)	내구성 요구와 지연을 조절할 때
busy_timeout	락 대기 시간	바로 실패 대신 대기/재시도로 완화할 때
BEGIN IMMEDIATE	쓰기 락을 빨리 확보	트랜잭션 시작 시 실패/대기를 명확히 하고 싶을 때
EXPLAIN QUERY PLAN	쿼리 플랜 확인	인덱스가 쓰이는지 확인할 때

실무 튜닝 포인트(설정/옵션별)

설정에는 정답이 없습니다. 워크로드(읽기/쓰기/동시성/데이터 크기) 기준으로 관측하고 조정합니다. 아래는 실무에서 가장 자주 만지는 레버를 '항목별'로 정리한 것입니다.

journal_mode=WAL
의미: 저널링을 WAL로 변경
언제 만지나: 읽기 동시성이 있고 쓰기도 적당히 있는 경우
주의/트레이드오프: 공유 파일시스템/백업/체크포인트를 같이 고려

synchronous=NORMAL/FULL
의미: fsync 강도
언제 만지나: 지연을 줄이거나 내구성을 높이고 싶을 때
주의/트레이드오프: 낮추면 장애 시 유실 가능성 증가

cache_size
의미: 페이지 캐시 크기(연결 단위)
언제 만지나: 반복 조회가 많아 IO를 줄이고 싶을 때
주의/트레이드오프: 연결이 많으면 총 캐시가 커질 수 있음

temp_store=MEMORY
의미: 임시 테이블 저장 위치
언제 만지나: 정렬/집계가 잦고 디스크 temp가 병목일 때
주의/트레이드오프: 메모리 사용 증가

busy_timeout
의미: 락 대기 시간(ms)
언제 만지나: 짧은 락 경합에서 실패 대신 대기
주의/트레이드오프: 대기 시간이 길면 요청 지연/큐잉 증가

wal_autocheckpoint
의미: WAL 자동 체크포인트
언제 만지나: WAL가 커지며 스파이크가 생길 때
주의/트레이드오프: 너무 잦으면 쓰기 성능 저하

mmap_size
의미: 메모리 매핑 사용량
언제 만지나: 큰 읽기에서 OS가 유리한 환경
주의/트레이드오프: 호환성/테스트 필요

foreign_keys
의미: FK 강제 여부
언제 만지나: 무결성 보장이 필요할 때
주의/트레이드오프: 쓰기 비용 증가 가능

쿼리/스키마 설계에서 성능이 갈리는 지점

SQLite에서 인덱스는 매우 중요합니다. 파일 기반이라 풀스캔이 IO로 직결되고, 동시 접근이 늘면 병목이 빨리 드러납니다. EXPLAIN QUERY PLAN으로 인덱스 사용 여부를 확인하는 습관이 필요합니다.

트랜잭션은 짧게가 기본입니다. 트랜잭션 안에서 오래 걸리는 작업(네트워크 호출/대기)을 하면 락 점유 시간이 길어져 SQLITE_BUSY가 급증합니다.

PRAGMA journal_mode=WAL;
PRAGMA busy_timeout=3000;
EXPLAIN QUERY PLAN SELECT * FROM logs WHERE user_id=42 AND created_at >= datetime('now','-7 day') ORDER BY created_at DESC LIMIT 50;
CREATE INDEX idx_logs_user_created ON logs(user_id, created_at DESC);

진단 명령/쿼리 모음(바로 실행용)

장애 때는 문서 찾을 시간이 없습니다. 아래는 현장에서 자주 쓰는 '첫 5분' 진단 명령/쿼리만 모은 것입니다. 환경/권한에 따라 일부는 제한될 수 있습니다.

PRAGMA journal_mode;
PRAGMA synchronous;
PRAGMA busy_timeout;
PRAGMA wal_checkpoint(TRUNCATE);
EXPLAIN QUERY PLAN SELECT 1;

자주 하는 실수(운영/튜닝)

실수 체크리스트:
- 동시 쓰기가 많은 서버 워크로드에 SQLite를 그대로 올린다
- 트랜잭션 안에서 네트워크 호출/긴 작업을 수행해 락 점유 시간을 늘린다
- WAL/체크포인트를 이해하지 못한 채 journal_mode 기본값에 의존한다
- busy_timeout/재시도 정책 없이 SQLITE_BUSY를 그냥 오류로 처리한다
- EXPLAIN QUERY PLAN 없이 풀스캔/정렬 비용을 방치한다
- 인덱스 없이 LIMIT만으로 빨라질 거라 기대한다

운영/모니터링 체크리스트

운영에서는 SQLITE_BUSY/timeout 빈도, WAL 크기/체크포인트 지연, 파일 IO 지연, 풀스캔 여부를 핵심으로 봅니다.

공통 체크리스트:
- 느린 쿼리 top N을 지속 수집(시간/호출수/총시간)
- 지연(p95/p99)과 오류율을 애플리케이션 지표와 함께 본다
- 캐시/메모리/디스크 IO 중 무엇이 병목인지 먼저 분리한다
- 복제/HA를 쓰면 일관성 요구가 높은 기능을 분리한다
- 튜닝/변경 후에는 동일 조건으로 재측정해 회귀를 막는다

트러블슈팅 루틴(순서 고정)

장애 때는 선택지가 너무 많아서 흔히 길을 잃습니다. 아래 순서를 팀 표준으로 정해두면, 원인 추적이 빨라지고 '감으로 설정만 만지는' 일을 줄일 수 있습니다.

1) SQLITE_BUSY/timeout 로그를 확인한다
2) 트랜잭션 범위를 줄이고 긴 작업을 밖으로 뺀다
3) WAL 모드와 busy_timeout을 점검한다
4) EXPLAIN QUERY PLAN으로 풀스캔/정렬을 확인한다
5) 필요한 인덱스를 추가한다
6) 체크포인트/IO 스파이크면 wal_autocheckpoint를 조정한다

문제 상황(정확히 1개)

상황 -> 서버에서 SQLite를 쓰는데 트래픽이 늘자 SQLITE_BUSY가 급증하고 일부 요청이 실패한다.
원인 -> 동시 쓰기가 늘면서 파일 락 경합이 커졌고, 트랜잭션이 길어 락 점유 시간이 길었다.
해결 -> WAL 전환 + busy_timeout/재시도 적용, 트랜잭션을 짧게 분리, 필요한 인덱스를 추가한다.
예방 팁 -> 쓰기-heavy로 성장할 가능성이 있으면 임계치에서 서버 DB로 마이그레이션 계획을 준비한다.

참고/출처

정확한 동작/버전별 차이는 공식 문서를 기준으로 확인합니다. 실무에서는 버전 차이가 곧 성능/장애 차이입니다.

• SQLite Documentation
• SQLite: WAL
• SQLite: Query Planner