Firebird에서 Insert SQL을 모니터링하려면

몇달 전에 파이어버드에서 대용량 벌크 인서트를 하는 방법을 알아봤었는데요. 물론 벌크 인서트는 단일 INSERT 문을 대량으로 날리는 것에 비하면 대단히 빠르기는 하지만, 그 목적상 당연히 단 하나의 SQL 문으로서는 아주 긴 실행 시간을 가지게 됩니다. (앞서의 예에서는 INSERT…SELECT 문에서 22초가 걸렸죠)
Firebird에서 고속으로 대용량 INSERT를 하려면

160만건의 레코드 개별 INSERT 문으로 인서트하는 경우, 전체 시간은 아주 오래 걸리긴 하지만, 루프 내에서 현재 진행 상황을 ProgressBar 등으로 표시하는 데에는 아무 어려움이 없습니다. 하지만 벌크 인서트의 INSERT…SELECT 문에서는 단일 SQL문에 대해 22초나 걸리기 때문에, 그 사이 현재의 인서트 상태가 어느 정도 진행되고 있는지 알아내어서 사용자에게 보여줄 방법이 묘연합니다.

물론 벌크 인서트 외에도 이렇게 장시간 실행되는 단일 SQL문의 경우엔 항상 마찬가지가 됩니다. 대량 INSERT 뿐만 아니라 대량의 레코드들을 가져오는 SELECT 문에서도 같은 문제가 생기겠지요. 오래 걸리는 작업에서 사용자에게 현재 상태를 적절히 보여주는 것은 유저 인터페이스에서 중요한 문제들 중 하나입니다.

이런 이유로, 파이어버드의 동작을 모니터링을 할 방법이 필요하게 됩니다. 다행히도, 파이어버드의 2.1 버전에서 데이터베이스 모니터링에 필요한 기능들이 추가되었고, 다시 2.5 버전에서도 새로운 모니터링 기능들이 추가되었습니다. (이번 포스트에서 설명드릴 SQL 모니터링 방법은 파이어버드 2.1에서 추가된 기능입니다)

파이어버드 2.1에서 추가된 모니터링 방법은, 모니터링을 위한 특수 테이블들을 이용하는 것입니다. (이런 방식은 오라클과 MS SQL서버에서도 사용되고 있는 방법이죠) 데이터베이스의 여러 상태 정보를 가지고 있는 특수 테이블들을 따로 마련해놓은 것입니다. 이런 모니터링 테이블에 대해서는, 파이어버드의 설치 디렉토리 아래의 doc 디렉토리에 있는 README.monitoring_tables.txt 파일에 간략한 설명이 있습니다.

모니터링 관련의 테이블 이름들만 간략하게 나열해보면…
MON$DATABASE : 연결된 데이터베이스들
MON$ATTACHMENTS : 연결된 클라이언트들
MON$TRANSACTIONS : 시작된 트랜잭션들
MON$STATEMENTS : 프리패어된 SQL문들
MON$CALL_STACK : 실행중인 PSQL의 콜 스택
MON$IO_STATS : I/O 상태 정보
MON$RECORD_STATS : 레코드 레벨 상태 정보
MON$MEMORY_USAGE : 현재 메모리 사용 상태 (2.5 버전에서 추가)
MON$CONTEXT_VARIABLES : 알려진 컨텍스트 변수들 (2.5 버전에서 추가)

문서를 뒤져봐도 저 텍스트 파일에 나온 간략한 설명 정도의 정보밖에 없어서… SQL 문 모니터링을 위해서는 각각 직접 돌려보고 삽질을 할 수 밖에 없더군요. 반나절 정도 걸려서 알아낸 결과에는, 이중 세 개의 테이블이 필요했습니다.

MON$RECORD_STATS
이름처럼, 이 테이블이 가장 중요한 테이블입니다. 레코드 단위의 상태를 알려주거든요. 이 테이블에는 순차 읽기, 인덱스 읽기, 레코드 인서트, 레코드 업데이트, 레코드 딜리트 등의 숫자가 나옵니다. 이 테이블에서 바로 MON$RECORD_INSERTS 컬럼이 해당 세션에서 인서트중인 레코드의 수를 표시해줍니다.

