Re: [pgsql-ru-general] Re: [pgsql-ru-general] Тюнинг БД

Есть у нас БД у которой памяти 20Гиг и на диске она где-то 100 гиг.

настраивалась она просто: инсталл+pgtune.

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

в итоге доросли до того что примерно 10К рабочих мест эта БД
обслуживает в реальном времени и вот щас дошли до где-то 70% средней
загрузки CPU.

далее или сплитить по шардам или (пока) переехать на более мощный
хост + потюнить.

взяли новый сервак 128Гиг + контроллер с батарейкой
fsync практически мгновенный, если за размер кеша не вылазит.

ща просто наладили реплику и планируем переключиться на нее.

однако хочется еще попутно понастроить.

pgtune при 120 Гиг RAM предлагает:

+effective_cache_size = 88GB
+work_mem = 768MB
+shared_buffers = 30GB

то есть он на буфера предлагает 1/4 и 3/4 на кеш.

я думаю pgtune писали по идее умные люди, но может при большом объеме
RAM лучше поюзать другое соотношение?

PS:
max_connections = 200
у нас стоит.
мы используем в среднем где-то 40 соединений. на рестартах их
получается 80 и на миграциях кратковременно 160. ну и запас.

запросы в основном key-value + немного запросов за списками, но они
оптимизированы под использование индексов.

на что еще обратить внимание?

автовакуум тоже дефолтный

autovacuum_max_workers = 1
autovacuum_vacuum_cost_delay = 50ms

проект lowcost. до DBA не доросли пока :)

и еще

wal_keep_segments = 4096

поставил - потому что экспериментируя с репликами иногда не успеваешь
и чтобы реплику перезапустить надо rsync'ать.
получается сегменты займут 64Гигабайта.

в принципе фигня, вопрос clean-процесс на таком объеме не будет
втупливать? можно сюда большое число такое вписать?
раньше стояло 64. игры с репликами (типа собрать статистику) часто
приводили к rsync.
--

