suggested block size for a frequently updated postgresql?

[Oliver Fromme]

Dryice Liu <dryice at hotpop.com> wrote:
 > I'm planning on setting up a postgresql server, the database data
 > files are on their own slice so I can tune the file system for pgsql.
 > 
 > The database is pretty large. On my test server, some data files get
 > larger than 1G and is splitted by pgsql. Also the database will be
 > updated frequently.
 > 
 > I'm planning on setting the slice with a bigger block size/fragment
 > size but not sure if that's a good idea. I know the default on FreeBSD
 > is 16K/2K, I'm planning on something like 1M/128K.

FreeBSD doesn't support bsize/fsize that large.  In fact,
I wouldn't trust anything other than 16K/2K and 8K/1K to
work reliably under load.  I also remember Matt Dillon
mentioned that larger block sizes are less-than-optimal
for the VM system.

Besides, I don't think that PostgreSQL would benefit from
such huge fragment and block sizes.  Let me explain ...

PostgreSQL does two things on the file system:  First, it
records all modifications to the WAL file.  This is a
sequential process, and I wouldn't expect the blocksize to
have any significant effect.  Second, it updates the actual
table data in the data files.  This consists of appending
data to the files and random access inside the files, which
would definitely not benefit from a larger block size.
Also take into account that the regular "vacuum" process of
postgresql is random access inside the data files.

For more details on that I recommend you ask in the Post-
greSQL list (pgsql-novice).  There are very knowledgeable
and responsive people there.

So, the bottom line is:  I recommend you leave the bsize
and fsize at the default 16K/2K.  However, you probably
should reduce the inode density from the default, i.e. use
the -i option with some high value such as 262144 (that's
2^18).  But don't make this value too high either ...
I remember someone tried to set it to 64 million or some-
thing, which broke his FS.

Best regards
   Oliver

-- 
Oliver Fromme,  secnetix GmbH & Co. KG, Marktplatz 29, 85567 Grafing
Dienstleistungen mit Schwerpunkt FreeBSD: http://www.secnetix.de/bsd
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"With sufficient thrust, pigs fly just fine.  However, this
is not necessarily a good idea.  It is hard to be sure where
they are going to land, and it could be dangerous sitting
under them as they fly overhead." -- RFC 1925