On 10/10/2011 12:14 PM, Leonardo Francalanci wrote:
>
>> database makes the fsync call, and suddenly the OS wants to flush 2-6GB of data
>> straight to disk. Without that background trickle, you now have a flood that
>> only the highest-end disk controller or a backing-store full of SSDs or PCIe
>> NVRAM could ever hope to absorb.
>>
>
> Isn't checkpoint_completion_target supposed to deal exactly with that problem?
>
checkpoint_completion_targets spreads out the writes to disk.
PostgreSQL doesn't make any attempt yet to spread out the sync calls.
On a busy server, what can happen is that the whole OS write cache fills
with dirty data--none of which is written out to disk because of the
high kernel threshold--and then it all slams onto disk fast once the
checkpoint starts executing sync calls. Lowering the size of the Linux
write cache helps with that a lot, but can't quite eliminate the problem.
> Plus: if 2-6GB is too much, why not decrease checkpoint_segments? Or
> checkpoint_timeout?
>
Making checkpoints really frequent increases total disk I/O, both to the
database and to the WAL, significantly. You don't want to do that if
there's another way to achieve the same goal without those costs, which
is what some kernel tuning can do here. Just need to be careful not to
go too far; some write caching at the OS level helps a lot, too.
> I'm not saying that those kernel parameters are "useless"; I'm saying
> they are used
> in the same way as the checkpoint_segments, checkpoint_timeout and
> checkpoint_completion_target are used by postgresql; and on a postgresql-only system
> I would rather have postgresql look after the fsync calls, not the OS.
>
Except that PostgreSQL doesn't look after the fsync calls yet. I wrote
a patch for 9.1 that spread out the sync calls, similarly to how the
writes are spread out now. I wasn't able to prove an improvement
sufficient to commit the result. In the Linux case, the OS has more
information to work with about how to schedule I/O efficiently given how
the hardware is acting, and it's not possible for PostgreSQL to know all
that--not without duplicating a large portion of the kernel development
work at least. Right now, relying the kernel means that any
improvements there magically apply to any PostgreSQL version. So far
the results there have been beating out improvements made to the
database fast enough that it's hard to innovate in this area within
Postgres.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us