In memory OLTP -Wstęp i Memory Optimized Tables

W ostatnim czasie zainteresowałem się nieco technologią In-memory OLTP zachęcony paroma demonstracjami, które było mi dane zobaczyć na przestrzeni ostatnich miesięcy. Dlatego też postanowiłem podzielić się z czytelnikami wiedzą na temat tej właśnie technologii – ten post jest swoistego rodzaju wstępem do tego aby zacząć zabawę z Hekatonem i ma za zadanie zachęcić Was do zgłębiania wiedzy na temat tego świetnego produktu.

Hekaton to nazwa kodowa omawianej technologii i nie jedyna, oficjalnie możemy ją nazywać In-memory OLTP ale funkcjonuje jeszcze trzecia nazwa, a mianowicie XTP czyli akronim od eXtreme Transaction Processing. Każda z tych nazw gdzieś funkcjonuje – pierwsza używana bardzo często z przyzwyczajenia przez ekspertów, druga to oficjalna nomenklatura Microsoftu, trzecia z kolei pojawia się w obiektach systemowych wbudowanych w SQL Server. Sama technologia pojawiła się w SQL Server 2014 i była flagowym produktem tego wydania.Opierała się ona na trzech podstawowych filarach, które koegzystując ze sobą miały dawać nieosiągalny do tej pory poziom wydajności:

Cała technologia miała za zadanie minimalizację użycia dysku, całkowicie współbieżny i niezależny system transakcji oraz natywną kompilację obiektów. Czy to wszystko coś zmienia? Z całą pewnością! Można wręcz powiedzieć, że technologia ta całkowicie zmienia warunki gry ze względu na  wspomnianą wcześniej wydajność. Ponadto gdy chcemy wykorzystać mechanizmy in-memory nie musimy instalować żadnych dodatkowych komponentów gdyż wszelkie potrzebne składniki są wbudowane w silnik SQL. W ramach niniejszego artykułu chciałbym opowiedzieć Wam o tym jak zacząć pracować z tabelami w ramach technologii In-memory OLTP, jak z nich korzystać i co kryje się pod spodem.

Nie owijając w bawełnę przejdźmy na samym początku od razu do stworzenia przykładowej bazy danych na której będziemy wykonywać nasze demonstracje:

CREATE DATABASE [Hekaton_demo]
 ON  PRIMARY 
( 
	NAME = N'Hekaton_demo', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL16\MSSQL\DATA\Hekaton_demo.mdf' , 
	SIZE = 524288KB , 
	FILEGROWTH = 65536KB 
)
 LOG ON 
( 
	NAME = N'Hekaton_demo_log', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL16\MSSQL\DATA\Hekaton_demo_log.ldf' , 
	SIZE = 131072KB , 
	FILEGROWTH = 65536KB 
)
GO

Jak widać bazę, która będzie zawierała nasze obiekty in-memory tworzymy w tradycyjny sposób. Oczywiście w powyższym kodzie pomijamy kwestie, że pliki bazy znajdują się na dysku systemowym 🙂 Kolejnym krokiem jaki musimy przedsięwziąć jest dodanie do naszej bazy danych specjalnej grupy plików:

ALTER DATABASE Hekaton_demo
ADD FILEGROUP AWMemGroup CONTAINS MEMORY_OPTIMIZED_DATA
GO

Kluczowa jest tutaj fraza CONTAINS MEMORY_OPTIMIZED_DATA to właśnie dzięki temu zapisowi SQL Server będzie wiedział, na której grupie plików działać w przypadku obiektów in-memory. Grupa plików stworzona w ten sposób jest grupą plików mechanizmu FILESTREAM, który w wersji SQL Server 2014 zarządzał wszelkimi plikami obiektów in-memory, a obecnie jest jedynie kontenerem na pliki, a całość zarządzania przekazana została do silnika in-memory OLTP. Na obecną chwilę (SQL Server 2016 SP1) możemy mieć tylko jedną tego typu grupę plików na bazę. Kolejnym krokiem jest dodanie pliku do powstałej grupy – robimy to niemal w standardowy sposób komendą ADD FILE:

ALTER DATABASE [Hekaton_demo] 
ADD FILE ( 
	NAME = N'Hekaton_demo_inmem', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL16\MSSQL\DATA\Hekaton_demo_inmem' 
	) 
TO FILEGROUP [AWMemGroup]
GO

Dlaczego niemal? Ponieważ jak zapewne zauważyliście to tak naprawdę nie dodaliśmy pliku tylko folder – to właśnie w tej lokalizacji będą znajdować się pliki np. zrzucane procesem Checkpoint na dysk twardy. W tym miejscu może pojawić się pytanie “jak to pliki zrzucane na dysk twardy?! Przecież to in-memory!” – otóż w przypadku gdy chcemy aby nasze dane były trwałe to jakaś interakcja z dyskiem twardym być musi ale nie jest to konieczne o czym przekonamy się już za chwilę. Gdy już mamy stworzone podwaliny pod nasze obiekty in-memory możemy przejrzeć metadane grupy plików:

select
	name,
	type,
	type_desc
from
	sys.filegroups AS FG

Rezultatem jest prosta informacja o tym czy dana grupa plików jest przeznaczona na obiekty tradycyjne czy też na obiekty in-memory:

Z powyższych przykładów płynie wniosek, że kontener jakim jest baza danych może zawierać obiekty zarówno te tradycyjne jak i nowe co daje nam pewne możliwości jeśli chodzi o hybrydową architekturę mieszającą oba podejścia. Tak więc na poziomie bazy danych nie zobaczymy informacji czy zawiera ona obiekty in-memory czy też nie. Skoro mamy już przygotowaną bazę danych stwórzmy pierwsze obiekty jakimi będą tabele:

  CREATE TABLE dbo.ShoppingCart (   
    ShoppingCartId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,  
    UserId INT NOT NULL INDEX ix_UserId,    
    CreatedDate DATETIME2 NOT NULL,   
    TotalPrice MONEY  
    ) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)
  GO
 
 CREATE TABLE dbo.UserSession (   
   SessionId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=400000),   
   UserId int NOT NULL,   
   CreatedDate DATETIME2 NOT NULL,    
 )   
 WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)   
 GO

Jak widać sama składnia nie jest zbyt skomplikowana i przypomina tradycyjną deklarację tabeli. Ważne jest to aby wskazać opcję MEMORY_OPTIMIZED=ON, która odróżnia tabelę dyskową od in-memory. Dodatkowym wymogiem jest to, aby tabela miała minimum jeden indeks. W ramach tabel in-memory mamy dostępne dwie całkowicie nowe struktury indeksowe tj:

• NONCLUSTERED (zwany również RANGE) INDEX
• HASH INDEX

O tym jak one działają wewnętrznie zdążymy sobie jeszcze powiedzieć, na ten moment warto wiedzieć, że tabela może mieć maksymalnie osiem indeksów z czego, jak już wspomniałem, jeden musi wystąpić zawsze. Kolejną opcją jaką możemy podać w deklaracji CREATE TABLE jest trwałość tabeli, którą definiujemy właściwością DURABILITY. Właściwość ta może przyjmować dwa tryby:

• SCHEMA_AND_DATA – dane będą trwałe to znaczy będą logowane w dzienniku transakcyjnym i czasowo zrzucane procesem CHECKPOINT na dysk twardy, jest to opcja domyślna
• SCHEMA_ONLY – dane nie będą trwałe co oznacza, że w żadne operacje na tabelach tego typu nie będzie angażowany dysk, a co za tym idzie operacje te będą ekstremalnie szybkie jednakże w przypadku awarii lub restartu instancji zostaną one utracone

Jak możecie się domyślać dla obu trybów trwałości danych w tabelach można znaleźć całkiem ciekawe zastosowania. Pierwszy z nich nie powinien sprawić problemu aby znaleźć mu scenariusz zastosowania, drugi z kolei może być wykorzystany np. jako obszar przejściowy hurtowni danych bądź też np. w aplikacjach z danymi sesyjnymi, których utrata praktycznie nie jest dla nas problemem czy nawet jako alternatywa dla tabel tymczasowych nie obciążająca przy tym bazy systemowej tempdb. Sprawdźmy czy mamy w widoku sys.tables jakieś informacje na temat in-memory:

SELECT 
	name,
	is_memory_optimized,
	durability,
	durability_desc 
FROM 
	sys.tables

Jak można było przypuszczać w metadanych mamy flagę is_memory_optimized wskazującą czy tabela jest in-memory czy też nie oraz tryb trwałości dla każdej tabeli. Podczas tworzenia tabeli in-memory SQL Server tworzy i kompiluje do DLL kod odpowiedzialny za działania na tabelach – sam silnik bezpośrednio nie działa na tych strukturach, on po prostu wywołuje określone metody odpowiedzialne za daną czynność na tabeli. Ze względu na takie działanie w SQL Server 2014 nie było możliwe wpływanie na strukturę tabeli komendą ALTER, od SQL Server 2016 jednakże można używać tego polecenia lecz w rzeczywistości tworzy on nową tabelę i kopiuje dane ze starych do nowych struktur co powoduje zwiększone wykorzystanie pamięci. Rozmiar pojedynczego wiersza dla danych in-row nie może przekraczać 8060 bajtów jednakże właśnie od SQL Server 2016 wspierany jest off-row czyli obiekty o zmiennej długości przekraczające ten limit oraz obiekty LOB jak (N)VARCHAR(max) czy VARBINARY(MAX).

Przechodząc dalej wypełnijmy nasze tabele używając prostej struktury pętli:

--insert sample data for dbo.Shopping Cart
DECLARE  @Counter INT=0
DECLARE  @NoOfRows INT=1000000

WHILE @Counter<=@NoOfRows
BEGIN
	 INSERT dbo.ShoppingCart(UserId,CreatedDate,TotalPrice)
	 VALUES (CAST(RAND()*10000 AS INT), DATEADD(DAY,-CAST(RAND()*1000 AS INT),GETDATE()),RAND()*100)   
SET @Counter=@Counter+1
END
GO

--insert sample data for dbo.UserSession

 DECLARE  @Counter INT=0
 DECLARE  @NoOfRows INT=100000

WHILE @Counter<=@NoOfRows
BEGIN
	 INSERT dbo.UserSession(UserId,CreatedDate)
	 VALUES (CAST(RAND()*10000 AS INT), DATEADD(DAY,-CAST(RAND()*1000 AS INT),GETDATE()))   
SET @Counter=@Counter+1
END
GO

Co do samego ładowania to bardzo duży narzut generowała pętla jednakże warto w tym miejscu zauważyć, że ładowanie tabeli dbo.UserSession trwało dokładnie 30 sekund z kolei ładowanie tabeli dbo.ShoppingCart trwałoponad minutę! Różnica wynika z ustawienia trwałości, ze względu na to że dbo.UserSession ustawione zostało jako SCHEMA_ONLY to nie mieliśmy żadnego styku z dyskiem twardym, żadnego logowania co skutkowało znacznie szybszym czasem wykonania – z tego też powodu tego typu tabele świetnie się sprawdzają w procesach ETL do przechowywania tymczasowych rezultatów. Aby dostać się do danych przechowywanych w strukturach tego typu możemy użyć jednego z dwóch podejść tzn. standardowego interpretowanego kodu TSQL lub też procedur natywnie kompilowanych. Różnice pomiędzy tymi podejściami mogą zdawać się oczywiste – standardowy kod TSQL musi być przez silnik odpowiednio zinterpretowany, parsowany, na jego podstawie budowany jest plan zapytania oraz wykonywana musi być jego kompilacja – dodatkowo kod odwołujący się do tabel in-memory musi wykorzystać specjalny komponent QUERY INTEROP, który powoduje dodatkowy narzut. Jak bardzo łatwo się domyśleć w przypadku procedur natywnie kompilowanych wszystko to odbywa się w momencie tworzenia takiej procedury po to by w późniejszym czasie tj. w momencie uruchomienia nie potrzeba było przechodzić przez te wszystkie kroki. Dlatego też już na tym etapie warto pamiętać, że najszybszą metodą na pracę z omawianymi tabelami jest bez wątpienia procedura natywnie kompilowana. Jej użycie natomiast nie jest zawsze możliwe gdyż w ramach takiej struktury nie możemy użyć całego wachlarza możliwości dostępnych w ramach języka TSQL (o tym napiszemy parę słów w kolejnym artykule o procedurach natywnie kompilowanych, który pojawi się już niedługo). Sam interpretowany TSQL też posiada pewne ograniczenia gdy odnosi się do tabel in-memory takie jak brak:

