понедельник, 21 ноября 2016 г.

In-Memory tables. Таблицы в памяти - просто.


Начиная с MS SQL Server 2014 Microsoft предоставила к использованию технологию таблиц In-Memory, в 2016 данная технология получила продолжения и улучшения. Технология подразумевает, что определяется таблица, которая оптимизирована для нахождения в памяти сервера, что позволяет повысить производительность обработки данных в данной таблице, за счет быстроты работы данных в памяти и исключения задержек, связанные с вводом\выводом (хотя здесь есть свои нюансы). Постараюсь описать все нюансы и возможности в одной статье, чтобы не искать по разным страницам msdn, немного много, но зато все в одном.


Итак, требования
Чтобы вы могли в MS SQL Server использовать In-Memory таблицы, то должны проверить следующие требования:
-  64 – разрядный MS SQL Server 2014и выше редакции Enterprise, Developer или Evaluation
- достаточное объем самой оперативной памяти для данных и версионности строк, так же это зависит о нагрузки на использования таблиц в памяти
- Необходимо включить быструю инициализацию файлов, т.е предоставить учетной записи MS SQL Server право на «Perform volume maintenance tasks» в локальных политиках сервера. Это требования желательное, в противном случае может сыграть отрицательно на производительность.

Немного теории.

Основным хранилищем для таблиц In-Memory является основная память, т.е вся память находится в памяти. Строки записываются и считываются только из памяти. Для отказоустойчивости данный таблиц дублируются на диск, но можно настроить, чтобы таблица была только в памяти, это не создает дополнительной нагрузки на диск, но и все данные в таблицах хранятся до перезагрузки сервера. Все операции с таблицами транзакционны и соответствуют классификации ACID(atomic, consistent, isolated, durable) . Транзакционность выполняется за счет версионность строк, т.е. каждая изменённая строка создает новую версию строки, к которой будет дальнейшее обращение. Это позволяет практически сократить блокировки в таблицах.

Одновременно с появлением In-Memory таблиц, появился новый тип индексов –HASH индексы. Создание HASH-индекса осуществляется с помощью внутренней hash функции, которая является единственной для всего ms sql server и является детерминированной, из этого следует, что несколько значений ключей индекса могут быть связаны с одним сопоставление хеш индекса, появляется конфликт хеша. Большое число конфликтов может отрицательно связаться на операции чтения. Использование hash индексов нужно быть аккуратным, они используются только когда в предикате условия указаны все поля hash индекса. К примеру: создали hash индекс на Имя, Фамилию, а в запросе используется только Фамилия, то наш Hash индекс работать не будет, нужно указать в Запросе и имя и Фамилию. Так же в HASH индексах запрещен поиск по диапазону.

На таблицах In-Memory могут быть определены индексы как кластерные, не кластерные, так и новые HASH индексы одновременно, возможно несколько HASH индексов. Единственное замечание: все индексы создаются при создании таблицы инструкцией CREATE TABLE, далее новые индексы создаются только через пересоздание таблицы.

