SQLite INSERT 속도 측정: 행마다 commit하면 1,000배 느려진다

SQLite INSERT 속도가 느리다면 원인은 대개 INSERT가 아니라 commit입니다. 같은 행을 넣는데 행마다 commit하면 초당 1,827행, 트랜잭션 하나로 묶으면 초당 188만 행이 들어갔습니다. commit 단위와 PRAGMA 설정을 바꿔 가며 14가지 조합을 쟀고, 맥에서 잰 숫자를 그대로 믿으면 안 되는 이유도 같이 확인했습니다.

먼저 결론

  • 행마다 commit하면 초당 1,827행, 트랜잭션 하나에 executemany로 넣으면 초당 188만 행이었습니다. 약 1,000배 차이입니다.
  • 전부 묶지 못해도 1,000행마다 commit하면 초당 119만 행이 나왔습니다.
  • WAL 모드에 synchronous=NORMAL을 쓰면 행마다 commit해도 초당 14만 7천 행입니다. 대신 전원이 나가면 최근 commit이 사라질 수 있습니다.
  • macOS의 기본 설정은 commit할 때 디스크까지 내려쓰지 않습니다. fullfsync=ON으로 실제로 내려쓰게 하자 행마다 commit은 초당 73행으로 25배 느려졌습니다.

실험 환경과 방법

macOS 27.2.1, Apple M4 Pro의 내장 SSD(APFS)에서 Python 3.11.15의 sqlite3 모듈로 실험했습니다. SQLite 버전은 3.54.1입니다. 아무 설정도 하지 않은 연결의 기본값은 다음과 같았습니다. synchronous의 2는 FULL입니다.

>>> conn = sqlite3.connect("probe.db")
>>> [conn.execute(f"PRAGMA {name}").fetchone()[0]
...  for name in ("journal_mode", "synchronous", "fullfsync")]
['delete', 2, 0]

테이블은 정수 기본 키와 컬럼 4개로 만들었고 한 행에 100바이트 문자열이 들어갑니다. 100만 행을 넣으면 DB 파일이 135MB가 됩니다. 행마다 commit하는 방식은 너무 오래 걸려서 최대 4초 또는 2만 행까지만 돌리고 실제로 넣은 행 수로 초당 행 수를 계산했습니다. 묶어서 넣는 방식은 100만 행을 전부 넣었습니다. 조합마다 새 DB 파일로 3번 돌려 중앙값을 썼습니다.

import sqlite3, time

SCHEMA = ("CREATE TABLE events (id INTEGER PRIMARY KEY, user_id INTEGER, "
          "kind TEXT, payload TEXT, created_at TEXT)")
INSERT = "INSERT INTO events (id, user_id, kind, payload, created_at) VALUES (?, ?, ?, ?, ?)"


def row(i):
    return (i, i % 1000, "click", "x" * 100, "2026-11-15T12:00:00")


def per_row_commit(conn, max_rows=20_000, time_box=4.0):
    started = time.perf_counter()
    done = 0
    for i in range(max_rows):
        conn.execute(INSERT, row(i))
        conn.commit()                      # 행마다 commit
        done += 1
        if time.perf_counter() - started > time_box:
            break
    return done, time.perf_counter() - started


def one_transaction_executemany(conn, rows=1_000_000):
    started = time.perf_counter()
    conn.executemany(INSERT, (row(i) for i in range(rows)))
    conn.commit()                          # commit 한 번
    return rows, time.perf_counter() - started

SQLite INSERT 속도: commit 단위별 결과

PRAGMA는 건드리지 않은 기본 설정에서 commit 단위만 바꾼 결과입니다.

방식 초당 행 수 100만 행에 걸리는 시간
행마다 commit 1,827 약 9분 7초(환산)
1,000행마다 commit 1,189,886 0.84초
트랜잭션 하나, execute 반복 1,456,427 0.69초
트랜잭션 하나, executemany 1,880,431 0.53초

행마다 commit한 경우 commit 한 번에 0.547ms가 걸렸습니다. INSERT 문 자체는 100만 번을 실행해도 0.7초가 안 걸리는데 commit을 100만 번 하면 9분이 넘습니다. SQLite FAQ도 같은 설명을 합니다. SQLite는 INSERT를 초당 수만 번 할 수 있지만 트랜잭션은 초당 수십 번 수준이고, commit마다 데이터가 디스크에 안전하게 기록되기를 기다리기 때문이라는 것입니다.

execute를 100만 번 부르는 것과 executemany 한 번의 차이는 0.69초와 0.53초였습니다. 차이가 있긴 하지만 commit 단위가 만드는 차이에 비하면 작습니다.

행마다 commit해야 한다면: PRAGMA별 결과

요청이 올 때마다 한 행씩 기록하는 서비스처럼 묶기 어려운 경우를 생각하고 행마다 commit하는 방식만 설정을 바꿔 쟀습니다.

설정 초당 행 수 commit 1회당
기본값(journal_mode=DELETE, synchronous=FULL) 1,827 0.547ms
synchronous=OFF 8,591 0.116ms
WAL + synchronous=FULL 10,620 0.094ms
WAL + synchronous=NORMAL 147,514 0.007ms
기본값 + fullfsync=ON 73 13.628ms
WAL + synchronous=FULL + fullfsync=ON 237 4.218ms

