Re: Disable WAL completely

Hi,

I want to disable Write Ahead Log (WAL) completely because
of following reasons,

1. 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)

2. CF has own limitation with respect to number of writes (I/O
operation).

3. 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(at)depesz(dot)com]
Sent: Monday, February 18, 2008 2:50 PM
To: Kathirvel, Jeevanandam
Cc: pgsql-performance(at)postgresql(dot)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(at)myemma(dot)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