MSSQL SHRINK online? offline? - shrink test 후기, 방법 및 주의할 점

요즘같이 디스크 용량 걱정이 없는 세상에서는 MSSQL Shrink를 볼 일이 없을 것 같았는데 얼마 전 회사에서 SHRINK를 할 일이 생겨서 진행했었다.

디스크 용량이 약 4테라이고 해당 디스크는 db file만 존재하여 딱히 지울 파일이 없었다.
그래서 db 자체적으로 용량을 확보하는 방법밖에 없었다.

크게 궁금했던 부분은 다음과 같다.
1. mssql table truncate를 했을 때 바로 db size(.ndf, .mdf)가 줄어드는가?
2. shrink를 한다면 어떻게 진행해야 하는가?

1. table truncate 직후 db size(.ndf, .mdf)가 줄어드는가?

먼저 1번의 경우에 과연 mssql에서 table을 truncate를 했을 때 바로 DB FILE SIZE가 줄어들어 공간을 확보할 수 있을까? 작업 전 예측하기로는 이미 늘어난 상태의 DB가 쪼그라들(축소)것 같지는 않았다. 그래서 테스트를 해봤다.

먼저 테스트용 DB를 생성했다.

초기 사이즈는 약 8MB, 증분은 64MB씩 되도록 하였다.

현재 깡통 DB이기 때문에 테스트 데이터를 밀어넣고자 랜덤데이터를 생성하였다.
증분 쿼리는 msdb에 sys.objects끼리 3번이상 cross join을 하면 DISK 공간이 부족해서 다음과 적당히 UNION ALL 해서 생성하였다.


ROW 18,819,504건, SIZE 2.54 GB 짜리 TABLE이고 DB FILE SIZE는 약 2.57GB가 되었다.

이후 TABLE TRUNCATE 후에 DB FILE SIZE는 변함이 없었다.

실제로 exec sp_spaceused를 날려보면 database_size는 그대로 2712MB를 사용하고 있었다(비할당 공간이 2637.30MB)

1번 궁금증이었던 mssql table truncate를 했을 때 바로 db size(.ndf, .mdf)가 줄어드는가?
는 해결되었다.

그럼 비할당된 공간은 어떻게 반환할 수 있을까?

DBCC SHRINKDATABASE 혹은 DBCC SHRINKFILE을 통해 비할당 공간을 줄여줄 수 있다.

반환 후 exec sp_spaceused 호출한 결과이다.

실제 DB file size도 줄어든 것을 확인할 수 있다.

2. shrink를 한다면 어떻게 진행해야 하는가?

사실 위의 경우에는 shrink를 해도 문제없는 DB였지만 실제 운영중에는 사용할 일이 없는 것이 좋다. 가장 큰 이유는 언제 끝날지 모르는 일이기 때문이다.

실제 운영 db에는 수백~수천개의 table이 존재하고 이들에게 insert/delete 등이 빈번하게 발생할 수 있기 때문에 물리적인 조각화(Fragment)가 생길 수 있다.

조각화는 DBCC SHRINKDATABASE 혹은 DBCC SHRINKFILE를 사용하여 없앨 수 있는데 굳이 SHRINKDATABASE를 하여 db 전체의 조각화를 없애기에는 부담이 가기때문에 file별로 shrinkfile을 해주는 것이 좋다.

shrink를 할 때 online? offline? singlemode?
1. 찾아보면 대부분이 offline을 권장했지만 결론은 온라인으로 진행했다. 이유는 rollback이 필요없기때문에 cancel이 바로되고 루프를 돌며 조금씩 파일 size를 줄였기때문이다.

즉 DBCC SHRINKFILE (N'SHRINK_DB' , 0)가 아닌 DBCC SHRINKFILE (N'SHRINK_DB' , 타겟사이즈)를 계속 줄여가는 방법을 택했다.

아래와 같은 방법으로 진행했다.

필자의 경우엔 온라인 상태에서 진행을 했었고 전체 DB SIZE는 약 3.5TB이고 Disk 공간부족으로 약 600GB를 확보하였다. loop를 한번 돌때마다 1GB씩 줄이는데 1분이 걸릴때도 있고 15분이 걸릴때도 있는데 약 24시간에 300회 정도 loop를 돌았다. 즉 600GB를 확보하는데 48시간이 걸렸는데 이건 사용자에 따라 다르겠지만 언제 끝날지 모른다는 것을 꼭 알아둬야한다.

한가지 조금 걱정스러웠던 것은 온라인 상태에서 진행을 할때 클러스터드 INDEX도 물리적인 저장공간을 옮겨서 저장할 것같은데 이런 경우 운영중인 DB에서 쿼리가 INDEX를 잘 타는지, 아니면 INDEX도 옮겨지는 도중이라서 random I/O가 일어나는지 궁금하다. 후자라면 위험해보인다.

