Buffer Pool Extension в SQL Server 2014 часть 1, обзор

Наверное, многие слышали фразу «640K ought to be enough for anybody», которую ошибочно предписывают Биллу Гейтсу. И хотя оперативная память постоянно дешевеет, но для сервера баз данных ее практически никогда не бывает достаточно. SQL Server обладает хорошей интеллектуальной системой кэширования часто используемых данных, но их размер почти всегда превышает размер доступной оперативной памяти, которую иногда бывает проблематично расширить. Одной из интересных особенностей, которые появятся в SQL Server 2014, является Buffer Pool Extension, которая позволяет в случае ограниченного набора памяти кэшировать страницы с данными на SSD диске. В этой статье я постараюсь сделать детальный обзор этой технологии на примере SQL Server 2014 CTP1.

Buffer Pool – область памяти (самая большая), которую SQL Server в основном использует для хранения страниц с данными. Любая страница, которая считывается с диска сначала помещается туда, а уже потом используется по назначению (для считывания или изменения данных). Страницы в памяти могут находиться в двух состояниях: так называемые «чистые» страницы, данные в которых никогда не изменялись, и «грязные», в которые были внесены изменения, но еще не сохранены обратно на диск с помощью процессов checkpoint или lazy writer. Buffer Pool Extension позволяет «увеличить» размер Buffer Pool используя для этого SSD диск. Это может быть полезно, если у вас есть возможность добавить SSD диск в ваш сервер, но нет возможности расширить оперативную память или вынести все часто используемые данные на SSD в силу каких-то ограничений. Например, можно использовать один дешевый SSD в качестве кэша в BPE не предъявляя к нему никаких требований по надежности, в то время как размещение всей или части базы данных потребует применения более дорогих и надежных систем.

Включить опцию очень легко. Для этого достаточно использовать команду ALTER SERVER CONFIGURATION и указать расположение файла и его размер, который будет использоваться в качестве Buffer Pool Extension.

alter server configuration
set buffer pool extension
on ( filename = 'X:\MyCache.bpe' , size = 64 gb );
go

После выполнения данной команды создается указанный нами файл, который сразу начинается использоваться. SQL Server может использовать его только для кэширования «чистых» страниц. Изменить расположение файла или его размер невозможно. Для этого требуется отключить BPE и включить его заново с другими параметрами. Отключается Buffer Pool Extension следующей командой.

alter server configuration
set buffer pool extension off;
go

Итак, для чего может быть полезна данная опция и какие у нее плюсы.

  1. Ее рекомендуется использовать для OLTP систем, в которых преобладают запросы на чтение данных.
  2. Нет никаких рисков потери данных, т.к. BPE кэширует только «чистые» страницы. При сбое диска BPE просто отключается.
  3. Не требуется вносить никаких изменений в приложения – все начинает работать сразу, как только вы включаете опцию.

Но, также имеются некоторые ограничения, которые требуется учитывать перед тем, как планировать использовать эту опцию.

  1. Возможность будет доступна только в редакции Enterprise Edition.
  2. Вы не можете задать файл для BPE размером меньше, чем размер текущей оперативной памяти. По крайней мере судя по моим тестам с использованием SQL Server 2014 CTP1, вы будете получать ошибку вида:

    Msg 868, Level 16, State 1, Line 18
    Buffer pool extension size must be larger than the current memory allocation threshold 2048 MB. Buffer pool extension is not enabled.

  3. Максимальный поддерживаемый размер оперативной памяти 128 Гб, т.е. обладатели систем с большим размером памяти воспользоваться опцией не смогут. И это довольно неприятный момент, т.к. такой и больший объемы вполне доступны покупателям даже для серверов начального уровня.
  4. Размер файла BPE не может превышать 32 размера вашей оперативной памяти. Т.е. максимально допустимый размер для него будет 32 x 128 Гб = 4 Тб. Рекомендуется задавать размер BPE не более 4-10 размеров оперативной памяти.
  5. Естественно располагать файл следует на быстром SSD диске, иначе смысл опции полностью теряется. И, хотя нет никаких требований к надежности, следует учесть, что если вдруг диск выйдет из строя – вы останетесь без BPE, что может существенно повлиять на производительность вашей системы, если она сильно зависит от этой опции.

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

  • Alexey

    Сергей, спасибо за статью. Интересно было понять, будет ли данная система полезна для хранилища для отчётности (SSRS)? Я так понял что пока MS об этом не писали.

    Like or Dislike: Thumb up 0 Thumb down 0

    • Для хранилищ они ее не позиционируют, что собственно ожидаемо. На хранилищах в основном присутствует нагрузка – последовательное чтение. А последовательно считать на максимальной скорости можно и с обычной дисковой подсистемы. Ну и в хранилищах обычно данных очень много, поэтому их и кэшировать не имеет смысла – все равно все не влезет. Для отчетов SSRS кстати отлично можно использовать их встроенное кэширование и предварительный расчет – у меня было много таких реализаций. Ночью, например, заливка новой порции данных, трансформация и т.п., а потом SSRS обновляет отчеты, и к утру все сделано и мгновенно открывается.

      Like or Dislike: Thumb up 0 Thumb down 0

      • Alexey

        Все новинки, кроме пожалуй columnstore clustered индекса для OLTP, даже скучно как-то : ) Насчёт кэширования SSRS не очень понял, там можно расписание настроить, когда отчёт будет кэшироваться или как?

        Like or Dislike: Thumb up 0 Thumb down 0

        • Columnstore это уже немало. 🙂 А для отчётов да, можно указать расписание, когда отчёты будут формироваться и настройки кэширования. Тут долго расписывать нюансы, но однозначно стоит посмотреть на эти настройки. Для тяжёлых и редко обновляемых отчётов самое то.

          Like or Dislike: Thumb up 0 Thumb down 0

          • Alexey

            Сергей, а если в таблице Clustered Columnstore Index, к ней можно создать B-tree non-clustered index или нет? Я всё никак не доберусь проверить)

            Like or Dislike: Thumb up 0 Thumb down 0

          • Если есть Clustered Columnstore, то других индексов добавить на таблицу нельзя.

            Like or Dislike: Thumb up 0 Thumb down 0

          • Alexey

            Вот это очень печально конечно : (

            Like or Dislike: Thumb up 0 Thumb down 0

          • Нормально, там же совершенно по другому рганизовано хранение. Если рассматривать каждый индекс как отдельную физическую структуру – то ничего не мешает создать 2 копии таблицы – одна с columnstore, а вторую обычную урезанную и создать кластеризованный rowstore индекс. Физически будет одинаково, только если это действительно нужно и дает преимущество. 🙂 Вообще про columnstore в 2014 надо тоже будет серию статей готовить.

            Like or Dislike: Thumb up 0 Thumb down 0

          • Alexey

            Копия таблицы это слишком жирно) Получается либо по старому (non-clustered columnstore index), либо секционировать по годам (например) и сделать clustered columnstore index в случае, если основной фильтр будет эта дата.

            Like or Dislike: Thumb up 0 Thumb down 0

          • Я к тому, что любой индекс это фактически копия части данных. Ну а так надо смотреть, что выгоднее будет в итоге учитывая все факторы.

            Like or Dislike: Thumb up 0 Thumb down 0