DB에 저장한 Perfmon 데이터 관리

DB서버에 perfmon을 저장하는 방법을 이전 포스트에서 포스팅 했습니다.

http://burning-dba.tistory.com/43

하지만 perfmon의 row 수가 많아지면, 성능 이슈 및 용량의 이슈가 발생합니다. 현재 1분에 1건씩 perfmon을 수집하여, 데이터를 넣고 있는데 한달정도 운영하였는데 건수가 벌써 150만건을 넘었습니다. 이렇게 많은 건수가 차후에 쌓이고 쌓여, 성능 이슈가 발생하거나 용량이 문제가 생길 수 있습니다. 

그래서 이번 포스트에서는 지난 번의 내용에서 응용을 하여, 5개씩 (5분) 1 set로 지정하여, 데이터를 1/5로 줄여, 데이터를 old 테이블에 

저장하는 방법에 대해서 적어보고자 합니다.

요구 사항은 다음과 같습니다.

① 기존의 데이터를 보존한다.

② 데이터의 사이즈를 줄인다. (row의 수를 줄인다.)

③ 현재 1분 단위로 perfmon을 수집하는데, 5분(5개 1set 로 묶어서 평균을 낸다.) 단위로 데이터 가공하여, 저장한다.

우선 1차적으로 데이터를 가공합니다. 

		select  [CounterID]
				,[CounterDateTime]
				,[CounterValue]
				,case  when SUBSTRING(counterdatetime,16,1) in (0,1,2,3,4) then 'A' 
						when SUBSTRING(counterdatetime,16,1) in (5,6,7,8,9) then 'B'  end as 'type'
				,convert(char(15),counterdatetime) as 'mtime'
		from [dbo].[CounterData_test] with(nolock)

이 가공되는 데이터는 다음과 같은 원리로 이루어집니다.

① mtime으로 데이터를 10분 단위 묶을 수 있도록, convert를 이용하여, 분의 앞자리 까지 짜릅니다.

② 잘려진 mtime 만으로, 5분을 알수 없으므로, SUBSTRING을 이용하여, 분의 뒷자리의 데이터를 case를 이용하여, type을 나눕니다.

위 처럼 가공된 데이터를 CTE를 이용하여, 다시 group 를 거쳐 가공합니다. 한번에 5분 단위로 쪼개기 어려워서 CTE를 이용하여, 다시 한번 데이터를 가공 하였습니다.

 

with perfmon_CTE (counterid,[CounterDateTime],[CounterValue],type,mtime)
as 
		(	
		select  [CounterID]
				,[CounterDateTime]
				,[CounterValue]
				,case  when SUBSTRING(counterdatetime,16,1) in (0,1,2,3,4) then 'A' 
						when SUBSTRING(counterdatetime,16,1) in (5,6,7,8,9) then 'B'  end as 'type'
				,convert(char(15),counterdatetime) as 'mtime'
		from [dbo].[CounterData_test] with(nolock)
		)
select  max([CounterDateTime]) as  'Date'
		,counterid
		,convert(float,avg(CounterValue)) as '5 minute Avg'
		,mtime
		,type
from perfmon_CTE
group by counterid,mtime,type
order by  'Date' asc
go

위처럼 가공하면, 다음과 같은 결과물을 얻을 수 있습니다.

컬럼의 대한 설명은 다음과 같습니다.

① Date         : 이 컬럼은 차후에 old 테이블을 조회 할때 사용하기 위해서 counterid와 함께 복합 인덱스로 PK를 잡았습니다.

② counterid    : counter의 항목 번호 입니다. 이 부분은 기존의 CounterData 테이블과 동일한 의미입니다.

③ 5 minute Avg : 5개 1set의 평균값 입니다.

④ mtime       : 10분 단위까지의 분의 앞자리 값 입니다. (group by를 위하여 사용)

⑤ type         : A type (0,1,2,3,4) B type (5,6,7,8,9) 입니다. 결국 숫자는 분의 뒷자리를 나타냅니다. 5분 단위로 쪼개기 위한 구분자 입니다.

이제 데이터를 넣을 old 테이블을 만들어 줍니다.

create table [dbo].[CounterData_old]
(
 date					datetime	not null
,counterid			int			not null
,five_minute_Avg		float		not null
,m_time				char(15)	not null
,type					char(1)	not null
constraint PK_CounterData_old_1 primary key clustered(counterid,date)
)
go

create nonclustered index NC_CounterData_old_2	on [CounterData_old] (date)
go

old 테이블은 차후에 old 테이블을 검색을 위한 인덱스와 데이터를 효율적으로 관리하기 위해서 컬럼 및 인덱스를 추가하였습니다.

만들어진 old 테이블에 아까의 스크립트에 insert 구문을 추가하여, 데이터를 밀어 넣습니다. 해당 작업은 운영서버에서는 이슈가 있을 수 있으므로, 조심해야 합니다.

with perfmon_CTE (counterid,[CounterDateTime],[CounterValue],type,mtime)
as 
		(	
		select  top 1000 [CounterID]
				,[CounterDateTime]
				,[CounterValue]
				,case  when SUBSTRING(counterdatetime,16,1) in (0,1,2,3,4) then 'A' 
						when SUBSTRING(counterdatetime,16,1) in (5,6,7,8,9) then 'B'  end as 'type'
				,convert(char(15),counterdatetime) as 'mtime'
		from [dbo].[CounterData_test] with(nolock)
		)
insert into [dbo].[CounterData_old] 
select  max([CounterDateTime]) as  'Date'
		,counterid
		,convert(float,avg(CounterValue)) as '5 minute Avg'
		,mtime
		,type
from perfmon_CTE
group by counterid,mtime,type
order by counterid, 'Date' asc
go

테이블이 초기에 1,547,544 건에서 309,570건으로 줄었습니다. 이는 몇몇의 데이터가 5분 단위가 안되기 때문에 row의 차이가 날 수 있습니다. 

해당 작업을 실행 후에 기존의 데이터를 보관 주기 정책을 수립하여, 몇개월 단위로 JOB 스케줄러를 걸고, 자동으로 삭제되도록 하면, 관리가 수월 할 것으로 보여집니다.

하지만, 주의해야 할 점은 delete 시에 너무 많은 건수를 한번에 지울 경우 CounterData 테이블에 문제가 있을 수 있으니 주의하여, 지워야 합니다.

실제로 3천만건에 육박하는 데이터를 지울 때 10일 단위 또는 30일 단위로 지우도록 while 문을 만들어서 수행하였습니다. 

운영DB의 경우 지속적으로 perfmon이 데이터를 수집하고 있기에 delete 발생하는 lock에 대한 이슈를 고민해야 합니다. 

감사합니다.