2009년 12월 16일 수요일

퍼포먼스 향상의 최단 코스를 알아보자

출처 : KACHISORI

 

본 자료는 일본

@IT(http://www.atmarkit.co.jp/fdb/index/index-db.html#tuneorasql) 에 株式会社アゲハ加藤 猛씨가 연재한 Oracle SQLチューニング講座를 번역 재구성한 것입니다.

 

본연재에서는 Oracle 데이타베이스의 퍼포먼스·튜닝중에서도 특히 SQL의 튜닝에 주목하고 실천 사례를 해설한다. 독자는 Oracle 데이타베이스의 아키텍쳐를 이해하고 있으며 운용 관리의 실무 경험을 가지고 있으시면 보다 좋겠네요. 대상으로 하는 버젼은 Oracle9i의 기능을 기본으로 하지만, Oracle 10g로 유효한 정보도 수시 소개해 나간다.

 

■ 퍼포먼스·튜닝 개요

 

「퍼포먼스·튜닝」이라고 하는 말을 듣고 머리를 움켜 쥐는 데이타베이스 관리자(이하, DBA)나 개발자는 많이 계시지 않을까란 생각이 듭니다. 「어려울 것 같고,  깊은 지식은 없다」혹은 「힘들고 어려운 작업」이라고 하는 이미지를 가지고 있는 분도 있다고 생각합니다.

 

그러나 한편에서는 「특별히 퍼포먼스·튜닝을 의식하고 있지 않다」라고 하는 의견도 자주 들립니다. 이것은 최근의 눈부신 하드웨어 기술의 진보에 의한 것이 크다고 말할 수 있습니다. 오늘의 하드웨어의 성능은 고성능인 CPU, 대량의 캐쉬를 쌓은 대용량 스토리지나 대량의 메모리 등, 옛날의 슈퍼 컴퓨터와 같은 수준이 되어 있습니다. 그 강대한 머신 파워에 의해서 이전 정도 처리 효율을 신경쓰지 않아도 적당한 퍼포먼스를 얻을 수 있게 되었기 때문에 일까 ?? 효율이 나쁜 처리가 존재하고 있는 것을 깨닫지 않고 운용하고 있는 시스템도 많이 있는 듯 합니다. 실제 퍼포먼스가 문제가 된 시스템을 조사해 보면 효율의 나쁜 처리가 무수히 발견되는 케이스도 적지 않습니다.

 

물론 다소 처리 효율이 나쁘더라도 현행의 퍼포먼스 요건을 충족시키고 향후에 부하가 증가하지 않는 시스템이면 문제 없을 것입니다. 그러나 대부분의 시스템은 해마다 데이터량 유저수가 증가해 가는 경향에 있습니다. 예를 들면 인터넷·쇼핑을 등등.. 이러한 시스템에서는 「8초 룰」이나 「3초 룰」이라는 리스폰스 시간이 항상 요구되고 있습니다.

 

효율화되지 않은 처리가 많이 잠재되어 시스템의 경우 지금은 문제가 없더라도 취급상품, 고객수가 증가에 따라 퍼포먼스 요건을 채울 수 없게 될 가능성이 매우 높아집니다. 리스폰스 시간의 지연은 많은 유저의 불만으로 연결되어  최악의 경우 소중한 고객을 잃게 될 수 있습니다. 이러한 시스템에서는 퍼포먼스의 좋고 나쁨이 매우 중요합니다(그림 1).。

 

그림 1　데이타베이스의 퍼포먼스는 서비스에 큰 영향을 준다

　

그러므로 본연재에서는 대표적인 RDBMS인 Oracle 데이타베이스를 사용해 퍼포먼스 개선을 가장 기대할 수 있는 SQL 튜닝 방법으로 대해서 전제의 지식이나 취득해야 할 정보와 그 해설과 함께 조우할 가능성의 높은 케이스 스터디에 의한 분석, 대응 순서를 소개해 갈 것입니다.

 

■ 퍼포먼스·튜닝의 목적이란?

 

구체적인 SQL 튜닝의 이야기를 시작하기 전에 무엇을 목적으로 퍼포먼스·튜닝을 실시할 것인가를 생각해 보겠습니다.

 

데이타베이스의 시스템 환경의 상당수는 데이터의 변경이나 데이터량의 증가, 이용자수의 증가, 새로운 기능의 추가등에 의해서, 데이타베이스 상태나 사용 상황은 항상 같다라고 할수는 없습니다.그 때문에 이전에는 문제없던 처리가 유저수나 데이터량의 증가에 따라 요구되는 퍼포먼스를 전혀 만족하지 못하는 경우가 자주 있습니다.

그리고 이러한 문제는 하드웨어의 증강, 예를 들면 고성능의 CPU로 교체나 CPU수, 메모리를 늘리는 것만으로는 개선할 수 없는 경우가 많습니다. 예를 들면 퍼포먼스의 열화 원인이 매우 큰 테이블에 액세스로 색인이 사용되지 않고 여분의 액세스가 대량으로 발생하고 있는 경우나 많은 이용자가 같은 데이터에 액세스하여 경합이 발생하고 있는 경우등입니다.

 

이런것들은 비싼 하드웨어의 증강하더라도 완전히 문제를 해결하지 못하며 쓸데 없는 비용만이 발생한 것이  되어 버립니다. 실제로 퍼포먼스가 악화된 상황에서도 예산등의 문제로 인해 용이하게 하드웨어를 증강할 수 있는 환경은 그리 많지 않습니다.우선 현재의 시스템 구성으로 퍼포먼스 튜닝을 실시해야 하는 경우가 대부분이지요.

 

이러한 상황하에서는 현상을 분석하여 문제점을 정리하고 효과가 가장 큰 퍼포먼스·튜닝 수법을 선택해 실시하는 것이 최선의 수단이 될수 있습니다. 적절한 퍼포먼스·튜닝을 실시할 수 있으면 새로운 하드웨어를 증강할 필요도 없고 또한 최소한의 증강으로 퍼포먼스의 문제에 대처하는 것이 가능하게 됩니다(그림 2). 즉 퍼포먼스·튜닝은 새로운 투자를 하지 않고 문제를 해결할 수 있는 투자대 효과의 매우 높은 수단임을 의미하고 있습니다.

 

그러므로 최종적인 데이타베이스의 퍼포먼스·튜닝의 목적은 「한정된 시스템·자원 안)에서, 최대한의 퍼포먼스 효과를 내는 것」입니다.

 

