4.3 데이터베이스 설계와 튜닝

제조실행시스템(MES)은 제조 현장의 생산 프로세스를 실시간으로 모니터링하고 제어하는 소프트웨어 기반 시스템입니다. 생산 계획, 작업 지시, 자재 관리, 품질 관리, 생산 실적 분석 등 다양한 기능을 제공하여 제조 효율성을 높이고 생산 비용을 절감하는 데 도움을 줍니다.

제조실행시스템

4.3.1 설계 수행절차

데이터베이스의 설계는 분석 단계에서 정의된 데이터 모델링 결과를 선택된 DBMS의 특징에 부합하도록 실제 물리적 스키마를 생성하고 이를 데이터 액세스 성능을 고려하여 튜닝된 스키마로 전환하는 과정이다. 
 
데이터베이스를 설계한다는 것은 적용되는 방법론에 따라 일부 절차가 생략될 수는 있지만 분석 단계의 ERD가 테이블로 전환되는 것이다. 많은 분석/ 설계자들이 논리모델링, 물리모델링, 데이블 전환 과정에서 엔티티의 개수가 눈에 보일 정도로 차이 나는 것 때문에 어려움을 겪는다.

 
각 단계를 진행하면서 추가되고 통합되고 사라지는 엔티티가 있을 수 있다. 차이가 발생하는 것이 문제가 될 수는 없지만 각 단계별 충분한 검토 및 분석이 이루어져야 한다. 관계형 데이터베이스의 특징과 장점을 살림으로써 나중에 개발자의 업무량을 1/10로 줄이면서도 10배 이상의 수행 속도를 향상할 수 있는 마법이 바로 ERD에서 테이블로 전환되는 단계라는 것을 명심해야 한다. 
 
물리적 설계는 분석 단계에서 작성된 논리적 데이터 모델을 관계형 데이터베이스로 전환하는 기본설계와 이를 데이터 액세스 성능 등을 고려하여 튜닝된 스키마로 전환하는 상세설계로 이루어진다.
 
분석/ 설계자는 DBMS의 기본적인 특징을 알아야 하고 성능 관점의 구체적인 지식을 습득해야 한다. 개발자보다 SQL 작성 능력이 뛰어나야 하고 PLAN을 분석해 효율적인 구조의 모델을 선택할 수 있어야 한다. 

---

4.3.2 관계형 DB 튜닝

튜닝이란 DBMS에 대한 정확한 이해를 바탕으로 객체(Object)의 최적 관리와 자원의 효율적인 활용 및 성능 향상을 도모하는 시스템 사용기법을 말한다. 튜닝 필요성이 대두되는 시점은 기능 개발이 대부분 완료되고 성능테스트를 하는 프로젝트 막바지일 경우가 많다.
 
그러나 실제적인 원인은 시스템 분석 단계인 모델링에서부터 개발 단계에 이르기까지 지속적으로 나타난다. 성능 향상을 위해서는 프로젝트 각 단계별로 고려사항을 염두에 두어야 한다.
 

계획(착수 단계)

시스템 확장을 감안한 H/W 및 S/W 아키텍처가 중요하다. 시간이 지남에 따라 데이터 건수가 증가하고 업무의 확장이 지속적으로 이루어진다고 가정해야 한다.
 

분석/설계 단계

성능을 감안한 데이터 모델링 및 데이터베이스 설계가 이루어져야 한다. 성능에 영향을 미치는 중요한 단계이지만 실천하기는 어려운 단계이다. 업무에서 요구하는 데이터를 분석해서 엔티티를 명확하게 정의하는 것이 정규화인데 정규화가 중요하다고 모든 분석/설계자들이 이해는 하지만 막상 성능 측면에서 비정규화를 강조하는 것을 많이 본다.
 
그러나 성능보다는 데이터 무결성이 우선한다. 비정규화가 많이 반영된 시스템은 데이터 무결성을 해결하기 위해 더 많은 프로그램 로직이 필요하고 그에 대한 오버헤드가 더 증가하는 경우가 많다. RDBMS를 이용한다면 무결성 규칙을 반드시 적용해야 한다.
 
참조 무결성 규칙은 설계 단계에서 프로그램 로직이나 제약조건을 이용하여 처리하는 방법이다. 인덱스는 설계 단계에서 명확하게 나타나지 않고 프로그램 개발 후 충분한 Access Path 검토 및 애플리케이션 튜닝 작업 후 최종 인덱스가 확정될 수 있다.
 
설계 단계에서는 확실한 인덱스와 Primary Key 정도만 기술한다. 마지막으로 데이터 모델과 UI(User Interface) 간의 적합성 검증이 필요하다. UI를 충족할 수 없는 데이터모델은 무의미하다.
 

개발 단계

