Separate the pg_xlog directory onto its own filesystem and retry your tests.
Bob Lunney
From: Havasvölgyi Ottó <havasvolgyi.otto@gmail.com> To: Marti Raudsepp <marti@juffo.org> Cc: Aidan Van Dyk <aidan@highrise.ca>; pgsql-performance@postgresql.org Sent: Thursday, December 8, 2011 9:48 AM Subject: Re: [PERFORM] Response time increases over time
I have moved the data directory (xlog, base, global, and everything) to an ext4 file system. The result hasn't changed unfortuately. With the same load test the average response time: 80ms; from 40ms to 120 ms everything occurs. This ext4 has default settings in fstab. Have you got any other idea what is going on here?
On Thu, Dec 8, 2011 at 06:37, Aidan Van Dyk <aidan@highrise.ca> wrote: > Let me guess, debian squeeze, with data and xlog on both on a single > ext3 filesystem, and the fsync done by your commit (xlog) is flushing > all the dirty data of the entire filesystem (including PG data writes) > out before it can return...
This is fixed with the data=writeback mount option, right? (If it's the root file system, you need to add rootfsflags=data=writeback to your kernel boot flags)
While this setting is safe and recommended for PostgreSQL and other transactional databases, it can cause garbage to appear in recently written files after a crash/power loss -- for applications that don't correctly fsync data to disk.
