In-Memory OLTP в SQL Server 2014. Частина II

    У першої частини ми коротко розглянули основні можливості SQL Server по in-memory обробці стосовно аналітичним і транзакційних і додатків, сконцентрувавшись на останніх, оскільки in-memory OLTP (Hekaton) є найбільш істотним нововведенням в SQL Server 2014. У даній статті ми продовжимо огляд функціональності Гекатон на прикладі раніше створеної БД.
 
Коль скоро ми говоримо про транзакционной обробці в пам'яті, не зайвим буде згадати акронім ACID: атомарность, цілісність, ізольованість і залишковість (ефекту), конкретно — літеру I. Оскільки добитися повної ізоляції накладно, в СУБД підтримуються рівні ізоляції, що допускають ту чи іншу ступінь компромісу. Це класика жанру. У міру наближення до ідеалу в SQL Server підтримуються: брудне читання (допускаються порушення види: перший транзакція змінює дані, другий читає зміни, перша відкочується, виходить, що друга прочитала неіснуюче), read committed (можливо неповторяющееся читання: перша транзакція може змінити дані в масштабі читання другий, і вдруге друга транзакція прочитає вже змінені дані), repeatable read (перша транзакція не може змінювати записи в масштабі друге, але може вставляти нові — фантоми), serializable (самий строгий — фантоми не проходять). Колись давно, коли сторінки в SQL Server були 2-кілобайтні, в ньому підтримувалися тільки сторінкові блокування, тому два останніх рівня були синоніми. Повноцінна підтримка блокування записи з'явилася в 7.0 (1998). У 2005-му до перерахованих рівням додався snapshot Isolation, що, строго кажучи, рівнем ізоляції не є, а перетворює SQL Server з блокіровочніка в версіоннік. Якраз snapshot є основним для Гекатон, тому що optimistic concurrency виступає найбільш природним підходом при роботі з об'єктами в пам'яті. Мається на увазі — по своїй суті, тому що версії записів у даному випадку не зберігаються в tempdb. Кожна операція запису має безперервно зростаючий номер транзакції, що використовується для наступних читань. Незафіксовані записи зберігаються в пам'яті, але до комітів не видні, тому брудних читань не буває. Старі записи видаляються в процесі збірки сміття і звільняють пам'ять.
Також в Гекатон підтримується Repeatable Read, яке (на відміну від дискового варіанти) нікого не блокує. Коли небудь в кінці транзакції відбувається повторне читання, то, якщо записи змінилися, транзакція скасовується з помилкою 41305 "The current transaction failed to commit due to a repeatable read validation failure on table [name]". Аналогічно працює Serializable — у разі виникнення фантомів транзакція скасовується.
Рівень ізоляції задається в блоці ATOMIC нативної компільованою збереженої процедури (подивимося нижче) або в табличному ХІНТ Т-SQL запиту. Як варіант — за допомогою датабазной опції MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, яка при зверненні до таблиць в пам'яті піднімає Read UnCommitted / Committed до Snapshot. Якщо звично написати
 
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
  UPDATE dbo.UserSession SET ShoppingCartId=3 WHERE SessionId=4 

 
буде помилка Msg 41333, Level 16, State 1, Line 3
The following transactions must access memory optimized tables and natively compiled stored procedures under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.
 
В Т-SQL скрипті рівень ізоляції задається Хинт:
 
 
BEGIN TRAN 
  UPDATE dbo.UserSession WITH (SNAPSHOT) SET ShoppingCartId=3 WHERE SessionId=4 
  UPDATE dbo.ShoppingCart WITH (SNAPSHOT) SET TotalPrice=100.00 WHERE ShoppingCartId=3 
COMMIT 
(1 row(s) affected)
(1 row(s) affected)

  
Теоретично Read Committed для memory optimized таблиць теж підтримується, але тільки у випадку autocommit (single statement) транзакцій.
Витрати на блокування при цьому мінімальні, заради чого, власне, все і затівалося.
 
 
BEGIN TRAN
INSERT dbo.ShoppingCart VALUES (5,500,GETUTCDATE(),50.5) 
SELECT resource_type, resource_description, object_name(resource_associated_entity_id), request_mode, request_type, request_status FROM sys.dm_tran_locks WHERE request_session_id = @@SPID

 
Зверніть увагу, що Гекатон «вішає» блокування тільки Schema Stability на таблицю. Ніяких Х і IX немає в помині:
 
 
Рис.1
 
