pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Running with fsync=off
7+ messages / 5 participants
[nested] [flat]

* Running with fsync=off
@ 2005-12-22 07:30  Benjamin Arai <barai@cs.ucr.edu>
  0 siblings, 1 reply; 7+ messages in thread

From: Benjamin Arai @ 2005-12-22 07:30 UTC (permalink / raw)
  To: pgsql-admin; pgsql-general@postgresql.org

I want to be able to do large updates on an existing backed up database 
with fsync=off but at the end of the updates how do I ensure that the 
data gets synced?

Somebody said running "sync ; sync; sync" from the console.  This seems 
reasonable but why not just "sync" or is there another command I should 
ruyn after the update either in postgres or the console?



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] Running with fsync=off
@ 2005-12-22 08:08  Martijn van Oosterhout <kleptog@svana.org>
  parent: Benjamin Arai <barai@cs.ucr.edu>
  0 siblings, 1 reply; 7+ messages in thread

From: Martijn van Oosterhout @ 2005-12-22 08:08 UTC (permalink / raw)
  To: Benjamin Arai <barai@cs.ucr.edu>; +Cc: pgsql-admin; pgsql-general@postgresql.org

On Wed, Dec 21, 2005 at 11:30:15PM -0800, Benjamin Arai wrote:
> I want to be able to do large updates on an existing backed up database 
> with fsync=off but at the end of the updates how do I ensure that the 
> data gets synced?

Do you know if that actually makes it much faster? Maybe you're better
off splitting WAL into a seperate disk.

> Somebody said running "sync ; sync; sync" from the console.  This seems 
> reasonable but why not just "sync" or is there another command I should 
> ruyn after the update either in postgres or the console?

The reason is partly historical. On some OSes running sync only starts
the process but returns immediatly. However, there can only be one sync
at a time so the second sync waits for the first the finish. The third
is just for show. However, on Linux at least the one sync is enough.

Don't you need to restart postgres to change that parameter anyway?

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] Running with fsync=off
@ 2005-12-22 16:47  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Martijn van Oosterhout <kleptog@svana.org>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2005-12-22 16:47 UTC (permalink / raw)
  To: Martijn van Oosterhout <kleptog@svana.org>; +Cc: Benjamin Arai <barai@cs.ucr.edu>; pgsql-admin; pgsql-general@postgresql.org

Martijn van Oosterhout <kleptog@svana.org> writes:
> On Wed, Dec 21, 2005 at 11:30:15PM -0800, Benjamin Arai wrote:
>> Somebody said running "sync ; sync; sync" from the console.  This seems

> The reason is partly historical. On some OSes running sync only starts
> the process but returns immediatly. However, there can only be one sync
> at a time so the second sync waits for the first the finish. The third
> is just for show. However, on Linux at least the one sync is enough.

No, the second and third are both a waste of time.  sync tells the
kernel to flush any dirty buffers to disk, but doesn't wait for it to
happen.

There is a story that the advice to type sync twice was originally given
to operators of an early Unix system, as a quick-and-dirty way of making
sure that they didn't power the machine down before the sync completed.
I don't know if it's true or not, but certainly the value would only
appear if you type sync<RETURN>sync<RETURN> so that the first sync is
actually issued before you type the next one.  Typing them all on one
line as depicted is just a waste of finger motion.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] Running with fsync=off
@ 2005-12-27 21:12  Jim C. Nasby <jnasby@pervasive.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Jim C. Nasby @ 2005-12-27 21:12 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Martijn van Oosterhout <kleptog@svana.org>; Benjamin Arai <barai@cs.ucr.edu>; pgsql-admin; pgsql-general@postgresql.org

