SQLite를 운영하며 새롭게 배운 몇 가지

 (jvns.ca)
1P by GN⁺ | ★ favorite | 댓글 1개
  • 소규모 웹사이트의 프로덕션 환경에도 SQLite를 쓸 수 있지만, Django ORM이 맡는 작업이 늘면서 데이터베이스의 운영 복잡성을 체감함
  • 4,000행 테이블의 FTS5 검색이 5초 걸렸으나 ANALYZE 실행 후 약 0.05초로 줄어, 쿼리 플래너 통계가 성능에 큰 영향을 줄 수 있음을 확인함
  • 대량 DELETE가 5초를 넘으면 다른 쓰기 작업도 시간 초과되고 워커와 VM까지 종료될 수 있어, 정리 작업을 작은 배치로 나눠 처리함
  • 백업에는 VACUUM INTO와 restic을 사용했지만 OOM 종료를 겪었으며, 더 효율적인 증분 백업을 위해 Litestream도 시험 중임
  • 테이블을 여러 SQLite 파일로 분리할 수 있으며, Mess with DNS는 2022년부터 4년간 SQLite로 운영돼 프로젝트에 적합한 선택이었다고 평가함

Django 사이트에서 SQLite 운영하기

  • 소규모 프로덕션 사이트에서도 SQLite를 사용할 수 있다는 여러 글을 참고해 Django 사이트의 데이터베이스로 채택함
  • SQLite 역시 운영 지식이 필요한 복잡한 데이터베이스이며, 이번 프로젝트에서는 Django ORM을 통해 이전보다 더 많은 작업을 맡기고 있음
  • 여러 권장 사항에 따라 처음부터 WAL 모드를 활성화해 운영함
  • 이번 사이트는 SQLite를 사용한 네 번째 웹사이트임

ANALYZE가 바꾼 검색 성능

  • 4,000행 테이블에서 SQLite FTS5를 이용한 전문 검색 쿼리에 5초가 걸림
  • ANALYZE를 실행하자 같은 쿼리가 약 0.05초로 단축돼 더 조사할 필요가 없을 정도가 됨
  • 정확히 어떤 쿼리 계획 문제가 있었는지는 확인하지 못했으며, 의도치 않은 이차 시간 복잡도와 비슷한 상황이었을 것으로 추측함
  • ANALYZE는 테이블 행 수 등을 포함한 통계 정보를 생성해 쿼리 플래너가 더 나은 선택을 하도록 도움
  • 아직 쿼리 계획을 읽는 방법은 익히지 못한 상태임

데이터 정리와 단일 쓰기 제약

  • django-tasks-db의 완료된 작업처럼 불필요한 행을 대량으로 삭제할 때 연쇄적인 문제가 생김
    • 많은 행을 처리하는 정리 명령이 5초 이상 실행됨
    • 그동안 다른 워커의 데이터베이스 쓰기가 설정된 5초 제한을 넘겨 시간 초과됨
    • 쓰기에 실패한 워커가 충돌하고 VM도 종료됨
  • DELETE가 느린 정확한 이유는 확인하지 못했으며, 트랜잭션 안에서 많은 Python 코드가 실행되기 때문일 가능성도 고려하고 있음
  • 현재는 각 데이터베이스 쿼리가 5초를 넘지 않도록 정리 작업을 작은 배치로 분할함
  • 이 경험을 통해 여러 쓰기 작업을 동시에 처리할 수 있는 Postgres 같은 데이터베이스가 필요한 이유를 체감함
  • 향후에는 이런 작업 중 사이트를 내려 예정된 유지보수를 수행하는 방안도 고려하지만, 아직 작업 흐름은 마련하지 못함

ORM 쿼리와 현재 데이터 규모

  • 지금까지 Django ORM으로 원하는 쿼리를 만들면서 성능을 별도로 점검하지 않았지만, ANALYZE 문제 외에는 대부분 정상적으로 작동함
  • 데이터베이스는 약 10,000행으로 작으며 앞으로도 계속 작은 규모를 유지할 것으로 예상함

