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

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

Итак, когда речь заходит о тестировании производительности, обычно выделяют:

• нагрузочный тест (load test) - насколько хорошо мы можем выдержать плановую загрузку (сохранив при этому заданные показатели производительности)
• стресс-тест (stress test) - под какой нагрузкой мы сломаемся и что при этом произойдет

Тестирование производительности обычно проводят для каждой версии приложения и наблюдют не только и не столько за абсолютными цыфрами (хотя важны и они), сколько за динамикой - "мы внесли изменения в архитектуру и нам удалось улучшить производительность на X%".

Теперь разберемся что есть что применительно к базам данных. Во-первых, при попытке произвести тестирование производительности базы данных, мы неизбежно сталкиваемся с набором довольно специфических трудностей:

• настройки: база данных не является самодостаточной сущностью с точки зрения приложения - она работает под управлением сервера БД, от характеристик и настроек которого сильно зависит то, с какой скоростью наше приложение сможет считывать/записывать данные
• непрямое взаимодействие: пользователь приложения в подавляющем большинстве случаев не взаимодействует с базой данных напрямую, поэтому при неизменном с точки зрения пользователя функционале, разные версии приложения могут генерировать существенно разную нагрузку
• профиль нагрузки: производительность сильно зависит от того, как именно используется приложение (есть более "тяжелые" и более "легкие" операции), впрочем, эта проблема характерна для приложения вцелом
• масштабируемость нагрузки: производительность существенным (и нелинейным) образом зависит от объемов данных в базе и количества подключений (одновременно работающих пользователей)
• единицы измерения: сложно определить в каких "попугаях" мерять производительность базы данных - ведь не существует "единых универсальных запросов к базе данных"

Попробуем разобраться с этими вопросами.

Настройки. Тут особенно сказать нечего: да, от настроек (равно как и от оборудования) очень многое зависит. Нужно постараться воспроизвести настройки целевого окружения (production environment) и стараться не менять их от теста к тесту. С оборудованием сложнее. Идеальный вариант - тестировать на точной копии целевых серверов, но практически мало кто может себе такое позволить. Однако запуск тестов на одном и том же оборудовании с одним и тем же набором настроек позволит получить общую картини движения: ничего не поменялось, улучшилос, ухудшилось и т.п.

Непрямое взаимодействие. По большому счету у нас здесь есть два пути:

1. Нагружать базу данных через приложение. При этом мы фактически тестируем еще и приложение. Плюс здесь в том, что мы будет эмулировать работу пользователя (по-идее) - т.е. все будет так же, как и в реальных условиях. Минус в том, что сложнее измерять (т.к. все измерения будут относиться к системе в целом), требуется больше ресурсов (как программных, так и аппаратных) и сложнее масштабировать (увеличиваем число взаимодействующих подсистем - увеличиваем число мест, которые могут стать бутылочным горлышком).
2. Нагружать базу данных напрямую. Здесь мы может свести к минимуму влияние других подсистем приложения, но можем столкнуться с необходиомостью реализации чуть ли не самого приложения с нуля (для эмуляции активности пользователя). Альтернативой является запись активности приложения с последующим его воспроизведением в несколько потоков.

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

Профиль нагрузки. Имеет смысл завести несколько "пакетов", в которые войдут разные наборы сценариев использования приложения. Можно составить некоторый обобщенный сценарий поведения пользователя (вроде: вошел в систему, почитал новости, зашел в раздел такой-то, потом туда-то, сдела то-то, вышел) и тестировать именно его. Несколько подобных профилей, составленных с учетом статистики использования реальной системы, позволят сгенерировать реалистичную нагрузку, в максимальной степени похожую на ту, которую генерируют реальные пользователи.

Масштабируемость нагрузки. Здесь все зависит от целей, которые мы преследуем. Как уже было сказано, масштабировать нужно объем базы данных и количество подключений (одновременно работающих пользователей). Очевидное общее правило: чем больше данных, тем меньше пользователей сможет комфортно работать. Существуют утилиты, которые могу нагенерировать случайные данные для БД любой структуры. Но не всегда их можно применить: денормализованность схемы, неполное описание ограничений на уровне схемы, "хитрая" логика обработки данных, хранящаяся в хранимых процедурах или коде приложения - все это значительно затрудняет генерацию. Хорошим подспорьем будет какая-нибудь реальная БД с пользовательскими данными.

Единицы измерения. Вот здесь, пожалуй, одна из самых больших проблем. Мы можем измерять нагрузку на сервер, на котором работает наша СУБД с тем, чтобы понять, справляется он или нет с заданной нагрузкой и что меняется от версии к версии. В этой связи полезно измерять:

1. среднюю загрузку процессора (CPU, %) - показатель в 60-70% считается нормальным (допустимым), если больше - сервер не справляется
2. очередь ввода/вывода (Average disk queue length) - даже значение в несколько единиц свидетельствует о проблемах с подсистемой ввода/вывода (должно быть 0)
3. количество запросов на блокировки в нашей базе (locks/sec) - чем меньше, чем лучше, важен прогресс от теста к тесту и изменение интенсивности этой величины в течении теста; всплески позволят идентифицировать наиболее тяжелые действия, которые нуждаются в оптимизации в первую очередь
4. среднее время ожидания блокировки (msec) - показывает сколько нам приходится ждать окончания работы других потоков (чем меньше - тем лучше)

В ходе тестирования всегда стоит запустить какой-нибудь инструмент мониторинга запросов к БД с тем, чтобы потом можно было наложить полученный трэйс на данные о производительности и понять, как они соотносятся, найти наболее "тяжелые" операции (запросы) и уделить внимание их оптимизации.

Self service analytics: проект Gemini

Ну что, дорогие читатели (или товарищ майор, фильтрующий Интернет), сегодня я расскажу вам о том, что Microsoft имеет предложить нам в не таком уж далеком будущем. Под "нами" я имею в виду не только разработческую братию, но и (что важнее) "обычных пользователей" (ну не совсем обычных, а скорее power users). Так что устраивайтесь поудобнее, доставайте поп-корн.

Итак, история началась довольно давно: с выходом SQL Server 2005 и Report Builder. Хотя, пожалуй, даже раньше: когда в Microsoft задумали увеличить привлекательность SQL Server-а путем увеличения функциональности последнего - добавили Data Transformation Services (ныне Integration Services), потом Analysis Services и Reporting Services. С количеством установленных копий все складывалось удачно (даже очень). Но со временем пришло понимание того, что Business Intelligence - штука гораздо более "пользовательская", чем те же базы данных (разработка схемы данных, организация хранения, оптимизация производительности запросов - задачи не из простых и врядли интересны конечным пользователям). Кроме того, схема: "нам нужен анализ" -> "обращаемся к IT-парням" -> "парни что-то делают (разрабатывают, куда-то что-то размещают, настраивают...)" -> "(не прошло и полгода как) мы получили что хотели (ну или почти что хотели)". И в тех случаях, когда получается не совсем то, что хотелось, приходится повторять все это сначала. По-идее создавать модель анализа не так уж и сложно (с точки зрения программиста) - инструментарий тот же (Visual Studio), только можно обойтись без программирования. Но все-равно это совсем не тоже самое, что работать с (привычным почти всем офисным работникам) Excel-ем.

Результат чаще всего такой: решения, разрабатываемые с благословения начальства, "крутятся" сами по себе, а большая часть пользователей работает сама по себе, решая свои задачи доступными им способами (рабочие книги Excel с массой сложных вычислений, макросов и ссылками на другие рабочие книги). В иторе имеем массу "приложений", нигде не учитываемую, неподконтрольную никому и с сомнительной достоверностью данных, но при этом весьма активно используемую - головная боль как для пользователей, так и для IT-служб.

Microsoft берется за решение этих проблем под лозунгом "People Ready BI". Первые шаги в этом направлении были сделаланы с выпуском Report Designer - продуктом для создания отчетов, который ориентирован на конечных пользователей (есть уже версия 2.0 с интерфейсом в стиле Office 2007). Следующий шаг - проект с кодовым именем Gemini, целью которого является аналитическая обработка данных на стороне клиента. То, что хотелось бы получить:

• для пользователей: привычные инструменты, возможность не обращаться к "программистам", возможность совместного использования полученного решения (aka collaboration);
• для "программистов": возможность хоть как-то контролировать то, что производится пользователями (или хотябы знать об этом) и отслеживать источники данных.

С технической точки зрения нам предлагается следующее:

• для пользователей: расширение к Excel, позволяющее проводить аналитическую обработку данных на клиентской машине (in-memory, column-based analytics), для совместной работы используется SharePoint;
• для "программистов" (они же IT-отдел): SharePoint, в который в каком-то виде встраивается Analysis Services со средствами мониторинга загржаемых пользователями моделей, обработка этих моделей по расписанию и т.п.

