로컬 인스턴스 스토리지를 사용하여 SQL Server용 Amazon RDS Custom에서 TempDB 성능 최적화

이 글은 AWS Database Delivery Blog에 게시된 Optimize TempDB performance in Amazon RDS Custom for SQL Server using local instance storage by Kalyan Banala을 한국어 번역 및 편집하였습니다.

이제 SQL Server용 Amazon RDS(Amazon Relational Database Service) Custom은 메모리 대 vCPU 비율이 높고 짧은 지연 시간, 향상된 랜덤 I/O 성능, 높은 순차 읽기 처리량에 최적화된 NVMe(비휘발성 메모리 익스프레스) SSD 기반 인스턴스 스토리지인 X2iedn 인스턴스 클래스를 지원합니다.

이 게시물에서는 TempDB 성능을 최적화하기 위해 로컬 NVMe 디스크를 사용하는 방법을 보여줍니다. 로컬 스토리지(GUI 및 PowerShell 사용)와 TempDB를 구성 및 초기화하기 위한 단계별 지침을 제공하고 로컬 스토리지의 TempDB와 Amazon Elastic Block Store(Amazon EBS) 스토리지의 성능 비교를 제공하며 로컬 인스턴스 스토어를 사용할 때의 TempDB 최적화 모범 사례를 논의합니다.

SQL Server TempDB 개요

SQL Server의 TempDB 시스템 데이터베이스는 인스턴스에 연결된 모든 사용자가 액세스할 수 있는 전역 리소스입니다. 또한 데이터베이스 엔진에서 생성된 임시 사용자 개체와 내부 개체를 저장하는 데에도 사용됩니다. 임시 사용자 개체에는 전역 또는 로컬 임시 테이블, 임시 저장 프로시저, 테이블 변수, 테이블 반환 함수에서 반환된 테이블 및 커서가 포함됩니다. 데이터베이스 엔진에서 생성된 내부 개체에는 스풀, 커서, 정렬 및 임시 LOB(대형 개체) 저장소와 같은 작업에 대한 중간 결과를 저장하기 위한 작업 테이블이 포함됩니다. 또한 해시 조인 또는 해시 집계 작업을 위한 작업 파일과 인덱스 생성 또는 재구축, 특정 GROUP BY, ORDER BY 또는 UNION 쿼리와 같은 작업에 대한 중간 정렬 결과도 포함됩니다.

많은 작업에서 TempDB의 역할을 고려하면 특히 많은 쿼리에 임시 저장소가 필요하거나 스냅샷 격리, 결과 집합 정렬 또는 집계 사용자 쿼리와 같은 기능을 많이 사용하는 경우 높은 I/O가 발생할 수 있습니다. TempDB는 많은 세션이 동시에 TempDB에 공간을 할당하려고 할 때 경합을 경험할 수 있으며, 이로 인해 전반적인 성능 문제가 발생할 수도 있습니다.

로컬 TempDB의 장점은 다음과 같습니다:

로컬 스토리지의 TempDB 성능은 대기 시간이 짧기 때문에 읽기/쓰기 속도가 더 빠릅니다. 또한 향상된 랜덤 I/O 성능과 높은 순차 읽기 처리량을 제공하여 전체 대기 시간을 줄입니다.
워크로드에 따라 TempDB 성능은 최대 20% 향상될 수 있습니다.
보조 TempDB 파일이 로컬 인스턴스 스토어에 배치되므로 전체 EBS 스냅샷 비용을 줄일 수 있습니다.

전제 조건으로 SQL Server 인스턴스용 RDS Custom이 있어야 합니다. 인스턴스 설정에 대한 자세한 내용은 AWS CloudFormation을 사용하여 SQL Server 인스턴스용 Amazon RDS 사용자 지정 시작을 참조하세요.

GUI를 사용하여 로컬 NVMe SSD 스토리지 구성 및 초기화

RDP를 통해 Amazon Elastic Compute Cloud(Amazon EC2) 인스턴스에서 SSD 디스크 사용을 초기화하고 시작하려면 다음 단계를 완료하세요.