Пример вполне можно создать данную таблицу:
CREATE TABLE [dbo].[TblInMem_Index]
(
       [id] [int] NOT NULL,
       [val1] [nchar](36) COLLATE Cyrillic_General_BIN2 not NULL,
       [val2] [nchar](36) COLLATE Cyrillic_General_BIN2 NOT NULL,

INDEX [Hass_ind] NONCLUSTERED HASH
(
       [val2]
)WITH ( BUCKET_COUNT = 1048576),

INDEX [idx2] NONCLUSTERED
(
       [val1] ASC,
       [val2] ASC
),
 PRIMARY KEY NONCLUSTERED
(
       [id] ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

GO

В которой мы определили три индекса:

Кластерные по полю Id
Не кластерный [idx2]
Hash индекс [Hass_ind].

Так же существуют два вида таблиц: таблицы оптимизированные с параметром DURABILITY =SCHEMA_AND_DATA – это таблицы, которые размещены в памяти, но и существуют на диске, второй тип таблиц это таблицы с параметром DURABILITY = SCHEMA_ONLY, это значит , что данные находятся в памяти и доступны только до перезагрузки сервера, так же эти данные не будут доступны и при создание резервной копии, с параметром DURABILITY =SCHEMA_AND_DATA данные в таблице In-Memory будут доступны после восстановления из резервной копии.

Параметр WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY) определяется для всей таблицы: вне зависимо есть ли в ней новые HASH индексы или кластерные, при значении параметра DURABILITY= SCHEMA_ONLY- данные хранятся до перезагрузки ms sql server.

Обращение к таблицам In-Memory происходит с помощью стандартных инструкций T-SQL с явным указанием уровня изоляции SNAPSHOT, REPETABLEREAD или SERIALIZABLE или помощью так называемых процедур скомпилированные в собственном коде (Native Compiled Stored Procedures). В обращение к таблицам In-Memory есть много ограничений, следует это учитывать.

Процедуры, скомпилированные в собственном коде (Native Compiled Stored Procedures) –это наиболее быстрый доступ к таблицам In-Memory, но и имеющий много особенностей. На «физическом» уровне после создания Native Compiled процедуры мы имеем dll библиотеку, которая компилируется один раз при создании или при рестарте сервера.

Особенности Native Compiled процедур:

- код процедуры определяется разово, далее ее можно изменить только через пересоздание
- внутри процедуры транзакция определяется как BEGIN ATOMIC , что определяет свои требования
- объекты, на которые ссылается процедура, не могут быть изменены при наличие данных процедур
- нельзя просмотреть актуальный план данных процедур
- нельзя получить статистику выполнения данных процедур
- для соединения таблиц внутри хранимой процедуры используется только NETED LOOP
- не используется параллелизм
- план выполнения Native Compiled процедуры определяется в момент ее создания, в MS SQL Server 2016 для перекомпиляции процедуры можно использовать процедуру sp_recompile

Пример создания Native Compiled процедурs:
create procedure [dbo].[InsertIntoMemoryTable](@i int)
with native_compilation,schemabinding,execute as owner
as
begin atomic with (transaction isolation level = snapshot,language = N'English' )
      
       declare @id int =convert(int,RAND()*1000000000)
       declare @val1 nchar(36)
       set @val1=convert(nvarchar(36),newid())COLLATE Cyrillic_General_BIN2 
       declare  @val2 nchar(36)
       set @val2= 'text'+convert(nchar(8),@i) COLLATE Cyrillic_General_BIN2
      
       insert into [dbo].[TblInMem1]
             values (@id,@val1,@val2)
        
end

with native_compilation,schemabinding,execute as owner- При определение данной процедуры обязательно
begin atomic with (transaction isolation level = snapshot,language = N'English' ) так же обязательные параметры, требования ATOMIC

После создания данной процедуры в каталоге баз данных будет создана папка xtp далее папка номер базы данных, внутри которой будут файлы нашей процедуры:
xtp_t_9_1973582069_183184666479020.xml
xtp_t_9_2037582297_183184668414697.c
xtp_t_9_2037582297_183184668414697.dll- сама dll библиотека
xtp_t_9_2037582297_183184668414697.obj
xtp_t_9_2037582297_183184668414697.out
xtp_t_9_2037582297_183184668414697.pdb
Содержимое которых вы можете посмотреть, оно связано с определением процедуры на коде C. Файлы вы можете изменить\удалить, но ms sql server придется их заново создать, что потребует время при вызове процедуры.

В имени файла выше 9 это номер базы данных, 2037582297 – номер объекта из sysobjects.

Кстати, выше процедура будет работать только в MS SQL Server 2016, т.к в MS SQL  Server 2014 текстовые поля все должны быть в UNICOD формате. В MS SQL Server 2014 нужно немного поменять определение

set @val2= N'text'+convert(nchar(8),@i) COLLATE Cyrillic_General_BIN2

иначе будет ошибка:
Msg 12329, Level 16, State 103, Procedure InsertIntoMemoryTable1, Line 21
The data types char(n) and varchar(n) using a collation that has a code page other than 1252 are not supported with natively compiled stored procedures.


Ограничение при работе с таблицами In-Memory:

Ниже описаны наиболее явные ограничения в MS SQL Server на таблицы In-Memory, которые чаще всего мы привыкли использовать при обычных disk таблицах. Приведена только часть ограничений, полные ограничения можно изучить в msdn.

Общие ограничения для MS SQL 2014 и MS SQL 2016:
Для баз данных с таблицами In-Memory запрещены свойство AUTO_CLOSE
Запрещена операция CREATE DATABASE  с параметром ATTACHE_REBUILD_LOG
Запрещена операция создания DATABASE SNAPSHOT
Операции проверки целостности DBCC CHECKDB, CHECKTABLE пропускают таблицы In-Memory
Не поддерживаются межбазовые запросы и транзакции, а также обращения со связанными серверами
Не поддерживаются вычисляемые столбцы в таблицах In-Memory
Не поддерживается репликация для таблиц In-Memory
Не поддерживаются столбцы SPARSE
Не поддерживаются операции TRUNCATE
Не поддерживается сжатие, секционирование таблиц
Не поддерживается репликация, зеркалирование
В Native Compiled процедурах Функции MIN и MAX не поддерживаются для типов nvarchar, char, varchar, varchar, varbinary и binary
В Native Compiled процедурах DISTINCT не поддерживается в предложении ORDER BY 
В Native Compiled процедурах не поддерживаются WITH TIES и PERCENT в предложении TOP 
В Native Compiled процедурах не поддерживается многостроковая вставка через INSERT.
В Native Compiled процедурах не поддерживается SELECT INTO
В Native Compiled процедурах не поддерживается инструкция CASE
Таблицы In-Memory с SCHEMA_ONLY в базах данных в группе доступности AlwaysOn будут пустыми.
Не поддерживаются типы данных: datetimeoffset, geography, geometry, hierarchyid, rowversion,xml, sql_variant, определяемые пользователем типы
Операция MERGE только в качестве назначения
Доступ из модулей CLR запрещен к таблицам In-Memory
Табличные подсказки
Фильтруемые индексы не поддерживаются
Не поддерживаются курсоры в Native Compiled процедурах

Ограничения MS SQL 2014

все ограничения выше +
Использование только UNICOD типов данных
Использование Collation _Bin для символьных полей индексов
Ограничение общего объем всех таблиц в памяти не должен превышать 250 Гб
Не авто обновляется статистика для таблиц In-Memory, необходимо вручную обновлять
Не поддерживаются LOB объекты


Пример создания таблиц.

Для начала нужно иметь базу данных, далее в базе данных создается файловая группы базы данных для таблиц In-Memory

USE [master]
GO
ALTER DATABASE [INMemDB] ADD FILEGROUP [InMemory_filegroup] CONTAINS MEMORY_OPTIMIZED_DATA
GO

Добавляем новый файл группы в нашу файловую группу для таблиц In-Memory
USE [master]
GO
ALTER DATABASE [INMemDB] ADD FILE
( NAME = N'InMemoryFile', FILENAME = N'C:\Data\InMemory2014\InMemoryFile' )
TO FILEGROUP [InMemory_filegroup]
GO

В этот момент в указанном каталоге создается каталог InMemoryFile с содержимым аналогично каталогу FileStream:

Далее создаем нашу таблицу:

CREATE TABLE [dbo].TblInMem
(
       [id] [int] NOT NULL,
       [val1] [char](20) NULL,
       [val2] [char](20) NOT NULL,
        PRIMARY KEY NONCLUSTERED HASH
(
       [id],
       [val2]
)WITH (BUCKET_COUNT=1000000)
)

WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);

