* [Sysadmins] PostgreSQL оптимизация
@ 2008-10-09 11:44 Alexandr Ogurtsov
2008-10-13 3:42 ` Evgeny Yugov
0 siblings, 2 replies; 10+ messages in thread
From: Alexandr Ogurtsov @ 2008-10-09 11:44 UTC (permalink / raw)
To: ALT Linux sysadmin discuss
[-- 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
^ permalink raw reply [flat|nested] 10+ messages in thread
* Re: [Sysadmins] PostgreSQL оптимизация
@ 2008-10-10 8:39 ` Alexandr Ogurtsov
2008-10-10 9:34 ` Ivan Fedorov
2008-10-10 16:06 ` Alexandr Ogurtsov
1 sibling, 1 reply; 10+ messages in thread
From: Alexandr Ogurtsov @ 2008-10-10 8:39 UTC (permalink / raw)
To: Ivan Fedorov, ALT Linux sysadmin discuss
[-- 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
^ permalink raw reply [flat|nested] 10+ messages in thread
* Re: [Sysadmins] PostgreSQL оптимизация
2008-10-10 8:39 ` Alexandr Ogurtsov
@ 2008-10-10 9:34 ` Ivan Fedorov
2008-10-10 10:10 ` Alexandr Ogurtsov
2008-10-10 11:07 ` Andrey Chichak
0 siblings, 2 replies; 10+ messages in thread
From: Ivan Fedorov @ 2008-10-10 9:34 UTC (permalink / raw)
To: ALT Linux sysadmin discuss
[-- 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 --]
^ permalink raw reply [flat|nested] 10+ messages in thread
* Re: [Sysadmins] PostgreSQL оптимизация
2008-10-10 9:34 ` Ivan Fedorov
@ 2008-10-10 10:10 ` Alexandr Ogurtsov
2008-10-10 12:27 ` Ivan Fedorov
2008-10-10 11:07 ` Andrey Chichak
1 sibling, 1 reply; 10+ messages in thread
From: Alexandr Ogurtsov @ 2008-10-10 10:10 UTC (permalink / raw)
To: ALT Linux sysadmin discuss
[-- 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
^ permalink raw reply [flat|nested] 10+ messages in thread
* Re: [Sysadmins] PostgreSQL оптимизация
2008-10-10 9:34 ` Ivan Fedorov
2008-10-10 10:10 ` Alexandr Ogurtsov
@ 2008-10-10 11:07 ` Andrey Chichak
2008-10-10 11:21 ` Alexandr Ogurtsov
1 sibling, 1 reply; 10+ messages in thread
From: Andrey Chichak @ 2008-10-10 11:07 UTC (permalink / raw)
To: ALT Linux sysadmin discuss
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
^ permalink raw reply [flat|nested] 10+ messages in thread
* Re: [Sysadmins] PostgreSQL оптимизация
2008-10-10 11:07 ` Andrey Chichak
@ 2008-10-10 11:21 ` Alexandr Ogurtsov
0 siblings, 0 replies; 10+ messages in thread
From: Alexandr Ogurtsov @ 2008-10-10 11:21 UTC (permalink / raw)
To: chch, ALT Linux sysadmin discuss
[-- 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
^ permalink raw reply [flat|nested] 10+ messages in thread
* Re: [Sysadmins] PostgreSQL оптимизация
2008-10-10 10:10 ` Alexandr Ogurtsov
@ 2008-10-10 12:27 ` Ivan Fedorov
0 siblings, 0 replies; 10+ messages in thread
From: Ivan Fedorov @ 2008-10-10 12:27 UTC (permalink / raw)
To: sysadmins
[-- 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 --]
^ permalink raw reply [flat|nested] 10+ messages in thread
* Re: [Sysadmins] PostgreSQL оптимизация
2008-10-10 8:39 ` Alexandr Ogurtsov
@ 2008-10-10 16:06 ` Alexandr Ogurtsov
1 sibling, 0 replies; 10+ messages in thread
From: Alexandr Ogurtsov @ 2008-10-10 16:06 UTC (permalink / raw)
To: ALT Linux sysadmin discuss
[-- 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
^ permalink raw reply [flat|nested] 10+ messages in thread
* Re: [Sysadmins] PostgreSQL оптимизация
2008-10-09 11:44 [Sysadmins] PostgreSQL оптимизация Alexandr Ogurtsov
@ 2008-10-13 3:42 ` Evgeny Yugov
2008-10-13 10:09 ` Maxim Tyurin
1 sibling, 1 reply; 10+ messages in thread
From: Evgeny Yugov @ 2008-10-13 3:42 UTC (permalink / raw)
To: sysadmins
Здравствуйте.
Много раз задавался подобным вопросом, теоретических материалов навалом.
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)
<поскипанно>
--
Здесь могла быть ваша реклама...
^ permalink raw reply [flat|nested] 10+ messages in thread
* Re: [Sysadmins] PostgreSQL оптимизация
2008-10-13 3:42 ` Evgeny Yugov
@ 2008-10-13 10:09 ` Maxim Tyurin
0 siblings, 0 replies; 10+ messages in thread
From: Maxim Tyurin @ 2008-10-13 10:09 UTC (permalink / raw)
To: ALT Linux sysadmin discuss
Evgeny Yugov writes:
> (с) Maxim Tyurin
Это не мой цопирайт.
Надергано из разных источников.
Основа - письмо Alex Gorbachenko
--
With Best Regards, Maxim Tyurin
JID: MrKooll@jabber.pibhe.com
___
/ _ )__ _____ ___ ____ _______ _____
/ _ / // / _ \/ _ `/ _ `/ __/ // (_-<
/____/\_,_/_//_/\_, /\_,_/_/ \_,_/___/
/___/
^ permalink raw reply [flat|nested] 10+ messages in thread
end of thread, other threads:[~2008-10-13 10:09 UTC | newest]
Thread overview: 10+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2008-10-09 11:44 [Sysadmins] PostgreSQL оптимизация Alexandr Ogurtsov
2008-10-10 8:39 ` Alexandr Ogurtsov
2008-10-10 9:34 ` Ivan Fedorov
2008-10-10 10:10 ` Alexandr Ogurtsov
2008-10-10 12:27 ` Ivan Fedorov
2008-10-10 11:07 ` Andrey Chichak
2008-10-10 11:21 ` Alexandr Ogurtsov
2008-10-10 16:06 ` Alexandr Ogurtsov
2008-10-13 3:42 ` Evgeny Yugov
2008-10-13 10:09 ` Maxim Tyurin
ALT Linux sysadmins discussion
This inbox may be cloned and mirrored by anyone:
git clone --mirror http://lore.altlinux.org/sysadmins/0 sysadmins/git/0.git
# If you have public-inbox 1.1+ installed, you may
# initialize and index your mirror using the following commands:
public-inbox-init -V2 sysadmins sysadmins/ http://lore.altlinux.org/sysadmins \
sysadmins@lists.altlinux.org sysadmins@lists.altlinux.ru sysadmins@lists.altlinux.com
public-inbox-index sysadmins
Example config snippet for mirrors.
Newsgroup available over NNTP:
nntp://lore.altlinux.org/org.altlinux.lists.sysadmins
AGPL code for this site: git clone https://public-inbox.org/public-inbox.git