45 способов ускорить базу данных Firebird

Тут ви знайдете перелік порад щодо продуктивності бази даних Firebird у різних сферах — від налаштування апаратного забезпечення/ОС і конфігурації Firebird до рекомендацій з оптимізації SQL. Цей перелік не є повним довідником з оптимізації Firebird і передбачає, що ви розумієте основи функціонування Firebird, такі як плани виконання, керування транзакціями та статистика продуктивності запитів.

Застосовуйте ці поради обережно та перевіряйте їхній ефект, перш ніж впроваджувати у виробництво.

Наша компанія (IBSurgeon) пропонує комплексну послугу оптимізації продуктивності бази даних.

1. Розмістіть базу даних на SSD

Розмістіть вашу базу даних на SSD. SSD-накопичувач забезпечує набагато кращий випадковий ввід-вивід, ніж традиційні диски. Випадковий ввід-вивід є критичним для читання та запису даних, розподілених по великому файлу бази даних — більшість операцій з базою даних потребують інтенсивного паралельного випадкового вводу-виводу.

2. Використовуйте RAID 10

Якщо ви використовуєте RAID1 або RAID5, розгляньте RAID10 — він на 15-25% швидший.

3. Перевірте BBU

Якщо ви використовуєте RAID-контролер, перевірте, чи встановлено та чи працює в ньому блок резервного живлення (BBU) — деякі виробники не постачають BBU за замовчуванням. Без BBU контролер вимикає кеш, і RAID працює дуже повільно, навіть повільніше за звичайні SATA-диски. Зазвичай статус BBU можна перевірити в інструменті конфігурації RAID.

4. Встановіть кеш запису в режим write-back

Якщо ви використовуєте RAID-контролер із встановленим BBU (і сервер з ДБЖ), перевірте, чи його кеш встановлено в режим write-back (а не write-through). «Write-back» вмикає кеш запису контролера.

5. Увімкніть кеш читання

Якщо ви використовуєте RAID-контролер, перевірте, чи в ньому увімкнено кеш читання.

6. Перевірте дискову підсистему

Перевірте ваші диски на наявність поганих блоків та інших апаратних проблем (включно з перегрівом). Апаратні проблеми можуть значно знизити продуктивність вводу-виводу та призвести до пошкодження бази даних.

7. Використовуйте SuperClassic або Classic у Firebird 2.5

Якщо ви використовуєте Firebird 2.5 SuperServer з багатьма з’єднаннями, спробуйте використовувати SuperClassic або Classic — вони можуть краще масштабуватися, використовуючи всі ядра CPU.

8. Використовуйте SuperServer 3.0 у Firebird 3.

Якщо ви використовуєте Classic або SuperClassic у 2.5, розгляньте міграцію на Firebird 3.0 SuperServer — тепер він може використовувати кілька ядер і поєднувати це з перевагами спільного кешу.

9. Збільште кеш сторінкових буферів

Збільште розмір кешу сторінкових буферів (параметр DefaultDBCachePages) від значень за замовчуванням. Для 2.5 SuperServer ми рекомендуємо 10000 сторінок, для 3.0 SuperServer — 50000 сторінок, для Classic і SuperClassic — від 256 до 2048 сторінок. Однак не встановлюйте значення кешу сторінкових буферів занадто високим — синхронізація кешу має свою ціну, і ідея розмістити всю базу даних у RAM шляхом налаштування цього значення не спрацює. Використовуйте попередньо оптимізовані файли конфігурації Firebird тут: /uk/optimized-firebird-configuration/

10. Збільште розмір пам’яті для операцій сортування

Збільште значення параметра TempCacheLimit у firebird.conf — він визначає розмір кешу тимчасового простору для сортування. Значення за замовчуванням занадто низькі (8 Мб для Classic і 64 Мб для SuperServer), використовуйте щонайменше 64 Мб для Classic і 1 Гб для SuperServer та SuperClassic. Знову ж таки, використовуйте оптимізовані файли конфігурації з пункту #9.

11. Встановіть Forced Writes Off (з обережністю!)

Якщо у вас інтенсивна активність вставки або оновлення (ви можете перевірити це за допомогою HQbird MonLogger, деталі дивіться на сторінці 60 HQbird User Guide), і якщо у вас встановлено ДБЖ та реплікацію для захисту від апаратних збоїв, розгляньте можливість встановити Forced Writes у значення OFF — це може збільшити швидкість операцій запису до 3 разів.

12. Збільште кількість хеш-слотів для Classic/SuperClassic

Збільште значення параметра LockHashSlots для Classic і SuperClassic від значення за замовчуванням 1009 до деякого великого простого числа (наприклад, 30011) — це зменшить черги у внутрішньому механізмі блокування.

13. Використовуйте CPU Affinity для Super Server 2.5

Якщо ви використовуєте SuperServer 2.5, встановіть параметр CPUAffinity у значення, що дорівнює кількості баз даних у використанні: SuperServer у 2.5 може використовувати різні ядра CPU для обробки запитів до певних баз даних.

14. Використовуйте швидкий диск для тимчасового простору

Встановіть першу частину параметра TempDirectory у firebird.conf на швидкий диск — SSD або RAM-диск. Це зменшить час великих сортувань — наприклад, коли база даних відновлюється.

15. Зберігайте резервні копії бази даних на іншому диску

Зберігайте резервні копії бази даних на виділеному фізичному диску (RAID). Це розділить читання та запис вводу-виводу під час резервного копіювання, збільшить швидкість резервного копіювання та зменшить навантаження на основний диск. Це особливо важливо, коли резервні копії створюються, поки користувачі працюють з базою даних. Більше деталей про конфігурацію апаратного забезпечення для Firebird можна знайти в " Firebird Hardware Guide".

16. Деактивуйте індекси для масових вставок

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

17. Використовуйте Global Temporary Tables для швидких вставок

Щоб прискорити вставки та оновлення, використовуйте Global Temporary Tables для масових вставок великих наборів записів, а потім переносьте записи в постійну таблицю. Це може бути дуже ефективним — вставляти записи в GTT, попередньо обробляти їх, а потім переміщувати в постійну таблицю.

18. Уникайте непотрібних індексів

Використовуйте менше індексів для таблиць з інтенсивними вставками та оновленнями. Кожен індекс додає значні накладні витрати для операцій вставки, оновлення, видалення та збирання сміття — може бути 3-4 додаткових читання та записи сторінок, коли один запис вставляється/оновлюється/видаляється/очищується для кожного індексу.

19. Замініть UDF на виклики вбудованих функцій

Замініть виклики UDF на виклики вбудованих функцій. Багато вбудованих функцій було додано в останніх версіях Firebird, які пропонують функціональність, раніше доступну лише в бібліотеках UDF. Замініть такі функції, де це можливо, оскільки вбудовані функції працюють до 3 разів швидше, ніж UDF.

20. Використовуйте транзакції лише для читання для операцій читання

Використовуйте транзакції лише для читання для операцій, які не змінюють записи (тобто SELECT), з режимом ізоляції = read committed. Такі транзакції не утримують версії записів від збирання сміття і можуть працювати нескінченно: вони не впливають на продуктивність бази даних.

21. Використовуйте короткі транзакції запису та позбудьтеся ВСІХ довготривалих

Використовуйте короткі транзакції запису (для операцій INSERT/UPDATE/DELETE).

Чим коротша транзакція запису, тим краще. Короткі транзакції пропорційно утримують меншу кількість версій записів від збирання сміття, ніж довготривалі. На жаль, навіть єдина довготривала транзакція (наприклад, залишена відкритою інструментом розробки) може зіпсувати добрий ефект усіх інших коротких транзакцій запису. Ось чому вам потрібно відстежувати довготривалі транзакції та виправляти відповідні місця у вихідному коді. Використовуйте інструмент HQbird DataGuard, щоб отримувати сповіщення про найстарішу активну транзакцію в базі даних Firebird (які застосунки її запустили, яка IP-адреса, часова мітка її початку), та інструмент HQbird MonLogger, щоб побачити повний список довготривалих активних транзакцій та їхню статистику вводу-виводу. Також, якщо ви використовуєте компоненти/бібліотеки доступу до бази даних, які можуть кешувати набори записів, використовуйте кешовані оновлення.

22. Уникайте довгих ланцюжків записів

Уникайте ситуацій, коли один запис має багато версій записів — Firebird працює набагато повільніше з довгими ланцюжками записів. (щоб побачити, скільки версій записів мають деякі таблиці та який найдовший ланцюжок записів, ви можете використовувати інструмент HQbird IBAnalyst, вкладка Tables, сортування за “Max Version”). Використовуйте комбінацію вставок і запланованого видалення старих записів замість багаторазових оновлень одного й того самого запису.

23. Правильно використовуйте PREPARE

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

24. Не робіть COMMIT занадто часто під час масових операцій вставки/оновлення

