database modeling 데이터베이스 진단의 핵심기법

데이터베이스 진단의 핵심기법

이춘식

1994년 10월 21일 성수대교 붕괴사건으로 32명이 죽고 17명이 다쳤다. 1995년 6월 25일에는 삼풍백화점이 무너져 500여명이 죽고 900여명이 다쳤다. 두 사건 모두 건축물에서 발생한 사건으로 전자는 관리 소홀로, 후자는 부실공사로 인해 발생했다. 사람이 죽어가고 눈물과 한숨을 촉발하는 건축물의 붕괴와 달리 정보시스템의 붕괴는 아주 심각하면 TV 뉴스에 나오는 정도이고, 대부분은 업무처리가 늦어지거나 경제적인 손해를 보는 정도로 무마된다. 그러나 인간이 불치병에 걸리듯 정보시스템도 불치병이 걸리는 경우가 있다. 구축된 정보시스템을 사용하지 못하고 다시 개발하는 경우가 바로 그렇다. 따라서 현재 잘 운영중인 데이터베이스라 해도 불치병에 걸리지 않도록 끊임없이 ‘진단’해야 하는 것이다.
요즘에도 TV 사극을 보면 임금님의 전속 의사인 어의가 높으신 중전마마를 진맥하는 장면이 종종 등장한다. 어의는 중전마마의 팔에 실을 연결해 그 떨림만으로 중전마마의 건강 상태나 병의 유무를 판단해 적절한 처방을 내린다. 옛 한의학 의사가 진맥 하나로 모든 병의 유무를 판단했다면 요즘은 피, 눈, 귀, 위, 심장 등 많은 신체 부위를 첨단 의학장비를 이용해 진단한다. 한의학의 우수성은 필자도 인정하지만 적어도 인간의 병을 진단하는 것만 놓고 보면 진맥 한 가지에 의존하는 전통 방식보다는, 예상되는 증상에 대해 체계적으로 진단하는 양의학의 방법이 훨씬 정확해 보인다.
필자가 데이터베이스를 진단하기 위해 업체를 방문해 보면, 어떤 경우에는 컨설턴트를 마치 데이터베이스에 관한 한 마이다스의 손처럼 알고 있는 경우가 종종 있다. 마치 손목을 진맥해 정확하게 병명을 찾아내는 것처럼 프로젝트에 대한 간단한 설명을 들으면 문제가 무엇이고 어떤 해결책이 있는지 이야기해 줄 수 있을 것으로 기대한다. 물론 전문성이 높아지는 만큼 감각이 예민해지는 것은 당연하다. 그러나 이것은 정상적인 방법이 아니다. 구체적인 문제에 대해서 정확하게 진단하는 것은 컨설턴트에게도 매우 어렵고 민감한 작업이다.
개발중이거나 운영중인 데이터베이스의 상태를 정확히 진단하기 위해서는 건강진단 프로세스와 유사한 진단 프로세스가 필요하다. 프로세스 내의 각 작업들이 목표한 WHAT을 정의하고 이를 달성할 수 있는 적절한 HOW를 실행할 수 있도록 전체적으로 조율하는 것이다. 이를 위해서는 데이터 아키텍처에 대한 확인부터 정리, 가이드를 도출할 때까지 일련의 잘 조직된 프로세스가 있어야 한다. 시스템 개발(SI)의 영역에서는 아키텍처와 일관성 등이 더 중요한 의미를 갖는다면 시스템 운영(SM)의 영역에서는 모니터링과 로그 파일에 대한 분석이 훨씬 중요하다. <그림 1>은 일반적인 데이터베이스 진단 프로세스이다.

<그림1> 데이터베이스 진단 프로세스

데이터베이스를 진단할 때는 일반적으로 두 가지 관점이 사용된다. 사람 몸을 검사해 심각한 병이 있는지 진찰하는 것처럼 데이터베이스에 치명적인 결함(이를 리스크(Risk)라고 한다)이 있는 지를 점검하는 관점이 하나이고, 다른 하나는 사람의 건강 수준을 파악해 체질 개선 대책을 세우는 것처럼 데이터베이스 체질 개선을 위해 진단하는 것이다. 첫 번째 관점은 데이터 아키텍처, 오브젝트 관리, 로그 파일, SQL 진단 등의 방법을 이용하고 두 번째 관점은 데이터베이스 파라미터 진단, 데이터베이스 모니터링 등을 통해 수행한다. 이를 정리하면 다음과 같은 10개 항목이다.

