컬럼스토어 인덱스 대용량 데이터 로드

Column Store Index Bulk Load Data

 

  • Version : SQL Server 2012, 2014

 

클러스터 컬럼 스토어 인덱스 대용량 데이터 로드

이전 포스팅에서 컬럼스토어 인덱스에 대한 동시성 및 인서트 동시성에 대해서 살펴 보았다.

 

이번 포스트는 대용량 데이터 로드(Bulk load)를 통해 데이터가 삽입될 때 잠금 동작에 대해서 설명한다. 테스트용 기본 테이블을 생성하고 더미 데이터를 생성한다.

Create table t_bulkload (

accountkey int not null,

accountdescription nvarchar (50),

accounttype nvarchar(50),

AccountCodeAlternatekey int

)

go

 

-- Let us prepare the data

-- insert 110K rows into a regular table

 

begin tran

    declare @i int = 0

 

    while (@i < 110000)

    begin

        insert into t_bulkload values (@i, 'description', 'dummy-accounttype', @i*2)

        set @i = @i + 1

    end

 

commit

 

커맨드 창에서 BCP명령을 사용하여 데이터를 파일로 생성한다.

bcp adventureworksDW2012..t_bulkload out c:\t_bulkoad.dat -c -T

 

 

테스트를 위해 만든 테이블을 truncate 하고 클러스터 컬럼스토어 인덱스를 생성한다. 테이블에 행이 없기 때문에 ROWGROUP은 없다.

--truncate the table

Truncate table t_bulkload

 

-- convert row clustered index into clustered columnstore index

CREATE CLUSTERED COLUMNSTORE index t_bulkload_cci on t_bulkload

 

이제 배치 크기를 103000 크기로 하고 대량 데이터를 로드 한다. 그리고 컬럼스토어 RowGroup 정보를 확인한다.

세션 1

세션 2

-- now bulkload the data

begin tran

bulk insert t_bulkload

FROM 'c:\t_bulkoad.dat'

WITH

(

BATCHSIZE = 103000

)

 

 
 

select * from sys.column_store_row_groups where object_id = object_id('t_bulkload')

 

 

위 그림은 두 개의 행 그룹이 있는 것을 보여준다. 첫 번째 행 row_group_id = 0 그룹은 103000행을 압축한다. 이는 BATCHSIZE >= 102400 일때 SQL Server가 직접 행 그룹을 압축하기 때문이다. 직접 행 압축은 행 델타 그룹을 사용하지 않기 때문에 SQL Server 로깅을 최소화 할 수 있다. 그리고 튜플 데이터를 이동 할 필요가 없다.

 

나머지 7천 데이터 파일은 행 델타 그룹 삽입(데이터파일은 110000 행이다)에서 열이 부족하기 때문에 행 그룹이 계속 닫혀 있지 않음을 의미하는 OPEN 상태로 표시되고 있다. 수 백만 행이 표시 되었을 때 백그라운드의 튜플 이동기에 의해 닫히고 압축을 할 것이다.

 

잠금 상태를 살펴 보자. 델타 행 그룹 및 압축 행 그룹 모두에서 X 잠금을 확인 할 수 있다. 행 그룹 레벨에서 잠금 오버헤드를 최소화 한다.

select

request_session_id as spid,

resource_type as rt,

resource_database_id as rdb,

(case resource_type

        WHEN 'OBJECT' then object_name(resource_associated_entity_id)

        WHEN 'DATABASE' then ' '

        ELSE (select object_name(object_id)

                from sys.partitions

                where hobt_id=resource_associated_entity_id)

END) as objname,

resource_description as rd,

request_mode as rm,

request_status as rs

from sys.dm_tran_locks

 

 

 

현재 대용량 데이터 로드가 실행된 상태에서 다른 세션에서 행을 삽입할 경우 어떤 현상이 발생 하는지 살펴보자.

세션 3

세션 4

begin tran

    insert into t_bulkload values (-1, 'single row', 'single row', -1)

 
 

select

request_session_id as spid,

