SQL Server 2005부터 H/W NUMA를 본격적으로 지원하고 있습니다.
NUMA란 Non-Uniform Memory Access으로 간단하게 특정 CPU 그룹 단위로
전용 메모리 공간을 가지는 것을 말합니다.
기존 SMP환경에서는 다수의 CPU가 동일한 메모리를 바라보게 되어
메모리 엑세스 경합이라는 문제가 발생하여 이러한 문제를 극복하고자 NUMA 아키텍쳐가
소개되었습니다.
기존 x86, x64 프로세스에서는 AMD의 옵테론 정도만 H/W NUMA를 지원하고 있었으나,
요즘 INTEL에서 발표된 네할렘 CPU도 메모리 컨트롤러가 CPU 내부로 들어가면서
CPU 당 NUMA 노드 하나씩 설정 할 수 있게 되었습니다.
그렇다면 SQL Server에서 NUMA를 사용하는 것이 기존 SMP환경보다 더 좋을까요?
여러 CPU가 하나의 메모리를 바라보지 않아도 되고, NUMA 노드별로 Lazy Write 프로세스가
독립적으로 생겨 성능상 이점을 줄 수 있습니다. 하지만 NUMA 또한 단점이 있습니다.
CPU 노드별로 NUMA 노드가 생기는 경우를 예로 들어보면
내가 어떠한 쿼리를 수행하기 위해서 필요한 데이터가 로컬 메모리에 있을 수도 있고,
리모트 메모리에 있을 수도 있습니다. 만약 모든 데이터가 로컬 메모리에 존재한다면
특별한 추가적인 작업 없이 데이터를 엑세스 할 수 있을 것이니다. 하지만 리모트에
데이터가 있다면 추가적인 오버헤드가 발생하게 됩니다. 하지만 일반적으로 메모리가 워낙 빨라
로컬 메모리에서 읽든, 리모트에서 읽든 사용자는 체감하기는 쉽지 않습니다.
그럼 과연 NUMA를 사용하는 SQL Server환경에서 리모트 메모리 엑세스와 로컬 메모리 엑세스가
눈에 보이는 성능 차이가 발생할지 또 어느정도 차이가 발생할지 살펴보겠습니다.
테스트는 두 가지로 샘플 테이블을 만들고 테이블을 쭉 읽는 SCAN 작업과 넌 클러스터 인덱스에서
데이터 페이지를 Lookup하도록 하여 테스트 하였습니다. 노드별 시간을 측정하기 위해 우선 NUMA
노드별로 데이터를 올리기 위해 TCP/IP 포트와 NUMA노드를 매핑하여 테스트 하였습니다.
1000번 포트로 연결하면 0번 NUMA 노드로, 2000번 포트로 연결하면 1번 NUMA 노드만을
사용하도록 세팅하였습니다. 모든 데이터 페이지를 비운 후 1000번 포트로 접근하여, 0번 노드의
Freepage에 데이터 페이지를 채운 후 두 개의 쿼리를 각각 5번씩 수행하였습니다.
처음으로 살펴볼 것은 lookup이 발생하는 쿼리 입니다.
SET STATISTICS TIME은 ms 단위이기에 두 경우가 비슷하게 나오는 경향이 있어 DMV를 통해
응답시간을 비교했습니다.
아래 표에서 볼 부분은 avg_Elapsed_Time(ms) 입니다.
로컬 메모리 엑세스와 리모트 메모리 엑세스의 평균 수행시간이 12ms정도 차이가 나는 것을 볼 수 statement_text ExecutionCount avg_Elapsed_Time(ms) max_Elapsed_Time(ms) avg_Worker_Time(ms) avg_logical_reads 로컬(0번 노드) SELECT @COL = COL2 FROM test..tbl WITH(NOLOCK,index=2) OPTION(MAXDOP 1) 5 391.406 404.296 391.406 106541 리모트(1번 노드) SELECT @COL = COL2 FROM test..tbl WITH(NOLOCK,index=2) OPTION(MAXDOP 1) 5 403.906 413.085 403.906 106541
있습니다.
두 번째로 살펴볼 것은 SCAN하는 쿼리입니다.
로컬 메모리 엑세스와 리모트 메모리 엑세스의 평균 수행시간이 4ms정도 차이가 나는 것을 볼 수
있습니다.
|
statement_text |
ExecutionCount |
avg_Elapsed_Time(ms) |
max_Elapsed_Time(ms) |
avg_Worker_Time(ms) |
avg_logical_reads | |
|
로컬(0번 노드) |
SELECT @COL = COL2 FROM test..tbl WITH(NOLOCK) OPTION(MAXDOP 1) |
5 |
293.554 |
303.71 |
293.554 |
100031 |
|
리모트(1번 노드) |
SELECT @COL = COL2 FROM test..tbl WITH(NOLOCK) OPTION(MAXDOP 1) |
5 |
297.851 |
304.687 |
297.851 |
100031 |
이러한 결과를 볼 때 분명 리모트 메모리 엑세스는 분명 로컬 메모리 엑세스 보다 느린 것을 볼 수
있습니다. 그렇다면 이러한 문제를 해결 할 수 있는 방법은 없을까? 아니 보다 효율적으로 사용할 수
있는 방법은 없을까에 대한 고민은 다음에 정리하도록 하겠습니다.
송 혁, SQL Server MVP
sqler.com // sqlleader.com
hyoksong.tistory.com
1. 물리적 CPU 리소스 분산이 필요한 경우
하나의 SQL Server에서 다른 타입의 쿼리가 수행되는 경우에
원활한 서비스를 위해 물리적인 리소스(CPU)를 분산 시켜 주어야 하는 이슈가 있을 수 있습니다.
예를 들어보면, 하나는 CPU자원을 많이 사용하지는 않지만, 많이 요청되는 OLTP성 쿼리,
다른 하나는 많이 수행되지는 않지만 CPU자원을 많이 사용하는 배치성 쿼리인 환경을 볼 수 있습니다.
이러한 경우에 배치성 쿼리로 인해 OLTP 쿼리 성능에 영향을 줄 수 있습니다.
이때 해결 할 수 있는 방법으로는 각각의 쿼리 타입마다 물리적인 리소스(CPU)를 할당하여
같은 다른 타입의 형태의 쿼리가 같은 CPU를 사용하지 못하게 한다면, 위와 같은 문제를 처리할 수 있습니다.
2. 해결할 수 있는 방법들
가장 먼저 떠오르는 방법은,
하나의 물리적인 머신에 두 개의 인스턴스를 올리고, affinity mask를 설정하는 것입니다.
또한 물리적인 메모리를 각각의 인스턴스에서 설정하여, 메모리 크기도 조절할 수 있는
장점이 있습니다. 하지만, 인스턴스가 다른 관계로 데이터를 동기화 해주어야 하는 부담이
존재하게 되고 전체적인 운영비용이 높아질 수 밖에 없습니다.
뭐~ 다른 방법으로 그냥 MAXDOP를 줘서 CPU사용을 제한 하는 것도 방법일 수 있지만,
MAXDOP의 경우 물리적인 CPU 지정 및 사용량을 지정을 할 수 없기에
배치가 수행되는 CPU에서 수행되는 OLTP성 쿼리는 여전히 문제가 될 수 있습니다.
음~ 그럼 SQL Server 2008의 리소스 관리자를 생각할 수 있습니다.
하지만 SQL Server 2008 Enterprise에서만 지원하는 제약이 있습니다.
3. 또 하나의 방법 NUMA
여기서 소개하려는 방법은 NUMA 를 이용한 방법 입니다.
상황에 따라 최선의 솔루션은 달라지기에 모든 상황에 대한 최선의 솔루션이라고는 볼 수 없습니다.
그리고 여기서는 NUMA를 소개하고자 하는 것이
아니기에 NUMA에 대한 소개는 건너뛰겠습니다.
요즘 판매되는 네할렘 기반의 프로세스는 메모리 컨트롤러가 CPU내부에 위치하고 있어
기본적으로 2CPU 이상인 경우 NUMA를 지원하고 있습니다.
만약 NUMA를 지원하지 않는 환경이라도 soft NUMA를 설정 할 수 있기에 크게 상관은 없습니다.
그럼 NUMA를 가지고 어떻게 할 수 있을까요?
BOL을 보면 NUMA 노드 별로 TCP/IP 포트를 설정할 수 있는 방법을 소개 하고 있습니다.
바로 이 설정으로 쿼리 타입별 CPU 리소스를 분리 할 수 있습니다.
만약 하드웨어 NUMA로 지원되는 노드가 아니라 더 세분하게 CPU 리소스를 분산하고자 한다면
soft NUMA를 추가적으로 설정해서 처리할 수 있습니다.
간단하게 예를 들면 1000포트는 0번 NUMA 노드, 2000번 포트는 1번 NUMA 노드,
3000번 포트는 모든 NUMA를 사용하게 설정 할 수 있습니다
이렇게 설정하게 된 후 각각의 클라이언트에서 각 쿼리 타입별로 사용 포트를 변경해서
사용하면 CPU 리소스 간섭 없이 처리 할 수 있습니다.
간단한 구성 방법은 아래를 참고하시길 바랍니다.
[soft NUMA 설정 방법] http://msdn.microsoft.com/ko-kr/library/ms345357.aspx
Soft NUMA를 설정하거나, NUMA를 지원하는 장비라면 sp_readerrorlog에서 아래와 같은 메시지를 확인할 수 있습니다.
2009-08-28 15:10:23.940 서버 Node configuration: node 0: CPU mask: 0x000000aa Active CPU mask: 0x000000aa.
2009-08-28 15:10:23.940 서버 Node configuration: node 1: CPU mask: 0x00000055 Active CPU mask: 0x00000055.
TCP/IP의 포트를 NUMA노드에 맵핑 후 재시작 하면 SP_READERORLOG 에서는 아래와 같이 확인할 수 있습니다.
2009-08-28 15:10:24.860 서버 Server is listening on [ 'any' <ipv4> 3000].
2009-08-28 15:10:24.860 서버 SQL Server Network Interfaces initialized listeners on node 0 of a multi-node (NUMA) server configuration with node affinity mask 0x00000003.
2009-08-28 15:10:24.860 서버 Server is listening on [ 'any' <ipv4> 1000].
2009-08-28 15:10:24.860 서버 SQL Server Network Interfaces initialized listeners on node 0 of a multi-node (NUMA) server configuration with node affinity mask 0x00000001.
2009-08-28 15:10:24.860 서버 Server is listening on [ 'any' <ipv4> 2000].
2009-08-28 15:10:24.860 서버 SQL Server Network Interfaces initialized listeners on node 1 of a multi-node (NUMA) server configuration with node affinity mask 0x00000002.
각각의 포트별로 스트레스를 주고 작업관리자를 확인해 보면, NUMA 노드 만을 사용하는 것을 확인할 수 있습니다
|
1000번 포트 사용 |
2000번 포트 사용 |
3000번 포트 사용 |
|
|
|
|
송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com
얼마전에는 캐시된 SP의 XML플랜을 기반으로 컴파일 정보 확인 하는 것을 소개하였습니다.
이번에도 XML플랜을 이용하여 캐시된 플랜 중 FULL SCAN 하는 SP를 찾는 방법에 대해 소개합니다.
[성능 모니터 – FULL Scans/Sec]
위와 같이 운영하고 있는 서버에서 많은 FULL SCAN 수치가 보인다면,
해당 서버는 성능적으로 문제가 발생할 가능성이 있다고 생각해 볼 수 있습니다.
그렇다면 빠르고 쉽게 FULL SCAN하는 쿼리를 찾아 수정 하여야 할 것 입니다.
하지만, 기본적인 DMV로는 인덱스에 대한 SCAN 수에 대해서는 확인 할 수 있지만, SP에 대해서는 SCAN 여부 조차 확인 할 수 없습니다.
그래서 DMV로 확인 할 수 있는 XML플랜을 XQUERY를 사용하여 찾아보는 쿼리를 만들어 보았습니다.
with XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql)
SELECT * FROM
(
SELECT
db_name(qt.dbid) AS 'DB Name'
,qt.dbid
,OBJECT_NAME(qp.objectid,qp.dbid) as sp_name
,qt.text AS 'sp_text'
, substring(qt.text, (qs.statement_start_offset/2)+1
, ((case qs.statement_end_offset
when -1 then datalength(qt.text)
else qs.statement_end_offset
end - qs.statement_start_offset)/2) + 1) as statement_text
, qs.creation_time
, qs.execution_count AS 'Execution Count'
, ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second'
, DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
, ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime(㎲)'
, qs.total_elapsed_time/1000.0/1000.0 AS 'TotalElapsedTime(sec)'
, max_elapsed_time /1000.0 AS 'maxelapsedTime(ms)'
, qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime(㎲)'
, qs.total_worker_time AS 'TotalWorkerTime(㎲)'
, max_worker_time as 'max_worker_time(㎲)'
, ISNULL(qs.total_logical_reads/qs.execution_count, 0) AS 'AvgLogicalreads'
, total_logical_reads
, qs.max_logical_reads
, ISNULL(qs.total_physical_reads/qs.execution_count, 0) AS 'AvgphysicalReads'
, total_physical_reads
, qs.max_physical_reads
, ISNULL(qs.total_logical_writes/qs.execution_count, 0) AS 'AvglogicalWrites'
, qs.total_logical_writes
, qs.max_logical_writes
,text
,cast(query_plan as xml) as query_plan
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(plan_handle) as qt
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,qs.statement_start_offset,qs.statement_end_offset) qp
)Y
CROSS APPLY
(
SELECT
c.value('(./@PhysicalOp)[1]','varchar(100)') as PhysicalOp
FROM Y.query_plan.nodes('//sql:RelOp')B(C)
)X
where PhysicalOp IN ('Table Scan','Index Scan')and
Y.dbid not in (1,2,3,4,32767)
[결과]
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com
