Bumping block size to 16K on FreeBSD...

Sean Chittenden <sean@chittenden.org> writes:

Are there any objections
to me increasing the block size for FreeBSD installations to 16K for
the upcoming 7.4 release?

I'm a little uncomfortable with introducing a cross-platform variation
in the standard block size. That would have implications for things
like whether a table definition that works on FreeBSD could be expected
to work elsewhere; to say nothing of recommendations for shared_buffer
settings and suchlike.

Also, there is no infrastructure for adjusting BLCKSZ automatically at
configure time, and I don't much want to add it.

regards, tom lane

Are there any objections to me increasing the block size for
FreeBSD installations to 16K for the upcoming 7.4 release?

I'm a little uncomfortable with introducing a cross-platform
variation in the standard block size. That would have implications
for things like whether a table definition that works on FreeBSD
could be expected to work elsewhere; to say nothing of
recommendations for shared_buffer settings and suchlike.

Hrm, well, given things just went to beta2, I'm going to bump
postgresql-devel to beta2 and include this patch for now, however, I'm
going to explicitly requests that people who have problems or
successes with beta2 on FreeBSD ask me before possible reporting
problems with a platform specific alteration.

That said, however, an 8% speedup on writes is non-trivial and
something I'd like to pick up if possible. :) I have faith that PG
does the right thing as I'm sure other people have done this w/o
incident in the past, I just don't think anyone's tuned this for the
platform and all of its users.

Also, there is no infrastructure for adjusting BLCKSZ automatically
at configure time, and I don't much want to add it.

The patch gets applied when the port gets built, so there doesn't need
to be a configuration option for it for this to work.

--
Sean Chittenden

Sean Chittenden <sean@chittenden.org> writes:

Hrm, well, given things just went to beta2, I'm going to bump
postgresql-devel to beta2 and include this patch for now, however, I'm
going to explicitly requests that people who have problems or
successes with beta2 on FreeBSD ask me before possible reporting
problems with a platform specific alteration.

You do realize that you'll be forcing initdbs on people if you blithely
add and remove such a patch?

I think we forced an initdb for beta2 for other reasons, but I'm hoping
to avoid any more in the 7.4 branch.

regards, tom lane

Hrm, well, given things just went to beta2, I'm going to bump
postgresql-devel to beta2 and include this patch for now, however,
I'm going to explicitly requests that people who have problems or
successes with beta2 on FreeBSD ask me before possible reporting
problems with a platform specific alteration.

You do realize that you'll be forcing initdbs on people if you
blithely add and remove such a patch?

Yup. I was tempted to include another patch just to bump the catalog
version in the event that it doesn't gracefully detect differing block
sizes... haven't tested if it does that or not.

I think we forced an initdb for beta2 for other reasons, but I'm
hoping to avoid any more in the 7.4 branch.

1) People shouldn't be using -devel for production
2) Reloading is still a PITA, which is why I asked for comments.

Other than you feeling uneasy about the possibility of uncovering bugs
because this hasn't been widely done like this before, do you have any
other concerns, or do you think the possibility of finding bugs very
likely?

-sc

--
Sean Chittenden

Tom Lane wrote:

Sean Chittenden <sean@chittenden.org> writes:

Are there any objections
to me increasing the block size for FreeBSD installations to 16K for
the upcoming 7.4 release?

I'm a little uncomfortable with introducing a cross-platform variation
in the standard block size. That would have implications for things
like whether a table definition that works on FreeBSD could be expected
to work elsewhere; to say nothing of recommendations for shared_buffer
settings and suchlike.

Also, there is no infrastructure for adjusting BLCKSZ automatically at
configure time, and I don't much want to add it.

Has anyone looked at changing the default block size across the board
and what the performance improvements/penalties might be? Hardware has
changed quite a bit over the years.

Sean Chittenden <sean@chittenden.org> writes:

You do realize that you'll be forcing initdbs on people if you
blithely add and remove such a patch?

Yup. I was tempted to include another patch just to bump the catalog
version in the event that it doesn't gracefully detect differing block
sizes... haven't tested if it does that or not.

