Buffer Pool Extension в SQL Server 2014 часть 2, сравнительное тестирование производительности

Давайте посмотрим поближе, насколько можно ускорить производительность вашей системы с помощью BPE. Для тестирования я буду использовать виртуальную машину с 4 Гб оперативной памяти. Я создал отдельную базу данных с одной таблицей. В таблице всего 2 столбца [id] и [n]. Первый столбец имеет целочисленный тип данных и является первым ключом с кластеризованным индексом на нем. Второй столбец просто выполняет роль каких-то данных в системе.

use [master];
go
 
create database [BufferPoolExtensions_test];
go
 
use [BufferPoolExtensions_test];
go
 
create table [dbo].[TestTable] (
	[id] int identity(1,1) not null,
	[n] [char](1000) not null,
 
	constraint [PK_TestTable] primary key clustered ([id])
);
go
 
insert into [dbo].[TestTable] ([n])
values (replicate('A', 1000));
go 1000000

Размер таблицы получается чуть больше 1 Гб и в первом тесте она целиком будет помещаться в память. Это так называемая идеальная ситуация, когда памяти хватает для всех оперативных данных.

Тестирование будет заключаться в том, что я буду выполнять 10000 случайных запросов на выборку одной строки данных. Это хороший пример OLTP нагрузки на чтение. Данный запрос я буду выполнять несколько раз и считать среднее арифметическое результатов:

select [n]
from [dbo].[TestTable]
where [id] = cast(rand(checksum(newid())) * 1000000 as int) + 1;
go 10000

Итак, результаты первого теста, когда все данные в оперативной памяти и BPE отключен: 1.4 секунды. Это так называемая идеальная ситуация, когда памяти достаточно и все данные находятся в кэше.

Во втором результате я слегка модифицирую запрос и добавлю в него команду очистки Buffer Pool, чтобы посмотреть на «худший» вариант, когда в кэше нет никаких данных.

dbcc dropcleanbuffers;
go
 
select [n]
from [dbo].[TestTable]
where [id] = cast(rand(checksum(newid())) * 1000000 as int) + 1;
go 10000

Результат 2 минуты и 2 секунды.

Во третьем тесте я ограничу размер максимально доступной памяти для SQL Server до 256 Мб – это почти в 4 раза меньше, чем размер данных и попробую повторить тест. При этом стоит отметить, что во время теста в кэше не находилось больше чем 90 Мб от всех данных в таблице, что составляет примерно только 8% от всего объема. Результат: 1 минута 56 секунд. Получилось вполне ожидаемо.

В четвертом тесте я включу Buffer Pool Extension размером 4 Гб и сделаю полное сканирование таблицы перед началом теста, чтобы все данные были либо в памяти, либо в BPE. Стоит отметить, что практически все данные размещаются в BPE, т.е. SQL Server старается поместить все чистые страницы туда и не держать их в памяти. Результат теста: 4.6 секунды. Это в 3 раза хуже, чем если бы данные находились в памяти, но в 25 раз лучше, чем случайно считывать их с диска, если памяти не хватает. Вполне достойно на мой взгляд.

И последний, пятый тест. Всю базу данных я размещу на SSD диске, отключу BPE и буду очищать кэш каждый раз перед запуском. Результат: 6.3 секунды – даже медленнее, чем использовать BPE.

Итоговая таблица с результатами:

№ теста

Описание теста

Результат, время

1

Все данные находятся в памяти.

1.5 сек.

2

Все данные находятся на диске, (“холодный” кэш, в памяти данных нет).

2 мин. 02 сек.

3

Размер оперативной памяти ограничен. В кэше находится примерно 8% от всего объема данных.

1 мин. 56 сек.

4

Размер оперативной памяти ограничен как в тесте №3, но включен BPE – практически все данные находятся в BPE, а не в памяти.

4.6 сек.

5

База данных расположена на SSD, (“холодный” кэш, в памяти данных нет).

6.3 сек.

 

Итак, какой вывод можно сделать из тестирования. Использование BPE может заметно ускорить случайную выборку данных из таблицы, если памяти недостаточно для полного кэширования таблицы. При этом очень интересны результаты четвертого и пятого тестов – получается, что использовать BPE может быть выгоднее, чем располагать базу на SSD. Но опять же, стоит оговориться, что мое тестирование не отражает вашей ситуации и данные могут заметно различаться. Поэтому перед внедрением стоит протестировать опцию на вашей конкретной нагрузке и только после этого принимать решение. Если вы тоже тестировали BPE и у вас получаются другие результаты – предлагаю поделиться и обсудить это в комментариях к заметке.

В следующей статье я расскажу о способах мониторинга систем с включенным Buffer Pool Extension.

  • Дмитрий

    Сергей, укажите пожалуйста характеристики дисков и памяти для полноты картины.

    Like or Dislike: Thumb up 0 Thumb down 0

    • Тесты проводились на виртуальной машине, которая была расположена на обычной рабочей машине с Core i7 + SSD. Виртуалке было выделено 4 Гб памяти, но в зависимости от теста для SQL Server размер ограничивался. Там где нужно в тесте было задействовать HDD – я использовал обычный диск на 7200 оборотов – один, но т.к. не было конкурентной нагрузки, то не должно появиться разницы, сколько дисков будет, надо будет только учитывать кэш контроллеров или хранилища. А вот если взять диск на 15000 оборотов, то результаты потенциально могли быть в 1.5-2 раза лучше у тестов с использованием HDD. Я нисколько не претендую на какую-то особую точность в результатах, но мне было интересно провести более-менее реальные тесты и посмотреть. На реальном сервере результаты будут другими, т.к. придется учитывать изменение данных, конкуретную загрузку, зависимость запросов не только от производительности диска, но и процессоров и т.п.

      Like or Dislike: Thumb up 0 Thumb down 0

      • Дмитрий

        Спасибо. Больше всего интересно, какие IOPS-ы у SSD? Лучше – сразу модель SSD. Мне кажется, эти значения могут оказать серьезное влияние на тесты с SSD.

        Like or Dislike: Thumb up 0 Thumb down 0

        • SSD – Corsair Neutron GTX. Если смотреть на то, что он может, то получаются такие результаты: случайное чтение 8кб блоками с очередью 1 – чуть больше 9000 IOPs, а случайная запись 1кб блоками с очередью 1 – почти 18000 IOPs. Эти показатели здесь, пожалуй, наиболее показательны.

          Like or Dislike: Thumb up 0 Thumb down 0

  • А почему четвертый быстрее пятого? о_О
    Часть BPE все же в оперативной памяти?
    Как я понимаю стандартный движок SQL без BPE не умеет держать части индекса в оперативной памяти, только целиком?

    Like or Dislike: Thumb up 0 Thumb down 0

    • Стандартный движок кэширует индексы по страницам, так что спокойно может находится в памяти только часть индекса. Здесь видимо играет роль то, что при чтении с диска Buffer Manager сначала выделяет Buf в памяти, заносит в него страницу и т.п. А если данные уже в BPE, то этой дополнительной нагрузки нет, поэтому и получилось быстрее. Опять же – это мое предположение.

      Like or Dislike: Thumb up 0 Thumb down 0