#title 성능최적화-기본편

출처: http://purumae.tistory.com/173

SSB 최적화는 여러 면에서 바라볼 수 있습니다.
지금부터 하나씩 살펴보겠습니다.

1. 스토리지 구성 - tempdb 파일 분리

SQL Server에서 스토리지 구성의 기본 원칙은 데이터파일과 트랜잭션 로그 파일을 물리적으로 분리된 디스크 볼륨에 위치시키는 것입니다.

그런데, SSB의 QUEUE는 tempdb에 존재합니다.
따라서 주고 받는 메시지가 많으면 많을 수록 tempdb 데이터 파일과 트랜잭션 로그 파일에 대한 I/O가 증가하게 됩니다.

즉, SSB를 사용하여 대량 메시지를 전송하는 시스템이라면.. tempdb 파일을 별도의 디스크 볼륨에 할당해 주어야 합니다.


2. 한개의 대화에 여러개의 메시지를 전송한다.

BOL의 자습서와 직전 포스트의 예제에서는 메시지 1개를 보낼때마다 1번씩 BEGIN DIALOG CONVERSATION문을 사용했습니다. 바꿔말하면 대화마다 메시지를 한번만 전송하고 대화를 마치는 방식이었습니다.

하지만, 메시지를 보낼때마다 매번 대화를 BEGIN / END하는 작업이 반복되므로 성능이 좋을리 없습니다.

한번 대화를 시작하면 동일한 Dialog Handle을 재사용하도록 Dialog Pool을 구현하는 것이 좋습니다.


3. Dialog Handle은 여러 개를 생성하여 사용한다.

Dialog Handle을 재사용해야하지만.. 그렇다고 1개의 핸들을 통해 모든 메시지를 전송한다면 그것은 오히려 더 큰 문제를 야기할 수 있습니다.

이는 sys.sysdesend (Dialog Endpoint Send) 시스템 테이블에 경합이 발생하기 때문인데요. 내부적으로 사용되는 시스템 테이블에 대해서는 다음 포스트에서 다루도록 하겠습니다.

어쨌든.. Thread 당 1개의 핸들을 생성하는 것이 적당합니다.
(sp_who 등으로 SQL Server에 연결한 Application Server의 접속 세션 수를 참고할 수 있습니다.)


4. TARGET 서버의 Receive Queue에서 한번에 여러 개의 메시지를 꺼낸다.

2항의 권고대로 한번의 대화에 여러개의 메시지를 전송했다면, TARGET에서는 RECEIVE 문에서 TOP (1)이 아닌 TOP (n)을 사용할 수 있습니다.

QUEUE에서 1개씩 꺼내 처리하는 것과 n개씩 꺼내 처리하는 것의 성능 차이는 굳이 설명할 필요가 없을 것 같습니다.

개인적으로 n의 범위는 통상 1,000 ~ 10,000 정도가 적당한 것 같습니다.


5. 구현

INITIATOR

- SQL Server Service가 시작하면 자동으로 n개의 Dialog Handle을 생성하고, 생성한 핸들 값을 DialogPool테이블에 저장합니다.
- 150TrickEnableFlag 값은 현재 단계에서는 무시하고 code reading하시기 바랍니다.
- [150 Trick] 은 다음 포스트에서 다룰 예정입니다.
{{{
USE InitDB;
GO
 
-- Dialog Pool 설정 테이블 생성
CREATE TABLE dbo.DialogPoolConfig (
        _150TrickEnableFlag bit NOT NULL,
        DialogCount int NOT NULL
);
GO
 
}}}

{{{
-- Dialog Pool 테이블 생성
CREATE TABLE dbo.DialogPool (
        DialogPoolID int IDENTITY(0, 1) NOT NULL,
        DialogHandle uniqueidentifier NOT NULL,
 
        CONSTRAINT PK_DialogPool PRIMARY KEY (DialogPoolID)
);
GO
}}}
 
{{{
/**
version : 1
author : 김도열
e-mail : purumae@eyedentitygames.com
created date : 2011-06-21
description : 대화 핸들 풀의 설정값을 참조하여 대화핸들을 미리 생성합니다.
**/
 
CREATE PROCEDURE dbo.P_StartUp_CreateDialogs
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
 
DECLARE @i int = 1
        , @intDialogCount int = 0
        , @bit150TrickEnableFlag bit
        , @uidDialogHandle uniqueidentifier;
 
SELECT @intDialogCount = DialogCount * (CASE _150TrickEnableFlag WHEN 0 THEN 1 ELSE 150 END)
        , @bit150TrickEnableFlag = _150TrickEnableFlag
FROM dbo.DialogPoolConfig;

TRUNCATE TABLE dbo.DialogPool;
 
WHILE @i <= @intDialogCount
BEGIN
        BEGIN DIALOG CONVERSATION @uidDialogHandle
               FROM SERVICE InitDBService
               TO SERVICE N'TargetDBService'
               ON CONTRACT LoggingContract
               WITH ENCRYPTION = OFF;
 
        IF @bit150TrickEnableFlag = 0 OR (@bit150TrickEnableFlag = 1 AND (@i % 150) = 0)
               INSERT dbo.DialogPool (DialogHandle)
               VALUES (@uidDialogHandle);
 
        SET @i += 1;
END
GO
}}}


{{{
USE master;
GO
 
CREATE PROCEDURE dbo.P_StartUp_CreateDialogs
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
 
EXEC InitDB.dbo.P_StartUp_CreateDialogs;
 
RETURN 0;
GO

EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
GO

EXEC sp_configure 'scan for startup procs', '1';
RECONFIGURE;
GO

EXEC sp_procoption N'P_StartUp_CreateDialogs', 'STARTUP', 'on'
GO
}}}




- Dialog Pool에 저장된 Dialog Handle을 통해 메시지를 전송할 SP를 생성합니다.
- 1개의 Thread가 1개의 핸들을 사용하도록 "@@SPID % 생성한 핸들 수" 값을 사용합니다.
- 게임 서버와 같이 기동 시 SQL Server와 연결을 맺는 타입에서는 거의 완벽하게 매핑됩니다.
단, IIS와 같이 자체적으로 connection pool이 구현되었다면 완전하게 밸런싱되지 않습니다.
(그래도 reasonable한 선에서의 분배는 가능 할 것으로 생각됩니다.)
{{{
USE InitDB;
GO
 
/**
version : 1
author : 김도열
e-mail : purumae@eyedentitygames.com
created date : 2011-06-21
description : 대화 핸들을 얻습니다.
**/
 
CREATE PROCEDURE dbo.P_GetDialog
        @uidDialogHandle uniqueidentifier OUTPUT
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
 
SELECT @uidDialogHandle = DialogHandle
FROM dbo.DialogPool WITH (INDEX(PK_DialogPool), FORCESEEK)
WHERE DialogPoolID = @@SPID % (SELECT TOP (1) DialogCount FROM dbo.DialogPoolConfig WITH (NOLOCK))
OPTION (KEEPFIXED PLAN);
 
RETURN 0;
GO
}}}
 
{{{
/**
version : 1
author : 김도열
e-mail : purumae@eyedentitygames.com
created date : 2011-06-21
description : Dialog Pool의 특정 Dialog가 대화 종료되어 존재하지 않는 경우, 새 Dialog로 교체합니다.
**/
 
CREATE PROCEDURE dbo.P_RenewDialog
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
 
DECLARE @i int = 1
        , @j int
        , @uidDialogHandle uniqueidentifier;
 
SET @j = 
       CASE (SELECT _150TrickEnableFlag FROM dbo.DialogPoolConfig)
               WHEN 0 THEN 1
               ELSE 150
        END;
 
WHILE @i <= @j
BEGIN
        BEGIN DIALOG CONVERSATION @uidDialogHandle
               FROM SERVICE InitDBService
               TO SERVICE N'TargetDBService'
               ON CONTRACT LoggingContract
               WITH ENCRYPTION = OFF;
 
        SET @i += 1;
END
 
UPDATE DP
SET DialogHandle = @uidDialogHandle
FROM dbo.DialogPool DP WITH (INDEX(PK_DialogPool), FORCESEEK)
WHERE DialogPoolID = @@SPID % (SELECT TOP (1) DialogCount FROM dbo.DialogPoolConfig WITH (NOLOCK))
OPTION (KEEPFIXED PLAN);
 
RETURN 0;
GO
}}}
 