resource_type as rt,

resource_database_id as rdb,

(case resource_type

        WHEN 'OBJECT' then object_name(resource_associated_entity_id)

        WHEN 'DATABASE' then ' '

        ELSE (select object_name(object_id)

                from sys.partitions

                where hobt_id=resource_associated_entity_id)

END) as objname,

resource_description as rd,

request_mode as rm,

request_status as rs

from sys.dm_tran_locks

 

 

한 행을 입력하였지만 현재 행 그룹 row_group_id = 1에 X 잠금을 보유하고 있기 때문에 다음과 같은 새로운 델타 행 그룹에 삽입된 것을 확인 할 수 있다.

 

 

[참고자료]

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2014/07/28/clustered-column-store-index-bulk-loading-the-data.aspx

 

 

2014-09-29 / 강성욱 / http://sqlmvp.kr

 

SQL Server 2012, 컬럼스토어 인덱스, 행 그룹, Column Store Index, 메모리 인덱스, SQL, 데이터베이스, 대용량 데이터베이스, Delta Row Group, Compress Row Group, bulk load, 대용량데이터로드

컬럼스토어 인덱스 INSERT 작업과 동시성

Column Store Index Concurrency with INSERT

 

  • Version : SQL Server 2012, 2014

 

클러스터 컬럼 스토어 INSERT

이전 블로그에 설명한 바와 같이 클러스터된 컬럼스토어 인덱스는 DW 시나리오에서 빠른 쿼리 성능으로 데이터로드에 최적화 되어 있다. 대량의 인서트 작업에서도 DW 쿼리는 커밋되지 않은 읽기(Read Uncommitted) 격리 수준에서 병렬로 데이터를 로드 할 수 있다.

 

데이터가 동시에 인서트 될 때 잠금 동작에 대해서 알아 본다.

Version : SQL Server 2014

CREATE TABLE [dbo].[T_ACCOUNT](

[accountkey] [int] IDENTITY(1,1) NOT NULL,

[accountdescription] [nvarchar](50) NULL

) ON [PRIMARY]

 

-- create a CCI

CREATE CLUSTERED COLUMNSTORE INDEX ACCOUNT_CCI ON T_ACCOUNT

 

 

INSERT Operations

한 행을 인서트하고 잠금을 살펴본다. 참고로 트랜잭션을 커밋하지 않았다.

세션1

세션 2

begin tran

    insert into T_ACCOUNT (accountdescription ) values ('row-1');

 
 

select

    request_session_id as spid,

    resource_type as rt,

    resource_database_id as rdb,

    (case resource_type

        WHEN 'OBJECT' then object_name(resource_associated_entity_id)

        WHEN 'DATABASE' then ' '

        ELSE (select object_name(object_id)

            from sys.partitions

            where hobt_id=resource_associated_entity_id)

    END) as objname,

    resource_description as rd,

    request_mode as rm,

    request_status as rs

from sys.dm_tran_locks

 

다른 세션에서 행을 인서트한다. 그리고 잠금 상태를 확인 한다. 세션 54에서 두 번째 트랜잭션이 같은 RowGroup에 행을 삽입한 것에 유의한다. 동시 인서트로 서로를 차단하지 않고 RowGroup 데이터를 로드 할 수 있다.

세션 2

세션 3

 

begin tran

    insert into T_ACCOUNT (accountdescription ) values ('row-2');

select

    request_session_id as spid,

    resource_type as rt,

    resource_database_id as rdb,

    (case resource_type

        WHEN 'OBJECT' then object_name(resource_associated_entity_id)

        WHEN 'DATABASE' then ' '

        ELSE (select object_name(object_id)

            from sys.partitions

            where hobt_id=resource_associated_entity_id)

    END) as objname,

    resource_description as rd,

    request_mode as rm,

    request_status as rs

from sys.dm_tran_locks

 

 

 

요약하면 CCI에 삽입은 동일한 Delta RowGroup에 인서트 작업과 데이터 로드시 서로를 차단하지 않는다.

 