RDP(원격 데스크톱 프로토콜)를 사용하여 RDS 사용자 지정 EC2 인스턴스에 연결합니다.
로그인하려면 인스턴스의 퍼블릭 IP 주소 또는 DNS 이름과 자격 증명이 필요합니다.
시작 메뉴 검색에 ‘Disk Management’를 입력하고 해당 결과를 선택하여 디스크 관리 창을 엽니다.

디스크 관리 창에 디스크 목록이 표시됩니다. 새 SSD 디스크는 공간이 할당되지 않은 알 수 없는 디스크로 나타날 가능성이 높습니다(다음 스크린샷의 Disk1).

3. Disk1(‘초기화되지 않음’으로 표시됨)을 선택(마우스 오른쪽 버튼으로 클릭)한 다음 Initialize Disk를 선택하여 파티션 설정을 시작합니다.
프롬프트에 따라 적절한 디스크 초기화 유형(이 경우 GPT)을 선택하고 OK를 선택하여 초기화를 진행합니다.
디스크를 초기화한 후 할당되지 않은 공간을 선택(마우스 오른쪽 버튼 클릭)하고 New Simple Volume을 선택합니다.
마법사를 따라 SSD에 새 파티션을 생성하려면 Next를 선택합니다.
파티션 크기와 드라이브 문자를 선택하고 파일 시스템(예: NTFS)으로 파티션을 포맷할 수 있습니다.
DB 인스턴스 클래스를 기반으로 로컬 스토리지 구성합니다.
드라이브 문자를 할당합니다. (이 경우 T, 아직 사용하지 않는 드라이브 문자를 선택합니다.)
적절한 파일 시스템(여기서는 NTFS)으로 포맷하고 볼륨에 레이블을 지정합니다. (여기서는 Temp 레이블을 선택했습니다.)
Next를 선택하여 파티션 생성을 계속합니다.
새로 생성된 파티션을 선택(마우스 오른쪽 버튼 클릭)하고 Format을 선택합니다.
원하는 파일 시스템과 할당 단위 크기를 선택합니다.
Next를 선택하여 파티션 포맷을 완료합니다.

이제 SSD 디스크가 초기화되어 사용할 준비가 되었습니다. 할당된 드라이브 문자(이 사용 사례의 경우 T)를 사용하여 액세스할 수 있습니다.

PowerShell을 사용하여 로컬 NVMe SSD 스토리지 구성 및 초기화

예시 사용 사례에서는 X2iedn 인스턴스에 하나의 로컬 SSD 스토리지가 있습니다. 관리자로 다음 PowerShell 명령을 실행하여 로컬 NVMe SSD 스토리지를 구성하고 초기화할 수 있습니다.

#Identify all available Disks
#Get all disks
$disks = Get-Disk

#Find the local SSD disk that needs to be configured 
$ssd = $disks | Where-Object { $_.OperationalStatus -eq 'offline' -or $_.PartitionStyle -eq "RAW" }

#Initialize the Disk
$ssd | Initialize-Disk -PartitionStyle GPT

#Create a New Partition
$partition = $ssd | New-Partition -UseMaximumSize

#Assign a unused Drive Letter in this example we used T 
$partition | Set-Partition -NewDriveLetter T 

#Format the Partition by provinding volume name, in this example we used 'Temp'
$partition | Format-Volume -FileSystem NTFS -NewFileSystemLabel 'Temp'

로컬 스토리지에 TempDB 구성

로컬 스토리지에 TempDB를 구성하려면 다음 단계를 완료하세요.

보조 TempDB 파일을 만드는 데 사용할 새 로컬 드라이브를 식별합니다. 이 드라이브에 TempDB를 지원할 만큼 충분한 디스크 공간과 I/O 용량이 있는지 확인하세요.
SSMS(SQL Server Management Studio)를 열고 SQL Server 인스턴스에 연결한 다음 master 사용자를 사용하여 SSMS에 연결합니다.
다음 SQL 명령을 실행하여 TempDB의 현재 구성과 해당 파일 위치를 검사합니다.
```
USE tempdb;    
 EXEC sp_helpfile;
```
기존 파일 위치를 기록해 두세요.

보조 TempDB 파일을 구성하기 전에 보조 추가 TempDB 파일이 저장될 새 드라이브에 디렉터리를 만듭니다. 이 예에서는 추가 TempDB 파일을 저장하기 위해 T 드라이브에 디렉터리와 T:\dsdbdata\\DATA 경로를 만들었습니다. 디렉터리를 생성하는 데 필요한 권한이 있는지 확인하세요. 또는 관리자 권한으로 다음 PowerShell 명령을 실행하여 디렉터리 구조를 만들고 권한을 복사할 수도 있습니다.

# Source directory in our case it is "D:\rdsdbdata\DATA"
$source_directory = "D:\rdsdbdata\DATA"
# Display the source directory 
Write-Host "Source directory is: $source_directory"

# Destination directory, in our case it is "T:\rdsdbdata\DATA"
$destination_directory = "T:\rdsdbdata\DATA"
# Display the destination directory 
Write-Host "Destination directory will be: $destination_directory"

# Get the directory structure from the source directory
Write-Host "Getting directory structure from source..."
$sub_directory = Get-ChildItem -Path $source_directory -Recurse | Where-Object { $_.PSIsContainer }

# Create the destination directory
Write-Host "Creating destination directory..."
New-Item -ItemType Directory -Force -Path $destination_directory

# Get the permissions from the source directory

다음 SQL 명령을 템플릿으로 사용하여 새로 생성된 드라이브에 보조 TempDB 파일을 생성합니다. 요구 사항에 따라 파일 경로와 크기를 조정합니다. 모든 TempDB 데이터베이스 파일을 동일한 초기 크기로 설정하는 것이 좋습니다.

USE master;
GO
ALTER DATABASE tempdb
ADD FILE (
    NAME = tempdev1, -- Example file name
    FILENAME = 'T:\rdsdbdata\DATA\tempdev1.ndf', -- we have to  Specify the new file path
    SIZE = 300GB, -- Set the file size
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 64MB -- Set the autogrowth size
);

필요에 따라 이 명령을 반복하여 필요한 수의 추가 TempDB 파일을 만들고 새 위치에 추가합니다.
sp_helpfile 명령을 다시 실행하여 TempDB 파일이 새 드라이브에 성공적으로 생성되었는지 확인합니다.
```
USE tempdb;
EXEC sp_helpfile;
```

로컬 및 비로컬 TempDB에 대한 성능 벤치마크

X2ieden 인스턴스의 단일 AZ 및 다중 AZ 배포에서 TempDB 성능을 분석하고, SQLQueryStress 도구를 사용하여 다양한 구성에서 TempDB 워크로드를 시뮬레이션했습니다.

다음 구성으로 벤치마킹하기 위해 db.x2iedn.16xlarge 인스턴스를 사용했습니다.

SQL Server 구성:
- 버전: CU20이 포함된 Enterprise Edition 2019
- TempDB 데이터 파일: 기본 추가 TempDB 데이터 파일 1개 및 보조 추가 TempDB 데이터 파일 6개(각각 352,250MB)
- SQL Server 최대 메모리: 20GB(20GB로 제한하여 더 많은 디스크 I/O를 시뮬레이션합니다. 이는 테스트 목적으로만 사용하세요.)
SQLQueryStress 도구 구성을 사용한 테스트 세부정보:
- 반복: 500
- 스레드: 1
- 지연: 0

TempDB에 상주하는 두 개의 임시 테이블 #tempOrders 및 #tempOrderDetails를 만들었습니다. 이 테이블은 대규모 애플리케이션 데이터를 모방하기 위해 각각 100만 개와 500만 개의 레코드로 채워졌습니다.

벤치마킹 쿼리를 위해 이러한 테이블을 조인하고 집계를 수행하고 결과를 필터링했습니다. 이 복잡한 쿼리는 대규모 데이터 세트와 집약적인 작업을 처리할 때 TempDB의 효율성을 테스트하여 성능에 대한 통찰력을 제공합니다.