SQLite 백업 방식

  • SQLite 백업에는 restic과 Litestream 두 가지 방식을 사용해 봄
  • 백업 작업은 보통 데드맨 스위치로 감시하지만, 실제 복원 테스트는 아직 하지 않은 것으로 보임

  • restic을 이용한 백업

    • VACUUM INTO로 데이터베이스 사본을 만들고 gzip으로 압축한 뒤 S3에 업로드함
    • restic으로 백업 생성, 스냅샷 확인, 오래된 백업 삭제 및 정리를 수행함
    • 백업이 간혹 OOM으로 종료되면서 잠금이 남아 restic unlock도 실행함
    sqlite3 /data/calendar.db "VACUUM INTO '/tmp/calendar.sqlite'"
gzip /tmp/calendar.sqlite
restic -r s3://s3.amazonaws.com/some_bucket/ unlock
restic -r s3://s3.amazonaws.com/some_bucket/ backup /tmp/calendar.sqlite.gz
restic -r s3://s3.amazonaws.com/some_bucket/ snapshots
restic -r s3://s3.amazonaws.com/some_bucket/ forget -l 1 -H 6 -d 2 -w 2 -m 2 -y 2
restic -r s3://s3.amazonaws.com/some_bucket/ prune

  • Litestream을 이용한 증분 백업

    • restic 백업의 OOM 종료를 피하고 증분 백업의 효율을 시험하기 위해 Litestream을 사용하기 시작함
    • 설정 파일을 작성한 뒤 다음 명령으로 복제를 실행함
    litestream replicate -config litestream.yml
    • 데이터베이스 이력을 일정 기간 유지하려고 retention: 400h를 지정했지만, 의도대로 작동하는지는 아직 확인하지 못함
    • AWS에 백업하고 있으나 콘솔에서 자격 증명을 생성하기가 번거로워, 향후 다른 S3 호환 서비스로 옮기는 방안도 고려함

여러 SQLite 데이터베이스 파일 사용

  • 현재 프로젝트는 데이터베이스 하나만 사용하지만, Mess with DNS에서는 함께 둘 필요가 없는 테이블을 3개 데이터베이스 파일로 분리함
  • 이러한 분리가 운영에 도움이 됐다고 판단함
  • Mess with DNS는 2022년부터 4년 동안 SQLite로 운영됐으며, 해당 프로젝트에서는 Postgres에서 SQLite로 옮긴 것이 좋은 선택이었음

운영하며 뒤늦게 발견한 기본 기능

  • 웹 프로젝트에서 SQLite를 처음 사용한 시점은 2022년이지만 ANALYZE의 존재는 이번에 처음 알게 됨
  • 사용하는 기술의 기본 기능도 실제 운영 과정에서 수년에 걸쳐 계속 배우게 됨

함께 보면 좋은 글 β

