Would it be OK if I put db file on a ext2 filesystem?

Magicloud Wang wrote:

Dear,
I think database has its own operation journal, and different journal
filesystem does give different performance. So if I put database file on a
non-journal filesystem, would it be safe? Does this like using a raw device?

You lose a little bit of data integrity in exchange for a little bit of
speed. I suppose it'd be a fine thing to do so long as you can live with
that trade off. If you want good data integrity you are more likely to
get it from battery backed RAID5 or RAID10 or something of that sort
rather than just trusting something like EXT3 or Reiser. EXT2 isn't a
bad file system.

Magicloud Wang wrote:

Dear,
I think database has its own operation journal, and different journal
filesystem does give different performance. So if I put database file on a
non-journal filesystem, would it be safe? Does this like using a raw device?

Regular database files need metadata journalling (data=writeback mount
option for ext3). This is quite faster than full-blown journalling
which is what you get with default ext3 mount options. WAL files
(pg_xlog) do not need any kind of journalling, so you can save the
overhead and put them on an ext2 filesystem (or any other nonjournalled
filesystem).

--
Alvaro Herrera Developer, http://www.PostgreSQL.org/
Voy a acabar con todos los humanos / con los humanos yo acabar�
voy a acabar con todos / con todos los humanos acabar� (Bender)

On Dec 12, 2007 9:46 AM, Collin Kidder <adderd@kkmfg.com> wrote:

Magicloud Wang wrote:

Dear,
I think database has its own operation journal, and different journal
filesystem does give different performance. So if I put database file on a
non-journal filesystem, would it be safe? Does this like using a raw device?

You lose a little bit of data integrity in exchange for a little bit of
speed. I suppose it'd be a fine thing to do so long as you can live with
that trade off. If you want good data integrity you are more likely to
get it from battery backed RAID5 or RAID10 or something of that sort
rather than just trusting something like EXT3 or Reiser. EXT2 isn't a
bad file system.

It's one of things where the known bugs in ext2/3 aren't as bad as
they sound, while the unknown bugs in some newer, less tested file
systems are often worse. OTOH, ext2/3 do have a 2 TB partition size
limit (or at least used to) so for some things, you just gotta go to a
different file system.

Back to the subject at hand, do you need journaling for the db, this
thread from last year has a lot of good info in it. It's the one
where I got the impression that ext2 for pg_xlog was fine and dandy.
I remember now, after reading it, that certain types of fsync might be
dangerous with non-journaled file systems.

http://archives.postgresql.org/pgsql-performance/2006-08/msg00101.php

On 12/12/07, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

Regular database files need metadata journalling (data=writeback mount
option for ext3). This is quite faster than full-blown journalling
which is what you get with default ext3 mount options. WAL files
(pg_xlog) do not need any kind of journalling, so you can save the
overhead and put them on an ext2 filesystem (or any other nonjournalled
filesystem).

I think in practice there won't be a performance difference between
ext3 with 'data=writeback' and ext2 for WAL-the files are
preallocated, so the only activity that's occurring with any frequency
is fsync'd data writes to existing file blocks, which don't go through
the journal. So you might as well go with ext3 since (a) you won't
have to fsck, and (b) ext3 is the bog-standard Linux filesystem now
and as such gets the most testing--ext2 is gradually dropping into
obsolescence.

-Doug