데이터베이스를 사용하는 애플리케이션의 제작에 있어서 아직까지는 JAVA, C/C++, Cobol, Fortran, Ada, Pascal과 같은 3GL 기반의 프로그램을 주로 사용하는 것이 일반적이다. 특히 오라클에서 사용하는 SQL 및 PL/SQL을 C언어와 병행하여 사용할 수 있도록 하는 전처리 컴파일러를 ProC/C++이라 부른다.
 
그러나 관계형 데이터베이스의 모든 데이터 처리는 SQL에 의해서만 가능하다. 따라서 SQL을 처리하는 Access Path가 시스템 수행 속도에 미치는 영향은 거의 절대적이다. 인덱스는 RDBMS를 구성하는 기본적인 객체 중의 하나로서 실제 데이터를 가지고 있는 것은 아니고 데이터를 빠르게 액세스 하기 위한 객체로서 성능에 절대적인 영향을 미친다. 
 
대부분 프로그램 기능 중 약 80%가 데이터 조회인데 애플리케이션 수정 없이 적절한 인덱스의 지정만으로도 약 60% 이상의 액세스 효율성을 향상할 수 있다. 프로젝트에 참여하는 단 몇 명만이라도 그 능력 배양에 힘써야 하는 이유이다. 

<인덱스 선정 시 고려사항>
· 칼럼의 분포도는 10~15%를 넘지 않아야 한다.
· 자주 조합되어 사용되는 칼럼은 결합 인덱스 생성을 고려한다.
· 수정이 빈번히 일어나지 않은 칼럼을 인덱스로 사용한다.
· Access Path에 의해 인덱스를 결정한다.
· 지나치게 많은 인덱스는 오버헤드를 발생시킨다.
· 칼럼의 조합이나 순서나 다른 유사한 인덱스에 의해 Access Path가 의도하지 않은 방향으로 선택되는 경우가 있다.

 

다음으로 SQL 최적화와 함께 옵티마이저(Optimizer)의 이해와 선택이 중요하다. 프로젝트의 특성, 개발자의 SQL 구사능력, 운영능력에 따라 선택할 수 있고, 경우에 따라서는 혼용을 할 수도 있다. 일반적인 DBMS에서는 2개의 옵티마이저가 제공되는데 규칙기준(RBO: Rule Base Optimizer)과 비용기준(CBO: Cost Base Optimizer)이다.

 

Rule은 SQL의 수행이 정해진 규칙에 따라 실행되는 경우로서 SQL의 구사능력이 비교적 우수한 개발자가 다수 존재할 때 유리하다. 물론 주기적인 튜닝은 해야 하지만 데이터가 증가되어도 규칙에 의해 작성된 SQL의 데이터에 의해 옵티마이저가 실행계획을 수행한다.

 

즉, 통계 데이터에 의해서 SQL이 수행되는 경우로서 최악의 결과는 나오지 않을 수 있지만 통계 데이터의 주기적인 갱신 작업이 반드시 필요하다. 예를 들어 100건의 데이터가 있을 때 작성된 통계 데이터가 그 후 데이터가 증가하여 100만 건이 되었는데도 통계 작업(Analyze수행)을 수행하지 않으면 옵티마이저는 100건을 기억하고 있을 뿐이다.

 

따라서 옵티마이저는 인덱스 스캔보다는 전체 범위를 스캔하는 경우가 발생하여 성능 문제가 발생할 수 있다. 어떤 선택을 하느냐에 따라 프로그램 작성 방법이 달라진다. 선행 테이블(Driving Table)의 위치가 변경되어야 한다.

 

Rule Base일 경우 SQL 문의 FROM 절 맨 뒤에 선행 테이블이 위치하고 Cost Base일 경우 반대의 경우가 된다. 결국 어떤 옵티마이저를 선택할지는 최초 프로그램이 개발되기 전에 많은 고민과 향후 운영방안을 고려하여 선택해야 한다.
 

테스트 및 운영 단계

실제 운영환경에 견줄 만한 대량의 테스트 데이터가 필요하다. 완벽하게 튜닝을 했어도 실제 운영환경과 동일하게 시스템을 구성하고 테스트를 수행해야 한다. 운영 단계에서도 주기적인 튜닝 작업이 필요하고 시스템 성능에 대한 지속적인 감시가 필요하다. 모니터링을 통하여 문제 되는 SQL을 보여주는 상용 Tool의 활용도 도움이 된다.

---

4.3.3 정리

애플리케이션의 성능 향상을 위해서는 각 단계별 튜닝 시 고려사항을 이해하고 테이블 구조 재정의를 통한 성능 향상과 함께 프로그램 개발 시 개발자들이 용이하게 코딩을 할 수 있는 방법을 이해해야 한다. 실질적인 애플리케이션의 튜닝 담당자는 개발자가 되어야 한다.
 
DBA가 도와줄 수는 있지만 자기가 코딩한 SQL 문은 수시로 실행계획(Execution Plan)을 통하여 확인하고 최적의 SQL을 지향할 수 있어야 한다.