특정쿼리의 결과 레코드를 하나의 문자열, 특히 CSV(Comma Separaed Value) 형태로 바꾸는 방법은 여러가지가 있는데 이를 정리해 보고자 한다.
Rowset sting concatenation : Which method is best ?
scalar UDF 방법
이 함수의 실제 사용은
temp 테이블 사용 방법
'SQL Server'에 해당되는 글 7건
- 2008/06/21 [MS Sql] 여러 레코드를 하나의 문자열로 합치기
- 2008/06/04 MySQL의 MYISAM과 InnoDB 타입의 성능비교
- 2007/08/25 MS-SQL Server의 성능 향상을 위한 몇 가지 제안
- 2007/08/06 VLDB를 위한 효과적인 아키텍처 구현 방법
- 2007/05/02 MS SQL Server Tips
- 2007/04/25 MS SQLServer에서 인덱스 최대길이
- 2007/03/27 SQL 7.0에서 네트웍공유에 백업하는 방법
MS SQL Server에 40만건의 데이타를 갖고 있는 테이블을 DTS를 이용해서 MySQL ODBC 3.51 드라이버를 이용하여 Export를 시작했다. 근데 영 속도가 안나오는 거다. 1시간정도 경과, 9만건의 데이타가 복사됐다. 와! 너무 느렸다. 모두 복사되려먼 4시간을 더 기다려야 하는데 이건 내가 뭔가 설정을 잘못했을 거야 생각하고 Export를 중단하고 여러가지를 체크해봤다.
MySQL에 생성된 테이블 형태를 보니 InnoDB 형태였다. 트랜잭션을 지원하는 게 InnoDB라고 알고 있는 나로서는 굳이 트랙잭션이 필요없어서 이를 MYISAM 타입으로 바꿔서 다시 Export를 시도해보았다. 엄청나게 빨라진 복사 속도. 5분도 안되서 40만건이 데이타가 복사됐다.
InnoDB로는 40만건의 데이타를 Insert하는데 5시간정도가 걸리고, MYISAM 형태로는 5분미만이 걸린다는 결론. 내가 MySQL을 잘 몰라서 다른 고려 사항도 있겠지만, InnoDB는 Insert시 확실히 느렸다. MySQL 버전 5부터 InnoDB 성능이 많이 향상됐다고 들었는데, 내가 체험해보기에는 MYISAM보다는 Insert에서 만큼은 차이가 많이 났다.
최지환 (데브피아)
데이터 베이스 디자인을 정규화하라.
논리적 데이터 베이스 디자인을 합리적으로 정규화하는 것이 최대의 성능 향상을 가져 온다는 것은 현업의 수많은 사례들이 충분히 증명한다. 필드의 갯수가 많은 커다란 테이블들을 단지 몇 개만 사용하여 만든 데이터 베이스들이 정규화되지 못한 데이터 베이스 디자인의 대표적인 보기이다.
고도로 정규화된 데이터 베이스들은 일반적으로 복잡한 관계적 Join으로 맺어져 있는데, 이 또한 성능을 잠식할 수 있다. 그러나, SQL 서버의 최적화 모듈은 효과적인 인덱스를 사용할 수 있는 한 매우 효과적으로 빠르고 효과적인 Join을 선택하는 데 효율적으로 작동한다. 정규화에 따른 효과들은 다음과 같다.
- 테이블들이 좁기 때문에 정렬과 인덱스 제작을 가속화한다.
- 더 많은 테이블들이 존재하기 때문에 더 많은 Clustered index를 만들 수 있다.
- 인덱스들이 보다 한정되고 집적된다.
- 테이블들을 물리적으로 배치하는 것을 조정하기 위해 세그먼트들을 보다 더 효과적으로 사용할 수 있게 한다.
- 하나의 테이블에 딸린 인덱스의 숫자가 적어짐으로써 UPDATE의 성능 향상을 가져온다.
- NULL과 중복된 데이터가 더 적어지기때문에 데이터 베이스의 집적도가 향상된다.
SQL 서버에서의 정규화는 종종 성능을 저하시키기보다는 오히려 향상시키는 결과를 가져온다. 정규화가 증가함에 따라 데이터를 얻어 내는 데 필요한 조인(join)의 숫자와 복잡도도 함께 증가한다. 많은 퀘리가 4방향 이 넘는 조인을 필요로 할 정도가 아니라면 정규화를 계속 진행하는 것이 바람직하다.
만일 논리적인 데이터베이스의 구성이 이미 고착되었고 전체적인 재설계가 그리 용이하지 않다면, 이런 경우에 병목의 주 요인이 되는 커다란 테이블의 정체가 확인되었을 경우 선택적으로 이들을 정규화하는 것도 가능하다.
만일 주어진 데이터베이스에 대한 접근이 Stored Procedure에 의해 수행된다 해도, 이러한 스키마의 변화는 응용 프로그램에 영향을 주지 않고서도 가능하다. 설령 그렇지 못한 상황이라 하더라도, 나뉘어진 테이블들을 마치 하나의 큰 테이블처럼 보이게 하는 새로운 뷰(view)를 만들어서 이러한 변화를 숨기는 것도 가능하다.
효율적인 인덱스 디자인을 하라.
많은 비관계형 시스템들과는 달리, 관계형 인덱스는 논리적 데이터베이스 설계의 부분으로 간주되지 않는다. 인덱스는 데이터베이스 스키마나 응용 프로그램 설계에 성능을 제외한 어떤 영향도 미치지 않고서도 제거할 수도 있고, 더하거나 변경할 수도 있다. 이러한 이유들로 인해, 여러 가지 다른 방식으로 인덱스를 구성해서 시험해 보는 데에 지체를 해서는 안된다.
현장에서의 사례들을 보면 대부분의 경우에 최적화기가 가장 효과적인 인덱스를 신뢰성 있게 선택한다. 전반적인 전략은 좋은 인덱스를 선택할 수 있는 상황을 최적화기에게 제공하는 것이고, 최적화기가 올바른 선택을 하도록 맏기는 것이다. 이렇게 하면 분석 시간을 줄일 수 있으며, 폭 넓고 다양한 상황에서 좋은 성능을 얻을 수 있다.
다음에 이어지는 내용은 인덱스를 구성할 때 생각해야 할 내용들이다. 우선 SQL 퀘리 문장에서 WHERE 문이 나오는 부분을 분석해야 하는데, 왜냐하면 바로 이 부분이 최적화기가 우선적으로 주목하는 촛점이기 때문이다. WHERE문에 나오는 각각의 컬럼은 인덱싱의 후보가 된다. 분석해야 할 퀘리가 너무 많다면, 대표적인 것들이나 혹은 느린 것들만 분석해도 된다.
만일 개발 툴이 자동적으로 SQL 문을 생성해 낸다면, 이 일은 보다 더 어려워질 수 있다. 대부분의 이러한 툴들은 생성된 SQL 구문을 디버깅을 위해 파일이나 스크린에 기록해준다. 이러한 기능이 제공되는 지를 공급자에게 문의해 보는 것이 바람직하다.
인덱스를 좁게 구성하라. 좁은 인덱스들은 종종 다중 컬럼의 복잡한 인덱스들보다 더욱 효과적이다. 좁은 인덱스는 페이지당 행이 더 많으며, 인덱스 레벨이 낮기때문에 성능 향상을 촉발한다. SQL 서버의 최적화기는 복잡한 인덱스를 만나면 단지 가장 우선적인 컬럼에 대한 통계만을 유지할 따름이다. 따라서, 복잡한 인덱스의 첫번째 컬럼이 그리 신통한 선택이 아니라면, 최적화기는 그 인덱스를 사용하지 않을 수도 있다.
최적화기는 수백, 혹은 심지어는 수천개의 인덱스와 조인의 가능성들을 효과적이고 신속하게 분석할 수 있다. 좁은 인덱스를 사용함으로써 더 많은 인덱스가 만들어지면 최적화기의 선택의 폭이 넓어짐과 동시에 이로 인해 일반적으로 성능이 보다 향상된다. 여러 컬럼에 걸친 폭이 넓은 인덱스를 단지 몇 개만 사용하는 경우에는 이와 반대로 최적화기의 선택의 폭이 좁아져서 성능의 저하를 가져 올 수 있다.
그렇다고 해서 적정한 읽기 성능을 얻는 데 필요한 인덱스의 숫자보다 더 많은 인덱스를 생성해서는 안되는데, 이는 이러한 인덱스들을 갱신하는 데 요구되는 부하 때문이다. 그러나, 심지어는 갱신이 가장 빈번하게 일어나는 작업들조차도 쓰기보다는 읽기를 훨씬 더 많이 요구한다. 따라서, 어떤 새로운 인덱스가 도움이 될 것이라고 생각되면 이를 시험해 보는 것을 주저해서는 안된다. 바라는 결과가 나오지 않을 경우에는 추후 언제라도 삭제를 할 수 있기 때문이다.
Clustered Index를 사용하라. Clustered Index를 적절히 사용하는 것 하나만으로도 대단한 성능 향상을 가져올 있다. UPDATE와 DELETE 작업조차도 종종 clustered index에 의해 속도가 빨라진다. 왜냐하면 이 작업들은 우선 많이 읽어야 하기 때문이다. 한 테이블당 clustered index는 하나밖에 있을 수 없기 때문에, 이를 사용할 때는 매우 현명하게 생각해야 한다.
수많은 행을 돌려주는 퀘리나 또는 어떤 영역에 걸친 값들과 관계된 퀘리들은 clustered index를 사용하여 속도를 개선할 수 있는 좋은 후보가 된다. 예를 들자면 다음과 같은 것들이다.
SELECT * FROM PHONEBOOK WHERE LASTNAME='SMITH', 또는
SELECT * FROM MEMBERTABLE WHERE MEMBER_NO > 5000 AND MEMBER_NO < 6000
두 경우를 비교해서 말한다면, 위에 언급된 LASTNAME 이나 MEMBER_NO의 행들은 non-clustered index를 사용하기엔 바람직하지 않다. 결과가 몇 행 밖에 안되는 상황에서는 non-clustered index를 사용하도록 노력해야 한다.
행 유일성을 조사하라. 이렇게 하면 어떤 행이 clustered index의 후보로서 알맞은 지, 어떤 행이 non-clustered index의 후보로서 알맞은 지, 혹은 아예 인덱싱을 하지 않는 것이 좋은 지를 결정하는 데 도움이 된다. 행 유일성을 조사해야 할 예제 퀘리로는 다음과 같은 것이 있다.
SELECT COUNT (DISTINCT COLNAME) FROM TABLENAME.
이 퀘리는 컬럼 내의 유일한 값들의 숫자를 보여 준다. 이 숫자를 테이블 내의 행 전체의 갯수와 비교를 해 본다. 10,000개의 행이 있는 테이블에서 5000개 정도의 유일한 값이 있는 행은 non-clustered index를 하기에 적당하다 하겠다. 같은 테이블에서, 20개 정도의 유일한 값이 존재하는 행은 clustered index로 구성하는 데 더욱 적합할 것이다.
3개의 유일한 값이 나온다면 인덱스를 아예 하지 않는 것이 좋다. 이는 단지 보기일 따름이며, 결코 엄격하고 고정된 규칙은 아니다. 검색문의 WHERE 구문에 나타난 개개의 열들을 인덱싱 하는 것을 잊지 말아야 한다.
검색으로 나타나는 행의 갯수 또한 인덱스 선택의 중요한 요소이다. 최적화기가 볼 때, non-clustered index에서는 한 행이 리턴될 때마다 적어도 한 페이지의 I/O가 소요된다. 이러한 비율로는, 머지 않아 차라리 그냥 전체 테이블을 스캔하는 것이 더욱 더 효과적이 된다. 이것은 결과 집합의 크기를 제한하거나 혹은 큰 결과를 clustered index로써 잡아 내는 또 다른 이유가 된다.
성능 저하를 분석하는 방법들
우선 느린 쿼리를 찾아내도록 한다. 단지 몇 개의 SQL 쿼리가 느린 경우에도 종종 전체 응용 프로그램이 느린 것처럼 보일 때가 있다. SQL 구문을 자동으로 생성해 주는 개발툴을 사용하는 경우에는, 이 툴에서 가능한 모든 진단 모드나 디버깅 모드를 사용하여 생성된 SQL 구문을 잡아 내도록 한다. SQL 구문을 삽입해 주는 개발도구를 사용하는 경우에는 이 작업은 훨씬 쉽다. 느린 쿼리가 무엇인 지를 파악한 후에는 다음의 과정들을 따르면 된다.
- ISQL이나 SAF 등의 쿼리 툴을 사용하여 느리다고 의심이 가는 쿼리를 따로 떼어서 실행해 본 다음 실제로 느린 지를 확인한다.
- SET STATISTICS IO ON과 SET SHOWPLAN ON을 사용하여 쿼리에 의해 소비되는 I/O와 선택된
Query plan을 검사한다. 논리적 페이지 I/O의 숫자에 주목한다. 논리적 I/O 숫자를 기록한다. 이는 성능 향상을
측정할 비교 수치가 된다.
- 만일 쿼리가 뷰나 stored procedure를 포함하고 있으면, 쿼리를 따로 추출하여 독립적으로 실행한다. 이렇게 하면 서로 다른 인덱스를 사용할 때 접근 계획을 바꿀 수 있도록 해 준다.
- 관계되는 테이블에 트리거가 실행되면서 자동적으로 I/O를 생성하는 트리거가 뷰가 있는 지를 확인한다.
- 느린 쿼리에 의해 사용되는 테이블의 인덱스들을 조사한다. 위에 나열된 방법들을 사용하여 이들이 좋은 인덱스인 지를 검사한다. 그리고 필요하면 변화를 준다.
- 인덱스에 변화를 준 다음 쿼리를 다시 실행하여 I/O 숫자나 접근 계획에 변화가 있는 지를 관찰한다.
- 성능이 향상된 것을 확인하였으면, 주 응용 프로그램을 실행하여 전체적인 성능이 향상되었는 지를 확인한다.
프로그램이 I/O나 CPU 한계치에 도달해서 동작하는 지를 관찰한다. 일반적으로 쿼리가 I/O나 CPU에 의해 성능이 제한되는 지를 조사하는 것은 많은 도움을 준다. 이러한 촛점은 진정한 병목이 일어나는 부분에 대해 노력을 집중할 수 있도록 해 준다. 예를 들어, 어떤 쿼리가 CPU에 의해 성능의 제약을 받으면, SQL 서버에 메모리를 더 설치한다고 해서 성능의 향상을 기대할 수는 없는데, 왜냐하면 메모리를 더 많이 설치할 경우에는 단지 캐시 적중률만 높여주며, 이는 이 경우 이미 충분히 높기 때문이다. SQL 서버가 I/O에 의해 제약을 받는 지, 아니면 CPU에 의해 제약을 받는 지를 분석하는 과정은 다음과 같다.
- OS/2 CPU monitoring program을 사용한다. CompuServe IBMOS2 forum에서 여러 가지를 구할 수 있다.
- 쿼리가 수행되는 동안 CPU 그래프가 계속 높은 상태를 유지한다면(예를 들어, >70%) 이는 CPU에 의해 제약을 받는 상태이다.
- 쿼리가 수행되는 동안 CPU 그래프가 계속 낮은 상태를 유지한다면(예를 들어, <50%) 이는I/O 병목 상태이다.
- CPU 그래프를 STATISTICS IO의 정보와 비교해 본다.
VLDB 의 성능 관리를 위한 아키텍처는 VLDB를 극복하기 위해 중요한 항목이다. 이러한 VLD에서 에서의 성능관리는 가만히 있는다고 구축되는 것이 아니다. 서로 힘을 보태야만 성능 관리를 위한 최적의 아키텍쳐가 구성될 것이다. VLDB에 대해 성능 관리를 위한 아키텍쳐로는 병렬 프로세싱, 직접 로딩 및 테이블 아키텍쳐 등이 있다.
VLDB는 우리가 인지하고 있는 상태에서 발생하는 현상이 아니다. 아무도 모르게 우리가 관리하는 데이터베이스가 VLDB로 변하게 된다. 그로 인해 성능 저하 등 VLDB의 여러가지 문제점이 발생하기 시작한다. 이처럼 VLDB의 문제가 발생하기 전까지는 우리가 인지하기 힘들기 때문에 VLDB와의 소리 없는 전쟁이 진행되는 것이다. 이미 문제가 발생하면 늦은 것이다. 이런저런 문제가 발생한 후 이를 잠재우기 위해서는 수많은 노력과 시간을 투자해야하기 때문이다. VLDB의 문제가 발생하기 전에 VLDB를 위한 효과적인 아키텍처를 구축해야 할 것이다.
고정관념을 버리는 것에서 최적의 아키텍처 시작
우리가 가지고 있는 고정관념을 버린다는 것은 매우 힘든 일 이다. 하지만 VLDB에서는 우리가 일반적으로 인지하고 있는 데이터베이스의 고정 관념을 버리지 않고서는 해결할 수 없는 문제가 너무 많다는 것이다. 이러한 고정 관념을 버리는 순간 우리는 VLDB를 효과적으로 대처할 수 있을 것이다.
예를 들어 보자. 어떤 대용량 테이블에 대해서 하나의 컬럼에 대해 모든 값을 갱신한다고 가정하자. 이와 같다면 모든 개발자들은 UPDATE를 수행하고자 할 것이다. 아마 UPDATE를 제외하고 다른 방법을 고려하는 사람은 거의 없을 것이다. 그렇기 때문에 해당 대용량 테이블의 모든 데이터에 UPDATE를 수행하게 되고 대부분의 개발자들은 데이터가 너무 많아 속도가 느리다고 불평을 하게 된다.
그래서 생각하는 것이 이러한 대용량 데이터에 대해 분리하여 UPDATE를 수행하는 것이다. 그래도 엄청난 데이터를 저장하고 있기 때문에 작업이 빠르게 종료하지는 않을 것이다. 이렇듯 대용량 테이블에서 전체 데이터의 UPDATE는 결코 쉬운 작업이 아니다. 이러한 작업이 한번만 수행되고 끝이라면 그나마 다행일 것이다. 하지만 정기적으로 발생하는 작업이라면 작업이 있는 날마다 제대로 퇴근하지 못하는 것은 물론 서비스도 불가능하게 마련이다.
대용량 UPDATE 작업은 시스템에는 엄청난 부하를 발생시키기 때문이다. 그렇다면 도대체 어떠한 방법으로 이러한 VLDB에서의 대용량 작업을 헤쳐 나갈 수 있겠는가? 바로 고정 관념을 버려야 하는 것이다. 왜 대용량 테이블에 대해 갱신 작업을 UPDATE로만 수행하려고 하는가? 위와 같은 작업에 대해 우리는 INSERT 작업을 수행할 수 있다는 것을 아는가? 많은 사람들이 갱신 작업에 대해 INSERT 작업을 이용할 수 있다는 말에 의아해 할 수도 있을 것이다.
우선 대용량 테이블에 대한 갱신 작업에서 왜 INSERT 작업을 사용하면 어떠한 장점이 있는지 확인해 보자. UPDATE, DELETE 및 INSERT 작업 중에 가장 속도가 빠른 것은 INSERT 작업이다. 또한, 오라클에서는 특정 INSERT 작업에 대해 직접 로딩(DIRECT LOADING), 병렬 프로세싱(PARALLEL PROCESSING) 및 로그 생략(NOLOGGING) 기능을 제공함으로써 동일한 양의 대용량 작업에 대해 UPDATE 보다 10배 이상 빠른 속도를 보장하게 된다.
왜 이러한 기능을 효과적으로 사용하지 않으려 하는가? 이러한 기능을 효과적으로 사용하기 위해 대용량 UPDATE 뿐만 아니라 대용량 DELETE에 대해서도 INSERT 아키텍처를 사용해야 할 것이다. 그렇다면 어떻게 UPDATE를 INSERT로 대신할 수 있는지 확인해 보자.
대용량 테이블의 하나의 컬럼에 대해 값을 변경한다고 가정하자. 그렇다는 이야기는 무엇인가? 다른 컬럼은 변경이 발생하지 않는다는 것이다. 이를 이용하여 해당 대용량 테이블을 조회하여 임시 테이블에 INSERT를 수행하는 것이다. 물론, 갱신되어야 하는 컬럼은 변경을 수행하면서 임시 테이블로 저장을 수행해야 할 것이다. 이와 같이 수행한다면 임시 테이블에는 우리가 원하는 최종 갱신된 데이터가 저장될 것이다. 어떻게 보면 매우 쉬운 방법이다. 그리고 이것이 과연 수행 속도가 빠르겠는가를 의아해 할지도 모른다.
하지만 INSERT 작업 시 직접 로딩, 병렬 프로세싱 및 로그 생략 기법을 이용한다면 UPDATE를 수행하는 것보다 몇 배 이상 빠른 수행 속도를 보장하게 될 것이다. 물론, 임시 테이블에 데이터를 저장한 후에는 테이블 이름을 변경해야 하며 원하는 인덱스를 생성해야 할 것이다. 테이블 이름 변경은 시간이 소요되지 않으며 인덱스의 경우도 병렬 프로세싱과 로그 생략 기법을 이용한다면 매우 빠른 수행 속도를 보장하게 될 것이다.
이와 같이 기존 데이터베이스에서 우리가 수행하던 작업들에 대한 고정 관념을 버리고 새로운 아키텍처로 재무장해야 할 것이다. UPDATE를 INSERT로 변경하게 되면 작업 절차는 좀더 복잡해 진다. 이제 선택은 우리에게 있는 것이다. 성능이 중요한 것인지 아니면 관리가 중요한 것인지 우리가 판단해야 할 때이다.
성능 관리를 위한 아키텍처를 고려하라.
VLDB의 성능 관리를 위한 아키텍처는 VLDB를 극복하기 위해 중요한 항목이다. 이러한 VLDB에서의 성능 관리는 가만히 있는다고 구축되는 것이 아니다. 담당자들이 힘과 머리를 모았을 때 성능 관리를 위한 최적의 아키텍처가 구성될 것이다. VLDB에 대해 성능 관리를 위한 아키텍처로는 병렬 프로세싱, 직접 로딩 및 테이블 아키텍처 등이 있다.
첫 번째로 병렬 프로세싱 아키텍처에 대해 확인해 보자. 병렬 프로세싱은 대분의 데이터베이스에서 지원하게 된다. 병렬 프로세싱의 아키텍처는 매우 단순한 아키텍처이다. 하나의 대용량 작업을 함에 있어서 하나의 프로세스로 작업을 수행하는 것보다는 여러 프로세스로 작업을 수행하는 것이 수행 속도를 향상시킬 수 있다는 것을 누구나 다 알 수 있을 것이다. 이처럼 병렬 프로세싱은 하나의 작업을 여러 개의 프로세스가 작업할 수 있게 하는 아키텍처이다.
이렇게 간단한 아키텍처임에도 불구하고 많은 곳에서 이러한 병렬 프로세싱을 제대로 이용하지 못한다는 것이다. 이론적인 것도 중요하지만 실제 업무에 적용하는 것은 매우 중요한 일이다. 많은 사람들이 병렬 프로세싱에 대해 모르는 것도 문제이지만 병렬 프로세싱을 아는 사람마저 제대로 병렬 프로세싱을 업무에 적용하지 못한다는 것이 문제이다.
병렬 프로세싱은 VLDB에서 대용량의 데이터를 처리하는 작업에 적용하지 않으면 안 되는 필수 아키텍처임을 명심하기 바란다. 병렬 프로세싱 아키텍처를 적용하지 않고 대용량의 데이터를 처리하는 작업은 이제 더 이상 수행하지 않기 바란다. 병렬 프로세싱이야 말로 10시간 정도의 작업을 불과 1시간으로 줄일 수 있는 무기이다.
두 번째로 직접 로딩(DIRECT LOADING) 아키텍처에 대해 확인해 보자. 직접 로딩은 오라클에서 사용할 수 있는 아키텍처이며 이는 일부 INSERT 및 IMPORT 작업 등에서 한정적으로 사용이 가능하다. 직접 로딩 또한 병렬 프로세싱과 같이 매우 간단한 아키텍처를 가진다. 하지만 그 위력은 엄청나다고 감히 이야기할 수 있다. INSERT 작업에서의 직접 로딩은 기존 INSERT 작업에 비해 최소 10배 이상의 성능 향상을 기대할 수 있다.
또한 앞서 언급했듯이 UPDATE 작업과 DELETE 작업을 INSERT로 변경하는 순간 많은 경우에 직업 로딩을 사용할 수 있으며 이로 인해 몇 배의 성능을 향상시킬 수 있다. 이 얼마나 놀라운 사실인가? 한가지 아키텍처를 추가함으로써 우리는 몇 배의 성능 향상을 기대할 수 있다는 사실이 놀랍지 않은가?
세 번째로 테이블 아키텍처이다. 데이터베이스에서 제공하는 테이블에는 많은 종류의 테이블이 존재한다. 어떤 테이블을 선택하는가에 따라 테이블을 엑세스하는 애플리케이션의 성능이 향상된다는 것을 아는가? 이 또한 놀라운 일이 아닐 수 없다. 오라클의 예를 들어보자. 오라클에는 일반 테이블, 파티션 테이블 및 IOT 등 여러 가지 형태에 의해 테이블을 구성할 수 있다. 하지만 많은 곳에서 이러한 테이블에 대한 아키텍처를 고려하지 않는 경우가 많다. 일반 테이블을 파티션 테이블로 변경하는 순간 또는 일반 테이블을 IOT 테이블로 변경하는 순간 우리 눈앞에는 새로운 세상이 펼쳐질 수 있다.
이제부터는 테이블을 생성하고 사용할 경우 이러한 테이블 아키텍처를 항상 신중하게 고려해야 할 것이다. 테이블 아키텍처의 선택이 VLDB의 성능을 좌우할 수 있다.
애플리케이션의 아키텍처와 추세(TREND)를 관리하라
애플리케이션은 업무를 구현하는 매우 중요한 요소이다. 이러한 애플리케이션에서 사용하는 데이터는 대부분 데이터베이스로부터 추출하게 된다. 이러한 애플리케이션과 데이터베이스가 별도로 아키텍처가 구성되면 안될 것이다. 애플리케이션 또한 데이터베이스 아키텍처를 따라야 하며 이러한 데이터베이스 아키텍처에 맞는 애플리케이션 성능 관리 아키텍처가 수립되어야 할 것이다.
첫 번째로 데이터베이스 아키텍처와 애플리케이션에 대해 확인해 보자. 데이터베이스에서 파티션 테이블로 구축했을 경우 애플리케이션에서 이러한 데이터베이스의 구성과 관계없이 별도로 어플리케이션을 작성했다면 아무리 데이터베이스에서 VLDB를 위한 아키텍처를 도입하였더라고 아무런 의미가 없게 된다. 결국, 데이터베이스 아키텍처가 확정되었다면 애플리케이션 또한 데이터베이스 아키텍처를 따라야 할 것이다.
두 번째로 애플리케이션의 성능 관리 아키텍처에 대해 확인해 보자. 성능 관리는 매우 어려운 부분임에는 틀림없다. 많은 프로젝트를 지원하면서 성능 관리를 정확히 구현하는 곳은 거의 보지 못했다. VLDB의 성능 관리는 CPU 또는 메모리 등의 자원에 대한 성능 관리와 애플리케이션에 대한 성능 관리로 구분되어 질 것이다.
애플리케이션에 대한 성능 관리 또는 자원에 대한 성능 관리 모두 추세(TREND)를 관리해야 할 것이다. 하나의 애플리케이션이 10분이 수행되었다면 이것이 과연 적정한 수준의 수행 속도를 유지한 것인지 아니면 1분이면 수행되어야 할 애플리케이션이 10분이 수행된 것인지를 어떻게 파악하겠는가? 바로 추세를 관리하는 애플리케이션 성능 관리 아키텍처를 수립하지 않는다면 이는 불가능한 일일 것이다.
VLDB에서의 애플리케이션 성능 관리는 매우 중요한 항목이다. 이러한 애플리케이션 성능 관리는 애플리케이션의 수행 속도도 중요하지만 해당 애플리케이션의 자원 사용 및 수행 속도의 추세(TREND)를 파악해야 할 것이다.
데이터의 데이터를 위한 데이터에 의한 아키텍처를 구축하라
VLDB를 효과적으로 관리하기 위한 아키텍처로 성능 관리 아키텍처의 수립이 중요하다고 앞에서 언급하였다. VLDB를 효과적으로 관리하기 위해 이보다 더 중요한 것이 데이터를 위한 아키텍처의 수립이다. 데이터베이스는 데이터의 데이터를 위한 데이터에 의한 시스템이다. 시작도 데이터고 끝도 데이터이다. 이러한 데이터를 효과적으로 관리하기 위한 아키텍처야 말로 VLDB에서 핵심 아키텍처가 될 것이다.
데이터의 생명 주기(LIFE CYCLE)에 대한 아키텍처 관리가 데이터 관리의 시작이다. 데이터에 대한 생명 주기라는 것이 무엇인가? 데이터가 살아 움직이는 것도 아닌데 무슨 생명 주기가 있냐고 반문할지도 모른다. 하지만 데이터는 살아 움직인다. 새롭게 생성된 데이터는 활발하게 활동하다 어느 순간에는 단지 조회용으로 변하기도 한다. 조회용으로 어느 정도를 살다가 데이터베이스에서 삭제되기도 한다.
어떤 데이터는 한번 태어나 영원히 죽음을 맞지 않는 데이터도 있다. 어떤 데이터는 태어나 병원에 다니는 것과 같이 매일 변경만 발생하는 데이터도 있다. 또한 어떤 데이터는 태어나서 이름도 알리지 못하고 바로 사라지는 임시 데이터도 존재한다. 데이터가 동시에 태어나도 이와 같이 서로 다른 운명을 살게 되는 것이다. 데이터들이 이와 같이 서로 다른 운명을 보내니 어찌 살아 움직이지 않는다고 할 수 있겠는가?
하지만 우리는 이러한 데이터의 운명을 무시하고 데이터베이스를 구축해 왔다. 이것이 우리가 VLDB에 대해 가장 큰 실수를 범한 것이다. VLDB는 대용량의 데이터를 가지고 있기 때문에 더 많은 가지수의 데이터의 생명 주기(LIFE CYCLE)이 존재하게 된다. 그렇다면 이러한 데이터의 생명 주기가 과연 왜 중요한 것일까?
한번 태어나서 영원이 해당 데이터베이스에서 삶을 사는 데이터는 무엇인가? 이러한 생명 주기를 사는 데이터는 일반적으로 참조 데이터가 된다. 참조 데이터는 무엇을 의미하는가? 참조 데이터는 영원히 데이터베이스에서 생활을 하지만 그 크기가 크게 변하지 않는다는 것이다. 어떻게 보면 거의 변하지 않는 데이터가 될 것이다. 이러한 운명을 가지는 데이터는 보통의 경우 조회를 위해 참조되는 데이터이기 때문에 반드시 조회 최적화 아키텍처가 수립되어야 할 것이다.
조회 성능을 최적화 하기 위해 IOT를 고려할 수 있으며 때로는 해당 데이터를 메모리에 상주시킬 수도 있을 것이다. 이와 같은 조회용으로 사용되는 데이터에 대해 아무런 고려를 하지 않는다면 절대 조회에 대해 성능을 보장 받을 수 없을 것이다. 또한 이러한 데이터가 단순 조회용으로 사용된다면 해당 데이터는 한번만 백업 받아 놓게 되면 해당 백업 데이터로 언제든지 현재 데이터로 복구가 가능할 것이다. 이렇게 백업 아키텍처를 수립한다면 백업 및 복구를 수행하는 시간을 감소시킬 수 있을 것이다.
태어나서 일정 기간 활발하게 사용되다 어느 기간이 지나면 완전 조회용으로 사용되는 데이터가 있다. 해당 데이터는 일정 기간이 지나게 되면 데이터베이스에서 사라지게 된다. 이와 같은 운영을 가지는 데이터가 데이터베이스를 VLDB로 변경시키는 대용량 이력 테이블이 된다. 이러한 대용량 이력 테이블은 어떠한가? 최근 데이터는 활발하게 활동을 하게 되며 과거 데이터는 대부분 조회용으로 사용하게 된다.
또한 보관 주기가 지나게 되면 해당 데이터는 데이터베이스에서 삭제될 것이다. 이러한 이력 데이터는 대용량 테이블이 되며 데이터베이스를 VLDB로 변화시키는 주범이 된다. 이력 데이터는 데이터의 보관 주기가 반드시 존재한다. 이러한 이력 데이터를 일반 테이블로 생성한다면 보관 주기를 관리하기 위해 보관 주기가 지난 데이터에 대해 DELETE를 수행해야 할 것이다. 대용량이 DELETE 작업은 시스템에 엄청난 성능 저하를 발생시키게 된다. 보관 주기를 용이하게 관리하기 위해 이러한 대용량 이력 테이블은 반드시 파티션 테이블로 생성해야 할 것이다.
소리 없는 전쟁의 승리자가 되라
VLDB를 위한 최적의 아키텍처는 쉽게 답을 찾을 수 는 없다. 하지만 VLDB의 최적화를 위해 가장 효율적인 아키텍처를 찾기 위해 노력해야 할 것이다. 그래야만 우리는 VLDB의 태풍을 무사히 지나갈 수 있을 것이다. VLDB의 태풍이 한번 지나갔다고 끝은 아니다. VLDB의 태풍은 계속 찾아올 것이다. 준비하는 자만이 VLDB의 태풍을 벗어날 수 있을 것이다.
VLDB에서 가장 중요한 것은 데이터를 감소시키는 것이다. 이는 논리적 모델링과 데이터를 감소시키는 정규화를 통해 구현할 수 있다. 또한 데이터베이스에서 제공하는 압축 기술을 이용하여 데이터를 획기적으로 감소시킬 수 있다.
파티션 테이블로 생성한다면 보관 주기 관리를 위해 DELETE 작업 대신 DROP을 수행하게 되므로 시스템에 부하가 발생하지 않게 된다. 따라서 수행 속도는 놀라울 정도로 향상될 것이다. 이는 보관 주기가 지난 데이터에 대해 DROP을 수행하는 것은 DDL이므로 시스템에 전혀 부하를 발생시키지 않기 때문이다.
또한 파티션 별로 전체 조회를 수행할 경우 병렬 프로세싱 아키텍처를 효과적으로 사용할 수 있기 때문에 일반 조회에 비해 몇 배 빠른 조회 속도를 보장할 수 있을 것이다. 이것이 바로 대용량 이력 테이블을 관리하는 아키텍처이다. 물론, 파티션 아키텍처와 효율적으로 적용되었을 경우에 이러한 혜택을 누릴 수 있다.
또한, 최근 데이터를 제외하고 과거 데이터는 보통의 경우 변경이 발생하지 않게 되며 조회용으로 사용될 것이다. 이러한 조회용으로 사용되는 데이터에 대해서만 한번 백업을 수행하게 되면 해당 백업 본으로 언제든지 복구를 손쉽게 수행할 수 있게 된다. 해당 데이터는 매일 수행하는 백업에서 제외해도 될 것이다. 결국, 대용량 데이터가 주기적인 백업에서 제외되기 때문에 백업 시간이 감소하게 된다. 백업 시간이 감소한다는 것은 데이터베이스가 그만큼 안정적인 서비스를 제공할 수 있다는 것을 의미하기도 한다.
그렇다면 매번 변경이 발생하는 운명을 가진 데이터는 어떠한가? 이러한 데이터는 해당 테이블의 인덱스 아키텍처가 매우 중요하게 된다. 인덱스는 조회 속도의 향상을 위해서는 반드시 필요하지만 DELETE, INSERT 및 UPDATE 등의 DML 작업에서는 성능 저하를 발생시키게 된다.
따라서 변경 작업이 중요한 데이터들에 대해서는 인덱스 최적화를 통해 인덱스 개수를 감소시켜야 할 것이다. 인덱스의 개수를 감소시키는 것은 DML 작업의 속도를 최적화할 수 있는 최고의 방법이 될 것이다. 물론, 대용량 INSERT 작업 시에는 직접 로딩 등을 사용하여 INSERT의 성능을 최적화해야 할 것이다.
결국, 데이터의 속성에 맞는 아키텍처를 적용하는 것이 VLDB의 최적화를 이루는 가장 좋은 방법이라는 것을 명심하길 바란다. 그러기 위해서는 데이터를 정확히 구분하여 각 데이터에 생명 주기(LIFE CYCLE)를 정확히 명시해야 할 것이다.
출처 : 경영과 컴퓨터 [2007년 6월호]
출처 : 경영과 컴퓨터 [2007년 6월호]
- INF: How to Shrink the SQL Server 7.0 Transaction Log
DBCC SHRINKFILE 명령 또는 DBCC SHRINKDATABASE 명령을 사용할 때 트랜잭션 로그가 줄어들지 않는 일반적인 이유가 몇 가지 있다. 본 문서에서는 이에 대해 간략하게 설명하며, SQL Server Books Online의 "DBCC SHRINKFILE" 및 "DBCC SHRINKDATABASE" 항목에서 자세하게 다룬다. - Exporting Data Programmatiaclly with bcp and xp_cmdshell
This article demonstrates how to programmatically control bcp to generate text files. It uses dynamic SQL and xp_cmdshell to execute a call to bcp from within Query Analyzer.
오늘 MS SQLServer에서 쿼리를 하다가 다음의 오류를 만났다.The index entry of length 934 bytes for the index 'Table_IDX' exceeds the maximum length of 900 bytes.
처음보는 오류다. 구글을 뒤져보니 MS SQLServer 인덱스의 최대길이 제한이 있었고, 그 길이는 900 바이트였다. 그랬군... 다음은 SQL Server 7.0과 2000의 제한 내용이다.
R. The reason is that the MSSQLSERVER service is running under a separate set of NT
credentials - all services are related to an NT account. It doesn't matter who
YOU are logged on as (after all SQL runs quite happily when nobody is logged on
locally to the server doesn't it). Therefore your logon account and any mapped
drives are irrelevant. It is SQL Server doing the backup, not you. This is the
same for backups done via SQL Executive/SQL Agent - they just pass the TSQL to
SQL Server to run, so it's still MSSQLSERVER doing the backup/restore.
For this reason the backup GUI does not show you mapped drives or allow a UNC path to
be typed in. You have to use raw TSQL commands to do the backup.
The default set of NT credentials used by MSSQLSERVER is the SYSTEM account. You can
check what userid that MSSQLSERVER is running under by looking at control
panel/services highlighting MSSQLSERVER and choosing the start-up option.
The SYSTEM account has no access to shares on the network as it isn't an
authenticated network account. Therefore SQL Server running under this account cannot
backup to a normal network share.
So, if you want to backup to a network share you have two choices :-
1. Change the account the MSSQLSERVER service runs under to a user account with the
relevant network rights.
or
2. Amend the following registry value on the TARGET server and add the share name
you want to dump to - the share does not then authenticate who is coming in and
so a SYSTEM account will work. The server service on the target server must be
re-started before the change takes effect. Note that this effectively removes ALL
security on that share, so you're letting anyone or anything have access. This is
probably not something you want to do with production business data.
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\LanmanServer\Parameters\NullSessionShares
Whichever method you use, you MUST also use a UNC name to reference the file required
and not a drive letter.
e.g. (6.5) DUMP DATABASE pubs to DISK='\\server01\share\backupdir\backup.dmp'
e.g. (7., 2000) BACKUP DATABASE pubs to DISK='\\server01\share\backupdir\backup.dmp'
댓글을 달아 주세요