IBAnalyst: советы и хитрости

Этот текст был написан в 2012 году, он актуален для версий 1.0 - 2.5, в версиях 3.0-5.0 произошло много изменений, которые не могли быть отражены. Пожалуйста, прочитайте документацию или свяжитесь с нами для поддержки: [email protected].

Некоторые вопросы, на которые нет ответов в Рекомендациях IBAnalyst и/или Справке:

1. Как перестроить индексы для ограничений PRIMARY, FOREIGN или UNIQUE?

О: Для версий Firebird 1.0-2.5. Да, вы не можете использовать ALTER INDEX xxx INACTIVE/ACTIVE для индексов ограничений. Если вы видите глубокий или фрагментированный индекс на этом ограничении, вы можете использовать специальный трюк (используемый gbak при восстановлении):

RDB$INDICES имеет флаг RDB$INDEX_INACTIVE, который равен null или 0, если индекс активен (после CREATE INDEX или ALTER INDEX ACTIVE). 1 означает, что индекс неактивен (после ALTER INDEX INACTIVE). Но также существует значение 3, используемое для обозначения неактивных индексов на ограничениях. Итак, вы можете установить RDB$INDEX_INACTIVE=3 для этого индекса, выполнить COMMIT, а затем вернуть значение к 0 и снова выполнить commit - индекс будет перестроен.

Для Firebird 3.0-5.0 - просто выполните ALTER INDEX indexname ACTIVE

2. Я использовал все рекомендации IBAnalyst, но это не помогло ускорить запросы.

О: Это отдельная проблема, в которой IBAnalyst не может помочь. Здесь может быть 2 причины проблемы:

1. Индексы имеют устаревшую статистику. Вы можете обновить статистику индекса командой SET STATISTICS INDEX xxx (подробнее см. http://www.ibase.ru/proc_selectivity/).

2. Просто нет подходящего индекса для некоторого условия, используемого в запросе.

3. Запросы очень сложные, или оптимизатор не может оптимизировать запрос, поэтому необходимо рефакторить запрос.

4. В некоторых случаях вы увидите «фрагментированные таблицы» сразу после восстановления.

Обычно Firebird и InterBase (без параметра -use_all_space) резервируют около 25% места на страницах данных для будущих вставок, обновлений или удалений (для размещения версий записей). Но при любом размере страницы базы данных (1, 2, 4 или 8 КБ) вы увидите ~50% фрагментации для таблиц с маленьким размером записи (около ~12-20 байт, например, таблица с 2 целочисленными полями имеет средний размер записи = 12 байт).

Это нормально, считайте это неким магическим числом сервера (или поведением).

Итак, если у вас есть такие таблицы с маленькими записями, вы можете:

a) игнорировать предупреждение о «фрагментации» для этих таблиц

b) снизить «фрагментацию %» до 45%, например, в диалоге Options в IBAnalyst.

4. Версии записей для таблицы, которая не должна обновляться

Если вы видите версии записей в таблице, которая не должна обновляться (например, таблица с журналом событий) - не беспокойтесь, эти версии создаются при удалении.

Таким образом, вы узнаете, сколько текущих записей в таблице и сколько записей было удалено.

Это верно только если MaxVer = 1. Если он > 1, значит, эта таблица обновляется каким-то приложением. Если вы действительно уверены, что эта таблица никогда не должна обновляться, лучше установить триггер «before update» с исключением, чтобы найти, какое приложение выполняет обновления.

5. Blob-поля могут вызывать фрагментацию таблиц.

Движок хранит blob-поля тремя разными способами:

1. Если содержимое blob помещается на странице данных (достаточно свободного места), оно будет храниться на этой странице данных рядом со своей записью (или версией).

2. Если содержимое blob не помещается на странице данных, оно будет храниться на отдельной странице.

3. Если в случае 2 blob не помещается на одной странице данных, создается страница указателей для ссылок на соответствующие страницы blob.

Случай 1 происходит в зависимости от размера хранимого blob и размера страницы базы данных. Например, если у вас был размер страницы 4 КБ и blob-поля со средним размером ~5 КБ, они хранятся не на страницах данных, а на дополнительных страницах blob.

Но если вы сделаете резервную копию базы данных и восстановите её с размером страницы 8 КБ, blob-поля поместятся на странице данных, и они будут храниться вместе с записями, вызывая высокую фрагментацию записей.

IBAnalyst помечает такие таблицы как Pale (колонка Records), и подсказка показывает оценочное количество записей для этой таблицы (на основе количества страниц данных) и реальное среднее значение заполнения (%).

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

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

В этом случае не пытайтесь выполнять резервное копирование/восстановление с большим размером страницы! Это приведет к тому, что blob-поля, которые не помещались на страницах данных при текущем размере страницы, будут размещены на страницах данных при восстановлении с большим размером страницы. Таким образом, ваши таблицы с blob-полями будут фрагментированы больше, чем раньше.

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