⑴ 데이터 아키텍처 진단
⑵ 데이터베이스 파라미터 진단
⑶ 로그 파일 진단
⑷ 데이터베이스 모니터링
⑸ 데이터모델 역공학(리버스 엔지니어링, reverse engineering)
⑹ 데이터 모델과 데이터베이스 일관성, 정합성 체크
⑺ 오브젝트 관리(테이블, 테이블 스페이스 등)에 대한 내용 진단
⑻ SQL 진단
⑼ 정리와 가이드
⑽ 지식화

데이터 아키텍처 진단

먼저 데이터 아키텍처에 대한 진단은 데이터 모델을 보고 전체 데이터베이스 스키마의 구성을 이해한 후 대용량 테이블의 구성과 PK (Primary Key)/FK(Foreign Key)의 구성, 관계의 적절성에 대해 검토하는 것이다. 하드웨어/소프트웨어 구성도를 기초로 데이터베이스 서버가 적절하게 구성되어 있는지 점검하고 분산환경 구성이나 고가용성 구성(Active-Active, Active-Standby)이 적절하게 됐는지를 확인한다. 공통코드, 기준정보, 메타데이터에 대한 관리체계와 처리방법이 안정성을 확보하고 있는지, 처리성능은 적절한 지에 대한 판단도 중요하다.
이와 같은 진단을 통해 엔티티 타입이 통합돼야 하는데 분리돼 있다거나 엔티티 타입 간의 적절한 관계가 누락된 데이터 모델이 아닌지 알 수 있고, 하드웨어 구성도를 보고 데이터베이스 분산전략에 의해 데이터베이스 서버의 배치가 올바른지, 보안을 고려해 네트워크의 데이터베이스 서버 환경이 구축됐는 지를 판단한다. 또한 소프트웨어 구성을 보면 DBMS가 다른 소프트웨어와 안정성과 성능을 보장할 수 있는 구조로 설계됐는지 알 수 있다. 특히 공통 코드, 기준 정보와 같은 데이터가 분산 환경 데이터베이스에서 적절하게 성능을 보장할 수 있는 구조로 되어 있는지 검증하는 것도 중요하다.

데이터베이스 파라미터 진단

데이터베이스 파라미터는 기본 환경에 대한 파라미터와 메모리에 대한 파라미터로 구분된다. 특히 메모리 관련 파라미터는 전체 DB 성능에 영향을 주는 경우가 많으므로 집중적으로 진단할 필요가 있다. 오라클 데이터베이스의 파라미터는 오라클이 기동(startup)될 때 참조하는 파라미터 파일에도 정보가 있지만 인스턴스가 서비스하는 도중 파라미터 정보가 변경될 수 있기 때문에 SHOW PARAMETER나 SELECT * FROM V$PARAMETER 등의 SQL을 실행해 조회하는 것이 좋다.
<표 1>은 오라클 9i 데이터베이스의 주요 파라미터이다. 이 가운데 DB_BLOCK_SIZE, DB_CACHE_SIZE, HASH_JOIN_EN ABLED, PGA_AGGREGATE_TARGET, SESSIONS, SHARED_ POOL_SIZE 등은 전체 대상 시스템의 크기에 따라 적절하게 산출돼 있는지 검증해야 한다.

<표1>오라클9i 데이터베이스의 주요 파라미터

 

로그 파일 확인

오라클 데이터베이스는 인스턴스가 기동되거나 종료, 혹은 에러가 발생하면 이와 관련된 특이 사항을 로그 파일로 기록한다. 일반적으로 $ORACLE_HOME\..\admin\\bdump\alert_.log에 저장되며 진단자는 이를 분석해 데이터베이스의 건강 상태를 체크할 수 있다. 주로 ‘ORA-’로 조회(특히 ORA-600 에러에 대한 내용 확인)해 적절한 조치를 취할 수 있고 교착상태(deadlock)가 유발되고 있는지, 체크 포인트 발생주기가 너무 빠른 것은 아닌지 등을 점검한다. <화면 1>은 로그 파일에서 발견된 교착상태의 예이다.
 

Mon Aug 19:46:02 2003
ORA-OOOO60: Deadlock detected. More info in file
/oracle/app/oracle/admin/ITONL/udump/itonl_ora_22300.trc.
Mon Aug 11 19:46:05 2003
ORA-OOOO60: Deadlock detected. More info in file
oracle/app/oracle/admin/ITONL/udump/itonl_ora_22306.trc
Mon aug 11 16:46:08 2003

<화면1> 로그 파일에서 발겨된 교착상태


<화면2> 스탯팩을 실행한 결과
 

SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION,
DAT_SCALE
FROM DBA_TAB_COLUMNS
WHERE OWNER LIKE 'UN%' ← 테이블 OWNER 지정
AND COLUMN_NAME IN (
   SELECT COLUMN_NAME
   FROM (
      SELECT DISTINCT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE
      FROM DBA_TAB_COLUMNS
      WHERER OWNER LIKE 'UN%' ) ← 테이블 OWNER 지정
   GROUP BY COLUMN_NAME
   HAVING COUNT (*)>1)
ORDER BY 3,4,5

<화면3> 오라클에서 컬럼 타입과 길이 일관성 검증하는 SQL
 

데이터베이스 모니터링

데이터베이스 모니터링은 두 가지로 나누어 수행한다. 하나는 데이터베이스가 탑재돼 있는 서버의 CPU와 메모리, 디스크 I/O에 대해 모니터링하는 것이고, 두 번째는 데이터베이스 자체 설정을 모니터링하는 것이다. 데이터베이스 서버 모니터링의 경우 윈도우 운영체제에서는 「제어판｜관리도구｜성능」 메뉴에서 확인할 수 있으며, 유닉스에서는 커맨드라인에서 vmstat 5, iostat 5, prstat 5 등을 이용해 모니터링 할 수 있다. 유닉스에서도 시스템 모니터링 툴을 제공한다.
데이터베이스 자체적인 모니터링 툴도 있다. 오라클은 버퍼 캐시의 히트율, 라이브러리 캐시의 히트율, 세션의 수, 공유 풀의 사용, 물리적인 I/O 발생빈도 등을 자체적으로 모니터링 할 수 있으며, 데이터베이스 딕셔너리 테이블에서 제공하는 V$ 유형의 테이블들은 데이터베이스가 기동된 이후에 정보가 모두 합쳐저 통계 데이터가 산출되므로 특정 시점(예들 들어 사용자가 많은 오전 10시~11시)에서 데이터베이스의 상태를 진단하고 싶다면 스탯팩(statspack)이라는 툴을 시간에 따라 기동하는 것도 방법이다.
데이터베이스 모니터링은 CPU, 메모리가 부족하지는 않은지 확인하고 물리적인 I/O가 특정 디스크나 파일에 집중되는지 진단하는 것으로 필요하다면 이를 분산 배치하기 위해 수행한다. 파라미터에 설정된 여러 데이터베이스 메모리에 대한 값의 적정성을 판단해 늘이거나 줄일 필요도 있다.

데이터 모델 역공학

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

과거에는 시스템을 개발하거나 운영할 때 구축된 데이터베이스가 모든 정보를 가졌기 때문에 데이터 모델은 개발을 위한 일시적인 분석/설계 도구에 불과했다. 그러나 최근에는 데이터베이스에 대한 모든 정보를 데이터 모델을 이용해 이해하고 수정하므로 그 의미가 더욱 중요해졌다.
데이터 모델 관리에는 데이터베이스에 생성된 스키마 정보를 읽어 데이터 모델을 만들어 내는 역공학(리버스 엔지니어링)을 많이 활용한다. 역공학을 적용해 데이터 모델을 만들면, 시스템 개발시 관리하는 데이터 모델과 일치하는지 비교해 볼 수 있으며, 오랜 기간 운영해 많이 변형된 데이터베이스라도 이를 기초로 데이터 모델을 만들 수 있다.

데이터 모델과 DB 일관성, 정합성 체크

데이터 모델과 데이터베이스는 반드시 일치해야 하지만 때때로 일관성이 깨지는 경우가 있다. 특히 동일한 컬럼의 데이터 타입과 길이가 달라지는 경우나 동일한 속성명에 대해 물리적인 속성명을 다르게 기술하는 경우 심각한 문제가 발생할 수 있다. 데이터 모델과 데이터베이스 스키마가 일치하지 않으면 정확한 데이터베이스의 모습을 알 수 없기 때문에 반드시 양자가 일치하는지 일관성 여부를 검증해야 한다. 또한 동일한 컬럼임에도 불구하고 데이터 타입과 길이가 다른 경우를 찾아 수정하면 응용 프로그램의 처리 로직 오류나 성능저하 현상을 예방할 수 있다. <화면 3>은 오라클에서 컬럼 타입과 길이의 일관성을 검증하는 SQL이다.

