Skip to main content

블로그

2 posts tagged with "데이터베이스"

View All Tags

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분만에 완료되는것을 확인

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

참고자료#

Sql Server Index

sykim

sykim

Sql Server Page 구성도#

데이터베이스에서 데이터 파일에 할당되는 디스크공간은 논리적인 페이지로 나뉘면서 연속적인 숫자가 페이지에 매겨진다.

책이 page로 구성되어 있는 것처럼 SQL 서버도 Page로 구성되어 있으며 크기는 8KB 이다.

SQL서버의 가장 기본적인 단위이며, 실제로 SQL서버에 데이터의 접근시에는 페이지 단위로 접근합니다.

페이지는 대부분 Data로 구성되어 있지만 일부 페이지는 인덱스(데이터 위치), 텍스트/이미지, 등등으로도 구성되어 있다.

각 페이지는 96바이트의 헤더로 구성되어 있으며, 헤더는 페이지에 대한 시스템 정보를 저장한다.

sql-server-page 구성

Extents#

Sql Server의 Extent는 공간 관리의 기본 단위이며, 하나의 Extent는 연속하는 8페이지이다.

일반적으로 신규 테이블이나 인덱스는 Mixed extent의 페이지를 할당한다. (효율을 위해서)

이후 해당 테이블이나 인덱스가 8페이지로 증가하면 후속 할당을 위해서 uniform extent를 사용하도록 전환된다.

(SQL Server 2016부터 모든 할당의 기본값은 uniform extent를 사용)

sql-server-extent 구성

typecontent
mixed extent최대 8개의 다른 개체(테이블이나 인덱스등)이 공유된다. (페이지 단위)
uniform extent단일 개체가 모든 8페이지를 소유하며, 전체 페이지는 소유개체만 사용가능.

PFS 페이지#

PFS(Page Free Spece) 페이지는 Page 할당여부 및 빈 공간의 양을 1바이트로 표시한다.

PFS 페이지는 8088개의 페이지(64MB)의 빈공간을 추적하며, 8088개 페이지 이후에는 새로운 PFS 페이지가 생성된다.

페이지 상태 비어 있음 1 ~ 50% 채워짐 51 ~ 80% 채워짐 81 ~ 95% 채워짐 96 ~ 100% 채워짐

Extent 할당 및 빈 공간 관리#

빈 공간 추적#

PFS(Page Free Space) 페이지는 개별 페이지의 할당 여부 및 각 페이지에 있는 빈 공간의 양과 같은 페이지의 할당 상태를 기록하고. PFS는 각 페이지에 1바이트를 사용하여 페이지의 할당 여부를 기록하고 할당된 경우 페이지의 상태를 비어 있음, 1~50% 채워짐, 51~80% 채워짐, 81~95% 채워짐 또는 96~100% 채워짐으로 기록을 진행한다.

개체에 익스텐트가 할당된 후에는 SQL Server 데이터베이스 엔진에서 PFS 페이지를 사용하여 익스텐트의 할당된 페이지 또는 빈 페이지를 기록합니다. 이 정보는 SQL Server 데이터베이스 엔진에서 새 페이지를 할당해야 할 때 사용된다.

추적되는 각 추가 범위에 대한 새 PFS, GAM 또는 SGAM 페이지가 데이터 파일에 추가된다.

따라서 첫 번째 PFS 페이지 뒤에 8,088페이지의 새 PFS 페이지가 있고, 그다음에는 8,088페이지 간격으로 추가 PFS 페이지가 나온다.

밑에 그림을 페이지1할당 후 PFS 페이지로 페이지가 관리되고, 그 뒤에 GAM 페이지 뒤에 64,000개 익스텐트의 새 GAM 페이지가 있고, 그다음에 나오는 64,000개 익스텐트를 추적한다. (이후 64,000개 익스텐트 간격으로 시퀀스가 계속된다)

sql-server-extent 관리

Sql Server Index#

인덱스가 없을경우 아래 그림처럼 저장된다.

sql-server-No-Index

Clusterd Index#

클러스터 인덱스 구조에서 인덱스 페이지의 루트 노드 및 중간 노드들은 인덱스 행을 포함하게 구성되어 있다.

리프 노드들은 기본 테이블의 데이터 페이지로 구성되어 있으며 기본 테이블 데이터 페이지의 데이터 값들은 인덱스 값을 기준으로 정렬된다.

인덱스 페이지를 통해서 인덱스를 통한 데이터 검색을 할 경우 해당하는 데이터 페이지를 찾아갈 수 있다.

sql-server-Index

클러스터 인덱스를 생성할경우 아래 그림처럼 생성이 된다.

sql-server-cluster-index

Non Clusterd Index#

인덱스 페이지의 루트 페이지에는 클러스터와 동일하게 인덱스키와 리프페이지의 페이지 번호로 구성된다.

하지만 클러스터 인덱스와는 다르게 인덱스 페이지의 리프 페이지는 데이터 페이지가 아닌 데이터 페이지의 포인터로

구성된다.(실제로는 페이지 번호 + 행번호(RID)로 구성)

sql-server-non-cluster-index

비 클러스터 인덱스에서 조회를 진행한다면

sql-server-non-cluster-search

  • 첫번째로 인덱스의 루트페이지를 접근해서 찾는 값이 어떤 리프 페이지에 있는지 확인
  • 리프 페이지로 이동 후에 페이지의 내부 행들을 검색해서 데이터의 포인터 정보에 접근
  • 포인터 정보를 기반으로 데이터 페이지에 접근

검색 과정에서 총 3개의 페이지(루트 페이지 + 리프 페이지 + 데이터 페이지)를 참조하게 됨.

참고자료#