그런데 이 테이블의 레코드들은 MON$STAT_ID에 따라 나오는 것이라, 현재 인서트 SQL을 날린 프로세스로부터 이 MON$STAT_ID 값을 알아낼 방법이 필요합니다. 이 MON$STAT_ID라는 컬럼이 여러 테이블에 존재하는데, 좀 난처하게도 테이블에 따라 뜻하는 의미가 좀 다른 경우가 있습니다. 이 MON$RECORD_STATS 테이블에서는 세션ID 정도의 의미인 것으로 보입니다. 따라서, 클라이언트 프로세스로부터 세션 ID의 의미를 가지는 MON$STAT_ID를 알아내야 합니다.

select
  MON$RECORD_INSERTS
from MON$RECORD_STATS 
where
  MON$STAT_ID=????

select

MON$RECORD_INSERTS

from MON$RECORD_STATS

where

MON$STAT_ID=????

MON$STATEMENTS
이 MON$STATEMENTS 테이블에서 STAT_ID를 알아낼 수 있습니다. 그런데 이번에는 이 값을 알아내기 위해선 ATTACHMENT_ID 라는 값이 필요합니다. 이 ATTACHMENT_ID는 커넥션 아이디라고 보면 됩니다. (여기서 말하는 Attach가 Connect와 일맥상통하는 의미인 듯)

select
  MON$STAT_ID
from MON$STATEMENTS
where
  MON$ATTACHMENT_ID=(알아낸ATTACHMENT_ID)

select

MON$STAT_ID

from MON$STATEMENTS

where

MON$ATTACHMENT_ID=(알아낸ATTACHMENT_ID)

MON$ATTACHMENTS
마지막으로, 이 테이블에는 연결된 클라이언트 정보가 들어있습니다. 각 클라이언트가 연결된 데이터베이스, 계정 이름, ROLE 이름, 연결 프로토콜, 클라이언트 IP 주소, 클라이언트 프로세스 ID, 접속 시간, 클라이언트 프로세스의 전체경로, STAT_ID 이런 정도의 필드들이 들어 있습니다.

바로 이 테이블에서 위에서 필요했던 ATTACHMENT_ID를 알아낼 수 있습니다. 그리고 이 테이블의 컬럼 중에 SQL을 날린 프로그램의 프로세스ID, 즉 MON$REMOTE_PID 컬럼이 있습니다. 즉, 아래의 SQL 문과 같이 PID를 넘겨서 연결 ID를 알아낼 수 있습니다.

select
  MON$ATTACHMENT_ID
from MON$ATTACHMENTS
where
  MON$REMOTE_PID=(프로세스ID)

select

MON$ATTACHMENT_ID

from MON$ATTACHMENTS

where

MON$REMOTE_PID=(프로세스ID)

최종적으로, 아래와 같이 조합된 모니터링 SQL문을 통해, SQL을 날린 프로그램의 PID로부터 이 순간 INSERT 되고 있는 레코드의 누적 갯수를 알아낼 수 있게 되었습니다.

select
  MON$RECORD_INSERTS
from MON$RECORD_STATS
where
  MON$STAT_ID=
    (select MON$STAT_ID from MON$STATEMENTS where MON$ATTACHMENT_ID=
      (select MON$ATTACHMENT_ID from MON$ATTACHMENTS where MON$REMOTE_PID=[PID])
    )

select

MON$RECORD_INSERTS

from MON$RECORD_STATS

where

MON$STAT_ID=

(select MON$STAT_ID from MON$STATEMENTS where MON$ATTACHMENT_ID=

(select MON$ATTACHMENT_ID from MON$ATTACHMENTS where MON$REMOTE_PID=[PID])

)

