2012/1/22 Tomas Vondra <tv@xxxxxxxx>: > That's suspiciously similar to the checkpoint timeout (which was set to > 4 minutes), but why should this matter for minimal WAL level and not for > archive? I went through and looked at all the places where we invoke XLogIsNeeded(). When XLogIsNeeded(), we: 1. WAL log creation of the _init fork of an unlogged table or an index on an unlogged table (otherwise, an fsync is enough) 2. WAL log index builds 3. WAL log changes to max_connections, max_prepared_xacts, max_locks_per_xact, and/or wal_level 4. skip calling posix_fadvise(POSIX_FADV_DONTNEED) when closing a WAL file 5. skip supplying O_DIRECT when writing WAL, if wal_sync_method is open_sync or open_datasync 6. refuse to create named restore points 7. WAL log CLUSTER 8. WAL log COPY FROM into a newly created/truncated relation 9. WAL log ALTER TABLE .. SET TABLESPACE 9. WAL log cleanup info before doing an index vacuum (this one should probably be changed to happen only in HS mode) 10. WAL log SELECT INTO It's hard to see how generating more WAL could cause a performance improvement, unless there's something about full page flushes being more efficient than partial page flushes or something like that. But none of the stuff above looks likely to happen very often anyway. But items #4 and #5 on that list like things that could potentially be causing a problem - if WAL files are being reused regularly, then calling POSIX_FADV_DONTNEED on them could represent a regression. It might be worth compiling with POSIX_FADV_DONTNEED undefined and see whether that changes anything. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
