메모리 내 OLTP 개요 및 사용 시나리오

  • 아티클

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

메모리 내 OLTP는 트랜잭션 처리, 데이터 수집, 데이터 로드 및 일시적인 데이터 시나리오의 성능을 최적화하기 위해 SQL Server 및 SQL Database에서 사용할 수 있는 최고의 기술입니다. 이 문서에서는 메모리 내 OLTP의 기술 및 사용 시나리오를 알아봅니다. 이 정보를 사용하여 메모리 내 OLTP가 애플리케이션에 적합 한지 확인할 수 있습니다. 이 문서에는 메모리 내 OLTP 개체를 보여 주는 예제, 성능 데모에 대한 참조 및 다음 단계에 사용할 수 있는 리소스에 대한 참조가 포함되어 있습니다.

메모리 내 OLTP 개요

메모리 내 OLTP는 적절한 워크로드에 대해 뛰어난 성능 향상을 제공할 수 있습니다. 어떤 경우에는 고객이 최대 30배의 성능 향상을 보았지만 워크로드에 따라 얻을 수 있는 이득이 달라집니다.

그렇다면 이러한 성능 향상은 어디에서 이루어지는 것일까요? 기본적으로 메모리 내 OLTP는 데이터 액세스 및 트랜잭션 실행의 효율성을 높이고 동시에 실행되는 트랜잭션 간에 잠금 및 래치 경합을 제거하여 트랜잭션 처리 성능을 향상시킵니다. 메모리 내 OLTP는 메모리 내이기 때문에 빠르지 않습니다. 메모리 내 데이터에 대한 최적화로 인해 속도가 빠릅니다. 메모리 내 및 높은 동시성 컴퓨팅의 최신 개선 사항을 활용하기 위해 데이터 스토리지, 액세스 및 처리 알고리즘이 처음부터 다시 설계되었습니다.

이제 데이터가 메모리에 있다고 해서 오류가 발생할 때 데이터가 손실되는 것은 아닙니다. 기본적으로 모든 트랜잭션은 완전 내구성이 있습니다. 즉, SQL Server의 다른 테이블에 대해 동일한 내구성을 보장합니다. 트랜잭션 커밋의 일부로 모든 변경 내용은 디스크의 트랜잭션 로그에 기록됩니다. 트랜잭션 커밋 후 언제든 오류가 발생한 경우 데이터베이스가 다시 온라인 상태로 전환되면 데이터가 그대로 유지됩니다. 또한 메모리 내 OLTP는 가용성 그룹, 장애 조치(failover) 클러스터 인스턴스, 백업/복원 등 SQL Server의 모든 고가용성 및 재해 복구 기능과 함께 작동합니다.

데이터베이스에서 메모리 내 OLTP를 사용하려면 다음 개체 유형 중 하나 이상을 사용합니다.

  • 메모리 최적화 테이블 은 사용자 데이터를 저장하는 데 사용됩니다. 테이블을 만들 때 메모리 최적화로 선언합니다.
  • 비영구 테이블 은 캐싱 또는 중간 결과 집합의 임시 데이터에 사용됩니다(기존의 임시 테이블 대체). 내구성이 없는 테이블은 DURABILITY=SCHEMA_ONLY 사용하여 선언된 메모리 최적화 테이블로, 이러한 테이블을 변경해도 IO가 발생하지 않습니다. 따라서 내구성이 중요하지 않은 경우 로그 IO 리소스 소모를 방지할 수 있습니다.
  • 메모리 최적화 테이블 형식 은 저장 프로시저에서 TVP(테이블 반환 매개 변수) 및 중간 결과 집합에 사용됩니다. 메모리 최적화 테이블 형식은 기존 테이블 형식 대신 사용할 수 있습니다. 메모리 최적화 테이블 형식을 사용하여 선언된 테이블 변수 및 TVP는 비지속성 메모리 최적화 테이블(효율적인 데이터 액세스 및 IO 없음)의 이점을 상속합니다.
  • 고유하게 컴파일된 T-SQL 모듈 은 작업을 처리하는 데 필요한 CPU 주기를 줄여 개별 트랜잭션에 소요되는 시간을 더 단축하는 데 사용됩니다. Transact-SQL 모듈을 생성 시 고유하게 컴파일하도록 선언합니다. 이 시점에서 고유하게 컴파일할 수 있는 T-SQL 모듈은 저장 프로시저, 트리거 및 사용자 정의 스칼라 함수입니다.

메모리 내 OLTP는 SQL Server 및 SQL Database에 기본 제공되어 있습니다. 이러한 개체는 기존 개체와 비슷한 방식으로 동작하기 때문에 데이터베이스 및 애플리케이션을 최소한의 변경만 하면서 성능 이점을 얻을 수 있습니다. 또한 메모리 최적화 테이블과 기존 디스크 기반 테이블을 모두 동일한 데이터베이스에 두고 두 데이터베이스에서 쿼리를 실행할 수 있습니다. 이 문서의 뒷부분에 있는 이러한 개체 유형 각각에 대한 샘플 Transact-SQL 스크립트 를 참조하세요.

