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 здесь: /ru/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 может использовать различные ядра процессора для обработки запросов к определенным базам данных.

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 перед циклом таких запросов. Prepare может занимать значительное время (особенно для больших таблиц), и подготовка запроса только один раз значительно повысит общую производительность.

24. Не выполняйте COMMIT слишком часто при массовой вставке/обновлении

В случае массовых операций INSERT/UPDATE/DELETE не фиксируйте транзакцию после каждого изменения (это может происходить, если вы используете опцию auto commit в драйвере базы данных) — фиксируйте транзакции как минимум после 1000 операций или более. Каждый commit транзакции выполняет несколько операций чтения/записи ввода-вывода с базой данных, поэтому частые commit снижают производительность базы данных.

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 быстрее для небольших фрагментов данных, потому что они хранятся в записи, и вся запись читается за один цикл ввода-вывода, а если размер записи меньше 2/3 размера страницы базы данных, вся запись хранится на одной странице базы данных. BLOB хранятся вне записи и требуют дополнительного цикла ввода-вывода для чтения, и они показывают преимущество при чтении и записи длинных строк.

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 может быть намного быстрее обычного соединения, использующего «nested loop» с индексом. Чтобы заставить оптимизатор Firebird использовать HASH join, используйте +0 в условии соединения: T1 JOIN T2 ON T1.FIELD1+0 = T2.FIELD2+0. Проверьте результат оптимизации перед выводом в production!

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-log каждой команды с undo-log транзакции. Чтобы ускорить массовые операции DML, запускайте транзакцию с опцией «NO AUTO UNDO», чтобы не объединять undo-log каждой команды с undo-log транзакции.

45. Не используйте аутентификацию SRP в Firebird 3, если она вам не нужна

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

Вместо резюме

Оптимизация производительности требует учёта множества факторов и может оказаться действительно непростой задачей. Если вы испробовали всё вышеперечисленное, рассмотрите возможность воспользоваться профессиональной услугой оптимизации производительности баз данных.

Свяжитесь с нами

Есть вопросы? Не стесняйтесь обращаться к нам по электронной почте!