--juwRYO7N74otsfNI
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Sat, Sep 15, 2007 at 04:28:16PM -0700, Darren Reed wrote:
> Playing around with a filesystem based database, such as postgres,
> is an interesting experience in trying to keep it running fast...some
> of the tweaks are db related, some not...

Indeed.

> For example, if the db is serving queries every second or so,
> then there's a lot of "atime" updates that happen and mounting the
> filesystem with -noatime has a noticable impact on performance.

It shouldn't, since atime writes are deferred to either vnode
recycling or when the inode needs to get rewritten for something else
either.  Hm, maybe that only happens with softdep?

Check also that the fs block size matches the db page size (8k by
default, though I run all mine larger).

> Having done that, I'm wondering if it makes sense for NetBSD
> to support -nomtime as well.  I'm not sure what impact this will
> have on postgres's operation (does it depend on file modification
> timestamp changes?) but I'm semi-willing to give it a shot.

By all means experiment :-)

> Does anyone else have any thoughts/criticisms/experience on this?

On the whole, I think if the seeks from this activity are causing
detectable overhead, you need more spindles regardless.  If you have
a performance problem (rather than just tweaking for the sake of fun
or curiosity or general efficiency) it's very easy to spend a lot of
time down in the noise when adding a spindle or three will make
enormous difference.

Once you do, or if you already have several, consider separating the
WAL onto dedicated disk to avoid contention, and if you have
specifically hot tables separate the tables from their indexes.  This
may seem less balanced than using a raid/stripe to spread all the disk
traffic stochastically, but it lets you take advantage of knowledge
about the unbalanced workload.  If you have a read-heavy workload,
using 2- or 3-way mirrored disk can greatly help with parallel
seeking.

--
Dan.
--juwRYO7N74otsfNI
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (NetBSD)

iD8DBQFG7HmGEAVxvV4N66cRAilhAJ4u9qpLrmu9SROfPcOjmP5MNGpk+ACeLwpm
U7I/xZZCyk4RtNKNgTbK53Y=
=ld/z
-----END PGP SIGNATURE-----

--juwRYO7N74otsfNI--