메모리 내 OLTP에 대한 사용 시나리오

메모리 내 OLTP는 빠른 매직 버튼이 아니며 모든 워크로드에 적합하지 않습니다. 예를 들어 메모리 최적화 테이블은 대부분의 쿼리가 큰 데이터 범위에서 집계를 수행하는 경우 CPU 사용률을 낮추지 않습니다. 해당 시나리오에는 columnstore 인덱스가 도움이 됩니다.

주의

알려진 문제: 메모리 최적화 테이블이 있는 데이터베이스의 경우 복구 없이 트랜잭션 로그 백업을 수행하고 나중에 복구를 사용하여 트랜잭션 로그 복원을 실행하면 응답하지 않는 데이터베이스 복원 프로세스가 발생할 수 있습니다. 이 문제는 로그 전달 기능에도 영향을 줄 수 있습니다. 이 문제를 해결하기 위해 복원 프로세스를 시작하기 전에 SQL Server 인스턴스를 다시 시작할 수 있습니다.

다음은 고객이 메모리 내 OLTP에 성공한 시나리오 및 애플리케이션 패턴 목록입니다.

높은 처리량 및 짧은 대기 시간 트랜잭션 처리

이는 메모리 내 OLTP를 빌드한 핵심 시나리오입니다. 개별 트랜잭션에 대해 일관된 짧은 대기 시간으로 대량의 트랜잭션을 지원합니다.

일반적인 워크로드 시나리오는 금융 상품 거래, 스포츠 베팅, 모바일 게임 및 광고 배달입니다. 또 다른 일반적인 패턴은 자주 읽거나 업데이트하는 "카탈로그"입니다. 예를 들어 대용량 파일이 있고 각 파일이 여러 클러스터 노드에 분산된 경우 메모리 최적화 테이블에서 각 파일의 분할 위치에 대한 카탈로그를 작성합니다.

구현 고려 사항

핵심 트랜잭션 테이블, 즉 성능이 가장 중요한 트랜잭션이 있는 테이블에 메모리 최적화 테이블을 사용합니다. 비즈니스 트랜잭션과 관련된 논리 실행을 최적화하려면 고유하게 컴파일된 저장 프로시저를 사용합니다. 데이터베이스에 저장 프로시저로 푸시할 수 있는 논리가 많을수록 메모리 내 OLTP에서 더 많은 이점을 얻을 수 있습니다.

기존 애플리케이션에서 시작하려면 다음을 수행합니다.

  1. 트랜잭션 성능 분석 보고서를 사용하여 마이그레이션할 개체를 식별합니다.
  2. 메모리 최적화 관리자 및 네이티브 컴파일 관리자를 사용하여 마이그레이션을 지원합니다.

IoT(사물 인터넷)를 비롯한 데이터 수집

메모리 내 OLTP는 여러 다른 원본에서 대량의 데이터를 동시에 수집하는 데 적합합니다. 또한 SQL Server는 데이터에 대해 쿼리를 빠르게 실행하고 실시간 인사이트를 얻을 수 있기 때문에 다른 대상에 비해 SQL Server 데이터베이스에 데이터를 수집하는 것이 좋습니다.

일반적인 애플리케이션 패턴은 다음과 같습니다.

  • 센서 판독값 및 이벤트를 수집하고 기록 분석뿐만 아니라 알림을 허용합니다.
  • 동시 읽기 워크로드에 미치는 영향을 최소화하면서 여러 원본에서 일괄 업데이트를 관리합니다.

구현 고려 사항

데이터 수집에 메모리 최적화 테이블을 사용합니다. 수집이 주로 업데이트가 아닌 삽입으로 구성되고 데이터의 메모리 내 OLTP 스토리지 공간이 중요한 경우

SQL Server 샘플 리포지토리에는 temporal 메모리 최적화 테이블, 메모리 최적화 테이블 형식 및 고유하게 컴파일된 저장 프로시저를 사용하여 센서 데이터의 메모리 내 OLTP 스토리지 공간을 관리하는 동시에 데이터 수집을 가속화하는 스마트 그리드 애플리케이션이 포함되어 있습니다.

캐싱 및 세션 상태

메모리 내 OLTP 기술은 SQL Server 또는 Azure SQL 데이터베이스의 데이터베이스 엔진을 기본 세션 상태(예: ASP.NET 애플리케이션) 및 캐싱을 위한 매력적인 플랫폼으로 만듭니다.

ASP.NET 세션 상태는 메모리 내 OLTP에 대한 성공적인 사용 사례입니다. SQL Server를 사용하면 한 고객이 초당 120만 개의 요청을 달성하려고 했습니다. 그 동안 엔터프라이즈의 모든 중간 계층 애플리케이션의 캐싱 요구에 메모리 내 OLTP를 사용하기 시작했습니다. 세부 정보: bwin이 SQL Server 2016(13.x) 메모리 내 OLTP를 사용하여 전례 없는 성능 및 규모를 달성하는 방법

구현 고려 사항

