컴퓨터 간 SQLite 데이터베이스를 더 빠르게 복사하는 방법

 (alexwlchan.net)
1P by GN⁺ | ★ favorite | 댓글 1개
  • 큰 SQLite 데이터베이스를 원격 서버에서 로컬로 옮길 때 원본 .db 대신 gzip 압축 SQL 덤프를 복사하면 전송량이 크게 줄어듦
  • 파일이 커지는 주요 원인은 인덱스이며, 쿼리를 빠르게 하려고 테이블 데이터를 중복 저장해 디스크 사용량을 늘림
  • SQLite의 .dump는 데이터베이스를 SQL 문장 텍스트로 만들고, 인덱스는 실제 데이터가 아니라 CREATE INDEX 한 줄로 표현해 중복 전송을 피함
  • 예시 데이터베이스는 원본 3.4GB, 덤프 텍스트 1.3GB, gzip 압축본 240MB였고 압축 덤프가 원본보다 14배 작았음
  • 복사 전에 덤프 파일을 고정해 두면 전송 중 원본이 바뀌어 database disk image is malformed 오류가 나는 상황을 줄일 수 있음

SQLite 덤프를 압축해 복사하기

  • 작은 SQLite 데이터베이스는 rsync로 직접 복사해도 충분함
    • 예시: rsync --progress username@server:my_remote_database.db my_local_database.db
  • 데이터베이스가 커지면 직접 복사는 느리고 불안정해질 수 있음
    • 250MB 데이터베이스를 가정용 인터넷으로 웹 서버에서 내려받는 데 약 1분이 걸림
    • 여러 GB 크기의 데이터베이스에서는 대기 시간이 더 커짐
  • 크기 증가의 병목은 인덱스에 있음
    • 인덱스는 쿼리 속도를 크게 높이지만 데이터베이스 파일도 키움
    • 한 데이터베이스에서는 특정 인덱스 하나가 디스크 크기의 절반을 차지함
    • 인덱스는 고유 데이터를 담기보다 다른 테이블 데이터를 중복해 조회를 빠르게 함
  • SQLite는 데이터베이스 전체를 텍스트 파일로 덤프할 수 있음
    • 명령: sqlite3 my_database.db .dump > my_database.db.txt
    • 결과 파일은 CREATE TABLE, INSERT INTO, CREATE INDEX 같은 SQL 문장으로 구성됨
    • 인덱스는 실제 인덱스 데이터가 아니라 CREATE INDEX [idx_photo_locations] ON [photos] ([longitude], [latitude]); 같은 생성 명령으로 저장됨
  • 덤프 파일은 다시 SQLite 데이터베이스로 복원 가능함
    • 명령: cat my_database.db.txt | sqlite3 my_reconstructed_database.db
  • SQL 덤프는 반복이 많아 gzip 압축 효율이 높음
    • 예시: sqlite3 explorer.db .dump | gzip -c > explorer.db.txt.gz
    • 크기 비교:
      • 원본 SQLite 데이터베이스: 3.4GB
      • 텍스트 덤프: 1.3GB
      • gzip 압축 텍스트: 240MB
    • gzip 압축본은 원본 SQLite 데이터베이스보다 14배 작았음

실제 절차와 안정성 효과

  • 새 방식은 원격 서버에서 압축 덤프를 만들고, 그 파일만 내려받은 뒤 로컬에서 데이터베이스를 재구성함
  • 복사 절차는 다음과 같음
    • 서버에서 gzip 압축 텍스트 파일 생성: ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"
    • 로컬로 복사: rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz
    • 서버의 임시 압축 파일 삭제: ssh username@server "rm my_remote_database.db.txt.gz"
    • 로컬 압축 해제: gunzip my_local_database.db.txt.gz
    • 로컬 데이터베이스 재구성: cat my_local_database.db.txt | sqlite3 my_local_database.db
    • 로컬 텍스트 파일 삭제: rm my_local_database.db.txt
  • 압축 덤프는 일관된 복사 원본 역할을 함
    • 큰 데이터베이스를 오래 복사하는 동안 원본이 갱신되면, 결과 파일의 앞부분은 갱신 전이고 뒷부분은 갱신 후인 상태가 될 수 있음
    • 이런 파일은 로컬에서 열 때 database disk image is malformed 오류를 낼 수 있음
  • 복사 전에 텍스트 덤프를 만들면 rsync가 전송하는 파일이 중간에 바뀌지 않음
    • 완전하고 일관된 텍스트 파일을 복사할 가능성이 높아짐
  • 큰 SQLite 데이터베이스를 자주 옮긴다면 이 방식으로 다운로드 시간을 줄이고 복사 신뢰도를 높일 수 있음

