full_page_writes = off?

Hi, would full_page_writes=off increase the risk of inconsistency or db
corruption in 8.3 and FreeBSD 7?

fsync = on;
Definitely "on", as single power outage after three years is guaranteed
to make your life interesting otherwise.

synchronous_commit = off;
Can be "off" in my case as I'm not doing any "external" actions based on
transaction's success or failure (if I understand the docs correctly).
So I don't care at which moment the evil strikes as long as the db is
consistent.

full_page_writes = off;
AFAIK when this is "on" it means synchronous WAL writing with less
impact, as it occurs once per checkpoint, but it's still synchronous.
Not sure at all about this one. How would FreeBSD 7's UFS survive the
power crash etc. with this set to "off"? OTOH, does "on" play well with
synchronous_commit=off? Meaning, will the transaction holder get success
immediately on commit, still guaranteeing consistency?

Thanks for any insights or clarifying missed points.

"rihad" <rihad(at)mail(dot)ru> writes:

> Hi, would full_page_writes=off increase the risk of inconsistency or db
> corruption in 8.3 and FreeBSD 7?

yes.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!

On Sat, 01 Dec 2007, rihad wrote:

> fsync = on;
> synchronous_commit = off;
> full_page_writes = off;
> Thanks for any insights or clarifying missed points.

Also check whether a disks have write-caching turned off - it should.
On Linux the command is:
# hdparm -W /dev/sda
/dev/sda:
write-caching = 0 (off)
I don't know how to check it on BSD.

It can be on for extra performance only if your disks are connected
to battery backed RAID controller.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh

Tomasz Ostrowski escribió:
> On Sat, 01 Dec 2007, rihad wrote:
>
>> fsync = on;
>> synchronous_commit = off;
>> full_page_writes = off;
>> Thanks for any insights or clarifying missed points.
>
> Also check whether a disks have write-caching turned off - it should.
> On Linux the command is:
> # hdparm -W /dev/sda
> /dev/sda:
> write-caching = 0 (off)

Uh? That command as was written lacks an argument for -W (0/1). From the
hdparm --help:

-W set drive write-caching flag (0/1) (DANGEROUS)

# hdparm -V
hdparm v6.9

On Sat, 01 Dec 2007, Martin Marques wrote:

>> Also check whether a disks have write-caching turned off - it should.
>> On Linux the command is:
>> # hdparm -W /dev/sda
>> /dev/sda:
>> write-caching = 0 (off)
>
> Uh? That command as was written lacks an argument for -W (0/1). From the
> hdparm --help:
>
> -W set drive write-caching flag (0/1) (DANGEROUS)
>
> # hdparm -V
> hdparm v6.9

# hdparm -V
hdparm v7.7
# hdparm --help 2>&1 | grep write-caching
-W get/set drive write-caching flag (0/1)

You can also use "hdparm -I" to check this - look for a "Write
caching" in "Commands/features" section. If it has a "*" in front
then it is enabled and dangerous.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh

On Sat, 1 Dec 2007, Tomasz Ostrowski wrote:

> You can also use "hdparm -I" to check this - look for a "Write
> caching" in "Commands/features" section. If it has a "*" in front
> then it is enabled and dangerous.

Right; using -I works with most Linux hdparm versions:

# hdparm -V
hdparm v6.6
# hdparm -I /dev/hda | grep "Write cache"
* Write cache
# hdparm -W 0 /dev/hda

/dev/hda:
setting drive write-caching to 0 (off)
# hdparm -I /dev/hda | grep "Write cache"
Write cache

While being able to check the state with -W only works in very recent
ones.

The best way to make this change permanent varies depending on your Linux
distribution.

Also: nowadays many SATA disks appear as SCSI devices like /dev/sda. In
some cases I believe you can use a recent hdparm on them anyway, in others
I've had to use sdparm instead. Several of the examples at
http://sg.torque.net/sg/sdparm.html show how to manipulate the Write Cache
Enabled (WCE) status similarly to the above on SCSI devices.

> I don't know how to check it on BSD.