varbinary(max) 열에 BLOB을 저장하여 비지속성 메모리 최적화 테이블을 간단한 키-값 저장소로 사용할 수 있습니다. 또는 SQL Server 및 SQL Database에서 JSON 지원을 사용하여 반구조화된 캐시를 구현할 수 있습니다. 마지막으로, 다양한 데이터 형식 및 제약 조건을 포함하여 전체 관계형 스키마가 있는 비지속성 테이블을 통해 전체 관계형 캐시를 만들 수 있습니다.

GitHub에 게시된 스크립트를 사용하여 기본 제공 SQL Server 세션 상태 공급자 인 aspnet-session-state에서 만든 개체를 대체하여 메모리 최적화 ASP.NET 세션 상태 시작

고객 사례 연구

tempdb 개체 바꾸기

비지속 테이블 및 메모리 최적화 테이블 형식을 사용하여 임시 테이블, 테이블 변수 및 TVP(테이블 반환 매개 변수)와 같은 기존 tempdb 기반 구조를 대체합니다.

메모리 최적화 테이블 변수 및 비지속성 테이블은 일반적으로 기존 테이블 변수 및 #temp 테이블과 비교할 때 CPU를 줄이고 로그 IO를 완전히 제거합니다.

구현 고려 사항

시작하려면: 메모리 최적화를 사용하여 임시 테이블 및 테이블 변수 성능 향상

고객 사례 연구

ETL(추출, 변환, 로드)

ETL 워크플로에는 준비 테이블에 대한 데이터 로드, 데이터 변환 및 최종 테이블로의 로드가 포함되는 경우가 많습니다.

데이터 스테이징에 비지속성 메모리 최적화 테이블을 사용합니다. 모든 IO가 완전히 제거되고 데이터 액세스 효율성이 향상됩니다.

구현 고려 사항

워크플로의 일부로 스테이징 테이블에서 변환을 수행하는 경우 고유하게 컴파일된 저장 프로시저를 사용하여 이러한 변환 속도를 높일 수 있습니다. 이러한 변환을 동시에 수행할 수 있으면 메모리 최적화에서 추가적인 크기 조정 관련 이점을 얻을 수 있습니다.

샘플 스크립트

메모리 내 OLTP 사용을 시작하기 전에 먼저 MEMORY_OPTIMIZED_DATA 파일 그룹을 만들어야 합니다. 또한 데이터베이스 호환성 수준 130 이상을 사용하고 데이터베이스 옵션 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON으로 설정하는 것이 좋습니다.

다음 위치에서 스크립트를 사용하여 기본 데이터 폴더에 파일 그룹을 만들고 권장 설정을 구성할 수 있습니다.

다음 샘플 스크립트는 데이터베이스에서 만들 수 있는 메모리 내 OLTP 개체를 보여 줍니다.

먼저 메모리 내 OLTP에 대한 데이터베이스를 구성합니다.

-- configure recommended DB option
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;
GO

내구성이 다른 테이블을 만들 수 있습니다.

-- memory-optimized table
CREATE TABLE dbo.table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON);
GO
-- non-durable table
CREATE TABLE dbo.temp_table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON,
      DURABILITY=SCHEMA_ONLY);
GO

메모리 내 테이블로 테이블 형식을 만들 수 있습니다.

-- memory-optimized table type
CREATE TYPE dbo.tt_table1 AS TABLE
( c1 INT IDENTITY,
  c2 NVARCHAR(MAX),
  is_transient BIT NOT NULL DEFAULT (0),
  INDEX ix_c1 HASH (c1) WITH (BUCKET_COUNT=1024))
WITH (MEMORY_OPTIMIZED=ON);
GO

고유하게 컴파일된 저장 프로시저를 만들 수 있습니다. 자세한 내용은 데이터 액세스 애플리케이션에서 고유하게 컴파일된 저장 프로시저 호출을 참조하세요.

-- natively compiled stored procedure
CREATE PROCEDURE dbo.usp_ingest_table1
  @table1 dbo.tt_table1 READONLY
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT,
          LANGUAGE=N'us_english')

  DECLARE @i INT = 1

  WHILE @i > 0
  BEGIN
    INSERT dbo.table1
    SELECT c2
    FROM @table1
    WHERE c1 = @i AND is_transient=0

    IF @@ROWCOUNT > 0
      SET @i += 1
    ELSE
    BEGIN
      INSERT dbo.temp_table1
      SELECT c2
      FROM @table1
      WHERE c1 = @i AND is_transient=1

      IF @@ROWCOUNT > 0
        SET @i += 1
      ELSE
        SET @i = 0
    END
  END

END
GO
-- sample execution of the proc
DECLARE @table1 dbo.tt_table1;
INSERT @table1 (c2, is_transient) VALUES (N'sample durable', 0);
INSERT @table1 (c2, is_transient) VALUES (N'sample non-durable', 1);
EXECUTE dbo.usp_ingest_table1 @table1=@table1;
SELECT c1, c2 from dbo.table1;
SELECT c1, c2 from dbo.temp_table1;
GO

관련 콘텐츠