함께 보면 좋은 글 β

GN⁺   [-]
Hacker News 의견들

  • 실행 중이고 갱신을 받는 데이터베이스 파일을 그대로 복사하면 당연히 손상될 수밖에 없음
    SQLite 데이터베이스를 안전하게 복제하려면 Litestream이 있음: https://github.com/benbjohnson/litestream

    • Litestream은 평범한 SFTP 위에서도 동작하므로, SSH로 접근 가능한 거의 모든 UNIX 엔드포인트에 데이터베이스 복제를 스트리밍할 수 있음
      선호하는 서비스는 있지만 어떤 SFTP 서버든 가능함
      [1] https://github.com/benbjohnson/litestream/issues/140
      [2] https://www.rsync.net/resources/notes/2021-q3-rsync.net_technotes.html
    • “반드시” 손상된다는 말에는 예외가 있음. Btrfs나 ZFS에서 파일 또는 하위 볼륨을 원자적으로 복사하면, ACID 데이터베이스나 LSM 트리라면 최악의 경우 롤백만 일어남
      물론 여러 파일이라면 모두 같은 트랜잭션 안에서 복사되도록 하위 볼륨으로 묶어야 하고, 단순히 cp --reflink=always만으로는 부족함
      SIGSTOP으로 프로세스를 멈추는 것도 비슷한 결과를 낼 수는 있겠지만, 거기에 기대고 싶지는 않음
    • 내장 .backup 명령도 실행 중인 데이터베이스의 스냅샷 버전을 만들어 복사할 수 있게 하려는 공식 도구임
    • 내 시스템에서는 Litestream을 잘 쓰고 있지만, 내장 도구만으로 비슷한 작업을 수동으로 하는 방법을 꽤 자세히 안내한 문서도 좋음: https://litestream.io/alternatives/cron/
    • Litestream은 정말 멋짐. 휴가 중 창고가 침수됐던, 작은 KVM 스타트업을 만든 전 Google 출신이 했던 것처럼 컨테이너 수준에서 SQLite 백업과 복구에 써볼 계획임
      정확히 기억한 게 맞다면 그가 완벽한 가이드를 썼는데, 찾을 가능성은 0에 가까움. 이 레퍼런스를 아는 사람이 링크를 올려주면 좋겠음

  • 컴퓨터 간 데이터베이스 복사라면서 원만 그려놓고 나머지 올빼미는 생략한 느낌임
    다른 사람들이 말했듯이 증분 rsync가 훨씬 빠르겠지만, 더 거슬리는 건 SQL 문을 보내는 게 데이터베이스를 보내는 것보다 빠르다고 하면서 그 SQL 문을 실행해야 한다는 사실을 완전히 빼먹은 부분임. 그다음 /optimize/도 돌리고 /vacuum/도 돌려야 함
    지금 CSV 파일에서 데이터베이스를 “증분 재구축”해야 하는 상황이 있는데, 내 경우에는 처음부터 다시 만드는 게 더 최적임. 그래도 많이 최적화했는데도 빈 메모리 데이터베이스에 배치 삽입을 하고 인덱스를 만드는 데만 30분이 걸림

    • https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite를 봤기를 바람
      SQLite3에서 빠르게 삽입하는 방법을 아주 잘 정리한 글임
    • 어떤 최적화든 여기서는 병목이 어디인지가 중요함. 원문 사례는 대역폭이 병목이고 CPU/디스크 I/O는 충분한 듯함
      250MB 데이터베이스를 내려받는 데 1분이 걸린다고 했는데, 나는 1Gbps 광회선 덕분에 회사 서버에서 2GB짜리 SQLite 테스트 데이터베이스를 15초 만에 가져왔음
    • 30분은 길어 보임. 데이터가 아주 많은가? 많은 JSON 데이터로 SQLite 데이터베이스를 부트스트랩하는 작업을 하고 있는데, 값을 리스트로 모아두고 1만 개씩 삽입하면 수백만 행도 몇 분 안에 넣을 수 있는 꽤 좋은 성능 sweet spot을 찾았음
      Bloom 필터와 LRU 캐시 같은 요령을 써야 했지만, 이제 6GB 데이터베이스를 20분 정도에 만들 수 있음

  • SQLite에는 이 용도의 공식 도구가 있음: https://www.sqlite.org/rsync.html
    페이지 수준에서 동작함
    “프로토콜은 복제본이 각 페이지의 암호학적 해시를 원본 쪽으로 보내고, 원본은 해시가 일치하지 않는 페이지의 전체 내용을 되돌려 보내는 방식이다”

    • 맞지만 아쉽게도 SQLite 팀은 그 도구를 대부분의 배포판과 brew가 SQLite 패키징에 쓰는 autotools tarball에 포함하지 않음
      이 도구를 쓰려면 직접 컴파일하는 방법밖에 없음

  • 최근 릴리스된 sqlite_rsync 유틸리티는 SQLite 데이터베이스의 내부 구조에 맞게 최적화한 rsync 알고리즘 버전을 사용함. 내부 데이터 페이지를 효율적으로 비교한 뒤 변경되었거나 없는 페이지만 동기화함
    글의 요령들도 좋지만, 이제는 내장 유틸리티를 쓰는 편이 더 쉬움 :)
    동작 원리를 자세히 블로그에 적어둠: https://nochlin.com/blog/how-the-new-sqlite3_rsync-utility-works

    • sqlite3_rsync는 이제 rsync.net 플랫폼에 내장되어 있음
      ssh user@rsync.net sqlite3_rsync … blah blah …
      지난주에 막 추가돼 아직 모든 리전에 배포되진 않았지만, 초기 사용자들은 모두 기대한 그대로 동작했다고 함
    • sqlite_rsync는 WAL 모드에서만 쓸 수 있음. WAL 모드의 추가 제약으로 데이터베이스 파일이 로컬 디스크에 있어야 함
      대부분은 당연히 그렇게 하겠지만, 그게 불가능한 경우에는 이 유틸리티를 쓸 수 없음
    • 자주 동기화한다면 대역폭 때문에 페이지 크기를 키워야 함

  • rsync가 제공하는 전송 중 압축을 시도하지 않은 점이 의외였음
    -z, --compress는 전송 중 파일 데이터를 압축하고, --compress-level=NUM은 압축 수준을 명시적으로 지정함
    아마 gzip으로 압축한 뒤 전송하는 편이 더 빠를 수도 있지만, 플래그 하나로 전송을 개선할 수 있다는 점은 좋음

    • 손상 문제를 언급했다면 -z와 함께 sqlite3_rsync(https://sqlite.org/rsync.html)를 쓰는 편이 더 나음
      SQLite 트랜잭션과 WAL을 인식하는 rsync에 전송 중 압축까지 붙는 셈임
    • 핵심은 인덱스를 건너뛰는 것이고, 이건 압축 전에 해야 함
      이런 작업을 할 때는 덤프를 바로 gzip으로 스트리밍함. 보통 중간 파일 없이 목적지로 직접 스트리밍하는 방법도 찾을 수 있음
      게다가 이렇게 하면 목적지에 압축된 상태로 저장됨. 가난한 사람의 복제가 아니라 백업이 목적이라면 특히 그렇다
    • 압축은 느린 네트워크에서만 유리하다고 봄
    • 이건 꼭 해야 함. 압축된 파일에 rsync를 쓰면 rsync를 쓰는 핵심인 롤링 체크섬 기반 차이 전송을 완전히 우회하게 됨
    • --remove-source-files를 써서 다시 SSH로 들어가 rm하지 않아도 됐을 것임

  • 텍스트 파일로 저장하는 건 비효율적임. 나는 SQLite 데이터베이스를 VACUUM INTO로 저장함
    sqlite3 -readonly /path/db.sqlite "VACUUM INTO '/path/backup.sqlite';"
    https://sqlite.org/lang_vacuum.html에 따르면, INTO 절이 붙은 VACUUM 명령은 실행 중인 데이터베이스의 백업 복사본을 만드는 백업 API의 대안임. 장점은 결과 백업 데이터베이스 크기가 최소화되어 파일시스템 I/O가 줄 수 있다는 것임

    • 멋지긴 하지만 원문에서 말한 인덱스 문제는 해결하지 못함. 느린 링크로 인덱스 데이터를 보내지 않는 것이 핵심 아이디어였는데, VACUUM INTO 방식은 인덱스를 유지함
      텍스트 파일은 그대로는 비효율적일 수 있지만 gzip 같은 원시적인 도구로도 잘 압축됨. SQLite 바이너리 형식도 그만큼 잘 압축되는지는 확실치 않지만 가능성은 있음
    • 그 방식은 인덱스를 보존하나? 원문에서 SQLite 파일이 큰 이유가 인덱스라고 했음

  • DuckDB에서는 같은 일을 하되 Parquet으로 내보낼 수 있고, 이러면 텍스트 기반 SQL 문보다 데이터 크기가 한 자릿수 배율로 작아짐. 전송도 더 빠르고 적재도 더 빠름
    https://duckdb.org/docs/stable/sql/statements/export.html

    • 명령줄로는 이렇게 할 수 있음
      duckdb -c "attach 'sqlite-database.db' as db; copy db.table_name to 'table_name.parquet' (format parquet, compression zstd)"
      내 테스트 데이터베이스에서는 gzip으로 압축한 텍스트 SQL 문보다 약 20% 작았음

  • SQLite에는 테이블 집합의 변경을 추적하고, 이전 버전의 SQLite 데이터베이스에 적용할 수 있는 changeset/patchset을 만드는 세션 확장이 있음
    https://www.sqlite.org/sessionintro.html

    • 이걸 지원하는 SQLite 바인딩을 아직 하나도 못 봤음. 그래서 애플리케이션을 C로 쓰거나 언어 바인딩을 패치할 생각이 없다면 꽤 쓸모가 떨어짐
      내 프로젝트 중 하나에서는 모든 SQL 문과 매개변수를 별도 데이터베이스에 기록한 뒤, 그걸 동기화하고 재생하는 식으로 가난한 사람의 세션을 직접 구현했음. 매일 약 0.1%만 바뀌는 30GB 정도의 데이터베이스에는 충분히 잘 동작함
    • 써본 적 있나? 문서는 읽어봤지만 이 확장을 실제로 쓴다는 사람은 들어본 적이 없음
    • SQLite에 세션이나 비슷한 방식으로 변경 데이터 캡처를 하는 기본 방법이 있으면 정말 좋겠음

  • 오래된 버전에서 새 버전으로 정기적으로 동기화한다면 gzip의 --rsyncable 옵션으로 더 최적화할 수 있음
    압축률은 약 1% 줄어들지만, 한 버전에서 다음 버전으로의 차이가 압축 출력 전체로 연쇄되지 않고 국소화됨
    또 다른 방법은 덤프 출력은 압축하지 않고, rsync가 이전 비압축 덤프와 현재 덤프의 차이를 계산하게 한 뒤 네트워크로 보내는 변경 집합만 압축하게 하는 것임. 즉 rsync -z를 쓰면 됨

  • 이건 하나의 파이프라인으로 처리할 수 있을 것 같음
    ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c" | gunzip -c | sqlite3 my_local_database.db

    • SSH 호출에 -oCompression=on이나 -C로 SSH 압축을 켠다면 gzip/gunzip은 중복일 수도 있음
    • 나도 첫 생각이 그거였음. 그리고 거기에 pv가 들어가야 한다는 점도 꽤 명백해 보임
답변달기