[Sysadmins] PostgreSQL оптимизация

[Sysadmins] PostgreSQL оптимизация

[Alexandr Ogurtsov]

[-- Attachment #1: Type: text/plain, Size: 3256 bytes --]

Уважаемые коллеги, ищу совета по настройке производительности PostgreSQL
8.1.(Debian Etch)
В качестве СУБД он используется для RoR приложения. База данных 600М+
Таблиц 60 штук Самая "тяжелая" табличка весит 160М или вместе с
индексами 255М, остальные заметно "легче". Хочется чтобы всё жило в
оперативке и как можно меньше обращалось к внешнему накопителю. Порылся
по всезнающему интернету, поигрался с опциями с postgresql.conf. Но
существенного выигрыша на своей стендовой машине так и не получил. Что с
умолчательными настройками, что со всеми попытками оптимизации тестовый
запрос выполняется 2.7сек. Стенд слабенький конечно Cel 1.2GHz 512RAM.
При тестировании LA до 1 редко дотягивает, но CPU usage ~100%
 Вот что на нём даёт ptop во время теста, с умолчательными настройками в
postgresql.conf
 last pid:  4362;  load avg:  0.99,  0.82,  0.47;       up 0+06:57:38
                                        18:12:57
4 processes: 1 running, 3 sleeping
CPU states: 93.4% user,  0.0% nice,  6.6% system,  0.0% idle,  0.0% iowait
Memory: 479M used, 17M free, 16M buffers, 115M cached
Swap: 40K used, 1451M free

  PID USERNAME PRI NICE  SIZE   RES STATE   TIME   WCPU    CPU COMMAND
 4345 postgres  20    0   23M   14M run     6:22 17.23% 98.84% postgres:
iscander test_db 127.0.0.1(53231) SE
 4040 postgres  20    0   21M 7092K sleep   0:00  0.00%  0.00% postgres:
iscander test_db 127.0.0.1(34713) id
 4039 postgres  20    0   21M 6780K sleep   0:00  0.00%  0.00% postgres:
iscander test_db 127.0.0.1(34711) id
 4041 postgres  20    0   20M 5964K sleep   0:00  0.00%  0.00% postgres:
iscander test_db 127.0.0.1(34715) id

 На "боевом" серевре железо ожидается естесвенно много мощнее. У
нынешнего хостера, этот же запрос выполнеят 0.06 - 0.07сек.
 На что следует обратить внимание в железе "боевого сервера" на память,
на число процессоров(имеет ли смыл для Postgre ?) их частоту.
 Что действительно имеет смысл крутить в настройках  postgresql.conf
чтоб  всё по максимуму держать в ОЗУ. Кроме сосбсвенно Postgre на
сервере будут жить nginx + 3-6 mongrel для Ruby on Rails.
 Буду благодарен за разумные рекомендации. Просто результаты у хостера
шокирующие(!) с учетом того что у них shared Postgre для нескольких
клиентов. А делиться своим опытом он не хотят.

[-- Attachment #2: iscander_alt.vcf --]
[-- Type: text/x-vcard, Size: 148 bytes --]

begin:vcard
fn:Alexandr Ogurtsov
n:Ogurtsov;Alexandr
note:Linux is very friendly it is just picky who its friends are
version:2.1
end:vcard

Re: [Sysadmins] PostgreSQL оптимизация

[Alexandr Ogurtsov]

[-- Attachment #1: Type: text/plain, Size: 3108 bytes --]

Спасибо за ответ
> Для начала перейдите на 8.3 - оно реально быстрее!
 Обязательно воспользуюсь, вы не первый кто об этом упоминает.
> Во вторых проверьте, все ли индексы у вас созданы.
 Индексы созданы, я ещё раз упомяну о том, что на нынешнем хостинге, а
не у меня на стенде, этот запрос выполняется за 0.06-0.07 секунды.
>> оперативке и как можно меньше обращалось к внешнему накопителю. Порылся
> Ну при вашем количестве оперативы это нереально!
 При нынешнем количесве ОЗУ на стенде, да. Больше же интересуют
настройки для production - там ОЗУ ожидается 4-8GB. Но не всё для
postgre часть отдастся под сервер приложений на RubyOnRails.
Одновременных конектов будет немного 6-10 mongrel серверов, то есть 20
конектов это с хорошим запасом.
> Выполняйте запрос с EXPLAIN ANALYZE и смотрите вывод.
> Возможно не хватает индексов, возможно, что вам надо просто
> оптимизировать запросы, а не настройки базы.
 Запрос тестовый и индексов хватает, выбран в качестве "тяжелого" для
тестирования именно настроек СУБД.
 Общие рекомендации по разнесению данных и журнала транзакций на разные
накопители, учёл уже. Чем больше RAM тем лучше, оптимизация самой БД не
являтся сейчас приоритетной. Ещё раз повторюсь я использую для
тестирования БД с реально работающего сервера. Каждый раз она создаётся
для тестирования из дампа. Там VACUM -у пока ещё делать нечего. Что меня
интересует это:
1. Рекомендации по настройке буферов памяти при наличии достаточного
объёма RAM для того чтобы вся БД помещалась в памяти. Объём базы около
600Мб реально откусить на сервере можно 2-4Gb только для того чтоб
Postgre не трогал винт при выборках.
2. Имеет ли смысл тонкий тюнинг настроек планировщика запросов QUERY TUNING?
3. Что имеет смысл крутить и в какую сторону для
Background writer. Cost-Based Vacuum Delay. WAL?

[-- Attachment #2: iscander_alt.vcf --]
[-- Type: text/x-vcard, Size: 148 bytes --]

begin:vcard
fn:Alexandr Ogurtsov
n:Ogurtsov;Alexandr
note:Linux is very friendly it is just picky who its friends are
version:2.1
end:vcard

Re: [Sysadmins] PostgreSQL оптимизация

[Ivan Fedorov]

[-- Attachment #1: Type: text/plain, Size: 2656 bytes --]

Alexandr Ogurtsov <iscander.alt@gmail.com> writes:

>>> оперативке и как можно меньше обращалось к внешнему накопителю. Порылся
>> Ну при вашем количестве оперативы это нереально!
> При нынешнем количесве ОЗУ на стенде, да. Больше же интересуют
> настройки для production - там ОЗУ ожидается 4-8GB. Но не всё для
Я боюсь, что тюнить что-либо не на том же оборудовании несколько
бессмысленно.

> postgre часть отдастся под сервер приложений на RubyOnRails.
> Одновременных конектов будет немного 6-10 mongrel серверов, то есть 20
> конектов это с хорошим запасом.
Ну вообще есть "золотое правило" - количество процессов Pg должно быть
равно количеству процессоров(ядер) * 2. То есть если у вас например 2
проца Intel Xeon 5430 с 4мя ядрами, то вам надо использовать до 16-ти
процессов Pg. 

Советую обратить внимание на PgBouncer.

> 1. Рекомендации по настройке буферов памяти при наличии достаточного
> объёма RAM для того чтобы вся БД помещалась в памяти. Объём базы
> около 600Мб реально откусить на сервере можно 2-4Gb только для того
> чтоб Postgre не трогал винт при выборках.
Ну сделайте на системе 1-2 гига shared memory (shm) и отдайте их
Pg. Тогда он почти гарантированно загрузить базу в память, а так
дисковый кэш в Linux работает вполне оптимально.

> 2. Имеет ли смысл тонкий тюнинг настроек планировщика запросов QUERY
> TUNING?
> 3. Что имеет смысл крутить и в какую сторону для
> Background writer. Cost-Based Vacuum Delay. WAL?
Это невозможно определить по 1 запросу. Если у системы выполняется 1
медленный и 10000 быстрых запросов, то тюнить стоит быстрые запросы!

[-- Attachment #2: Type: application/pgp-signature, Size: 196 bytes --]

Re: [Sysadmins] PostgreSQL оптимизация

[Alexandr Ogurtsov]

[-- Attachment #1: Type: text/plain, Size: 1600 bytes --]

Спасибо ещё раз, уточняю.
> Ну вообще есть "золотое правило" - количество процессов Pg должно быть
> равно количеству процессоров(ядер) * 2. То есть если у вас например 2
> проца Intel Xeon 5430 с 4мя ядрами, то вам надо использовать до 16-ти
> процессов Pg. 
 Правило понял, но не совсем пойму как это управляется настройками PG
это же явно не
max_connections = параметр. :) Я правильно понимаю что вы предлагаете
запустить для примера с 8 ядрами 16 отдельных "сущностей" PG?
> Советую обратить внимание на PgBouncer.
Обратил. И балансировать между 16 запущными postmaster процессами?

> Ну сделайте на системе 1-2 гига shared memory (shm) и отдайте их
> Pg. Тогда он почти гарантированно загрузить базу в память, а так
> дисковый кэш в Linux работает вполне оптимально.

Опять же притворюсь "танкистом" ;)  Это вы про отдельную точку
монтирования для
 /var/lib/postgresql/8.3/main или все же про настройки
shared_buffers, temp_buffers, maintenance_work_mem, work_mem,
effective_cache_size, max_fsm_*

[-- Attachment #2: iscander_alt.vcf --]
[-- Type: text/x-vcard, Size: 148 bytes --]

begin:vcard
fn:Alexandr Ogurtsov
n:Ogurtsov;Alexandr
note:Linux is very friendly it is just picky who its friends are
version:2.1
end:vcard

Re: [Sysadmins] PostgreSQL оптимизация

[Andrey Chichak]

Ivan Fedorov wrote:
> Alexandr Ogurtsov <iscander.alt@gmail.com> writes:
>   
>> 1. Рекомендации по настройке буферов памяти при наличии достаточного
>> объёма RAM для того чтобы вся БД помещалась в памяти. Объём базы
>> около 600Мб реально откусить на сервере можно 2-4Gb только для того
>> чтоб Postgre не трогал винт при выборках.
>>     
> Ну сделайте на системе 1-2 гига shared memory (shm) и отдайте их
> Pg. Тогда он почти гарантированно загрузить базу в память, а так
> дисковый кэш в Linux работает вполне оптимально.
>   
не могу согласиться - постгрес в shared не хранит данных. и в 
руководствах по оптимизации написано - добейтесь нужного количества 
свободной памяти, которую система использует под кэш.
т.е. в данном случае необходимо сделать так чтобы около 1Гб памяти было 
не занято процессами, дабы использовалось под буфера и дисковый кеш.

статейка про оптимизацию например: http://www.powerpostgresql.com/PerfList

-- 
С уважением,
Андрей Чичак.
ООО "С-Плюс", г.Томск
JID: chch@kit.tomsk.ru
ICQ: 13154894

Re: [Sysadmins] PostgreSQL оптимизация

[Alexandr Ogurtsov]

[-- Attachment #1: Type: text/plain, Size: 183 bytes --]

> статейка про оптимизацию например: http://www.powerpostgresql.com/PerfList
Старовата статейка, да уже и получше есть:
http://www.inp.nsk.su/~baldin/PostgreSQL/postgresql-tuning.pdf

[-- Attachment #2: iscander_alt.vcf --]
[-- Type: text/x-vcard, Size: 141 bytes --]

begin:vcard
fn:Alexandr Ogurtsov
n:Ogurtsov;Alexandr
note:Linux is very friendly it is just picky who its friends are
version:2.1
end:vcard

Re: [Sysadmins] PostgreSQL оптимизация

[Ivan Fedorov]

[-- Attachment #1: Type: text/plain, Size: 2417 bytes --]

Alexandr Ogurtsov <iscander.alt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
writes:

> Спасибо ещё раз, уточняю.
>> Ну вообще есть "золотое правило" - количество процессов Pg должно быть
>> равно количеству процессоров(ядер) * 2. То есть если у вас например 2
>> проца Intel Xeon 5430 с 4мя ядрами, то вам надо использовать до 16-ти
>> процессов Pg. 
>  Правило понял, но не совсем пойму как это управляется настройками PG
> это же явно не max_connections = параметр. :)
Никак по сути. Но вообще max_connections сделает именно это, просто на
мой взгляд это уже перебор! :)

> Я правильно понимаю что вы предлагаете запустить для примера с 8
> ядрами 16 отдельных "сущностей" PG?
Нет конечно...
>> Советую обратить внимание на PgBouncer.
> Обратил. И балансировать между 16 запущными postmaster процессами?
Ну на самом деле не 16, а до 16ти... :)
То есть если у вас небольшая нагрузка, то зачем держать 16-ть
процессов. :)

>> Ну сделайте на системе 1-2 гига shared memory (shm) и отдайте их
>> Pg. Тогда он почти гарантированно загрузить базу в память, а так
>> дисковый кэш в Linux работает вполне оптимально.
>
> Опять же притворюсь "танкистом" ;)  Это вы про отдельную точку
> монтирования для
>  /var/lib/postgresql/8.3/main или все же про настройки
> shared_buffers, temp_buffers, maintenance_work_mem, work_mem,
> effective_cache_size, max_fsm_*
Про настройки и sysctl... хотя как указали дальше, в 8.3 уже могли и
отрвать храниние данных в shm - я давно не проверял. У меня и дисковый
кэш прикрасно справляется.

[-- Attachment #2: Type: application/pgp-signature, Size: 196 bytes --]

Re: [Sysadmins] PostgreSQL оптимизация

[Alexandr Ogurtsov]

[-- Attachment #1: Type: text/plain, Size: 379 bytes --]

Снимаю шляпу!
> Для начала перейдите на 8.3 - оно реально быстрее!
Что было на 8.1
Total time for    20 requests:   55.4200sec Average: 2.770999sec
Что стало на 8.3
Total time for    20 requests:    2.4603sec Average: 0.123014sec
Это просто тест, на том самом "дохлом" стенде!

[-- Attachment #2: iscander_alt.vcf --]
[-- Type: text/x-vcard, Size: 148 bytes --]

begin:vcard
fn:Alexandr Ogurtsov
n:Ogurtsov;Alexandr
note:Linux is very friendly it is just picky who its friends are
version:2.1
end:vcard

Re: [Sysadmins] PostgreSQL оптимизация

[Evgeny Yugov]

Здравствуйте.

Много раз задавался подобным вопросом, теоретических материалов навалом.
Maxim Tyurin поделился общей формулой, которая годится в большинстве
случаев, надеюсь он не обидится если я её приведу тут:

""Стандартная настройка"

Среднестатическая настройка для максимальной производительности.

Берём размер памяти (RAM), ставим:

   * shared_buffers = 1/8 RAM или больше (но не более 1/4);
   * work_mem в 1/20 RAM;
   * maintenance_work_mem в 1/4;
   * max_fsm_relations в планируемое кол-во таблиц в базах * 1.5;
   * max_fsm_pages в max_fsm_relations * 2000;
   * fsync = true;
   * wal_sync_method = fdatasync;
   * commit_delay = от 10 до 100 ;
   * commit_siblings = от 5 до 10;
   * effective_cache_size = 0.9 от значения cached, которое показывает free;
   * random_page_cost = 2 для быстрых cpu, 4 для медленных;
   * cpu_tuple_cost = 0.001 для быстрых cpu, 0.01 для медленных;
   * cpu_index_tuple_cost = 0.0005 для быстрых cpu, 0.005 для медленных;

ну и autovacuum включить ещё. с analyze treshhold в 900, и vacuum
treshhold в 1800.

Если на сервере крутится еще что-то большое кроме PostgreSQL то нужно
изменить effective_cache_size (например 1С рекомендует устанавливать
этот параметр в RAM/2).

Выключить fsync можно только при использовании аппаратного рейда с BBU.

Также в рекомендациях от 1С встречается "установить enable_nestloop = off "

Не стоит так делать. Как, впрочем, и использовать другие костыли, для
того, чтобы обмануть планировщик запросов PostgreSQL. Планировщик у
PostgreSQL значительно умнее среднестатистического администратора БД, и
в 99.9% случаев срабатывает корректно.

Если же он работает некорректно, то нужно настраивать в первую очередь
effective_cache_size, random_page_cost и cpu*_cost. Чем меньше значения
этих параметров, тем больше будут использоваться агрессивные планы с
использованием индексов."

(с) Maxim Tyurin

Alexandr Ogurtsov пишет:
> Уважаемые коллеги, ищу совета по настройке производительности PostgreSQL
> 8.1.(Debian Etch)
<поскипанно>

-- 
Здесь могла быть ваша реклама...

Re: [Sysadmins] PostgreSQL оптимизация

[Maxim Tyurin]

Evgeny Yugov writes:

> (с) Maxim Tyurin

Это не мой цопирайт.
Надергано из разных источников.
Основа - письмо Alex Gorbachenko
-- 

With Best Regards, Maxim Tyurin
JID:	MrKooll@jabber.pibhe.com
   ___                                 
  / _ )__ _____  ___ ____ _______ _____
 / _  / // / _ \/ _ `/ _ `/ __/ // (_-<
/____/\_,_/_//_/\_, /\_,_/_/  \_,_/___/
               /___/