Вы также не должны пытаться изменить blob-поля на varchar-поля - varchar-поля всегда хранятся как часть записи, поэтому запись может иметь 2 или более фрагментов (быть размещена на 2 или более страницах данных), если она не помещается на странице данных.

p.s. IBAnalyst может сообщать об этих таблицах «по ошибке», например, таблица имела blob-поля с данными, но они были удалены из структуры таблицы. К сожалению, нет настраиваемой опции для этого предупреждения, потому что мы вычисляем его точно из данных, сообщаемых сервером (статистика).

6. Связь между VerLen и RecLength

a) VerLen >= 90% от RecLength: версии, которые вы видите в колонке Version, в основном являются удалениями записей. Чем больше записей удалено, тем меньше будет RecLength (до 0 байт). Также VerLen может быть больше RecLen, если вы обновляете таблицу с большими строковыми данными, чем было в исходных записях.

b) VerLen <= 80% от RecLength: версии в основном являются обновлениями записей.

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

7. Почему IBAnalyst называет некоторые индексы «плохими»?

Индексы со значением селективности ниже 0.01 помечаются как «плохие» в IBAnalyst (см. справку представления Index). Есть несколько причин, по которым конкретный индекс называется плохим:

1. Селективность этого индекса ниже 0.01. Теоретически оптимизатор не должен использовать этот индекс, но он использует его, если нет других индексов (для where, order by или join, по крайней мере).

2. Такой индекс вызывает очень медленную сборку мусора. Эта проблема не существует в InterBase 7.1/7.5 и будет исправлена в Firebird 2.0.

3. Этот индекс делает процесс восстановления очень медленным, и он создается очень медленно (create/alter index active). Это связано с тем, что цепочка номеров записей велика для одного ключа индекса.

4. Если этот индекс используется в where, использование памяти будет зависеть от искомого значения (размер битовой маски). Поскольку цепочка записей может быть большой (много дубликатов ключей), потребление памяти также будет большим.

5. Если этот индекс используется в «order by», и много дубликатов в основном в нижних значениях ключа (в зависимости от порядка сортировки индекса), будет много чтений страниц индекса, что замедлит запрос.

Это связано с тем, что IBAnalyst не может игнорировать существование таких индексов.

Худший случай для индекса - когда колонка Uniques = 1, т.е. все значения для индексированной колонки одинаковы. Эти индексы перечислены в «Бесполезные индексы» на странице Summary.

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

8. Что если «плохой» индекс создан ограничением Foreign Key?

Что ж, предыдущий пункт показывает, что лучше удалить «плохие» индексы (если вы не используете их для поиска ключей с меньшим количеством дубликатов, чем другие ключи). Но если такой индекс создан внешним ключом, вы можете удалить его только удалив внешний ключ. Удаление внешнего ключа отключит проверку ограничения связи, что может быть неприемлемо.

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

• Записи не будут удаляться из мастер-таблицы, или удаление будет происходить в режиме «snapshot table reserving».

• Столбец, используемый первичным ключом в главной таблице, никогда не будет изменён. Вы можете ограничить это с помощью триггера BEFORE UPDATE.

Если вы будете соблюдать эти условия, вы можете удалить конкретный внешний ключ. Конечно, не создавайте индекс вручную на этом столбце.

9. Почему в строке процента данных только 12 мегабайт данных, а у меня база данных 140 мегабайт?

1. IBAnalyst здесь показывает «чистый» объём данных, без учёта других структур базы данных (индексов, метаданных…) и фрагментации страниц.

2. После восстановления InterBase и Firebird оставляют некоторое свободное пространство (15-25%) на страницах данных, чтобы ускорить будущие обновления/удаления.

3. Существует специфическое поведение сервера, когда он оставляет страницы данных фрагментированными примерно на 50%, если размер записи в таблице мал, около 11-22 байт.

10. Как улучшить производительность оптимизатора в случае частых обновлений

Статистика индексов хранится в столбце RDB$INDICES.RDB$STATISTICS и обновляется тремя способами:

1. SET STATISTICS INDEX

2. ALTER INDEX ACTIVE, или CREATE INDEX …

3. процесс восстановления (все индексы перестраиваются, а также «ALTER INDEX ACTIVE»)

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

Если статистика не обновлялась в течение длительного времени, оптимизатор может составить плохой план, потому что существующие значения статистики не соответствуют фактическому состоянию дел, так как данные в таблице могут значительно измениться (например, количество записей увеличилось в 5-10 раз, или наоборот, все записи были удалены).

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

Альтернативный (и правильный) способ - периодически обновлять статистику, применяя оператор SET STATISTICS для всех индексов. Вы можете запланировать выполнение SQL-скрипта для обновления статистики с помощью ISQL или готового инструмента gidx (только для Windows).

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

• Таблица A загружается данными 4-5 раз в день.
• После обработки загруженных данных все записи в таблице A удаляются.

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

Чтобы избежать этого, вам нужно пересчитывать статистику для индексов таблицы A только когда таблица заполнена данными. Лучше всего - перед выполнением запросов к этой таблице.

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