MSSQL(PDW)에서 ORC 타입으로 하둡에 데이터를 내리고 하이브 테이블 생성하기

MSSQL및 PDW(parallel data warehouse)에는 하둡과 연결고리가 잘 되어있다.
이들이 지원하는 PolyBase라는 기능은 RDBMS의 데이터와 하둡에 저장된 데이터를 조인할 수 있도록 하는데 예를 들어 하둡에 저장되어 있는 데이터를 RDBMS로 가져오고 둘을 조인해서 원하는 측정값을 뽑아내기에 매우 유용하다.

위 처럼 HDFS -> MSSQL(PDW) 로의 데이터 이동이 아닌 MSSQL(PDW) -> HDFS로 데이터를 내리는 것도 가능하며 예제는 다음과 같다. (PDW 기준으로 예제를 작성하였다.)

다음 예제는 ORC 타입으로 데이터를 내리는 방법이고 파케이(parquet)나 텍스트 등 다양한 타입 및 압축(Gzip, Snappy 등)으로 데이터 포맷을 생성할 수 있다.

먼저 데이터 소스랑 파일 포맷을 생성한다. (각 DB별로 생성할 수 있다.)


USE TEST_DB;
-- 데이터 소스 생성
IF EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'HDFS_DS_SNAPSHOT')
   DROP EXTERNAL DATA SOURCE HDFS_DS_SNAPSHOT

CREATE EXTERNAL DATA SOURCE HDFS_DS_SNAPSHOT
WITH (TYPE = HADOOP
, LOCATION = N'hdfs://XXX.XXX.XXX.XXX:8020'
, RESOURCE_MANAGER_LOCATION = N'XXX.XXX.XXX.XXX:8050')
GO

-- 파일 포맷 생성
IF EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'HDFS_FF_ORC')
   DROP EXTERNAL FILE FORMAT HDFS_FF_ORC

CREATE EXTERNAL FILE FORMAT HDFS_FF_ORC
WITH (
    FORMAT_TYPE = ORC,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

-- 생성된 데이터 소스 및 파일 포맷 확인

SELECT * FROM sys.external_data_sources

SELECT * FROM sys.external_file_formats

-- 하둡에 월 파티션으로 데이터 내리기

DECLARE @DT VARCHAR(8) = '20170101'
DECLARE @SQL NVARCHAR(4000);

WHILE(1=1)
BEGIN
      SET @SQL  = N' IF  OBJECT_ID(''TEST_DB..HDFS_TEST_TABLE_'+@DT+''') IS NOT NULL
                       DROP EXTERNAL TABLE HDFS_TEST_TABLE_' + @DT + '

      CREATE EXTERNAL TABLE HDFS_TEST_TABLE_' + @DT + '
      WITH (LOCATION=' + '''/apps/hive/xxxxxxxx/yyyyyyyy/zzzzzzzzz/TEST_TABLE/yyyymmdd=' + @DT + ''',
           DATA_SOURCE = HDFS_DS_SNAPSHOT,
           FILE_FORMAT = HDFS_FF_ORC,
           REJECT_TYPE = PERCENTAGE,
           REJECT_SAMPLE_VALUE = 10000,
           REJECT_VALUE = 0.1
      ) AS
      SELECT *
      FROM TEST_TABLE
      WHERE ' + N'DT' + ' >= ''' + @DT +  ''' AND ' + N'DT' +  ' < CONVERT(VARCHAR(8),DATEADD(MM,1,''' + @DT + '''),112) '

      --PRINT @SQL
      SET @DT = CONVERT(VARCHAR(8),DATEADD(MM,1,@DT),112)
      IF(@DT > '20180101')
        BREAK;
END

다음부터는 하이브에서 진행하며 위에서 생성한 HDFS를 사용한다.

-- 하이브에서 테이블 만들기

 CREATE EXTERNAL TABLE HDFS_TEST_TABLE

(

    `COL1` bigint ,

    `COL2` bigint ,

    `COL3` bigint ,

)

PARTITIONED BY (YYYYMMDD STRING)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

STORED AS ORC

LOCATION '/apps/hive/xxxxxxxx/yyyyyyyy/zzzzzzzzz/TEST_TABLE';

-- 파티션 적용하기
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20170101');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20170201');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20170301');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20170401');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20170501');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20170601');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20170701');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20170801');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20170901');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20171001');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20171101');
ALTER TABLE database_name.HDFS_TEST_TABLE ADD PARTITION (YYYYMMDD='20171201');


정리하면 MSSQL(PDW)에서 데이터 소스와 파일 포맷을 만들어주고 이를 활용해서 HDFS로 데이터를 내린다. 그리고 하이브에서 내린 데이터를 가지고 외부 테이블을 생성하면 된다.