[SQLP] III. 제1장. 아키텍처 기반 튜닝 원리 - 1

제1절. 데이터베이스 아키텍처

 

1. 아키텍처 개관

가. Oracle 아키텍처

• Oracle에서의 데이터베이스(Database)는 디스크에 저장된 데이터 집합 (Datafile, Redo Log File, Control File 등)
• SGA 공유 메모리 영역과 이를 엑세스하는 프로세스 집합을 합쳐서 인스턴스(Instance)라고 한다.
• 기본적으로 하나의 인스턴스가 하나의 데이터베이스만 액세스하지만, RAC(Real Appliacation Cluster) 환경에서는 여러 인스턴스가 하나의 데이터베이스를 액세스할 수 있다.
• 하나의 인스턴스가 여러 데이터베이스를 액세스할 수는 없다.

 

나. SQL Server

• SQL Server는 하나의 인스턴스 당 최고 32,767개의 데이터베이스를 정의해 사용할 수 있다. 
• 기본적으로 master, model, msdb, tempdb 등의 시스템 데이터베이스가 만들어지며, 여기에 사용자 데이터베이스를 추가로 생성하는 구조
• 데이터베이스 하나를 만들 때마다 주(Primary 또는 Main) 데이터 파일과 트랜잭션 로그 파일이 하나씩 생기는데, 전자는 확장자가 mdf이고 후자는 ldf이다.
• 저장할 데이터가 많으면 보조(Non-Primary) 데이터 파일을 추가할 수 있으며, 확장자는 ndf이다.

 

2. 프로세스

• SQL Server는 쓰레드(Thread) 기반 아키텍처이므로 프로세스 대신 쓰레드라는 표현을 써야 한다.
• 프로세스는 서버 프로세스(Server Process)와 백그라운드 프로세스(Background Process) 집합으로 나뉜다.
• 서버 프로세스 : 전면에 나서 사용자가 던지는 각종 명령 처리
• 백그라운드 프로세스 : 뒤에서 주어진 역할 수행

가. 서버 프로세스 (Server Process)

• 서버 프로세스는 사용자 프로세스와 통신하면서 사용자의 각종 명령을 처리
• SQL Server에서는 Worker Thread가 같은 역할을 담당
• SQL을 파싱하고 필요 시 최적화를 수행하며, 커서를 열어 SQL을 실행하면서 블록을 읽고, 읽은 데이터를 정렬해서 클라이언트가 요청한 결과집합을 만들어 네트워크를 통해 전송하는 일련의 작업을 모두 처리
• 스스로 처리하도록 구현되지 않은 기능(ex. 데이터 파일로부터 DB 버퍼 캐시로 블록을 적재하는일, Dirty 블록을 캐시에서 밀어냄으로써 Free 블록을 확보하는 일, Redo 로그 버퍼를 비우는 일 등)은 OS, I/O 서브시스템, 백그라운드 프로세스가 대신 처리하도록 시스템 Call을 통해 요청
• 클라이언트가 서버 프로세스와 연결하는 방식은 DBMS마다 다르지만 Oracle을 예로 들면 전용 서버 방식과 공유 서버 방식, 두 가지가 있다.

      1) 전용 서버 (Dedicated Server) 방식

          -  처음 연결 요청을 받는 리스너가 서버 프로세스(Window 환경에서는 쓰레드)를 생성해주고, 이 서버 프로세스

             가 단 하나의 사용자 프로세스를 위해 전용(Dedicated) 서비스를 제공

          -  만약 SQL을 수행할 때마다 연결 요청을 반복하면 서버 프로세스의 생성과 해제도 반복하게 되므로 DBMS에

             매우 큰 부담을 주고 성능을 크게 떨어뜨린다.

          -  따라서 전용 서버 방식을 사용하는 OLTP성 애플리케이션에서는 Connection Pooling 기법을 필수적으로 사용

             해야 한다. (ex. 50개의 서버 프로세스와 연결된 50개의 사용자 프로세스를 공유해서 반복 재사용하는 방식)

      2) 공유 서버 (Shared Server) 방식

          -  하나의 서버 프로세스를 여러 사용자 세션이 공유하는 방식

          -  Connection Pooling 기법을 DBMS 내부에 구현해 놓은 것과 유사. 즉, 미리 여러 개의 서버 프로세스를 띄워

             놓고 이를 공유해서 반복 재사용

          -  공유 서버 방식으로 Oracle에 접속하면 사용자 프로세스는 서버 프로세스와 직접 통신하지 않고 Dispatcher

             프로세스를 거친다.

          -  사용자 명령이 Dispatcher에게 전달되면 Dispatcher는 이를 SGA에 있는 요청 큐(Request Queue)에 등록.

             이후 가장 먼자 가용해진 서버 프로세스가 요청 큐에 있는 사용자 명령을 꺼내서 처리하고, 그 결과를 응답 큐

             (Response Queue)에 등록

          -  응답 큐를 모니터링하던 Dispatcher가 응답 결과를 발견하면 사용자 프로세스에게 전송

