IBAnalyst: понимание вашей базы данных

Дмитрий Кузьменко, [email protected], последнее обновление 31 марта 2014 года

Я работаю с InterBase с 1994 года. Тогда большинство баз данных были небольшими и не требовали какой-либо настройки. Конечно, бывали случаи, когда мне приходилось изменять ibconfig на сервере и перенастраивать оборудование или ОС, но это было почти всё, что я мог сделать для настройки производительности.

Четыре года назад наша компания начала предоставлять техническую поддержку и обучение пользователей InterBase. Работа со многими производственными базами данных также научила меня многим разным вещам. Однако большая часть того, что я узнал, касалась приложений - использования параметров транзакций, оптимизации запросов и наборов результатов.

Конечно, я уже довольно давно знал о gstat - инструменте, который предоставляет информацию о статистике базы данных. Если вы когда-либо смотрели на вывод gstat или читали о нём в opguide.pdf, вы знаете, что статистический вывод выглядит просто как набор цифр и ничего больше. Хорошо, вы можете обнаружить информацию о фрагментации для конкретной таблицы или индекса, но какую ещё полезную информацию можно получить?

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

Долгая история, но результатом стало создание IBAnalyst. Несмотря на мой опыт, он до сих пор позволяет мне находить очень интересные вещи или проблемы с производительностью в различных базах данных.

Реальные системы имеют производительность во время выполнения, которая колеблется, как волна. Амплитуда таких «волн» может быть низкой или высокой, поэтому вы можете видеть, как производительность отличается ото дня ко дню (или от часа к часу). Фактическая производительность зависит от многих факторов, включая дизайн приложения, конфигурацию сервера, параллелизм транзакций, версионный мусор в базе данных и так далее. Чтобы узнать, что происходит в базе данных (как положительные, так и отрицательные аспекты производительности), вам следует, по крайней мере, время от времени заглядывать в статистику базы данных.

Давайте рассмотрим возможности IBAnalyst. IBAnalyst может получать статистику из gstat или Services API и составлять из неё отчёт, дающий вам полную информацию о базе данных, её таблицах и индексах. Он содержит встроенные предупреждения, доступные при просмотре статистики; также включает подсказки-комментарии и отчёты с рекомендациями.

Информация о базе данных

Рисунок 1 Сводка статистики базы данных

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

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

Как я уже говорил, необработанная статистика базы данных выглядит загадочно и её трудно интерпретировать. IBAnalyst выделяет любые потенциальные проблемы чётко жёлтым или красным цветом, и детали проблемы можно прочитать, просто наведя курсор на соответствующую запись и прочитав отображаемую подсказку.

Что мы можем узнать из приведённого выше рисунка? Это база данных диалекта 3 с размером страницы 4096 байт. Шесть-восемь лет назад разработчики использовали размер страницы по умолчанию 1024 байта, но в более позднее время такой маленький размер страницы может привести ко многим проблемам с производительностью. Поскольку эта база данных имеет размер страницы 4k, предупреждение не отображается, так как этот размер страницы приемлем.

Далее мы видим, что параметр Forced Write установлен в OFF и отмечен красным. InterBase 4.x и 5.x по умолчанию имели этот параметр включённым. Forced Writes сам по себе является методом кэша записи: когда он включён, изменённые данные записываются немедленно на диск, но когда выключен, это означает, что записи будут храниться в течение неизвестного времени операционной системой в её файловом кэше. InterBase 6 создаёт базы данных с Forced Writes OFF.

Почему это отмечено красным в отчёте IBAnalyst? Ответ прост - использование асинхронных записей может привести к повреждению базы данных в случаях сбоя питания, ОС или сервера.

Совет: Интересно, что современные интерфейсы жёстких дисков (ATA, SATA, SCSI) не показывают существенной разницы в производительности при Forced Write, установленном в On или Off(1).

Далее в отчёте идёт загадочный «интервал подметания» (sweep interval). Если он положительный, он устанавливает размер разрыва между самой старой (2) и самой старой транзакцией снимка, при котором движок получает сигнал о необходимости начать автоматическую сборку мусора. В некоторых системах достижение этого порога вызовет эффект «внезапной потери производительности», и в результате иногда рекомендуется устанавливать интервал подметания в 0 (полностью отключая автоматическое подметание). Здесь интервал подметания отмечен жёлтым, потому что значение разрыва подметания отрицательное, что может быть в статистике InterBase 6.0, Firebird и Yaffil, но не в InterBase 7.x. Когда значение разрыва подметания больше, чем интервал подметания (если интервал подметания не равен 0), запись отчёта для интервала подметания будет отмечена красным с соответствующей подсказкой.