GN⁺   [-]
Hacker News 의견들

  • SQLite의 .expert 모드를 쓰면 쿼리 계획 읽기를 배우는 날을 조금 미룰 수 있음: https://www.sqlite.org/cli.html#index_recommendations_sqlite...
    SELECT * FROM x1 WHERE a=? AND b>?;를 분석하면 CREATE INDEX x1_idx_000123a7 ON x1(a, b);처럼 권장 인덱스를 알려주고, 이를 생성한 뒤 다시 분석하면 새 인덱스가 필요 없다고 표시함
    Postgres 같은 “진짜” 데이터베이스에서도 정리 작업은 보통 작은 배치로 나누는 것이 권장됨. 단지 소규모에서는 비효율적인 작업이라는 사실이 덜 드러날 뿐이므로, 원문의 접근은 생각보다 더 옳았음

    • 일부 데이터베이스에서 1,000만 행을 삭제하면 그만큼의 실행 취소 데이터도 기록됨. Oracle에서는 이를 제때 백업하고 비우지 못하면 아카이브 로그용 디스크 공간이 고갈될 수 있음
      자주 커밋하면 도움이 되지만, 대규모 데이터베이스를 정기적으로 정리할 때는 파티셔닝이 가장 효과적이었음. 가장 오래된 파티션을 삭제하면 거의 즉시 끝남
      다만 “작업자가 데이터베이스에 쓰지 못해 충돌하고 VM이 종료된다”에서 VM이 왜 종료되는지는 불분명함. 여기서 VM은 운영체제가 실행되는 가상 머신을 뜻하는 것으로 보임
    • 행 기반 복제를 사용하는 대규모 MySQL에서는 수백만 행을 바꾸는 UPDATE나 DELETE를 배치로 실행해야 했음. 쿼리 하나로 처리하면 갱신된 수백만 행을 모든 복제본에 한꺼번에 전송해야 하기 때문임
    • EXPLAIN QUERY PLAN 과 비슷해 보임: https://sqlite.org/eqp.html
      일반 EXPLAIN은 보통 필요 이상으로 장황한 바이트코드를 출력하지만, EXPLAIN QUERY PLAN은 요약된 계획을 보여줌
    • “비효율적인 작업이라는 사실을 덜 드러낸다”는 점을 장점으로 보는 것인지 궁금함

  • 데이터베이스를 다루는 입장에서 읽기 답답했고, 직접 원인을 찾아 해결하고 싶었음. 행이 1만 개뿐인 테이블이라면 전체 테이블 스캔도 매우 빨라야 하며, 같은 물리 서버에서 프로세스 내부로 실행되는 SQLite라면 더욱 그럴 것임
    머릿속에 떠오른 해법은 당연히 “인덱스를 생성하라”였음. 느린 삭제는 ORM 사용자가 내부 데이터베이스 상호작용을 이해하기 전 흔히 겪는 고전적인 N+1 문제일 가능성이 높아 보이며, Julia가 후속 글을 올리길 바람

  • AWS 콘솔에서 자격 증명을 만드는 일이 너무 번거로워 몇 년 전 그 문제만 해결하는 도구를 만들었음
    uvx s3-credentials create my-existing-s3-bucket
    이 명령은 해당 버킷에만 범위가 제한된 읽기·쓰기 자격 증명을 출력함. --read-only, --write-only로 권한을 더 제한하거나 --prefix foo/bar로 특정 접두사로 시작하는 키만 읽고 쓰게 할 수 있음
    Restic과 Cloudflare R2도 함께 사용해 봤는데 아주 잘 작동했음

    • 복잡한 AWS 서비스를 다루는 더 일반적인 방법은 Terraform을 조금만 익힌 뒤 나머지를 LLM에 맡기는 것임. 생성한 자원을 기억할 필요 없이 나중에 철거하기도 훨씬 쉬워짐
    • 비슷한 기존 도구로 https://litestream.io/도 있음
    • 아주 훌륭한 도구라 AWS CLI가 흡수할 만해 보임. 다만 쓰기 전용 자격 증명이 언제 필요한지는 궁금함

  • LLM 시대가 되니 Julia의 글을 더욱 높이 평가하게 됨. 진솔한 탐구 과정은 자신만만하게 모든 것을 아는 척하는 자동 생성 글의 해독제임

  • 백업을 다음과 같이 실행함:
    OUT="${i}.sql.zst"
    PART="${OUT}.part"
    sqlite3 -readonly "${i}" .dump | zstd --fast --rsyncable -v -o "${PART}" -
    mv "${PART}" "${OUT}"
    쓰기 작업자가 WAL을 사용하면 이를 막지 않으며, 압축률이 좋으면서도 동기화하기 쉬운 덤프가 만들어짐. Home Assistant 데이터베이스는 1.8GB지만 압축된 덤프는 286MB이고, 매일 생성되는 내용의 약 90%가 동일할 것으로 추정함

    • Home Assistant에서 무엇을 백업하는지 궁금함. 기본 백업은 너무 커서 설정만 남기고 동영상, 캐시, HACS가 내려받은 저장소는 제외했는데 중요한 것을 놓치고 있는지 모르겠음
      데이터베이스가 그렇게 커진 이유가 장기간 보존한 시계열 기록 때문인지도 궁금함
    • VACUUM INTO, .backup, sqlite3_rsync, Litestream 역시 쓰기 작업자를 막지 않음. .backup은 백업 API를 사용함
    • 실시간 데이터베이스에서 .dump 때문에 한 번 잠긴 뒤 .backup으로 전환했음. 그래도 .part에 쓴 뒤 mv 하는 방식은 깔끔함

  • “더 조사하지 않았다”, “최선의 추측이다”, “아마 다른 것들도?”, “트랜잭션 안에서 Python 코드가 많이 실행될지도 모른다” 같은 표현뿐이라 글에 실질적인 내용이 없다고 봄. 제대로 알아보거나 조사하지 않은 채 때로는 틀린 추측까지 함
    Debian 사용자로서 Linux 관련 검색 결과에 Ubuntu 포럼이 나오면 열어보지 않는 이유도 잘못된 추측이 너무 많기 때문임. 반대로 Arch는 Debian과 크게 다르지만, 지식 있는 이들이 작성한 Arch Wiki는 대체로 확인함

    • Julia Evans는 기술 지식이 매우 풍부하며, 기술의 신비감을 걷어내고 실제 문제 해결 과정이 어떤지 이해시키는 데 특히 뛰어남
      이 글은 SQLite 세계 최고 전문가의 설명인 척하지 않으며, 제목부터 “SQLite 운영에 관해 몇 가지 배우기”라고 기대치를 명확히 설정함
      모든 것을 알거나 아는 척할 필요 없이, 간단한 방법으로 문제를 파악하며 지식을 쌓을 수 있다는 메시지가 Julia의 글 전반에 흐름. 지금까지 알아낸 내용을 최대한 명확히 공유하는 것은 좋은 태도임
    • 이 글은 해당 기술을 사용하는 똑똑한 일반 사용자의 상황을 잘 보여줘서 훌륭하다고 봄. 글쓴이의 초점은 웹사이트 운영이며, 이런 문제는 특정 도구만 종일 다루지 않는 일상적인 사용자들이 흔히 걸려드는 부분임
      나도 어제 기능 하나를 만들면서 프로그래밍 언어 2개, 빌드 시스템 2개, 클라우드 제공자, 비밀 관리 도구, 두 언어에 걸친 복잡한 클라이언트·서버 통신 프레임워크, 버전 관리 시스템, 편집기, CI 도구를 사용했음. 여기에 운영체제와 런타임 버전, 데이터베이스, 역방향 프록시, 캐시, 도메인 로직까지 파고들 수 있음
      마주치는 모든 주제를 깊이 파면 아무 일도 끝내지 못하므로, 글쓴이처럼 집중할 문제를 선택해야 함
    • Arch Wiki는 최고의 Linux 자료 중 하나임. Mint를 사용할 때는 늘 찾아봤지만, 정작 Arch를 사용하게 된 뒤에는 Mint 시절보다 덜 찾아보게 됨

  • DELETE 문제는 배치 삭제, 배치 사이 지연, SELECT로 rowid를 미리 불러오는 방식으로 쉽게 완화할 수 있음. SELECT는 차단을 일으키지 않음
    데이터가 같은 테이블에 순차적으로 추가됐다면 파일에도 비슷한 순서로 저장됐을 가능성이 높으므로, 그 순서나 역순으로 삭제하면 더 빨라질 수 있음. 저장 매체와 다른 조건에 따라 결과는 달라짐

    • 행 ID 사전 로딩은 SQLite뿐 아니라 대규모 Aurora MySQL이나 Postgres 클러스터에서도 매우 효과적이었음. SELECT를 복제본으로 보낼 수 있었고, 행 필터링에 따른 인덱스 메모리 압박이 데이터베이스의 CPU와 버퍼 캐시에 큰 부담을 주고 있었기 때문임
      파티션 가지치기 같은 방식으로 불필요한 데이터를 주요 처리 경로에서 제외하기 어렵다면 매우 강력한 전략임

  • 현재 익숙한 수준이나 업무 요구보다 데이터베이스를 조금 더 깊게 파는 것은 여전히 실력을 높이는 좋은 방법임. 많은 웹 개발자가 데이터베이스 도구 앞에서 막히곤 하며, 나도 K8s 같은 일부 운영 기술에서는 비슷하게 막힘
    SQL이 어떻게 디스크에서 데이터를 읽고 쓰는 작업으로 변환되는지 알아두면 어떤 접근이 괜찮을지 직관적으로 판단하는 데 큰 도움이 됨. 데이터베이스의 잠금 체계도 함께 이해해야 함
    이런 지식이 있으면 Postgres에서 단순한 COUNT조차 빨리 끝나지 않을 때 당황할 가능성이 줄어듦

  • sqlite_stat1과 sqlite_stat4에는 인덱스 값 분포에 관한 여러 통계 정보가 저장되며, 쿼리 플래너는 이를 바탕으로 인덱스의 선택도와 유용성을 추정함
    sqlite_stat1은 인덱스의 레코드 수와 값당 평균 레코드 수만 제공하고, 활성화하면 sqlite_stat4가 히스토그램 데이터도 저장함

  • Litestream은 매우 흥미로우며 S3를 백엔드로 연결해 실행하는 데 성공했음. SQLite를 사용하는 수많은 앱을 파일 시스템 상태에 거의 의존하지 않게 만들 수 있음
    S3의 상태는 훨씬 관리하기 쉽고, 백업과 동기화도 제공자가 처리해 줌

답변달기