Давайте для порівняння згадаємо, що відбувається у випадку традиційних таблиць. Створимо аналогічну дискову таблицю, вставимо той же набір рядків в якості попереднього наповнення і повторимо транзакцію:
 
 
CREATE TABLE dbo.ShoppingCart_Disk ( 
   ShoppingCartId int not null 
primary key, 
   UserId int not null, 
   CreatedDate datetime2 not null, 
   TotalPrice money,
   INDEX ixUserid nonclustered (UserId)) 
...
BEGIN TRAN
INSERT dbo.ShoppingCart_Disk VALUES (5,500,GETUTCDATE(),50.5) 
SELECT resource_type, resource_description, case resource_type when 'object' then object_name(resource_associated_entity_id) else cast(resource_associated_entity_id as sysname) end, resource_associated_entity_id, request_mode, request_type, request_status FROM sys.dm_tran_locks WHERE request_session_id = @@SPID       

 
Що називається, відчуйте різницю:
 
 
Рис.2
 
Мінімальні блокування — одне з явних переваг Гекатон. Додаток виграє від перенесення в пам'ять, якщо воно виконано в стилі OLTP, тобто характеризується відносно короткими транзакціями з високим відсотком конкуренції від численних сполук, і проблема полягає в тому, що підлягає висять блокування або фізичні Латчі і не відпускають ресурси. Портація буде простіше, якщо використовуються збережені процедури, і проблеми з продуктивністю вдається виділити у відносно невелике підмножина таблиць / збережених процедур. Зрозуміло, не всі сценарії виграють. Граничний випадок — одна таблиця з одного запису, яку всі оновлюють новим значенням. Прагнення в що б те не стало запхнути все і вся в пам'ять призведе до того, що вона тупо скінчиться. До речі, досить часто ставлять запитання, що буде, якщо Гекатон зжере всю виділену йому пам'ять? Загалом, зрозуміло, що нічого доброго: Msg 701, Level 17, State 103. There is insufficient system memory in resource pool 'default' to run this query. Що в цьому випадку робити — як правило, нічого. SQL Server досить спритно чистить пам'ять, і на релізі мені його ще не вдалося загнати в ступор, який лікується тільки рестартом (на відміну від СТР). Збірка сміття спроектована як неблокуючий і кооперативна, що означає, що незважаючи на спеціальний потік для цих цілей, більшу частину роботи виконують користувача процеси, які час від часу перевіряють і позначають записи для подальшого видалення, а потім і видаляють. Спеціалізований потік включається, коли для користувача активність дуже мала
Як уникнути переповнення в процесі роботи? Очевидно, правильно оцінювати дійсність при перенесенні таблиць в пам'ять, тим більше, що sp_spaceused ніхто не відміняв.
Можна використовувати Memory Optimization Advisor, який проводить статичний аналіз схеми, індексів і пр. на предмет потенційних проблем, і більш інтелектуальний AMR (Analysis, Migrate and Report) для динамічного аналізу за характером використання (бере статистику з
Performance Data Warehouse). Можна превентивно обмежити Гекатон за допомогою регулятора ресурсів:
 
CREATE RESOURCE POOL mem_optpool WITH (MAX_MEMORY_PERCENT = сколько не жалко)
EXEC sp_xtp_bind_db_resource_pool 'mydatabase', 'mem_optpool‘

Вплив Resource Governor відноситься тільки до пам'яті, що виділяється In-Memory OLTP аллокаторамі, тобто за відсутності в БД об'єктів, оптимізованих для пам'яті, з пулу ресурсів пам'ять не вилучається. In-memory OLTP engine інтегрований в загальний SQL Server Memory Manager, в чому ви можете переконатися, запустивши
 
select type, name, memory_node_id, pages_kb/1024 as pages_MB from sys.dm_os_memory_clerks where type like '%xtp%‘