즉, 시간이 오래 걸리는 INSERT 문을 쓰레드로 실행시키고(프로그램 실행에 블로킹이 걸리니까요), 메인 쓰레드에서는 일정한 인터벌마다(예를 들면 1초마다) 위의 모니터링 SQL문을 날림으로써 현재 몇 레코드까지 인서트가 완료되었는지를 알아낼 수 있게 되었습니다.

여기에 한가지 주의할 점이 있습니다. 파이어버드 SQL의 동작 특성상, 위의 모니터링 SQL을 실행하는 경우에도 즉시 새로운 트랜잭션이 시작되는데, 트랜잭션이 계속되는 상황에서는 위의 모니터링 SQL문을 다시 실행해도 새로운 결과가 보이질 않습니다. 이것은 파이어버드가 버저닝 방식의 데이터베이스(versioning database)이기 때문에, 한 트랜잭션이 진행인 상태에서는 동일한 테이블 사본을 보고 있기 때문입니다. 즉, 1초 후 새로운 레코드 갯수로 업데이트된 값을 보려면, 이전의 실행 때와 같은 트랜잭션 중이면 안되고 반드시 이전 트랜잭션을 rollback하거나 commit한 후라야 합니다.

이런 동작을 델파이 코드로 보자면 다음과 같은 형식이어야 합니다. (물론 qrSqlMonitor의 SQL문에는 사전에 위의 모니터링 SQL문을 넣어놓은 상태이지요)

procedure TfrmMain.Timer1Timer(Sender: TObject);
begin
  conFirebird.StartTransaction;
  try
    qrSqlMonitor.Open;
    pbStep.Position := qrSqlMonitor.Fields[0].AsInteger;
    qrSqlMonitor.Close;
  finally
    conFirebird.Rollback;
  end;
end;

procedure TfrmMain.Timer1Timer(Sender: TObject);

begin

conFirebird.StartTransaction;

try

qrSqlMonitor.Open;

pbStep.Position := qrSqlMonitor.Fields[0].AsInteger;

qrSqlMonitor.Close;

finally

conFirebird.Rollback;

end;

동일한 코드가 C++빌더에서는 다음과 같이 되겠지요.

void __fastcall TfrmMain::Timer1Timer(TObject *Sender)
{
    conFirebird->StartTransaction();
    try
    {
        qrSqlMonitor->Open();
        pbStep->Position = qrSqlMonitor->Fields->Fields[0]->AsInteger;
        qrSqlMonitor->Close();
    }
    __finally
    {
        conFirebird->Rollback();
    }
}

void __fastcall TfrmMain::Timer1Timer(TObject *Sender)

{

conFirebird->StartTransaction();

try

{

qrSqlMonitor->Open();

pbStep->Position = qrSqlMonitor->Fields->Fields[0]->AsInteger;

qrSqlMonitor->Close();

}

__finally

{

conFirebird->Rollback();

}

이 모니터링 테이블들이 추가된 것은 파이어버드 2.1 부터였으니까, 베타가 아닌 정식 버전을 기준으로 하더라도 2008년부터 지원되어온 것입니다. 그런데 의외로 파이어버드 모니터링에 대한 정보가 너무 적더군요. 앞으로도 기회가 있으면 파이어버드 모니터링에 대해 더 써보도록 하겠습니다.

참고로, 이런 모니터링은 파이어버드의 서버 버전에만 적용 가능합니다. 임베디드 버전의 경우 하나의 프로세스에 하나의 인스턴스로 동작하기 때문에, 두 개의 연결을 통해 한 연결에서는 인서트 작업을, 다른 한 연결에서는 모니터링을 수행할 수가 없습니다. 이건 임베디드라는 컨셉 자체의 한계인 셈이죠.

Firebird에서 Insert SQL을 모니터링하려면

1 comment for “Firebird에서 Insert SQL을 모니터링하려면”

답글 남기기 응답 취소

Post navigation

1 comment for “Firebird에서 Insert SQL을 모니터링하려면”

답글 남기기 응답 취소