Re: Response time increases over time

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 <http://uploadpic.org/v.php?img=qIjfWBkHyE>
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(at)yahoo(dot)com>

> Otto,
>
> Separate the pg_xlog directory onto its own filesystem and retry your
> tests.
>
> Bob Lunney
>
> ------------------------------
> *From:* Havasvölgyi Ottó <havasvolgyi(dot)otto(at)gmail(dot)com>
> *To:* Marti Raudsepp <marti(at)juffo(dot)org>
> *Cc:* Aidan Van Dyk <aidan(at)highrise(dot)ca>; pgsql-performance(at)postgresql(dot)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(at)juffo(dot)org>
>
> On Thu, Dec 8, 2011 at 06:37, Aidan Van Dyk <aidan(at)highrise(dot)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.
>
> Regards,
> Marti
>
>
>
>
>