It does, and you will definitely incur my wrath if you start putting in
unnecessary platform-specific catversion hacks.

I think we forced an initdb for beta2 for other reasons, but I'm
hoping to avoid any more in the 7.4 branch.

1) People shouldn't be using -devel for production

No, but they might reasonably hope to segue a beta installation into
production without yet-another reload ...

regards, tom lane

Thomas Swan <tswan@idigx.com> writes:

Tom Lane wrote:

I'm a little uncomfortable with introducing a cross-platform variation
in the standard block size.

Has anyone looked at changing the default block size across the board
and what the performance improvements/penalties might be? Hardware has
changed quite a bit over the years.

Not that I know of. That might actually be a more reasonable proposal
than changing it only on one platform. It would take a fair amount
of legwork to generate enough evidence to convince people, though ...

regards, tom lane

On Thu, 28 Aug 2003, Thomas Swan wrote:

Has anyone looked at changing the default block size across the board
and what the performance improvements/penalties might be? Hardware has
changed quite a bit over the years.

I *think* that the reason for the performance improvement on FreeBSD is
that our FS block size is 16k, instead of 8k ... are there any other
OSs that have increased theirs?

On Thu, Aug 28, 2003 at 03:56:18PM -0400, Tom Lane wrote:

Sean Chittenden <sean@chittenden.org> writes:

Hrm, well, given things just went to beta2, I'm going to bump
postgresql-devel to beta2 and include this patch for now, however, I'm
going to explicitly requests that people who have problems or
successes with beta2 on FreeBSD ask me before possible reporting
problems with a platform specific alteration.

You do realize that you'll be forcing initdbs on people if you blithely
add and remove such a patch?

I think we forced an initdb for beta2 for other reasons, but I'm hoping
to avoid any more in the 7.4 branch.

if it does in fact give performance for FreeBSD (and possibly others),
at least make it a build time option.

--
[ Jim Mercer jim@reptiles.org +1 416 410-5633 ]
[ I want to live forever, or die trying. ]

On Thu, 28 Aug 2003, Marc G. Fournier wrote:

On Thu, 28 Aug 2003, Thomas Swan wrote:

Has anyone looked at changing the default block size across the board
and what the performance improvements/penalties might be? Hardware has
changed quite a bit over the years.

I *think* that the reason for the performance improvement on FreeBSD is
that our FS block size is 16k, instead of 8k ... are there any other
OSs that have increased theirs?

Linux, is still, as far as I know, limited to the max page size of the CPU
it's on, which for most x86 is 4k.

Windows 2k can go up to 64k block sizes.

Tom Lane wrote:

Thomas Swan <tswan@idigx.com> writes:

Tom Lane wrote:

I'm a little uncomfortable with introducing a cross-platform variation
in the standard block size.

Has anyone looked at changing the default block size across the board
and what the performance improvements/penalties might be? Hardware has
changed quite a bit over the years.

Not that I know of. That might actually be a more reasonable proposal
than changing it only on one platform. It would take a fair amount
of legwork to generate enough evidence to convince people, though ...

I know that you can specify different block sizes for different fs/OS
combinations, notably there were discussions before about running the
WAL on a fat16/32 disks with different performance characteristics.
Also, it's not just an OS abstraction; hardware has changed and evolved
in such a way that the physical disks are reading and writing in larger
chunks. To me it would seem wasteful to not use that bandwidth that is
available for little or no extra cost.

Perhaps testing it for 8K, 16K, 32K, and 64K blocksizes would be a
worthwhile venture. I will have time this weekend with the holiday to
work on some benchmarking for these sizes if only on a linux system.
Tom, what would you consider to be acceptable for a preliminary
investigation? What should I look at: runtime, disk space required
before and after, fsync (on/off)?

--
Thomas

Sean Chittenden <sean@chittenden.org> writes:

Early performance tests on my laptop suggest it's about 8% faster for
writing when both the FS and PostgreSQL use 16K blocks.

