Новые средства и методы оптимизации производительности и отказоустойчивости на примере MS SQL 2012 (RC0): Denali

Пока мы готовили эту статью Microsoft уже выпустила MS SQL Server 2012: RTM и совсем уже скоро выйдет финальная версия продукта, в котором планируется множество интересных нововведений.

Охватить их все в одном материале довольно трудно, поэтому остановлюсь только на двух из них, которые показались наиболее интересными — связаны они с повышением производительности и отказоустойчивости. Рассматривались они на примере релиз-кандидата, но не думаю, что в финальном релизе что-то существенно изменится.

Практически все описанные в данной статье функциональные возможности MS SQL, связанные с производительностью сервера, направлены на оптимизацию операций чтения. Для эффективного внедрения некоторых новых функциональных возможностей придется восстановить в памяти построение реляционных БД и формы представления данных. Также потребуется модификация программного обеспечения, которое с этими самыми базами данных работает.

Зачастую система обработки данных находится в пределах одного ЦОДа, что не всегда логично с точки зрения здравого смысла, ибо отказ ЦОДа весьма вероятен. В этой части статьи будет рассмотрена территориально разнесенная среда обработки данных, хоть и эмулированной в стендовых условиях на виртуальной ферме следующего вида:

Один за всех и все за одного: AlwaysOn & Availability group

Как все знают, высокопроизводительный кластер — штука ручной работы и, стало быть, дорогая. Многим хотелось бы его пощупать без ущерба для кошелька. Сходное решение сначала возникло для Exchange Server в виде Database Availability Group, а затем было приспособлено для нужд серверов баз данных.
Итак, что же это такое? Функция AlwaysOn (доступная, к слову, только в Enterprise Edition) предназначена для обеспечения отказоустойчивости баз данных. Именно баз банных, а не инстансов, в которых эти базы данных работают. Работает эта технология, осуществляя репликацию базы данных между серверами, точнее — лога транзакций.

Рассмотрим процесс моделирования отказоустойчивой группы географически распределенных серверов. Берем 4 одинаковых сервера, введенных в домен, под управлением внешнего домен-контроллера. Чтобы посмотреть, как они будут обмениваться данными в обстановке, приближенной к реальной, для двух (SQLONE, SQLTWO) настраиваем соединение на скорости 10 Gb, SQLTHREE сажаем на канал в 1 Gb, а SQLFOUR синхронизируется по 3G (так как это сферический конь в вакууме, то берем скорость 10 Mb). Все они входят в виртуальное облако (ESX 5.0), созданное на основе сервера DEPO Storm 5302 (FCoE) и хранилища LSI 2600 (FC), объединенных посредством Cisco Nexus 5548.

Имея группу MSSQL-серверов, объединенных в отказоустойчивый кластер (обойдемся без кворума, хватит и Node Majority), включаем на каждом из них опцию ‘AlwaysOn Avaibility Groups’.

Выбираем будущую отказоустойчивую базу и переключаем в модель восстановления типа Full, так как репликация осуществляется посредством, как уже было сказано, размазывания лога транзакций на серверы-партнеры. Создаем на основе данной базы Availability Group, которой назначаем запасные места размещения числом до 3-х Secondary-серверов.

Указываем схему работы с этими серверами, все они будут реплицировать на себя данные выбранной БД. Не забываем создать на всех серверах группы логины для пользователей данной БД. Один или два сервера могут поддерживать синхронную репликацию с исходным набором данных. Один из них должен быть Failover-ready, чтобы в случае проблем с Primary успеть перехватить обслуживание клиентов на себя. По умолчанию Secondary ничем не занимаются и просто жрут электричество. Если поставить галочку ‘Read Access Allow’ обычный Secondary, находящийся в режиме синхронной репликации, превращается в Active Secondary или Readable Secondary.

Выглядит это так:

«А какая нам польза от этого нахлебника?» — спросите вы. Во-первых, он может обслуживать запросы чтения. Особенно если приложение, подключающееся к группе доступности, делает это исключительно с целью считывания данных (Read Only), то есть в строке инициализации соединения есть параметр ‘Application Intent=readonly’.

Во-вторых, для баз, находящихся в Availability Group, есть замечательная опция ‘Use secondary for backup’. Таким образом, даже если базы разрослись до таких объемов, что бэкап занимает 24 часа — вы можете спокойно делать его с реплики данных на Secondary, не пытаясь впихнуть в одни сутки рабочий день офиса и окно обслуживания сервера. Это особенно удобно для серверов БД с режимом работы 24/7.

