Re: High checkpoint_segments

Поиск
Список
Период
Сортировка
От Albe Laurenz
Тема Re: High checkpoint_segments
Дата
Msg-id D960CB61B694CF459DCFB4B0128514C2077EBC45@exadv11.host.magwien.gv.at
обсуждение исходный текст
Ответ на High checkpoint_segments  (Jay Levitt <jay.levitt@gmail.com>)
Список pgsql-general
Jay Levitt wrote:
> We need to do a few bulk updates as Rails migrations.  We're a typical
> read-mostly web site, so at the moment, our checkpoint settings and
WAL are
> all default (3 segments, 5 min, 16MB), and updating a million rows
takes 10
> minutes due to all the checkpointing.
>
> We have no replication or hot standbys.  As a consumer-web startup,
with no
> SLA, and not a huge database, and if we ever do have to recover from
> downtime it's ok if it takes longer.. is there a reason NOT to always
run
> with something like checkpoint_segments = 1000, as long as I leave the
> timeout at 5m?

There's nothing wrong with the idea except for the amount of WAL and a
huge checkpoint that can stall your system for a while in a worst-case
scenario.  You can't get rid of checkpoint I/O completely.

I'd tune to a more conservative value, maybe 30 or at most 100 and see
if that solves your problem.  Check statistics to see if checkpoints
are time-driven or not.  As soon as almost all checkpoints are time-
driven, further raising of checkpoint_segments won't do anything for
you.

Yours,
Laurenz Albe

В списке pgsql-general по дате отправления:

Предыдущее
От: Bartosz Dmytrak
Дата:
Сообщение: Re: Easy form of "insert if it isn't already there"?
Следующее
От: Andre Lopes
Дата:
Сообщение: Re: Backup database remotely