Мы рассмотрим следующие 8 строк как группу, так как все они отображают аспекты состояния транзакций базы данных:

Самая старая транзакция - это самая старая незавершённая транзакция. Любые номера транзакций ниже относятся к завершённым транзакциям, и для таких транзакций нет доступных версий записей. Номера транзакций выше самой старой транзакции относятся к транзакциям, которые могут находиться в любом состоянии. Это также называется «самой старой интересной транзакцией», потому что она замораживается, когда транзакция завершается откатом, и сервер не может отменить её изменения в этот момент.
Самый старый снимок - самая старая активная (т.е. ещё не завершённая) транзакция, которая существовала на момент начала транзакции, которая в настоящее время является самой старой «интересной» транзакцией. Указывает наименьший номер транзакции снимка, которая заинтересована в версиях записей.
Самая старая активная - самая старая текущая активная транзакция (3).
Следующая транзакция - номер транзакции, который будет присвоен новой транзакции.
Активные транзакции - IBAnalyst выдаст предупреждение, если номер самой старой активной транзакции на 30% ниже, чем ежедневное количество транзакций. Статистика не сообщает, есть ли какие-либо другие активные транзакции между самой старой активной и следующей транзакцией, но такие транзакции могут быть. Обычно, если самая старая активная застревает, есть две возможные причины: а) какая-то транзакция активна в течение длительного времени, или б) дизайн приложения позволяет транзакциям выполняться в течение длительного времени. Обе причины препятствуют сборке мусора и потребляют ресурсы сервера.
Транзакций в день - это вычисляется из следующей транзакции, разделённой на количество дней, прошедших с момента создания базы данных до момента получения статистики. Это может быть корректно только для производственных баз данных или для баз данных, которые периодически восстанавливаются из резервной копии, что приводит к сбросу нумерации транзакций.

Как вы уже узнали, если есть какие-либо предупреждения, они отображаются в виде цветных строк с чёткими описательными подсказками о том, как исправить или предотвратить проблему.

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

Не собирайте статистику, если вы:

Только что восстановили свою базу данных
Выполнен бэкап (gbak -b db.gdb) без ключа -g
Недавно выполнен ручной sweep (gfix -sweep)

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

Как понять, что с базой данных что-то не так?

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

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

Информация о таблицах

Давайте посмотрим на другой пример вывода из IBAnalyst.

![](/images/article_IBAnalyst (1).jpg)

Рисунок 2 Статистика таблиц

Представление статистики таблиц в IBAnalyst также очень полезно. Оно может показать, в каких таблицах много версий записей, где было выполнено большое количество обновлений/удалений, фрагментированные таблицы, с фрагментацией, вызванной обновлением/удалением или BLOB-полями, и так далее. Вы можете увидеть, какие таблицы часто обновляются, и каков размер таблицы в мегабайтах. Большинство этих предупреждений настраиваются.

В этом примере базы данных есть несколько проблем. Прежде всего, желтый цвет в столбце VerLen предупреждает, что пространство, занимаемое версиями записей, больше, чем пространство, занимаемое самими записями. Это может быть результатом обновления большого количества полей в записи или массовых удалений. Смотрите строки, в которых столбец MaxVers отмечен синим цветом. Это показывает, что хранится только одна версия на запись, и, следовательно, проблема связана с массовыми удалениями. Значение в столбце Versions показывает, сколько записей было удалено.

Долгоживущие активные транзакции, препятствующие сборке мусора, являются основной причиной деградации производительности. Для некоторых таблиц может быть много версий, которые все еще «используются». Сервер не может решить, действительно ли они используются, потому что активные транзакции потенциально могут нуждаться в любой одной или всех этих версиях. Соответственно, сервер не считает эти версии мусором, и для построения корректной записи из множества версий требуется все больше и больше времени, когда транзакция пытается ее прочитать. На рисунке 2 вы можете видеть две таблицы, в которых количество версий в три раза превышает количество записей. Используя эту информацию, вы также можете проверить, является ли тот факт, что ваши приложения так часто обновляют эти таблицы, предусмотренным проектом или следствием ошибки.

Представление индексов

Индексы используются ядром базы данных для обеспечения ограничений первичного ключа, внешнего ключа и уникальности. Они также ускоряют извлечение данных. Уникальные индексы лучше всего подходят для извлечения данных, но степень пользы от неуникальных индексов зависит от разнообразия индексируемых данных.

