pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedfull_page_writes = off?
11+ messages / 6 participants
[nested] [flat]
* full_page_writes = off?
@ 2007-12-01 08:02 rihad <rihad@mail.ru>
2007-12-01 11:37 ` Re: full_page_writes = off? Gregory Stark <stark@enterprisedb.com>
2007-12-01 11:47 ` Re: full_page_writes = off? Tomasz Ostrowski <tometzky@batory.org.pl>
0 siblings, 2 replies; 11+ messages in thread
From: rihad @ 2007-12-01 08:02 UTC (permalink / raw)
To: pgsql-general@postgresql.org
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: full_page_writes = off?
2007-12-01 08:02 full_page_writes = off? rihad <rihad@mail.ru>
@ 2007-12-01 11:37 ` Gregory Stark <stark@enterprisedb.com>
1 sibling, 0 replies; 11+ messages in thread
From: Gregory Stark @ 2007-12-01 11:37 UTC (permalink / raw)
To: rihad <rihad@mail.ru>; +Cc: pgsql-general@postgresql.org
"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!
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: full_page_writes = off?
2007-12-01 08:02 full_page_writes = off? rihad <rihad@mail.ru>
@ 2007-12-01 11:47 ` Tomasz Ostrowski <tometzky@batory.org.pl>
2007-12-01 12:04 ` Re: full_page_writes = off? Martin Marques <martin@marquesminen.com.ar>
1 sibling, 1 reply; 11+ messages in thread
From: Tomasz Ostrowski @ 2007-12-01 11:47 UTC (permalink / raw)
To: rihad <rihad@mail.ru>; +Cc: pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: full_page_writes = off?
2007-12-01 08:02 full_page_writes = off? rihad <rihad@mail.ru>
2007-12-01 11:47 ` Re: full_page_writes = off? Tomasz Ostrowski <tometzky@batory.org.pl>
@ 2007-12-01 12:04 ` Martin Marques <martin@marquesminen.com.ar>
2007-12-01 12:16 ` Re: full_page_writes = off? Tomasz Ostrowski <tometzky@batory.org.pl>
0 siblings, 1 reply; 11+ messages in thread
From: Martin Marques @ 2007-12-01 12:04 UTC (permalink / raw)
To: Tomasz Ostrowski <tometzky@batory.org.pl>; +Cc: rihad@mail.ru; pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: full_page_writes = off?
2007-12-01 08:02 full_page_writes = off? rihad <rihad@mail.ru>
2007-12-01 11:47 ` Re: full_page_writes = off? Tomasz Ostrowski <tometzky@batory.org.pl>
2007-12-01 12:04 ` Re: full_page_writes = off? Martin Marques <martin@marquesminen.com.ar>
@ 2007-12-01 12:16 ` Tomasz Ostrowski <tometzky@batory.org.pl>
2007-12-02 20:58 ` Re: full_page_writes = off? Greg Smith <gsmith@gregsmith.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tomasz Ostrowski @ 2007-12-01 12:16 UTC (permalink / raw)
To: Martin Marques <martin@marquesminen.com.ar>; +Cc: rihad@mail.ru; pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: full_page_writes = off?
2007-12-01 08:02 full_page_writes = off? rihad <rihad@mail.ru>
2007-12-01 11:47 ` Re: full_page_writes = off? Tomasz Ostrowski <tometzky@batory.org.pl>
2007-12-01 12:04 ` Re: full_page_writes = off? Martin Marques <martin@marquesminen.com.ar>
2007-12-01 12:16 ` Re: full_page_writes = off? Tomasz Ostrowski <tometzky@batory.org.pl>
@ 2007-12-02 20:58 ` Greg Smith <gsmith@gregsmith.com>
2007-12-03 12:15 ` Re: full_page_writes = off? Martin Marques <martin@marquesminen.com.ar>
2007-12-10 14:06 ` Re: [GENERAL] full_page_writes = off? Bruce Momjian <bruce@momjian.us>
0 siblings, 2 replies; 11+ messages in thread
From: Greg Smith @ 2007-12-02 20:58 UTC (permalink / raw)
To: pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: full_page_writes = off?
2007-12-01 08:02 full_page_writes = off? rihad <rihad@mail.ru>
2007-12-01 11:47 ` Re: full_page_writes = off? Tomasz Ostrowski <tometzky@batory.org.pl>
2007-12-01 12:04 ` Re: full_page_writes = off? Martin Marques <martin@marquesminen.com.ar>
2007-12-01 12:16 ` Re: full_page_writes = off? Tomasz Ostrowski <tometzky@batory.org.pl>
2007-12-02 20:58 ` Re: full_page_writes = off? Greg Smith <gsmith@gregsmith.com>
@ 2007-12-03 12:15 ` Martin Marques <martin@marquesminen.com.ar>
2007-12-03 14:04 ` Re: full_page_writes = off? Greg Smith <gsmith@gregsmith.com>
1 sibling, 1 reply; 11+ messages in thread
From: Martin Marques @ 2007-12-03 12:15 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: pgsql-general@postgresql.org
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: full_page_writes = off?
2007-12-01 08:02 full_page_writes = off? rihad <rihad@mail.ru>
2007-12-01 11:47 ` Re: full_page_writes = off? Tomasz Ostrowski <tometzky@batory.org.pl>
2007-12-01 12:04 ` Re: full_page_writes = off? Martin Marques <martin@marquesminen.com.ar>
2007-12-01 12:16 ` Re: full_page_writes = off? Tomasz Ostrowski <tometzky@batory.org.pl>
2007-12-02 20:58 ` Re: full_page_writes = off? Greg Smith <gsmith@gregsmith.com>
2007-12-03 12:15 ` Re: full_page_writes = off? Martin Marques <martin@marquesminen.com.ar>
@ 2007-12-03 14:04 ` Greg Smith <gsmith@gregsmith.com>
0 siblings, 0 replies; 11+ messages in thread
From: Greg Smith @ 2007-12-03 14:04 UTC (permalink / raw)
To: Martin Marques <martin@marquesminen.com.ar>; +Cc: pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [GENERAL] full_page_writes = off?
2007-12-01 08:02 full_page_writes = off? rihad <rihad@mail.ru>
2007-12-01 11:47 ` Re: full_page_writes = off? Tomasz Ostrowski <tometzky@batory.org.pl>
2007-12-01 12:04 ` Re: full_page_writes = off? Martin Marques <martin@marquesminen.com.ar>
2007-12-01 12:16 ` Re: full_page_writes = off? Tomasz Ostrowski <tometzky@batory.org.pl>
2007-12-02 20:58 ` Re: full_page_writes = off? Greg Smith <gsmith@gregsmith.com>
@ 2007-12-10 14:06 ` Bruce Momjian <bruce@momjian.us>
2007-12-10 14:27 ` Re: [GENERAL] full_page_writes = off? Greg Smith <gsmith@gregsmith.com>
1 sibling, 1 reply; 11+ messages in thread
From: Bruce Momjian @ 2007-12-10 14:06 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: pgsql-docs
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@gregsmith.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@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Attachments:
/rtmp/diff (text/x-diff, 1.5K) download
Index: doc/src/sgml/wal.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/wal.sgml,v
retrieving revision 1.46
diff -c -c -r1.46 wal.sgml
*** doc/src/sgml/wal.sgml 28 Nov 2007 15:42:31 -0000 1.46
--- doc/src/sgml/wal.sgml 10 Dec 2007 14:04:12 -0000
***************
*** 58,65 ****
while some are write-back, and the
same concerns about data loss exist for write-back drive caches as
exist for disk controller caches. Consumer-grade IDE drives are
! particularly likely to contain write-back caches that will not
! survive a power failure.
</para>
<para>
--- 58,74 ----
while some are write-back, and the
same concerns about data loss exist for write-back drive caches as
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
! <application>atacontrol</>. (For SCSI disks use <ulink
! url="http://sg.torque.net/sg/sdparm.html"><application>sdparm</></ulink>
! to turn off <literal>WCE</>.) On <productname>Windows</> write caching is
! controlled by <literal>My Computer\Open\{select disk
! drive}\Properties\Hardware\Properties\Policies\Enable write caching on
! the disk</>.
</para>
<para>
* Re: [GENERAL] full_page_writes = off?
2007-12-01 08:02 full_page_writes = off? rihad <rihad@mail.ru>
2007-12-01 11:47 ` Re: full_page_writes = off? Tomasz Ostrowski <tometzky@batory.org.pl>
2007-12-01 12:04 ` Re: full_page_writes = off? Martin Marques <martin@marquesminen.com.ar>
2007-12-01 12:16 ` Re: full_page_writes = off? Tomasz Ostrowski <tometzky@batory.org.pl>
2007-12-02 20:58 ` Re: full_page_writes = off? Greg Smith <gsmith@gregsmith.com>
2007-12-10 14:06 ` Re: [GENERAL] full_page_writes = off? Bruce Momjian <bruce@momjian.us>
@ 2007-12-10 14:27 ` Greg Smith <gsmith@gregsmith.com>
2007-12-10 14:51 ` Re: [GENERAL] full_page_writes = off? Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 11+ messages in thread
From: Greg Smith @ 2007-12-10 14:27 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-docs
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@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [GENERAL] full_page_writes = off?
2007-12-01 08:02 full_page_writes = off? rihad <rihad@mail.ru>
2007-12-01 11:47 ` Re: full_page_writes = off? Tomasz Ostrowski <tometzky@batory.org.pl>
2007-12-01 12:04 ` Re: full_page_writes = off? Martin Marques <martin@marquesminen.com.ar>
2007-12-01 12:16 ` Re: full_page_writes = off? Tomasz Ostrowski <tometzky@batory.org.pl>
2007-12-02 20:58 ` Re: full_page_writes = off? Greg Smith <gsmith@gregsmith.com>
2007-12-10 14:06 ` Re: [GENERAL] full_page_writes = off? Bruce Momjian <bruce@momjian.us>
2007-12-10 14:27 ` Re: [GENERAL] full_page_writes = off? Greg Smith <gsmith@gregsmith.com>
@ 2007-12-10 14:51 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 11+ messages in thread
From: Bruce Momjian @ 2007-12-10 14:51 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: pgsql-docs
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@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2007-12-10 14:51 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-01 08:02 full_page_writes = off? rihad <rihad@mail.ru>
2007-12-01 11:37 ` Gregory Stark <stark@enterprisedb.com>
2007-12-01 11:47 ` Tomasz Ostrowski <tometzky@batory.org.pl>
2007-12-01 12:04 ` Martin Marques <martin@marquesminen.com.ar>
2007-12-01 12:16 ` Tomasz Ostrowski <tometzky@batory.org.pl>
2007-12-02 20:58 ` Greg Smith <gsmith@gregsmith.com>
2007-12-03 12:15 ` Martin Marques <martin@marquesminen.com.ar>
2007-12-03 14:04 ` Greg Smith <gsmith@gregsmith.com>
2007-12-10 14:06 ` Bruce Momjian <bruce@momjian.us>
2007-12-10 14:27 ` Greg Smith <gsmith@gregsmith.com>
2007-12-10 14:51 ` Bruce Momjian <bruce@momjian.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