Skip to main content

Sql Server Bulk Work

sykim

sykim

Sql Server Bulk Working#

이번에 단독서버를 만드는 업체를 위해 기존에 데이터를 백업하여 작업하는 사항이 생겼다.

현재 운영중인 Sql Server에서 대량 추가, 수정, 삭제 작업을 요구할 때 어떻게 효율적으로 처리해야 하는지에 대한 방법들을 적용시키고 테스트를 진행해보았다.

일반적으로 몇만개의 데이터를 처리할 경우에는 DML 명령어로 처리하면 초~분 단위로 처리가 가능하다.

하지만, 100만개 이상의 데이터가 있는경우 수십분, 수시간의 시간이 소요될 수 있다.

아이랩의 결재정보와 관련된 테이블의 경우 2억개 이상의 Row 데이터가 입력되어있다.

Bulk Insert, Update#

Insert#

Sql Server Bulk Insert

  • 대량 Insert의 경우 가지고 있는 데이터를 Bulk Insert 문을 활용하여 작업한다.
  • 대량의 데이터를 가지고 있는 테이블을 다른 테이블로 옮기려면 insert into + select 문을 사용한다면 Bulk Insert 동작을 발생할 수 있도록 한다.
insert into TestTable
(Id, Name, Size, Crc, Contents, ContentType, CompressedType, CompressedSize, Archived, Created)
select
Id, Name, Size, Crc, Contents, ContentType, CompressedType, CompressedSize, Archived, Created
from BigDataTable -- 1,000,000 Row Table
Order By Id -- PK로 OrderBy로 한다면 인덱스 단편화 없이 Insert가 가능

다른 테이블에서 옮길경우 Index의 적절한 활용도 중요하다.

만약 대량의 데이터가 있는 테이블에서 Index가 없는 조건으로 Table Scan이 발생할 경우 select 절의 속도가 굉장히 오래 소요되기 때문에 Data Search 부분에서 오래 소요될 수 있다.

전체 데이터를 다 옮겨야한다면 Order By PK 조건을 추가하여 진행하는것이 좋다.

Update#

Update의 경우 Index 조건을 잘 맞춰주고 몇십만건 이상의 데이터라면 일정부분 잘라서 작업하는게 좋다.

대량 작업으로 인한 과도한 메모리 사용 및 Dead Lock 방지

select 1
WHILE @@ROWCOUNT > 0
BEGIN
update TOP (10000) dbo.EXPRESSES
set ADDRESS = 'abc'
where Client_Id = 1
END

Bulk Delete#

DELETE 작업의 경우 Insert, Update보다 발생하는 엄청난 양의 트랜잭션 로그가 고려된다.

Sql Server의 경우 백업모드가 3가지가 존재한다.

  • SIMPLE
  • BULK_LOGGED
  • FULL

BULK_LOGGED의 경우 대량 로그변경이 일어날경우, FULL 백업 설정의 경우 모든 수정사항이 트랜잭션 로그가 저장되므로 실제 작업을 할때는 Simple 모드에서 설정 후 작업하는게 좋다.

FULL 백업모드에서 몇천만개의 데이터를 추가, 수정, 삭제하면 오랜 시간이 소요되며 트랜잭션 로그파일의 크기가 순식간에 100GB가 넘게되버리는 마법을 체험할 수 있다.

고려할 사항#

  • SIMPLE 복구 모드로 설정
  • 전체 백업 만들기
  • 데이터 삭제
  • 인덱스 다시 작성
  • FULL 복구 모드로 다시 설정

데이터를 옮기기 위해#

아이랩의 경우 메인 데이터파일의 용량이 약 500GB, 첨부파일 관련 데이터파일의 용량이 약 1.7TB

  1. SIMPLE 복구 모드 설정
  2. 전체 백업 만들기(31분 소요)
  • 백업자료 생성시작 2021.05.19 22:55
  • 백업자료 생성완료 2021.05.19 23:26
  1. 새로운 데이터베이스에 백업하기(1시간 6분 소요)
  • 백업시작 2021.05.19 23:45
  • 백업완료 2021.05.20 00:51
  1. 필요한 데이터만 남기고 삭제하기

데이터 삭제#

