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 - вони можуть краще масштабуватися, використовуючи всі ядра процесора.
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 сторінок. Однак не встановлюйте значення кешу сторінок буфера занадто високим - синхронізація кешу має свою ціну, і ідея розмістити всю базу даних в оперативній пам’яті шляхом налаштування цього значення не спрацює. Використовуйте попередньо оптимізовані файли конфігурації Firebird тут: /uk/optimized-firebird-configuration/
10. Збільште обсяг пам’яті для операцій сортування
Збільште значення параметра TempCacheLimit у firebird.conf - він визначає розмір кешу тимчасового простору для сортування. Значення за замовчуванням занадто низькі (8 Мб для Classic і 64 Мб для SuperServer), використовуйте щонайменше 64 Мб для Classic і 1 Гб для SuperServer і SuperClassic. Знову ж таки, використовуйте оптимізовані файли конфігурації з #9.
11. Вимкніть Forced Writes (з обережністю!)
Якщо у вас інтенсивна активність вставки або оновлення (ви можете перевірити це за допомогою HQbird MonLogger, деталі на сторінці 60 Посібника користувача HQbird), і якщо у вас встановлено ДБЖ та реплікацію для захисту від апаратних збоїв, розгляньте можливість встановити параметр 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 може використовувати різні ядра процесора для обробки запитів для певних баз даних.
14. Використовуйте швидкий диск для тимчасового простору
Встановіть першу частину параметра TempDirectory у firebird.conf на швидкий диск - SSD або RAM-диск. Це зменшить час великих сортувань - наприклад, коли база даних відновлюється.
15. Зберігайте резервні копії бази даних на іншому диску
Зберігайте резервні копії бази даних на виділеному фізичному диску (RAID). Це розділить ввід/вивід читання та запису під час резервного копіювання, збільшить швидкість резервного копіювання та зменшить навантаження на основний диск. Це особливо важливо, коли резервні копії створюються, поки користувачі працюють із базою даних. Більше деталей про апаратну конфігурацію для Firebird можна знайти в «Посібнику з апаратного забезпечення Firebird».
16. Деактивуйте індекси для масових вставок
Якщо ви вставляєте або оновлюєте багато записів (більше 25% таблиці), деактивуйте індекси для таблиці, куди вставляються записи, і реактивуйте їх після вставки або оновлення. Операція перебудови індексу може бути швидшою, ніж багато оновлень індексу.
17. Використовуйте глобальні тимчасові таблиці для швидких вставок
Щоб прискорити вставки та оновлення, використовуйте глобальні тимчасові таблиці для масових вставок великих наборів записів, а потім переносьте записи в постійну таблицю. Це може бути дуже ефективно: вставляти записи в 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
У разі масових операцій INSERT/UPDATE/DELETE не фіксуйте транзакцію після кожної зміни (це може статися, якщо ви використовуєте опцію автокоміту у вашому драйвері бази даних) - фіксуйте транзакції принаймні після 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 на з’єднання (joins).
27. Правильно використовуйте LEFT JOIN
Якщо ви використовуєте LEFT OUTER joins, явно розміщуйте таблиці в з’єднанні від найменшої до найбільшої.
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_KEY31. Зберігайте короткі рядки у VARCHAR, довгі - у BLOBs
Для зберігання коротких символьних даних використовуйте VARCHAR, для довгих текстів - BLOBs. VARCHAR швидші для невеликих фрагментів даних, оскільки вони зберігаються в записі, і весь запис читається під час того самого циклу IO, а якщо розмір запису менший за 2/3 розміру сторінки бази даних, весь запис зберігається на тій самій сторінці бази даних. BLOBs зберігаються поза записом і потребують додаткового циклу IO для читання, і вони показують перевагу при читанні та запису довгих рядків.
32. Виключайте BLOB-колонки з великих SELECT-запитів
Виключайте BLOB-колонки з великих SELECT-запитів. Використовуйте свого роду пізнє зв’язування з підзапитами для вибіркового відображення інформації з BLOBs (наприклад, показ вмісту документа).
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 employee41. Використовуйте перемикач -se для gbak
Використовуйте перемикач -se, щоб збільшити швидкість резервного копіювання та/або відновлення gbak до 20%, наприклад
gbak -b -g -se service_mgr c:\db\data.fdb e:\backup\data.fbk42. 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 для деталей).
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-автентифікацією встановлюється повільніше, ніж звичайне з’єднання.
Замість підсумку
Оптимізація продуктивності вимагає врахування багатьох факторів і може бути досить складною. Якщо ви спробували все вищезазначене, розгляньте можливість скористатися професійною послугою оптимізації продуктивності баз даних.
Зв’яжіться з нами
Маєте запитання? Не соромтеся зв’язатися з нами електронною поштою!