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
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
