[Database] Data Page Structure, SQL 서버가 갖는 페이지의 구조

본 글은 공부하면서 수정중이기 때문에 오타나 틀린 부분이 존재할 수 있습니다!

 

회사에서 Table을 생성하던 중 다음과 같은 에러가 발생하였다.

사양은 다음과 같았다.

MariaDB 10.10.3 , InnoDB Engine

 

ERROR 1118: Row size too large(> 8126). The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

 

이유는 간단하다. 한 row에 넣을 수 있는 최대 사이즈를 초과했기 때문이다.

검색을 하다보면 해결하는 방법이 있는데 문득 왜 에러가 발생하는지 SQL의 페이지 구조가 궁금해졌다.

그래서 SQL에서 데이터를 어떻게 저장하는지  SQL 데이터 페이지 구조에 대해 작성해보려고 한다.

매우 깊게 파고들지는 못했지만 꾸준히 기억나면 수정하려고 한다.

 

MySQL 스토리지엔진 중 InnoDB의 경우 .ibd 파일, MyISAM .MYD, .MYI 파일을 이용하여 디스크에 저장하지만 

여기서는 MDF 파일로 저장되는 Microsoft SQL Server를 기준으로 작성한다. (InnoDB Engine으로 에러 났지만...)

 

먼저 Page와 Extent를 짚고 넘어간다.

 

 

SQL Server의 기본 데이터 스토리지 단위는 8Page의 컬렉션으로 이루어 저장된다.

1Page = 8K의 size를 갖는다.

Page - SQL Server에서 데이터를 저장하고 검색하는 데 사용되는 기본 저장 단위(예: 4KB, 8KB, 16KB 등)

Extent - 연속적인 데이터 Page를 그룹화한 저장 단위(공간 할당의 효율성과 관리를 개선하기 위해 사용)

다음 그림을 보면 연속적으로 놓여진 8개의 8K Page를 1개의 Extent로 관리하는 것을 알 수 있다.

마치 조그마한 상품들(Page)을 하나의 장바구니(extent)에 담아 사용하는 느낌이다.

 

Extent를 사용하는 이유는 다음과 같다.

1. 연속된 데이터 페이지: Extent는 연속적인 데이터 페이지의 집합으로 구성되며, 이 구조는 디스크 입출력 작업의 효율성을 높이고, 공간 할당 및 회수를 더 빠르게 수행할 수 있게 합니다.(큰 크기의 파일을 다루는 시스템에서 효율적이다.)
2. 공간 할당 및 관리: 데이터베이스 시스템은 Extent를 사용하여 데이터 저장 공간을 할당하고 관리합니다. 예를 들어, 테이블이나 인덱스와 같은 데이터베이스 개체가 공간이 필요할 때, 시스템은 새로운 Extent를 할당할 수 있습니다. 마찬가지로, 데이터베이스 개체에서 더 이상 사용되지 않는 공간이 있으면 해당 Extent를 회수할 수 있습니다.
3. 성능 및 관리 개선: Extent는 디스크 공간 할당을 최적화하고, 시스템 성능을 향상시킵니다. Extent를 사용함으로써, 데이터베이스 시스템은 연속된 페이지들을 일괄 처리하여 공간 관리 작업을 효율적으로 수행할 수 있습니다.

SQL Server에는 다음과 같이 두 가지의 유형의 익스텐트가 존재한다.

• 균등 익스텐트(Uniform Extent): 하나의 오브젝트(테이블이나 인덱스 등)에만 할당되는 익스텐트로, 해당 오브젝트가 64KB 이상의 데이터를 저장할 때 사용됩니다.
• 혼합 익스텐트(Mixed Extent): 여러 오브젝트들 사이에 공유되는 익스텐트로, 각 오브젝트가 처음 생성되거나 작은 양의 데이터를 저장할 때 사용됩니다.

 

HEAP/INDEX는 어떤 공간인가?

HEAP과 INDEX가 합쳐져 128K의 공간을 차지한다고 되어있지만 이는 예시일 뿐이다. CS 기본지식을 공부했으면 알듯이 이는 DB 구성, 저장된 데이터의 양과 종류, 인덱스 종류와 개수 등에 따라 동적으로 바뀐다.

다음과 같은 구조에 변화가 생길 수 있는 상황에 기본적으로 달라질 수 있다.

1. 데이터 삽입 및 업데이트

2. 인덱스 생성 및 변경

3. 데이터 삭제(공간 재활용 방식은 DB 시스템의 정책에 따라 다름)

4. 데이터베이스 관리 작업(정렬, 샤딩, 백업, 복원 등 수행)

 

이제 SQL 데이터 페이지 구조를 알아보자.

SQL 데이터 페이지 구조(SQL Data Page Structure)는 관계형 데이터베이스 관리 시스템(RDBMS)에서 데이터를 저장하고 검색하는 데 사용되는 기본 저장 단위입니다. 데이터 페이지는 일반적으로 고정된 크기(예: 4KB, 8KB, 16KB 등)로, 디스크에서 데이터를 읽고 쓰는 효율성을 높이기 위해 사용됩니다.

 

데이터 페이지 구조의 주요 구성 요소는 다음과 같습니다:

1. 페이지 헤더(Page Header): 데이터 페이지의 맨 앞 부분에 위치하며, 페이지에 관한 메타데이터를 포함합니다. 페이지 헤더에는 페이지 ID, 페이지 타입, 사용 가능한 공간, 사용 중인 공간, 데이터 레코드 개수 등과 같은 정보가 포함될 수 있습니다.
   
   1. 각 페이지는 96-byte의 header로 시작하며 시스템 정보를 저장한다.
2. 데이터 레코드(Data Records): 데이터 페이지의 주요 부분으로, 실제 테이블의 행(row) 데이터를 포함합니다. 데이터 레코드는 각 컬럼의 값들과 해당 레코드에 대한 정보(예: 레코드 길이, 삭제 표시 등)를 포함합니다.
   1.  
3. 레코드 오프셋 배열(Record Offset Array): 페이지의 끝 부분에 위치하며, 페이지 내의 데이터 레코드 위치를 가리키는 오프셋들로 구성됩니다. 이 배열은 레코드를 신속하게 검색하고 데이터 페이지의 빈 공간을 효율적으로 사용할 수 있도록 돕습니다.
4. 프리 스페이스(Free Space): 데이터 레코드와 레코드 오프셋 배열 사이에 위치하는 빈 공간입니다. 데이터가 추가되거나 삭제될 때, 이 공간은 동적으로 변경됩니다.

다음 그림에 빗대면 다음과 같다.

페이지 헤더 : Header(시스템 정보 저장)

데이터 레코드 : Row0, Row1, Row2(본문)

레코드 오프셋 배열 : Row Offset Table (주소값을 저장한다.)

Free Space : Body(아직 사용되지 않은 공간 새로운 데이터가 삽입되거나 확장될 때 이 공간 사용)

 

데이터 페이지 구조에 대해 쉽게 이해하기 위해 위의 그림을 사용하였다. 여기까지 이해 했다면 밑 그림도 이해할 수 있다.

 

SQL Server 데이터 구조의 기본 할당 상태이다.

Page Header는 96Byte

Records는 8060Byte(데이터가 추가 삭제됨에 딸다 변동 됨)

Offset Array는 36Bytes(데이터가 추가 삭제됨에 따라 변동 됨)

= 이렇게 총 8192Byte의 저장 공간을 하나의 페이지 구조로 갖는다.

 

만약 1byte 혹은 10000byte가 들어오면 어떻게 될까?

1Byte가 들어와도 하나의 페이지 구조가 할당되고 만약 8060Byte가 초과될 경우 ROW_OVERFLOW_DATA 영역에 저장되고 24Byte의 포인터 정보를 저장한뒤 다음 페이지 주소로 가서 나머지 Byte들을 저장한다.(이 때 Double Linked List 로 페이지끼리 연결된다.)

 

예)

name = 10

phone = 10

email = 100

etc =5000

5120 byte가 있다면 하나의 페이지(8060bytes)에 다 들어감

남는 공간은 버려야함(I/O비용 발생, 트랜잭션등 이슈 관리 때문에)

 

8060byte 초과시 포인터 정보 저장으로 다른 영역에 데이터를 넣음으로써 I/O 추가 발생

한 데이터를 읽으려면 한 페이지 단위를 읽음

한 페이지 안에 데이터 10개가 들어있으면 한 페이지 읽음

10페이지 안에 1개의 데이터가 있으면 10 페이지 읽음

그래서 몇억건의 페이지를 읽으면 I/O 비용 발생

파일 읽기 쓰기 단위랑 똑같음 DB는 8K단위 인것 뿐

 

여기까지 이해했다면, DB 관리를 어떻게 해야 효율적으로 할 수 있는지 조금은 도움이 됫으리라 생각한다.

포인트 :) 도메인을 잘 분리하자 다 때려박지 말자

 

 

--내용 추가 예정--

 

 

부록

Oracle의 Extent는?

http://dbtech.co.kr/bbs/?bo_c=1040&bo_v=155

 

 

출처:

 

https://laigo.kr/41

SQL 파일 구조 - Pages and Extents

https://learn.microsoft.com/ko-kr/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver16 

페이지 및 익스텐트 아키텍처 가이드 - SQL Server

https://sqlsanctum.wordpress.com/2016/08/31/sql-server-storage-pages-and-extents/

SQL Server Storage: Pages and Extents

SW Maestro 13기 멘토 특강 - 왜 내가 만든 쿼리는 느릴까?