IBAnalyst: розуміння вашої бази даних

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

Я працюю з InterBase з 1994 року. Тоді більшість баз даних були невеликими і не потребували жодного налаштування. Звісно, бували випадки, коли мені доводилося змінювати ibconfig на сервері та переналаштовувати апаратне забезпечення або операційну систему, але це було майже все, що я міг зробити для налаштування продуктивності.

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

Звісно, я вже досить давно знав про gstat - інструмент, який надає інформацію про статистику бази даних. Якщо ви коли-небудь переглядали вивід gstat або читали про нього в opguide.pdf, ви знаєте, що статистичний вивід виглядає просто як набір чисел і нічого більше. Гаразд, ви можете виявити інформацію про фрагментацію для конкретної таблиці або індексу, але яку ще корисну інформацію можна отримати?

На щастя, до роботи з InterBase я цікавився різними структурами даних, тим, як вони зберігаються та які алгоритми використовують. Це допомогло мені інтерпретувати вивід gstat. Тоді я вирішив написати інструмент, який міг би аналізувати вивід gstat, щоб допомогти в налаштуванні бази даних або принаймні визначити причину проблем із продуктивністю.

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

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

Розгляньмо можливості IBAnalyst. IBAnalyst може отримувати статистику з gstat або Services API та компілювати її у звіт, який надає вам повну інформацію про базу даних, її таблиці та індекси. Він має вбудовані попередження, доступні під час перегляду статистики; також включає підказки-коментарі та звіти з рекомендаціями.

Інформація про базу даних

Рисунок 1 Підсумок статистики бази даних

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

Примітка: Усі рисунки в цій статті містять статистику gstat, отриману з реальної виробничої бази даних (з дозволу її власників).

Як я вже казав, сира статистика бази даних виглядає незрозумілою і її важко інтерпретувати. IBAnalyst чітко виділяє будь-які потенційні проблеми жовтим або червоним кольором, а деталі проблеми можна прочитати, просто навівши курсор на відповідний запис і прочитавши підказку, що відображається. Що ми можемо дізнатися з наведеного вище рисунка? Це база даних діалекту 3 із розміром сторінки 4096 байтів. Шість-вісім років тому розробники використовували розмір сторінки за замовчуванням 1024 байти, але в новіші часи такий малий розмір сторінки може призвести до багатьох проблем із продуктивністю. Оскільки ця база даних має розмір сторінки 4k, попередження не відображається, оскільки цей розмір сторінки є прийнятним.

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

Чому це позначено червоним у звіті IBAnalyst? Відповідь проста - використання асинхронних записів може спричинити пошкодження бази даних у випадках збою живлення, операційної системи або сервера.

Порада: Цікаво, що сучасні інтерфейси жорстких дисків (ATA, SATA, SCSI) не показують значної різниці в продуктивності при Forced Write увімкненому або вимкненому стані(1).

Далі у звіті йде загадковий «інтервал очищення» (sweep interval). Якщо він позитивний, він встановлює розмір розриву між найстарішою (2) та найстарішою транзакцією знімка, при якому рушій отримує сигнал про необхідність запуску автоматичного збору сміття. У деяких системах досягнення цього порогу спричиняє ефект «раптової втрати продуктивності», і тому іноді рекомендується встановлювати інтервал очищення на 0 (повне вимкнення автоматичного очищення). Тут інтервал очищення позначено жовтим, оскільки значення розриву очищення є від’ємним, що можливо в статистиці InterBase 6.0, Firebird і Yaffil, але не в InterBase 7.x. Коли значення розриву очищення більше за інтервал очищення (якщо інтервал очищення не дорівнює 0), запис про інтервал очищення буде позначено червоним із відповідною підказкою.

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

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

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

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

Не збирайте статистику, якщо ви:

Щойно відновили свою базу даних
Виконано резервне копіювання (gbak -b db.gdb) без ключа -g
Нещодавно виконано ручне очищення (gfix -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. Цей звіт надає покроковий аналіз, включаючи детальніші описові попередження про примусові записи, інтервал очищення, активність бази даних, стан транзакцій, розмір сторінки бази даних, очищення, сторінки інвентаризації транзакцій, фрагментовані таблиці, таблиці з великою кількістю версій записів, масові видалення/оновлення, глибокі індекси, неоптимальні для оптимізатора індекси, марні індекси та навіть порожні таблиці. Уся ця інформація та супутні пропозиції створюються динамічно на основі завантаженої статистики.

Як приклад виводу звіту, розгляньмо звіт, згенерований для статистики бази даних, яку ви бачили раніше в цій статті:

«Загальний розмір сторінок інвентаризації транзакцій (TIP) великий - 94 кілобайти або 23 сторінки. Транзакція Read_committed використовує глобальний TIP, але snapshot-транзакції створюють власні копії TIP у пам’яті. Великий розмір TIP може уповільнити продуктивність. Спробуйте виконати очищення вручну (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 Енн Гаррісон каже, що найстаріша активна транзакція - це транзакція, яка була активною, коли почалася поточна найстаріша активна транзакція. Для застосунків тут немає великої різниці.