I have put pg_xlog back to the ext3 partition, but nothing changed.
I have also switched off sync_commit, but nothing. This is quite interesting...
Here is a graph about the transaction time (sync_commit off, pg_xlog on separate file system):
Graph On the graph the red line up there is the tranaction/sec, it is about 110, and does not get lower as the transaction time gets higher.
Based on this, am I right that it is not the commit, that causes these high transaction times?
Kernel version is 2.6.32.
Any idea is appreciated.
Thanks,
Otto
2011/12/8 Bob Lunney
<bob_lunney@yahoo.com>Otto,
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?
Thanks,
Otto
2011/12/8 Marti Raudsepp
<marti@juffo.org>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...
(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.
Regards,
Marti