Skip site navigation (1) Skip section navigation (2)

Re: how unsafe (or worst scenarios) when setting fsync OFF for postgresql

From: "Guoping Zhang" <guoping(dot)zhang(at)nec(dot)com(dot)au>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-performance(at)postgresql(dot)org>,"'Guoping Zhang (E-mail)'" <guopingz(at)nstc(dot)nec(dot)com(dot)au>
Subject: Re: how unsafe (or worst scenarios) when setting fsync OFF for postgresql
Date: 2006-04-28 04:43:26
Message-ID: 004101c66a7e$4ab95000$74304c93@eddy (view raw or flat)
Thread:
Lists: pgsql-performance
Hi, Tom,

Thanks for the reply.

a) The tests consists of ten thousands very small transactions, which are
not grouped, that is why so slow with compare to set fsync off.
b) we are using Solaris 10 on a SUN Fire 240 SPARC machine with a latest
postgresql release (8.1.3)
c) wal_sync_method is set to 'open_datasync', which is fastest among the
four, right?
d) wal_buffers set to 32

Looks like, if we have to set fsync be true, we need to modify our
application.

Thanks and regards,
Guoping

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org]On Behalf Of Tom Lane
Sent: 2006Äê4ÔÂ28ÈÕ 0:53
To: guoping(dot)zhang(at)nec(dot)com(dot)au
Cc: pgsql-performance(at)postgresql(dot)org; Guoping Zhang (E-mail)
Subject: Re: [PERFORM] how unsafe (or worst scenarios) when setting
fsync OFF for postgresql


"Guoping Zhang" <guoping(dot)zhang(at)nec(dot)com(dot)au> writes:
> Our application has a strict speed requirement for DB operation. Our tests
> show that it takes about 10secs for the operation when setting fsync off,
> but takes about 70 seconds when setting fsync ON (with other WAL related
> parametered tuned).

I can't believe that a properly tuned application would have an fsync
penalty that large.  Are you performing that "operation" as several
thousand small transactions, or some such?  Try grouping the operations
into one (or at most a few) transactions.  Also, what wal_buffers and
wal_sync_method settings are you using, and have you experimented with
alternatives?   What sort of platform is this on?  What PG version?

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend


In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2006-04-28 04:56:48
Subject: Re: how unsafe (or worst scenarios) when setting fsync OFF for postgresql
Previous:From: Mark KirkwoodDate: 2006-04-28 02:30:36
Subject: Re: Hardware: HP StorageWorks MSA 1500

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group