Контакты
IBSurgeon
Эта страница переведена машинным переводом. Читайте английский оригинал. English

Библиотека IBSurgeon

23 способа ускорить Firebird

Алексей Ковязин, IBSurgeon, [email protected], 08-Jan-2019

Переводы: Португальский

Почему «ещё 23»?

Некоторые из вас помнят статью «45 способов ускорить Firebird», опубликованную в мае 2016 года. Теперь пришло время опубликовать следующую серию советов и приёмов, в основном основанных на опыте оптимизации и обслуживания баз данных и серверов Firebird с большим числом подключений (1000+).

1. Установите схему электропитания «Высокая производительность» в Windows Server 2016 и 2019

По умолчанию в Windows Server установлена схема электропитания «Сбалансированная», которая не подходит для серверов баз данных. Установите «Высокая производительность» и получите примерно +20% производительности для операций, интенсивно использующих CPU. Это можно сделать онлайн, без перезапуска или перезагрузки. На рисунке ниже показан график CPU, демонстрирующий преимущество схемы электропитания «Высокая производительность»:

Подробнее о наших тестах со схемами электропитания Windows можно узнать здесь.

2. Включите «Взаимодействие с рабочим столом» для Classic в Windows

Если вы используете Firebird с архитектурой Classic в Windows, включите флажок «Разрешить службе взаимодействовать с рабочим столом». Без этой настройки ресурс «desktop heap» ограничен Windows, и Firebird не может открыть более 250-300 подключений (зависит от метаданных базы данных и связанного потребления памяти) — возникнет ошибка Out Of Memory.

3. Осторожно: контроллер домена

Проблема в том, что Windows с ролью контроллера домена отключает кэш записи на диске с базой данных Active Directory.

Это влияет на Firebird различными способами (как и на другие приложения, конечно), и он демонстрирует значительно худшую производительность, чем на серверах без ролей Active Directory.

Обратите внимание, что эта проблема затрагивает такие популярные версии Windows, как Windows Small Business Server 2011, а также другие версии с DC.

4. Увеличьте лимит «max open files» в Linux

Если вы используете Linux в качестве сервера баз данных, не забудьте настроить лимиты для Firebird. Проверьте лимиты для вашего процесса Firebird (SuperServer или SuperClassic) следующей командой:

Code 
cat /proc//limits

и обратите внимание на строку с максимальным числом открытых файлов.

Firebird может использовать до 4 дескрипторов на подключение, и если у вас что-то вроде этого:

Code 
Max open files 4096 4096 files

это означает, что общее число подключений, обслуживаемых процессом Firebird, будет ограничено примерно 1000.

Обратите внимание — если у вас на сервере 4 базы данных, подключение для каждой базы данных учитывается.

Установите больше — я рекомендую 65535.

Не забудьте проверить снова после перезапуска процесса Firebird: применилось ли это или нет.

Для архитектуры Classic необходимо проверить и увеличить лимиты для пользователя «firebird».

5. Используйте современный Linux

Да, я понимаю, что этот совет тривиален, но я много раз видел хорошее улучшение производительности после миграции с CentOS 6 на 7, с Ubuntu 12 на 16 (на том же оборудовании!), так что теперь это обязательная рекомендация для серверов баз данных с более чем 250-300 подключениями. Современный Linux — это предварительное условие перед дальнейшими шагами оптимизации.

Рекомендуемые версии Linux: CentOS 7.x и Ubuntu 16, 18.

6. Зарезервируйте 40% RAM для файлового кэша в Windows

Менеджер памяти ОС имеет особенности в отношении распределения памяти, и по умолчанию Windows требует 40% RAM для файлового кэша.

К сожалению, несовершенный инструмент Windows Task Manager показывает память, используемую для файлового кэша, как «свободную», и некоторые администраторы пытаются заставить Firebird потребить всю эту свободную память, поэтому они устанавливают параметр DefaultDBCachePage в firebird.conf в очень высокие значения, и это обычно приводит к свопингу.

Всегда используйте инструмент RAMMap, чтобы увидеть фактическое использование памяти в Windows.

