LINUX.ORG.RU
ФорумAdmin

Специфичный тюнинг postgresql

 ,


4

4

Всем привет. Я надеюсь призвать в этот тред богов постгреса, которые подскажут мне секреты его тюнинга.
Итак, есть сервер, который будет обслуживать чисто каталог bacula (который на mysql с innodb занимал 120Гб, из которых 95Гб было в одной таблице), на postgresql.
В сервере установлен не слабый CPU:

Intel(R) Core(TM) i7-3820 CPU @ 3.60GHz 
1 package(s) x 4 core(s) x 2 SMT threads
и 64Gb RAM

Требования у bacula таковы:
- очень сложные многоэтажные селекты, для примера:

SELECT Path.Path, Filename.Name, T1.FileIndex, T1.JobId, LStat, DeltaSeq FROM ( SELECT FileId, Job.JobId AS JobId, FileIndex, File.PathId AS PathId, File.FilenameId AS FilenameId, LStat     , File.DeltaSeq AS DeltaSeq, Job.JobTDate AS JobTDate FROM Job, File, ( SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId, DeltaSeq FROM ( SELECT JobTDate, PathId, FilenameId, DeltaSeq FROM File JOIN Job USING (JobId) WHERE File.JobId IN(3401,3532,3614,3694,3774,3858,4048,4127,4215,4297,4377,4568,4652,4751,4833) UNION ALL SELECT JobTDate, PathId, FilenameId, DeltaSeq FROM BaseFiles JOIN File USING (FileId) JOIN Job  ON    (BaseJobId = Job.JobId) WHERE BaseFiles.JobId IN(3401,3532,3614,3694,3774,3858,4048,4127,4215,4297,4377,4568,4652,4751,4833) ) AS tmp GROUP BY PathId, FilenameId, DeltaSeq ) AS T1 WHERE (Job.JobId IN ( SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (3401,3532,3614,3694,3774,3858,4048,4127,4215,4297,4377,4568,4652,4751,4833)) OR Job.JobId IN(3401,3532,3614,3694,3774,3858,4048,4127,4215,4297,4377,4568,4652,4751,4833)) AND T1.JobTDate = Job.JobTDate AND Job.JobId = File.JobId AND T1.PathId = File.PathId AND T1.FilenameId = File.FilenameId ) AS T1 JOIN Filename ON (Filename.FilenameId = T1.FilenameId) JOIN Path ON (Path.PathId = T1.PathId) WHERE FileIndex > 0 ORDER BY T1.JobTDate, FileIndex ASC

- ну и многочисленные параллельные insert-ы во время выполнения ежесуточного бекапа большого количества файлов;

Интересует, как бы оптимальнее настроить postgres для такой вот единственной базы? Пока остановился на следующем:

max_connections = 150 # в bacula director стоит max concurrent 150
shared_buffers = 15GB
temp_buffers = 8GB
wal_buffers = 16MB
work_mem = 512MB
maintenance_work_mem = 1GB 
effective_cache_size = 42GB
fsync = off
synchronous_commit = off
wal_sync_method = fsync 
random_page_cost = 2.0
cpu_tuple_cost = 0.001
cpu_index_tuple_cost = 0.0005
track_counts = on
update_process_title = off
autovacuum = on
autovacuum_vacuum_threshold = 5000
autovacuum_analyze_threshold = 2500 
default_statistics_target = 50
constraint_exclusion = on
checkpoint_segments = 16

Что в этом конфиге кажется вам диким? Или просто неоптимальным? Подскажите, т.к не имею большого опыта в настройке Постгреса. Версия PG: postgresql-server-9.2.1

Все это стоит на FreeBSD, там установил вот что:

kern.ipc.shm_use_phys=1
kern.ipc.semmni=256
kern.ipc.semmns=1024
kern.ipc.somaxconn=4096
kern.ipc.shmmax=17179869184
kern.ipc.shmall=15395266600
kern.maxfiles=256000
kern.maxfilesperproc=240000
kern.maxvnodes=500000
kern.threads.max_threads_per_proc=4096
vm.pmap.shpgperproc=4900
vm.pmap.pv_entry_max=32000000

Опять же, может с чем переборщил, или забыл что важное? Скорректируйте с высоты вашего опыта =) Буду благодарен любым замечаниям.

★★★★★

Ответ на: комментарий от zgen

на restore от 10 до 50 в день (да, необычно, но так получается). на бекап - где-то сотня, все выполняются одновременно ночью. при этом избежать слишком больших тормозов при SELECT-ах для меня более приоритетно, чем избежать тормозов при INSERT-ах.

Komintern ★★★★★ ()
Последнее исправление: Komintern (всего исправлений: 1)
Ответ на: fsync = off - бить ногами от Deleted

понял, спасибо за рекомендацию. но если репликация не планируется, а потеря питания вообще очень маловероятна - то сильно ли критично значение off?

Komintern ★★★★★ ()
Последнее исправление: Komintern (всего исправлений: 1)
Ответ на: комментарий от Komintern

там же написано - 3% намеряли, т.е. в любом случае лучше не отключать:

Быстродействие вставки в зависимости от значения fsync

Была протестирована в нескольких циклах зависимость скорости вставки от значения fsync. По результатам получилось что при fsync=off скорость выше примерно на 3.3%. Стоит-ли такое ускорение потери базы при отключении питания — решать каждому самостоятельно. Я лично намерен использовать Postgresql исключительно с fsync=on.

Deleted ()
Ответ на: комментарий от Deleted

понял, спасибо =)
а насчет остальных настроек с виду все в порядке?

Komintern ★★★★★ ()

Вот это тюнить:

http://wiki.etersoft.ru/PostgreSQL/Optimum#work_mem Вложенные селекты - это плохо, уничтожить. Это можно немножко укоротить и убыстрить, использовав вьюхи (\h CREATE VIEW), ну и вообще ощущение, что это всё неоптимально. Сама структура неоптимальна, для такого вида запросов хорошо бы ввести дополнительные построения. Или денормализацию. А если нет, то возможно даже придется отказаться от индексов, может быть это даже будет работать быстрее без них.
И где-то я этот фарш уже видел, вроде бы в какой-то тизерке.

UFO-man ()
Ответ на: Вот это тюнить: от UFO-man

И где-то я этот фарш уже видел, вроде бы в какой-то тизерке

что такое тизерка и какой фарш имеется ввиду?
насчет запросов - их же готовая софтина делает, предлагаешь переписывать bacula? =)

Komintern ★★★★★ ()

wal_buffers лучше оставить по умолчанию, 16 мб слишком много, скорее всего будет снижать производительность при записи данных.

wal_sync_method тоже лучше оставлять дефолтным, если нет осознанных причин его менять.

+ к убрать «fsync = off», вообще никогда и нигде так делать не нужно, synchronous_commit = off вполне достаточно если хочется ускорить запись данных в бд в обмен на потенциальные потери недавно закоммиченных транзакций.

temp_buffers и work_mem тоже слишком большие. Это размеры буферов для каждой сессии (т.е. коннекта), соответственно их нужно брать раз в 150 меньше, иначе есть вероятность уйти в своп во время работы и всё повесить

mashina ★★★★★ ()

... И max_connections нужно делать с запасом порядка ~ %10 по отношению к плапируемым одновременным подключениям. Если в бакуле 'max concurrent' в 150 означает что может потенциально быть 150 одновременных подключений, то max_connections стоит немного поднять.

Немного офтопик, но не многвато ли 150 вркеров бакуллы? Сможет ли железо успевать бэкапить сразу в столько потоков.

mashina ★★★★★ ()
Ответ на: комментарий от mashina

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

Komintern ★★★★★ ()
Ответ на: комментарий от Komintern

Тизерка - такая рекламная сеть под тизеры. Тизеры - сорт рекламных объявлений. Фарш здесь - приведенный выше запрос, мне он не нравится, ябпереписал. На счёт переписывать ли bacula - это уж твоё дело конечно, в принципе - в этом ничего такого нет.

UFO-man ()
Ответ на: комментарий от anonymous

может тут шардинг базы сделать?

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

mashina ★★★★★ ()
Ответ на: комментарий от ukr_unix_user

всё перепутали, сперва sqlи оптимизируют, а потом уже всё остальное.

Обычно до развертки СУБД правильно выбирают железо и настраивают систему, потому что потом это можно будет сделать только на другом сервере. Потом (если база уже спроектирована и весь код написан) выставляют наиболее подходящие параметры в postgresql.conf, из понимания специфики работы СУБД в конкретном проекте. И только потом смотрят через лог на неэффективные запросы и, может быть, их оптимизируют. Сразу лезть в рабочую систему кодерам это моветон (вероятность, что радиус кривизны рук оптимизаторов не совпадает с радиусом кривизны рук разработчиков близка к 100%).

soomrack ★★★ ()
Ответ на: комментарий от soomrack

если никто не смотрел на планы запросов при разработке, то ни сервер ни его «тонкая» настройка не помогут.

ukr_unix_user ★★★★ ()
Ответ на: комментарий от mashina

wal_buffers лучше оставить по умолчанию, 16 мб слишком много, скорее всего будет снижать производительность при записи данных.

Откуда такая информация?

ventilator ★★★ ()
Ответ на: комментарий от ventilator

уже не многу найти источнинов и тестов, когда-то наблюдалось похожее на каких-то 8.х. При увеличении свыше нескольких сотен кб шёл небольшой спад. Сейчас, похоже, такого больше нет и дефолтные значения с типичными shared_buffers и так 16мб.

mashina ★★★★★ ()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.