그림 2　한정된 시스템·자원 안에서 최대한의 퍼포먼스를 낸다

 

 

■ 퍼포먼스·튜닝에 있어서의 SQL 튜닝

 

그렇다면 실제로 퍼포먼스·튜닝으로의 SQL 튜닝의 자리 매김을 보겠습니다.

 

논리 설계나 물리 설계의 변경, Oracle 인스턴스의 초기화 파라미터의 변경 등 Oracle 데이타베이스의 퍼포먼스·튜닝 방법 중에서 SQL튜닝은 가장 효과를 기대할 수 있는 수단입니다.

경우에 따라서는 색인의 사용의 유무 등 SQL문을 최적화하는 것으로 수배또는 수백배의 퍼포먼스 향상이가능한.... , 그 다른 수단에서는 실현 불가능한 결과를 가져올 수도 있습니다.

SQL 튜닝의 처리는 Oracle 인스턴스나 하드웨어·자원 등에 밀접하게 관계되어 있고 퍼포먼스·튜닝의 관점에서 보면 이러한 관련합에 있어는 그림 3과 같은 관계로 나타낼 수 있습니다. 이 그림 중에서, SQL 튜닝은 가장 하위에 위치하고 있습니다. 하위의 계층은 보다 상위의 계층에 대해서 퍼포먼스의 영향을 주는 것을 의미합니다.

 

그림 3　퍼포먼스·튜닝 항목의 상호 영향도

  • OS(하드웨어) 자원의 튜닝CPU나 메모리, 디스크, 네트워크 등, OS자원의 최적화
    　

  • Oracle 인스턴스·튜닝
    SGA(시스템 글로벌 영역), PGA(프로그램 글로벌 영역), 각종 백그라운드·프로세스 등 초기화 파라미터의 조정

  • 어플리케이션의 튜닝프로그램 논리나 어플리케이션·서버의 설정등의 어플리케이션의 최적화
    　

  • 오브젝트의 튜닝테이블이나 색인등 데이타베이스·오브젝트의 설정이나 설계의 변경 등
    　

  • SQL 튜닝
    SQL문의 튜닝