Эмпирическое правило для Windows Server (выделенного для использования в качестве сервера Firebird) следующее: память Firebird (Working Set) должна быть меньше 40% от общего объёма RAM. Если общий размер рабочих наборов для всех процессов превышает 50%, Windows может начать свопинг.

Обратите внимание: «зарезервировать» здесь означает не только «не устанавливать слишком много буферов страниц в Firebird», но, что также важно, ограничить потребление памяти другим программным обеспечением. Например, если у вас на том же сервере, что и Firebird, работают MS Exchange или MSSQL, обязательно ограничьте их аппетиты к памяти.

Если вам интересно узнать подробности, я записал вебинар, посвящённый управлению памятью в Firebird:

7. Зарезервируйте 30% RAM для файлового кэша в Linux

Linux работает с файловым кэшем иначе, чем Windows, и, в целом, объём RAM, используемый для файлового кэша, может быть значительно меньше, чем в Windows, без заметного снижения производительности Firebird. Однако, чтобы гарантировать высокую производительность системы с большим числом подключений, особенно на Classic и SuperClassic, хорошей идеей будет зарезервировать 30% RAM для файлового кэша.

8. Используйте irqbalance в Linux

irqblalnce часто улучшает производительность Firebird и балансировку нагрузки CPU на серверах с большим числом ядер.

9. Для виртуальных машин — остерегайтесь Memory Overcommit

Виртуальная машина может быть настроена так, чтобы иметь больше памяти, чем физически существует на хост-машине — с помощью функции, известной как Memory Overcommit (название может отличаться в разных системах виртуализации). Это означает, что в случае пика потребления памяти (на VM с сервером баз данных или на соседней VM) может начаться свопинг, что приведёт к значительным задержкам. Для высокопроизводительной VM, предназначенной для сервера баз данных, вся память должна быть статической.

10. Для виртуальных машин — проверьте лимиты VM

Часто VM создаются с лимитами CPU и IO по умолчанию, которые могут быть очень низкими, например 50 IOPS и 10% CPU. Проверьте настройки вашей серверной VM и снимите любые ограничения — высокопроизводительный сервер баз данных должен иметь все возможные CPU, пропускную способность и IO.

11. Очищайте временные файлы Firebird

Firebird создаёт множество временных файлов для различных операций: сортировки, обработки BLOB, трассировки. Эти файлы хранятся в следующих местах: в Windows C:\ProgramData\firebird, в Linux / tmp / firebird

Обычно эти файлы должны очищаться автоматически, однако иногда этого не происходит (например, в случае перезагрузки сервера).

Периодически проверяйте эти папки и очищайте старые файлы — там могут быть многие гигабайты устаревших файлов fb_NNN, и их очистка освободит место на системном диске.

12. Не забывайте включать файловый кэш при большом кэше Firebird

Как вы знаете, кэш Firebird (также называемый «буферами страниц») задаётся параметром DefaultDBCachePages в firebird.conf/databases.conf или непосредственно в заголовке базы данных.

В Firebird 3 SuperServer размер этого кэша можно установить очень большим, но важно помнить о другом параметре: FileSystemCacheThreshold.

Если FileSystemCacheThreshold меньше DefaultDBCachePages или буферов страниц, файловый кэш операционной системы не будет использоваться, что может привести к проблемам с производительностью.

В 99% случаев лучше, чтобы файловый кэш был включён.

Чтобы обеспечить это, всегда устанавливайте параметры согласно следующему правилу:

  • DefaultDBCachePages = X
  • FileSystemCacheThreshold = X+ N, N>1

Есть редкие случаи, когда отключение файлового кэша улучшает производительность — если у вас есть такой пример, пожалуйста, свяжитесь со мной — [email protected]!

13. Ускорьте базу данных безопасности

Каждое подключение к базе данных Firebird устанавливает соединение с базой данных безопасности (security3.fdb в случае Firebird 3) и выполняет несколько чтений и записей (страницы транзакций, заголовочная страница). Если у вас частые подключения, производительность вашей базы данных безопасности может стать проблемой.