• TRUNCATE TABLE
• MERGE (w momencie gdy tabela in-memory jest ustawiona jako target)
• Transakcji i zapytań odpytujących obiekty z więcej niż jednej bazy
• zapytań opartych o Linked server
• hintów blokujących
• hintów poziomów izolacji (READUNCOMMITTED,READCOMMITTED,READCOMMITTEDLOCK)
• kursorów dynamicznych
• kilku innych hintów jak np. IGNORE_CONSTRAINTS

Gdy zdecydujemy się na wykorzystanie standardowego, interpretowanego TSQL to możliwe jest w pojedynczym zapytaniu odpytać zarówno MEMORY OPTIMIZED TABLES jak i tabele tradycyjne – zapytania tego typu w nomenklaturze hekatonu nazywamy cross-container queries. Natywne procedury z kolei mogą odpytywać tylko i wyłącznie struktury in-memory.

Przechodząc dalej po załadowaniu danych kolejnym krokiem w naszej demonstracji jest włączenie statystyki wejścia-wyjścia oraz statystyk czasowych i odpytanie naszych tabel:

SET STATISTICS IO,TIME ON 
GO

SELECT * FROM dbo.UserSession
 WHERE SessionId= 9319
 GO

Niemal identyczne statystyki otrzymałem odpytując tabelę dbo.ShoppingCart. Sam rezultat nie jest szczególnie interesujący – bardziej ciekawe są wiadomości, które otrzymaliśmy. Jak można zauważyć przeszukanie miliona wierszy trwało rząd wielkości krócej niż milisekunda – tutaj możemy wręcz mówić o nanosekundach! Ponadto nie mamy żadnych statystyk IO! Jest to podstawowa zasada działania MEMORY OPTIMIZED TABLES tzn. dane nigdy nie są odczytywane z dysku twardego – zawsze odczytywane są z pamięci. Dysk jest potrzebny tylko i wyłącznie w celu Disaster Recovery i to tylko dla tabel oznaczonych jako DURABILITY=SCHEMA_AND_DATA.

To, że omawiana technologia wykorzystuje dużo pamięci operacyjnej jest oczywiste, ale ile jej wykorzystuje? Możemy to sprawdzić na kilka sposobów, a najprostszym z nich jest użycie nowego DMV o nazwie sys.dm_db_xtp_table_memory_stats. Daje on nam informacje o tym ile pamięci zajmuje dana tabela – użyjmy go zatem:

SELECT 
	t.object_id, 
	t.name, 
	ISNULL((SELECT CONVERT(decimal(18,2),(TMS.memory_used_by_table_kb)/1024.00)), 0.00) AS table_used_memory_in_mb,
	ISNULL((SELECT CONVERT(decimal(18,2),(TMS.memory_allocated_for_table_kb - TMS.memory_used_by_table_kb)/1024.00)), 0.00) AS table_unused_memory_in_mb,
	ISNULL((SELECT CONVERT(decimal(18,2),(TMS.memory_used_by_indexes_kb)/1024.00)), 0.00) AS index_used_memory_in_mb,
	ISNULL((SELECT CONVERT(decimal(18,2),(TMS.memory_allocated_for_indexes_kb - TMS.memory_used_by_indexes_kb)/1024.00)), 0.00) AS index_unused_memory_in_mb
FROM 
	sys.tables t JOIN sys.dm_db_xtp_table_memory_stats TMS ON (t.object_id = TMS.object_id)