In FreeBSD I believe you use atacontrol to check the settings, and you can
make the changes permanent by fiddling with the /boot/device.hints file.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Greg Smith escribió:
> On Sat, 1 Dec 2007, Tomasz Ostrowski wrote:
>
>> You can also use "hdparm -I" to check this - look for a "Write
>> caching" in "Commands/features" section. If it has a "*" in front
>> then it is enabled and dangerous.
>
> Right; using -I works with most Linux hdparm versions:
>
> # hdparm -V
> hdparm v6.6
> # hdparm -I /dev/hda | grep "Write cache"
> * Write cache
> # hdparm -W 0 /dev/hda
>
> /dev/hda:
> setting drive write-caching to 0 (off)
> # hdparm -I /dev/hda | grep "Write cache"
> Write cache

Out of ignorance, do RAID[1] devices have write cache?

[1]: I'm talking about RAID through hardware.

On Mon, 3 Dec 2007, Martin Marques wrote:

> Out of ignorance, do RAID devices have write cache? I'm talking about
> RAID through hardware.

Depends on the device. There are hardware RAID cards with cache, and ones
without. Generally the ones with cache allow you to adjust whether the
cache is used or not ("write-back"=used and "write-through"=unused). If
you're using it, you should have a battery installed lest the system get
interrupted while data is in the cache (and presumed written by the
database) but not written to disk yet.

Some cards allow you to adjust whether the individual disks utilize their
cache or not. For database use, if you're using the controller cache to
preserve writes, you should make sure the individual disk caches are
turned off.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

I thought we had the disk write cache control documented for popular
operating systems, but I see we don't so I added the following patch to
our documentation.

---------------------------------------------------------------------------

Greg Smith wrote:
> On Sat, 1 Dec 2007, Tomasz Ostrowski wrote:
>
> > You can also use "hdparm -I" to check this - look for a "Write
> > caching" in "Commands/features" section. If it has a "*" in front
> > then it is enabled and dangerous.
>
> Right; using -I works with most Linux hdparm versions:
>
> # hdparm -V
> hdparm v6.6
> # hdparm -I /dev/hda | grep "Write cache"
> * Write cache
> # hdparm -W 0 /dev/hda
>
> /dev/hda:
> setting drive write-caching to 0 (off)
> # hdparm -I /dev/hda | grep "Write cache"
> Write cache
>
> While being able to check the state with -W only works in very recent
> ones.
>
> The best way to make this change permanent varies depending on your Linux
> distribution.
>
> Also: nowadays many SATA disks appear as SCSI devices like /dev/sda. In
> some cases I believe you can use a recent hdparm on them anyway, in others
> I've had to use sdparm instead. Several of the examples at
> http://sg.torque.net/sg/sdparm.html show how to manipulate the Write Cache
> Enabled (WCE) status similarly to the above on SCSI devices.
>
> > I don't know how to check it on BSD.
>
> In FreeBSD I believe you use atacontrol to check the settings, and you can
> make the changes permanent by fiddling with the /boot/device.hints file.
>
> --
> * Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Mon, 10 Dec 2007, Bruce Momjian wrote:

> exist for disk controller caches. Consumer-grade IDE drives are
>! particularly likely to have write-back caches that will not survive a
>! power failure. To check write caching on <productname>Linux</> use
>! <command>hdparm -I</>; it is enabled if there is a <literal>*</> next
>! to <literal>Write cache</>. <command>hdparm -W</> can to turn off
>! write caching. On <productname>FreeBSD</> use

I'm not where I can submit this as a patch right now, but there are two
things that should get fixed in the above:

--Saying "Consumer-grade IDE drives" isn't quite right; "Consumer-grade
IDE and SATA drives" would correctly label the scope of the problem.

--There's a typo on the next to last line here: "can to turn off".

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Greg Smith wrote:
> On Mon, 10 Dec 2007, Bruce Momjian wrote:
>
> > exist for disk controller caches. Consumer-grade IDE drives are
> >! particularly likely to have write-back caches that will not survive a
> >! power failure. To check write caching on <productname>Linux</> use
> >! <command>hdparm -I</>; it is enabled if there is a <literal>*</> next
> >! to <literal>Write cache</>. <command>hdparm -W</> can to turn off
> >! write caching. On <productname>FreeBSD</> use
>
> I'm not where I can submit this as a patch right now, but there are two
> things that should get fixed in the above:
>
> --Saying "Consumer-grade IDE drives" isn't quite right; "Consumer-grade
> IDE and SATA drives" would correctly label the scope of the problem.
>
> --There's a typo on the next to last line here: "can to turn off".

Thanks, both done.
--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +