DBSTAT 가상 테이블

DBSTAT 가상 테이블은 SQLite 데이터베이스의 내용을 저장하는 데 사용된 디스크 공간의 양에 대한 정보를 반환하는 읽기 전용 eponymous 가상 테이블이에요. 디스크 사용량 분석에 유용해요.

출처: The DBSTAT virtual table

본문

1. 개요 (Overview)

DBSTAT 가상 테이블은 SQLite 데이터베이스의 내용을 저장하는 데 사용된 디스크 공간의 양에 대한 정보를 반환하는 읽기 전용 eponymous 가상 테이블이에요. DBSTAT 가상 테이블의 사용 사례로는 sqlite3_analyzer.exe 유틸리티 프로그램과, SQLite 용 Fossil 구현 버전 관리 시스템의 테이블 크기 원형 차트가 있어요.

SQLITE_ENABLE_DBSTAT_VTAB 컴파일 타임 옵션으로 SQLite 를 빌드하면 모든 데이터베이스 연결에서 DBSTAT 가상 테이블을 사용할 수 있어요.

DBSTAT 가상 테이블은 eponymous 가상 테이블이므로, 사용하기 전에 dbstat 가상 테이블의 인스턴스를 만들기 위해 CREATE VIRTUAL TABLE 을 실행할 필요가 없어요. "dbstat" 모듈 이름을 테이블 이름인 것처럼 사용해 dbstat 가상 테이블을 직접 쿼리할 수 있어요. 예를 들어:

SELECT * FROM dbstat;

dbstat 모듈을 사용하는 명명된 가상 테이블을 원한다면, dbstat 가상 테이블의 인스턴스를 만드는 권장 방법은 다음과 같아요:

CREATE VIRTUAL TABLE temp.stat USING dbstat(main);

가상 테이블 이름("stat") 앞의 "temp." 한정어에 주목하세요. 이 한정어는 가상 테이블을 임시 테이블로 만들어, 현재 데이터베이스 연결이 유지되는 동안에만 존재하도록 해요. 이것이 권장되는 방법이에요.

dbstat 의 "main" 인자는 정보를 제공할 기본 스키마예요. 기본값은 "main" 이라서 위 예에서 "main" 을 사용한 것은 중복이에요. 특정 쿼리에 대해서는, 쿼리의 FROM 절에서 가상 테이블 이름의 함수 인자로 대체 스키마를 지정해 스키마를 바꿀 수 있어요.

DBSTAT 가상 테이블의 스키마는 다음과 같아요:

CREATE TABLE dbstat(
  name       TEXT,        -- Name of table or index
  path       TEXT,        -- Path to page from root
  pageno     INTEGER,     -- Page number, or page count
  pagetype   TEXT,        -- 'internal', 'leaf', 'overflow', or NULL
  ncell      INTEGER,     -- Cells on page (0 for overflow pages)
  payload    INTEGER,     -- Bytes of payload on this page or btree
  unused     INTEGER,     -- Bytes of unused space on this page or btree
  mx_payload INTEGER,     -- Largest payload size of all cells on this row
  pgoffset   INTEGER,     -- Byte offset of the page in the database file
  pgsize     INTEGER,     -- Size of the page, in bytes
  schema     TEXT HIDDEN, -- Database schema being analyzed
  aggregate  BOOL HIDDEN  -- True to enable aggregate mode
);

DBSTAT 테이블은 데이터베이스 파일 안의 btree 내용만 보고해요. 프리리스트(freelist) 페이지, 포인터-맵(pointer-map) 페이지, 잠금 페이지는 분석에서 제외돼요.

기본적으로 DBSTAT 테이블에는 데이터베이스 파일의 각 btree 페이지마다 하나의 행이 있어요. 각 행은 데이터베이스의 그 페이지 하나의 공간 활용에 대한 정보를 제공해요. 하지만 숨은 컬럼 "aggregate" 가 TRUE 이면 결과가 집계되어, 데이터베이스의 각 btree 마다 DBSTAT 테이블에 하나의 행이 생기고, 전체 btree 에 걸친 공간 활용 정보를 제공해요.

2. dbstat 가상 테이블의 "path" 컬럼

"path" 컬럼은 btree 구조의 루트 노드에서 각 페이지까지의 경로를 설명해요. 루트 노드 자체의 "path" 는 '/' 이에요. "aggregate" 가 TRUE 일 때 "path" 는 NULL 이에요. btree 페이지의 루트에서 가장 왼쪽 자식 페이지의 "path" 는 '/000/' 이에요. (btree 는 내용을 왼쪽에서 오른쪽으로 정렬해 저장하므로, 왼쪽 페이지가 오른쪽 페이지보다 더 작은 키를 가져요.) 루트 페이지의 왼쪽에서 두 번째 자식은 '/001' 이고, 이렇게 각 형제 페이지는 3자리 16진수 값으로 식별돼요. 451번째 왼쪽 형제의 자식들은 '/1c2/000/', '/1c2/001/' 같은 경로를 가져요. 오버플로우 페이지는 연결된 셀로의 경로에 '+' 문자와 6자리 16진수 값을 붙여 지정해요. 예를 들어, 루트 페이지의 450번째 자식의 가장 왼쪽 셀에서 연결된 체인의 세 오버플로우 페이지는 다음과 같은 경로로 식별돼요:

'/1c2/000+000000'         // First page in overflow chain
'/1c2/000+000001'         // Second page in overflow chain
'/1c2/000+000002'         // Third page in overflow chain

경로를 BINARY 정렬 순서로 정렬하면, 셀과 연결된 오버플로우 페이지가 그 자식 페이지보다 정렬 순서에서 먼저 나타나요:

'/1c2/000/'               // Left-most child of 451st child of root

3. 집계 데이터 (Aggregated Data)

SQLite 버전 3.31.0 (2020-01-22) 부터 DBSTAT 테이블에 "aggregate" 라는 새 숨은 컬럼이 추가됐어요. 이것이 TRUE 로 제한되면 DBSTAT 가 페이지당 하나가 아니라 데이터베이스의 btree 당 하나의 행을 생성하게 해요. 집계 모드에서 실행할 때 "path", "pagetype", "pgoffset" 컬럼은 항상 NULL 이고, "pageno" 컬럼은 행에 해당하는 페이지 번호가 아니라 전체 btree 의 페이지 수를 담아요.

다음 표는 DBSTAT 의 (숨지 않은) 컬럼들의 일반 모드와 집계 모드에서의 의미를 보여줘요:

Column	Normal meaning	Aggregate-mode meaning
name	현재 행의 btree 가 구현하는 테이블 또는 인덱스의 이름	(동일)
path	위에서 설명한 대로	항상 NULL
pageno	현재 행의 데이터베이스 페이지 번호	현재 행의 btree 의 총 페이지 수
pagetype	'leaf' 또는 'interior'	항상 NULL
ncell	현재 페이지 또는 btree 의 셀 수	(동일)
payload	현재 페이지 또는 btree 의 유용한 페이로드 바이트	(동일)
unused	현재 페이지 또는 btree 의 미사용 바이트	(동일)
mx_payload	현재 페이지 또는 btree 어디에서든 발견된 가장 큰 페이로드	(동일)
pgoffset	페이지 시작 부분의 바이트 오프셋	항상 NULL
pgsize	현재 페이지 또는 btree 가 사용하는 총 저장 공간	(동일)

4. dbstat 가상 테이블 사용 예제

스키마 "aux1" 에서 테이블 "xyz" 를 저장하는 데 사용된 총 페이지 수를 찾으려면 다음 두 쿼리 중 하나를 사용해요 (첫 번째는 전통적인 방법이고, 두 번째는 집계 기능의 사용을 보여줘요):

SELECT count(*) FROM dbstat('aux1') WHERE name='xyz';
SELECT pageno FROM dbstat('aux1',1) WHERE name='xyz';

테이블의 내용이 디스크에 얼마나 효율적으로 저장되는지 보려면, 실제 내용을 담는 데 사용된 공간을 총 디스크 공간으로 나눠 계산해요. 이 숫자가 100% 에 가까울수록 패킹이 효율적인 거예요. (이 예에서는 'xyz' 테이블이 'main' 스키마에 있다고 가정해요. 역시 집계 기능 없이/있이 DBSTAT 의 사용을 보여주는 두 가지 버전이 있어요.)

SELECT sum(pgsize-unused)*100.0/sum(pgsize) FROM dbstat WHERE name='xyz';
SELECT (pgsize-unused)*100.0/pgsize FROM dbstat
 WHERE name='xyz' AND aggregate=TRUE;

테이블의 평균 팬아웃(fan-out)을 찾으려면:

SELECT avg(ncell) FROM dbstat WHERE name='xyz' AND pagetype='internal';

현대 파일시스템은 디스크 접근이 순차적일 때 더 빨리 동작해요. 따라서 데이터베이스 파일의 내용이 순차 페이지에 있으면 SQLite 가 더 빨리 실행돼요. 데이터베이스의 페이지 중 순차적인 페이지의 비율을 알아내려면(그래서 VACUUM 을 실행할 때를 결정하는 데 유용한 측정값을 얻으려면), 다음과 같은 쿼리를 실행해요:

CREATE TEMP TABLE s(rowid INTEGER PRIMARY KEY, pageno INT);
INSERT INTO s(pageno) SELECT pageno FROM dbstat ORDER BY path;
SELECT sum(s1.pageno+1==s2.pageno)*1.0/count(*)
  FROM s AS s1, s AS s2
 WHERE s1.rowid+1=s2.rowid;
DROP TABLE s;

더 알아보기 (Learn more)

sqlite3_analyzer.exe — 데이터베이스 공간 사용 분석 유틸리티
가상 테이블 — eponymous 가상 테이블과 테이블-값 함수
VACUUM — 데이터베이스 재구성