Таблицы, оптимизированные для оперативной памяти

Выпуск Microsoft SQL Server 2014 ознаменовался появлением революционного компонента In-Memory OLTP, который позволяет хранить таблицы непосредственно в оперативной памяти! Использование памяти позволяет достичь многократного ускорения в скорости запросов на извлечение и изменение данных. Думаю, что в ближайшие годы эта технология изменит представления о хранении и обработке данных во всем мире. Подход Microsoft при разработке данного компонента оказался намного более гибким чем у других производителей СУБД. В частности в других СУБД требуется создавать в оперативной памяти целую базу данных. В MS SQL 2014 можно в зависимости от ресурсов и потребностей расположить часть таблиц в оперативной памяти, а часть на диске. При этом гарантируется полная транзакционная целостность данных. Данные безопасно сохранятся даже при нештатной перезагрузке сервера. Это достигается за счет использования так называемых checkpoint-файлов, в которых хранится вся необходимая информация о данных таблицы, находящейся в памяти. Доступ к этим файлам не замедляет работу движка In-Memory OLTP, поскольку доступ последовательный, только на добавление. При необходимости (например, для ETL процессов загрузки данных) для таблиц в памяти можно отключить транзакционное логирование, благодаря чему скорость обработки данных можно еще больше увеличить.
Кроме того, имеет место мультиверсионность строк в таблице. Эта версионность лучше чем классический уровень изоляции транзакций snapshot. В частности снижается уровень блокирования не только при параллельной работе читателей и писателей, но и при совместной работе нескольких писателей. Каждая строка имеет идентификатор начала и окончания работы транзакции. И каждая транзакция может видеть эту строку, только, если ее идентификатор находится в пределах этого диапазона.
При изменениях в таблице, которые приводят к изменению в индексах, изменения не логируются, что снижает уровень системных блокировок. А сами индексы пересоздаются при старте сервера.
Посмотрим как происходит работа с новыми видами таблиц. Создадим базу данных:

use master

create database tasks
Теперь нам понадобится создать специальную файловую группу в новой базе данных, в которой будет сохраняться информация об изменениях в таблице, которая создана в памяти - checkpoint-файлы. Эти файлы бывают двух типов: файлы данных и файлы дельт. Они используются в частности для возможности воссоздания таблиц в памяти при старте сервера. Чуть позже мы поговорим о них подробнее. Код на добавление файловой группы:

alter database tasks add filegroup MemData contains memory_optimized_data

alter database tasks add file

    (

        name = MemData,

        filename = 'C:\Документы\Временные\tmp\MemData'

    ) to filegroup MemData

Для исполнения этого кода требуется, чтобы директория "C:\Документы\Временные\tmp" уже существовала. Директории "C:\Документы\Временные\tmp\MemData" быть не должно, ее создаст сервер. Теперь можно создать таблицу, оптимизированную для оперативной памяти. Пусть нам требуется хранить 2 столбца: целочисленный идентификатор (iRowId int not null) и имя некоторой сущности (vcName char ( 20 )). Перед написанием кода на создание таблицы сделаем ряд замечаний.

Движок, реализующий In-Memory For OLTP, создан на совершенно других принципах в отличие от классического Database Engine. При хранении данных на жестком диске аллокация дисковой памяти оптимальна блоками. Это дало жизнь таким структурам данных как страница и экстент. При хранении данных в оперативной памяти ее выделение происходит байтами. Поэтому когда таблица хранится в памяти, нет таких понятий как страница или экстент. Хранение всех табличных данных сугубо построчное. В Database Engine для определения того какие страницы принадлежат той или иной таблице или индексу используются битовые карты распределения IAM (Index Allocation Map). В In-Memory For OLTP таких карт нет, поэтому для того, чтобы связать строки в массив данных, относящийся к конкретной таблице, требуется хранить строки упорядоченно. Поэтому у каждой строки в памяти должен быть уникальный индекс, являющийся первичным ключом. Кроме того именно в структуре строк хранится информация о том в какой последовательности хранятся ключи того или иного индекса, поэтому все индексы должны указываться при объявлении таблицы. Ниже код на создание таблицы Persons:

create table dbo.Persons

(

    iRwoId int not null primary key nonclustered hash with ( bucket_count = 1048416 ),

    vcName varchar ( 20 ) collate Latin1_General_100_BIN2 not null,

    index IX_vcName nonclustered ( vcName )

) with ( memory_optimized = on, durability = schema_and_data )
В этом коде в конце предложения create table мы видим опцию memory_optimized со значением on. Это означает, что таблица будет храниться в памяти. Если бы было указано значение off, которое используется по умолчанию, то таблица хранилась бы на диске. Также указана опция durability со значением schema_and_data. Это значение является умолчальным и гарантирует транзакционную целостность. Если бы мы написали durability = schema_only, то это бы означало, что для таблицы транзакционное логирование отключено. Если же происходит перезагрузка сервера, то данные для такой таблицы теряются (поскольку они хранятся только в памяти), а остается только сама пустая таблица (информация о структуре таблицы хранится в метаданных на диске).
При определении текстового столбца мы используем ключевое слово collate для указания бинарной сортировки. Это требование является обязательным. Также для таблицы в памяти нельзя использовать длинные столбцы (image, text, xml, varchar ( max ) и т. д.). Максимальная длина строки составляет 8060 байт, однако в отличие от дисковых таблиц, здесь нет механизма вынесения длинных столбцов, которые не умещаются на строке (нет аналога страниц типа row overflow data). В частности, нельзя создать таблицу со двумя столбцами типа varchar ( 4000 ) и varchar ( 5000 ), поскольку 4000 + 5000 = 9000 > 8060. Когда таблица создана ее нельзя изменить, возможно только пересоздание.
При создании таблицы сервер создает библиотеку dll, которая отвечают за извлечение, вставку, обновление и удаление строк. Она хранятся в одной из директорий каталога, в который инсталлирована СУБД. Получается, что при модификации данных сервер ничего не делает, а работает эта dll. Набор библиотек свой для каждой таблицы, они написаны на языке программирования C, и их код оптимизирован под конкретную структуру таблицы.

В одной из последних строк в create table создается индекс на столбце vcName. Это также новый синтаксис MS SQL 2014, который позволяет создавать индекс на одном или нескольких столбцах сразу при создании таблицы. Индексов может быть не больше 8. Ниже мы увидим, что любой индекс является покрывающим.

Теперь обсудим первую строку в коде на создание, где используется ключевое слово hash. Это hash-индекс, являющийся первичным ключом. Он может быть только некластерным. В скобках задается число блоков для hash-таблицы, которая лежит в основе первичного ключа. Как его выбрать? Для ответа на этот вопрос посмотрим как хранятся строки таблицы. Мы выбрали поле iRowId в качестве первичного ключа. Это значит, что в памяти создается хэш-таблица с заданным в разделе bucket_count числом блоков, а хэш-функция применяется к значениям iRowId. Если есть несколько iRowId, которым соответствует одно значение хэш-функции, то они образуют цепочку. Значение из хэш-таблицы указывает на первый элемент цепочки. Этот элемент является строкой. В строке есть заголовок. В конце строки перечисляются значения столбцов и есть раздел, в котором хранится указатель на следующий элемент цепочки, то есть на следующую строку. Она в свой очередь имеет указатель на очередную строку и так далее. Если мы имеем запрос с условием равенства на iRowId, то для iRowId находится значение хэш-функции и происходит поиск по цепочке. В схеме ниже показан алгоритм построения хэш-индекса для таблицы со столбцами Row1, Row2, Row3, у которой есть два хэш-индекса: один по столбцу Row1, второй по столбцам Row2, Row3:

То есть для того чтобы поиск был быстрее необходимо, чтобы цепочка состояла из возможно меньшего числа элемента, в идеале из одного. Поэтому лучше чтобы число блоков совпадало с числом строк. Следует быть внимательным и не делать число блоков большим чем число строк в таблице. Число блоков на самом деле округляется до ближайшей (вверх) степени двойки. Если оно превышает число строк в таблице, то это приведет к бесполезному расходу памяти. И может замедлиться скорость запросов, которые будут выполнять сканирование таблицы по такому индексу: нужно будет сканировать блоки, которые не указывают ни на какие строки. Можно проверить истинное значение числа блоков по системному каталогу sys.hash_indexes (поле bucket_count).

Если в таблице есть несколько хэш-индексов, то это означает, что каждая строка имеет по одному указателю на каждый индекс. Каждый указатель указывает на следующую строку, входящую в подмножество строк, имеющих такое же значение хэш-функции, для заданного индекса. В частности, все индексы на таблице в памяти являются покрывающими относительно всех столбцов в таблице.

Существуют представления, которые позволяют получить статистику по хэш-индексу. По представлению sys.dm_db_xtp_hash_index_stats можно определеить общее число блоков хэш-функции, число занятых блоков, среднюю и максимальную длину цепочки элементов, соответствующих строкам с одинаковым значением хэш-функции.

Из архитектуры хэш-индекса понятно, что он поможет выполнять поиск по запросу с условием равенства на столбец, входящий в хэш-индекс. Но, если, например, запрос содержит условие where ColName like 'data%' или iRowId between, то поиска по индексу не будет, поскольку неизвестно по какому хэш-значению делать поиск. Для поиска по таким условиям нужно использовать другой тип индексов: диапазонные. Такой индекс создан на таблице dbo.Persons, на столбце vcName. Такие индексы свободны от лэтчей, изменения в них не логируются, поскольку индекс пересоздается при рестарте сервера.

Данные индексы имеют похожую на классическую структуру двоичного дерева. Но страницы могут иметь разный размер (максимум 8 Кб). Страницы индексов никогда не обновляются. Например, при обновлении строки, запись на странице индекса помечается как удаленная. Создается просто компенсирующая страница, на которой содержится новая строка. В такой дельта-странице содержится только одна строка. Когда таких дельта-страниц становится слишком много, то происходит так называемое слияние страниц. Страницы с устаревшими записями помечаются, как предназначенные для удаления сборщиком мусора. Есть и специальные алгоритмы, которые выполняют разбиение страницы, если она полностью заполнены. Ели страница содержит мало записей, может происходить ее слияние.

Теперь проверим хэш-индексы в деле. Посмотрим на скорость вставки и операций поиска и сканирования по хэш-индексу. Сравним ее с аналогичными операциями для дисковых таблиц с обычным индексом. Сравним также скорость вставки в таблицу, хранящуюся в памяти с отключенным журналированием транзакций. Поскольку на моем компьютере памяти мало (4 Гб), то число записей в таблице будет небольшим: 2 млн. строк. Наполним таблицу dbo.Persons:

insert into dbo.Persons ( iRowId, vcName )

       select top 2000000, row_number () over ( order by data1.number ), left (

             replace ( cast ( binary_checksum ( newid () ) as varchar ( 100 ) ), '-', '' ) +

             replace ( cast ( binary_checksum ( newid () ) as varchar ( 100 ) ), '-', '' ) +

             replace ( cast ( binary_checksum ( newid () ) as varchar ( 100 ) ), '-', '' ), 20 )

       from

             master.dbo.spt_values data1

                    cross join

             master.dbo.spt_values data2
Вставка длилась 18 секунд. Теперь создадим и наполним дисковую таблицу с такой же структурой/ Запрос на вставку будет аналогичным:

create table dbo.Entity

(

       iRowId int identity ( 1, 1 )      not null,

       vcName char ( 20 )                      not null,

       constraint AK_Entity_vcName unique nonclustered ( vcName asc ) on [PRIMARY],

       constraint PK_Entity_iRowId primary key clustered ( iRowId asc ) on [PRIMARY]

) on [PRIMARY]

