PostgreSQL Reliability when fsync = false on Linux-XFS - Mailing list pgsql-performance

(Please follow Mail-Followup-To, I'm not on the pgsql-performance
mailing list but am on the Linux-XFS mailing list. My apologies too for
the cross-post. I'm cc'ing the Linux-XFS mailing list in case people
there will be interested in this, too.)


Hi,

We have a server running PostgreSQL v7.3.3 on Debian GNU/Linux with
Linux kernel 2.4.21-xfs. The PostgreSQL data is stored on an XFS[1]
partition mounted with the options "rw,noatime,logbufs=8". The machine
is an Intel Pentium III 733MHz with 512MB RAM and a four-disk hardware
IDE RAID-5 array with a 3ware controller.

Among other databases, we have a centralized Snort[2] database that is
analyzed by ACIDLab[3]. I noticed performance problems during SELECT and
INSERT operations when the tables reach around 200,000 records. Because
of timeout issues, the PHP-based ACIDLab can't be used properly.

I read the performance section of the ACID FAQ[4] as well as the
PostgreSQL "Managing Kernel Resources" document , and so far have tuned
my system by setting /proc/sys/kernel/{shmall,shmmax} to 134217728.

I also turned off fsync in /etc/postgresql/postgresql.conf.

The latter did a LOT to improve INSERT performance, which is now
CPU-bound instead of I/O-bound. However, as expected, I am concerned
about the reliability penalty this will cause. Our server has been up
and running without problems for 67 days since the last reboot, but this
doesn't mean it will never hiccup either because of some random problem
or because of an extended power outage.

Would anyone have "authoritative" information with respect to:

 - the way PostgreSQL expects data to be written to disk without the
   fsync calls for things not to get corrupted in the event of a crash,
   and

 - the way XFS writes data to disk without the fsync calls that
   PostgreSQL normally does and how this will affect PostgreSQL data
   integrity in the event of a system crash?

I know that at the end of the day, if I value my data, I must (1) back
it up regularly, and (2) keep fsync enabled in PostgreSQL. However given
the significance performance hit (at least as far as massive INSERT or
UPDATE operations are concerned) and the journalling component of XFS,
it would be great to find out just how bad the odds are if the system
goes down unexpectedly.

Thank you very much for your time. :)

 --> Jijo

Note- I should also have selected RAID10 instead of RAID5, but that's a
change I can't afford to do at this point so I have to explore other
options.

[1] http://oss.sgi.com/projects/xfs/
[2] http://www.snort.org
[3] http://acidlab.sourceforge.net
[4] http://www.andrew.cmu.edu/~rdanyliw/snort/acid_faq.html#faq_c9
[5] http://developer.postgresql.org/docs/postgres/kernel-resources.html

--
Federico Sevilla III  : http://jijo.free.net.ph      : When we speak of free
Network Administrator : The Leather Collection, Inc. : software we refer to
GnuPG Key ID          : 0x93B746BE                   : freedom, not price.