나. 백그라운드 프로세스 (Background Process)

• Checkpoint / Database Checkpoint thread : 좀 더 자세히 설명하면, Write Ahead Logging 방식(데이터 변경 전에 로그부터 남기는 메커니즘)을 사용하는DBMS는 Redo 로그에 기록해 둔 버퍼 블록에 대한 변경사항 중 현재 어디까지를 데이터 파일에 기록했는지 체크포인트 정보를 관리해야 한다. 이는 버퍼 캐시와 데이터 파일이 동기화된 시점을 가리키며, 장애가 발생하면 마지막으로 체크포인트 이후 로그 데이터만 디스크에 기록함으로써 인스턴스를 복구할 수 있도록 하는 용도로 사용된다. 이 정보를 갱신하는 주기가 길수록 장애 발생 시 인스턴스 복구 시간도 길어진다.

3. 파일 구조

가. 데이터 파일

• Oracle과 SQL Server 모두 물리적으로는 데이터 파일에 데이터를 저장하고 관리한다. 공간을 할당하고 관리하기 위한 논리적인 구조도 크게 다르지 않지만 약간의 차이는 있다.

      1) 블록(=페이지)

          -  대부분 DBMS에서 I/O는 블록 단위로 이루어진다.

          -  블록은 데이터를 읽고 쓸 때의 논리적인 단위

          -  Oracle은 블록(Block), SQL Server는 페이지(Page)

          -  Oracle은 2KB, 4KB, 8KB, 16KB, 32KB, 64KB 등의 다양한 블록 크기를 사용할 수 있지만, SQL Server에서는

             8KB 단일 크기를 사용한다.

          -  블록 단위로 I/O한다는 것은, 하나의 레코드에서 하나의 칼럼만을 읽으려 할 때도 레코드가 속한 블록 전체를

             읽게됨을 뜻한다.

          -  SQL 성능을 좌우하는 가장 중요한 성능지표는 액세스하는 블록 개수이며, 옵티마이저의 판단에 가장 큰 영향

             을 미치는 것도 액세스해야 할 블록 개수다.

          -  예를 들어, 옵티마이저가 인덱스를 이용해 테이블을 액세스할지 아니면 Full Table Scan 할지를 결정하는 데

             있어 가장 중요한 판단 기준은 읽어야 할 레코드 수가 아니라 읽어야 하는 블록 개수다.

      2) 익스텐트 (Extent)

          -  데이터를 읽고 쓰는 단위는 블록이지만, 테이블 스페이스로부터 공간을 할당하는 단위는 익스텐트다.

          -  테이블이나 인덱스에 데이터를 입력하다가 공간이 부족해지면 해당 오브젝트가 속한 테이블 스페이스(물리적

             으로는 데이터 파일)로부터 추가적인 공간을 할당받는데, 이 때 정해진 익스텐트 크기의 연속된 블록을 할당받

             는다.

          -  예를 들어, 블록 크기가 8KB인 상태에서 64KB 단위로 익스텐트를 할당하도록 정의했다면, 공간이 부족할 때마

             다 테이블 스페이스로부터 8개의 연속된 블록을 찾아(찾지 못하면 새로 생성) 세그먼트에 할당해 준다.

          -  익스텐트 내 블록은 논리적으로 인접하지만, 익스텐트끼리 서로 인접하지는 않는다. 예를 들어, 어떤 세그먼트

             에 익스텐트 2개가 할당됐는데, 데이터 파일 내에서 이 둘이 서로 멀리 떨어져 있을 수 있다.

          -  Oracle은 다양한 크기의 익스텐트를 사용하지만 SQL Server에선 8개 페이지의 익스텐트만을 사용한다. 페이

             지 크기도 8KB로 고정되어있으므로 익스텐트는 항상 64KB인 셈이다.

          -  Oracle은 한 익스텐트에 속한 모든 블록을 단일 오브젝트가 사용하지만, SQL Server에서는 2개 이상 오브젝트

             가 나누어 사용할 수도 있다. SQL Server는 다음 2가지 타입의 익스텐트를 사용한다.

• 균일(Uniform) 익스텐트 : 64KB 이상의 공간을 필요로 하는 테이블이나 인덱스를 위해 사용되며, 8개 페이지 단위로 할당된 익스텐트를 단일 오브젝트가 모두 사용한다.
• 혼합(Mixed) 익스텐트 : 한 익스텐트에 할당된 8개 페이지를 여러 오브젝트가 나누어 사용하는 형태다. 모든 테이블이 처음에는 혼합 익스텐트로 시작하지만 64KB를 넘으면서 2번째부터 균일 익스넽르를 사용하게 된다.