Создали, ОК, далее. СТОП далее, нужно уточнить, что выше создалась таблица в MS SQL Server 2016, в 2014 она не создается, т.к в 2014 в таблицах In-Memory возможно использовать только UNICODE типы данных
В 2014 создаем таблицу:

CREATE TABLE [dbo].[TblInMem1]
(
       [id] [int] NOT NULL,
       [val1] [nchar](36) COLLATE Cyrillic_General_CI_AS NULL,
       [val2] [nchar](36)COLLATE Cyrillic_General_BIN2  NOT NULL,

 PRIMARY KEY NONCLUSTERED HASH
(
       [id],[val2]
)WITH ( BUCKET_COUNT = 1048576)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )


Таблица определена с одним hash индексом.
Немного об синтаксисе создания таблицы:

PRIMARY KEY NONCLUSTERED HASH  – создается не кластерный HASH индекс, HASH индекс поддерживается только для In-Memory таблиц, без него мы не сможем создать нашу таблицу в памяти, обязательный параметр.
WITH (BUCKET_COUNT=1000000) – так же обязательный параметр при создание HASH индексов, указывается так называемые количество контейнеров для hash индексов, которое желательно должно быть в 2 раза более уникальных значений нашего индекса. Если выбрано неоптимальное значение то, может привести к деградации производительности при обращении к данной таблице. 

