SQLite 튜닝하기 [1] - memory mapped I/O(mmap) 사용하기

sqlite는 기본적으로 튜닝 없이 사용하시는 경우가 많지만, 특정 상황에서 몇 가지 튜닝 옵션을 제공합니다. 오늘은 그 중 하나인 memory-mapped I/O에 대해서 알아보도록 하겠습니다.

Table Of Contents

• Memory-Mapping I/O(mmap)이란?
• SQLite에서 Memory-Mapped I/O 사용시 장점
• SQLite에서 Memory-Mapped I/O 사용시 단점
• PRAGMA 구문이란?
• SQLite에서 Memory-Mapped I/O 사용하기
  • 1. PRAGMA mmap_size 문
  • 2. 파이썬에서 PRAGMA mmap_size로 SQLite mmap 설정하기

Memory-Mapping I/O(mmap)이란?

메모리 매핑 I/O (Memory-Mapped I/O)는 디스크의 파일을 메모리에 한 부분인 버퍼에 대응시켜 읽거나 쓰는 기법입니다. 이를 통해 파일 입출력을 위한 read나 write를 사용할 필요가 없습니다. 메모리 매핑 I/O를 사용하면 다음과 같은 이점이 있습니다:

• 효율성: 입출력 장치의 메모리나 레지스터를 메모리로 취급하여 하나의 메모리 공간에서 입출력을 처리합니다.
• 간편성: 파일을 메모리에 대응시키기만 하면 되므로 별도의 입출력 함수를 사용할 필요가 없습니다.
• 데이터 일관성: 메모리 매핑된 영역을 수정하면 파일의 내용도 수정됩니다.

메모리 매핑 I/O를 사용하려면 커널에 파일을 메모리에 대응시키겠다고 정보를 알려줘야 합니다. 이를 위해 mmap 함수를 사용해서 구현할 수 있습니다.

SQLite에서 Memory-Mapped I/O 사용시 장점

 

SQLite가 데이터베이스 디스크 파일에 액세스하고 업데이트하는 기본 메커니즘은 sqlite3_io_methods VFS 객체의 xRead() 및 xWrite() 메서드입니다. 이러한 메서드는 일반적으로 운영 체제가 커널 버퍼 캐시와 사용자 공간 간에 디스크 콘텐츠를 복사하도록 하는 "read()" 및 "write()" 시스템 호출로 구현됩니다.

버전 3.7.17(2013-05-20)부터 SQLite는 메모리 매핑된 I/O(memory-mapped I/O)와 sqlite3_io_methods의 새로운 xFetch() 및 xUnfetch() 메서드를 사용하여 디스크 콘텐츠에 직접 액세스할 수 있는 옵션이 추가되었습니다.

SQLite에서 메모리 매핑 I/O 사용시 장점은 다음과 같습니다:

1. 성능개선: 커널 공간과 사용자 공간 간에 콘텐츠를 복사할 필요가 없으므로 많은 작업, 특히 I/O 집약적인 작업의 속도가 빨라질 수 있습니다.
2. 더 적은 메모리(RAM) 사용: SQLite 라이브러리는 운영 체제 페이지 캐시와 페이지를 공유하며 작업 페이지의 복사본을 사용하지 않으므로 더 적은 메모리를 사용하게 될 수 있습니다.

SQLite에서 Memory-Mapped I/O 사용시 단점

메모리 매핑된 파일의 I/O 오류는 SQLite에서 처리할 수 없습니다. I/O 오류를 애플리케이션에서 처리하지 못하면 프로그램이 크래쉬되는 signal을 발생시킵니다.

특히 두 개의 프로세스가 동일한 데이터베이스 파일에 액세스하는 상황에서 한 프로세스는 메모리 매핑된 I/O를 사용하고 다른 프로세스는 사용하지 않는 경우, 다른 데이터를 보거나 하는 문제가 있을 수 있습니다. 메모리 매핑된 I/O 확장이 제대로 작동하려면 운영 체제에 통합 버퍼 캐시가 있어야 합니다. 모든 운영 체제에 통합 버퍼 캐시가 있는 것은 아닙니다. 통합 버퍼 캐시가 있다고 주장하는 일부 운영 체제에서는 구현에 버그가 있어 데이터베이스가 손상될 수 있습니다.

참고로, 리눅스의 경우 커널 2.4 부터 통합 버퍼 캐시를 지원합니다.

리눅스의 커널 2.4 이상 통합버퍼 캐시 지원에 대한 설명

통합 버퍼 캐시를 사용하지 않으면 메모리 매핑 I/O를 사용하는 경우에는 페이지 캐시와 커널 영역의 버퍼 캐시를 두 번 거쳐야 합니다. 그러나 통합 버퍼 캐시를 사용하면 버퍼 캐시가 곧 페이지 캐시이므로 페이지 캐시에 한 번 접근하는 것만으로 I/O 처리가 가능합니다.
간단히 말해, 통합 버퍼 캐시를 사용하면 메모리 매핑 I/O를 더 효율적으로 처리할 수 있으며, 페이지 캐시와 버퍼 캐시를 중복해서 거치지 않아도 됩니다.