{{{
/**
version : 1
author : 김도열
e-mail : purumae@eyedentitygames.com
created date : 2011-06-28
description : GameLog 메세지를 전송합니다.
**/
 
CREATE PROCEDURE dbo.P_SendGameLogMessage
        @xmlMessage xml --// 전송할 메세지
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
 
DECLARE @intReturnValue int
        , @uidDialogHandle uniqueidentifier;
 
BEGIN TRY
        BEGIN TRANSACTION;
 
        /**_# Dialog Pool로부터 대화 핸들을 얻습니다.*/
        EXEC dbo.P_GetDialog @uidDialogHandle = @uidDialogHandle OUTPUT;
 
        /**_# 메시지를 전송합니다.*/
        SEND ON CONVERSATION @uidDialogHandle
               MESSAGE TYPE MsgToTargetDB (@xmlMessage);
 
        COMMIT TRANSACTION;
END TRY
BEGIN CATCH
        /**_# 대화 핸들이 존재하지 않으면, Dialog Pool에서 핸들을 갱신하고 재귀호출합니다.*/
        IF ERROR_NUMBER() = 8426
        BEGIN
               IF @@TRANCOUNT > 0
                       ROLLBACK TRANSACTION;
 
               EXEC dbo.P_RenewDialog;
               EXEC dbo.P_SendGameLogMessage @xmlMessage = @xmlMessage;
 
               RETURN 0;
        END
 
        /**_# 8426외의 에러는 Rethrow합니다.*/
        GOTO ErrorHandler;
END CATCH;
 
RETURN 0;
 
ErrorHandler:
IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;
 
DECLARE @nvcErrorMessage nvarchar(4000) = N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: ' + ERROR_MESSAGE()
        , @intErrorNumber int = ERROR_NUMBER()
        , @intErrorSeverity int = ERROR_SEVERITY()
        , @intErrorState int = ERROR_STATE()
        , @intErrorLine int = ERROR_LINE()
        , @nvcErrorProcedure nvarchar(128) = ERROR_PROCEDURE();
 
RAISERROR (@nvcErrorMessage, @intErrorSeverity, 1, @intErrorNumber, @intErrorSeverity, @intErrorState, @nvcErrorProcedure, @intErrorLine);
 
RETURN @intReturnValue;
GO
}}


TARGET

- TARGET 서버의 QUEUE 활성화 프로시져를 아래와 같이 수정합니다.
- RECEIVE TOP (1000) 을 사용하여, 한번에 1,000개의 메시지를 꺼내 처리하도록 합니다.
{{{
ALTER PROCEDURE dbo.P_TargetDBQueueActivate
AS
 
DECLARE @tblReceive table (
    seq int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    message_type_name sysname NOT NULL,
    message_body xml NULL,
    [conversation_handle] uniqueidentifier NOT NULL
);
 
DECLARE @i int = 1
    , @xmlMessage xml
    , @nvcMessageType sysname
    , @uidDialogHandle uniqueidentifier
    , @intDoc int;
 
WHILE (1 = 1)
BEGIN
    BEGIN TRANSACTION;
 
    WAITFOR (
        RECEIVE TOP (1000) message_type_name, message_body, [conversation_handle]
        FROM dbo.TargetDBQueue
        INTO @tblReceive
    ), TIMEOUT 3000;
 
    IF @@ROWCOUNT = 0
    BEGIN
        ROLLBACK TRANSACTION;
        BREAK;
    END
 
    WHILE 1 = 1
    BEGIN
        SELECT @xmlMessage = message_body
            , @nvcMessageType = message_type_name
            , @uidDialogHandle = [conversation_handle]
            , @i += 1
        FROM @tblReceive
        WHERE seq = @i;
 
        IF @@ROWCOUNT = 0
            BREAK;
 
        IF (@nvcMessageType = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error') OR (@nvcMessageType = N'MsgEndOfStream')
            END CONVERSATION @uidDialogHandle;
 
        ELSE IF (@nvcMessageType = N'MsgToTargetDB')
        BEGIN
            EXEC sp_xml_preparedocument @intDoc OUTPUT, @xmlMessage;
 
            INSERT dbo.GameLogs (LogDate, LogCode, LogString)
            SELECT LogDate, LogCode, LogString
            FROM OPENXML (@intDoc, N'/root/GameLog')
            WITH (
                LogDate datetime N'@LogDate',
                LogCode tinyint N'@LogCode',
                LogString nvarchar(100) N'@LogString'
            );
 
            EXEC sp_xml_removedocument @intDoc;
        END
    END
 
    DELETE @tblReceive;
 
    COMMIT TRANSACTION;
END
GO 
}}}


테스트

- INITIATOR 서버에서 아래 Query를 실행한 후 SQL Server Service를 재 시작합니다.
{{{
USE InitDB;
GO
 
DELETE dbo.DialogPoolConfig;
 
INSERT dbo.DialogPoolConfig (_150TrickEnableFlag, DialogCount)
VALUES (0, 10);
GO
}}}


- 이제 Dialog Pool에 10개의 핸들이 저장된 것을 아래 Query로 확인할 수 있습니다.
{{{
USE InitDB;
GO
 
SELECT * FROM dbo.DialogPool;
GO
}}}


- 서로 다른 여러 개의 세션에서 동시에 아래 Query를 실행하여 메시지를 전송합니다. (세션 당 3,000개)
{{{
USE InitDB;
GO
 
DECLARE @xmlMessage xml;
DECLARE @i int = 1;
 
WHILE @i <= 3000
BEGIN
        SET @xmlMessage = N'<root><GameLog LogDate="' + CONVERT(nvarchar(19), GETDATE(), 121) + N'" LogCode="1" LogString="abc"/></root>';
 
        EXEC dbo.P_SendGameLogMessage @xmlMessage;
 
        SET @i += 1;
END
GO
}}}



- TARGET 서버에 메시지가 적재되고 있는지 확인합니다.
{{{
USE TargetDB; 
GO

SELECT * FROM dbo.GameLogs;
GO


CLEAN
- 아래 Query를 사용하여 SSB 개체를 제거합니다.

INITIATOR
{{{
USE master;
GO
 
DROP ENDPOINT InitDBEndpoint;
DROP CERTIFICATE InitDBAuthCertPriv;
DROP MASTER KEY;
DROP LOGIN TargetDBProxy;
DROP CERTIFICATE TargetDBAuthCertPub;
DROP USER TargetDBProxy;
EXEC sp_procoption N'P_StartUp_CreateDialogs', 'STARTUP', 'off'
DROP PROCEDURE dbo.P_StartUp_CreateDialogs;
GO
}}}



TARGET
{{{
USE master;
GO
 
DROP DATABASE TargetDB;
DROP ENDPOINT TargetDBEndpoint;
DROP CERTIFICATE TargetDBAuthCertPriv;
DROP MASTER KEY;
DROP LOGIN InitDBProxy;
DROP CERTIFICATE InitDBAuthCertPub;
DROP USER InitDBProxy;
GO
}}}


다음 포스트에서는 위에 잠깐 언급된 시스템 테이블 sys.sysdesend와 150 Trick에 대해 알아보겠습니다.