Как минимум, вы можете сделать следующее:

  • Увеличьте page buffers для securityN.fdb (эмпирический оптимум — 256 буферов)
  • Переместите security3.fdb на быстрый диск (это стандартная возможность в Firebird 3, в 2.5 потребуется переустановка)

Затем можно установить Forced Writes OFF для базы данных security — небольшая вероятность повреждения в этом случае не является проблемой.

Самый радикальный способ — сделать базу данных security доступной только для чтения — это исключит все записи в неё.

Если вы нечасто меняете пользователей в базе данных security, это лучшее решение.

14. Попробуйте SuperClassic на Firebird 3

В Firebird 3 архитектура SuperServer активно рекламировалась как идеальное решение для производительности, но есть некоторые типы нагрузки, которые демонстрируют лучшую производительность с SuperClassic (но не с Classic — он всегда работает медленнее, чем SuperServer/SuperClassic).

Как провести этот эксперимент безопасным способом? Выполните следующие шаги:

Чтобы попробовать SuperClassic

  1. Установите в firebird.conf
  2. ServerMode=SuperClassic
  3. DefaultDbCachePages=1024
  4. gfix -buff 0
  5. Перезапустите Firebird

Чтобы вернуться к SuperServer

  1. Установите в firebird.conf
  2. ServerMode=SuperServer
  3. DefaultDbCachePages=N # N*размер страницы*количество_баз < 25% RAM
  4. FileSystemCacheThreshold = N+1
  5. gfix -buff 0
  6. Перезапустите Firebird

Пожалуйста, напишите мне ([email protected]) о результатах эксперимента, мне интересно увидеть результаты.

15. Большая база данных? Увеличьте размер страницы

По умолчанию базы данных Firebird имеют следующие размеры страниц:

  • 2.5 — 4096 байт
  • 3.0 — 8192 байт

Однако максимальный размер страницы — 16K (в 4.0 — 32K).

Для баз данных размером более 100 Гб в 95% случаев лучше иметь максимально доступный размер страницы, чтобы:

  • Уменьшить глубину индексов. Рекомендуется иметь индексы с глубиной меньше или равной 3. Индексы с глубиной 4 и 5 будут работать значительно медленнее
  • Увеличить использование RAM. Кэш Firebird задаётся в страницах, 1000 страниц с размером страницы 8K составят 8 Мб фактической памяти, а с 16K — 16 Мб.
  • Уменьшить количество системных страниц. Это ускорит доступ к записям больших таблиц (меньше переходов указатель-указатель-страница данных) и поможет при подготовке больших SQL-запросов. Чтобы увеличить размер страницы базы данных, базу данных следует создать резервную копию с помощью утилиты gbak, а затем восстановить с параметром -page ( gbak -c -page 16384).

Обратите внимание: если у вас есть база данных с множеством небольших blob-ов, увеличение размера страницы может как уменьшить фрагментацию, так и увеличить её, и трудно предсказать, увеличит ли это производительность или уменьшит её.

16. Не используйте флаг no_reserve

Флаг no_reserve заставляет Firebird не резервировать свободное пространство (30%) на страницах данных для возможных версий записей, которые возникают после UPDATE или DELETE. Этот флаг позволяет хранить данные более компактно (и размер базы данных тоже меньше), но в случае UPDATE/DELETE все изменения попадают на новую страницу данных. В результате в базе данных с флагом no_reserve операции UPDATE/DELETE выполняются медленнее.

Итак, если ваша база данных не доступна только для чтения, я рекомендую снять флаг no_reserve.

Как проверить, установлен ли он — см. строку Attributes в выводе

Code 
gstat -h database

Как отключить:

Code 
gfix -use reserve database

После этой команды новые страницы данных будут создаваться с зарезервированным пространством.

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

Обратите внимание: размер базы данных вырастет после снятия флага no_reserve и backup/restore.

17. Установите большой начальный размер для таблицы блокировок Firebird

Таблица блокировок — это механизм Firebird, который используется для синхронизации доступа к внутренним объектам движка.

Таблица блокировок Firebird может расти автоматически, но её увеличение — медленная операция, которая может приводить к микрозависаниям. Таблица блокировок может только расти, начиная с начального размера (он задаётся в firebird.conf).

Чтобы предотвратить множественные раунды увеличения таблицы блокировок, хорошей идеей будет отслеживать размер таблицы блокировок в конце рабочего периода (дня, недели и т.д.), а затем установить его в качестве начального размера в firebird.conf.

LockMemSize=99999999

Для справки: LockMemSize на системах с высокой нагрузкой и ~1000 пользователей обычно ниже 200 Мб.

18. Используйте fb_lock_print для подсчёта количества подключений к базе данных

Получение количества подключений к базе данных — частая задача для разработчиков баз данных: например, это может понадобиться для целей лицензирования.

Часто разработчики используют запрос SELECT count(*) FROM MON$ATTACHMENTS для получения этого значения, но это не оптимальный способ: частые запросы к таблицам MON$ могут создавать нагрузку на базу данных, поэтому лучше использовать альтернативу:

Запустите

fb_lock_print -d database_name | alias

и проверьте значение Owners — оно покажет текущее количество подключений к базе данных.

19. Избегайте ненужных LEFT JOIN

Часто я вижу запросы с конструкцией вроде этой:

T1 LEFT JOIN T2 ON (…) WHERE T2.Field_condition

По сути, условие на T2 исключает NULL из вывода LEFT JOIN T2, поэтому можно заменить LEFT JOIN на INNER JOIN — это не повлияет на результат запроса.

INNER JOIN даёт больше свободы оптимизатору Firebird, и в современных версиях Firebird он оптимизируется намного лучше, чем LEFT.

Особенно это имеет смысл в следующих случаях:

  • Нет условия для T1 в предложении WHERE
  • T2 — небольшая таблица

20. Избегайте ненужного подсчёта записей

Ещё одна распространённая ошибка в сложных запросах к базе данных и хранимых процедурах — использование select count() только для проверки существования записи.

Следующий запрос прочитает все записи согласно condition1:

Code 
(select count(*)…. where condition11) >0

Лучше использовать вместо этого такую конструкцию

Code 
Exists(select first 1 id where condition1)

Если condition1 возвращает более 1 записи, предложенный вариант будет намного быстрее, потому что он не читает все записи, а останавливается после первой извлечённой записи.

21. Избегайте ненужной сортировки в хранимых процедурах

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

Например, в примере хранимой процедуры ниже предложение ORDER BY бесполезно с точки зрения бизнес-логики, но добавляет ненужную операцию сортировки.

Code 
create or alter procedure NEW_PROCEDURE
returns (
    SUMX double precision)
as
declare variable _amount double precision;
begin
for select T1.amount from Table1 t1 where ....
    order by id
    into :_amount
    do
    begin
     sumx=sumx+_amount
    end;
  suspend;
end

Проверьте ваш код PSQL на похожие ситуации и удалите бесполезный ORDER BY (а также distinct и UNION).

22. Не держите запросы в подготовленном состоянии без необходимости

Часто мы видим 500-1000 подготовленных операторов в каждом подключении (это можно проверить с помощью запросов MON$).

Подавляющее большинство из них выполняется только один раз, а затем они просто лежат в RAM, увеличивая рабочий набор Firebird и замедляя запросы MON$.

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

23. Всегда закрывайте запросы с большой сортировкой

Пока SQL-запрос с сортировкой (ORDER BY, GROUP BY, UNION, distinct) не закрыт, Firebird удерживает отсортированные записи в памяти. Размер памяти, выделяемой для сортировки, задаётся параметром TempCacheLimit в firebird.conf, по умолчанию он составляет 64 Мб.

Даже при увеличенном TempCacheLimit длительно выполняющиеся запросы с большим количеством сортируемых записей в конечном итоге израсходуют весь выделенный объём, и в результате сортировка перейдёт во временные файлы (т.е. на диск). Как следствие, это может привести к значительному замедлению.

Рекомендация — своевременно закрывать все такие запросы.

Вопросы?

Пожалуйста, обращайтесь ко мне с любыми вопросами: [email protected]!