아이랩의 경우는 기관마다 기관번호로 구분되어있어 조건을 설정하여 Delete문을 작성하기는 쉬웠지만 시간상에 문제가 발생했다.

특정기관의 데이터만 남기고 백업본을 남기기 위해서..

DML 명령어로 여러 테이블에서 수천만개의 데이터를 삭제하는 동작을 실행 시 최소 4시간 이상이 소요되는 문제가 발생

-- 6,044,679개의 데이터
-- 17번 기관은 34,254 데이터
SELECT 1
WHILE @@ROWCOUNT > 0
BEGIN
delete TOP (10000) dbo.EXPRESSES --10000건씩 삭제하는데 약 10 ~ 15초의 시간이 걸린다.
where ClientId <> 17
and REQUEST_ID not in (select Request_Id from dbo.Requests where Client_Id = 17)
END
-- 6,000,000개의 데이터가 삭제되는데 대략 150분의 시간이 필요하다.
-- 테이블이 300개가 넘고, 특정 테이블은 2억개 이상의 데이터를 가지고 있다.

TRUNCATE 활용#

기존 데이터를 임시 테이블로 Bulk Insert하여 옮긴 후 다시 원본 테이블로 Bulk Insert 하는 작업으로 시간을 단축

TRUNCATE의 경우 DDL 단위의 명령어로 테이블에 기본정보는 유지하고 모든 데이터를 지울 수 있는데 0.1초만에 처리가 가능하다.

-- dbo.EXPRESSES
IF OBJECT_ID('dbo.EXPRESSES_intermediate', 'U') IS NOT NULL
DROP TABLE dbo.EXPRESSES_intermediate;
GO
-- 6,044,679개의 데이터에서 17번 기관은 34,254 데이터만 옮겨둔다.
SELECT * INTO dbo.EXPRESSES_intermediate
FROM dbo.EXPRESSES
WHERE ClientId = 17
TRUNCATE TABLE dbo.EXPRESSES -- 원본 테이블 비우기
SET IDENTITY_INSERT dbo.EXPRESSES ON
INSERT INTO dbo.EXPRESSES (EXPRESS_ID,REQUEST_ID,ZIP_CODE,ZIP_ADDRESS,DETAIL_ADDRESS,EXPRESS_TYPE,CUSTOMER_NAME,EXPRESS_DATE,PRINT_QUANTITY,EXPRESS_GUBUN,ClientId,TelNumber)
SELECT EXPRESS_ID,REQUEST_ID,ZIP_CODE,ZIP_ADDRESS,DETAIL_ADDRESS,EXPRESS_TYPE,CUSTOMER_NAME,EXPRESS_DATE,PRINT_QUANTITY,EXPRESS_GUBUN,ClientId,TelNumber
FROM dbo.EXPRESSES_intermediate
ORDER BY EXPRESS_ID
SET IDENTITY_INSERT dbo.EXPRESSES OFF

위에 작업으로 기존 1시간30분 이상의 작업에서 1분으로 단축

기존 DB에서 새로운 DB로 옮기기#

WAFS의 크기#

WAFS DB는 1.7TB이며 테이블은 한개만 사용하지만 용량이 너무 크기때문에 백업 생성 후 옮기기가 너무 부담스러운 문제가 발생

새로운 데이터베이스를 생성하여 같은 테이블을 생성 후 기존 데이터베이스 정보를 옮기는 전략

insert into NEW_WAFS.dbo.files (
Id, Name, Size, Crc, Contents, ContentType, CompressedType, CompressedSize, Archived, Created
)
select Id, Name, Size, Crc, Contents, ContentType, CompressedType, CompressedSize, Archived, Created
from wafs.dbo.Files
where ClientId = 17 -- 1.7TB가 존재하는 테이블이지만 특정기관만 선택해보면 10GB 미만이다.

고려사항#

  • 새로운 데이터 베이스 생성 NEW_WAFS
  • 기존 데이터베이스에서 특정 데이터를 옮기기

만약 백업을 생성해서 진행했다면 대략 4시간이 소요될 수 있었지만, 해당 작업으로 3분만에 완료되는것을 확인

혹시 모를 상황에 대비해 반드시 데이터는 백업을 해두고 테스트를 진행해자

참고자료#