오브젝트 관리 내용 확인

데이터베이스에는 테이블 이외에도 테이블스페이스, 데이터 파일, 인덱스, 뷰 등이 설계된다. 데이터베이스 오브젝트 관리에 대한 진단에서는 대량의 데이터 발생시 처리 방법의 적절성이나 물리적인 I/O 분산을 고려했는지, 트랜잭션의 성격에 따라 적절하게 인덱스 등을 고려했는지를 점검한다. 또한 데이터베이스에서 발생된 트랜잭션의 양에 따라 얼마 만큼의 데이터가 누적되는지 데이터베이스 용량을 산출하고, 그 결과에 따라 특정 테이블이나 인덱스에 트랜잭션이 집중되지 않는지를 판단할 수 있다. 만약 집중되는 현상이 발생했다면 테이블 분산, 테이블 통합, 파티셔닝 등의 조치를 취해야 한다. <화면 4>는 이와 같은 과정을 통해 용량 산정을 한 사례다.

SQL 진단

보통 데이터베이스 문제는 SQL의 성능 저하로 연결되므로 SQL에 대한 진단은 더 체계적으로 진행해야 한다. 보통 V$SQL, V$SQL TEXT 등의 오라클 딕셔너리 테이블을 이용하는데 특정 시점에 발생된 SQL에 대한 성능을 검증하려면 앞서 설명한 스탯팩을 이용해야 한다. 문제가 되는 SQL이 발견될 경우 실행계획(execution plan)을 조회해 다른 실행 계획을 이용하거나 인덱스를 추가하고, 아직 개발하는 시점이라면 데이터베이스 스키마를 적절하게 변경해야 한다. 스탯팩 이외에도 프리사이즈, DB WINE과 같은 툴을 활용해서 악성 SQL을 진단할 수 있다. <화면 5>는 DB WINE 툴을 이용해 자원을 많이 사용하는 SQL을 검출한 화면이다.

정리와 가이드

데이터베이스를 진단하면 각 항목마다 적절하게 되어 있는지 정확한 상태를 알 수 있다. 이 때 진단자가 조사한 내용을 구두로만 전달하거나 문제가 된 내용을 곧바로 수정하고 근거를 남기지 않으면 또 다시 동일한 문제가 발생할 가능성이 높다. 따라서 진단 결과를 반드시 현상(AS-IS)과 개선방향(TO-BE) 형태의 문서로 남기고 DBA, 개발자, 시스템 담당자 등 관련 담당자들과 충분히 의사소통해야 한다.

<화면4> 데이터베이스 용량 산정



<화면5> DB WINE 툴을 이용해 자원을 많이 사용한 SQL을 검출한 화면
 

지식화

지식화란 이전의 진단 사례를 이용해 다른 시스템에서 나타날 수 있는 문제를 미리 예방하거나 진단에 참여하지 않은 다른 사람에게 동일한 진단 결과가 전달될 수 있도록 정리하는 것을 뜻한다. 예를 들어 SQL 문장에서 FK에 대한 인덱스를 설정하지 않아 성능이 저하되는 경우가 많이 나타났다면 그러한 유형을 정리해 다른 프로젝트나 시스템에서 나타나지 않도록 참고할 수 있어야 한다. 지식화를 위해서는 진단된 내용을 데이터베이스 테이블에 입력하는 것이 가장 바람직하며, 비슷한 유형의 진단 내용을 문서로 정리하?? 진단 프로세스와 각 항목에 대한 내용을 간략하게 살펴봤다. 점검 항목별로 어떤 사항들을 살펴봐야 하는지 구체적인 방법과 사례까지 설명하진 못했지만 여기서 꼭 기억해야 할 사실은 데이터베이스도 인간과 마찬가지로 건강검진이 필요하다는 사실이다. 진맥을 하듯 포괄적인 경험에 의지할 것인지, 문제가 될 만한 요소에 대해 체계적으로 접근할지 선택해야 한다면 당연히 후자가 바람직하다.

제공 : DB포탈사이트 DBguide.net

[출처] http://www.dbguide.net/knowledge.db?cmd=view&boardUid=125545&boardConfigUid=19&boardStep=&categoryUid=205