[seanc@FreeBSD.org: Re: Performance tests I did with FreeBSD, Linux and PostgreSQL]

There's been some recent discussion on some of the FreeBSD mailing
lists about PostgreSQL and performance.  FreeBSD uses 16K blocks for
its FS and it has been suggested that PostgreSQL on FreeBSD be
modified to use 16K blocks instead of 8K blocks.  Are there any
adverse reactions or reasons to not do this for the upcoming 7.4
release?

'bout the only reason I could think of would be someone using FreeBSD,
but with their PGDATA dir on an ext2 partition. :-P

Early performance tests on my laptop suggest it's 8% faster when the
FS has 16K blocks (the default after FreeBSD 4.5-RELEASE).  From my
tests loading a database:

With 8K blocks:
15.188u 3.404s 7:12.27 4.2%     209+340k 1251+0io 0pf+0w
14.867u 3.686s 7:32.54 4.0%     201+327k 1252+0io 0pf+0w

avg wall clock sec to complete: 442

With 16K blocks:
15.192u 3.312s 6:44.43 4.5%     198+322k 1253+0io 0pf+0w
15.120u 3.330s 6:51.43 4.4%     205+334k 1254+0io 0pf+0w

avg wall clock sec to complete: 407

I'll take the 35sec/8% speedup any day of the week and twice on
Sunday.  Granted these tests were done on my laptop and were 100%
write.  If someone wants to do some good read tests, I'd be interested
in those results to see if it's still 8% faster..  I imagine this'd
make seq scans cheaper on FreeBSD.

Comments?  -sc

--
Sean Chittenden
> > > Like all benchmarks, I doubt these are perfect (or even close)
> > > examples of real-world use.
> > >
> > > However, in the hopes that they will be useful for improving FreeBSD, I
> > > present them to the community.
> > >
> > > http://www.potentialtech.com/wmoran/postgresql.php
> > >
> > > Intelligent comments are welcome.
> >
> > Well, a little trussing revealed that pgsql uses a block size of
> > 8K, which can be a significant pessimization when the filesystem
> > block size is 16K, which is the default in FreeBSD!  ext2fs uses
> > 4K blocks and reiserfs uses extents, so those filesystems would
> > not suffer from that problem.  You might consider trying your test
> > on a UFS filesystem with 8K blocks, or you could tune pgsql to use
> > a different block size.  (For pgsql, I think the appropriate
> > tunable is BLCKSZ in include/pg_config.h.  You'll have to
> > recompile it and create a new database.)
>
> I wonder if it would benefit us to do some tweaks to Postgres to
> tune the pgsql block size to the file system it's running on, and
> get it integrated back into the distribution....  As disks get
> bigger, a 16k block size is going to become more common, so this
> won't just hit FreeBSD, I expect.

Hrm, that sounds like a good idea to me.  The appropriate patch is
included (src/include/pg_config_manual.h).  I'll bounce this by the
PostgreSQL guys to see what their reaction is, but I'm inclined to
include this patch in the postgresql-devel port for now, and if things
go well there, possibly in the main postgresql7 port (pending
feedback).  I haven't ever played with this setting before and would
welcome feedback from anyone who has (rergardless of their
results). -sc

PS You have to re-initdb/reload your data after applying this patch
   because the size of data on disk has changed.

--
Sean Chittenden
_______________________________________________
freebsd-performance@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "freebsd-performance-unsubscribe@freebsd.org"