BTW, I don't really believe that one set of tests, conducted on one
single machine, are anywhere near enough justification for changing this
value. Especially not if it's a laptop rather than a typical server
configuration. You've got considerably less I/O bandwidth in proportion
to CPU horsepower than a server. Why is that an issue? Well, a larger
block size will substantially increase our WAL overhead (because we tend
to dump whole blocks into WAL at the slightest provocation) and on
slower machines the CRC64 calculations involved in WAL entries are a
significant cost. On a machine with less CPU and more disk horsepower
than you tested, the tradeoffs could be a lot different.

regards, tom lane

Sean, can we get a copy of your test set? And any scripts that you have
for running the tests?

On Thu, 28 Aug 2003, Tom Lane wrote:

On Thu, Aug 28, 2003, Tom Lane wrote:

Sean Chittenden <sean@chittenden.org> writes:

Are there any objections
to me increasing the block size for FreeBSD installations to 16K for
the upcoming 7.4 release?

I'm a little uncomfortable with introducing a cross-platform variation
in the standard block size. That would have implications for things
like whether a table definition that works on FreeBSD could be expected
to work elsewhere; to say nothing of recommendations for shared_buffer
settings and suchlike.

Also, there is no infrastructure for adjusting BLCKSZ automatically at
configure time, and I don't much want to add it.

On recent versions of FreeBSD (and Solaris too, I think), the
default UFS block size is 16K, and file fragments are 2K. This
works great for many workloads, but it kills pgsql's random write
performance unless pgsql uses 16K blocks as well, due to the
read-modify-write involved. Either the filesystem or the database
needs to be changed in order to get decent performance. I have
not compared 16K UFS/16K pgsql to 8K UFS/8K pgsql, so I can't say
which option makes more sense, though. There probably isn't
anything wrong with the pgsql default, except that it's set in
stone.

It's entirely feasible for administrators to create 8K/1K UFS
filesystems specifically for pgsql, but they need to be aware of
the issue. On the other hand, I don't see how it would be a bad
thing if pgsql were able to adapt at runtime either. Thus, I've
come up with two possible fixes:

(1) Document the problem with having a filesystem block size
larger than the database block size. With a simple call to
statvfs(2), the postmaster could warn about this on startup, too.

(2) Make BLCKSZ a runtime constant, stored as part of the database.
Grepping through the source, I didn't see any places
using BLCKSZ where efficiency appeared to be so critical that
you had to have constant folding. Of course, one could introduce
a 'lg2blksz' constant to avoid divides and multiplies.

This would NOT introduce cross-platform incompatibilities, only
efficiency problems with databases that have been moved across
filesystems in some cases. The ability to adapt at database
creation time is also useful in that it allows the database to
be tuned to the characteristics of the particular device on
which it resides.[1]Right now, the seek time to transfer time ratio of the drive is mostly hidden by the operating system's clustering and read-ahead. I tried modifying pgsql to use direct I/O, but it seems that pgsql doesn't do its own clustering or read-ahead, so that was a lose...

I don't know very much about pgsql, so corrections and feedback
regarding these ideas would be appreciated.

[1]: Right now, the seek time to transfer time ratio of the drive is mostly hidden by the operating system's clustering and read-ahead. I tried modifying pgsql to use direct I/O, but it seems that pgsql doesn't do its own clustering or read-ahead, so that was a lose...
is mostly hidden by the operating system's clustering and
read-ahead. I tried modifying pgsql to use direct I/O, but
it seems that pgsql doesn't do its own clustering or read-ahead,
so that was a lose...

On Thu, Aug 28, 2003, scott.marlowe wrote:

On Thu, 28 Aug 2003, Marc G. Fournier wrote:

On Thu, 28 Aug 2003, Thomas Swan wrote:

Has anyone looked at changing the default block size across the board
and what the performance improvements/penalties might be? Hardware has
changed quite a bit over the years.

I *think* that the reason for the performance improvement on FreeBSD is
that our FS block size is 16k, instead of 8k ... are there any other
OSs that have increased theirs?

Linux, is still, as far as I know, limited to the max page size of the CPU
it's on, which for most x86 is 4k.

I don't know about the page size issue, but Linux has the
additional problem that ext2/ext3 do not support fragments or
variable block sizes within the same filesystem. Therefore, Linux
wastes an excessive amount of space for larger block sizes.