В целом при аккуратной правке приложений, работающих с базой, можно добиться того, что все операции чтения будут производиться с активных Secondary, а операции записи будут сконцентрированы на Primary.
Также есть опция назначения выделенного «слухача» (listener) дабы приложения, работающие с базой, обращались сразу по нужному адресу:

Недостатки у этой схемы конечно есть и в первую очередь это цена. С другой стороны получаем схему работы, когда один сервер пишет, два читают, четвертый бездельничает и присматривает за своей копией. При том, что писатель с первым читателем могут быть в Москве, второй читатель в Санкт-Петербурге или Лондоне, а «бездельник» будет сидеть во Владивостоке или Пекине. Такой подход позволяет снизить нагрузку на основной сервер по операциям чтения, разгрузив его для более эффективного выполнения операций записи, что эффективнее по сравнению с классическим отказоустойчивым кластером.

7 по вертикали и 4 по горизонтали — Columnstore index

Другой вариант оптимизации работы с БД — так называемые Columnar СУБД, о которых многие наверняка наслышаны. Их основное отличие от классических Rowbased заключается во внутреннем представлении данных. Предположим, что у нас есть таблица следующего вида:

Классическая Rowbased (строковая) база данных хранит эти данные в виде:

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

В случае «новомодной» (то есть придуманной еще в начале 70-х годов прошлого века, но по мнению специалистов из Microsoft внезапно оказавшейся нужной именно сейчас) Columnar базы данные хранятся в виде:

Как легко заметить здесь мы видим ситуацию совершенно обратную: для того чтобы добавить строку, нужно модифицировать каждый столбец, а следовательно вытаскивать БД в оперативную память, перебирать и укладывать обратно. Зато добавить новый столбец или изменить существующий за одну операцию — раз плюнуть. Опять же селективная выборка становится очень удобной: просто берем нужные столбцы исходной базы и таким образом экономим место в оперативной памяти.

Дополнительный плюс Columnar БД проявляется при сжатии — так как в пределах столбца данные имеют более однородный вид, чем в пределах строки, то алгоритмы сжатия вида LZW дают в разы большую степень сжатия данных на столбцах, чем на строках.

Columnstore index (CSI) же дает нам возможность сделать из таблицы некий гибрид Rowbased и Columnar. Иными словами, предоставлена возможность в классической Rowbased таблице выбрать столбцы для хранения в Columnar виде.

Это влечет за собой некоторые ограничения, самые неприятные из которых:

• если в таблице определен Columnstore index, таблицу нельзя модифицировать. Никаких UPDATE, INSERT, DELETE и MERGE. Можно конечно сделать DROP -> INSERT -> CREATE, но осадок неприятный остается;
• есть ограничения по типам для столбцов, в которых можно использовать Columnstore index (http://msdn.microsoft.com/en-us/library/gg492088(v=sql.110).aspx#Described);
• Columnstore Index не может быть ключом (как первичным — PRIMARY, так и внешним FOREIGN), не может быть уникальным и не может подлежать сортировке ASC&DESC (ибо за сортировку отвечает алгоритм сжатия) и многое другое (http://msdn.microsoft.com/en-us/library/gg492088(v=sql.110).aspx#Restrictions);
• также Columnstore index весьма требователен к оперативной памяти, иными словами, если столбец не влезает в оперативную память целиком, пользы не будет.

Но, безусловно, есть и положительные стороны данного решения:

• выборка (SELECT) по полям Columnstore index производится приблизительно в 10 раз быстрее, чем выборка из Rowbased;
• то же можно сказать про * JOIN.

«И это все?» — спросите вы. Да, это все. Мы «всего-то» имеем выигрыш приблизительно в десять раз на двух самых популярных запросах к БД. Далее начинается работа архитектора по приведению базы к приличному виду. Одни из них мы оставляем в Rowbased как часто изменяющиеся, другие частично преобразуем в Columnstore, как статические.

Или подходим более творчески. Например, есть открытые заказы, которые хранятся в Rowbased, а каждую полночь глобальный архив обработанных заказов делает в таблице за последний месяц: DROP COLUMNSTORE INDEX, INSERT DATA INTO MONTH, CREATE COLUMNSTORE INDEX. А потом, быстренько сверив обе таблицы, вычищает общие строки из рабочей базы и все живут счастливо.

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

Заключение

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

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

В целом Release Candidate оставил приятное впечатление. Появился функционал, который позволяет не кривя душой называть MS SQL 2012 продуктом Enterprise-уровня, с чем я нас всех и поздравляю.

Иван Рябцев,
системный инженер DEPO Computers

Автор: DEPOteam