[SQLp]과목 3 - 1 데이터베이스 Call과 네트워크 부하

 

SQLp - 국가공인 SQL 전문가

INDEX

• 과목 1 - 데이터 모델링의 이해

  • 데이터 모델링의 이해

  • 데이터 모델과 성능

  ​

• 과목 2 - SQL 기본 및 활용

  • SQL 기본

  • SQL 활용

  • SQL 최적화 기본 원리

  ​

• 과목 3 - SQL 고급 활용 튜닝

  • 아키텍처 기반 튜닝 원리

  • Lock 과 트랜잭션 동시성 제어

  • 옵티마이저 원리

  • 인덱스와 조인

  • 고급 SQL 튜닝

---

 

3과목은 튜닝에 대한 이해와 실습 병행이 필요할 것 같아 주제별로 정리를 할 계획입니다.

 

 

과목 3 - SQL 고급 활용 및 튜닝

1 . 아키텍처 기반 튜닝 원리

데이터베이스 성능 튜닝의 3대 핵심 요소로서 SQL 파싱 부하 해소, 데이터베이스 call 최소화, I/O 효율화를 뽑을 수 있다. 이번 포스팅에서는 성능튜닝의 핵심 요소들을 이해하기 위한 기본 지식과 요소들을 소개할 것 입니다.

• 데이터베이스 Call과 네트워크 부하

  서버 프로세스에게 작업을 지시하거나 결과 집합을 요청할 때 명령은 데이터베이스 Call을 통해 전달 된다. 데이터베이스 Call 과 결과 전송은 네트워크를 통해 이루어지며 서버와의 Roundtrip 횟수가 많을 수록 쿼리 수행 속도가 떨어진다. 쿼리의 수행 속도 뿐만 아니라 시스템 전체의 확장성도 낮아진다. 따라서 데이터베이스 Call 종류와 특성을 이해하여 그 횟수를 줄이도록 노력해야 한다.

   

1. 데이터베이스 Call 종류

   
   

   • SQL 커서에 대한 작엽 요청에 따른 구분

     • Parse Call : 파싱을 요청하는 Call

     • Execute Call : 실행을 요청하는 Call

     • Fetch Call : SELECT 문의 결과 데이터 전송을 요청하는 Call

   • Call 발생 위치에 따른 구분

     • User Call

       DBMS 외부로 부터 요청되는 Call을 말한다. User Call은 동시 접속자 수가 많은 Peak 시간대에 시스템 확장성을 떨어뜨리는 가장 큰 요인중 하나이다. User Call 이 많이 발생하는 프로그램은 성능이 좋지 않고, 이는 개발자의 기술력과 애플리케이션 설계와 프레임워크 기술 구조 에 기인한다.(Ex- Array Processing을 지원하지 않는 프레임워크, 화면 페이지 처리를 잘못 설계한 표준 가이드, 사용자 정의 함수/프로시저에 대한 무조건 적인 제약, 프로시저 단위 모듈을 지나치게 잘게 쪼개서 SQL을 매번 호출하는 설계..)

       DBMS 성능과 확장성을 높이려면 User Call을 최소화 하려는 노력과 다음과 같은 기술요소를 활용해야 한다.

       • 기술 요소

         1) Loop 쿼리 해소, 집합적 사고를 통해 One SQL로 구현

         2) Array Processing : Array 단위 Fetch, Bulk Insert, Update, Delete

         3) 부분범위처리 원리 활용

         4) 효과적인 화면 페이지 처리

         5) 사용자 정의 함수, 프로시저, 트리거의 적절한 활용

        

     • Recursive Call

       DBMS 내부에서 발생하는 Call을 말한다. SQL 파싱과 최적화 과정에서 발생하는 데이터 딕셔너리 조회, 사용자 정의 함수/프로시저 내에서의 SQL 수행에서 Recursive Call이 발생한다.

       이를 최소화 하려면 바인드 변수를 적극적으로 사용해 하드파싱 발생횟수를 줄여야 한다. 그리고 사용자 정의 함수와 프로시저가 어떤 특징을 가지며 내부적으로 어떻게 수행되는지를 잘 이해하고 적절하게 사용해야만 한다.

   
   

    

