On 2014-04-14 16:22:48 -0700, Joe Conway wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On 04/14/2014 04:17 PM, Tom Lane wrote: > > Andres Freund <and...@2ndquadrant.com> writes: > >> On 2014-04-14 14:33:03 -0700, Joe Conway wrote: > >>> checkpoint_segments = 96 checkpoint_timeout = 10min > > > >> I bet you'll see noticeably - while still not great - better > >> performance by setting checkpoint_timeout to an hour (with a > >> corresponding increase in checkpoint_segments). Have you checked > >> how often checkpoints are actually created? I'd bet it's far more > >> frequent than every 10min with that _segments setting and such a > >> load. > > > > My thoughts exactly. It's not hard to blow through WAL at > > multiple megabytes per second with modern machines. I'd turn on > > checkpoint logging and then do whatever you need to do to get the > > actual intercheckpoint time up to 10-15 minutes at least. > > That'll help performance, but lets say I generally keep WAL files for > PITR and don't turn that off before starting -- shouldn't I be very > surprised to need over 3TB of archive storage when loading a 50GB > table with a couple of indexes?
The point is that more frequent checkpoints will increase the WAL volume *significantly* because more full page writes will have to be generated. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
