Re: Redesigning checkpoint_segments

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

>> One such surprise was that the conversion ran faster, even on a
>> "largish" database of around 200GB, with 3 checkpoint_segments
>> than with larger settings.
>
> !
>
> I can't account for that finding, because my experience is that
> small checkpoint_segments settings lead to *terrible* bulk
> restore performance.
>
> *scratches head*

Perhaps it was due to some of the "running with scissors" settings
we used for the upgrade process that we don't normally use, like
fsync = off and full_page_writes = off.  We also used a larger than
usual maintenance_work_mem which reduced disk sorts, possibly
helping the WAL files to remain cached on the controller.

Maybe it also helped keep data flowing to the actual disks, so that
it didn't alternate between "idle" and "glutted" states, although I
don't have any evidence to support that theory.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company