Azure SQL 2018, SQL Serv er 2019에 추가된 ADR(Accelerated Database Recovery) 옵션 성능 테스트를 해보자. 아래 링크로 ADR에 대한 간단한 설명과 테스트를 확인할 수 있다.
가속 데이터베이스 복구(ADR) 간단 테스트 https://blog.naver.com/sinjoker/222475052527
절차는 다음과 같다. ▼ (2,4번은 influxdb 사용시 참고)
- ostress 사용을 위한 RML 유틸리티 설치 2. 그래파나(grafana) 모니터링 연동을 위한 influxDB 설치 3. 그래파나인스톨 4. 그래파나 influxdb 데이터소스 연결 5. 퍼포먼스 모니터에서 SQL Server 리소스 수집 –> csv 파일로 drop6.SQL Server 리소스 연결 ->
- 1. RML 유틸리티 설치 O_Stress 테스트를 위해 RML 유틸리티를 설치한다. MS 공식 사이트에서 다운로드 링크를 제공한다.
- RML 유틸리티 다운로드 장소 ▼ https://github.com/microsoft/SqlNexus/releases/tag/09.04.0097
2.influx DB설치 in flux DB는 그래파나와 연동하여 SQL Server의 자원 추이를 감시하기 위해 설치한다. 나의 경우 githup에서 1.8.9 버전의 zip 파일을 받아 설치했다. 2.0.8 버전의 경우 지원되는 실행 파일이 한정되어 있고 설정 파일(influxdb.conf)도 없기 때문에 1.8.9 버전의 설치를 권장한다.
influxDB binary 다운로드 ▼ https://portal.influxdata.com/downloads/influxDB zip 파일 다운로드 ▼ https://docs.influxdata.com/influxdb/v2.0/install/ ? t = WindowsinfluxDB githup 다운로드 ▼ https://github.com/influxdata/influxdb/releases
아래 경로에 zip 파일을 압축 해제한다. ▼
아래와 같이 데이터, meta, wal폴더를 생성하고 ▼
influxdb.conf 파일을 수정해 준다. ▼ [ meta ]
[ data ]
influxd 실행 파일을 클릭하여 서버를 실행하고 ▼
그래파나에 연동시키는 PER F 데이터베이스를 생성한다. ▼
3. 그라파나(Grafana) 설치 그라파나는 엔터프라이즈 에디션을 설치하면 된다. OSS 에디션을 설치하면 그래파나에서 제공하는 모든 기능을 사용할 수 있다.
그라파나 다운로드 링크▼https://grafana.com/grafana/download?platform=windows
설치 후에는 grafana의 bin폴더에서 grafana-server.exe를 실행하면 된다. ▼
로컬로 인스톨을 할 수 있어 그래파나 접속 ip 주소는 127.0.0.1 이며, 그래파나포트는 3000 고정이다. 그래서 127.0.0.1:3000으로 접속하면 된다. ▼
초기 접속 시의 id는 admin이고, password도 admin이다. 최초 admin 접속 후 패스워드를 변경하면 된다.
4. 그라파나 influxdb 데이터 소스의 연결 influxdb와 그라파나 설치를 마치면 이 둘을 서로 연결시킨다.그라파나의 configuration에서 Add data source를 선택하여 Influx DB를 검색한다. ▼
influxdb Server를 실행한 상태에서 데이터 원본 옵션을 아래와 같이 설정한다. ▼
정상적으로 접속되면 아래와 같은 메시지가 나타난다. ▼
5.SQL Server 자원 수집 in Perfmon 실행 창에서 perfmon을 실행하거나 작업 표시줄에서 Perfmon을 검색하여 실행한다. ▼
데이터 수집기 집합 > 신규작성(N) > 데이터 수집기 집합을 클릭한다. ▼
적당한 이름을 입력하고 수동으로 작성(고급)(C)을 선택한 후 다음을 클릭한다. ▼
데이터로그 작성 성능 카운터에 체크한다. ▼
기록하고 싶은 성능 카운터를 추가한다. ▼(아래쪽은 15초로 나와 있지만, 실제 샘플 간격은 5초로 설정했다.)
데이터를 저장할 장소를 설정한다. 내 경우 바탕화면에 데이터를 저장하도록 했다. ▼
데이터 수집기가 만들어지면 마우스 우클릭 후 데이터 관리자를 실행한다. ▼ (이 단계는 보고서 파일을 필요로 할 때 수행한다.)
데이터 관리자 탭을 보면 초기 화면에서 리포트 파일의 이름이 report.html로 되어 있을 것인데 이를 report.csv로 변경한다. 그리고 데이터 관리 및 보고서 작성 사용을 체크한다. ▼ (이 단계는 보고서 파일을 필요로 할 때 수행한다.)
설정을 마치면, 이제 데이터 수집기를 실행한다. 시작 또는 화살표 아이콘을 클릭하면 데이터 수집이 시작된다. ▼
데이터 저장 경로로 가면 c sv 파일이 생성되어 있고, 5초 간격으로 데이터가 수집되고 있음을 확인할 수 있다. ▼
6.SQL Server Agent 기능 활성화를 수집한 csv 파일을 DB 테이블에 INSERT 하기 위해서 SQL Server Agent 기능을 활성화 한다. SQL Server 2019 구성 관리자를 실행한다. ▼
SQL Server 서비스 > SQL Server 에이전트(SQLEXPRESS) > 서비스 > 시작 모드 > 수동으로 변경▼
SQL Server 에이전트 서비스를 시작한다. ▼
만약 아래와 같은 오류가 발생했을 경우에는 다음 순서로 진행하도록 한다. ▼
로그 경로에 들어가서 SQL Agent 실행 시 기록된 에러 로그를 확인해 본다. ▼이건 아마 Agent 유저마다 오류가 다르게 기록될 것이다 master DB에 문제가 있어서 안될수도 있고, 나처럼 msdb 권한문제로 안 될수도 있다.
이 경우 SQL Agent 서비스를 실행시킬 주체의 서버 역할을 설정해야 한다.SSMS > Security(보안) > Logins(로그인) > 내 경우 NTAUTHORITY STEM ▼
Server Roles(서버 롤)에서 sysadmin 체크 후 OK ▼
위 오류를 해결하면 다른 오류가 발생하는데, “이 서비스를 설치한 SQL Server 버전에서 SQL Server 에이전트가 지원되지 않습니다.”라고 하는 에러가 기록된다. 현재 로컬에 설치한 버전은 Express 버전인데, 해당 버전은 SQL Server 에이전트가 지원되지 않는다는 것이다. ▼
이번에는 SQL Server 설치 센터에서 버전 업그레이드를 진행한다. ▼
무료버전 지정 콤보 값을 Developer로 변경하여 업그레이드 하면 된다. ▼
업그레이드 후 SSMS를 시작하면 SQL Server Agent 항목이 생성되어 있다. ▼
7. 배치 파일 및 파워 셸 스크립트를 생성하고, 그 다음 과정은 csv 파일을 테이블에 넣기 위해 불필요한 문자를 제거하는 과정이다. 성능 모니터를 통해 생성된 csv 파일 내용을 확인해 보면 다음과 같이 리소스 값 사이에 큰 따옴표가 생성되어 있는 것을 확인할 수 있다. ▼
이 파일을 SQL 서버에서 Import 하면 오류가 발생하므로, 큰 따옴표(“)를 제거해야 한다.큰 따옴표를 삭제한 뒤 새로운 파일을 생성하는 파워셸 스크립트를 작성하고 이를 주기적으로 실행하는 배치 파일을 만든다.
우선 파워셸 스크립트를 다음과 같이 작성한다. test 77.csv 파일에서 큰 따옴표(“)를 삭제하고 test 88.csv 파일을 새로 생성하는 코드이다. ▼
배치 파일은 아래와 같이 작성한다. 위에서 작성한 test_210901 파워셸 스크립트 파일을 실행하는 코드이다. ▼
@echo off : 배치파일 내 명령어가 실행될 때마다 결과가 출력되지 않도록 설정한다. – pause : 명령어 실행 후 명령어 창이 자동으로 닫히지 않도록 설정한다. 필요하지 않을 경우 삭제한다.
주기적으로 새로운 파일을 생성해야 하기 때문에 이는 스케줄이 필요한 작업이다. 그 때문에 작업 스케줄로 이것을 설정해 준다.작업일정기로 작업만들기를 수행한다. ▼
스케줄러 이름을 입력하고 가장 높은 수준의 권한으로 실행에 체크한다. ▼
트리거는 아래와 같이 설정한다. ▼
동작은 위에서 생성한 배치 파일이 실행되도록 설정해 준다. ▼
이후의 탭은 사용자의 조건에 맞게 설정하면 된다.
설정이 완료되면 트리거의 조건에 따라 2021년 9월 1일 수요일 오후 1:45:59에 test 88.csv 파일이 생성되었음을 확인할 수 있다. ▼ (이때 파일 내용에서 큰 따옴표가 제거되었음을 확인한다.)
8. csv 파일을 테이블에 INSERT 큰 따옴표(“)를 제거한 csv 파일이 생성되므로 이를 수집하여 테이블에 INSER 하는 작업을 진행한다.생성한 테이블 스키마는 다음과 같다. ▼
SQL Server Agent 작업을 설정하기 전에 INSERT 하고자 하는 질의문이 정상적으로 실행되는지 확인한다. ▼
위의 명령문을 실행하면 7건의 데이터가 입력되는 것을 확인할 수 있다. ▼
쿼리에 이상이 없는 것으로 확인되면 개체 탐색기에서 New job을 클릭하여 Agent job을 설정한다.▼
General 설정 ▼
Steps 설정 ▼
Schedules 설정 ▼
Start Job at Step을 눌러 jo b를 실행한다. ▼
그러면 5분 후에 테이블에 데이터가 입력된다. ▼
9. SQL Server와 그래파나 데이터 소스의 접속 데이터를 그래프로 확인하기 위해 그래파나에서 데이터 소스를 설정한다. ▼ (접속 정보를 올바르게 입력했는데 접속에 실패했을 경우 SQL Server가 Express 버전이 아닌지 확인하고 이를 Developer 버전으로 업그레이드하여 진행한다.)
데이터(csv 파일)가 입력된 테이블 쿼리를 생성한다. ▼
그래파나와 데이터 소스가 연결되어 있기 때문에 데이터에 따라 그래프가 그려지는 것을 확인할 수 있다. ▼
10.ADR 비활성화 OSTRESS 테스트의 1번 과정에서 RML 유틸리티를 설치했기 때문에 CMD에서 바로 OSTRESS를 실행할 수 있다.CMD에서 OSTRESS를 실행할 때는 다음과 같은 형태에서 파라미터를 구성하는 코드를 실행하면 된다. ▼ OSTRESS파라미터의 사용법은 ostress-help명령으로 확인할 수 있다. ▼ Parameter List-S name of Microsoft SQL Server server to connect to(포트는 Endpoint옆에 콤마(,)를 찍고 넣어주면 된다.)-D ODBC data source name to use for connection-E use Windows auhentication to connect(default)-U login ID-P password-d database name-Q”single batch query to process”-i SQL/RML file name or file wildcard to process-n number of connections processing each input file/query – stress mode-r number of iterations for each connection to execute its input file/query-o output directory to write query results and log file-l login timeout (sec)-L integer value representing the language id-t query timeout (sec)-p network packet size for connections – SQL Server only-q quiet mode; suppress all query output – v verbose mode ; show additional diagnostic output – m [ stress | replay ] run in stress or replay mode – a default password to use for SQL authentication during replay – c control file name – required for replay mode – T enable trace flag – fx write query results as XML – N disable ” OSTRESS exiting ” message-M Advanced setting: max threads allowed, 0 is default setting.-b Stop processing if an error is encounting 중간중간 수행한 If는 10000회 정도이고, exquery is encountered during ▼
시간 확인 ▼
11.ADR 활성화 OSTRESS 테스트 다음은 ADR을 활성화하여 10000의 스레드에서 10000회 반복 INSERT한 결과이다. ▼해당 작업도 20분 정도 테스트를 하고 도중에 프로세스를 종료했다. (약 4,000만 건)
12. 결과 ADR의 활성화/비활성화 데이터 비교는 그래프상의 04:00를 기점으로 확인하면 된다.비교한 표는 다음과 같다. ▼ (MAX값으로 비교하였다.자원 ADR(X) 3,800만 건 ADR(O) 4,000만 건 Processor Time%100Page writes/sec183355Page lookup/sec124kPage expentancy2.36k5.96kPage Rads/sec58317Log Bytes
아래는 위 표에서 큰 차이가 있는 주요 지표를 다시 측정한 결과이다. ▼ (테스트는 스레드 4000, 반복 4000 으로 진행되며, 테스트 시간은 약 9분 정도 소요되었다.)
< Page Writes / sec >
< Page lookup / sec >
< Transactions / sec >
< Log Flushes / sec >
ADR을 설정한 DB는 ADR을 설정하지 않은 DB보다 복구가 빨리 진행되어 좋아 보일 수 있는데, 리소스 사용면에서 볼 때 과연 이 기능을 도입해야 하는가? 하는 의문이 든다. 단순히 데이터베이스의 복구만으로 기능을 평가하기에는 의심스러운 부분이 많다. 라이브 환경과 같은 환경에서 테스트를 해도 제약이 있는 것이다. 결국 운영 중인 Live 환경에서 테스트를 해야 문제가 있는지 판단할 수 있을 것이다. 그러나, 비교 내역을 보고 라이브 환경에 크게 영향을 주지 않는다고 판단되면, ADR 옵션을 활성화 해 모니터링 해 보는 것도 나쁘지 않을 것이다.