2. 데이터베이스 Call과 성능

   
   

   • One SQL 구현의 중요성

     루프를 통해 작업이 반복 수행하는 프로그램을 One SQL로 구현했을 때 성능 개선 효과가 높다. 이는 데이터베이스 Call 횟수를 줄였기 때문이다. 데이터베이스 Call을 줄이면 네트워크 구간에서의 소비되는 시간을 줄일 수 있고, OS 의 CPU와 메모리 리소스를 할당받는 시간을 줄일 수 있다.

     
     

   • 데이터베이스 Call과 시스템 확장성

     데이터베이스 Call은 개별 프로그램의 수행 속도에 영향이 크며 궁극적으로 시스템 전체의 확장성에 영향을 준다. 이를 통해 애플리케이션 설계가 DBMS 성능을 좌우하는 중요한 요인임을 알 수 있다.

      

   • Array Processing 활용

     Array Processing 기능을 통해 한번의 SQL 수행으로 다량의 레코드를 동시에 처리할 수 있다. 이는 네트워크를 통한 데이터베이스 Call을 줄이고 SQL 수행 시간과 CPU 사용량을 획기적으로 줄여준다. 이 효과를 극대화 하려면 연속된 일련의 처리과정이 모두 Array 단위로 진행되야 한다.

    

   • Fetch Call 최소화

     • 부분범위처리 원리

       DBMS 는 일정량씩 나누어 데이터를 전송한다.

       Oracle 의 경우 ArraySize 혹은 FetchSize를 통해 단위를 조절할 수 있다. Array 크기의 데이터를 내부적으로 다시 SDU(Session Data Unit, Session 레이어), TDU(Transport Data Unit, Transport 레이어) 단위로 나누어 전송한다. ArraySize를 작게 설절하면 하나의 네트워크 패킷에 담아 전송하겠지만, 크게 설정하면 여러개 패킷으로 나누어 전송한다.

        

       xxxxxxxxxx
       set arraysize 100
       

       이렇게 설정한 후에 30000개의 로우를 읽는다면, Fetch Call 이 301번 발생한다.

        

       SQL Server 의 경우 네트워크 패키지 크기로 운반단위를 조절하는데, 쿼리 분석기 옵션에서 '네크워크 패키지 크기'항목을 보면 기본 값이 4096 바이트로 설정되어 있다.

       전체 결과 집합 중 아직 전송하지 않은 분량이 남아 있어도 클라이언트로 부터 추가 Fetch Call을 받기 전까지 서버는 기다린다.

       이처럼 전체 데이터를 연속적으로 전송하지 않고 사용자로부터 Fetch Call이 있을 때마다 일정학 나누어 전송하는 것을 부분범위처리라고 한다.

       OLTP성 업무에서 화면상에서 사용자가 행위를 할 때만 추가적인 Fetch Call을 일으켜 필요한 만큼 더 가져온다. 물론, 커서를 닫지 않은 상태여야 한다.

       이런 화면 처리가 가능한 업무라면, 출력 대상 레코드가 많을수록 Array 를 빨리 채울수 있어 쿼리 응답속도도 빨라 진다. 이는 잘 설계된 인덱스와 부분 범위처리 방식을 사용하여 대용량 OLTP 환경에서 극적인 성능 개선 효과를 얻을 수 있는 원리이다.

       출력 대상 레코드가 많을 수록 응답 속도가 빨라지는 것은 부분범위처리가 가능한 업무에서만 해당한다.

       -> 결과집합 전체를 Fetch 하는 DW/OLAP성 업무나 서버 내에서 데이터를 가공하는 프로그램에선 결과 집합이 많을 수록 더 빨라지는 일은 없다.

        

        

     • ArraySize 조정에 의한 Fetch Call 감소 및 블록 I/O 감소 효과

       네트워크의 양에 따라ArraySize를 조절해야 한다. ArraySize를 조절한다고 전체 데이터의 양이 줄지는 않지만 Fetch Call 횟수를 조절할 수 있다. ArraySIze가 증가하면 네트워크 부하가 줄어들고 서버 프로세스가 읽어야할 블록 갯수도 감소한다.

       • 블록 I/O의 감소 이유

         한번에 읽는 범위가 클수록 블록과 블록을 겹쳐서 읽어야 하는 경우가 적어지기 때문에 결과적으로 블록 I/O가 감소한다.

       즉, ArraySize 와 (Fetch Count 및 블록 I/O)는 반비례 관계를 보인다.

       다음으로 프로그램 언어에서 Array 단위로 Fetch 기능을 활용하는 방법이다.

       • Oracle PL/SQL : 10g 부터는 자동으로 100개씩 Array Fetch 가 일어나지만 커서의 Open, Fetch, Close 가 내부적으로 이루어지는 Cursor FOR Loop 구문을 이용할 때 작동한다.

         xxxxxxxxxx
         for item in cursor 
         loop 
         ....
         end loop;
         ​
         

       • Java :setFetchSize 메소드를 이용해 조정한다. Java의 FetchSize기본값은 10이다.

         xxxxxxxxxx
         String sql ="selete custid, name from customer";
         PreparedStatement stmt = conn.prepareStatement(sql);
         stmt.setFetchSize(100); // Statement에서 조정
         ​
         ResultSet rs = stmt.executeQuery();
         //rs.setFetchSize(100);
         while(rs.next()){ // 최초 rs.next()호출 시 한번에 100개를 가져와서 Array버퍼에 캐싱한다. 이후에는 Call하지 않고 버퍼에서 읽는다. 101번째에 다시 100개를 가져온다(반복)
             int empno = re.getInt(1);
             String ename = rs.getString(2);
             System.out.println(empno+":"+ename);
             
         }
         rs.close();
         stmt.close();
         ​
         

          

   • 페이지 처리 활용

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

     웹 어플리케이션 환경에서는 데이터베이스와의 연결을 지속하지 않을 경우 커서를 계속 연 채로 결과 집합을 핸들링 할 수 없기 때문에 다음 페이지를 요청할 때마다 개별적으로 SQL문을 수행하는 페이지 처리 방식으로 구현해야 한다.

     페이지 처리로 구현 할 때에는 서버 단에서 페이지 처리를 완료하고 최종적으로 출력할 페이지의 레코드만 Fetch하도록 하는 것이 효율적이다.

     페이지 처리를 하지 않고 개발을 할 경우 부하를 발생시킨다.

     • 부하 요인

       1) 다량 발생하는 Fetch Call 부하

       2) 대량의 결과 집합을 클라이언트로 전송하면서 발생하는 네트워크 부하

       3) 대량의 데이터 블록을 읽으면서 발생하는 I/O부하

       4) AP 서버 및 웹 서버 리소스 사용량 증가

     이렇게 여러 부하를 일으키지만 사용자가 모든 데이터를 필요로 할 확률은 매우 낮다. 또한 이러한 방법은 시스템 전반의 불필요한 리소스 낭비를 야기한다.

     이를 해결하기 위한 페이지 처리 방법이다.

     • 해소 방안

       1) 페이지 단위로 화면에서 필요한 만큼만 페치

       2) 페이지 단위로 화면에서 필요한 만큼만 네트워크를 통해 결과 전송

       3) 인덱스와 부분범위처리 원리를 이용해 각 페이지에 필요한 최소량만 I/O

       4) 데이터를 소량씩 전송하므로 AP 웹 서버 리소스 사용 최소화

     결론적으로, 조회할 데이터가 일정량 이상이고 수행 빈도가 높다면 필수적으로 페이지 처리를 구현해야 한다.

   • 분산 쿼리

     부하 분산, 재해 복구, 보안 등 여러가지 목적으로 분산 환경의 데이터베이스를 구축한다.

     이때, 원격 조인이 자주 문제시 되는데, 원격에 있는 테이블과의 NL 방식으로의 조인은 쿼리 성능의 문제를 야기한다. 그래서 더 작고 필터링 되어 있는 결과 집합을 전송하는 것이 좋다. 또한 원격 서버가 쿼리를 처리하도록 driving_site 힌트를 지정하고 수행한다면 성능이 높아진다. 분산 쿼리의 성능을 높이는 핵심은 네트워크를 통한 데이터 전송량을 줄이는 데에 있다.

     • 이전 쿼리

       xxxxxxxxxx
       select channel_id, sum(quantity_sold) anuantity_cold
       from order a, sales@lk_sales b
       where a.order_date between :1 and :2
       and b.order_no = a.order_no
       group by channel_id
       ​
       

        

     • 이후 쿼리

       xxxxxxxxxx
       select /*+ driving_site(b)*/ channel_id, sum(quantity_sold) anuantity_cold
       from order a, sales@lk_sales b
       where a.order_date between :1 and :2
       and b.order_no = a.order_no
       group by channel_id
       

        

   • 사용자 정의 함수/프로시저의 특징과 성능

     프로그래밍 언어에서는 일반적으로 모듈화를 권장한다. 그러나 DBMS 내부에서 수행되는 사용자 정의 함수 및 프로시저는 모듈화를 한다면 성능 문제를 발생시킬 수 있다.

     • 사용자 정의 함수/프로시저의 특징

       사용자 정의 함수 및 프로시저는 내장 함수처럼 Native 코드로 완전 컴파일 된 형태가 아니어서 가상 머신 같은 별도의 실행 엔진을 통해 실행된다. 실행 될 때마다 컨텍스트 스위칭이 발생하며 이때문에 내장 함수를 호출할 때보다 성능이 떨어진다.

       게다가 메인 쿼리가 참조하는 사용자 정의 함수에 또 다른 쿼리문이 내장되어 있다면 수행 성능은 훨씬 저하된다. 이는 함수에 내장된 쿼리가 수행될 때마다 Execute Call, Fetch Call이 재귀적으로 일어나기 때문이다. (= Recursive Call이 반복적으로 일어남) Parse Call은 처음 수행 할 때 한번 일어나며 네트워크를 통해 DBMS 에 전달되는 User Call 에 비해 성능 부하는 미미하지만 그래도 주의 해야 한다.

        

     • 사용자 정의 함수/프로시저에 의한 성능 저하 해소 방안

       사용자 정의 함수는 소량의 데이터를 조회할 때, 혹은 부분범위처리가 가능한 상황에서 제한적으로 사용해야 한다. 또한 성능을 위해서 가급적 함수를 풀어 조인 또는 스칼라 서브 쿼리 형태로 변환하는 것을 권장한다.

       One SQL 로 구현하는 것도 해결 방법 중 하나이다. 그러나 구현내용이 아주 복잡하여 불가능하다면 함수 호출을 최소화 하도록 튜닝해야 한다.

       사용자 정의 함수를 절대 사용하지 말라는 것이 아니라 소량 호출과 내부에서 다량의 SQL을 수행할때에 사용자 정의 함수를 사용하는 것이 좋다는 것이다. 만약 이러한 로직을 외부 프로그램 언어로 구현한다면 다량의 SQL을 User Call 로써 수행해야 하기 때문에 사용자 정의 함수가 더 유용하다.

• 정리