. ''`. Dmitry E. Oboukhov
: :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537

Привет.

> 13 июня 2015 г., в 12:05, Dmitry E. Oboukhov <unera(at)debian(dot)org> написал(а):
>
> Есть у нас БД у которой памяти 20Гиг и на диске она где-то 100 гиг.
>
> настраивалась она просто: инсталл+pgtune.
>
> не тюнили, но по возрастанию нагрузки анализировали что грузит и
> приводили все запросы к тому чтоб в частых запросах всегда выборка шла
> из индекса, а в редких - пофиг, на то они и редкие.
>
> в итоге доросли до того что примерно 10К рабочих мест эта БД
> обслуживает в реальном времени и вот щас дошли до где-то 70% средней
> загрузки CPU.

Если вы стали упираться в процессор, то уверены ли вы в том, что больше памяти и контроллер с батарейкой вас спасут? Эти два средства всё-таки про оптимизацию I/O, а не CPU. Процессор тратится в iowait/system/user?

>
> далее или сплитить по шардам или (пока) переехать на более мощный
> хост + потюнить.
>
> взяли новый сервак 128Гиг + контроллер с батарейкой
> fsync практически мгновенный, если за размер кеша не вылазит.
>
> ща просто наладили реплику и планируем переключиться на нее.
>
> однако хочется еще попутно понастроить.
>
> pgtune при 120 Гиг RAM предлагает:
>
> +effective_cache_size = 88GB
> +work_mem = 768MB
> +shared_buffers = 30GB
>
> то есть он на буфера предлагает 1/4 и 3/4 на кеш.
>
> я думаю pgtune писали по идее умные люди, но может при большом объеме
> RAM лучше поюзать другое соотношение?

Я не знаю, кто писал pgtune, но я бы так делать не стал. Тут есть два варианта:
1. Классический олдскульный. Под shared_buffers надо отдавать 25% всей оперативки, но не более 8 ГБ. Работает во всех без исключения случаях.
2. Если база целиком влезает в память (100 ГБ), то можно 100 ГБ отрезать под shared_buffers. Тогда целиком и полностью кэшированием будет заниматься база, а не page cache операционной системы. Это большой плюс, но такой подход имеет ряд недостатков. Например, когда рабочий набор данных перестанет влезать в shared_buffers, начнутся вот такие проблемы [0]. Checkpoint’ы при таком подходе должны будут выполняться нечасто, а потому crash recovery в случае факапа будет занимать сильно больше времени.

Размер work_mem целиком и полностью зависит от сложности запросов. Если все они близки к key/value по индексу без сортировки больших объёмов данных, то больше 16 МБ, кажется, ставить нет смысла.

>
> PS:
> max_connections = 200
> у нас стоит.
> мы используем в среднем где-то 40 соединений. на рестартах их
> получается 80 и на миграциях кратковременно 160. ну и запас.
>
>
> запросы в основном key-value + немного запросов за списками, но они
> оптимизированы под использование индексов.
>
> на что еще обратить внимание?

Надо забыть про использование pgtune и почитать, зачем каждый из параметров нужен - это не займёт больше пары часов. Например, из письма видно, что ты плохо понимаешь суть параметра effective_cache_size.

>
> автовакуум тоже дефолтный
>
> autovacuum_max_workers = 1
> autovacuum_vacuum_cost_delay = 50ms
>

В этом месте должен прийти Илья Космодемьянский и рассказать тебе, что ты не прав :)

> проект lowcost. до DBA не доросли пока :)
>
> и еще
>
> wal_keep_segments = 4096
>
> поставил - потому что экспериментируя с репликами иногда не успеваешь
> и чтобы реплику перезапустить надо rsync’ать.

archive_command и restore_command тебя спасут. А после переезда на 9.4 сможешь решить эту проблему с помощью replication slots [1].

> получается сегменты займут 64Гигабайта.
>
> в принципе фигня, вопрос clean-процесс на таком объеме не будет
> втупливать? можно сюда большое число такое вписать?

Можно, не будет.

[0] http://www.postgresql.org/message-id/0DDFB621-7282-4A2B-8879-A47F7CECBCE4@simply.name <http://www.postgresql.org/message-id/0DDFB621-7282-4A2B-8879-A47F7CECBCE4@simply.name>
[1] http://www.postgresql.org/docs/9.4/static/warm-standby.html#STREAMING-REPLICATION-SLOTS <http://www.postgresql.org/docs/9.4/static/warm-standby.html#STREAMING-REPLICATION-SLOTS>
> раньше стояло 64. игры с репликами (типа собрать статистику) часто
> приводили к rsync.
> --
>
> . ''`. Dmitry E. Oboukhov
> : :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
> `. `~’ GPGKey: 1024D / F8E26537 2006-11-21
> `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537

--
Да пребудет с вами сила…
https://simply.name/ru

> 20 июня 2015 г., в 15:19, Vladimir Borodin <root(at)simply(dot)name> написал(а):
>
> Привет.
>
>> 13 июня 2015 г., в 12:05, Dmitry E. Oboukhov <unera(at)debian(dot)org <mailto:unera(at)debian(dot)org>> написал(а):
>>
>> Есть у нас БД у которой памяти 20Гиг и на диске она где-то 100 гиг.
>>
>> настраивалась она просто: инсталл+pgtune.
>>
>> не тюнили, но по возрастанию нагрузки анализировали что грузит и
>> приводили все запросы к тому чтоб в частых запросах всегда выборка шла
>> из индекса, а в редких - пофиг, на то они и редкие.
>>
>> в итоге доросли до того что примерно 10К рабочих мест эта БД
>> обслуживает в реальном времени и вот щас дошли до где-то 70% средней
>> загрузки CPU.
>
> Если вы стали упираться в процессор, то уверены ли вы в том, что больше памяти и контроллер с батарейкой вас спасут? Эти два средства всё-таки про оптимизацию I/O, а не CPU. Процессор тратится в iowait/system/user?
>
>>
>> далее или сплитить по шардам или (пока) переехать на более мощный
>> хост + потюнить.
>>
>> взяли новый сервак 128Гиг + контроллер с батарейкой
>> fsync практически мгновенный, если за размер кеша не вылазит.
>>
>> ща просто наладили реплику и планируем переключиться на нее.
>>
>> однако хочется еще попутно понастроить.
>>
>> pgtune при 120 Гиг RAM предлагает:
>>
>> +effective_cache_size = 88GB
>> +work_mem = 768MB
>> +shared_buffers = 30GB
>>
>> то есть он на буфера предлагает 1/4 и 3/4 на кеш.
>>
>> я думаю pgtune писали по идее умные люди, но может при большом объеме
>> RAM лучше поюзать другое соотношение?
>
> Я не знаю, кто писал pgtune, но я бы так делать не стал. Тут есть два варианта:
> 1. Классический олдскульный. Под shared_buffers надо отдавать 25% всей оперативки, но не более 8 ГБ. Работает во всех без исключения случаях.
> 2. Если база целиком влезает в память (100 ГБ), то можно 100 ГБ отрезать под shared_buffers. Тогда целиком и полностью кэшированием будет заниматься база, а не page cache операционной системы. Это большой плюс, но такой подход имеет ряд недостатков. Например, когда рабочий набор данных перестанет влезать в shared_buffers, начнутся вот такие проблемы [0]. Checkpoint’ы при таком подходе должны будут выполняться нечасто, а потому crash recovery в случае факапа будет занимать сильно больше времени.

И да, с 9.3 про второй вариант лучше забыть, потому что отрезая 100 ГБ под shared_buffers без использования huge pages (а в 9.3 их никак использовать нельзя) на базе с 200 соединений ты потеряешь на page tables около 5 ГБ памяти.

>
> Размер work_mem целиком и полностью зависит от сложности запросов. Если все они близки к key/value по индексу без сортировки больших объёмов данных, то больше 16 МБ, кажется, ставить нет смысла.
>
>>
>> PS:
>> max_connections = 200
>> у нас стоит.
>> мы используем в среднем где-то 40 соединений. на рестартах их
>> получается 80 и на миграциях кратковременно 160. ну и запас.
>>
>>
>> запросы в основном key-value + немного запросов за списками, но они
>> оптимизированы под использование индексов.
>>
>> на что еще обратить внимание?
>
> Надо забыть про использование pgtune и почитать, зачем каждый из параметров нужен - это не займёт больше пары часов. Например, из письма видно, что ты плохо понимаешь суть параметра effective_cache_size.
>
>>
>> автовакуум тоже дефолтный
>>
>> autovacuum_max_workers = 1
>> autovacuum_vacuum_cost_delay = 50ms
>>
>
> В этом месте должен прийти Илья Космодемьянский и рассказать тебе, что ты не прав :)
>
>> проект lowcost. до DBA не доросли пока :)
>>
>> и еще
>>
>> wal_keep_segments = 4096
>>
>> поставил - потому что экспериментируя с репликами иногда не успеваешь
>> и чтобы реплику перезапустить надо rsync’ать.
>
> archive_command и restore_command тебя спасут. А после переезда на 9.4 сможешь решить эту проблему с помощью replication slots [1].
>
>> получается сегменты займут 64Гигабайта.
>>
>> в принципе фигня, вопрос clean-процесс на таком объеме не будет
>> втупливать? можно сюда большое число такое вписать?
>
> Можно, не будет.
>
> [0] http://www.postgresql.org/message-id/0DDFB621-7282-4A2B-8879-A47F7CECBCE4@simply.name <http://www.postgresql.org/message-id/0DDFB621-7282-4A2B-8879-A47F7CECBCE4@simply.name>
> [1] http://www.postgresql.org/docs/9.4/static/warm-standby.html#STREAMING-REPLICATION-SLOTS <http://www.postgresql.org/docs/9.4/static/warm-standby.html#STREAMING-REPLICATION-SLOTS>
>> раньше стояло 64. игры с репликами (типа собрать статистику) часто
>> приводили к rsync.
>> --
>>
>> . ''`. Dmitry E. Oboukhov
>> : :’ : email: unera(at)debian(dot)org <mailto:unera(at)debian(dot)org> jabber://UNera(at)uvw(dot)ru <jabber://UNera(at)uvw(dot)ru>
>> `. `~’ GPGKey: 1024D / F8E26537 2006-11-21
>> `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
>
>
> --
> Да пребудет с вами сила…
> https://simply.name/ru <https://simply.name/ru>

--
Да пребудет с вами сила…
https://simply.name/ru

20 июня 2015 г., 15:19 пользователь Vladimir Borodin <root(at)simply(dot)name>
написал:

> автовакуум тоже дефолтный
>
> autovacuum_max_workers = 1
> autovacuum_vacuum_cost_delay = 50ms
>
>
> В этом месте должен прийти Илья Космодемьянский и рассказать тебе, что ты
> не прав :)
>

А как правильно? Где почитать?

>
> проект lowcost. до DBA не доросли пока :)
>
> и еще
>
> wal_keep_segments = 4096
>
> поставил - потому что экспериментируя с репликами иногда не успеваешь
> и чтобы реплику перезапустить надо rsync’ать.
>
>
> archive_command и restore_command тебя спасут. А после переезда на 9.4
> сможешь решить эту проблему с помощью replication slots [1].
>
> получается сегменты займут 64Гигабайта.
>
>
> в принципе фигня, вопрос clean-процесс на таком объеме не будет
> втупливать? можно сюда большое число такое вписать?
>
>
> Можно, не будет.
>
> [0]
> http://www.postgresql.org/message-id/0DDFB621-7282-4A2B-8879-A47F7CECBCE4@simply.name
> [1]
> http://www.postgresql.org/docs/9.4/static/warm-standby.html#STREAMING-REPLICATION-SLOTS
>
> раньше стояло 64. игры с репликами (типа собрать статистику) часто
> приводили к rsync.
>
> --
>
> . ''`. Dmitry E. Oboukhov
> : :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
> `. `~’ GPGKey: 1024D / F8E26537 2006-11-21
> `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
>
>
>
> --
> Да пребудет с вами сила…
> https://simply.name/ru
>
>

--
Your faithfully
Alexey Kolpakov

Отсюда можно начать.

http://www.highload.ru/2014/abstracts/1541.html

2015-07-07 14:47 GMT+03:00 Alexey Kolpakov <al(dot)kolpak(at)gmail(dot)com>:

>
>
> 20 июня 2015 г., 15:19 пользователь Vladimir Borodin <root(at)simply(dot)name>
> написал:
>
>> автовакуум тоже дефолтный
>>
>> autovacuum_max_workers = 1
>> autovacuum_vacuum_cost_delay = 50ms
>>
>>
>> В этом месте должен прийти Илья Космодемьянский и рассказать тебе, что ты
>> не прав :)
>>
>
> А как правильно? Где почитать?
>
>
>>
>> проект lowcost. до DBA не доросли пока :)
>>
>> и еще
>>
>> wal_keep_segments = 4096
>>
>> поставил - потому что экспериментируя с репликами иногда не успеваешь
>> и чтобы реплику перезапустить надо rsync’ать.
>>
>>
>> archive_command и restore_command тебя спасут. А после переезда на 9.4
>> сможешь решить эту проблему с помощью replication slots [1].
>>
>> получается сегменты займут 64Гигабайта.
>>
>>
>> в принципе фигня, вопрос clean-процесс на таком объеме не будет
>> втупливать? можно сюда большое число такое вписать?
>>
>>
>> Можно, не будет.
>>
>> [0]
>> http://www.postgresql.org/message-id/0DDFB621-7282-4A2B-8879-A47F7CECBCE4@simply.name
>> [1]
>> http://www.postgresql.org/docs/9.4/static/warm-standby.html#STREAMING-REPLICATION-SLOTS
>>
>> раньше стояло 64. игры с репликами (типа собрать статистику) часто
>> приводили к rsync.
>>
>> --
>>
>> . ''`. Dmitry E. Oboukhov
>> : :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
>> `. `~’ GPGKey: 1024D / F8E26537 2006-11-21
>> `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
>>
>>
>>
>> --
>> Да пребудет с вами сила…
>> https://simply.name/ru
>>
>>
>
>
> --
> Your faithfully
> Alexey Kolpakov
>

--
Regards, Andrey Lizenko