각 반복에서 쿼리를 실행한 후에는 후속 테스트에서 공정하고 새로운 비교를 보장하기 위해 SQL Server 캐시를 지운 다음 임시 테이블을 삭제하여 정리하고 다음 반복 작업에 대한 준비 상태를 만들었습니다.

다음 코드를 참조하세요.

--Workload queries for tempdb performance evaluation
-- Create Temporary Tables for benchmark test :
CREATE TABLE #tempOrders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATETIME,
Amount DECIMAL(10,2)
);

CREATE TABLE #tempOrderDetails (
DetailID INT PRIMARY KEY,
OrderID INT,
ProductID INT,
Quantity INT
);

-- Populate Temporary Tables with sample test Data:
-- Populate #tempOrders
INSERT INTO #tempOrders
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY a.name) AS OrderID,
ABS(CHECKSUM(NEWID())) % 5000 AS CustomerID,
DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 365, '2023-01-01') AS OrderDate,
ABS(CHECKSUM(NEWID())) % 1000 AS AmountFROM sys.all_objects aCROSS JOIN sys.all_objects b;

-- Populate #tempOrderDetails
INSERT INTO #tempOrderDetails
SELECT TOP 5000000 ROW_NUMBER() OVER (ORDER BY a.name) AS DetailID,
ABS(CHECKSUM(NEWID())) % 1000000 AS OrderID,
ABS(CHECKSUM(NEWID())) % 10000 AS ProductID,
ABS(CHECKSUM(NEWID())) % 100 AS QuantityFROM sys.all_objects aCROSS JOIN sys.all_objects b;

-- Sample Complex Query for Benchmarking:
WITH CTE AS (
SELECT o.OrderID, o.CustomerID, o.OrderDate, o.Amount,
d.ProductID, d.Quantity,
(o.Amount * d.Quantity) AS TotalAmountFROM #tempOrders o
JOIN #tempOrderDetails d ON o.OrderID = d.OrderID
)
SELECT CustomerID, SUM(TotalAmount) AS GrandTotalFROM CTEGROUP BY CustomerIDHAVING SUM(TotalAmount) > 5000
ORDER BY GrandTotal DESC;

DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

DROP TABLE #tempOrders;
DROP TABLE #tempOrderDetails;

다음 스크린샷은 벤치마크 테스트에서 나온 것입니다.

다음 표는 로컬 스토리지로 구성된 다중 AZ 및 단일 AZ 인스턴스 모두에서 TempDB 성능이 우리가 적용한 테스트 매개 변수에 대해 경과 시간 및 반복 당 평균 실제 시간 측면에서 EBS 스토리지보다 성능이 20% 우수함을 나타냅니다. 이는 TempDB 워크로드가 높은 경우 로컬 스토리지 구성이 더 효율적이라는 것을 의미합니다. 성능 향상은 EBS 스토리지에 비해 로컬 스토리지가 제공하는 대기 시간이 감소하고 IOPS(초당 I/O 작업)가 더 높기 때문일 수 있습니다.

다음 TempDB 구성을 사용했습니다.

로컬 스토리지가 있는 다중 AZ(D 및 E 드라이브에 TempDB 구성)
EBS 스토리지가 포함된 Multi-Z(D 드라이브에만 TempDB 구성)
EBS 스토리지가 포함된 단일 AZ(D 드라이브에만 TempDB 구성)
로컬 스토리지가 있는 단일 AZ(D 및 E 드라이브에 TempDB 구성)

Configuration	Perform Metric Total (Disk Transfer/Second)	Elapsed Time (MM:SS)	Average Actual Time per Iteration (Seconds)	Elapsed Time Improvement (%)	Average Actual Time per Iteration Improvement (%)	Disk Transfer/Second Improvement (%)
Multi-AZ with EBS storage (only D drive)	1947.28	57:09	6.8627s	–	–	–
Multi-AZ with local storage (D and E drive)	2743.91	46:04	5.5263s	20%	20%	41%
Single-AZ with EBS storage (only D drive)	2200.94	57:54	6.9712s	–	–	–
Single-AZ with local storage (D and E drive)	3127.91	46:22	5.5663s	20%	20%	42%