Powyższy zrzut ekranowy obrazuje rezultat wykonania zapytania dla naszej przykładowej bazy danych. Zatem nasza tabela ShoppingCart zajmuje obecnie około 69MB z czego 0.4MB jest nieużywane, indeksy na tej tabeli zajmują około 13MB z czego 0.9MB jest nieużywane. Bardzo przydatne informacje – część z Was może zapytać czemu napisałem zapytanie w tak dziwny sposób? Odpowiedź jest prosta – jest to zapytanie używane przez wbudowany raport o nazwie Memory Usage By Memory Optimized Objects:

O ile wolę wykorzystywać DMV wprost o tyle musze przyznać, że ten raport jest naprawdę treściwy i w naprawdę dobry sposób przedstawia istotne dla nas informacje na temat użycia pamięci przez poszczególne obiekty.

No dobrze, widzimy jak to wszystko dobrze działa jednakże wszędzie mówią o tym, że in-memory OLTP to całkiem nowa technologia – wiemy, że dane przetwarzane są w pamięci operacyjnej i zawsze z tej lokalizacji odczytywane ale jaka jest tego architektura? Przede wszystkim podstawową różnicą jest sposób przechowywania danych. W tradycyjnym “dyskowym” podejściu dane składowane są na stronach i ekstentach – w przypadku in-memory OLTP dane są przechowywane… w wierszach. Struktura wiersza została przedstawiona poniżej:

Standardowy wiersz składa się z dwóch elementów tj. nagłówka (Row Header) oraz sekcji PayLoad czyli po prostu z danych. Szczegółowa struktura nagłówka przedstawiona została poniżej:

Dane zapisywane do tabel in-memory są wersjonowane co umożliwia optymistyczną współbieżność transakcji. Informacje o wersji przechowywane są w dwóch polach tj. Begin TS oraz End TS. Pierwszy z nich to nic innego jak znacznik czasowy zatwierdzenia (COMMIT) transakcji, która wiersz wstawiła, drugi z kolei to czas zatwierdzenia transakcji, która wiersz usunęła. Warto w tym miejscu zdać sobie sprawę z faktu, że dane w wierszach nigdy nie są aktualizowane po to aby nie powodować blokad i dlatego też operacja UPDATE to nic innego jak oznaczenie wiersza jako nieaktualnego (czyli wstawienie wartość do End TS) i dodanie wiersza z nowymi wartościami, który End TS ma ustawione na specjalną wartość, którą możemy utożsamiać z nieskończonością. Na tej właśnie podstawie wersjonowane są wiersze w pamięci, w tle działa oczywiście specjalny proces (Garbage Collector), który “wyrzuca” z pamięci te wiersze, które nie są już “widoczne” dla żadnej z aktywnych transakcji.

Kolejna informacje zawarte w nagłówku to:

• Stmt Id, który jest identyfikatorem zapytania w ramach transakcji, które wstawiło/usunęło wiersz ( identyfikator potrzebny do tzw. Halloween Protection).
• Indx Link Count czyli licznik indeksów, które odwołują się do danego wiersza
• Padding – dodatkowa nieużywana przestrzeń
• Index Pointers – zestaw wskaźników indeksów wskazujących kolejny wiersz w łańcuchu indeksu.

Jak możecie wywnioskować ten sposób przechowywania znacznie różni się od tradycyjnego opartego na stronach danych. Do tego dochodzą dodatkowe plusy wynikające z pozostałych mechanizmów Hekatonu co sprawia, że cała technologia może działać naprawdę szybko. Oczywiście jest całkiem sporo mechanizmów nie wspieranych przez omawianą technologię jak kompresja czy też partycjonowanie jednakże warto pamiętać o tym, że niekiedy ich użycie jest wykluczone gdyż są one wprost przystosowane do obecnych struktur opartych na stronach i ekstentach (jak np. page compression) być może zostaną one w jakiś sposób dostosowane lub dostarczone w inny sposób w przyszłych wydaniach SQL Server ale nie wszystkie są potrzebne przy zmianie wewnętrznych mechanizmów z dyskowych na in-memory. Ze względu na to, że tak jak wspomniałem technologia ta jest w ostatnim czasie w kręgu moich zainteresować to możecie spodziewać się dalszych artykułów poruszających tematykę obiektów natywnie kompilowanych, transakcji , migracji logowania czy DR. Mam nadzieję, że będzie to dla Was interesujące.