En45 способов улучшить производительность Firebird
В этой статье собраны 45 достаточно простых способов улучшить производительность Firebird, начиная от рекомендаций по железу, конфигурации Firebird, и заканчивая советами по оптимизации SQL. Если они не помогают, самый главный совет читайте внизу.
Используйте такой вариант:
Еще пример, показывает сумму кредита, накопленную сумму выплат и остаток по выплатам: для ускорения backup и restore как минимум на 20%. Например,
’. Это быстрее чем ‘where rb$db_key = :v_db_key’ и быстрее чем поиск по первичному или уникальному ключу.
Также, наша компания предлагает услуги по оптимизации производительности СУБД Firebird в рамках технической поддержки для предприятий. Наши специалисты помогут решить любую проблему с Firebird, включая разработку специальных патчей и версий.
1. Поместите базу данных на SSD
SSD обеспечивает более высокую скорость произвольного ввода-вывода, чем обычные жесткие диски. Работа с БД в основном состоит из большого количества операций чтения-записи в разных местах файла БД, и поэтому SSD дают существенный выигрыш в производительности баз данных.2. Используйте RAID10
Если вы используете массивы RAID1 или RAID5, подумайте о переходе на RAID10 – такой массив будет на 15-25% быстрее.3. Проверьте BBU
Если вы используете RAID, проверьте наличие и работоспособность Backup Battery Unit (BBU) – некоторые производители не поставляют BBU по умолчанию, или помещают батарею в отдельную упаковку, а отсутствующая батарея чаще всего полностью отключает кэш контроллера, отчего он работает очень медленно, даже медленнее, чем обычные SATA-диски. Для проверки состояния BBU необходимо воспользоваться утилитой конфигурирования RAID (также может устанавливаться отдельно).4. Установите write-back в кэше на запись
Если вы используете RAID с BBU (и сервер с UPS), проверьте настройку типа кэша на запись – должен быть write back, не write-through, иначе при записи кэш контроллера не будет использоваться.5. Включите кэш на чтение
Если вы используете RAID, убедитесь, что кэш на чтение включен, и составляет не менее 75% (обычно для БД).6. Проверьте дисковую подсистему
Проверяйте ваши диски (или логи системы или RAID) на плохие блоки и другие проблемы (включая перегрев). Подобные проблемы серьезно ухудшают производительность и приводят к повреждениям БД.7. Используйте SuperClassic или Classic версии Firebird 2.5
Если вы используете Firebird 2.5 SuperServer с большим количеством соединений и многоядерным процессором, производительность может оказаться недостаточной, т.к. SuperServer в версии 2.5 использует только 1 ядро процессора для работы с одной базой данных (SuperServer 3.0 и выше отлично распараллеливается по ядрам). SuperClassic или Classic версии 2.5 масштабируются лучше, чем SuperServer (однако с версии 3.0 архитектуры SuperClassic и Classic уже не так актуальны).8. Используйте SuperServer версии Firebird 3.0
Если вы для версии 2.5 использовали Classic или SuperClassic, запланируйте переход на Firebird 3.0 SuperServer – теперь эта архитектура может использовать много ядер и общий кэш БД, и по производительности быстрее Classic или SuperClassic.9. Увеличьте размер кэша
Увеличьте в firebird.conf значение параметра DefaultDBCachePages – значений по умолчанию обычно недостаточно. Для 2.5 SuperServer мы рекомендуем 10000 страниц, для 3.0 SuperServer – 50000 страниц, для Classic и SuperClassic 2.5 и 3.0 – от 256 до 2048 страниц. Однако, не увеличивайте размер кэша чрезмерно – синхронизация кэша имеет свою цену, и идея поместить всю базу данных в RAM при помощи увеличения этого параметра не сработает (операционная система и так будет кэшировать файл БД). Мы рекомендуем воспользоваться оптимизированными файлами конфигурации Firebird: http://ib-aid.com/en/optimized-firebird-configuration/.10. Увеличьте размер памяти для сортировок
Увеличьте параметр TempCacheLimit (по умолчанию 8Mb для Classic и 64Mb для SuperClassic и SuperServer). Установите как минимум 64Mb для Classic и 1Gb для SuperClassic и SuperServer.11. Установите Forced Writes OFF (может быть опасно!)
Если у вас высокая интенсивность запросов на вставку и обновление данных (можете проверить это утилитой HQbird MonLogger), и у вас есть UPS и репликация, установите режим Forced Writes у базы данных в OFF – это увеличит скорость операций записи до 3х раз.12. Увеличьте количество слотов в лок-таблице
Увеличьте значение параметра LockHashSlots в firebird.conf для Classic и SuperClassic со значения по умолчанию 1009 до большего простого числа (например, 30011), это уменьшит очереди внутреннего механизма блокировок.13. Установите правильный CPU Affinity
Если вы используете SuperServer 2.5, установите параметр CPUAffinity в значение, равное количеству разных используемых баз данных: SuperServer 2.5 может использовать разные ядра для обработки запросов к разным БД (например, 3 БД – 3 ядра, и т.д.). В firebird.conf параметр CPUAffinity должен быть указан как десятичное число из битовой маски (двоичного числа) используемых ядер, поэтому 2 ядра = 3, 3 ядра = 7, 4 ядра = 15, и т.д.14. Настройте папку для сортировок
Если у вас настроен параметр TempDirectory, то укажите в его начале наиболее быстрый диск – SSD или RAM drive. Это ускорит большие сортировки – например, при восстановлении БД из бэкапа.15. Используйте отдельный физический диск для бэкапов
Создавайте и храните бэкапы базы данных на отдельном физическом диске – это не только повысит надежность, но и разделит операции чтения и записи при создании резервной копии БД и ускорит процесс резервного копирования. Больше о правильной настройке дисковой подсистемы Вы можете прочитать в "Руководстве по аппаратному обеспечению для Firebird".16. Деактивируйте индексы при вставке
Если вы вставляете и обновляете много записей (более 25% таблицы), деактивируйте хотя бы часть индексов этой таблицы, и активируйте после окончания вставки или обновления. Вставка и обновление записей плюс активирование индекса в сумме пройдет быстрее, чем вставка и обновление при активном индексе.17. Используйте глобальные временные таблицы для быстрой вставки
Для ускорения вставки и обновления используйте временные таблицы (Global Temporary Table – GTT) для массовых вставок и последующего переноса данных в обычные таблицы.18. Избегайте излишних индексов
Не создавайте много индексов на таблицах, в которых часто вставляются и обновляются данные. Индекс замедляет вставку, обновление и удаление, а также сборку мусора – каждый индекс на этих операциях может добавлять 3-4 дополнительных чтения и записи страниц на одну добавленную/измененную строку в таблице.19. Замените вызовы UDF на встроенные функции
В последних версиях Firebird было добавлено много встроенных функций, которые ранее были доступны только в виде UDF (в дистрибутиве сервера или в виде сторонних). Замените функции UDF на встроенные везде, где это возможно, т. к. встроенные функции работают до 3х раз быстрее, чем UDF.20. Используйте транзакции только для чтения (read-only)
Используйте транзакции только для чтения (read-only) для операций, которые не меняют данные (например, SELECT), с уровнем изолированности read committed. Такие транзакции не удерживают ненужные версии от сборки мусора, и могут длиться долго без влияния на производительность.21. Используйте короткие пишущие транзакции (для операций INSERT/UPDATE/DELETE).
Чем короче пишущая транзакция, тем лучше – она не будет задерживать сборку мусора (приводить к накоплению версий и ухудшению производительности). Если вы используете компоненты или драйверы с кэшированием результата выборки, используйте кэшированные обновления (cached updates).22. Избегайте длинных пишущих транзакций
Избегайте длинных транзакций, вызывающих накопление версий – для обработки больших цепочек версий требуется больше времени, т.е. падает производительность (для поиска таблиц с большим количеством версий используйте инструмент HQbird IBAnalyst). Если возможно, используйте вставку и удаление вместо множественных обновлений одной и той же записи.23. Используйте prepare правильно
Используйте подготовленные (prepared) запросы, когда меняются только значения параметров. Например, если вы выполняете запрос с разными параметрами в цикле, сделайте prepare перед началом цикла. Prepare может занять длительное время (особенно на больших таблицах), поэтому его разовое выполнение увеличит общую производительность.24. Коммиттесь пореже
В случае массовых операций INSERT/UPDATE/DELETE, не завершайте транзакцию после каждой операции (это может произойти, если вы используете autocommit, или без явного управления стартом и завершением транзакций). Завершайте транзакцию не менее чем через 1000 таких операций. Завершение транзакции выполняет несколько операций чтения и записи в БД, поэтому слишком частые старт и завершение транзакций (на каждую запись) увеличивает нагрузку на БД и ухудшает производительность.25. Отключайте индексы в случае IN cо списком константам
Если вы используете конструкцию WHERE fieldX IN (Contant1, Constant2, Constan3,… ConstantN), и есть индекс по fieldX, Firebird будет использовать индекс столько раз, сколько констант упомянуто в списке IN. Отключите использование индекса превращением fieldX в выражение fieldX+0: WHERE fieldX+0 IN (Contant1, Constant2, Constan3,… ConstantN) (для строковых переменных – fieldX||’’).26. Замените IN на JOIN
Избегайте запросов с вложенными SELECT - WHERE IN( SELECT WHERE IN (SELECT WHERE IN() )). Перепишите IN на JOIN, такой запрос будет лучше оптимизироваться.27. Упорядочите внешние соединения
Если в вашем запросе есть несколько групп LEFT OUTER JOIN, старайтесь расположить их так, чтобы вначале были таблицы с меньшим количеством записей, а затем – с большим.28. Ограничьте фетч
Всегда старайтесь ограничить вывод запросов SELECT ограничителями FIRST… SKIP или ROWS. Если запрос не является «отчетным» (где требуется распечатать или сохранить все выводимые записи), то обычно имеет смысл показывать пользователю не более 10-100 записей.29. Уберите лишние столбцы
Уберите лишние столбцы (особенно строковые) в части SELECT при использовании ORDER BY/GROUP BY. Firebird обычно выполняет такие запросы при помощи сортировки (в памяти, затем на диске, если объем сортировки слишком большой), и если в списке столбцов есть длинные varchar, то размер сортировки также будет большим. Ограничение количества выводимых столбцов существенно ускорит сортировку при ORDER BY/GROUP BY.30. Используйте производные таблицы
Используйте производные таблицы (derived tables) для исключения ненужных сортировок: например, вместоSELECT FIELD_KEY, FIELD1, FIELD2, ... FIELD_N
FROM T
ORDER BY FIELD2
FROM T
ORDER BY FIELD2
Используйте такой вариант:
SELECT T.FIELD_KEY, T.FIELD1, T.FIELD2, ... T.FIELD_N
FROM (SELECT FIELD_KEY FROM T ORDER BY FIELD2) T2
JOIN T ON T.FIELD_KEY = T2.FIELD_KEY
FROM (SELECT FIELD_KEY FROM T ORDER BY FIELD2) T2
JOIN T ON T.FIELD_KEY = T2.FIELD_KEY
31. Храните строки эффективно
Для хранения коротких строк используйте VARCHAR, для хранения длинных строк (текста) используйте BLOB. Varchar работает быстрее с небольшими строками, потому что данные хранятся непосредственно как часть записи, и считываются как запись за один раз (если размер записи не превышает 2/3 страницы). BLOB хранятся отдельно от записи, и для чтения требуют отдельного обращения к страницам, поэтому работа с ними медленнее.32. Исключите столбцы BLOB из больших выборок (SELECT)
Используйте отдельные запросы для извлечения BLOB, например, для конкретной записи.33. Используйте BIGINT для ключей
Используйте BIGINT для первичных и уникальных ключей, и для любых других идентификаторов. Операции с BIGINT выполняются существенно быстрее, чем операции со строками, и тип BIGINT является достаточным для хранения больших чисел.34. Не используйте VARCHAR для ключей
Не используйте VARCHAR для хранения идентификаторов, если это не необходимо – операции со строками медленнее, чем с числовыми типами. Особенно избегайте идентификаторов типа GUID для ключей Primary/Unique – из-за случайных значений скорость вставки или обновления такого столбца может быть от 3 до 20 раз медленее, чем в случае BIGINT.35. Обновляйте статистику индексов
Регулярно пересчитывайте статистику индексов (SET STATISTICS INDEX …). Обновление статистики индексов часто обновляемых таблиц позволяет оптимизатору правильно использовать (или не использовать) индексы и строить лучший план запроса. Инструмент HQbird Firebird DataGuard может производить пересчет статистики индексов по расписанию.36. Используйте пул коннектов
Если соединения к БД длятся недолго (типично для веб-приложений), используйте пул коннектов – например, в PHP это функция ibase_pconnect вместо ibase_connect37. Используйте опцию LINGER
Если соединения к БД длятся недолго, и вы используете Firebird 3.0 SuperServer, укажите опцию LINGER для сохранения кэша БД в течение некоторого времени, пока соединения отсутствуют. Например, оператор ALTER DATABASE SET LINGER TO 60 установит интервал сохранения кэша в 60 секунд после отключения всех соединений. Эта опция сохранит кэш «разогретым» для последующих соединений, если они появятся до истечения указанного интервала.38. Используйте HASH JOIN
В Firebird 3.0, при объединении (join) больших и малых таблиц, HASH JOIN может быть гораздо быстрее обычного «вложенного» JOIN (nested loop). Для того, чтобы оптимизатор Firebird использовал HASH join, используйте +0 в условии объединения: T1 JOIN T2 ON T1.FIELD1+0 = T2.FIELD2+0. Не забудьте в этом случае проверить план запроса!39. Указывайте опцию DETERMINISTIC для функций PSQL
Указывайте опцию DETERMINISTIC для функций PSQL (в Firebird 3+), которые не имеют параметров и возвращают постоянные значения. Детерминистические функции вычисляются и кэшируются в контексте конкретного запроса.40. Используйте оконные (аналитические) функции
Если в вашем SELECT в виде столбца есть подзапрос с агрегатной функцией, используйте оконные (аналитические) функции (window functions) – они быстрее, чем подзапрос или 2 запроса. Например,select id, department, salary, salary / (select sum(salary) from employee) percentage
from employee
замените на
from employee
select id, department, sapary, salary / sum(salary) OVER () percentage from employee
Еще пример, показывает сумму кредита, накопленную сумму выплат и остаток по выплатам:
SELECT
payments.id AS id,
payments.bydate AS bydate,
credit.amount AS credit_amount,
payments.amount AS pay,
SUM(payments.amount) OVER(ORDER BY payments.bydate) AS s_amount,
SUM(payments.amount) OVER(ORDER BY payments.bydate, payments.id) AS s_amount2,
credit.amount - SUM(payments.amount) OVER(ORDER BY payments.bydate, payments.id) AS balance
FROM credit
JOIN payments ON payments.credit_id = credit.id
WHERE credit.id = 1
ORDER BY payments.bydate
payments.id AS id,
payments.bydate AS bydate,
credit.amount AS credit_amount,
payments.amount AS pay,
SUM(payments.amount) OVER(ORDER BY payments.bydate) AS s_amount,
SUM(payments.amount) OVER(ORDER BY payments.bydate, payments.id) AS s_amount2,
credit.amount - SUM(payments.amount) OVER(ORDER BY payments.bydate, payments.id) AS balance
FROM credit
JOIN payments ON payments.credit_id = credit.id
WHERE credit.id = 1
ORDER BY payments.bydate
41. Используйте опцию -se для gbak
Используйте опцию gbak –segbak -b -g -se service_mgr c:\db\data.fdb e:\backup\data.fbk
42. WHERE CURRENT OF
Наиболее быстрый способ обработки записей, возвращаемых курсором в PSQL, это ‘where current of43. Избегайте частого обращения к таблицам мониторинга (MON$).
Такие запросы потребляют значительное количество ресурсов, и могут ухудшать производительность, особенно если обращения к MON$ производятся регулярно в процедурах или триггерах. Мы рекомендуем обращаться к MON$ не чаще 1 раза в минуту.44. Используйте опцию NO_AUTO_UNDO для массовых вставок или обновлений
Если у вас в одной транзакции выполняется много операторов DML (Update/Insert/Delete), Firebird объединяет undo-log каждого оператора с undo-log транзакции. Для ускорения операций массовой вставки или обновления рекомендуем стартовать транзакцию с опцией NO AUTO UNDO. В этом случае слияние undo-log не будет производится, что ускорит операцию.45. Не используйте аутентификацию пользователей SRP, если она вам не нужна
Не используйте аутентификацию пользователей SRP, если она вам не нужна (Firebird 3.0+) – соединение с аутентификацией SRP происходит медленнее, чем с обычной аутентификацией.Самый главный совет
И еще совет, вне списка, так сказать – научитесь читать и анализировать планы запросов и анализировать статистику их выполнения – это ключ к производительности SQL запросов. Без этого все усилия по оптимизации окажутся бесполезными, поэтому настоятельно рекомендуем пройти курсы по Firebird, на которых рассматривается оптимизация запросов.Также, наша компания предлагает услуги по оптимизации производительности СУБД Firebird в рамках технической поддержки для предприятий. Наши специалисты помогут решить любую проблему с Firebird, включая разработку специальных патчей и версий.