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 (с осторожностью!)

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

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 соединения, явно размещайте таблицы в соединении от наименьшей к наибольшей.

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

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-аутентификацией устанавливается медленнее, чем обычное соединение.

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

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

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

У вас есть вопросы? Не стесняйтесь связаться с нами по электронной почте!