[참고자료]

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2014/07/27/clustered-column-store-index-concurrency-with-insert-operations.aspx

 

2014-09-25 / 강성욱 / http://sqlmvp.kr

 

SQL Server 2012, 컬럼스토어 인덱스, 행 그룹, Column Store Index, 메모리 인덱스, SQL, 데이터베이스, 대용량 데이터베이스, Delta Row Group, Compress Row Group, 트랜잭션 격리수즌, read uncommitted, transaction isolation level, 스냅샷 격리수준

컬럼스토어 인덱스 동시성

Column Store Index Concurrency and Isolation Levels

 

  • Version : SQL Server 2012, 2014

 

클러스터 컬럼 스토어와 동시성

클러스터 컬럼스토어 인덱스(Clustered column index(CCI))는 기본적으로 데이터웨어하우스 시나리오에 기반하여 디자인 되어 있다.

 

  • 한 번 쓰고 여러 번 읽기 : CCI는 쿼리 성능에 최적화 되어 있다. 이는 기둥 형식으로 압축된 데이터에서 필요한 컬럼만 가져와서 성능을 높인다.
  • 대량 데이터 가져오기 및 세류(천천히) 데이터 로드 : 인서트 오퍼레이션

 

INSERT / UPDATE를 지원하지만 이러한 작업은 대량의 작업에 최적화 되어 있지 않다. 사실 동시성 경우 DELETE / UPDATE 경우 블록킹이 발생 할 수 있으며 대량의 delta row groups로 이어질 수 있다. 동시성 모델에는 새로운 잠금 리소스 ROWGROUP이 있다.

 

잠금이 발생하는 시나리오에 대해 트랜잭션 격리 수준으로 알아 보자.

 

트랜잭션 격리 수준 지원 (Transaction Isolation levels Supported)

  • Read Uncommitted : 대부분 DW 쿼리에 대한 작업이며 쿼리가 실행 되는 동안 PDW 어플라이언스에서 CCI에 엑세스할 때 read uncommitted 로 DML에 대한 동시성이 차단되지 않도록 한다.
  • Read Committed : 잠금 기반으로 실행되며 DML에 대한 블록킹을 제공한다.

 

RCSI는 하나 이상의 CCI 테이블을 포함하여 사용하는 경우 CCI 이외의 모든 테이블은 read committed 격리 수준에서 non-blocking 의미로 액세스 할 수 있다. 하지만 CCI는 불가능하다.

If RCSI is enabled on the database containing one or more tables with CCI, all tables other than CCI can be accessed with non-blocking semantics under read committed isolation level but not for CCI

 

Example : SQL Server 2014

select

    is_read_committed_snapshot_on, snapshot_isolation_state_desc,snapshot_isolation_state

from sys.databases where name='AdventureWorksDW2012'

 

 

CREATE TABLE [dbo].[T_ACCOUNT](

[accountkey] [int] IDENTITY(1,1) NOT NULL,

[accountdescription] [nvarchar](50) NULL

) ON [PRIMARY]

 

-- create a CCI

CREATE CLUSTERED COLUMNSTORE INDEX ACCOUNT_CCI ON T_ACCOUNT

 

세션 1

세션 2

세션3

use AdventureWorksDW2012

go

 

-- Do a DML transaction on CCI but don't commit

begin tran

    insert into T_ACCOUNT (accountdescription )

    values ('value-1');

   
 

set transaction isolation level read committed

go

 

select * from t_account

--You will see CCI query is blocked on session-1 as shown using the query below

 
   

select

    request_session_id as spid,

    resource_type as rt,

    resource_database_id as rdb,

    (case resource_type

        WHEN 'OBJECT' then object_name(resource_associated_entity_id)

        WHEN 'DATABASE' then ' '

        ELSE (select object_name(object_id)

            from sys.partitions

            where hobt_id=resource_associated_entity_id)

    END) as objname,

    resource_description as rd,

    request_mode as rm,

    request_status as rs

from sys.dm_tran_locks

 

 