Например, посмотрите на ADDR_ADDRESS_IDX6. Прежде всего, само имя индекса предполагает, что он был создан вручную. Если статистика была получена через Services API с метаданными, вы можете увидеть, какие столбцы индексируются (в IBAnalyst 1.83 и выше). Для рассматриваемого индекса видно, что он имеет 34999 ключей, TotalDup равен 34995, а MaxDup равен 25056. Оба столбца дубликатов отмечены красным. Это связано с тем, что среди всех ключей этого индекса есть только 4 уникальных значения ключа, как видно из столбца Uniques. Кроме того, наибольшая цепочка дубликатов (ключ, указывающий на записи с одинаковым значением столбца) составляет 25056 - то есть почти все ключи хранят одно из четырех уникальных значений. В результате этот индекс может:

Снижать скорость процесса восстановления. Хорошо, тридцать пять тысяч ключей - это не проблема для современных баз данных и оборудования, но влияние все равно стоит отметить.
Замедлять сборку мусора. Индексы с низким количеством уникальных значений могут замедлять сборку мусора до десяти раз по сравнению с полностью уникальным индексом. Эта проблема была решена в InterBase 7.1/7.5 и Firebird 2.0.
Вызывать лишние чтения страниц, когда оптимизатор читает индекс. Это зависит от значения, которое ищется в конкретном запросе - поиск по индексу с большим значением MaxDup будет медленнее. Поиск по значению в столбце с меньшим количеством дубликатов будет быстрее, но только вы знаете, что столбец индексирован.

Именно поэтому IBAnalyst обращает ваше внимание на такие индексы, помечая их красным и желтым цветом и включая в отчет «Рекомендации». К сожалению, большинство «плохих» индексов создаются автоматически для обеспечения ограничений внешнего ключа. В некоторых случаях эту проблему можно решить, предотвращая с помощью триггеров удаления или обновления первичного ключа в справочных таблицах. Но если реализовать такие изменения невозможно, IBAnalyst будет показывать вам «плохие» индексы на внешних ключах каждый раз при просмотре статистики.

Отчеты

Нет необходимости каждый раз просматривать весь отчет, выискивая цвет ячеек и читая подсказки для новых предупреждений. Более прямую и подробную информацию можно получить с помощью функции «Рекомендации» в IBAnalyst. Просто загрузите статистику и перейдите в меню Reports/View Recommendations. Этот отчет предоставляет пошаговый анализ, включая более подробные описательные предупреждения о принудительной записи, интервале sweep, активности базы данных, состоянии транзакций, размере страницы базы данных, sweep, страницах инвентаризации транзакций, фрагментированных таблицах, таблицах с большим количеством версий записей, массовых удалениях/обновлениях, глубоких индексах, индексах, недружелюбных к оптимизатору, бесполезных индексах и даже пустых таблицах. Вся эта информация и сопутствующие предложения динамически создаются на основе загруженной статистики.

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

«Общий размер страниц инвентаризации транзакций (TIP) велик - 94 килобайта или 23 страницы. Транзакция Read_committed использует глобальный TIP, но транзакции snapshot создают собственные копии TIP в памяти. Большой размер TIP может замедлить производительность. Попробуйте выполнить sweep вручную (gfix -sweep), чтобы уменьшить размер TIP».

Вот еще одна цитата из части отчета о таблицах/индексах:

«Количество версионированных таблиц: 8. Большое количество версий записей обычно замедляет производительность. Если в таблице много версий записей, значит, сборка мусора не работает, или записи не читаются ни одним оператором select. Вы можете попробовать выполнить select count(*) по этим таблицам, чтобы принудительно запустить сборку мусора, но это может занять много времени (если много версий и существуют неуникальные индексы) и может быть безуспешным, если есть хотя бы одна транзакция, заинтересованная в этих версиях.

Вот список таблиц с соотношением версий/записей больше 3:


Таблица	Записи	Версии	Размер версий/записей
CLIENTS_PR	3388	10944	92%
DICT_PRICE	30	1992	45%
DOCS	9	2225	64%
N_PART	13835	72594	83%
REGISTR_NC	241	4085	56%
SKL_NC	1640	7736	170%
STAT_QUICK	17649	85062	110%
UO_LOCK	283	8490	144%

Резюме

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

1 InterBase 7.5 и Firebird 1.5 имеют специальные функции, которые могут периодически сбрасывать несохраненные страницы, если параметр Forced Writes выключен.

2 Самая старая транзакция - это та же самая самая старая интересная транзакция, упоминаемая повсюду. Вывод gstat не показывает эту транзакцию как «интересную».

3 Энн Харрисон говорит, что самая старая активная транзакция - это самая старая транзакция, которая была активна, когда началась текущая самая старая активная транзакция. Для приложений здесь нет большой разницы.