I'm hoping that through compare/contrast we might help someone start closer to their own best values.... >>> Scott Carey <scott@xxxxxxxxxxxxxxxxx> wrote: > Tests with writes can trigger it earlier if combined with bad dirty_buffers > settings. We've never, ever modified dirty_buffers settings from defaults. > The root of the problem is that the Linux paging algorithm estimates that > I/O for file read access is as costly as I/O for paging. A reasonable > assumption for a desktop, a ridiculously false assumption for a large > database with high capacity DB file I/O and a much lower capability swap > file. Our swap file is not on lower speed drives. > If you do have enough other applications that are idle that take up RAM that > should be pushed out to disk from time to time (perhaps your programs that > are doing the bulk loading?) a higher value is useful. Bulk loading was ssh cat | psql. > The more RAM you have and the larger your postgres memory usage, the lower > the swappiness value should be. I think the test environment had 8 GB RAM with 256 MB in shared_buffers. For the conversion we had high work_mem and maintenance_work_mem settings, and we turned fsync off, along with a few other settings we would never using during production. > I currently use a value of 1, on a 32GB machine, and about 600MB of 'stuff' > gets paged out normally, 1400MB under heavy load. Outside of bulk load, we've rarely seen anything swap, even under load. > ***For a bulk load database, one is optimizing for _writes_ and extra page > cache doesn't help writes like it does reads.*** I'm thinking that it likely helps when indexing tables for which data has recently been loaded. It also might help minimize head movement and/or avoid the initial disk hit for a page which subsequently get hint bits set . > Like all of these settings, tune to your application and test. We sure seem to agree on that. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