이 데이터베이스는 기본적으로 non-blocking read committed 격리 수준의 행 버전 관리를 사용하지만 CCI는 잠금 기반의 read committed로 접근한다.

 

  • Snapshot Isolation : 이는 CCI를 포함하는 데이터베이스에서 사용 할 수 있다. CCI 이외의 디스크 기반 테이블은 스냅샷 격리에서 액세스 할 수 있지만 CCI에 대한 액세스가 허용되지 않으며 다음과 같은 에러가 발생한다.

Msg 35371, Level 16, State 1, Line 26

SNAPSHOT isolation level is not supported on a table which has a clustered columnstore index.

 

  • Repeatable Read : CCI에서 지원

set transaction isolation level repeatable read

go

 

begin tran

select * from t_account

 

 

Serializable : CCI에서 지원

set transaction isolation level serializable

go

 

begin tran

    select * from t_account

go

 

 

 

 

[참고자료]

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2014/07/27/clustered-column-store-index-concurrency-and-isolation-level.aspx

 

 

2014-09-23 / 강성욱 / http://sqlmvp.kr

 

SQL Server 2012, 컬럼스토어 인덱스, 행 그룹, Column Store Index, 메모리 인덱스, SQL, 데이터베이스, 대용량 데이터베이스, Delta Row Group, Compress Row Group, 트랜잭션 격리수즌, read uncommitted, transaction isolation level, 스냅샷 격리수준

컬럼스토어 인덱스 ROW와 ROWGROUP 영향

 

  • Version : SQL Server 2012, 2014

 

SQL Server 메모리 내 Columnstore 인덱스는 열 기반 데이터 저장소 및 열 기반 쿼리 처리를 사용하여 데이터를 저장하고 관리한다. Columnstore 인덱스는 주로 대량 로드 및 읽기 전용 쿼리를 수행하는 데이터웨어 하우징 작업에 효과적이다.

 

columnstore index는 columnstore라는 칼럼 데이터 형식을 사용하여 데이터를 저장, 검색 및 관리하는 기술이다. SQL Server는 클러스터형 columnstore 인덱스와 비클러스터형 columnstore 인덱스를 모두 지원한다. 둘 다 동일한 메모리 내 columnstore 기술을 사용하지만 용도와 지원 기능에 차이가 있다. columnstore 인덱스는 전체 테이블 검색을 사용하는 쿼리에는 뛰어난 성능을 제공하지만 특정 값을 찾아 데이터를 검색하는 쿼리에는 부적합하다.

 

자세한 내용은 컬럼스토어에 대한 마이크로소프트 공식 문서를 참고 한다.

  • Cloumnstore 인덱스 소개 :

http://msdn.microsoft.com/ko-kr/library/gg492088(v=sql.120).aspx

 

이번 포스트는 컬럼스토어 인덱스에서 Row와 rowgoup에 대한 영향으로 SQL Server Storage Engine Blog를 읽고 이해한 내용을 정리한 것으로 번역의 오류나 기술적 오류가 있음을 미리 알려둔다. 자세한 내용은 원문을 참고 하길 바란다.

 

컬럼스토어 인덱스가 가지고 있는 로우에 대한 그룹을 RowGroup이라고 한다. RowGroup에는 두 가지 타입의 그룹이 있다.

  • Delta RowGroup : 기존의 행 스토리지 형식으로 데이터를 저장
  • Compressed RowGroup : 컬럼 스토리지 형식의 높은 행 압축 수준으로 저장

 

컬럼스토어에 대한 압축은 RowGroup 행의 수에 의존 한다. RowGroup은 Columnsotre 형식으로 동시에 압축되는 행 그룹이다. 성능과 압축률을 높이기 위해 columnstore 인덱스는 테이블을 여러 행 그룹으로 조각화한 후 각 행 그룹을 열 방식으로 압축 한다. 행 그룹의 행수는 압축률을 높일 만큼 크고 메모리 내 작업을 활용할 만큼 작아야 한다.

