데이터베이스 성능 개선 포인트! 데이터베이스 Call 수 줄이기, 네트워크 부하 줄이기(array processing, 부분 범위 처리)

데이터베이스 SQL 쿼리 호출(Call) 수 줄이기

DB 성능 최적화를 하기 위해서 가볍게 개선 포인트를 알아보는 시간을 지난 포스트에 이어 갖는다.

지난 포스트에서는 SQL 쿼리가 날라왔을 때 SQL 쿼리를 파싱하고 실행 계획을 캐시하고 최적화하는 부분을 살펴봤다.

이번 포스트에서는 애초에 데이터베이스에 쿼리를 적게 날리는 즉, 호출 수를 줄이는 부분과 오고가는 데이터의 양(네트워크 부하)을 줄이는 부분을 알아볼 것이다.

(다음 포스트에서는 데이터베이스가 파일시스템(ex. SSD)과 데이터 I/O하는 부분에서 최적화를 알아볼 것이다. 아래 그림을 참조하면 좋다.)

---

데이터베이스 Call과 성능 

위에서는 편의를 위해 애플리케이션을 통해 호출되는 Call이 전부인양 말했지만 엄밀히 따지면 Call을 종류는 다양하다.

• parse call : SQL 파싱을 요청하는 call
• execute call : SQL 실행을 요청하는 call
• Fetch call : SELECT문의 결과 데이터 전송을 요청하는 call

• user call : 애플리케이션(외부)을 통해 요청하는 call
• recursive call : DBMS 내부에서 발생하는 call (SQL 파싱과 최적화를 위한 data dictionary 조회, 사용자 정의 함수, 프로시져 내에서의 SQL 수행)

같은 분류기준으로 call을 나눈 것은 아니니 참고만 하면 된다. (call의 종류가 다양하다 정도)

이번 포스트는 가벼운 마음으로 성능 개선 포인트만 보는 부분이니 user call을 개선하는 방법 몇 가지를 알아본다.

1. One SQL 사용하기

쉽게 설명하면 어떤 프로그램에서 루프를 돌면서 10만번, 100만번, 혹은 그 이상의 SQL쿼리를 날리는 것보다 SQL을 잘 튜닝해서 1번의 SQL로 동일한 데이터를 불러올 수 있게 개선하는 방법을 쓰라는 얘기다.

또는 SQL쿼리는 1번 호출했지만 내부적으로 같은 테이블을 여러번 불러오는 쿼리는 튜닝이 필요하다는 얘기다.

예를들어서 어떤 프로그램에서 (1)어떤 테이블을 조회(SELECT)하고 (ex. 조회한 레코드 수는 10만건) (2)조회한 레코드에서 필터링을 통해 다른 테이블에 삽입(INSERT)해야한다고 가정해보자.

그러면 벌써 10만건을 조회하는데 fetch call을 10만 번 했고 삽입하는데 parse call과 execute call을 적게는 10만 번씩 혹은 (insert문의 수가 더 많다면) 20만 번, 30만 번씩 되었을 것이다.

그러면 총 30만 번 이상 call이 쓰이는데 이것을 한 번의 SQL로 해결할 수 있게 전환하면 단 2회의 call(parse call, execute call)이 일어날 것이다.

이 뿐만아니라 예를 들어 하나의 작업을 하는데 5번의 call이 필요하다고 가정해보자. 5번의 call이 필요한 작업을 1번의 call이 필요하게 바꾸면, 24시간 내내 그 작업만 한다고 단순 계산했을 때 5배의 성능 개선이 있는 것이다.

2. Array Processing 활용하기

이것은 대용량의 작업을 할 때, 코드에서 하나하나 insert/update/delete를 하지말고 모아서 하는 batch의 개념이다.

주의할 점은 일련의 과정이 전부다 array단위로 진행되어야 한다.

무슨말이냐면 fetch는 array로 했는데 다음 단계에서 insert를 일일이 처리하면 효과가 떨어지고 반대의 경우도 마찬가지라는 얘기다. (모든 작업이 배치로 이뤄져야 효과가 극대화됨)

(개발 언어의 인터페이스를 확인할 것!) 

3. Fetch call 최소화 하기

DBMS도 엄청나게 큰 대용량의 레코드를 한 번에 클라이언트에게 전송할 수 없다.

따라서 일정량씩 나누어서 전송하게 되는데 이런 방법으로 fetch call을 줄일 수 있다.

