Re: Excessive (and slow) fsync() within single transaction

Stephen Tyler wrote:
> I don't understand how maxwritten_clean could be as high as 95058, and
> increment at more than 1 per second. This is a process count, not a
> buffer count? How often is the background cleaner launched? Does
> that mean I need to massively increase bgwriter_lru_maxpages, and
> other bgwriter params? They are currently default values.
maxwritten_clean is a straight out count of times that even happened,
not a buffer count.
The background writer runs at whatever frequency bgwriter_delay is set
to, which defaults to 200ms for 5 executions/second. You could increase
bgwriter_lru_maxpages and the rest, but those actually make the system
less efficient if you're having trouble just keeping up with checkpoint
I/O. They're more aimed to improve latency on systems where there's
enough I/O to spare that you can write ahead a bit even if costs you an
occasional penalty. In your situation, I'd turn bgwriter_lru_maxpages=0
and just get rid of it altogether--it's really unlike it's helping, and
it might be making things a tiny bit worse.

> checkpoint_segments = 128
> checkpoint_segments is large, but in the suggested range of 32 to 256
> for large, write-heavy, batch-oriented systems. I've used smaller
> values in the past, but I got lots of checkpoint warnings in the console.
I think that given your backing disk situation and the situation you're
in, you could easily justify 256 on your system.

> wal_buffers might be a bit high. I don't fully understand the
> implications, and how to tune this value, even after reading the
> tuning guides.
It's not documented very well, that's for sure. Setting it too high
won't hurt you, just takes up a tiny amount of RAM. I've been talking
recently with people about increasing the standard recommendation for
that to 16MB.

> *) disable fsync...risky, but I bet will make the problem go away
>
>
> I'm very tempted to live dangerously. I would like to actually turn
> off fsync's during a transaction, and just use the fsync at the very
> end of the transaction to confirm that the transaction will be permanent.
The only other option that might help you out here is to turn off
synchronous_commit for either the whole system, or just for transactions
where durability isn't that important. That introduces a small risk of
data loss, but not the risk of corruption that turning fsync off
altogether does. Basically, it reduces the number of fsync's from
transaction commits to be a fixed number per unit of time, rather than
being proportional to the number of commits.

--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com