DB 튜닝 입문 (1) 오라클의 구조 (진짜 처음에는 튜닝하기 전에 구조를 알아야 한다!)

그림으로 공부하는 오라클 구조

* DB 튜닝 입문 포스트는 "그림으로 공부하는 오라클 구조" 라는 책을 읽고 정리한 내용이다.

* MySQL, Oracle, PostgreSQL, MSSQL등 관계형 데이터베이스 중 아무거나 하나를 사용해본 경험이 한번이라도 있는 사람이 보는 것이 좋다. (단순 SQL문 작성 가능한 정도(select, insert, update, delete, commit, rollback)

* 블로그 필자는 아직 초급개발자도 아니라서 실무에서 튜닝을 하지 않지만 관심이 생겨 보는 수준.

---

기본 마인드 

1. 병렬 처리를 가능케 하고 높은 처리량을 실현

2. 빠른 응답 중시

3. 커밋한 데이터는 지킴

---

데이터베이스도 결국 디스크 I/O

데이터베이스도 근본적으로 데이터를 디스크(HDD, SSD)에 데이터를 저장하고 꺼내오는 것이다.

(오라클같은 DBMS를 컴퓨터에서 실행되고 있는 일종의 프로세스로 봐도 무방.)

컴퓨터 (CPU - Memory - Disk) 구조에서 CPU와 메인메모리는 ns(나노초)단위로 데이터를 주고 받고 메인메모리와 디스크는 ms(밀리초)단위로 데이터를 주고 받는다.

따라서 DBMS는 디스크에서 메모리로 적재하고 저장하는(access) I/O 작업을 줄여야 한다.

결론부터 말하면 가급적이면 디스크에 접근하는 것을 줄이는 것이 좋고 HDD에서 SSD로 바꿔 I/O성능이 좋아진다고 해서 무조건 DBMS의 성능이 좋아지는 것은 아니다.

디스크 I/O 이슈는 여러 개의 디스크로 데이터베이스를 구축하는 것으로 I/O를 분산시켜 성능을 높일 수 있다는 것까지만 알아둔다.

---

오라클의 인덱스 구조

디스크 I/O를 줄이는 방법 중 한 가지로 full scan을 지양하는 것이 있다.

데이터베이스를 조금이라도 공부해본 사람은 '인덱스'를 이용해서 성능을 높인다는 것을 알고 있다.

오라클에서는 인덱스를 어떻게 사용하는지 정리한다.

[ SELECT "소속회사" FROM "개인데이터" WHERE "이름" = "정프로"; ] 

이런 SQL문을 예로들면 "이름"이 "정프로"인 데이터의 정보가 어디있는지 인덱스 테이블에서 ROWID를 찾고, ROWID를 토대로 데이터를 찾아온다.

그런데 인덱스 테이블이 데이터 테이블만큼 많아지면 어떨까?

오라클에서는 '인덱스의 인덱스'를 붙이는 형태로 여러 단계의 인덱스 테이블을 구성한다.

- 무조건 인덱스를 사용하는 것이 빠르고 효율적이진 않다.

  보통 접근하려는 데이터가 전체의 15% 미만일 때만 인덱스를 사용하는 것이 효율적이라고 한다.

2만건의 데이터가 있는 테이블에서 1만건 정도를 조회할 때 일일이 인덱스를 거치는 것보다 그냥 2만건을 전부 읽는 것이 빠르기 때문.

---

오라클은 여러 개의 프로세스

오라클은 여러 개의 프로세스로 이루어져있다.

그런 이유는 다중 처리를 하기 위해서다.

SQL 처리가 길어지면 그 동안 다른 사용자를 기다리게 할 수 없기 때문에 속도가 느린 I/O를 하는 동안 CPU는 다른 SQL을 처리할 수 있게 해줘야 한다.

따라서 담당 업무에 따라 프로세스를 나눴다. (같은 오라클을 여러개 띄운 것이 아님, 리눅스에서 ps명령어로 확인 가능)

* 단, 윈도우 OS에서는 멀티 프로세스가 아닌 멀티 쓰레드로 병렬처리를 함.

구성 : 서버 프로세스 + 백그라운드 프로세스

서버프로세스는 SQL문 처리를 담당하고 백그라운드 프로세스는 보조 역할이다.

- 백그라운드 프로세스 (X에는 0,1같은 숫자가 들어감)

ora_dbwX_XXXXXX : '데이터베이스 라이터'로 데이터를 디스크에 기록하는 역할

ora_lgwr_XXXXXX : '로그 라이터'로 로그를 디스크에 기록하는 역할

ora_pmon_XXXXXX : '피몬'으로 프로세스를 감시하고 프로세스의 장애(비정상 종료등 )를 발견했을 때 정리하는 역할

ora_arcX_XXXXXX : '아카이버'로 로그 데이터를 아카이브(장기 보관을 위해 별도의 파일로 보관)하는 역할

server process = shadow process = foreground process 같은 용어.

* 튜닝을 할 때는 '서버프로세스'를 봐야한다.

---

DB 성능, 튜닝의 핵심 '캐시'

CPU와 메모리 사이에 캐시를 두어 빠르게 접근하듯 오라클에서도 메모리에 버퍼 캐시를 두어 디스크I/O를 줄이고 성능을 증가시킨다. (캐시알고리즘 = LRU)

* 오라클에서 데이터는 '블록' 단위로 관리한다.

I/O도 블록 단위 캐시도 블록 단위다.

오라클의 데이터는 수천 바이트 이상의 다수의 '행'으로 존재하므로 오라클도 블록이라고 하는 상자에 보관하는 것이다.

블록의 크기는 2KB, 4KB, 8KB, 16KB, 32KB 중에서 고를 수 있다. 보통 8KB를 쓴다.

대신 크기가 엄청 큰 테이블을 시퀀셜 액세스로 읽어 와야만 하는 데이터웨어하우스에서는 16, 32KB 크기를 선택할 때도 있다.

* 프로세스들은 캐시를 공유한다.

다른 프로세스가 변경한 데이터를 볼 수 없는 문제가 생기고 프로세스마다 캐시를 갖게하면 낭비가 많아진다.

- 기본적으로 다른 프로세스의 메모리를 보는 것은 불가능하다. (OS가 보호해줌)

대신 OS의 특수 기능으로 '공유메모리' 를 사용할 수 있다. (결국 실제메모리는 한 개라는 점을 이용)

그래서 프로세스들은 공유메모리를 각각 점유하고 있는 것처럼 보여 용량은 커보이지만 사실 공유하고 있는 것이다.

* 공유메모리 설정하기

initXXXX.ora 라는 오라클의 설정 파일에 'DB_CACHE_SIZE'라는 파라미터의 값을 변경하면 된다.

버퍼 캐시의 기본 크기는 48KB(오라클 9i/10g의 기본값)이다. 하지만 버퍼 캐시의 크기는 가능하다면 수백MB를 할당하는 것이 좋다.

참고로 공유 메모리에는 버퍼 캐시만 있는 것이 아니라 작은 부분이지만 shared pool, 로그 버퍼 같은 영역도 있다.

- 오라클을 설치할 때 OS에 맞춰 공유메모리를 설정할 필요가 있다.

(오라클에서 제공하는 메뉴얼에 따라 각 OS용 설정을 따르도록 한다. OS에 제한이 있을 수 있다.)

OS의 제한이 없다면 버퍼 캐시의 크기를 GB단위로 설정하기도 한다.

set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=10

['shm'으로 시작하는 부분이 공유 메모리의 설정]

---

* 세마포어

세마포어는 OS가 제공하는 자원을 관리하기 위한 장치의 일종으로 자원 수에 비해 사용하고자 하는 프로세스의 수가 많을 경우 순서대로 자원을 사용할 수 있도록 프로세스를 제어한다.

set semsys:seminfo_semmni=100
set semsys:seminfo_semmsl=256
set semsys:seminfo_semmns=1024
set semsys:seminfo_semopm=100
set semsys:seminfo_semvmx=32767
// 'sem'으로 시작하는 부분이 세마포어

세마포어도 메뉴얼에서 제공하는 대로 설정해야하고 오라클 구동시 세마포어가 부족하다는 메시지가 나오면 늘리는 것을 검토해야한다.

* OS, 스토리지 고려하기

버퍼 캐시 사이즈를 크게 올렸지만 충분한 메모리가 없을 경우 가상메모리기능을 사용해서 오라클을 작동시킬 수도 있다.

그래서 버퍼 캐시 자체가 '스왑(swap)'에 들어가버려 캐시사용의 의미가 없어질 수 있다.

따라서 충분한 메모리를 준비하고 가상 메모리를 사용하지 않게 하는 것을 권고한다.

---

옵티마이저(최적화, parser)

애플리케이션에서 사용하는 SQL도 어쨋든 컴퓨터가 이해할 수 있는 언어로 파싱(parsing)해야 한다.

심지어 DB 특성상 어떻게하면 더 효율적으로 SQL문을 처리할지도 고민하는 실행 계획(plan)도 생성해야 한다.

=> 결과적으로 실행 계획을 짜는 것은 비용 기반으로 짠다.

비용 기반은 말 그대로 'I/O횟수가 적고 실행 속도가 가장 빠른 처리 방법이 최고다!' 라는 알고리즘이다.

이를 예측(계산)하기 위해서 기본적으로 (누적데이터)통계수치가 있어야 한다.

오라클에서는 이 정보를 "dbms_stats" 라는 작업을 통해 얻을 수 있다.

<비용 계산에 사용되는 정보들, 출처 : 그림으로 배우는 오라클 구조 57page>

이러한 분석 작업을 매 쿼리마다 한다는 것은 CPU라는 자원을 엄청나게 잡아먹고 실행도 느려지게된다.

따라서 이런 분석 작업은 공유해서 CPU소비를 줄이는 방법이 공유 풀(Shared pool)이다.

공유풀도 역시 공유되야하므로 공유 메모리에 존재하고 shared pool안에는 딕셔너리 캐시(Dictionary cache), 라이브러리 캐시(Library cache)등 영역으로 나눠진다.

* 공유 풀 작성 원리 알아보기

=> SQL문마다 ID를 생성한다. 여기서 ID는 해시함수를 이용해서 같은 SQL문이면 같은 해시코드(ID)를 만든다.

그러면 어떤 SQL문을 같다, 다르다 판단할까?

***

select id, cust_name, tel_no from cust where id = 1;
Select id, cust_name, tel_no from cust where id = 1;

이 두 SQL문은 같은 문장이지만 오라클은 그렇게 생각하지 않고 다른 ID(해시코드)를 생성한다.

즉, 대소문자를 구분한다.

SELECT id, cust_name, tel_no FROM cust where id = 1;
SELECT id, cust_name, tel_no FROM cust where id = 2;

이 두 SQL문은 다른 문장이지만 오라클은 같은 문장이라고 생각한다.

뒤에 1,2가 다른것은 '바인드 변수'를 이용해서

SELECT id, cust_name, tel_no FROM cust where id = :A;

라고 적용해서 ID(해시코드)를 만들기 때문이다.

파스(parse)에서도 하드 파스, 소프트 파스로 나뉜다.

하드 파스 : 공유 풀에 실행 계획이 없어서 실행계획을 생성하는 경우

소프트 파스 : 공유 풀에 이미 실행 계획이 있어서 사용하는 경우

---

** 튜닝에 실행 계획 사용하기

1) 실행 계획이 나쁘고 SQL문의 성능이 좋지 않을 때