우리는 실험에서 RowGroup이 102,400+ 행에 대해 더 좋은 컬럼 압축을 달성 할 수 있음을 발견했다. 이를 바탕으로 컬럼스토어 인덱스 가이드 라인은 다음과 같다.

  • 100000 + 행을 로드할 때 압축된 rowgroup을 직접 로드 한다. (대규모 대량 로드 중에 대부분 행은 deltastore를 통과하지 않고 columnstore로 곧바로 이동한다.)
  • 일반 인서트는 Delta RowGroup에 행을 로드 한다. 행번호 1,048,576에 도달하면 RowGroup은 닫히고 튜플무브(tuple move)라는 백그라운드 스레드가 컬럼스토어 형식으로 변환하여 저장한다.
  • 나머지 행은 columnstore 또는 deltastore에 추가된다. 행수가 행 그룹당 최대행보다 적으면 deltastore에 추가 된다.

 

RowGroup 크기에 영향을 미치는 요인은 다음과 같다.

  • 병럴처리 정도(DOP)
  • 사전의 크기
  • 메모리

인덱스 만들기는 메모리가 제한되지 않는한 기본적으로 병렬작업이다. 병렬 작업은 많은 메모리를 필요로 하다. 메모리가 충분하면 동일한 열에 B-tree를 작성할 때 보다 1.5배 많은 메모리가 columnstore 인덱스를 만드는데 사용된다. 충분한 메모리가 없는 경우 메모리에 맞게 자동으로 MAXDOP을 줄인다.

 

예제와 함께 각각 요인을 살펴보자. 다음 스크립트는 데이터를 생성하고 컬럼스토어 인덱스를 생성한다. (매우 오랜시간 동안 스크립트가 실행 된다.)

  • 클러스터형 컬럼스토어 인덱스 : SQL Server 2014 지원
  • 비클러스터형 컬럼스토어 인덱스 : SQL Server 2012 ~ 2014 지원

CREATE TABLE dbo.t_colstore (

c1 int NOT NULL,

c2 INT NOT NULL,

c3 char(40) NOT NULL,

c4 char(1000) NOT NULL

)

go

 

set nocount on

go

 

-- load 2000000 rows

declare @outerloop int = 0

declare @i int = 0

 

while (@outerloop < 2000000)

begin

    Select @i = 0

    

    while (@i < 2000)

    begin

        insert t_colstore values (@i + @outerloop, @i + @outerloop, 'a',

        concat (CONVERT(varchar, @i + @outerloop), (replicate ('b', 950))))

        set @i += 1;

    end

 

set @outerloop = @outerloop + @i

 

set @i = 0

end

go

 

CREATE CLUSTERED COLUMNSTORE INDEX t_colstore_cci ON t_colstore with (maxdop = 1)

 

생성된 컬럼스토어의 RowGroup과 각 그룹에 포함된 행을 살펴보자. 필자가 테스트한 결과로는 39개의 그룹과 각 그룹당 45468의 행이 포함되어 있다.

select * from sys.column_store_row_groups where object_id = object_id('t_colstore')

 

 

위에 설명에서는 행번호 1,048,576에 도달해야만 세그먼트 그룹이 닫히고 새로운 세그먼트 그룹으로 저장된다고 하였는데 왜 이런 현상이 발생한 걸까? 이는 사전의 크기(16MB)가 가득차게 되면 자동으로 RowGroup의 크기를 줄인다. 즉 메모리가 부족한 경우 로우 카운트가 1048576에 도달하지 않더라도 현재의 세그먼트를 닫는다.

 

 

[참고자료]

http://msdn.microsoft.com/ko-kr/library/gg492088(v=sql.120).aspx

 

 

2014-09-15 / 강성욱 / http://sqlmvp.kr

 

SQL Server 2012, 컬럼스토어 인덱스, 행 그룹, Column Store Index, 메모리 인덱스, SQL, 데이터베이스, 대용량 데이터베이스, Delta Row Group, Compress Row Group, 인덱스 압축

 

+ Recent posts