Unified Buffer Cache와 non Unified Buffer Cache

메모리 매핑 I/O로 성능이 항상 향상되는 것은 아닙니다. 실제로 메모리 매핑 I/O를 사용하면 성능이 저하되는 테스트 사례를 구성할 수 있습니다.

Windows에서는 메모리 매핑된 파일을 truncate할 수 없습니다. 따라서 Windows에서는 메모리 매핑된 데이터베이스 파일의 크기를 줄이려고 VACUUM 또는 auto_vacuum과 같은 작업을 수행하면 크기 축소 시도가 자동으로 실패하여 데이터베이스 파일 끝에 사용되지 않은 공간이 남게 됩니다. 이 문제로 인해 데이터가 손실되는 것은 아니며, 사용하지 않은 공간은 다음에 데이터베이스가 커질 때 다시 재사용됩니다. 그러나 3.7.0 이전 버전의 SQLite가 이러한 데이터베이스에서 PRAGMA integrity_check를 실행하는 경우, 마지막에 사용되지 않은 공간으로 인해 데이터베이스 손상으로 판단하는 오류가 있습니다. 또는 3.7.0 이전 버전의 SQLite가 마지막에 사용되지 않은 공간이 남아 있는 상태에서 데이터베이스에 쓰면 해당 사용되지 않은 공간에 액세스할 수 없게 되어 다음 VACUUM 이후까지 재사용할 수 없게 될 수 있습니다.

sqlite의 버전은 아래와 같이 설치된 sqlite3 명령에 --version을 붙이면 확인할 수 있습니다.

sqlite3 --version
3.37.2 2022-01-06 13:25:41 872ba256cbf61d9290b571c0e6d82a20c224ca3ad82971edc46b29818d5dalt1

 

잠재적인 단점 때문에 메모리 매핑 I/O는 기본적으로 비활성화되어 있습니다. 메모리 매핑 I/O를 활성화하려면 mmap_size pragma를 사용하고 애플리케이션의 여유 주소 공간에 따라 보통 256MB 이상의 큰 숫자로 설정하세요.

나머지는 자동으로 설정됩니다. 메모리 매핑 I/O를 지원하지 않는 시스템에서는 PRAGMA mmap_size 문이 자동으로 작동하지 않습니다.

 

PRAGMA 구문이란?

 

PRAGMA 문은 SQLite 전용 SQL 확장문으로, SQLite 라이브러리의 작업을 수정하거나 내부(테이블이 아닌) 데이터를 SQLite 라이브러리에 쿼리하는 데 사용됩니다. 예를들어, MMAP_SIZE를 지정하여 MMAP을 사용할 때 PRAGMA 문을 사용해서 SQLite 라이브러리의 동작을 튜닝할 수 있습니다.

PRAGMA 문은 다른 SQLite 명령(예: SELECT, INSERT)과 동일한 인터페이스를 사용하여 실행되지만 다음과 같은 중요한 측면에서 다릅니다.

 

• PRAGMA 명령은 SQLite에만 해당되며 다른 SQL 데이터베이스 엔진과 호환되지 않습니다.
• 향후 SQLite 릴리스에서 특정 pragma 문이 제거되고 다른 문이 추가될 수 있습니다. 이전 버전과의 호환성은 보장되지 않습니다.
• 알 수 없는 프라그마가 발행되어도 오류 메시지는 생성되지 않습니다. 알 수 없는 프라그마는 단순히 무시됩니다. 즉, 프라그마 문에 오타가 있는 경우 라이브러리는 사용자에게 그 사실을 알리지 않습니다.
• 일부 PRAGMA는 실행 단계가 아닌 SQL 컴파일 단계에서 적용됩니다. 즉, C언어의 sqlite3_prepare(), sqlite3_step(), sqlite3_finalize() API(또는 이와 유사한 래퍼 인터페이스)를 사용하는 경우 일반 SQL 문처럼 sqlite3_step() 호출이 아닌 sqlite3_prepare() 호출 중에 프래그마가 실행될 수 있습니다. 또는 일반 SQL 문처럼 sqlite3_step() 중에 프래그마가 실행될 수도 있습니다. 프래그마가 sqlite3_prepare() 또는 sqlite3_step() 중에 실행되는지 여부는 프래그마 및 특정 SQLite 릴리스에 따라 다릅니다.
• SQL 문의 EXPLAIN 및 EXPLAIN QUERY PLAN 접두사는 sqlite3_step() 동안의 동작에만 영향을 줍니다. 즉, sqlite3_prepare() 중에 적용되는 PRAGMA 문은 앞에 "EXPLAIN"이 있는지 여부에 관계없이 동일한 방식으로 작동합니다.

PRAGMA command syntax

 

SQLite에서 Memory-Mapped I/O 사용하기