Далее сделаем тест на загрузку данных.

Я сделал несколько простых тестов на вставку:
set nocount on
go
declare @start datetime2(7)=SYSDATETIME()
declare @stop datetime2(7)
select @start

declare @i int=0
while @i<1000000
begin

 begin try
       insert into [dbo].[TblInMem]
       values (convert(int,RAND()*1000000000),convert(varchar(36),newid()),'text'+convert(nchar(8),@i))
       set @i=@i+1
 end try
 begin catch
   print @i
 end catch

end

set @stop = SYSDATETIME()
select @stop
select DATEDIFF (ss,@start,@stop)
go
set nocount off
go
Использовал таблицы, созданные выше в пример создания таблицы,
CREATE TABLE [dbo].[TblInMem1]
(
       [id] [int] NOT NULL,
       [val1] [nchar](36) COLLATE Cyrillic_General_CI_AS NULL,
       [val2] [nchar](36)COLLATE Cyrillic_General_BIN2  NOT NULL,

 PRIMARY KEY NONCLUSTERED HASH
(
       [id],[val2]
)WITH ( BUCKET_COUNT = 1048576)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
 структура во всех тестах была одинакова, за исключением менял параметр DURABILITY, а так же изменял поля в MS SQL Server2016.

Результаты тестирования:
Parameters of Test
MS SQL Server 2014
average val, sec
MS SQL Server 2016
average val, sec
Table with DURABILITY = SCHEMA_AND_DATA
585/584/584/588
585,25
626/637/610/616
622,25
Table with DURABILITY = SCHEMA_AND_DATA with no UNICODE column, BIN


610/604/585/606/
601,25
Table with DURABILITY = SCHEMA_AND_DATA , UNICODE, не BIN поле


588/604/614/617
605,75
Table with DURABILITY = SCHEMA_ONLY
38/37/39
38
47/55/52
51,3
Table with DURABILITY = SCHEMA_ONLY with no UNICODE column, BIN


44/46/49
46,3
Table with DURABILITY = SCHEMA_ONLY , UNICODE, не BIN поле


53/50/52
51,7
Native procedure with DURABILITY = SCHEMA_AND_DATA
560/553/559
557,3
564/584/581
576,3
Native procedure with DURABILITY= SCHEMA_ONLY
28/26/30/
28
38/38/37
37,7
Disk table
614/605/596
605
633/637/634
634,67

По результатам тестирования:

Наиболее интересные результаты выделил желтым цветом. В целом вставка в In-Memory таблиц смотрится хорошо, можно заметить, что в MS SQL Server 2014 она даже быстрее чем в 2016, видно из-за того, что в 2016 было снято множество ограничений, что немного повлияло на скорость. По таблице заметен выигрыш Native Compiled процедур.

Тесты «Table with DURABILITY = SCHEMA_AND_DATA, UNICODE, не BIN поле» -это тестирование в MS SQL Server 2016 с полями таблицы не UNICODE и не BIN collation видно, что они не сильно влияют на скорость, но заметно что не BIN и не UNICODE полей и при DURABILITY = SCHEMA_AND_DATA данные чуть ниже, возможно из-за меньших типов данных при хранении.

По результатам Table with DURABILITY = SCHEMA_AND_DATA и Disk table результаты не сильно отличаются в пользу In-memory таблиц. У меня disk table таблицы и файлы файловой группы In-Memory расположены на одних дисках, так что все упирается в них.  На практике, для данных таблиц In-Memory желательно выделять отдельный диск, а лучше SSD диск, тогда производительность таблиц In-Memory будет заметна. К примеру, у вас есть база данных 1 тб, вы покупаете отдельный диски 120 Гб , строите Raid массив, и располагаете на них вашу файловую группу In-Memory, то в данном случае мы получим довольно хороший выигрыш.

Тесты запускались больше количество раз, чем указано выше в таблице, все результаты были в этих границах.

Наблюдение таблиц In-Memory

Ниже несколько запрос по получению информации по таблицам In-Memory на вашем сервере:

Получение общей информации, сколько таблицы занимают в памяти:
--получение общей информации, объем таблиц в памяти
SELECT type 
, name 
, pages_kb/1024 AS pages_MB  
FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%' 

В общем, объекты, связанные с таблицами In-Memory, выделяются префиксов xtp.

По таблицам
---распределение в памяти по таблицах
SELECT object_name(t.object_id) AS [Table Name] 
     , memory_allocated_for_table_kb 
 , memory_allocated_for_indexes_kb 
FROM sys.dm_db_xtp_table_memory_stats dms JOIN sys.tables t  
ON dms.object_id=t.object_id 
WHERE t.type='U' 

Размер файлов таблиц In-Memory на диске:
--размер файлов на диске, размер папки InMemoryFile на диске
SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB] 
FROM sys.filegroups f JOIN sys.database_files df  
   ON f.data_space_id=df.data_space_id 
