가늘어도 긴놈이 장땡

SQL Server 2008 관련된 동영상을 보다가 Entity Data Platform 이란거에 대해서 좀 봤고 LINQ라는 것에 대해서 좀 봤고, 최근에 알게된 Erlang 에도 생각이 미치더니만, 괜한 공상이 머릿속을 흔든다.

앞으로도 DB의 중요성은 절대 줄어들지 않을 것 같으므로 정확하고 효율적인 모델링, 능숙한 SQL사용, 성능과 저장공간을 동시에 고려한 최적의 인덱스 작성 능력은 언제나 요구되는 능력일 것이다. DB를 기반으로 한 솔루션에서 대부분의 병목은 DB에서 발생하기 때문이다. 그런데 인터넷 세상이 되고 비즈니스에서 속도가 강조되면서 개발도 함께 빨라져야할 필요가 생기고 있다. 더불어 유지보수의 용이함도 꼭 필요하다. 이런 측면에서 객체 모델의 도입이 필요하다. 하지만  OR impedence 를 해결하는게 쉬운 일은 아니며, 이를 위한 OR매핑 솔루션의 사용도 결코 쉽지 않다. 만들기는 더더욱 어렵다.

SQL2008 white paper 를 읽다보니 개발측면에서 흥미로운게 눈에 띄었는데, ADO, Visual Studio 와 함께 결합한 LINQ라는 놈이다. Language Integrated Query 라는건데, DB에 SQL로 질의를 하는게 아니라(테이블 데이터를 내놔라! 하는 기존방식) 고객 엔티티를 내놔라! 하고 요청하고 그 결과가 DataSet이 아니라 .NET 객체로 반환되는 것이다. 아... 이건 사실상의 OR매퍼가 아닌가? 다음과 같은 syntax로 사용한다.

public partial class DataDemos_1_HelloWorld : System.Web.UI.
{
    protected void Page_Load(object sender, EventArgs e)
    {
        AdventureWorks db = new AdventureWorks();

        var query = from person in db.SalesPeople
                         where person.HireData > new DataTime(2002,1,1)
                          select person;

        DataList1.DataSource = query;
        DataList1.DataBind();
    }
}

코드를 보자. 저거 ASP.NET 코드다. 

근데 AdventureWorks 라니? 그렇다.. SQL2005 의 데모 DB인 AdventureWorks를 객체로 이해하고 접근하고 있다. db.SalesPeople 는 저 DB에 있는 스키마일까? person 은 어떤 테이블하고 매핑되어 있는 것일까? 글고 원본 그림을 보면 "Results are .NET objects, strongly typed, support data binding" 이라고 되어 있다. 음.. 별도의 비즈니스 객체 레이어를 생성해줘야지 거기다가 메서드를 추가할텐데...  혹시 매핑관계를 별도로 다 정의해줘야 하는걸까? 그 노가다도 상당히 골때릴텐데... 게다가 저 놈이 결국 DB에 날리는건 SQL일텐데 이 놈의 성능은 어떨까? 객체를 Persist 할 때 만들어 내는 SQL은 객체의 프라퍼티중 변경된 넘들만 들어가게 만들어 줄까? DBA입장에서 보기엔 DB관리가 점점 더 힘들어지는 상황인 것 같다.

관련 링크 : http://link.allblog.net/4060901/http://pobeez.egloos.com/218530
관련 링크 : http://blogs.msdn.com/charlie/archive/2007/01/26/anders-hejlsberg-on-linq-and-functional-programming.aspx (동영상 꼭 볼것!)

동영상을 보면 대충 뭘 하려는지 짐작이 된다. 데이터를 다루는 시스템을 예로 들어보자. 결국 다른 소스에서 온 서로 다른 데이터들을 이리저리 엮고, 계산해서 보여주고 인풋을 계산해서 보여주는거 아니던가 말이다. LINQ가 하려는건 언어에 데이터를 다루는 기능을 통합하되, 그걸 좀 더 추상적인 레벨에서 수행할 수 있게 하는거다. 음.. 간단하게 설명하자면 C#같은 언어에 DB의 쿼리 엔진같은넘이 들어간다고 보면 되겠다. 동영상에 함수형 프로그래밍이니 지연된 실행이니 타입 추론이니 어쩌구 나오는데... 설명 가만 들어보면 SQL을 DB가 받아서 parse tree만들고 그러면서 타입체크하고 실행 계획 만들면서 CPU갯수 체크해서 병렬로 돌릴 수 있는거 돌리고... 그러는거랑 LINQ에서 추구하는게 대단히 흡사하다. 얘네들은 언어레벨에서 이걸 하려고 하는거고, 이렇게 하기 위한 최고의 방법이 함수형 언어 패러다임의 도입이고, 이것의 밑바탕은 선형 대수이고 이게 되면 람다식을 해석해서 만든 expression tree를 보고 CPU남으면 병렬로 돌릴지 결정하는거도 쉽게 쉽게 할 수 있다는 말이 되겠다.