1. PRAGMA mmap_size 문

SQLite에서 Memory-Mapped I/O, 즉 mmap을 사용하기 위해서는 아래 PRAGMA 구문을 이용하여 N에 256MB보다 큰 값을 설정하면 됩니다.

PRAGMA schema.mmap_size;
PRAGMA schema.mmap_size=N

 

위의 구문은 단일 데이터베이스에서 메모리 매핑된 I/O를 위해 할당된 최대 바이트 수를 쿼리하거나 값을 변경합니다.

인수가 없는 첫 번째 형식은 현재 mmap_size 값을 쿼리합니다. 숫자 인수를 포함한 두 번째 형식은 schema로 지정된 데이터베이스 또는 데이터베이스 이름이 생략된 경우 모든 데이터베이스에 대한 제한을 설정합니다.

두 번째 형식에서 데이터베이스 이름이 생략된 경우 설정된 제한이 후속 ATTACH 문에 의해 데이터베이스 연결에 추가되는 모든 데이터베이스에 대한 기본 제한이 됩니다.

 

인자 N은 메모리 매핑된 I/O를 사용하여 액세스할 데이터베이스 파일의 최대 바이트 수입니다. N이 0이면 메모리 매핑된 I/O가 비활성화됩니다. N이 음수이면 제한은 가장 최근의 sqlite3_config(SQLITE_CONFIG_MMAP_SIZE)에 의해 결정된 기본값으로 되돌아가거나, 시작 시간 제한이 설정되지 않은 경우 SQLITE_DEFAULT_MMAP_SIZE에 의해 결정되는 컴파일 시간 기본값으로 되돌아갑니다.

 

PRAGMA mmap_size 문은 메모리 맵핑된 I/O에 사용되는 주소 공간의 양을 컴파일 타임에 SQLITE_MAX_MMAP_SIZE 옵션으로 설정된 hard limit이나 시작 시 sqlite3_config(SQLITE_CONFIG_MMAP_SIZE)의 두 번째 인수로 설정한 hard limit보다 증가시키지 않습니다.

 

2. 파이썬에서 PRAGMA mmap_size로 SQLite mmap 설정하기

SQLite 공식 홈페이지에 기술되어있고, 앞서 설명드린 것 처럼 256MB이상의 값으로 설정해야 mmap이 설정됩니다.

그러므로, 다음과 같이 코드를 작성할 수 있습니다.

# 현재 mmap_size 확인하기
cursor = conn.cursor()
cursor.execute("PRAGMA mmap_size")
result = cursor.fetchone()
print(f"Current mmap_size: {result[0]} bytes")

# mmap_size 256MB 이상으로 설정하기
conn.execute("PRAGMA mmap_size = 268435456")  # 256MB로 설정 (원하는 크기로 변경 가능)

 

만약 512MB로 설정한다면 N의 값을 536870912로 수정하면 됩니다.

다음과 같이 파이썬 예제 프로그램을 작성해서 테스트 해보았습니다. 파일 이름은 test.py로 아래 코드를 작성후 실행 결과를 확인해 봅니다.

import sqlite3

conn = sqlite3.connect('mydatabase.db')

conn.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER NOT NULL
    );
''')

conn.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Alice', 30))
conn.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Bob', 25))
conn.commit()

cursor = conn.execute('SELECT * FROM users')
for row in cursor:
    print(f'ID: {row[0]}, Name: {row[1]}, Age: {row[2]}')


cursor = conn.cursor()
cursor.execute("PRAGMA mmap_size")
result = cursor.fetchone()
print(f"Current mmap_size: {result[0]} bytes")

conn.execute("PRAGMA mmap_size = 268435456")  # 256MB로 설정 (원하는 크기로 변경 가능)

cursor.execute("PRAGMA mmap_size")
result = cursor.fetchone()
print(f"Current mmap_size: {result[0]} bytes")

conn.close()

 

최초에는 mmap_size는 0으로 설정되어있으며 설정 후에는 256MB로 설정되는 것을 확인하실 수 있습니다.

~/sqlite$ python3 test.py
ID: 1, Name: Alice, Age: 30
ID: 2, Name: Bob, Age: 25
ID: 3, Name: Alice, Age: 30
ID: 4, Name: Bob, Age: 25
ID: 5, Name: Alice, Age: 30
ID: 6, Name: Bob, Age: 25
Current mmap_size: 0 bytes
Current mmap_size: 268435456 bytes

 

성능의 경우 제가 테스트 해본결과 약 9%정도 개선되는 것으로 나타났습니다. 

 

오늘은 SQLite에서 mmap을 이용해서 성능을 튜닝하는 방법을 알아보았습니다. 파이썬으로 간단하게 해당 설정을 바꾸고 사용하는 법을 알아보았으며, 다른 언어나 인터페이스에서도 동일한 형태로 사용하시면 간단하게 바꿔서 사용해 보실 수 있으니 한번 적용해서 성능 테스트를 해보는 것도 좋을 것 같습니다.