Обсуждение: 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@mail.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@gregsmith.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@gregsmith.com http://www.gregsmith.com Baltimore, MD