LINQ를 자유자재로 쓰게 되면 XML하고 텍스트, DB데이터를 필터링해서 읽어온다음 join 해서 group by 한 다음에 결과를 어디로 보내는 작업을 DB에서 쿼리 만들듯이 할 수 있게 될거다. 중간에 Hashtable에 넣고 Vector에 넣고 이러는거 하나도 없다. 알아서 한다는게 동영상의 내용으로 보인다. 뭐, DB에서 현재 하고 있는거랑 똑같자나? ETL툴들도 여러 데이터 소스에서 읽어와서 join , group by 하는거 다 된다. 특정한 모듈, 이를테면 DW에서 많이 쓰는 lookup 같은것도 CPU 여러개 쓰면서 잘 돈다. 데이터를 핸들링하는 과정이 단위 기능으로 쪼개졌기 때문이다.

자 그러면 이런 문제를 자연스럽게 예상할 수 있다. expression tree를 만드는게 얼마나 효율적인 것이냐. DB에서 실행 계획 만드는게 의외로 CPU많이 쓰는 작업임을 생각해보면 이런 문제도 분명히 생길거다. 글고 실행계획 재사용같은게 되려나? 앗. 프로그램이니까 컴파일이 되는구나-_-; 실행계획 재사용에 해당하는 문제는 없겠다. ㅋㅋㅋ 근데 만들어낸 expression tree가 진짜로 최적일거냐 하는 문제는 여전히 예상이 된다.  두개의 XML소스와 세개의 텍트스 소스와 하나의 DB에서 데이터를 읽어서 join을 한다고 치면 뭘 먼저 읽고 어떤 순서로 join 할지는 상황에 따라 최적값이 다를텐데. 뭐, 이건 DB 튜닝을 하다보면 맨날 만나는 문제지만. 그럴때는 지가 알아서 컴파일된 코드를 바꿀까???? ㅋㅋㅋ

적으면서 생각을 정리하다보니 비전은 원대하나 실무에서 검증되기엔 많은 난관이 있어보인다는 생각이 든다. 게다가... 객체도 잘 모르는 대다수의 개발자들이 함수형 프로그래밍도 공부해야 한다는 어려움도 예상이 되는구먼. 절차지향, 집합지향, 객체지향, AOP, 함수형 등등 뭐가 이리 많단 말이냐. ㅎㅎㅎㅎ


@제가 FP나 LINQ에는 전혀 경험이 없으니 위의 동영상을 깊이있게 이해하지 못하거나 오해하고 있는 부분이 있을 수 있습니다. 추가하실 말씀이 있으시면 덧글로 가르침을 주시면 감사하겠습니다.

Posted by maceo

대상 테이블 1500만건. 데이터 사이즈 6.6G
작업 : 클러스터드 인덱스 생성작업
maxdop 2 일 때 걸린 시간 : 3분 4초
maxdop 4 일 때 걸린 시간 : 2분 17초
maxdop 16 일 때 걸린 시간 : 4분 28초
maxdop 32 일 때 걸린 시간 : 12분 30초

maxdop 가 커지면 스레드들끼리 gather stream 작업을 하는데 SQL Server 2000은
그 부분에서 매우 비효율적인 것 같다. 2005 에서는 얼마나 좋아졌으려나....

Posted by maceo

파티션 테이블, 병렬계획 & 성능 고려사항들

http://blogs.msdn.com/sqlcat/archive/2005/11/30/498415.aspx
번역:박노철(maceo.park@gmail.com)

Q:SS2005 의 파티션 테이블에 쿼리를 날릴 때 어떤 종류의 병렬 계획이 나올 것이며 성능에는 어떤 영향을 미치는가?

A:먼저 파티셔닝에 대한 간략한 배경을 소개하겠다. SS2005의 테이블 파티셔닝은 관리와 가용성 측면에서 많은 향상을 가져왔다. 관리적 측면에서 보자면, 데이터 파티션에 대해서 메타데이터 스위치 인, 아웃을 가능케 했다(sliding window requirements를 지원한다. (뭔소리여? -_-)) 가용성 측면에는 온라인 인덱스 리빌드, 병렬 실행, 파일그룹의 piecemeal 리스토어가 가능하다.

이제 파티션 테이블이 성능에 어떤 영향을 미치는지 살펴보자. 파티셔닝을 사용하는지 아닌지에 관계없이 병렬 계획 선택여부는 CPU갯수, 쿼리 비용, 가용 메모리와 현재 workload 에 의해서 결정된다. 이 글에서 이야기하는 모든 것들은 병렬 계획이 가능할때만 유효한 것들이다.