insert into dbo.Entity ( vcName )

       select top 2000000 left (

             replace ( cast ( binary_checksum ( newid () ) as varchar ( 100 ) ), '-', '' ) +

             replace ( cast ( binary_checksum ( newid () ) as varchar ( 100 ) ), '-', '' ) +

             replace ( cast ( binary_checksum ( newid () ) as varchar ( 100 ) ), '-', '' ), 20 )

       from

             master.dbo.spt_values data1

                    cross join

             master.dbo.spt_values data2
Теперь вставка длилась 49 секунд, в 2.7 раз медленнее.
Проверим скорость работы запросов. Все запросы будет запускаться с предварительно очищенным кэшем (с помощью инструкции dbcc dropcleanbuffers). Проверим скорость сканирования таблиц. Выполним запросы:

select *

from dbo.Entity with ( index ( PK_Entity_iRowId ) )

where vcName = '12345678901234567890'

select *

from dbo.Persons with ( index ( 3 ) )

where vcName = '12345678901234567890'
Первый запрос (к дисковой таблице) отработал на холодном кэше за 5819 миллисекунд, второй работал 499 мс. То есть скосроть выросла в 11.6 раза!
Проверим теперь скорость работы при поиске по первичному ключу. Запустим такие запросы:

select *

from dbo.Entity --with ( index ( PK_Entity_iRowId ) )

where iRowId = 1000000

select *

from dbo.Persons --with ( index ( 3 ) )

where iRowId = 1000000

Первый запрос отработал за 363 мс, второй - за 101 мс, скосрость выросла в 3.6 раза.

Следующий тест покажет эффективность работы запроса с условием like:

select *

from dbo.Entity

where vcName like '%1%2%3%4%5%6%7%8%9%0%'

select *

from dbo.Persons

where vcName like '%1%2%3%4%5%6%7%8%9%0%'

Первый запрос отработал за 7290 мс, второй - за 995 мс, то есть скорость выросла 7.3 раза.

Теперь проверим поиск по некластерному индексу дисковой таблицы и по диапазонному индексу таблицы в памяти:

select *

from dbo.Entity

where vcName = '12345678901234567890'

select *

from dbo.Persons

where vcName = '12345678901234567890'

Скорость первого запроса составила 207 мс, второго - 82 мс, то есть второй запрос отработал быстрее первого в 2.5 раза.

Теперь проверим как влияет на скорость вставки наличие транзакционного логирования. Создадим копию таблицы Persons:

create table dbo.Persons_Add

(

       iRowId int not null primary key nonclustered hash with ( bucket_count = 1048416 ),

       vcName varchar ( 20 ) collate Latin1_General_100_BIN2 not null

       index IX_vc_Addnonclustered ( vcName )

) with ( memory_optimized = on, durability = schema_only )

insert into dbo.Persons_Add( iRowId, vcName )

       select top 2000000 row_number () over ( order by data1.number ), left (

             replace ( cast ( binary_checksum ( newid () ) as varchar ( 100 ) ), '-', '' ) +

             replace ( cast ( binary_checksum ( newid () ) as varchar ( 100 ) ), '-', '' ) +

             replace ( cast ( binary_checksum ( newid () ) as varchar ( 100 ) ), '-', '' ), 20 )

       from

             master.dbo.spt_values data1

                    cross join

             master.dbo.spt_values data2

В ходе создания таблицы мы указали здесь значение опции durability, равное schema_only.

Теперь проверим скорость работы таких запросов на обновление:

update dbo.Entity

       set vcName = '12345678901234567890'

update dbo.Persons

       set vcName = '12345678901234567890'

update dbo.Persons_Add

       set vcName = '12345678901234567890'

Предварительно я удалил уникальный ключ на vcName в таблице Entity, заменив его неуникальным некластерным индексом на vcName.