Early performance tests on my laptop suggest it's about 8%
faster for writing when both the FS and PostgreSQL use 16K
blocks.

BTW, I don't really believe that one set of tests, conducted on
one single machine, are anywhere near enough justification for
changing this value. Especially not if it's a laptop rather than
a typical server configuration. You've got considerably less I/O
bandwidth in proportion to CPU horsepower than a server. Why is
that an issue? Well, a larger block size will substantially
increase our WAL overhead (because we tend to dump whole blocks
into WAL at the slightest provocation) and on slower machines the
CRC64 calculations involved in WAL entries are a significant cost.
On a machine with less CPU and more disk horsepower than you
tested, the tradeoffs could be a lot different.

Sean, can we get a copy of your test set? And any scripts that you
have for running the tests?

Unfortunately not, my tests were simply re-initdb'ing and loading in
my schema. I have some read tests I'm going to perform here in a bit,
but I'm waiting for kde to finish compiling before I start testing.
I have another tests machine that I'm going to task with comparing 16K
and 8K blocks. It's not SCSI, but I don't have any available machines
that I can newfs + reinstall PostgreSQL on. I was thinking about
running the regression tests 10x ...

-sc

--
Sean Chittenden

On Thu, Aug 28, 2003 at 01:00:44PM -0700, Sean Chittenden wrote:

Other than you feeling uneasy about the possibility of uncovering bugs
because this hasn't been widely done like this before, do you have any
other concerns, or do you think the possibility of finding bugs very
likely?

In case Tom didn't make this clear, I'm strongly opposed to making
this change without doing the necessary (non-FreeBSD-specific) legwork.
The bottom-line is that if we're going to be changing the block size
on a regular basis, it needs to be completely transparent to the user,
from a functionality perspective. That's currently not the case:
changing the BLCKSZ changes the meaning of shared_buffers and
effective_cache_size, for example, so tuning documents written for
other operating systems won't apply as easily to PostgreSQL on
FreeBSD. Until the user-visible effects of BLCKSZ have been ironed
over[1]Other improvements, like making it easier to change the blocksize (making it a configure option?) would be cool too., I definately think you shouldn't include the patch in the
FreeBSD port.

[1]: Other improvements, like making it easier to change the blocksize (making it a configure option?) would be cool too.
blocksize (making it a configure option?) would be cool too.

-Neil

On Thu, 28 Aug 2003, Neil Conway wrote:

On Thu, Aug 28, 2003 at 01:00:44PM -0700, Sean Chittenden wrote:

Other than you feeling uneasy about the possibility of uncovering bugs
because this hasn't been widely done like this before, do you have any
other concerns, or do you think the possibility of finding bugs very
likely?

In case Tom didn't make this clear, I'm strongly opposed to making
this change without doing the necessary (non-FreeBSD-specific) legwork.
The bottom-line is that if we're going to be changing the block size
on a regular basis, it needs to be completely transparent to the user,
from a functionality perspective. That's currently not the case:
changing the BLCKSZ changes the meaning of shared_buffers and
effective_cache_size, for example, so tuning documents written for
other operating systems won't apply as easily to PostgreSQL on
FreeBSD. Until the user-visible effects of BLCKSZ have been ironed
over[1], I definately think you shouldn't include the patch in the
FreeBSD port.

"tuning documents" is *not* a valid reason for not doing this ... that's
like saying "we can make it faster on some operating systems, but because
we're going to have to modify the tuning documents, we're not going to do
it" ... wait, that is exactly what you are saying ...

Now, Tom made one point in his original that *was* valid ... a table
definition made under a 16k BLCKSZ db will not necessarily work under an
8k compiled server .. the example that he made to me was that a table of
float8 under a 16k server could have N fields, but if you tried to
dump/import that table into an 8k BLCKSZ one with that max # of fields, it
would fail ... that is a *serious* concern against doing this ...

Now, here's a question for someone running a non-FreeBSD OS ... if we were
to jump the BLCKSZ to 16k, would it cause a degradation in performance, or
would it make no difference to them? Would they see an 8% reduction in
performance?