On Thu, Dec 22, 2005 at 11:47:13AM -0500, Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > On Wed, Dec 21, 2005 at 11:30:15PM -0800, Benjamin Arai wrote:
> >> Somebody said running "sync ; sync; sync" from the console.  This seems
> 
> > The reason is partly historical. On some OSes running sync only starts
> > the process but returns immediatly. However, there can only be one sync
> > at a time so the second sync waits for the first the finish. The third
> > is just for show. However, on Linux at least the one sync is enough.
> 
> No, the second and third are both a waste of time.  sync tells the
> kernel to flush any dirty buffers to disk, but doesn't wait for it to
> happen.
> 
> There is a story that the advice to type sync twice was originally given
> to operators of an early Unix system, as a quick-and-dirty way of making
> sure that they didn't power the machine down before the sync completed.
> I don't know if it's true or not, but certainly the value would only
> appear if you type sync<RETURN>sync<RETURN> so that the first sync is
> actually issued before you type the next one.  Typing them all on one
> line as depicted is just a waste of finger motion.

How would sync<RETURN>sync<RETURN> differ from sync;sync? The second
case will wait for the first command to return, or is there a race
condition that's reduced by typing by hand?
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] Running with fsync=off
@ 2005-12-27 22:44  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2005-12-27 22:44 UTC (permalink / raw)
  To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Martijn van Oosterhout <kleptog@svana.org>; Benjamin Arai <barai@cs.ucr.edu>; pgsql-admin; pgsql-general@postgresql.org

"Jim C. Nasby" <jnasby@pervasive.com> writes:
> How would sync<RETURN>sync<RETURN> differ from sync;sync? The second
> case will wait for the first command to return, or is there a race
> condition that's reduced by typing by hand?

The actual runtime of the "sync" program is epsilon, because it doesn't
wait for all the I/O to happen.  The entire reason for the custom is
that the I/O is going on while you type the second "sync".  And yes,
it's a race condition.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] Running with fsync=off
@ 2005-12-28 19:12  Qingqing Zhou <zhouqq@cs.toronto.edu>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Qingqing Zhou @ 2005-12-28 19:12 UTC (permalink / raw)
  To: pgsql-admin


"Tom Lane" <tgl@sss.pgh.pa.us> wrote
>
> The actual runtime of the "sync" program is epsilon, because it doesn't
> wait for all the I/O to happen.  The entire reason for the custom is
> that the I/O is going on while you type the second "sync".

But in our 7.4.x, there is code like this:

 sync();
 if (IsUnderPostmaster)
  sleep(2);
 sync();

So for standalone backend, that's just "sync();sync()". So the "I/O is going
on while you type the second sync" theory doesn't work. For ordinary
backends, that's even more wierd - Why wasting 2 seconds is enough to save
the data? Why not 3 seconds or 4?

Regards,
Qingqing






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] Running with fsync=off
@ 2005-12-28 19:58  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Qingqing Zhou <zhouqq@cs.toronto.edu>
  0 siblings, 0 replies; 7+ messages in thread

From: Tom Lane @ 2005-12-28 19:58 UTC (permalink / raw)
  To: Qingqing Zhou <zhouqq@cs.toronto.edu>; +Cc: pgsql-admin

"Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:
> But in our 7.4.x, there is code like this:

>  sync();
>  if (IsUnderPostmaster)
>   sleep(2);
>  sync();

I'm not claiming that that code is correct; it is in fact just as broken
as the traditional custom.  You'll notice we don't have it anymore ;-)

> Why wasting 2 seconds is enough to save the data?

It's not.  The entire custom is and always has been bogus.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2005-12-28 19:58 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-22 07:30 Running with fsync=off Benjamin Arai <barai@cs.ucr.edu>
2005-12-22 08:08 ` Martijn van Oosterhout <kleptog@svana.org>
2005-12-22 16:47   ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-27 21:12     ` Jim C. Nasby <jnasby@pervasive.com>
2005-12-27 22:44       ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-28 19:12         ` Qingqing Zhou <zhouqq@cs.toronto.edu>
2005-12-28 19:58           ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox