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