The thing is ... there has been presented a strong, valid reason for
moving to 16k (at least under FreeBSD) ... and there has been a valid
reason for not making it "easily configurable" ... but, are there any
strong reasons not to just move to 16k across the board?

On Fri, Aug 29, 2003 at 12:06:59AM -0300, Marc G. Fournier wrote:

"tuning documents" is *not* a valid reason for not doing this ... that's
like saying "we can make it faster on some operating systems, but because
we're going to have to modify the tuning documents, we're not going to do
it" ... wait, that is exactly what you are saying ...

No, it's a perfectly valid reason for not doing this (in the present,
half-baked form that has been presented). PostgreSQL is at the moment
fairly simple to configure. Adding a significant amount of complexity
to the configuration / tuning process and making a given configuration
non-portable between different platforms and different compiles of
PostgreSQL is something I'd like to avoid, if possible.

And I think it's possible to avoid it, it's just that the original
patch makes no attempt to do so. For example, why does
shared_buffers need to be specified in disk pages, anyway? ISTM it
could just as easily be specified in bytes, and PostgreSQL could
internally round up/down to the nearest multiple of the BLCKSZ that
this instance of PostgreSQL happened to be compiled with.

Now, Tom made one point in his original that *was* valid ... a table
definition made under a 16k BLCKSZ db will not necessarily work under an
8k compiled server .. the example that he made to me was that a table of
float8 under a 16k server could have N fields, but if you tried to
dump/import that table into an 8k BLCKSZ one with that max # of fields, it
would fail ... that is a *serious* concern against doing this ...

Uh, yeah -- I was talking about that as well. I said "it needs to be
completely transparent to the user, from a functionality perspective".
If changing the BLCKSZ makes things faster or slower, then fine; if it
changes the meaning of various random configuration parameters, makes
certain schemas work or not work, and makes other changes to postgres
functionality, then it's not fine.

-Neil

On Thu, 28 Aug 2003, David Schultz wrote:

(2) Make BLCKSZ a runtime constant, stored as part of the database.

Now this I really like. It would make benchmarking 8K vs. 16K blocksizes
much easer, as well as of course avoiding the "initdb required after
rebuilding" problem.

BTW, pretty much every BSD system is going to be using 16K block sizes
on large partitions; the cylinder group size and filesystem overhead is
way, way too small when using 8K blocks.

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.NetBSD.org
Don't you know, in this new Dark Age, we're all light. --XTC

I am not 100% sure that 16K blocksize is the best size, for instance :

Using FreebSD 5.1 - I got the best read and write performance using a
blocksize of 32K with 4K fragments - [ reading and writing 8K blocks,
ufs1 and ufs2 fs ].

I dont have the results in front of me, but I think I tried fs
blocksizes from 4K upwards....

I am also not convinced that using 16K in Pg will be better than 8K (you
would expect sequential performance to improve, but maybe at the expense
of random ....)

regards

Mark

On Fri, Aug 29, 2003 at 12:06:59AM -0300, Marc G. Fournier wrote:

The thing is ... there has been presented a strong, valid reason for
moving to 16k (at least under FreeBSD) ... and there has been a valid

It sounds to me, actually, like there is a strong reason for telling
people running FreeBSD, "Hey, you can get this big speedup at the
possible expense of compatibility by compiling with changes XYZ."
But quietly putting that into packages for distribution strikes me as
the sort of support headache that one really doesn't want.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Marc G. Fournier wrote:

Now, here's a question for someone running a non-FreeBSD OS ... if we were
to jump the BLCKSZ to 16k, would it cause a degradation in performance, or
would it make no difference to them? Would they see an 8% reduction in
performance?

The thing is ... there has been presented a strong, valid reason for
moving to 16k (at least under FreeBSD) ... and there has been a valid
reason for not making it "easily configurable" ... but, are there any
strong reasons not to just move to 16k across the board?

First, I assume all this discussion about default block size is for 7.5,
not for 7.4, which is in beta. Second, the tests were done only for
_write_ performance. We can expect random read performance to be worse
for larger block sizes, so I think more research needs to be done.
Also, Tatsuo reported years ago that he got ~15% performance improvement
with a 32k PostgreSQL block size. The OS was AIX or Linux.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073