오라클에서는 ArraySize 또는 FetchSize설정을 통해 일정량의 단위를 조절할 수 있다.

예를들어 ArraySize가 100으로 설정되어있고 30,000개의 레코드를 읽는다면 fetch call은 301번 발생할 것이다.

OLTP성 업무에서는 쿼리 결과(레코드)가 아주 많아도 그 중 일부만 fetch해서 보여줘도 되는 경우 사용하면 좋다.

(이러한 방법을 부분 범위 처리라 함 + 사용자가 수 천만건을 다 읽지는 않기 때문에 위와 같이 응답성 올림)

주의할 점은 DBMS에서는 부분 범위 처리를 하는데 개발자가 모든 값이 fetch되어야 처리하는 방식으로 프로그래밍해버리면 성능 개선 효과가 없다.

결국 포인트는 네트워크를 통해 전송해야할 데이터의 양에 따라서 ArraySize를 적절히 조절할 필요가 있다는 것이다.

- 전체 데이터를 다 받아야 한다면 ArraySize를 크게 하는 것이 좋다. 양은 그대로지만 fetch call수가 줄어든다.

- 전체 데이터에서 앞에 일부분만 필요하다면 ArraySize를 작게 하는 것이 좋다.

ArraySize를 증가시키면 네트워크 부하가 줄어들 뿐만 아니라 서버 프로세스가 읽어야할 블록 개수도 줄어든다.

이유는 하나의 Block에 10개의 레코드가 있다고 치고 2개의 Block 즉, 20개의 레코드가 있을 때, 20개의 레코드를 다 읽어야 할 때 ArraySize가 3이라면 block I/O는 8번 일어나고 fetch call은 7번 일어난다.

근데 ArraySize가 5라면 block I/O는 4번 일어나고 fetch call도 4번 일어나기 때문이다.

단, ArraySize는 무조건 키우는게 좋은 것만은 아니다. ArraySize와 call 수가 정반비례(?)로 되지 않기 때문에 적절히 테스트하고 사용하면 좋다.

4. 페이지 처리 활용

앞서 부분 범위 처리를 사용한 성능 개선은 커서를 닫지 않은 상태에서 사용자가 명시적으로 요청할 때만 fetch하는 식의 개발환경에서만 가능한 단점이 있다.

데이터베이스와 연결을 지속하지 않는 웹 애플리케이션 환경에서는 커서를 계속 연 채로 핸들링할 수 없기 때문에 사용자가 다음 페이지를 요청할 때마다 개별적인 SQL문을 수행하는 식의 처리를 해야한다.

페이징 처리는 다른 포스트에서 이미 사용해봤기 때문에 인지하고 있으면 된다.

5. 분산 쿼리, 원격 조인할 때 최소한의 데이터만 필터링해서 보내자

분산 데이터베이스 시스템을 만들었다면 원격에 있는 테이블을 전송받아 조인해야하는 경우가 있다.

이럴 때 만약 조인해야하는 테이블의 레코드 수가 50만건이 있다면 네트워크 부하는 50만 건을 다 전송하고 수신할 것이다.

그렇게 하면 역시 쿼리 성능이 좋지 않게될 것이니 필요한 부분만 쪼개서 보내는 쿼리를 사용해야한다.

6. 꼭 필요할 때만 사용자 정의 함수, 프로시져 사용하기

DBMS 내부에서 수행되는 사용자 정의 함수와 프로시져는 내장 함수처럼 Native 코드로 완전 컴파일된 형태가 아니어서 가상머신같은 별도의 엔진에서 실행된다.

따라서 실행할 때마다 컨텍스트 스위칭이 일어나면서 내장함수에 비해 성능이 떨어지게 된다.

추가로 사용자 정의 함수에 또 다른 쿼리문이 내장되어 있으면 더욱더 나빠진다.

무작정 쓰지 말기를 권장하기보다는 적절한 때를 찾아야한다.

그 적절한 때는 소량 호출하고 내부에서 다량으로 SQL을 수행해야 하는 형태일 때이다.

이렇게 해서 네트워크 부하를 줄이기 위해 DB call의 수를 줄이는 방법을 알아보았다.

다 이해하지 못했지만 인지만 하고 다음에 세밀하게 파고들 때 더 잘 알아보도록 한다.

참고 자료

: SQL 전문가 가이드 2013 edition - 한국 데이터베이스 진흥원