ext3 - Mailing list pgsql-general

          Hello,

Gabor Szima asked us to translate the letter below.

"I read that ext3 writeback mode is recommended for PostgreSQL. I made
some tests.

                data=ordered        data=writeback
----------------------------------------------------------------------
restoredb:             2m16.790s        1m42.367s
UPDATE <tbl1> (17krows):    9.289s            7.147s
UPDATE <tbl1> (17krows) (2.):    10.480s            3.778s
VACUUM ANALYZE <tbl1>:        9.364s            0.986s !
VACUUM FULL <tbl1>:        16.071s            2.575s
REINDEX TABLE <tbl1>:        3.815s            1.886s
----------------------------------------------------------------------

It's seductive.
However I made some crash-tests too. Updated 4 tables simultaneously and
recurring for 10 to 120s, then powered off the machine (without the
reset button. i just pulled out the cable).

SEQ RECOVERY-WARNINGS   VACUUM
-------------------------------
01: 1650                OK        (WARNING:  invalid page header in
block 769 of relation "18800"; zeroing out page)
02: 3            FATAL        (ERROR:  could not access status of
transaction 37814272)
-------------------------------        (DETAIL:  could not open file
"/data/pgdata/pg_clog/0024": No such file or directory)

I have stopped my tests at this point because this is not for production
use. The database was corrupted.


With ordered mode I got this:

ext3-noatime,data=ordered:

SEQ RECOVERY-WARNINGS   VACUUM
------------------------------
01: 0                   OK
02: 0                   OK
03: 0                   OK
04: 0                   W,OK    (relation "<tbl>" page 398 is
uninitialized --- fixing)
05: 0                   OK
06: 0                   OK
07: 0                   W,OK    (relation "<tbl>" page 911 is
uninitialized --- fixing)
08: 0                   OK
09: 0                   OK
10: 0                   OK
------------------------------

I think that writeback mode first records the data then the inode, and
the ordered mode does it in reverse order.  I also mean that postgres
log requires the inode recorded correctly, the data loss is handled by
the WAL.

AMD XP2000, 512MB RAM, PostgreSQL 7.4.6 (i686), linux-2.4.28, gcc-3.3.5,
Adaptec 29160, WD Enterprise 4360 (SCSI, SCA-80)

I made mkfs and initdb before every tests and I repeated them in reverse
order too. No quake3 ran in the background.

-Sygma"


Sorry for my english.


       Mage