Перший товариш буде пам'ять, аллоцірованная системою, останній (memory_node = 64) — виділене адміністративне з'єднання, і між ними — користувальницькі БД з MEMORY_OPTIMIZED_DATA.
 
Як зазначалося в першій частині, зовсім необов'язково засовувати цілком всю базу в пам'ять. Можливі комбіновані запити між таблицями в пам'яті і звичними таблицями, наприклад,
 
 
select m1.ShoppingCartId, m2.UserId, d.CreatedDate, d.TotalPrice from ShoppingCart m1 
join ShoppingCart_Disk d on m1.ShoppingCartId = d.ShoppingCartId 
join UserSession m2 on d.ShoppingCartId = m2.ShoppingCartId

delete from ShoppingCart
insert ShoppingCart select * from ShoppingCart_Disk

update d set TotalPrice = m.TotalPrice from ShoppingCart_Disk d join ShoppingCart m on d.ShoppingCartId = m.ShoppingCartId where m.UserID <= 100

Скрипт 1
 
Крім таблиць, в пам'яті можуть розміщуватися збережені процедури. Такі процедури можуть працювати тільки з in-memory таблицями. Процедура обов'язково створюється з опцією SCHEMABINDING, що означає, що таблиці, на які посилається процедура, не можу бути модифіковані або видалені перш неї. Традиційні процедури за умовчанням виконуються з опцією EXECUTE AS CALLER. Для процедур в пам'яті вона не підтримується. Можливими варіантами є: EXECUTE AS OWNER, EXECUTE AS 'user_name' або EXECUTE AS SELF (творець). Існують і інші обмеження — див BOL , «Скомпільовані у власному коді збережені процедури».
Тіло процедури полягає в блок BEGIN ATOMIC зі стандарту ANSI SQL, і на даний момент це його єдине застосування в T-SQL. Він відрізняється від BEGIN TRAN тим, що автоматично відкочується при помилку в той час, як в транзакції необхідно використовувати TRY / CATCH і ROLLBACK, т.к. з приводу SET XACT_ABORT ON є нюанси. Опціями конструкції служать згадуваний вище TRANSACTION ISOLATION LEVEL = SNAPSHOT | REPEATABLEREAD | SERIALIZABLE і LANGUAGE, в якості якого можна використовувати будь-яку мову з sys.languages. Він визначає формат date | time і мова системних повідомлень.
 
 
CREATE PROCEDURE dbo.usp_InsertSampleCarts @StartId int, @InsertCount int 
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 
AS 
BEGIN ATOMIC 
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
  IF @StartId is null SELECT @StartId = isnull(MAX(ShoppingCartId), 0) + 1 FROM dbo.ShoppingCart
  DECLARE @i int = 0
  WHILE @i < @InsertCount 
  BEGIN 
    INSERT INTO dbo.ShoppingCart VALUES (@StartId + @i, RAND() * 1000, GETUTCDATE(), NULL) 
    SET @i += 1 
  END
END 

Скрипт 2
 
Як і у випадку таблиць, для процедур відбувається нативная компіляція, яка перетворює інтерпретуються команди T-SQL в код на С і далі — в машинний код, тому якщо ми зараз повторимо запит Скрипт 4 з попередній частині , то побачимо, що додалася 3 -я dll, відповідна свіжостворений процедурою.
 
Ключовим моментом використання об'єктів в пам'яті виступає продуктивність. Для порівняння створимо аналогічну збережену процедуру традиційного способу зберігання, яка рівно так само буде вставляти задане число записів, але вже в дискову таблицю:
 
 
CREATE PROCEDURE dbo.usp_InsertSampleCarts_Disk @StartId int = null, @InsertCount int 
AS BEGIN  
  IF @StartId is null SELECT @StartId = isnull(MAX(ShoppingCartId), 0) + 1 FROM dbo.ShoppingCart
  DECLARE @i int = 0
  WHILE @i < @InsertCount 
  BEGIN 
    INSERT INTO dbo.ShoppingCart_Disk VALUES 
         (@StartId + @i, RAND() * 1000, GETUTCDATE(), NULL) 
    SET @i += 1 
  END
END

Скрипт 3
 
І вставимо туди і туди по мільйону записів:
 
 
SET NOCOUNT ON; DBCC FREEPROCCACHE WITH NO_INFOMSGS; DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

