Контакти
IBSurgeon
Цю сторінку перекладено машинним перекладом. Читайте англійський оригінал. English

Бібліотека IBSurgeon

IBAnalyst: поради та прийоми

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

Деякі питання, на які немає відповідей у Рекомендаціях IBAnalyst та/або Довідці:

1. Як перебудувати індекси для обмежень PRIMARY, FOREIGN або UNIQUE?

A: Для версій 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, але це не допомогло пришвидшити запити.

A: Це окрема проблема, з якою 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%, наприклад, у діалозі Опції IBAnalyst.

4. Версії записів для таблиці, яка не повинна оновлюватися

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

Отже, ви будете знати, скільки поточних записів у таблиці та скільки записів було видалено.

Це вірно лише якщо MaxVer = 1. Якщо він > 1, то ця таблиця оновлюється якимось застосунком. Якщо ви впевнені, що ця таблиця ніколи не повинна оновлюватися, краще встановити тригер “before update” з винятком, щоб знайти, який застосунок виконує оновлення.

5. Blobs можуть спричиняти фрагментацію таблиць.

Рушій зберігає blobs трьома різними способами:

  1. Якщо вміст blob поміщається на сторінці даних (достатньо вільного місця), він зберігається на цій сторінці даних поруч зі своїм записом (або версією).

  2. Якщо вміст blob не поміщається на сторінці даних, він зберігається на окремій сторінці.

  3. Якщо у випадку 2 blob не поміщається на одній сторінці даних, створюється сторінка-покажчик для посилання на відповідні сторінки blob.

Випадок 1 відбувається залежно від розміру збереженого blob та розміру сторінки бази даних. Наприклад, якщо у вас розмір сторінки 4K і blobs із середнім розміром ~5K, вони зберігаються не на сторінках даних, а на додаткових сторінках blob.

Але якщо ви створите резервну копію бази даних і відновите її з розміром сторінки 8K, blobs помістяться на сторінці даних, і вони будуть зберігатися разом із записами, що спричинить високу фрагментацію записів.

IBAnalyst позначає ці таблиці як Pale (стовпець Records), а підказка показує оціночну кількість записів для цієї таблиці (на основі кількості сторінок даних) та реальне середнє значення заповнення (%).

Якщо ваш запит читає будь-які поля, окрім blobs, з цієї таблиці, природне сканування, з’єднання або агрегація виконуватимуться дуже повільно.

Єдине рішення, щоб цього уникнути: створити додаткову таблицю (пов’язану 1-1 з оригінальною таблицею) і перенести всі стовпці blob, які мають середній розмір менший за розмір сторінки, до неї.

У цьому випадку не намагайтеся виконувати резервне копіювання/відновлення з більшим розміром сторінки! Це призведе до того, що blobs, які не могли поміститися на сторінках даних із поточним розміром сторінки, будуть розміщені на сторінках даних під час відновлення з більшим розміром сторінки. Отже, ваші таблиці з blobs будуть більш фрагментованими, ніж раніше.

Також не рекомендується відновлювати з меншим розміром сторінки, оскільки це може знизити продуктивність для індексів і таблиць без 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”

  • Колонка, що використовується як PK у головній таблиці, ніколи не буде змінена. Ви можете обмежити це тригером 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 видаляються.

У цьому випадку ми можемо бачити 2 правильні значення статистики для індексів таблиці A - коли вона завантажена даними, і коли вона порожня. Отже, статистика, переобчислена для завантаженої таблиці, буде марною, коли таблиця порожня, і навпаки.

Щоб уникнути цього, потрібно переобчислювати статистику для індексів таблиці A лише тоді, коли таблиця заповнена даними. Найкраще - перед виконанням запитів до цієї таблиці.

Починаючи з версії 1.91, IBAnalyst показує різницю статистики індексів і дозволяє переобчислити її в будь-який момент. Спочатку потрібно подивитися на інформацію про записи таблиці - чи це звичайна середня кількість записів, чи ні. Якщо так, ви можете сміливо переобчислити селективність індексу. Якщо ні - можливо, краще не чіпати статистику індексів, оскільки це може призвести до того, що оптимізатор створить ще гірші плани запитів.