SQL Server용 RDS Custom에서 X2iedn을 프로비저닝하는 모범 사례

다음 모범 사례를 고려하세요.

X2iedn 설정 – X2iedn 인스턴스 유형으로 RDS Custom을 시작하면 로컬 스토리지용으로 할당되지 않은 원시 디바이스만 갖게 됩니다. 사용하기 전에 이 장치를 올바르게 마운트하고 포맷해야 합니다. 그런 다음 최적의 성능을 보장하기 위해 TempDB를 구성할 수 있습니다. 다중 AZ 인스턴스의 경우 대기 인스턴스에서 구성을 수행하는 것이 좋습니다. 이렇게 하면 장애 조치가 발생하는 경우 구성이 이미 대기 인스턴스에 있으므로 시스템이 문제 없이 계속 작동합니다.
TempDB 파일 배치 – 로컬 스토리지의 이점은 상당하지만 로컬 연결 스토리지에는 보조 TempDB 파일만 배치할 수 있다는 점을 기억하는 것이 중요합니다. TempDB(tempdev 및 templog)의 기본 파일과 기타 사용자 및 시스템 데이터베이스는 항상 D 드라이브에 배치되어야 합니다. 이 배치를 준수하지 않으면 인스턴스가 경계 외부 상태로 유지되어 잠재적으로 작업이 중단될 수 있습니다.
TempDB 크기 – 단일 파일이 성능 병목 현상을 일으키는 것을 방지하려면 모든 TempDB 파일에 대해 일관된 크기를 유지하는 것이 좋습니다. 이 접근 방식은 균형 잡힌 I/O 작업을 보장하고 성능을 최대화합니다.
인스턴스 작업 – 이 설정을 사용하면 확장 컴퓨팅, 인스턴스 교체, 스냅샷 복원 또는 PITR(특정 시점 복구)과 같은 인스턴스 작업을 사용할 때 로컬 스토리지가 할당되지 않은 원시 상태로 되돌아갑니다. 이러한 상황에서는 기능을 적절하게 복원하려면 드라이브와 TempDB를 모두 다시 탑재하고, 다시 포맷하고, 재구성해야 합니다.
TempDB 구성 조정 – 인스턴스 작업 후에는 TempDB 구성을 변경하고 이에 따라 sys.metadata를 조정하는 것이 좋습니다. 이 작업을 수행하면 시스템 메타데이터가 TempDB 설정을 반영하여 잠재적인 충돌이나 문제를 방지할 수 있습니다.
모니터링 – 로컬 NVMe 드라이브의 TempDB 공간 사용량을 정기적으로 모니터링하고 TempDB 사용량에 따라 확장 또는 축소합니다.
적용하기 전에 항상 테스트 하세요. – 이 설정을 사용하여 프로덕션 환경에 중요한 변경 사항을 적용하기 전에 준비 또는 개발 환경에서 테스트하는 것이 좋습니다. 이렇게 하면 예상치 못한 문제나 성능 영향을 방지하는 데 도움이 됩니다.

결론

이 게시물에서는 높은 메모리 대 vCPU 비율과 NVMe SSD 지원 인스턴스 스토리지를 자랑하는 RDS Custom의 강력한 제품인 X2iedn 인스턴스 클래스의 기능에 대해 자세히 살펴보았습니다. 새 파티션을 생성하고 로컬 스토리지에 TempDB를 구성하는 과정을 안내하면서 이 설정이 제공할 수 있는 성능의 대폭 향상을 보여주었습니다. 성능 벤치마킹에서는 로컬 스토리지의 짧은 대기 시간, 높은 I/O 기능을 활용할 때 TempDB 성능이 향상되는 것을 보여주었습니다. 또한 X2iedn 인스턴스 클래스를 사용할 때 TempDB 구성을 최대한 활용할 수 있도록 하는 중요한 모범 사례를 강조했습니다.

RDS Custom을 언제 어디서 사용해야 하는지 자세히 알아보려면 SQL Server용 RDS Custom 작업을 확인하세요.

AWS 기술 블로그