Все мы привыкли к таким вещам, как обслуживание автомобилей (этот процесс называется периодическим ТО), обслуживание оборудования или аппаратуры. Есть понимание, что, не меняя в автомобиле масло для двигателя или фильтра, мы получим поломку всего двигателя и, естественно, у нас будут финансовые потери.
Аналогично ситуация обстоит и с обслуживанием баз данных, особенно используемых в высоконагруженных информационных системах, где происходят частые изменения данных. Зачастую, специалисты, инженеры и программисты забывают о важности обслуживания базы данных и сталкиваясь с проблемами производительности в первую очередь начинают анализировать имеющиеся программные и аппаратные ресурсы, код приложения, индексы, дисковые массивы и сеть. В подавляющем большинстве ситуаций при резком ухудшении производительности в первую очередь необходимо провести тщательную проверку выполнения процедур обслуживания баз данных, а в случае качественного обслуживания переходить к другим видам анализа.
Как правило обслуживание баз данных состоит из нескольких основных частей:
- Перестроение и реорганизация индексов.
Индексы – объекты базы данных (принадлежат таблицам), которые необходимы для ускорения поиска требуемых выборок из таблиц.
- Обновление статистик баз данных.
Статистика – объект баз данных, который необходим для формирования оптимального плана выполнения запроса SQL с учетом распределения данных в таблице.
- Обрезание/приращение файлов баз данных и журналов транзакций.
- Обновление кеша процедур.
Кеш процедур содержит скомпилированные планы выполнения процедур.
- Контроль за свободным местом на дисках, связанный с ростом размера файлов данных.
В данной статье рассмотрим основные признаки и показатели, по которым можно определить, что база данных нуждается в улучшении мероприятий по обслуживанию.
Обслуживание индексов
В индексе есть такой параметр, как FILL FACTOR – процент заполнения данными страниц индекса (например, 80 – это значит, что 80% индексных страниц заполнены данными, а 20% — оставлены для заполнения в процессе изменения данных по полям индекса). А что произойдет, когда делаются множественные изменения данных по полям индекса и 20% свободных страниц не хватает для заполнения новыми значениями? Тогда данные будут сохраняться в новые страницы индекса, при этом последовательность данных в соответствии с сортировкой будет нарушаться. Это явление называется фрагментацией индекса. Оно ухудшает эффективность использования индекса, так как данные в необходимом для получения выборки порядке располагаются в различных местах файла.
Рис.1. Индексы и степень их фрагментации
На рисунке 1 показана статистика по индексам таблицы, при этом важными показателями фрагментированности индексов является ScanDensity – для не фрагментированного индекса значение этого показателя 100, по мере фрагментации значение этого показателя уменьшается. Часто администраторы отслеживают качество обслуживания индексов по заданиям на перестроение/реорганизацию индексов по расписанию, это очень грубая оценка, так как скрипт может работать неправильно, в нем могут быть не учтены новые таблицы и индексы. Более правильно оценку делать через ScanDensity.
Теперь перейдем к самому скрипту по реорганизации/перестройки индексов:
— Реорганизация индексов – это дефрагментация страниц индекса (процесс похож на дефрагментацию диска), процесс не долгий, не блокирует пользователей, при этом достигнуть SCANDENSITY = 100 практически невозможно.
— Перестройка индексов – это процесс создания нового индекса с актуальными данными, процесс в зависимости от размера полей и количества строк может занять продолжительное время, блокирует пользователей, получающих выборки с использованием этого индекса, но SCANDENSITY = 100 можно получить.
Обычно рекомендуют использовать реорганизацию для индексов с SCANDENSITY > 85 AND SCANDENSITY < 95, а перестройку индекса для SCANDENSITY < 85.
Но все ли так просто … приведу пример таблицы оборотов за 5 лет, в основном в этой таблице изменяются данные последнего периода (1 месяц), остальные периоды практически не изменяются. Получается, что для такой таблицы SCANDENSITY вполне может быть 98%, при этом данные в последнем периоде полностью фрагментированы и индекс работает неэффективно. Скрипт никогда не выполнит обслуживание индекса этой таблицы по условиям выше. Что же делать?
Более правильным вариантом для фильтрации индексов в скрипте для обслуживания было бы не условие по SCANDENSITY, а показатель – количество новых страниц индекса, созданных при нехватке свободного места в индексе. Наша компания рекомендует использовать именно этот критерий для обслуживания индексов (мониторинг производительности PERFEXPERT содержит всю необходимую статистику для улучшения эффективности обслуживания).
Обновление статистики
Для чего нужна статистика и зачем ее обновлять?
Для получения выборки запроса SQL не существует единственного варианта – разработаны ряд алгоритмов, которые в зависимости от распределения данных могут по отличаться по времени выполнения. Например, в случае, если предполагаемое количество строк одной из таблиц при пересечении небольшое, оптимизатор будет использовать вложенный цикл (nested loops) для получения выборки, если большое – то объединение слиянием или через хеш-функцию (hache join или merge join). Таким образом, статистика дает актуальную информацию о текущих распределениях по данным (статистика построена несколько сложнее, но для понимания мы немного упростили ее смысл). Если статистика неактуальная, то для выполнения запроса SQL будет построен неправильный план выполнения, а соответственно вместо выполнения запроса X секунд – запрос начнет выполняться N*X, где значение N может быть очень большое.
Рассмотрим практический пример запроса SQL для информационной системы 1С:
Рис.2. Список запросов SQL определенного вида
Как видно из рисунка 2, два запроса SQL одного и того же вида выполняются с различными длительностями, один с 0,44 секунды, другой 14,89 секунд. Причем с точки зрения потребления ресурсов при выполнении тоже есть различия – второй запрос потребляет ресурсов CPU и ресурсов диска и памяти значительно больше. С большой степенью вероятности эта проблема неактуальности статистики, не оптимальности плана выполнения запроса, с учетом того, что даты выполнения этих запросов разные.
Важно: без анализа качества обслуживания базы данных нельзя приступать к анализу оптимальности кода запроса SQL, индексному тюнингу, так как это может привести к необоснованным тратам времени и денег.
Выводы по обслуживанию
Практическими примерами в статье мы пытались обосновать важность обслуживания баз данных, предостеречь Вас от избыточных и неэффективных мероприятий по решению проблем производительности.
В качестве инструмента по проведению анализа проблем производительности рекомендуем использовать программный комплекс для мониторинга производительности информационных систем для CE
Be the first to comment on "Важность обслуживания базы данных"