Re: synchronous_commit=off doesn't always return immediately

On Mon, Aug 3, 2009 at 2:14 AM, tomrevam<tomer@fabrix.tv> wrote:
>
>
>
> Tom Lane-2 wrote:
>>
>> tomrevam <tomer@fabrix.tv> writes:
>> It might help to increase wal_buffers.
>>
>
> Hi,
>
> I increased the wal_buffers to 2 MB. Initially this improved the performance
> very much, but after 4 days of continuous operation the system is back to
> very long inserts and updates (at least as bad as it was before).
> Is there a way to know what resource the DB is running out of?


I can almost guarantee you that you are i/o bound.  synchronous commit
helps, but is not magic: your single sata disk can only deliver about
100-200 iops and you are expecting more than that.  I think you have
only two options: add more/faster disks (and consider separating wal
from the data volume) or disable sync completely with fsync=off (which
is of course quite dangerous).

This can be confirmed with an iostat, noting the tps on each volume
and the iowait.

merlin