exec dbo.usp_InsertSampleCarts null, 1000000
exec dbo.usp_InsertSampleCarts_Disk @InsertCount = 1000000
 

 
Як кажуть, різниця в наявності:
 
 
Рис.3
 
З особливостей слід зазначити, що SQL Server 2014 не підтримує автоматичну перекомпіляцію нативних збережених процедур. План фіксований. Parameter sniffing не використовується, всі параметри покладаються unknown. Замість рекомпіляції, можливо, доведеться видалити і заново створити процедуру при зміні даних (для генерації нового плану). Можливо, вистачить рекомпіляції, яка відбувається при старті сервера, переїзді на резервний вузол і т.д. Зрозуміло, що чим свіже статистика за таблицями, тим коректніше буде побудований план, а статистика, як ми пам'ятаємо з першої частини, автоматично не оновлюється. Необхідно робити вручну за допомогою UPDATE STATISTICS або sp_updatestats. Остання завжди оновлює статистику для оптимізованих по пам'яті таблиць (для звичайних — у міру потреби).
 
З наочних речей давайте подивимося ще різницю в журналированием. У класичному варіанті, якщо ми пишемо мільйон записів у таблицю з некластерние індексом (і поновлення зачіпають якусь колонку цього індексу), ми отримаємо два мільйони записів в журнал, що, природно, позначається на продуктивності. До того ж (write-ahead logging), все це міститься в буфер логу негайно і в навантажених системах призводить до високої конкуренції за буфер. У разі Гекатон незафіксовані транзакції взагалі не скидаються на диск, тому не потрібно зберігати undo-інформацію або агресивно вставляти в буфер логу. Операції над індексам не у журнал — вони не зберігаються між рестарту. Формується тільки одна консолідована запис журналу при фіксації, яка містить необхідну інформацію про всі деталі змін, для всіх записів, порушених транзакцією. Дивимося.
 
 
declare @lsn nvarchar(46) = (select max([Current LSN]) from sys.fn_dblog(null, null)) 
exec dbo.usp_InsertSampleCarts_Disk @InsertCount = 100
select * from sys.fn_dblog(null, NULL) where [Current LSN] > @lsn order by [Current LSN] desc 
select count(1), sum([Log Record Length]) from sys.fn_dblog(null, NULL) where [Current LSN] > @lsn

set @lsn = (select max([Current LSN]) from sys.fn_dblog(null, null)) 
exec dbo.usp_InsertSampleCarts null, 100
select * from sys.fn_dblog(null, NULL) where [Current LSN] > @lsn order by [Current LSN] desc 
select count(1), sum([Log Record Length]) from sys.fn_dblog(null, NULL) where [Current LSN] > @lsn
 

 
 
Рис.4
 
І бачимо, що вставка 100 записів у випадку in-memory таблиці журналірована у вигляді всього двох записів у журнал типу LOP_HK (Log Operation Hekaton). Як вже зазначалося, ці записи консолідовані. Подивитися, у що вони насправді розкриваються, можна за допомогою нової недокументованою функції, яку я запозичив у Kalen Delaney. В якості Current LSN ставимо значення з відповідної колонки третього резалтсета на Рис.4:
 
 
SELECT [current lsn], [transaction id], operation, operation_desc, tx_end_timestamp, total_size, object_name(table_id) AS TableName 
FROM sys.fn_dblog_xtp(null, null) 
WHERE [Current LSN] = '00000027:000001dd:0002'

SELECT [current lsn], [transaction id], operation, operation_desc, tx_end_timestamp, total_size, object_name(table_id) AS TableName 
FROM sys.fn_dblog_xtp(null, null) 
WHERE [Current LSN] = '00000027:000001dd:0001'
 

 
 
Рис.5
 
Перший запис, як ми бачимо, складається з 102-х записів: begin tx, 100 вставок, commit. Незважаючи на це їх сумарний обсяг (див. другий і четвертий результат Рис.4) в> 10 раз компактней, ніж у випадку дискової таблиці. Якщо взяти non-durable table (DURABILITY = SCHEMA_ONLY), журналирования взагалі не буде.
    
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.