Впервые эта технология была продемонстирована на ежегодной Microsoft Business Intelligence Conference в октябре этого года. Демонстрация и правда выглядит впечатляюще: довольно просто и в тоже время вполне фукнционально для большинства несложных моделей анализа. Там же огласили планы: выпуск в первом полугодии 2010 г, CTP - в течении 12 месяцев. Хотя позже было сказано (в кулуарах) о первом CTP в декабре этого года (правда не очень-то публичном), и о втором CTP ближе к лету 2009 г.

Техническая аргументация в пользу "пользовательской аналитики" достаточно проста: бОльшая (если не подавляющая) часть аналитических моделей укладывается в размер 1 Гб, что вполне под силу нынешнему парку клиентских компьютеров (ноутбук, на котором я пишу эту заметку, имеет 2 Гб оперативной памяти - и ему уже больше года), не говоря уже о том, что будет через полтора года (в момент официального выхода) - так что волноваться, по-идее не о чем. Помимо этого была обещана очень эффективная компрессия данных, что по-идее должно положительно сказаться как на требованиях к памяти, так и на объеме полученных файлов. Таким образом на долю "обычного" Analysis Services остается многопользовательский доступ, сложные вычисления и сложные (и большие) модели с высокой нагрузкой. Что пока неясно:

• схема распространения: будет ли это "волшебное расширение" входить в стандартную поставку какой-то очередной версии Office (было бы хорошо, если бы входило)
• будет ли возможность конвертации "пользовательских моделей" в базу данных Analysis Services (почти уверен что будет, в той или иной форме, хотя об этом и не говорят)

В скобках нужно отметить, что Microsoft - не первая компания, открывшая для себя рынок in-memory аналитики. Стоит упомянуть хотябы QlikView (для которой это основной бизнес), IBM, Oracle и SAP - каждая из последних за счет недавних поглощений явно выказали свой интерс к этому направлению.

Так что будущее аналитики обещает быть интерсным (поговаривают даже о новом поклении OLAP).

По метариалам:

• olapreport.com: Project Gemini — Microsoft’s Brilliant OLAP Trojan Horse
• http://www.qlikview.com/
• Доклад о развитии платформы обработки данных на Business Intelligence Conference
• http://platforma2009.ru

"Платформа 2009"

Сегодня вернулся из Москвы - был на "Платформе 2009". Был на этой конференции в первый раз. По направленности она довольно сильно отличается от DevConnections на которой мне довелось побывать в прошлом году - более "айтишная", нежели программистская. Хотя при этом все-равно крайне познавательная.

Из того, кто/что запомнилось:

• Доклад "Интернет-сервисы с высокой нагрузкой" (представители СКБ Контур) вызвал чувство дежавю: почти один-в-один те проблемы, с которыми приходилось сталкиваться во время работы в Stella Systems.
• Григорий Погульский (Microsoft) с докладом об управлении, основанном на политиках (Policy-Based Management) в SQL Server 2008. Крайне любопытно было узнать как же это сделано.
• Лабораторные работы по SQL Server 2008 с виртуальным образом "производства" sqlskills.com (Kimberly L. Tripp и Paul S. Randall, которых я имел счастье слушать на DevConnections'07 в Лас-Вегасе) - пожалуй это (такой образ) именно то, чего мне не хватало (а самому делать довольно долго и руки как-то никак не доходили ...).
• Валерий Ким (Microsoft) с докладом о будущем Analysis Services 2010 (codename "Kilimanjaro"). Точнее о той части продукта, которая будет отвечать за обработку данных на стороне клиента (in-memory analytics, codename "Gemini"). Впервые об этой технологии было объявлено на Microsoft BI Conference в начале октября. Но одно дело - прочитать скупой анонс, и совсем другое - послушать человека, который непосредственно участвует в разработке. Постараюсь в ближайшее время написать об этом поподробнее.
• "Функциональное программирование и параллельные вычисления" в исполнении Ивана Бодягина и Андрея Корявченко - о .NET Parallel FX. Кажется эта штука должна здорово упростить распараллеливание выполнения кода (ибо толково автоматизировать этот процесс нельзя).
• Большое количество известных людей: Marc Russinovich, David Chappel, Eric Rudder, Dmitry Robsman.
• Ключевые слова "Платформы 2009": Azure, cloud computing, Windows 7.

В целом - интересно и полезно.

Ускорение SMO v.10: дело не в запросах

Мне все никак не дает покоя причина увеличение скорости работы SMO v.10 (см. здесь). Все думал: ну ничего себе, насколько, оказывается, можно эффективно (или неэффективно) работать с метаданными! Возникло сильное желание узнать: как нужно и как не нужно делать.

Разбор полетов начал с профайлера (слева - версия 10, справа - 9):

Говоря по-простому: на уровне запросов к базе не поменялось ничего. По содержанию. А вот форма немного изменилась. Если в версии 9 все запросы генерировались динамически (т.е. для каждого объекта создавался свой запрос), то в версии 10 используются запросы с явной параметризацией, что по-идее существенно облегчает работу сервера - не надо на каждый запрос создавать новый план выполнения. Мониторинг сервера во время создания скриптов базы с помощью dbbuilder показывает, следующее:

• для SMO v.9 практически на каждый запрос создается новый план выполнения (даже для однотипных запросов), повторного использования планов не происходит;
• для SMO v.10 план типовых запросов в большинстве случаев используется повторно.

В целом ожидаемо. Но дальше начинается интересное. Если проанализировать статистику выполнения этих запросов (суммарное время выполнения, общая нагрузка на подсистемы ввода/вывода и загрузка процессора), то обнаруживается, что разница между версиями во-первых не так уж и велика, а во-вторых, по "чистому времени выполения запросов" она даже не в пользу новой версии (v9: 19.5 s, v.10: 26 s).

Так что версию о том, что причина ускорения кроется в эффективности/неэффективности самих запросов (или способа их выполнения) скорее всего нужно отбросить.

Начал было "ковыряться" в коде SMO, но что-то пока без особого успеха (слишком уж его много).

Чисто для себя решил выяснить разницу по производительности (измеренную на уровне приложения) между параметризированными запросами и динамически сгенерированным SQL. Для простых запросов эта разница составляет около 20%, что тоже неплохо.

4я встреча IT Talk в Харькове

Был вчера на IT Talk. Мероприятие привлекает довольно много людей и развивается. Теперь уже докладчики - не приглашенные "варяги", а те посетители, кто изъявил желание о чем-то рассказать. Но все-равно у меня лично целый ряд вопросов/замечаний:

• Так и не понял для себя: кто является целевой аудиторией (чисто визуально половина аудитории - студенты и интересующиеся, а половина - "представители индустрии"). При этом доклады по большому счету не ориентированы ни на одну из этих групп: для одних, пожалуй, чересчур сложно, а для других в основном беспредметно.
• Второй (или даже третий - точно не припомню) раз хожу туда и так и не пойму цели мероприятия (может, правда, это я лично ожидаю от него слишком многого). Мне бы виделось, что если мероприятие массовое, то и цель у него должна быть понятна (и близка) этим самым массам. А по факту получается полигон, на котором очень ограниченный круг товарищей "демонстрирует шары". Причем конструктивизма в большинстве высказываний ни на грош - они имеют целью либо показать какой говорящий "классный, всезнающий и профессиональный" или что докладчик "ничего не смыслит в колбасных обрезках".
• Организация: если мы хотим дискуссию (а судя по названию мероприятия мы таки ее хотим), то нужно либо организовывать круглый стол (с ограниченным числом говорящих) - наподобие "Свободы слова" или "К барьеру", либо сокращать количество участников. Ибо дискуссия во время лекции или после оной - это балаган (тем более в отсутствии ведущего).
• Тема должна быть более конкретной и конструктивной. А не: "Я тут вам сейчас расскажу что-нибудь (а вы подеретесь из-за деталей, о которых я намеренно не говорил)". Кто-то может поделиться опытом? Пожалуйста! Только нужно рассказывать об условиях, в которых этот опыт получен: размер проектной команды, ее квалификация, сложность проекта, каков заказчик (как он участвует в процессе), есть ли бюрократия и т.п. - с деталями (ведь именно они зачастую и делают разницу между номинально похожими проектами колоссальной).

С одной стороны, конечно, приятно, что в городе есть какое-то публичное общество разработчиков, организуются какие-то мероприятия, но осадок остается после каждой такой встречи - время было потеряно впустую (ну или почти впустую). Вспоминается фраза из одной песни: "Иди тусуйся!" - в этом по-моему (на данном этапе) и заключается суть мероприятия. А могло бы быть полезным. Хочется надеяться таким оно когда-нибудь станет (если кризис нас всех не прикончит ;-) ).

svn:keywords и Unicode

Давно хотел, чтобы все было как у людей: в каждом файле в заголовке - номер последней ревезии, в которой этот файл изменялся, автор изменений и дата. Такой заголовок особенно полезен для sql - скриптов, которые создают хранимые процедуры и пользовательские функции (в общем: логику), ибо позволяет заглянув в базу сразу же узнать кто и когда менял тот или иной объект.

Для тех, кому повезло использовать SVN для контроля за версиями (мне, как раз повезло), есть, казалось бы, простой и безболезненный способ - svn:keywords. Выставляем для sql (или любого другого) файла это свойство (см. руководство по TortoiseSVN здесь), и в файле используем ключевые слова, например так:

-- $Rev$

-- $Author$

CREATE PROCEDURE ...

и вот оно счастье...

Но не тут-то было! Проблема появляется оттуда, откуда ее никто не ждал. SQL Server Management Studio по-умолчанию сохраняет все файлы в формате Unicode. Причем не просто Unicode, а UTF-16 с byte-order mark. Точно также поступают и некоторые библиотеки (в частности SMO). А SVN помечает файлы в этой кодировке как имеющие mime-type: octet-stream/application - т.е. фактически считает их бинарными и, соответственно, никакой подстановки ключевых слов не делает. Обидно, тем более, что файл-то текстовый.

Поиск в Google дает мало толковых результатов: да, есть такая проблема, можно попытаться обойти (и то, сайт недоступен, можно почитать только из кэша Google). И только покопавшись еще глубже, можно обнаружить вот это: SVN поддерживает Unicode, но текстовым будет считаться только файл в кодировке UTF-8 (а не UTF-16 или UTF-32) - соответсвенно и ключевые слова будут работать только если файл в одной из однобайтовых кодировок (верх "крутизны" - та самая UTF-8).

Ну лучше уж так, чем никак. В результате:

• быстренько сделал утилиту по переконвертированию файлов в кодировку UTF-8 (зачем пользоваться shareware, если самом сделать - дело 5ти минут? ;) ). Исполняемый файл здесь, исходник (если кому интересно) здесь.
• сделал так, что dbbuilder теперь сохраняет все скрипты в кодировке UTF-8

Надеюсь кому-нибудь поможет.

AlexS

Пару слов о Sql Server Management Objects (SMO) (v.9 и v.10)

На днях повысил версию SMO. Причин на то было две: категорически не устраивала работа SMO v.9 с полнотекстовыми индексами и хотелось добавить поддержку SQL Server 2008 (что в любом случае требовало SMO v.10). Но обо всем по-порядку.

Когда появилась настоятельная необходимость добавить в dbbuilder поддержку полнотекстовых объектов (каталогов и индексов), то первым (вполне очевидным) решением было обратиться к SMO и при генерации скриптов сохранять еще пару типов объектов. При создании базы проблем возникнуть не должно было. Но на деле оказалось, что в SMO v.9 есть такая "особенность": при создании скрипта для полнотекстового индекса в него всегда добавляется USING [<исходная БД>]. Причем эта "фича" гнездится в самом коде SMO (спасибо, Reflector) и на нее не влияют ScriptingOptions равно как и порядок создания скриптов. В результате мы всегда получаем скрипты, которые непригодны к использованию сразу после генерации и требуют ручной "доработки напильником" (удаления USING), что нехорошо.

Поругавшись про себя решил посмотреть: а как обстоит дело в SMO v.10? Оказалось что хорошо обстоит: баг (или фичу) починили (или убрали) и теперь скрипты получаются такие, какие и дОлжно. В ходе тестовых запусков обнаружил, что задачи по генерации скриптов/созданию базы стали выполняться быстрее. Решил проверить и с еще большим удивлением обнаружил, что таки да, таки что-то произошло: SMO v.10 работает значительно быстрее v.9. Сравнительные (усредненные по двум запускам) результаты для некоторой базы данных (141 таблица, 194 хранимых процедуры, 2 триггера, 10 пользовательских функций и 5 представлений) приведены в таблице ниже.

	SMO v.9	SMO v.10
Создание новой (пустой) базы	0:24	0:20
Генерация скриптов	8:35	0:58

Признаюсь: меня сильно впечатлило увеличение скорости генерации скриптов почти на порядок.

А теперь ложка дёгтя: SMO как-то очень забавно работает с SQL Server Express. Если я попытаюсь через SMO обратиться к полнотектовому каталогу базы данных, работающей на SQL Server Express with Advanced Services (в котором есть Full-text search), то получу исключение "SQL Server Express не поддерживает Full-Text". Отлично, просто отлично! Но этот самый Full-text, к счастью, работает вне зависимости от того, что на этот счет "думает" SMO.

Надеюсь кому-то поможет.

AlexS

Начало

Сегодня я решил положить начало блоговой эры в моей жизни. Формальным поводом стало решение создать проект с открытым исходным кодом - dbbuilder. Хотя и без этого в последнее время у меня периодически возникает желание записать что-то, с чем приходится сталкиваться в своей профессиональной деятельности - для себя (для истории), ну и может еще кому-нибудь пригодится.

Так что начнем.