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
g...@2ndquadrant.com www.2ndQuadrant.com
