On 2014-10-20 21:41:26 +0200, jes...@krogh.cc wrote: > > > On 2014-10-20 21:03:59 +0200, jes...@krogh.cc wrote: > >> One of our "production issues" is that the system generates lots of > >> wal-files, lots is like 151952 files over the last 24h, which is about > >> 2.4TB worth of WAL files. I wouldn't say that isn't an issue by itself, > >> but the system does indeed work fine. We do subsequently gzip the files > >> to limit actual disk-usage, this makes the files roughly 30-50% in size.
I'm a bit doubtful that 16MB vs., say, 64MB files really changes anything substantial for you. If it indeed is a problem, it's simple enough to join the files temporarily? > > Have you analyzed what the source of that volume is? Which version of > > postgres are you using? What's your checkpoint_timeout/segments > > settings? > > Suggestions are surely welcome. Once you're on 9.3 I'd suggest using pg_xlogdump --stats on it. There's a backport of the facility for 9.3 (looking somewhat different than what is now in 9.5) at http://archives.postgresql.org/message-id/CABRT9RAzGowqLFcEE8aF6VdPoFEy%2BP9gmu7ktGRzw0dgRwVr9Q%40mail.gmail.com That'd tell you a fair bit more. It's noticeably harder to backport to < 9.3. > I do suspect the majority is from 30 concurrent processes updating an > 506GB GIN index, but it would be nice to confirm that. There is also a > message-queue in the DB with a fairly high turnaround. A 506GB GIN index? Uh, interesting :). What's it used for? Trigrams? I'd suspect that the message queue isn't the primary culprit, but it's hard to say for sure. > Currently PG 9.2 moving to 9.3 hopefully before end-of-year, > checkpoint_timeout = 30min, checkpoint_segments = 4096. Generally a high checkpoint_timeout can significantly reduce the WAL volume because of fewer full page writes. I've seen cases where spacing checkpoint further apart by a factor of two reduced the overall WAL volume by more than two. > According to logs checkpoints are roughly 15 minutes apart. Can you show log_checkpoints output? 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