-> 비용 계산에 사용되는 기초 정보가 잘 수집되는지 확인, 수집하고 있지 않으면 dbms_stats 패키지를 실행해서 최신 통계 정보 수집.

정보가 잘 수집되고 있는대도 실행계획이 최적이 아닐 때는 옵티마이저 판단이 좋지 않다는 의미라 힌트나 플랜 스태빌리티(plan stability)라는 기능을 이용해서 오라클에 지시.

2) 하드 파스가 많으며 분석에 사용하는 CPU양이 많을 때

SQL문을 바인드 변수를 사용하도록 변경, 불가할 시 CURSOR_SHARING이라 하는 초기화 파라미터를 설정함으로써 바인드 변수를 적용한 것과 비슷한 효과볼 수 있음

3) 공유 풀 크기조정

무조건 크게한다고 좋은게 아니다. 왜냐하면 사용 빈도가 떨어졌는데도 공유 풀에서 정보를 유지하기 때문.

따라서 소프트 파스가 많이 일어나서 효율이 좋아야 하는데 왜이럴까? 싶을 때 캐시의 크기를 높여주는 방법으로 조정.

---

버퍼 사이즈 참고 자료

네이버가 실험에 사용한 DBMS 서버 사양

- cpu : xeon cpu L5650(2.27GHz 2*6 cores)

- memory : 16GB

- HDD : 300GB * 2

- SSD : 64GB * 4

- OS : CentOS 5.3 x86_64

- DBMS : CUBRID 2008 R4.1 (8.4.1.0516) (64bit release build for linux_gnu)

TPS : Transactions per second

그림 출처 및 참고 사이트

http://d2.naver.com/helloworld/7005