쿼리가 하나의 파티션을 사용할 때 SS2005는 maxdop에 설정된 수치까지 여러개의 스레드를 사용해서 병렬로 데이터를 읽어올 수 있다. maxdop는 보통 시스템에 설치된 CPU갯수와 같은 값을 의미하는 0으로 설정되어 있다. 쿼리가 두개 이상의 파티션에서 데이터를 읽어올 때 파티션당 단 하나의 스레드만 사용될 수 있다.

만약 파티션 갯수가 maxdop와 같거나 더 적다면 데이터가 한쪽으로 치우침으로 인해서 CXPACKET 대기가 생길 수 있다. 파티션 갯수가 maxdop보다 크면 SS2005는 하나의 스레드가 특정 파티션에서 작업을 끝내면 자동적으로 다음 파티션으로 이동한다. 16개의 파티션을 가지고 있고 maxdop가 8이라면 첫 8개의 스레드는 파티션1-8에 대해서 작동한다. 작업이 끝난 첫번째 스레드는 파티션9에서 돌게 된다.

만약 8개 이상 CPU를 가진 기계라면, 최악의 경우는 두개의 파티션에 걸쳐있는 단일 SELECT문이다. (테이블1 참조) 테이블2에 노란색 강조 부분, 파티션 80,81과 Executes 컬럼의 스레드 숫자를 보면, 노란색 부분 이후의 단계에서 maxdop가 적용될 수 있음에도 불구하고 데이터를 읽어올 때는 파티션당 하나의 스레드만 돌아간다. (녹색 강조 참고)

Table 1: Retrieve 2 weeks of data

SELECT

SUM(Sales_Qty) as Sales_Qty,

SUM(Sale_Amt)  as Sales_Amount

FROM   SalesDB.dbo.Tbl_Fact_ Sales – Partitioned by week

WHERE  date_id between '20050703' and '20050716'

Table 2: Set Statistics Profile:  MAXDOP = 12

Rows	Executes	StmtText
1	1	SELECT SUM([Sales_Qty]) [Sales_Qty],SUM([Sale_Amt]) [Sales_Amount] FROM [SalesDB].[dbo].[Tbl_Fact_Sales] WHERE [date_id]>=@1 AND [date_id]<=@2
0	0	|--Compute Scalar(DEFINE:([Expr1002]=CASE WHEN [globalagg1008]=(0) THEN NULL ELSE [globalagg1010] END, [Expr1003]=CASE WHEN [globalagg1012]=(0) THEN NULL ELSE [globalagg1014] END))
1	1	|--Stream Aggregate(DEFINE:([globalagg1008]=SUM([partialagg1007]), [globalagg1010]=SUM([partialagg1009]), [globalagg1012]=SUM([partialagg1011]), [globalagg1014]=SUM([partialagg1013])))
2	1	|--Parallelism(Gather Streams)
2	12	|--Stream Aggregate(DEFINE:([partialagg1007]=COUNT_BIG([SalesDB].[dbo].[Tbl_Fact_Sales].[Sales_Qty] as [ss].[Sales_Qty]), [partialagg1009]=SUM([SalesDB].[dbo].[Tbl_Fact_Sales].[Sales_Qty] as [ss].[Sales_Qty]), [partialagg1011]=COUNT_BIG([SalesDB].[dbo].[Tbl_Fact_Sales].[Sale_Amt] as [ss].[Sale_Amt]), [partialagg1013]=SUM([SalesDB].[dbo].[Tbl_Fact_Sales].[Sale_Amt] as [ss].[Sale_Amt])))
20577235	12	|--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1006]) PARTITION ID:([PtnIds1006]))
2	12	|--Parallelism(Distribute Streams, Demand Partitioning)
2	1	|    |--Constant Scan(VALUES:(((80)),((81))))
20577235	2	|--Index Seek(OBJECT:([SalesDB].[dbo].[Tbl_Fact_Sales].[IX_Tbl_Fact_Sales_SKDteItmStrIDSalQtySalAmtDiscMkd] AS [ss]), SEEK:([ss].[SK_Date_ID] >= (20050703) AND [ss].[SK_Date_ID] <= (20050716)) ORDERED FORWARD PARTITION ID:([PtnIds1006]))

예를 들어 월단위로 파티션된 테라바이트급 매출 테이블이 있다고 하자. 일반적인 쿼리 패턴이라면, 이달과 전달 또는 이달과 1년전을 비교할 것이다.

Table 3:  MONTHLY Partitions

	WHERE clause	Partitions	Retrieval Parallelism
Single SELECT statement	SELECT …. WHERE DateCol BETWEEN ’10/1/2005’ and ‘11/30/2005’	2	1 thread per partition *
SELECT UNION SELECT [UNION SELECT]	Select …. Where DateCol between ’10/1/2005’ and ‘10/31/2005 UNION Select …. Where DateCol between ’11/1/2005’ and ‘11/30/2005	1 per select	MAXDOP per partition