WAL 모드로 바꾸는 것만으로 5.8배, 거기에 synchronous=NORMAL까지 주면 80배가 빨라졌습니다. 의외였던 것은 synchronous=OFF입니다. 동기화를 아예 끈 설정인데 WAL + FULL보다 느렸습니다. 기본 저널 모드는 commit마다 롤백 저널 파일을 만들고 지우는데 그 비용이 남아서 그런 것으로 보입니다. 이 부분은 따로 확인하지 않은 추정입니다.

맥에서 잰 숫자를 그대로 믿으면 안 되는 이유

표의 아래 두 줄이 이 실험에서 가장 중요한 결과입니다. macOS의 fsync()는 데이터를 드라이브에 넘기기만 하고, 드라이브가 자기 버퍼의 내용을 영구 저장소에 다 썼는지는 보장하지 않습니다. man 2 fsync는 더 강한 보장이 필요한 데이터베이스 같은 프로그램은 F_FULLFSYNC를 쓰라고 안내합니다. SQLite에서 이것을 켜는 설정이 PRAGMA fullfsync이고 기본값은 꺼짐입니다.

켜고 재면 commit 한 번이 0.547ms에서 13.628ms로 늘어납니다. 초당 1,827행이던 것이 73행이 됩니다. 기본값에서 본 0.547ms는 디스크에 실제로 기록하는 비용이 아니었던 셈입니다.

방식 fullfsync=OFF(기본) fullfsync=ON
행마다 commit 초당 1,827행 초당 73행
1,000행마다 commit 초당 1,189,886행 초당 60,496행
트랜잭션 하나, executemany 초당 1,880,431행 초당 1,788,415행

commit 횟수가 줄수록 fullfsync의 영향도 줄어듭니다. 트랜잭션 하나로 묶으면 commit이 한 번뿐이라 0.53초와 0.56초로 거의 같았습니다. 묶어서 넣는 방식은 디스크가 느린 환경에서 더 큰 이득을 본다는 뜻입니다.

이 실험에서 리눅스는 재지 않았습니다. 다만 맥의 기본값으로 잰 행마다 commit 속도는 디스크 기록을 기다리지 않은 낙관적인 숫자라는 점은 확인했습니다. 개발용 맥에서 충분히 빠르다고 판단한 코드가 서버에서 훨씬 느릴 수 있으니, 배포할 환경에서 직접 재거나 맥에서 fullfsync=ON으로 한 번 더 재 보는 것이 좋습니다.

WAL + NORMAL이 빠른 대신 포기하는 것

WAL 모드에서 synchronous=NORMAL이면 commit할 때마다 동기화하지 않고 체크포인트 때 합니다. SQLite 문서에 따르면 이 설정에서 DB가 깨지지는 않지만, 전원이 나가거나 시스템이 죽으면 최근에 commit한 트랜잭션이 롤백될 수 있습니다. 프로그램만 죽는 경우에는 commit한 내용이 유지됩니다. 로그나 통계처럼 마지막 몇 건을 잃어도 되는 데이터에는 맞고, 결제 기록처럼 commit이 곧 약속인 데이터에는 맞지 않습니다.

그래서 어떻게 쓸까

파이썬의 sqlite3 모듈은 기본 설정에서 INSERT 앞에 트랜잭션을 자동으로 열고 commit()을 부를 때까지 닫지 않습니다. 그러니 행마다 commit이 일어나는 것은 루프 안에서 commit()을 부르거나, 한 행을 저장하고 commit하는 함수를 반복해서 부를 때입니다. 묶을 수 있으면 아래처럼 묶습니다.

# 한 번에 넣을 수 있을 때
with conn:                                 # 정상 종료하면 commit, 예외가 나면 rollback
    conn.executemany(INSERT, rows)

# 계속 들어오는 데이터를 1,000행씩 끊어 넣을 때
batch = []
for item in stream:
    batch.append(item)
    if len(batch) >= 1000:
        with conn:
            conn.executemany(INSERT, batch)
        batch.clear()
if batch:
    with conn:
        conn.executemany(INSERT, batch)

연결 객체를 with로 쓰면 블록이 끝날 때 commit하고 예외가 나면 rollback합니다. 자세한 동작은 sqlite3 문서에 있습니다. 정리하면 이렇습니다.

  • 한 번에 넣을 수 있으면 트랜잭션 하나로 묶습니다.
  • 계속 들어오는 데이터는 일정 개수마다 commit합니다. 1,000행 단위만 돼도 전부 묶은 것의 63% 속도가 나왔습니다.
  • 행마다 commit이 꼭 필요하면 WAL 모드를 먼저 켜고, 최근 commit을 잃어도 되는 데이터인지 따져 본 뒤 synchronous=NORMAL을 정합니다.

이 실험의 한계

맥 한 대에서 연결 하나로 잰 값입니다. 보조 인덱스가 없는 테이블이고 행 크기도 작습니다. 인덱스가 여러 개 있거나 여러 프로세스가 동시에 쓰는 경우는 다루지 않았습니다. 초당 행 수는 CPU와 디스크에 따라 달라지므로 절대값보다는 방식 사이의 비율을 참고하는 것이 맞습니다. 묶어서 넣는 방식들은 100만 행에 0.5~0.8초로 서로 비슷해서 환경에 따라 순서가 바뀔 수 있습니다.

함께 읽으면 좋은 글

Similar Posts

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다