[SQL] Process와 I/O가 SQL 속도에 미치는 영향

 

데이터 베이스 조회 속도를 향상시키기 위해서 Index가 중요한 것은 다들 아는 이야기입니다. 그렇다면 무엇이 속도의 영향을 미치는가?에 대한 질문에는 정확한 답변이 어려울 수 있습니다. 요번 포스팅에서는 해당 질문을 시작으로 SQL에서 속도에 영향을 미치는 프로세스에 대해 설명하도록 하겠습니다.

 

 

 

| Process란?

 

프로세스는 '실행 되는 프로그램'으로 SQL에서는 데이터베이스 작업을 수행하는 독립적인 작업 단위를 뜻합니다. Select, Update 등이 Process에 해당합니다.

 

 

| Process Life Cycle

 

프로세스의 생명 주기는 아래와 같이 5가지로 볼 수 있으며 성능에 영향을 미치는 부분은 '대기' 내용으로 해당 부분을 집중적으로 설명드리도록 하겠습니다.

 

1. 생성

• 데이터베이스에서 쿼리를 실행하거나 작업을 요청하면 데이터베이스 서버는 요청을 해결하기 위한 프로세스를 생성합니다.

 

2. 실행

• 요청을 실행하기에 앞서 SGA(SGA란? 포스팅 확인하러 가기)의 Library Cache에서 해당 SQL 쿼리가 있는지 탐색을합니다.
• SQL 쿼리가 확인될 경우 Cache에 있는 쿼리를 사용하며 확인되지 않을 경우, 옵티마이저(SQL의 두뇌 역할)가 쿼리의 최적화를 진행합니다(조인 방식, 정렬 여부, Index 사용 등). 이때 많은 CPU해 성능에 영향을 미치긴 하지만 SQL의 성능이라하면 SQL 쿼리 해석보다 I/O(Input/Output)에 큰 비중을 두겠습니다.

 

3. 대기

대기라고 하면 앞선 작업이 끝나기를 기다리는 것처럼 들리나, SQL은 멀티태스킹 운영 체제로 앞선 프로세스가 끝나길 기다리는 것이 아닙니다. 다만 같은 디스크 블록(블록이란? 포스팅 확인하러 가기)을 읽을 때는 락 대기를 통해 동시성을 제한해 일관성과 무결성을 확보합니다. 즉 아래와 같이 두 가지의 프로세스 대기가 있으며, 저희가 살펴볼 것은 I/O 대기입니다.

• Lock 대기 : 서로 다른 프로세스가 같은 디스크 블록을 읽고 쓰는 것을 방지하기 위해 동시성을 제한합니다. 이로 인해 일관성과 무결성을 확보합니다.
• I/O 대기 : DB Buffer Cache에서 찾는 블록이 없는 경우 DB 디스크에 접근해야 합니다. DB 디스크를 탐색하는 동안 프로세스는 대기 상태에 들어가며, 프로세스가 잠든다고도 표현합니다. 즉, I/O 대기가 많고 길어질수록 요청이 느려지게되며 성능 저하로 이어집니다.

 

4. 종료

• 요청에 대한 결과를 반환하고 종료 상태로 넘어가며, 할당된 메모리, cpu 자원을 반납하고 프로세스는 삭제됩니다.

 

5. 예외 및 오류 처리

• 예외가 발생하면 예외처리를 진행하며, 오류가 발생하면 트랜잭션을 통해 롤백을 진행합니다. 또한 개선할 수 있도록 로그에 기록합니다.

 

<Process 생명 주기>

 

 

| SQL 성능 개선

 

즉 프로세스의 대기를 최소화시켜 SQL의 성능을 높여야 합니다. 즉 공유 메모리 영역인 SGA Cache에 정보를 넣어 DB 디스크의 I/O를 줄여야 합니다. 하지만 메모리는 한정되어 있기에 모든 블록을 넣어둘 수 없습니다(메인 메모리 DB가 있습니다만, 일반적인 SQL 형태를 기준으로 설명하겠습니다).

이를 해결하기 위해 SGA에 필요한 정보들만 넣어 성능을 향상시켜야 하며, 다음 포스팅에서 성능의 지표가 되는 버퍼 캐시 히트율과 함께 설명 드리도록 하겠습니다.