У разі масових операцій INSERT/UPDATE/DELETE не фіксуйте транзакцію після кожної зміни (це може статися, якщо ви використовуєте опцію auto commit у вашому драйвері бази даних) — фіксуйте транзакції щонайменше після 1000 операцій або більше. Кожна фіксація транзакції виконує кілька операцій читання/запису IO щодо бази даних, тому часті фіксації знижують продуктивність бази даних.

25. «Вимкніть» індекси, якщо ви використовуєте IN з багатьма константами

Якщо ви використовуєте конструкцію WHERE fieldX IN (Constant1, Constant2,… ConstantN), і на fieldX є індекс, Firebird використовуватиме індекс стільки разів, скільки констант є у списку IN. Вимкніть пошук за індексом, перетворивши fieldX на вираз +0: WHERE fieldX+0 IN (Constant1, Constant2,… ConstantN), або для рядків використовуйте fieldX||''

26. Замініть IN на JOIN

Уникайте запитів із вкладеними WHERE IN(SELECT… WHERE IN (SELECT.. WHERE IN() )), це може заплутати оптимізатор Firebird. Перетворюйте вкладені IN на з’єднання.

27. Використовуйте LEFT JOIN правильно

Якщо ви використовуєте LEFT OUTER з’єднання, явно розташовуйте таблиці у з’єднанні від найменшої до найбільшої.

28. Обмежуйте вибірку SELECT-запитів

Завжди намагайтеся обмежити великий вивід для SELECT-запитів за допомогою конструкцій FIRST… SKIP або ROWS. Якщо запит не призначений спеціально як звіт (який вимагає виведення/експорту всіх записів), зазвичай достатньо показати перші 10-100 записів. Виберіть лише необхідні записи.

29. Вказуйте меншу кількість стовпців у SELECT з ORDER BY/GROUP BY

Зменшуйте кількість стовпців та їхню сумарну ширину в запитах з ORDER BY/GROUP BY як у частині SELECT (тобто поля, які потрібно показати), так і в реченні ORDER BY. Firebird об’єднує стовпці з речень SELECT та ORDER BY/GROUP BY і сортує їх у пам’яті (або, якщо пам’яті недостатньо, на диску). Тому, якщо в SELECT є довгий VARCHAR, розмір файлів сортування може бути справді великим (багато гігабайтів). Зменшення кількості полів лише до тих, які потрібно сортувати, та пізнє з’єднання з великими полями, які потрібно показати, може значно (у 3-10 разів) підвищити швидкість запиту з ORDER BY/GROUP BY.

30. Використовуйте похідні таблиці для оптимізації SELECT з ORDER BY/GROUP BY

Інший спосіб оптимізувати SQL-запит із сортуванням — використовувати похідні таблиці, щоб уникнути непотрібних операцій сортування. Замість

SELECT FIELD_KEY, FIELD1, FIELD2, ... FIELD_NFROM TORDER BY FIELD2

використовуйте таку модифікацію:

SELECT T.FIELD_KEY, T.FIELD1, T.FIELD2, ... T.FIELD_NFROM (SELECT FIELD_KEY FROM T ORDER BY FIELD2) T2JOIN T ON T.FIELD_KEY = T2.FIELD_KEY

31. Зберігайте короткі рядки у VARCHAR, великі — у BLOB

Для зберігання коротких символьних даних використовуйте VARCHAR, для зберігання довгих текстів — BLOB. Varchar швидші для невеликих фрагментів даних, оскільки вони зберігаються в записі, і весь запис читається за один цикл IO, а якщо розмір запису менший за 2/3 розміру сторінки бази даних, весь запис зберігається на тій самій сторінці бази даних. BLOB зберігаються поза записом і вимагають додаткового циклу IO для читання, і вони показують перевагу при читанні та записі довгих рядків.

32. Виключайте стовпці BLOB з великих SELECT-запитів

Виключайте стовпці BLOB з великих SELECT-запитів. Використовуйте свого роду відкладене зв’язування з підзапитами, щоб вибірково показувати інформацію з BLOB (наприклад, показувати вміст документа).

33. Використовуйте BIGINT для первинних і унікальних ключів

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

34. Не використовуйте VARCHAR для ключів

Не використовуйте VARCHAR для ідентифікаторів, якщо це справді не потрібно — операції з ними набагато менш ефективні, ніж з цілочисельними стовпцями. Особливо уникайте GUID як ідентифікаторів — через випадковий розподіл значень GUID операції INSERT/UPDATE з первинними/унікальними ключами GUID можуть бути у 20 разів повільнішими, ніж з цілими числами.

