Настройки PostgreSQL на Ubuntu Linux

Публикация № 983170

Администрирование - Системное

Postgres Ubuntu

21
Прилагаю свои настройки, которые позволили мне решить проблему с производительностью.

Хочу поделиться своими настройками, которые позволили мне решить проблему зависаний тяжелых динамических списков и ошибок

Конфигурация сервера СУБД:

  • Процессор = Intel(R) Xeon(R) Gold 6128 CPU @ 3.40GHz (12 ядер)
  • Память = 32GB
  • Диск = 200GB (SSD)
  • ОС = Ubuntu 16.04.5 LTS x64
  • СУБД = PostgeSQL 10.5-9.1C

Платформа 8.3.13.1644, размер базы 30GB

Настройки в файле postgresql.conf

 

max_connections = 1000
ssl = off
row_security = off
shared_buffers = 8GB
temp_buffers = 256MB
work_mem = 1GB
maintenance_work_mem = 2GB
shared_preload_libraries = 'online_analyze, plantuner'
bgwriter_delay = 20ms
bgwriter_lru_maxpages = 400
bgwriter_lru_multiplier = 4.0
commit_delay = 1000
max_wal_size = 8GB
min_wal_size = 4GB
checkpoint_completion_target = 0.9
seq_page_cost = 0.1
random_page_cost = 0.1
cpu_operator_cost = 0.00025
effective_cache_size = 16GB
default_statistics_target = 100
from_collapse_limit = 20
join_collapse_limit = 20
autovacuum_max_workers = 6
autovacuum_naptime = 20s
max_locks_per_transaction = 256
online_analyze.threshold = 50
online_analyze.scale_factor = 0.1
online_analyze.enable = on
online_analyze.verbose = off
online_analyze.local_tracking = on
online_analyze.min_interval = 10000
online_analyze.table_type = 'temporary'
plantuner.fix_empty_table = 'on'

fsync и synchronous_commit отключать не стал для надежности.

P.S. для настройки параллелизма отредактировал параметры:

 

max_worker_processes = 12
max_parallel_workers_per_gather = 12
max_parallel_workers = 12

А так же выполнил скрипт в базе pg_set_parallel.sql

 

 

UPD в версии PostgeSQL 10.5-24.1C стал нормально отрабатывать параметр join_collapse_limit = 20. Еще рекомендую выставить geqo_effort = 1 - сложные запросы будут работать ~2 раза быстрее

Если у вас SSD диски, а не HDD, то рекомендую установить seq_page_cost = random_page_cost (то есть оценка последовтельного доступа к диску равна произвольному для планировщика) - это очень сильно ускоряет работу + если памяти много, то можно понизить значения до минимальных, например 0.1 чтобы предпочтние отдавалось данным в памяти. Установил в значение 0.1 у обоих параметров. enable_nestloop = off выставил на on

21

См. также

Специальные предложения

Комментарии
Избранное Подписка Сортировка: Древо
1. Gilev.Vyacheslav 1822 19.01.19 02:38 Сейчас в теме
настройки
online_analyze.enable = off
enable_nestloop = off
для некоторых нагрузок могут не подойти
Dream_kz; starik-2005; +2 Ответить
5. w.r. 212 21.01.19 08:15 Сейчас в теме
(1)

online_analyze.enable - включение опции (ON) приводило к ошибке: canceling statement due to lock timeout
enable_nestloop - включение оцпии (ON) приводило к зависаниям динамического списка с 30 тыс записями
14. w.r. 212 14.03.19 13:03 Сейчас в теме
(1)

Собственно пора бы уже фирмам, делающим сборки, да и сообществу postgre ставить настройки, которые соотвествуют железу современных серверов (для планировщика seq_page_cost = 0.1 и random_page_cost = 0.1), а не считать, что на серверах установлен HDD и маленький объеем ОЗУ (по-умолчанию seq_page_cost = 1.0 и random_page_cost = 4.0). Тогда и не придется людям извращаться с enable_nestloop = off
2. ansh15 19.01.19 03:35 Сейчас в теме
Память = 32GB размер базы 30GB

Сэкономили... Ну да, она же (память) дорогая. SSD дешевле, пусть трудится.
На последних версиях платформы, СУБД и конфигураций(типовых) enable_nestloop = off уже не актуально, разве что в редком случае, когда конфу семилетней давности не удосужились обновить. Или не так уж и редком?
13. w.r. 212 14.03.19 12:59 Сейчас в теме
(2)
иповых) enable_nestloop = off уже не актуально, разве что в редком случае, когда конфу семилетней давности не удосужились обновить. Или не так уж и редком?

В современных конфигурациях планировщик заточен под HDD (seq_page_cost = 1,0 и random_page_cost = 4.0), а не для SSD + для машин с маленьким объемом ОЗУ (большие значения оценки доступа к диску по сравнению к данным в памяти). Причем это во всех современных сборках - 1С, Postgres Pro и другие

Пока я не прочитал эту статью и не настроил параметры оценки доступа к диску, мне приходилось ставить enable_nestloop = off, иначе динамические списки в моей концигурации с 30 тыс записями при поиске в них через Ctrl + F висли намертво
3. agerov 20.01.19 22:55 Сейчас в теме
И как оно? Какова разница в производительности? Приведённый конфиг не является даже универсальным и для актуальных версий ПО + железа с SSD.
В общем, данные бы до/после.
4. agerov 20.01.19 22:58 Сейчас в теме
И что с памятью, при вполне вменяемом ЦП?
6. viptextil1 15 21.01.19 09:45 Сейчас в теме
Эх, еще бы знать, как влияет каждый параметр на производительность. И каким образом оптимизировали систему... А то статью нужно бы переименовать на "по смотрите на мой конфиг."
7. w.r. 212 21.01.19 11:50 Сейчас в теме
(6) просто не счел нужным перепечатывать документацию. Найти на русском можно например на сайте https://postgrespro.ru/docs/postgresql/10/runtime-config-resource
8. Dream_kz 89 24.01.19 14:56 Сейчас в теме
join_collapse_limit = 1
Тут я бы тоже не был так категоричен, запросы с большим количеством соединений могут при такой настройке выполняться очень долго.

В такой связке патч работал бы лучше, ибо работал бы только для временных таблиц, а не для всех
online_analyze.enable = on
online_analyze.table_type = 'temporary' //вместо 'all'

И да, настройки бы "до".
9. w.r. 212 25.01.19 08:47 Сейчас в теме
(8)

протестировал тяжелый запрос - выборка из журнала бухгалтерии с подзапросом, соединением с другими регистрами и объединением. Результаты:

join_collapse_limit = 20 // по-умолчанию
online_analyze.enable = on
online_analyze.table_type = 'temporary'


269,251 сек

join_collapse_limit = 1 
online_analyze.enable = off
online_analyze.table_type = 'all'


193,046 сек

Как я понял, Postgre вообще очень интересный продукт в плане настроек - максимальной производительности можно добиться только экспериментально и не всегда настройка параметров по их описанию, т.е. "как должно работать" = "как работает в реальности"

И еще рекомендую использовать подзапросы в Postgre вместо временных таблиц, иногда выигрыш в скорости выполнения 2 раза
10. Dream_kz 89 25.01.19 08:55 Сейчас в теме
(9)
выборка из журнала бухгалтерии с подзапросом, соединением с другими регистрами и объединением

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

(9)
join_collapse_limit = 20 // по-умолчанию

По умолчанию должно быть 8, как и from_collapse_limit

(9)
И еще рекомендую использовать подзапросы в Postgre вместо временных таблиц, иногда выигрыш в скорости выполнения 2 раза

Опять же из-за отсутствия статистики для временных таблиц
11. w.r. 212 25.01.19 09:28 Сейчас в теме
(10)
При таком запросе патч работать не должен, ибо статистика собирается для временных таблиц, которых в запросе нет.

Тогда непонятно, откуда проседение производительности, если в моем запросе патч и так работать не должен
По умолчанию должно быть 8, как и from_collapse_limit

После этих настроект запрос выполнялся 10 минут, после чего прибил сеанс
12. ansh15 25.01.19 10:12 Сейчас в теме
По умолчанию в PostgreSQL, версия 9.6.7-1.1C - join_collapse_limit и from_collapse_limit равно 8.
В PostgreSQL, версия 10.5-10.1C эти параметры равны 20 по умолчанию.
Видимо, в 1С решили, что для большинства последних версий типовых с учетом версии платформы(8.3.13) значение 20 является более оптимальным. Наверное. неоднократно тестировали, прежде чем прийти к такому значению...
Оставьте свое сообщение