Первый запрос отработал за 1 минуту 6 секунд, второй - за 6 секунд, 3-ий - за 2 секунды!!! То есть при использовании таблицы в памяти скорость обновления выросла в 11 раз, а при использовании таблицы в памяти без транзакционного логирования скорость обновления выросла в 33 раза (и в 3 раза по сравнению с таблицами в памяти, для которых логирование включено)! Видно, что даже при использовании таблицы в памяти, для которой пишется транзакционный лог, его объем намного меньше по сравнению с дисковыми таблицами.

При написании хранимых процедур, которые обращаются только к таблицам в памяти, можно создавать эти процедуры с опцией машинной компиляции. Это означает, что при создании процедуры происходит создание и загрузка библиотеки dll, которая решает все задачи, описанные в процедуре. Библиотека содержит скомпилированный код на языке C. Она использует движок In Memory OLTP, а также библиотеки, созданную для таблиц, к которым обращается процедура. Машинная компиляция, позволяет избежать стадии интерпретации и построения плана выполнения при исполнении процедуры. План выполнения также создается и компилируется в машинный код, а затем всегда используется. Машинная компиляция позволяет повысить производительность в высоко нагруженных OLTP системах, где могут быть сотни или тысячи активных соединений, которые постоянно запускают процедуры. В таких средах благодаря машинной компиляции снизится время, необходимое для выполнения большого числа запросов. Ниже приведен простой пример создания нативной процедуры:

create proc dbo.Test

with native_compilation, schemabinding, exec as owner

as

begin atomic with ( transaction isolation level = snapshot, language = N'us_english' )

       select iRowId, vcName

       from dbo.Persons

end

Если в таблицах, которые используются в процедуре, изменится распределение данных, то используемый план выполнения может стать неоптимальным. В этом случае можно пересоздать процедуру или перезагрузхить сервер. В последнем случае все нативные процедуры перекомпилируются. В частности, библиотеки dll для таблиц и процедур не входят в бэкап. Они пересоздаются при старте базы данных.
Список библиотек можно посомтреть с помощью такого запроса:

select *

from sys.dm_os_loaded_modules

where description = 'XTP Native DLL'
Обсудим теперь архитектуру checkpoint-файлов. Данные об изменениях в таблицах, оптимизированных для памяти, постепенно перетекают из журнала в checkpoint-файл. Файлы checkpoint хранятся в файловой группе filestream, которая была создана в самом начале. Все файлы разбиты на пары: файл данных и файл дельты. В файле данных хранятся сведения и том, какие записи были вставлены в таблицы в памяти, а в файле дельт хранится информация о том, какие записи были помечены для удаления. Каждая пара checkpoint-файлов соовтетствует 100 Мб данных журнала. Со временем объем файлов дельт увеличивается, а объем неустаревших данных в файле данных уменьшается.  В этом случае может происходить слияние checkpoint-файлов. Оно происходит автоматически, но при возникновении проблем можно запускать слияние вручную, вызвав хранимую процедуру sys.sp_xtp_merge_checkpoint_files. С помощью представления sys.dm_db_xtp_checkpoint_files можно посмотреть количество и статус checkpoint-файлов.

Файлы checkpoint нужны серверу для возможности наполнения памяти при старте сервера. Поскольку между файлами данных и файлами дельт есть взаимно однозначное соответствие, то процесс наполнения памяти может эффективно распараллеливаться.

Движок In Memory OLTP интегрирован с регулятором ресурсов. С помощью процедур sp_xtp_bind_db_resource_pool, sp_xtp_unbind_resource_pool можно привязать или отвязать базу данных к определенному пулу ресурсов, задав таким образом верхний предел объема памяти, доступной для таблиц в памяти в данной базе данных.

Для таблиц в памяти имеются некоторые синтаксические ограничения. Это связано с тем, что для реализации той или иной функции (например, truncate table) необходимо писать новый код для таблиц в памяти. Для основных методов доступа к данных (select, insert и пр.) эти функции реализованы, для некоторых (select *, identity-поля) пока не успели. Но там, где есть синтаксические ограничения, их можно обойти, проявив смекалку. С выходом новых версий синтаксические возможности без сомнения будут расширяться.