Subject: Re: POSIX file updates
From: Andreas Kostyrka <andreas@kostyrka.org>
To: James Mansion <james@mansionfamily.plus.com>
Cc: Greg Smith <gsmith@gregsmith.com>,
	pgsql-performance@postgresql.org
In-Reply-To: <47F3DA25.7050705@mansionfamily.plus.com>
References: <47F1413B.40109@mansionfamily.plus.com>
	<Pine.GSO.4.64.0803311618040.15117@westnet.com>
	<47F3DA25.7050705@mansionfamily.plus.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-o/LFjS0GnvKwj0b2AFTT"
Date: Wed, 02 Apr 2008 22:24:12 +0200
Message-Id: <1207167852.8299.179.camel@localhost>
Mime-Version: 1.0


--=-o/LFjS0GnvKwj0b2AFTT
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable


Am Mittwoch, den 02.04.2008, 20:10 +0100 schrieb James Mansion:
> It strikes me as odd that fsync_writethrough isn't the most preferred=20
> option where
> it is implemented. The postgres approach of *requiring* that there be no=20
> cache
> below the OS is problematic, especially since the battery backup on inter=
nal
> array controllers is hardly the handiest solution when you find the mobo=20
> has died.

Well, that might sound brutal, but I'm having today a brute day.

There are some items here.

1.) PostgreSQL relies on filesystem semantics. Which might be better or
worse then the raw devices other RDBMS use as an interface, but in the
end it is just an interface. How well that works out depends strongly on
your hardware selection, your OS selection and so on. DB tuning is an
scientific art form ;) Worse the fact that raw devices work better on
hardware X/os Y than say filesystems is only of limited interest, only
if you happen to have already an investement in X or Y. In the end the
questions are is the performance good enough, is the data safety good
enough, and at which cost (in money, work, ...).

2.) data safety requirements vary strongly. In many (if not most) cases,
the recovery of the data on a failed hardware is not critical. Hint:
being down till somebody figures out what failed, if the rest of the
system is still stable, and so on are not acceptable at all. Meaning the
moment that the database server has any problem, one of the hot standbys
takes over. The thing you worry about is if all data has made it to the
replication servers, not if some data might get lost in the hardware
cache of a controller. (Actually, talk to your local computer forensics
guru, there are a number of way to keep the current to electronics while
moving them.)

3.) a controller cache is an issue if you have a filesystem in your data
path or not. If you do raw io, and the stupid hardware do cache writes,
well it's about as stupid as it would be if it would have cached
filesystem writes.

Andreas


> And especially when the inability to flush caches on modern SATA and SAS
> drives would appear to be more a failing in some operating systems than i=
n
> the drives themselves..
>=20
> The links I've been accumulating into my bibliography include:
>=20
> http://www.h2database.com/html/advanced.html#transaction_isolation
> http://lwn.net/Articles/270891/
> http://article.gmane.org/gmane.linux.kernel/646040
> http://lists.apple.com/archives/darwin-dev/2005/Feb/msg00072.html
> http://brad.livejournal.com/2116715.html
>=20
> And your handy document on wal tuning, of course.
>=20
> James
>=20
>=20

--=-o/LFjS0GnvKwj0b2AFTT
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: Dies ist ein digital signierter Nachrichtenteil

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQBH8+tsHJdudm4KnO0RAh5uAKC6u6OuKyr0y4wIMQmDuGTym0gDqACg3R/Q
0Ac+DQBuEP/eImpiu28gRRk=
=jEWx
-----END PGP SIGNATURE-----

--=-o/LFjS0GnvKwj0b2AFTT--