I am going to compare a 16KB PostgreSQL system to an 8KB system. I am working
on the assumption that 16K takes about as long to read as 8K, and That the CPU
overhead of working with a 16K block is not too significant. 

I know with toast, block size is no longer an issue, but 8K is not a lot these
days, and it seems like a lot of syscall and block management overhead could be
reduced by doubling it. Any comments?

The test system is a dual 850MHZ PIII, 1G memory, RedHat 7.2, 2 IBM SCSI 18G
hard disks, intel motherboard with onboard adaptec SCSI ULVD.

Besides pgbench, anyone have any tests that they would like to try?

Has anyone already done this test and found it useful/useless?

Thu, 25 Apr 2002 09:04:07 -0400
mlw <markw@mohawksoft.com>
Block size: 8K or 16K?
Jean-Paul ARGUDO wrote:
> 
> > I know with toast, block size is no longer an issue, but 8K is not a lot these
> > days, and it seems like a lot of syscall and block management overhead could be
> > reduced by doubling it. Any comments?
> 
> IMHO, I think this would enhance performances only if tuple lenght is
> above 8k, huh?..
> 
> I mean, I think this would enhance databases with many large objects. On
> the contrary, database with classical varchar and integers wont benefit
> it, don't you think?

See, I'm not sure. I can make many arguments pro or con, an I could defend
either, but my gut tells me that using 16K blocks will increase performance
over 8K. Aleady I have seen a sequential scan of a large table go from 20
seconds using 8K to 17.3 seconds using 16K.

select * from zsong where song like '%fubar%';


I am copying my pgbench database to the new block size to test that.

8K vs 16K
Pros: 
A sequential scan will require 1/2 the number of system calls for the same
amount of data.
Block "cache" management costs will be cut in half, for the same amount of
data.
More index information per fetch.
Larger tuples can be stored without toasting.

Cons:
Time to search a block for a tuple may increase.
More memory used per block (These days, I don't think this is too much of an
issue.)

This is based on the assumption that reading an 8K chunk is about as costly as
reading a 16K chunk. If this assumption is not true, then the arguments do not
work.

Thu, 25 Apr 2002 09:49:40 -0400
mlw <markw@mohawksoft.com>
Re: Block size: 8K or 16K?
On Thu, 25 Apr 2002 09:04:07 -0400
"mlw" <markw@mohawksoft.com> wrote:
> I am going to compare a 16KB PostgreSQL system to an 8KB system. I am working
> on the assumption that 16K takes about as long to read as 8K, and That the CPU
> overhead of working with a 16K block is not too significant. 
> 
> I know with toast, block size is no longer an issue, but 8K is not a lot these
> days, and it seems like a lot of syscall and block management overhead could be
> reduced by doubling it. Any comments?

It's something I was planning to investigate, FWIW. I'd be interested to see
the results...

> The test system is a dual 850MHZ PIII, 1G memory, RedHat 7.2, 2 IBM SCSI 18G
> hard disks, intel motherboard with onboard adaptec SCSI ULVD.
> 
> Besides pgbench, anyone have any tests that they would like to try?

Perhaps OSDB? http://osdb.sf.net

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Thu, 25 Apr 2002 11:21:14 -0400
Neil Conway <nconway@klamath.dyndns.org>
Re: Block size: 8K or 16K?
On Thu, 25 Apr 2002, mlw wrote:

> ...but my gut tells me that using 16K blocks will increase performance
> over 8K. Aleady I have seen a sequential scan of a large table go from 20
> seconds using 8K to 17.3 seconds using 16K.

You should be able to get the same performance increase with 8K
blocks by reading two blocks at a time while doing sequential scans.
That's why I've been promoting this idea of changing postgres to
do its own read-ahead.

Of course, Bruce might be right that the OS read-ahead may take
care of this anyway, but then why would switching to 16K blocks
improve sequential scans? Possibly because I'm missing something here.

Anyway, we now know how to test the change, should someone do it:
compare sequential scans with and without readahead on 8K blocks,
and then compare that against a server without readahead but with
block sizes the size of the readahead (64K, I propose--oh wait, we
can only do 32K....)

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


Fri, 26 Apr 2002 14:09:23 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: Block size: 8K or 16K?
Curt Sampson wrote:
> On Thu, 25 Apr 2002, mlw wrote:
> 
> > ...but my gut tells me that using 16K blocks will increase performance
> > over 8K. Aleady I have seen a sequential scan of a large table go from 20
> > seconds using 8K to 17.3 seconds using 16K.
> 
> You should be able to get the same performance increase with 8K
> blocks by reading two blocks at a time while doing sequential scans.
> That's why I've been promoting this idea of changing postgres to
> do its own read-ahead.
> 
> Of course, Bruce might be right that the OS read-ahead may take
> care of this anyway, but then why would switching to 16K blocks
> improve sequential scans? Possibly because I'm missing something here.

I am almost sure that increasing the block size or doing read-ahead in
the db will only improve performance if someone is performing seeks in
the file at the same time, and hence OS readahead is being turned off.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 26 Apr 2002 01:28:25 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Block size: 8K or 16K?
Bruce Momjian wrote:
> 
> Curt Sampson wrote:
> > On Thu, 25 Apr 2002, mlw wrote:
> >
> > > ...but my gut tells me that using 16K blocks will increase performance
> > > over 8K. Aleady I have seen a sequential scan of a large table go from 20
> > > seconds using 8K to 17.3 seconds using 16K.
> >
> > You should be able to get the same performance increase with 8K
> > blocks by reading two blocks at a time while doing sequential scans.
> > That's why I've been promoting this idea of changing postgres to
> > do its own read-ahead.
> >
> > Of course, Bruce might be right that the OS read-ahead may take
> > care of this anyway, but then why would switching to 16K blocks
> > improve sequential scans? Possibly because I'm missing something here.
> 
> I am almost sure that increasing the block size or doing read-ahead in
> the db will only improve performance if someone is performing seeks in
> the file at the same time, and hence OS readahead is being turned off.

I largely agree with you, however, don't underestimate the overhead of a read()
call. By doubling the block size, the overhead of my full table scan was cut in
half, thus potentially more efficient, 20 seconds was reduced to 17. (That was
on a machine only doing one query, not one under full load, so the real effect
may be much more subtle.)

In fact, I posted some results of a comparison between 16k and 8k blocks, I saw
very little difference on most tests while a couple looked pretty interesting.

Fri, 26 Apr 2002 08:27:24 -0400
mlw <markw@mohawksoft.com>
Re: Block size: 8K or 16K?