WHERE f.type=N'FX' 

По типам файлов
SELECT state_desc 
 , file_type_desc 
 , COUNT(*) AS [count] 
 , SUM(CASE 
   WHEN state = 5 AND file_type=0 THEN 128*1024*1024 
   WHEN state = 5 AND file_type=1 THEN 8*1024*1024 
   WHEN state IN (6,7) THEN 68*1024*1024 
   ELSE file_size_in_bytes 
    END) / 1024 / 1024 AS [on-disk size MB]  
FROM sys.dm_db_xtp_checkpoint_files 
GROUP BY state, state_desc, file_type, file_type_desc 
ORDER BY state, file_type 

Для диагностики таблиц In-Memory в плане достаточности параметра BUCKET_COUNT, есть запрос:
SELECT object_name(hs.object_id) AS 'object name',
       i.name as 'index name',
       hs.total_bucket_count,
       hs.empty_bucket_count,
       floor((cast(empty_bucket_count as float)/total_bucket_count) * 100) AS'empty_bucket_percent',
       hs.avg_chain_length,
       hs.max_chain_length
FROM sys.dm_db_xtp_hash_index_stats AS hs
JOIN sys.indexes AS i ON hs.object_id=i.object_id AND hs.index_id=i.index_id

Необходимо обратить на значения:
empty_bucket_count – указывает число пустых контейнеров в  hash индексе.

Меньше 10, то число значение BUCKET_COUNT слишком малое, идеально значение более 33 и более.

avg_chain_length –указывает среднюю длину цепочек в hash контейнерах. Если значение avg_chain_length больше 10 и empty_bucket_percent больше 10, то, вероятнее всего, имеется много одинаковых значений ключей индекса и использование некластеризованного индекса будет более целесообразным. Средняя длина цепочки, равная 1, является оптимальной.

Заключение.

Технология In-Memory довольно полезное дополнение, которое позволяет получить огромную производительность в базах данных на MS SQL Server. Но, как и везде, для этого нужно правильно организовать и построить вашу базу данных, просто взять и включить опцию In-Memory на ваших таблицах недостаточно, в некоторых случаях мы можем вообще получить деградацию.

Необходим глубокий анализ структуры таблиц и данных, запросов, а может и архитектуры ваших приложений. Для новых систем и приложений, лучше сразу планировать поддержку данной технологии, это будет легче чем потом пытаться подстроить базу под In-Memory технологию, тем более в MS SQL Server 2016 снято много ограничений.


Так что тестируйте, используйте, улучшайте. 

Комментариев нет :

Отправить комментарий