Table 4:  WEEKLY Partitions:  Sales for November 1-15

	WHERE clause	Partitions	Retrieval Parallelism
Single SELECT statement	SELECT SUM(Sales) from WKSales WHERE DateCol BETWEEN ’11/1/2005’ and ‘11/15/2005’	3	1 thread per partition *
SELECT UNION SELECT [UNION SELECT]	SELECT SUM(Sales) from WKSales WHERE DateCol BETWEEN ’11/1/2005’ and ‘11/5/2005’ UNION SELECT SUM(Sales) from WKSales WHERE DateCol BETWEEN ’11/6/2005’ and ‘11/12/2005’ UNION SELECT SUM(Sales) from WKSales WHERE DateCol BETWEEN ’11/13/2005’ and ‘11/15/2005’	1 per select	MAXDOP per partition

비록 관리나 가용성 측면에서 어려움이 있긴 하지만 거대한 단일 테이블이 성능상 더 좋은 경우도 있다. 예를 들어 1TB 파티션 테이블에 두개의 파티션을 읽어야 하는 쿼리가 있다고 하면, SS2005는 파티션당 1개의 스레드만 할당한다. 하지만 SS2005는 1TB짜리 단일 테이블에 대해서는 maxdop를 적용하여 데이터를 읽어온다.

Table 5:  Monolithic BigSalesTable

	WHERE clause	Partitions	Retrieval Parallelism
Single SELECT statement	SELECT …. WHERE DateCol BETWEEN ’10/1/2005’ and ‘11/30/2005’	N/A	MAXDOP

Best Practices:

파티션과 병렬 실행의 퍼포먼스에 관한 위의 커멘트는 DW, 배치 프로세싱, 리포팅에 적용가능하다. 모든 DW가 병렬 쿼리를 허용하지는 않을 것이다. 병렬 계획은 시스템에 실행되는 쿼리가 몇개 없고 실행 시간을 최소화 하기 위해 가능한 많은 리소스를 사용하기를 위할 때 가장 효과적이다. 만약 DW가 이미 동시성이 매우 높은 환경이라면, 병렬 계획은 throughput이나 응답 시간을 향상시켜주지 않을 것이다. 이미 수많은 단일 스레드 쿼리들이 가용 자원을 대부분 소비하고 있을 것이기 때문이다. 최고의 퍼포먼스를 위해서, 동시성이 높은 OLTP시스템에서도 병렬 계획을 원하지는 않을 것이다.

파티셔닝 정밀도(일별, 주별, 월별)를 결정할 때 사용자들의 일반적인 쿼리 패턴을 고려해야 하며 CPU8개 이상의 시스템에서 최고의 성능을 위해서는 최소한 maxdop개의 파티션을 사용하도록 해야 한다. 테이블 3,4에서 보여준바와 같이 SQL문을 여러개의 단일 파티션 쿼리로 재작성해야 최고의 성능을 얻을 수 있다.

Posted by maceo

오늘 마이그레이션 테스트를 하다가 알아낸 사실.

다음과 같은 쿼리가 있다고 하자.

VERY_BIG_TABLE 의 데이터 갯수는  1억건, BIG_TABLE 의 갯수는 1천만건이다.
데이터 마이그레이션 중이므로 당연히 CPU 4개를 써서 한꺼번에 읽어서 hash join 으로 처리하기를 기대하고 위와 같이 만들었다. 그런데 돌려보니 평소에 걸리던 것보다 시간이 엄청 오래 걸린다. 실행계획을 보니 병렬 계획을 세우지 못하는게 아닌가!

도대체 이게 무슨 일인가 하여 테이블을 하나하나 빼가면서 여러 모로 살펴본 결과 원인은 두가지였다.

믿기지가 않겠지만 정말로 일어난 일이다. SQL Server 2000 sp4 에서 발생했다.

1번에 대해서는 그럴수도 있겠다는 생각이 든다. SQL Server 는 아주 가끔 병렬 계획을 세워서 쿼리를 수행하다가 Intra Query Paralleism Deadlock 을 일으킨다. 즉, 복수개의 CPU 들이 쿼리를 처리하다가 지들끼리 데드락을 잡아서 쿼리가 실패하는 경우다. 아무래도 복수개의 CPU가 같은 테이블을 self-join 해야 하는 상황이 되다보니 옵티마이저가 데드락을 우려하여 알아서 병렬 계획을 안세운 것이 아닌가 하는 생각이 든다.

2번에 대해서는... 전혀 알 수 없다. 도대체 function 하고 병렬 계획하고 무슨 상관이람-_-;
function 은 실행계획에 아예 나오지도 않는데 뭐 그런 문제랑 관련이 있는건지.. 흠....

Posted by maceo