안녕하세요. SQLER의 코난 김대우입니다.
이번 강좌에서는, 4-7. 최적의 데이터베이스 구성을 진행 하겠습니다.
SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록
이번 강좌에서는 데이터베이스 생성과 성능 최적화를 살펴봅니다.
TL;DR
데이터베이스 성능 최적화를 위해 하드웨어 구성과, 데이터베이스 설정을 고려할 수 있습니다. 또한, 파일 및 파일 그룹 배치와 테이블 및 인덱스 배치도 고려할 수 있습니다.
클라우드 시대에 무슨 데이터베이스를 위한 성능 최적화 구성?
클라우드 시대입니다. XaaS로 모든 서비스가 다 클라우드에서 실행됩니다. 클라우드의 PaaS는 우리 개발자들을 인프라와 플랫폼 모두에서 해방시키는 기적 같은 서비스입니다.
☑️ 챗GPT 활용: XaaS 클라우드에 대해서 설명해 줘
SQL Server와 같은 데이터베이스 역시 마찬가지입니다. PaaS 기반 데이터 서비스로 Microsoft Azure의 Azure SQL Database와 AWS의 RDS for SQL Server, Google Cloud의 Cloud SQL 제품이 바로 이런 완전 관리형 PaaS입니다.
버튼만 누르면 Scale-up으로 데이터베이스 쿼터를 증가시켜 성능을 높일 수 있고, Scale-out으로 분산처리 데이터베이스로 즉시 설정 가능합니다.
대부분 기업에서 이미 모든 On-Premise의 앱과 서비스를 클라우드로 이전하고 PaaS 데이터 서비스를 사용하는 중에 SQL Server의 데이터베이스 디스크 구성은 큰 가치를 제공하기 어렵습니다.
하지만, 지금까지 공부한 데이터베이스와 로그, FILESTREAM 등의 구조를 이해하면 PaaS 데이터 서비스도 더 잘 사용하실 수 있습니다.
데이터베이스 구성 - 성능 최적화
만수르 님 회사라 박스당 1억짜리 스토리지를 열개 구성해 데이터베이스를 설정하는 것도 한 방법이지만, 대부분의 경우 불가능하니 아래와 같은 기본 공식을 염두에 두고 데이터베이스를 생성합니다.
최적화 구성 기본 고려사항
1. 모든 디스크마다 디스크 컨트롤러 구성
2. 빠른 디스크를 사용할 경우 더 빠른 처리 가능
3. 하드웨어 전용 RAID나 미러링 시스템을 사용
4. 여러 개의 디스크가 성능에 유리
1번 디스크 = OS 부팅 + 스왑
2번 디스크 = DATA디스크 1
3번 디스크 = DATA디스크 2
4번 디스크 = 색인용 디스크 1
5번 디스크 = 색인용 디스크 2
…
9번 디스크 = LOG 디스크
5. 시스템 데이터베이스와 사용자 데이터베이스를 분리
6. 데이터베이스 파일 그룹(file group)으로 물리적으로 분리된 디스크에 데이터 파일들을 목적에 맞춰 생성
7. 읽기/쓰기가 많은 테이블이나 인덱스를 빠른 디스크 파일 그룹에 생성
8. 쓰기 작업이 많을 경우 트랜잭션 로그 파일의 위치를 독립된 장소에 생성 고려
9. 임시 테이블 처리가 과도하게 사용될 경우 tempdb를 독립된 장소에 생성 고려
10. OS 디스크와 분리
11. JOIN 구문으로 자주 사용되는 테이블이 각각 다른 디스크에 위치하면, 쿼리 최적화기가 병렬로 검색 가능해 성능에 유리
12. 자주 사용되는 테이블과 비클러스터형(Non-Clustered) 인덱스는 여러 파일 그룹에 나눠서 다른 디스크에 분산하면 쿼리 최적화기가 IO를 병렬로 분산해 처리 가능
13. 데이터 백업 파일과 로그 백업 파일을 격리된 안전한 장소에 저장
☑️ 챗GPT 활용: 디스크 컨트롤러, RAID, 미러링에 대해 알려줘
파일그룹으로 데이터베이스를 생성
아래의 쿼리를 이용해 분산된 파일그룹을 생성할 수 있습니다. 여러 디스크가 있다면 쿼리를 수정해 여러 디스크로 나눠서 데이터베이스와 파일 그룹을 생성하세요.
USE master;
GO
-- DB를 기본 데이터 파일 그룹, FILESTREAM 파일그룹, 로그파일로 생성
-- 데이터파일에 크기와 증가분 설정
CREATE DATABASE MyFGDB
ON PRIMARY
( NAME='MyDB_Primary',
FILENAME=
'c:\sql_data\MyDB_Prm.mdf',
SIZE=4MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
FILEGROUP MyDB_FG1
( NAME = 'MyDB_FG1_Dat1',
FILENAME =
'c:\sql_data\MyDB_FG1_1.ndf',
SIZE = 1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
( NAME = 'MyDB_FG1_Dat2',
FILENAME =
'c:\sql_data\MyDB_FG1_2.ndf',
SIZE = 1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM
( NAME = 'MyDB_FG_FS',
FILENAME = 'c:\sql_data\filestream1')
LOG ON
( NAME='MyDB_log',
FILENAME =
'c:\sql_data\MyDB.ldf',
SIZE=1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB);
GO
--MyDB_FG1를 기본 파일 그룹으로 변경
ALTER DATABASE MyFGDB
MODIFY FILEGROUP MyDB_FG1 DEFAULT;
GO
-- 테이블을 사용자 정의 파일그룹에 생성
USE MyFGDB;
CREATE TABLE MyTable
( cola int PRIMARY KEY,
colb char(8) )
ON MyDB_FG1;
GO
-- FILESTREAM 파일그룹에 테이블 생성.
CREATE TABLE MyFSTable
(
cola uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE,
colb VARBINARY(MAX) FILESTREAM
);
GO
만약, “FILESTREAM이 활성화되지 않았다”는 오류가 발생하면, 4-4. 데이터베이스 옵션 강좌의 FILESTREAM설정을 참조해 설정하고 진행하세요.
이미지 - 데이터베이스 파일 그룹
이런 패턴으로(파일 경로는 다르지만) 쿼리를 이용해 파일그룹을 분산해 생성합니다.
데이터베이스 파일 및 파일 그룹 권장사항
마이크로소프트에서 제공하는 파일 및 파일 그룹 작업 시 권장 사항 가이드입니다. 더 상세한 내용은 아래 링크를 참조하세요.
- 대부분의 데이터베이스에는 하나의 데이터 파일과 하나의 트랜잭션 로그 파일만 있으면 됩니다.
- 여러 데이터 파일을 사용하는 경우 추가 파일에 대한 두 번째 파일 그룹을 만들고 해당 파일 그룹을 기본 파일 그룹으로 만듭니다. 이렇게 하면 주 파일에는 시스템 테이블과 개체만 있게 됩니다.
- 성능을 극대화하려면 가능한 여러 개의 사용 가능한 디스크에 파일이나 파일 그룹을 만듭니다. 디스크 공간이 많이 필요한 개체는 여러 파일 그룹에 배치합니다.
- 특정 물리적 디스크에 개체를 배치할 수 있도록 파일 그룹을 사용합니다.
- 동일한 조인 쿼리에서 사용되는 여러 테이블은 여러 파일 그룹에 배치합니다. 이 단계는 조인된 데이터에서 병렬 디스크 I/O 검색을 하기 때문에 성능이 향상됩니다.
- 자주 액세스되는 테이블과 해당 테이블에 속한 비클러스터형 인덱스는 여러 파일 그룹에 배치합니다. 여러 파일 그룹을 사용하면 파일이 여러 물리적 디스크에 있을 경우 병렬 I/O가 수행되기 때문에 성능이 향상됩니다.
- 트랜잭션 로그 파일은 다른 파일 및 파일 그룹과 동일한 물리적 디스크에 배치하지 마세요.
- Diskpart와 같은 도구를 사용하여 데이터베이스 파일이 상주하는 볼륨 또는 파티션을 확장해야 하는 경우 모든 시스템 및 사용자 데이터베이스를 백업하고 먼저 SQL Server 서비스를 중지해야 합니다. 또한 디스크 볼륨이 성공적으로 확장되고 나면 DBCC CHECKDB 명령을 실행하여 해당 볼륨에 있는 모든 데이터베이스의 물리적 무결성을 보장해야 합니다.
- 데이터베이스 파일 및 파일 그룹 - SQL Server | Microsoft Learn
SQL 강좌 책 구매
강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다.
책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022
