Thread: Disable WAL completely
Hi,
I want to disable Write Ahead Log (WAL) completely because of following reasons,
- I am running Linux on the Compact Flash, which has limited memory; I can’t afford disk space of 32MB for pg_xlog folder. ( checkpoints_segments = 1)
- CF has own limitation with respect to number of writes (I/O operation).
- And, Our Database is Small and embedded as part of system (along with web-server, drivers). We may not require data recovery from the pg_xlog folder. It is not an enterprise application.
Please give your inputs, to resolve this issue..
Thanks,
Jeeva…
On Mon, Feb 18, 2008 at 02:41:50PM +0530, Kathirvel, Jeevanandam wrote: > I want to disable Write Ahead Log (WAL) completely because > of following reasons, basically, you can't disable it. regards, depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV)
am Mon, dem 18.02.2008, um 14:41:50 +0530 mailte Kathirvel, Jeevanandam folgendes: > Hi, > > I want to disable Write Ahead Log (WAL) completely because of > > > Please give your inputs, to resolve this issue.. Change the destination for this log to /dev/null Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Hi Depesz,
Is there way to minimize the I/O operation on disk/CF.
Can I create RAM file system and point the pg_xlog files to RAM
location instead of CF. whether this will work?
Regards,
Jeeva
-----Original Message-----
From: hubert depesz lubaczewski [mailto:depesz@depesz.com]
Sent: Monday, February 18, 2008 2:50 PM
To: Kathirvel, Jeevanandam
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Disable WAL completely
On Mon, Feb 18, 2008 at 02:41:50PM +0530, Kathirvel, Jeevanandam wrote:
> I want to disable Write Ahead Log (WAL) completely because
> of following reasons,
basically, you can't disable it.
regards,
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)
On Mon, Feb 18, 2008 at 03:00:47PM +0530, Kathirvel, Jeevanandam wrote: > Is there way to minimize the I/O operation on disk/CF. > Can I create RAM file system and point the pg_xlog files to RAM > location instead of CF. whether this will work? it will, but in case you'll lost power you will also (most probably) loose your database. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV)
On Feb 18, 2008, at 3:32 AM, hubert depesz lubaczewski wrote: > On Mon, Feb 18, 2008 at 03:00:47PM +0530, Kathirvel, Jeevanandam > wrote: >> Is there way to minimize the I/O operation on disk/CF. >> Can I create RAM file system and point the pg_xlog files to RAM >> location instead of CF. whether this will work? > > it will, but in case you'll lost power you will also (most probably) > loose your database. Right. Without the xlog directory you'll have very little chance of ever doing any kind of clean stop/start of your database. If you don't need the reliability offered by Postgres's use of transaction logs you'll probably be much better served with a different database or even a completely different storage scheme than trying to make Postgres fit that bill. Erik Jones DBA | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
[Erik Jones] > Right. Without the xlog directory you'll have very little chance of > ever doing any kind of clean stop/start of your database. If you > don't need the reliability offered by Postgres's use of transaction > logs you'll probably be much better served with a different database > or even a completely different storage scheme than trying to make > Postgres fit that bill. We actually have some postgres databases that are read-only, others that can be rebuilt by a script or from some old backup, and yet others that can be wiped completely without ill effects ... and others where we would prefer to keep all the data, but it would be no disaster if we lose some. Maybe we would be better off not using postgres for those purposes, but it's oh so much easier for us to stick to one database system ;-) We've considered both running postgres from a ram-disk and to have the fsync turned off for some of our databases, but right now we're running all off one host, fsync didn't reduce the performance that much, and after one disasterous power failure we considered that it was not worth the effort to have fsync turned off. That being said, postgres is probably not an optimal solution for an embedded system running on flash memory ;-)
On Mon, 18 Feb 2008, Tobias Brox wrote: > We actually have some postgres databases that are read-only, others that > can be rebuilt by a script or from some old backup, and yet others that > can be wiped completely without ill effects ... and others where we > would prefer to keep all the data, but it would be no disaster if we > lose some. If there's not much write traffic, the WAL won't be used much anyway. If you really don't care much about the integrity, then the best option is probably to put the WAL on ramfs. Having said that, flash is cheaper than RAM. Why not just get a bigger flash device? The "too many writes wear it out" argument is mostly not true nowadays anyway. Matthew -- Don't worry! The world can't end today because it's already tomorrow in Australia.
On Tue, Feb 19, 2008 at 02:48:55PM +0000, Matthew wrote: > If there's not much write traffic, the WAL won't be used much anyway. You still have checkpoints. > If you really don't care much about the integrity, then the best option is > probably to put the WAL on ramfs. Um, that will cause the WAL to go away in the event of device crash. Surely that's a bad thing? A