그림으로 이해하는 SQL 서버의 구조

디스크 I / O 조작의 이해

 - SQLOS 스케줄러에 관리되는 동작 중 하나인 데이터 파일과 트랜젝션 로그 파일 같은 물리 파일에 대한 디스크 I / O조작

   에 대해 살펴본다.

 - DB 버퍼 캐시는 데이터 파일로부터 읽어 들인 데이터 페이지 (또는 인덱스 페이지)를 담는 캐시 영역이다. 

 - 일부 Direct Path Read 메커니즘이 작동하는 경우를 제외하면, 모든 페이지 읽기는 버퍼 캐시를 통해 이루어진다.
 => 즉, 읽고자 하는 페이지를 먼저 버퍼 캐시에서 찾아보고 없을 때 디스크에서 읽는다.

 - 디스크에서 읽을 때도 먼저 버퍼 캐시에 적재한 후에 읽는다. 
 - 데이터 변경도 버퍼 캐시에 적재된 페이지를 통해 이루어지며, 

 - 디스크 I/O는 물리적으로 액세스 암(Arm)이 움직이면서 헤드를 통해 이루어지는 반면, 
 - 메모리 I/O는 전기적 신호에 불과하기 때문에 디스크 I/O에 비교할 수 없을 정도로 빠르다. 
 - 디스크에서 읽은 데이터 블록을 메모리 상에 보관해 두는 기능이 모든 데이터베이스 시스템에 필수적인 이유다.

 

 

SQL 서버가 관리하는 데이터 베이스의 실체

※ 유저 애플리케이션이 비교적 큰 양의 데이터를 장기적으로 보관해야하는 경우는 어떤 방법이 현실적일까?

 => 파일을 작성해서 보존할 필요가 있는 데이터를 디스크 등의 기억 미디어에 기록하는 방법을 선택할 것이다.

 - SQL 서버도 장기적으로 방대한 데이터를 보관 관리하는 데이터베이스 관리 시스템으로 일반적인 유저 애플리케이션과

   다르지 않다.

 

※ 데이터 베이스의 실체란?

 - 그것은 윈도우가 관리하는 폴더에 작성된 파일이다. 데이터 베이스는 데이터 파일과 트랜잭션 로그 파일이라는 2종류의

   관리 파일로 구성되어 있다.

 - 데이터 베이스를 배치한 폴더에는 데이터 파일 ( 확장자 .mdf 또는 .ndb)과 트랜잭션 로그 파일 ( 확장자 .ldf )이 저장되어

   있다.

데이터 파일 ( 확장자 .mdf, .ndf )

Primary data filesPrmary data file은 데이터베이스의 시작점이고 데이터베이스의 다른 파일을 가리키고 있다. 
모든 데이터베이스는 primary data file을 가지고 있다. 보통 .mdf 확장자를 갖는다

Secondary data filesSecondary data files은 primary data file 이외의 데이터 파일로 구성된다. 
Secondary data file이 아예 없는 데이터베이스도 있을 수 있다. 또는 여러개 secondary data file을 가질 수도 있다. 
보통 .ndf 확장자를 갖는다.

 - 사용자가 참조 / 삽입 / 갱신 / 삭제하는 데이터 본체가 저장되어 있다. SQL 서버는 파일을 8KB의 블록으로 분할해서

   사용하고 있다.

 - 분할한 8KB 블록은 페이지라는 논리적 단위로 관리되며 페이지는 SQL 서버가 물리 디스크에서 읽어들인 데이터를 

   처리할 때 또는 물리 디스크에 데이터를 쓸 때 최소의 논리적 단위이기도 하다.

트랜잭션 로그 파일 ( 확장자 .ldf )

Log filesLog files은 데이터베이스를 복구하는데 사용되는 로그 정보가 포함되어있다. 
데이터베이스에는 적어도 하나 이상의 로그 데이터베이스가 있어야 한다. 보통 .ldf 확장자를 갖는다.

 - SQL 서버가 관리하는 데이터에 대해 실행한 갱신 내용을 기록한다.

 => SQL 서버가 관리하는 데이터가 변경되면 데이터 자체를 변경하기 전에 변경 내용의 이력을 모두 물리 디스크에 

      존재하는 트랜젝션 로그 파일에 적는다. 변경 관련 트랜젝션 로그가 모두 바르게 적히면 데이터 변경 처리를 수행한다.

  => 이 동작은 미리 쓰기 로그(Write Ahead Logging, WAL )라 불리며 SQL 서버가 트랜잭션과 데이터의 관계를 

        유지하는 데 중요한 의미를 갖는다. 이 덕분에 트랜잭션 처리 도중에 전원이 꺼지는 경우에도 데이터의 상태를

        확실하게 파악할 수 있다.

1. 데이터베이스에 대해 갱신 처리를 개시
2. 갱신 내용을 로그 파일에 기록, 요구 실행
3. 로그 파일에 기록이 완료되면 애플리케이션에 처리가 성공했다고 회신
4. 전원 장애 발생? => 이 시점에 데이터 A는 파일에 추가되지 않았음
5. 전원 복구 후 SQL 서버 시작 시에 로그 파일의 해석 실행
6. 데이터 파일에 반영도지 않은 트랜잭션 처리를 재실행 => 로그에는 갱신 내용이 모두 기록되어 있음.
7. 데이터 A가 데이터 파일에 추가 된다. => 애플리케이션이 인식하고 있는 처리 결과와 데이터 상태 일치

 

데이터베이스 파일 액세스 패턴

1. 온라인 트랜잭션 처리 시스템의 경우 ( Online Transaction Processing System )

 - OLTP 시스템에서는 다수의 클라이언트가 액세스하고 각각이 필요로하는 소규모 데이터의 조작을 수행한다.

 - 데이터 파일에 저장된 데이터가 파일 내의 다양한 장소에 저장해 있을 가능성이 높다.

 => 그 결과 데이터 파일에 액세스하는 것은 파일 전체에 랜덤으로 발생하는 경향이 높아진다.

 

client 1 : SELETE col1 FROM T1 WHERE c2 = 'A';

client 2 : SELETE col1 FROM T1 WHERE c2 = 'B';

client 3 : SELETE col1 FROM T1 WHERE c2 = 'C';

client 4 : SELETE col1 FROM T1 WHERE c2 = 'D';

client 5 : SELETE col1 FROM T1 WHERE c2 = 'E';

2. 데이터 웨어하우스의 경우 ( DWH )

 - 주요 용도가 축적한 데이터를 분석하는 일인 데이터 웨어하우스인 경우 소수의 클라이언트가 대규모 기록을 수행한다.

 => 데이터 파일에 순차 액세스 하는 경우가 강하다고 할 수 있다.

SELECT col1 FROM T1 WHERE year BETWEEN 1994 AND 2015

3. 트랜젝션 로그 파일

 - 로그 파일에 갱신 내용을 기록할 때는 반드시 시계열로 진행된다.

 - 복수의 갱신 처리가 데이터베이스에서 실행되고 있는 경우에도 각각의 내용이 변경된 순서대로 디스크상의 트랜잭션

   로그 파일에 기록된다.

 => 디스크에 기록하는 포인트는 항상 한부분이다. 그렇기에 디스크 장치에 여러 개의 스핀들이 있어도 혜택을 받을

      수가 없다.

- 트랜잭션 로그는 실행된 순서대로 로그 파일에 기록된다.
- 2개의 로그 파일에 동시에 기록되는 일은 없다.

  => 또한 전회의 디스크 기록이 종료된 지점부터 다음의 기록을 시작한다. 때문에 각각의 데이터베이스에

       로그 파일 전용의 스핀들을 준비하면 헤드가 기록 시작 포인트까지 이동하는 시간을 매회 절약할 수 있다.

 

1) 로그 파일 전용의 디스크가 아닌 경우

-  같은 디스크상에 배치된 다른 파일의 읽기 등 트랜잭션 로그 기록을 재개할 때마다 디스크의 회전과 디스크 헤드의 탐색

   이 발생
- 1 회당 디스크 헤드의 이동은 짧아보이지만, 빈도가 잦아짐에 따라 퍼포먼스에 악영향을 미친다.

 

 

2) 로그 파일 전용의 디스크인 경우

 

- 트랜잭션 로그 파일 전용 디스크라면 디스크 헤드가 이동하지 않기 때문에 기록 재개를 같은 지점에서 시작 가능하다.

SQL 서버가 수행하는 I / O의 특징적 동작

 - 여기서는 SQL 서버가 I / O를 효율적으로 수행하기 위해 도입한 주요 동작을 소개한다.

 

1. 미리 읽기 ( Read - Ahead )

 - 기본적으로 데이터 파일에서 읽어들이기는 쿼리가 필요로 한 만큼의 데이터를 취득할 때마다 수행한다.

   이에 추가해 SQL 서버는 장래에 필요할 것으로 예측되는 데이터에 관해서는 사전( 실제 읽기 요구가 발생하기 전에 )에

   물리상의 디스크에 존재하는데이터 파일로부터 메모리에 데이터를 읽어들이는 일이 있다.

※ SQL 서버가 데이터를 읽어들이는 메모리 영역을 버퍼 캐시라고 한다.

SELETE col1 FROM table1 WHERE year_col > 2011 AND year_col < 2014 

 => 장래의 사용에 대비해 쿼리의 결과 범위 밖의 데이터도 읽어들인다 ( 2009 ~ 2020 )

  => I / O 오버헤드를 완화

 

2. 체크포인트 ( checkpoint )

 - SQL 서버가 관리하는 데이터가 변경되면 미리 쓰기 로그 조작이 실행된다.

 - 이어서 버퍼 캐시상의 데이터가 갱신되는데 이 시점의 갱신 조작을 논리 기록이라고 부른다.

 

 - 버퍼 캐시상의 갱신된 데이터는 임의의 타이밍에 물리 디스크에 기록된다.

※ 더티 페이지 : 논리 기록이 이미 실행되어 있고 물리 기록이 실행되지 않는 버퍼 캐시상의 데이터

- 더티페이지에 대한 물리 기록 수단의 하나가 체크 포인트이다.

1. SQL 서버 내에는 체크포인트 프로세스라 불리는 내부 컴포넌트가 존재하고 버퍼 캐시에 읽어들인 각 데이터베이스의
   데이터를 정기적을 스캔하고 있다.
2. 스캔 시 더티 페이지가 발견되면 체크포인트 프로세스는 각 더티 페이지에 대해 물리 기록을 실행한다.
3. 체크 포인트 프로세스는 한 번에 16개까지 더티 페이지의 물리 기록을 요구하며 기본적으로 더티 페이지가
   없어질 때까지 비동기로 물리 기록을 반복한다.

3. 집중 기록 ( Eager Write )

 - 집중 기록도 더티 페이지의 물리 기록 수단 중 하나이다.

 => 보통의 경우 미리 쓰기 로그 동작에서의 데이터 갱신 동작은 모두 로그 파일에 기록된다.

 

 - 데이터가 큰 파일을 저장할 경우 미리 쓰기 로그를 실행하면 대량의 트랜잭션 로그 기록이 발생한다.

 => 스루풋 저하

 - 일괄 조작이라는 선택에 마련되어 있는데 대량 데이터의 입력을 일관 조작으로 취급하면 개별 데이터의 갱신 로그는

   기록되지 않고 일괄 조작 트랜잭션 로그만 기록한다.

 => 일괄 조작을 실행하면 대량의 더티 페이지가 버퍼 캐시상에 발생하는 데, 집중 기록이란 이 데이터를 물리 기록하기 위한 준비 동작이다. 대량의 데이터를 효율적으로 기록하기 위해 새로운 데이터를 저장하는 데 필요한 페이지를 디스크 상의 데이터 파일에 작상하는 동작과 버퍼 캐시상의 더티 페이지에 따른 물리 기록을 수행하는 동작을 병렬로 실행한다!!

 

즉, 대량의 데이터를 물리 디스크에 쓰기 위해 페이지 생성과 페이지 기록을 병렬로 처리하는 것을 의미한다.

4. 지연 기록 ( Lazy Write )

 - 지연 기록을 수행하기 위해 SQL 서버에는 지연 기록기 ( Lazy Writer )라는 내부 컴포넌트가 준비되어 있는데 

   지연 라이터의 가장 중요한 임무는 버퍼 캐시에 항상 일정량의 빈 페이지를 마련해 두는 것이다.

 - 디스크에서 읽어들인 데이터는 반드시 버퍼 캐시상에 저장된다.

 - 데이터가 새로 디스크에서 읽히면 저장하기 위한 빈 페이지가 버퍼 캐시상에 필요하다. 하지만 디스크에서 읽을 때마다

   빈페이지를 찾아 버퍼 캐시를 스캔하는 일은 비효율 적이다.

 => 미리 사용가능한 빈 페이지를 프리 리스트라는 링크 리스트에 등록해두고 데이터를 읽어들일 때 프리 리스트에 등록된

      빈 페이지에 저장하기만 하면 되는 것이다.

 

 - 프리 리스트에 등록된 빈 페이지수가 임계값에 밑돌게 되면 데이터를 읽어들일 때 대비할 목적으로 지연 라이터는

   참조 빈도가 낮은 버퍼 캐시상의 페이지를 초기화해 프리 리스트에 추가한다.

 => 만약 이 페이지가 더티 페이지인 경우 초기화를 하기 전에 물리 디스크에 내용이 기록된다. 이 동작을 지연 동작이라고 한다.