예를 들면 이미 가동중인 데이타베이스·시스템에서 퍼포먼스가 나오지 않는 경우, 상층에 해당되는 Oracle 데이타베이스의 초기화 파라미터를 조정해도 최적인 값을 찾아내는 것은 어려울 것입니다.

만일 비효율적인 SQL이 쓸데 없이 많이 캐쉬에 있는 경우등을 생각해 보면 아무리 캐쉬 관련의 초기화 파라미터를 크게 하더라도 캐쉬의 효과는 적고 메모리·자원만 낭비하게 되므로 적절한 대응이라고는 할 수 없습니다. 하위에 해당되는 SQL에 관해서 최적인 튜닝을 사전에 실시하고, 거기에 맞추어 초기화 파라미터를 조정하는 것이 퍼포먼스를 향상시키는 방법으로서 바람직합니다. 즉, SQL 튜닝을 실시하는 것이 그 외 모든 퍼포먼스·튜닝의 기초가 됩니다.

 

 

■ SQL 튜닝의 순서

 

SQL의 튜닝을 실시하려면  물론 튜닝 대상의 SQL을 정해야 합니다. 그러나 실제의 DBA에게 오는 문의내용은 「 어플리케이션등의 처리가 늦다」등의 구체적으로 무엇이 늦은 것인지가 판명되어 있지 않고 SQL의 부분이 늦은 것인지 다른 부분이 늦은것인지 모르겠다란 것이 적지 않습니다. 그것이 SQL 튜닝을 실시해야하는 것에 해당되고 실제로 실시해야 할 순서의 개략이 그림 4가 됩니다.

 

그림 4　SQL 튜닝을 실시할 때까지의 순서의 개획전략

 

순서1

먼저 퍼포먼스의 문제한 유저로부터 "무슨 처리를 실시하고 있는 것인가" "처리에 어느 정도 시간이 걸려 있는 것인가" 등의 상황 보고와 "시스템 형태나 어플리케이션의 종류" 등의 환경을 확인합니다.

 

순서2

다음은 CPU 부하나 과도의 스왑 처리의 발생, 네트워크 부하등의 시스템·자원에 문제가 없는지, 어플리케이션에 문제없나 등을 확인해 문제점을 분리합니다. 이것들이 system resource 전체에 문제가 없는 경우나 특정의 Oracle 프로세스가 system resource를 사용하고 있는 것이 명확한 경우등에서는 SQL이나 인스턴스라고 하는 Oracle 측에 문제가 있다고 판단할 수 있습니다.

 

순서3

Oracle측의 문제가 SQL에 있었을 경우 반드시 SQL 튜닝의 목표를 결정합니다. SQL 튜닝은 목표를 정하지 않으면 끝이 없는 작업이 되어 버리기 때문에 "응답 시간이 몇초 이하" 라고 하는 명확한 골을 결정해 두어야 합니다. 또 SQL 튜닝의 작업에 걸릴 시간은 스케줄등에 의해 한정되어 있으므로 튜닝을 실시해야 할 SQL의 선택, 예를 들면 CPU에 부하를 걸리고 있는 SQL, 물리 액세스량이 많은 SQL등의  작업의 우선 순위부도 아울러 결정해 둡니다.

 

순서4

그리고 실제의 처리간 SQL의 상세 정보의 취득을 실시합니다. 또 먼저 설명한 것처럼 SQL의 튜닝은 Oracle 인스턴스나 OS자원의 상황에도 관계되므로 Oracle의 가동 상황이나, 데이타베이스·서버의 시스템·자원 정보도 취득합니다.

 

순서5～7

그 후, 정보를 분석, 적절한 SQL 튜닝을 실시해, 목표에 이를 때까지 정보 취득과 튜닝을 반복합니다.

 

◇

 

이번회에서는 SQL 튜닝의 목적과 의미 튜닝을 실시할 때까지의 순서의 개략을 소개했습니다.다음회 에서는 SQL 튜닝을 실시하는데 필요한 기초지식으로서 데이터·액세스 방법, 옵티마이져에 관해서 설명합니다.

작성자: 시간:
라벨:

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)