35. Перераховуйте статистику індексів

Регулярно перераховуйте статистику індексів. Оновлюйте статистику індексів для таблиць із частими або масовими змінами командою SET STATISTICS, це дозволяє оптимізатору Firebird вибирати кращі SQL-плани. HQbird Firebird DataGuard може виконувати таке перерахування статистики індексів автоматично за бажаним розкладом (зазвичай раз на тиждень).

36. Використовуйте пул з’єднань

Якщо з’єднання з базою даних Firebird є короткими (це типово для веб-сайтів), використовуйте пул з’єднань — наприклад, у PHP використовуйте функцію ibase_pconnect замість ibase_connect

37. Використовуйте опцію LINGER у Firebird 3.0

Якщо з’єднання з базою даних є короткими і ви використовуєте Firebird 3+, використовуйте опцію LINGER, щоб підтримувати кеш активним протягом заданого часу, це збереже часто використовувані сторінки в кеші, навіть якщо не буде інших з’єднань. Наприклад, ALTER DATABASE SET LINGER TO 60 збереже кеш протягом 60 секунд після завершення останнього з’єднання.

38. Використовуйте HASH JOIN

У Firebird 3.0 у разі з’єднання великих і малих таблиць HASH JOIN може бути набагато швидшим за звичайне з’єднання, яке використовує «вкладений цикл» з індексом. Щоб змусити оптимізатор Firebird використовувати HASH join, використовуйте +0 в умові з’єднання: T1 JOIN T2 ON T1.FIELD1+0 = T2.FIELD2+0. Перевірте результат оптимізації перед впровадженням у виробництво!

39. Позначайте відповідні PSQL-функції як DETERMINISTIC

Позначайте ваші PSQL-функції (у Firebird 3+), які не мають параметрів і повертають постійні значення, ключовим словом DETERMINISTIC. Детерміновані функції обчислюються та кешуються в межах поточного запиту.

40. Використовуйте аналітичні (віконні) функції у Firebird 3.0

Якщо ви виконуєте SELECT з одночасним виведенням деякого стовпця та агрегатної функції для нього, використовуйте віконні (аналітичні) функції — це швидше, ніж підзапит або 2 запити. Наприклад:

Select id, department, salary, salary / (select sum(salary) from employee) percentagefrom employee

замініть на

Select id, department, salary, salary / sum(salary) OVER () percentage from employee

41. Використовуйте перемикач -se для gbak

Використовуйте перемикач -se, щоб підвищити швидкість резервного копіювання та/або відновлення gbak до 20%, наприклад

gbak -b -g -se service_mgr c:\db\data.fdb e:\backup\data.fbk

42. WHERE CURRENT OF

Найшвидший спосіб обробити записи, вибрані курсором у PSQL, — це речення ‘where current of <>’. Воно швидше за ‘where rb$db_key = :v_db_key’ і набагато швидше за пошук за первинним або унікальним ключем.

43. Уникайте частих запитів до таблиць моніторингу

Не виконуйте запити до таблиць моніторингу Firebird (MON$) занадто часто — такі запити споживають значні ресурси і можуть сильно знизити продуктивність основної бізнес-логіки. Ми рекомендуємо виконувати запити MON$ не частіше ніж раз на хвилину. Для безперервного моніторингу запитів/транзакцій/з’єднань Firebird використовуйте інструмент HQbird PerfMon, який підтримує Trace API (див. сторінку 66 HQbird User Guide для деталей).

44. Використовуйте опцію NO_AUTO_UNDO для масових вставок/оновлень

Якщо ви виконуєте багато команд DML (Update/Insert/Delete) у межах однієї транзакції, Firebird об’єднує undo-лог кожної команди з undo-логом транзакції. Щоб прискорити масові операції DML, запускайте транзакцію з опцією «NO AUTO UNDO», щоб не об’єднувати undo-логи кожної команди з undo-логом транзакції.

45. Не використовуйте автентифікацію SRP у Firebird 3, якщо вона вам не потрібна

Не використовуйте автентифікацію користувачів SRP (Firebird 3.0+), якщо вона вам справді не потрібна — з’єднання з автентифікацією SRP встановлюється повільніше, ніж звичайне з’єднання.

Замість підсумку

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

Зв’яжіться з нами

Маєте запитання? Не соромтеся зв’язатися з нами електронною поштою email!